What is REBALANCE or RESILVER or RESYNC or COMPACT?

What is REBALANCE or RESILVER or RESYNC or COMPACT?

All these words look similar in nature of work they perform and everyone can assume all these words represents the similar in ASM diskgroup.

IF we go in deep understanding of each of these activities, each activity has its own roles to be performed.

I will try to explain in this blog in simple words what all these activities means.

When these activities can be seen in ASM disk groups?

--- When disk fails (transit failure or permeant failure)

--- When disk replacement activity happens

--- When flash disk fails

--- When ACFS disk fails

--- When ZFS disk fails

--- and may more scenarios  

SQL> select * from v$asm_operation;

   INST_ID GROUP_NUMBER OPERA PASS      STAT      POWER     ACTUAL  SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE   CON_ID

---------- ------------ ----- --------- ---- ----------  ---------- ----- -------- -------- ----------- -----------  ----------

         1            1 REBAL RESYNC        DONE         12                                                              0

         1            1 REBAL RESILVER     DONE         12                                                              0

         1            1 REBAL REBALANCE WAIT          12                                                              0

         1            1 REBAL COMPACT     WAIT          12               

                                  

          

What is RESYNC or RESILVER or REBALANCE or COMPACT?

Rebalancing:

is something like spreading the data evenly across all the disks in a disk group.

--- It will happen during disk replacement operation.

--- It happens when disk fails.

Resilvering:

means copying of data from one side of the mirror to another. It is like rebuilding.

--- It will happen in EXADATA flash disks

--- It will happen in ACFS disks as well 

--- It will also be seen in ZFS

Resync:

is something like syncronizing the disks with the data that should reside on them. (can be used on transient failure)

--- It occurs in transient failure of a disk.

--- The content present in the failed disk is tracked by other disks and any modification that is made to the content of failed disk is actually made in other available disks. Once we get the disk back and attach it, the data belonging to this disk and which got modified during that time will get resynchronized back again.

Compact:

is de-fragments and compacts extents across Oracle ASM disks

Regards,

Mallik

Exadata - Linking Oracle Home with RDS or UDP protocol in Exadata

Linking Oracle Home with RDS or UDP protocol in Exadata:

Enabling RDS or UDP protocol in Exadata:

In Oracle Exadata oracle home either work with RDS or UDP protocol. We need to enable wither of one protocol on RAC oracle home.

If there is the miss match between the protocol across the RAC nodes, RAC instance will not come online.

Example:

4 node RAC, In node1 Oracle Home is enabled with RDS and rest other nodes Oracle Home enabled with UDP. In this scenario RAC instances will not come online. 

[root@eddrdbadm01 ~]# dcli -g dbs_group -l root /u01/product/db/CSEBSC1/11.2.0.3/bin/skgxpinfo

eddrdbadm01: rds      >>>>>>>>>>>>>>> Issue

eddrdbadm02: udp

eddrdbadm03: udp

eddrdbadm04: udp

Solution: We need to either convert all RAC nodes to link with RDS protocol or UDP protocol.

[root@eddrdbadm01 ~]# dcli -g dbs_group -l root /u01/product/db/CSEBSC1/11.2.0.3/bin/skgxpinfo

eddrdbadm01: udp

eddrdbadm02: udp

eddrdbadm03: udp

eddrdbadm04: udp

[root@eddrdbadm01 ~]# dcli -g dbs_group -l root /u01/product/db/CSEBSC1/11.2.0.3/bin/skgxpinfo

eddrdbadm01: rds

eddrdbadm02: rds

eddrdbadm03: rds

eddrdbadm04: rds

Below is the MOS document explains how to convert or enable RDS or UDP in Oracle home.

Linking Home with the RDS or UDP (Doc: 1574772.1)

For RDS:

$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk ipc_rdsioracle

For UDP:

$ cd $GRID_HOME/rdbms/lib

$ make -f ins_rdbms.mk ipc_gioracle

Enabling UDP:

===========

[oragold@HOST01 ~]$ . oraenv

ORACLE_SID = [/u01/app/oracle/product/11.2.0.4/EBSGOLD] ? EBSGOLD

The Oracle base has been set to /u01/app/oracle/EBSGOLD

[oragold@HOST01 ~]$ /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin/srvctl status database -d EBSGOLD

Instance EBSGOLD1 is not running on node HOST01

Instance EBSGOLD2 is not running on node HOST02

[oragold@HOST01 ~]$ cd $ORACLE_HOME/rdbms/lib

[oragold@HOST01 lib]$ make -f ins_rdbms.mk ipc_g ioracle

rm -f /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/libskgxp11.so

cp /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib//libskgxpg.so /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/libskgxp11.so

chmod 755 /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin

 - Linking Oracle

rm -f /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/oracle

gcc  -o /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/oracle -m64 -z noexecstack -L/u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/ -L/u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/ -L/u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/stubs/   -Wl,-E /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/opimai.o /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/ssoraed.o /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/ttcsoi.o  -Wl,--whole-archive -lperfsrv11 -Wl,--no-whole-archive /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/nautab.o /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/naeet.o /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/naect.o /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/naedhs.o /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/config.o  -lserver11 -lodm11 -lcell11 -lnnet11 -lskgxp11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11  -lvsn11 -lcommon11 -lgeneric11 -lknlopt `if /usr/bin/ar tv /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap11" ; fi` -lslax11 -lpls11  -lrt -lplp11 -lserver11 -lclient11  -lvsn11 -lcommon11 -lgeneric11 `if [ -f /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/libavserver11.a ] ; then echo "-lavserver11" ; else echo "-lavstub11"; fi` `if [ -f /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/libavclient11.a ] ; then echo "-lavclient11" ; fi` -lknlopt -lslax11 -lpls11  -lrt -lplp11 -ljavavm11 -lserver11  -lwwg  `cat /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lmm -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lztkg11 `cat /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `if /usr/bin/ar tv /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo11"; fi` -L/u01/app/oracle/product/11.2.0.4/EBSGOLD/ctx/lib/ -lctxc11 -lctx11 -lzx11 -lgx11 -lctx11 -lzx11 -lgx11 -lordimt11 -lclsra11 -ldbcfg11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11 -locr11 -locrb11 -locrutl11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11  -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged  -lippsmerged -lippcore  -lippcpemerged -lippcpmerged  -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lsnls11 -lunls11  -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lasmclnt11 -lcommon11 -lcore11 -laio    `cat /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/11.2.0.4/EBSGOLD/lib -lm    `cat /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/11.2.0.4/EBSGOLD/lib

test ! -f /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin/oracle ||\

           mv -f /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin/oracle /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin/oracleO

mv /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/oracle /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin/oracle

chmod 6751 /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin/oracle

[oragold@HOST01 lib]$

[oragold@HOST01 lib]$ /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin/skgxpinfo

udp

[oragold@HOST01 lib]$

Enabling RDS:

===========

[oragold@HOST01 ~]$ . oraenv

ORACLE_SID = [/u01/app/oracle/product/11.2.0.4/EBSGOLD] ? EBSGOLD

The Oracle base has been set to /u01/app/oracle/EBSGOLD

[oragold@HOST01 ~]$ /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin/srvctl status database -d EBSGOLD

Instance EBSGOLD1 is not running on node HOST01

Instance EBSGOLD2 is not running on node HOST02

[oragold@HOST01 ~]$ cd $ORACLE_HOME/rdbms/lib

[oragold@HOST01 lib]$ make -f ins_rdbms.mk ipc_rds ioracle                 

rm -f /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/libskgxp11.so

cp /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib//libskgxpr.so /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/libskgxp11.so

chmod 755 /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin

 - Linking Oracle

rm -f /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/oracle

gcc  -o /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/oracle -m64 -z noexecstack -L/u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/ -L/u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/ -L/u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/stubs/   -Wl,-E /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/opimai.o /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/ssoraed.o /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/ttcsoi.o  -Wl,--whole-archive -lperfsrv11 -Wl,--no-whole-archive /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/nautab.o /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/naeet.o /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/naect.o /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/naedhs.o /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/config.o  -lserver11 -lodm11 -lcell11 -lnnet11 -lskgxp11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11  -lvsn11 -lcommon11 -lgeneric11 -lknlopt `if /usr/bin/ar tv /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap11" ; fi` -lslax11 -lpls11  -lrt -lplp11 -lserver11 -lclient11  -lvsn11 -lcommon11 -lgeneric11 `if [ -f /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/libavserver11.a ] ; then echo "-lavserver11" ; else echo "-lavstub11"; fi` `if [ -f /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/libavclient11.a ] ; then echo "-lavclient11" ; fi` -lknlopt -lslax11 -lpls11  -lrt -lplp11 -ljavavm11 -lserver11  -lwwg  `cat /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lmm -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lztkg11 `cat /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `if /usr/bin/ar tv /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo11"; fi` -L/u01/app/oracle/product/11.2.0.4/EBSGOLD/ctx/lib/ -lctxc11 -lctx11 -lzx11 -lgx11 -lctx11 -lzx11 -lgx11 -lordimt11 -lclsra11 -ldbcfg11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11 -locr11 -locrb11 -locrutl11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11  -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged  -lippsmerged -lippcore  -lippcpemerged -lippcpmerged  -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lsnls11 -lunls11  -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lasmclnt11 -lcommon11 -lcore11 -laio    `cat /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/11.2.0.4/EBSGOLD/lib -lm    `cat /u01/app/oracle/product/11.2.0.4/EBSGOLD/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/11.2.0.4/EBSGOLD/lib

test ! -f /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin/oracle ||\

           mv -f /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin/oracle /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin/oracleO

mv /u01/app/oracle/product/11.2.0.4/EBSGOLD/rdbms/lib/oracle /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin/oracle

chmod 6751 /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin/oracle

[oragold@HOST01 lib]$ /u01/app/oracle/product/11.2.0.4/EBSGOLD/bin/skgxpinfo

rds

[oragold@HOST01 lib]$

Regards,

Mallik

Exadata - RAC TFA Collector - TFA with Database Support Tools Bundle

TFA Collector - TFA with Database Support Tools Bundle

Oracle Trace File Analyzer (TFA) provides a number of diagnostic tools in a single bundle, making it easy to gather diagnostic information about the Oracle database and clusterware, which in turn helps with problem resolution when dealing with Oracle Support.

My Oracle Support note 1513912.1 "TFA Collector - Tool for Enhanced Diagnostic Gathering" at https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1513912.1

Trace File Analyzer (TFA) Collector simplifies diagnostic data collection on Oracle Cluster Ready Services (CRS), Oracle Grid Infrastructure (Oracle GI), and Oracle RAC systems. TFA behaves in a similar manner to the ion utility packaged with Oracle Clusterware. Both tools collect and package diagnostic data. However, TFA is much more powerful than ion because TFA centralizes and automates the collection of diagnostic information.

Please refer the below MOS document for more details:
TFA Collector - TFA with Database Support Tools Bundle (Doc ID 1513912.1)

Patch download, unzip and Install/upgrade:

HOST01:(root)-/opt
>mkdir oracle.tfa

HOST01:(root)-/opt
>ls -ltrh

HOST01:(root)-/opt
>cp /u01/patches/TFA/TFA-LINUX_v18.1.1.zip /opt/oracle.tfa/

HOST01:(root)-/opt
>cd /opt/oracle.tfa/

HOST01:(root)-/opt/oracle.tfa
>ls -ltrh
total 172M
-rw-r--r-- 1 root root 172M Apr  5 04:12 TFA-LINUX_v18.1.1.zip

HOST01:(root)-/opt/oracle.tfa
>unzip TFA-LINUX_v18.1.1.zip
Archive:  TFA-LINUX_v18.1.1.zip
  inflating: README.txt
  inflating: installTFA-LINUX
HOST01:(root)-/opt/oracle.tfa

TFA installation:

HOST01:(root)-/opt/oracle.tfa
>./installTFA-LINUX
TFA Installation Log will be written to File : /tmp/tfa_install_385107_2018_04_05-04_15_23.log

Starting TFA installation

TFA Version: 181100 Build Date: 201802010159

TFA HOME : /u01/app/12.2.0.1/grid/tfa/HOST01/tfa_home

Installed Build Version: 122120 Build Date: 201709270025

TFA is already installed. Patching /u01/app/12.2.0.1/grid/tfa/HOST01/tfa_home...
TFA patching CRS or DB from zipfile extracted to /tmp/.385107.tfa
TFA patching typical install from zipfile is written to /u01/app/12.2.0.1/grid/tfa/HOST01/tfapatch.log

TFA will be Patched on:
HOST01
HOST02

Do you want to continue with patching TFA? [Y|N] [Y]:

Checking for ssh equivalency in HOST02
HOST02 is configured for ssh user equivalency for root user

Using SSH to patch TFA to remote nodes :

Applying Patch on HOST02:

TFA_HOME: /u01/app/12.2.0.1/grid/tfa/HOST02/tfa_home
Stopping TFA Support Tools...
Shutting down TFA
oracle-tfa stop/waiting. . . . .
Killing TFA running with pid 101177. . .
Successfully shutdown TFA..
Copying files from HOST01 to HOST02...

Current version of Berkeley DB in  is 5 or higher, so no DbPreUpgrade required
WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.
Moving Properties.bkp to Properties
WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.
WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.
Running commands to fix init.tfa and tfactl in HOST02...
WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.
WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.
WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.
Updating init.tfa in HOST02...
Starting TFA in HOST02...
Starting TFA..
oracle-tfa start/running, process 23827
Waiting up to 100 seconds for TFA to be started... . . . .
Successfully started TFA Process... . . . .
WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.
TFA Started and listening for commands
WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.
WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.

Enabling Access for Non-root Users on HOST02...

Applying Patch on HOST01:

Stopping TFA Support Tools...

Shutting down TFA for Patching...

Shutting down TFA
oracle-tfa stop/waiting. . . . .
Killing TFA running with pid 240432. . .
Successfully shutdown TFA..

No Berkeley DB upgrade required

Copying TFA Certificates...
Moving Properties.bkp to Properties

Running commands to fix init.tfa and tfactl in localhost

Starting TFA in HOST01...

Starting TFA..
oracle-tfa start/running, process 105565
Waiting up to 100 seconds for TFA to be started... . . . .
Successfully started TFA Process... . . . .
TFA Started and listening for commands

Enabling Access for Non-root Users on HOST01...

WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.
WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.
.-------------------------------------------------------------------.
| Host        | TFA Version | TFA Build ID         | Upgrade Status |
+-------------+-------------+----------------------+----------------+
| HOST01 |  18.1.1.0.0 | 18110020180201015951 | UPGRADED       |
| HOST02 |  18.1.1.0.0 | 18110020180201015951 | UPGRADED       |
'-------------+-------------+----------------------+----------------'
HOST01:(root)-/opt/oracle.tfa


TFA post verification:

HOST01:(root)-/u01/app/12.2.0.1/grid/tfa/bin
>./tfactl  print status
.----------------------------------------------------------------------------------------------------.
| Host        | Status of TFA | PID    | Port | Version    | Build ID             | Inventory Status |
+-------------+---------------+--------+------+------------+----------------------+------------------+
| HOST01 | RUNNING       | 105843 | 5000 | 18.1.1.0.0 | 18110020180201015951 | COMPLETE         |
| HOST02 | RUNNING       |  24075 | 5000 | 18.1.1.0.0 | 18110020180201015951  | COMPLETE         |
'-------------+---------------+--------+------+------------+----------------------+------------------'
HOST01:(root)-/u01/app/12.2.0.1/grid/tfa/bin


HOST01:(root)-/u01/app/12.2.0.1/grid/tfa/bin
>./tfactl print repository
.-----------------------------------------------------.
|                     HOST01                     |
+----------------------+------------------------------+
| Repository Parameter | Value                        |
+----------------------+------------------------------+
| Location             | /u01/app/grid/tfa/repository |
| Maximum Size (MB)    | 10240                        |
| Current Size (MB)    | 758                          |
| Free Size (MB)       | 9482                         |
| Status               | OPEN                         |
'----------------------+------------------------------'

.-----------------------------------------------------.
|                     HOST02                     |
+----------------------+------------------------------+
| Repository Parameter | Value                        |
+----------------------+------------------------------+
| Location             | /u01/app/grid/tfa/repository |
| Maximum Size (MB)    | 10240                        |
| Current Size (MB)    | 604                          |
| Free Size (MB)       | 9636                         |
| Status               | OPEN                         |
'----------------------+------------------------------'
HOST01:(root)-/u01/app/12.2.0.1/grid/tfa/bin


TFA collection:

utility: tfactl

Below are the steps to collect TFA:

[root@HOST01]locate tfactl
/u01/app/12.2.0.1/grid/bin/tfactl
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/tfactl
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/tfactl.bat.tmpl
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/tfactl.pl
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/tfactl.tmpl
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/common/tfactlglobal.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/common/tfactlshare.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/common/tfactlwin.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/common/exceptions/tfactlexceptions.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/modules/tfactlaccess.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/modules/tfactladmin.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/modules/tfactlanalyze.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/modules/tfactlbase.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/modules/tfactlcell.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/modules/tfactlcollection.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/modules/tfactldiagcollect.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/modules/tfactldirectory.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/modules/tfactlexttools.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/modules/tfactlips.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/modules/tfactlmineocr.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/bin/modules/tfactlprint.pm
/u01/app/12.2.0.1/grid/suptools/tfa/release/tfa_home/resources/tfactlhelp.xml
/u01/app/12.2.0.1/grid/tfa/bin/tfactl
HOST01:(grid):(+ASM1)- /home/grid

HOST01:(grid)$/u01/app/12.2.0.1/grid/tfa/bin/tfactl diagcollect -all -from "MON/23/2018 06:30:00" -to "MON/23/2018 07:30:00"

WARNING: User 'grid' is not allowed to run Collections on Storage Cells (Run diagcollect as root user to collect files from Storage Cells).

Collecting data for all components using above parameters...
Collecting data for all nodes
Scanning files from Feb/03/2018 04:00:00 to Feb/03/2018 06:30:00
Creating ips package in master node ...
Trying ADR basepath /u01/app/oracle
Trying to use ADR homepath diag/rdbms/obiprod/OBIPROD1 ...
Submitting request to generate package for ADR homepath /u01/app/oracle/diag/rdbms/obiprod/OBIPROD1
Trying to use ADR homepath diag/rdbms/obiprod/OBIPROD ...
Submitting request to generate package for ADR homepath /u01/app/oracle/diag/rdbms/obiprod/OBIPROD
Trying to use ADR homepath diag/rdbms/dbm01/DBM011 ...
Submitting request to generate package for ADR homepath /u01/app/oracle/diag/rdbms/dbm01/DBM011
Trying to use ADR homepath diag/rdbms/ebsprod_delete/EBSPROD1 ...
Submitting request to generate package for ADR homepath /u01/app/oracle/diag/rdbms/ebsprod_delete/EBSPROD1
Trying to use ADR homepath diag/rdbms/obipaos/OBIPAOS1 ...
Submitting request to generate package for ADR homepath /u01/app/oracle/diag/rdbms/obipaos/OBIPAOS1
Trying ADR basepath /u01/app/grid
Trying to use ADR homepath diag/asm/+asm/+ASM1 ...
Submitting request to generate package for ADR homepath /u01/app/grid/diag/asm/+asm/+ASM1
Trying to use ADR homepath diag/crs/HOST01/crs ...
Submitting request to generate package for ADR homepath /u01/app/grid/diag/crs/HOST01/crs
Master package completed for ADR homepath /u01/app/oracle/diag/rdbms/obiprod/OBIPROD1
Master package completed for ADR homepath /u01/app/oracle/diag/rdbms/obiprod/OBIPROD
Master package completed for ADR homepath /u01/app/oracle/diag/rdbms/obipaos/OBIPAOS1
Master package completed for ADR homepath /u01/app/oracle/diag/rdbms/ebsprod_delete/EBSPROD1
Created package 3 based on time range 2018-02-03 04:00:00.000000 -05:00 to 2018-02-03 06:30:00.000000 -05:00, correlation level basic
Master package completed for ADR homepath /u01/app/oracle/diag/rdbms/dbm01/DBM011
Created package 3 based on time range 2018-02-03 04:00:00.000000 -05:00 to 2018-02-03 06:30:00.000000 -05:00, correlation level basic
Master package completed for ADR homepath /u01/app/grid/diag/asm/+asm/+ASM1
Created package 3 based on time range 2018-02-03 04:00:00.000000 -05:00 to 2018-02-03 06:30:00.000000 -05:00, correlation level basic
Master package completed for ADR homepath /u01/app/oracle/diag/rdbms/ebsprod/EBSPROD1
Created package 3 based on time range 2018-02-03 04:00:00.000000 -05:00 to 2018-02-03 06:30:00.000000 -05:00, correlation level basic
Master package completed for ADR homepath /u01/app/grid/diag/crs/HOST01/crs
Created package 3 based on time range 2018-02-03 04:00:00.000000 -05:00 to 2018-02-03 06:30:00.000000 -05:00, correlation level basic
Master package completed for ADR homepath /u01/app/oracle/diag/rdbms/ebsprod/EBSPROD
Created package 3 based on time range 2018-02-03 04:00:00.000000 -05:00 to 2018-02-03 06:30:00.000000 -05:00, correlation level basic
Non matching adrhomepath for diag/rdbms/ebsprod_delete/EBSPROD1 during the creation of ips package in remote node HOST02.
Non matching adrhomepath for diag/rdbms/ebsprod_delete/EBSPROD1 during the creation of ips package in remote node HOST03.
Non matching adrhomepath for diag/rdbms/dbm01/DBM011 during the creation of ips package in remote node HOST02.
Non matching adrhomepath for diag/rdbms/ebsprod/EBSPROD during the creation of ips package in remote node HOST02.
Non matching adrhomepath for diag/rdbms/ebsprod/EBSPROD during the creation of ips package in remote node HOST03.
Non matching adrhomepath for diag/rdbms/ebsprod/EBSPROD1 during the creation of ips package in remote node HOST02.
Non matching adrhomepath for diag/rdbms/ebsprod/EBSPROD1 during the creation of ips package in remote node HOST03.
Remote package completed for ADR homepath(s) /diag/crs/HOST02/crs,/diag/crs/HOST03/crs
Remote package completed for ADR homepath(s) /diag/asm/+asm/+ASM2,/diag/asm/+asm/+ASM3

Collection Id : 20180203063237HOST01

Detailed Logging at : /u01/app/grid/tfa/repository/collection_Sat_Feb_03_06_32_37_EST_2018_node_all/diagcollect_20180203063237_HOST01.log
2018/02/03 06:33:30 EST : Collection Name : tfa_Sat_Feb_03_06_32_37_EST_2018.zip
2018/02/03 06:33:30 EST : Collecting diagnostics from hosts : [HOST03, HOST01, HOST02]
2018/02/03 06:33:30 EST : Scanning of files for Collection in progress...
2018/02/03 06:33:30 EST : Collecting additional diagnostic information...
2018/02/03 06:39:37 EST : Completed collection of additional diagnostic information...
2018/02/03 06:42:30 EST : Getting list of files satisfying time range [02/03/2018 04:00:00 EST, 02/03/2018 06:30:00 EST]
2018/02/03 06:42:47 EST : Collecting ADR incident files...
2018/02/03 06:42:48 EST : Completed Local Collection
2018/02/03 06:42:48 EST : Remote Collection in Progress...
.--------------------------------------.
|          Collection Summary          |
+------------+-----------+------+------+
| Host       | Status    | Size | Time |
+------------+-----------+------+------+
| HOST02 | Completed | 98MB | 297s |
| HOST03 | Completed | 91MB | 355s |
| HOST01 | Completed | 70MB | 558s |
'------------+-----------+------+------'

Logs are being collected to: /u01/app/grid/tfa/repository/collection_Sat_Feb_03_06_32_37_EST_2018_node_all
/u01/app/grid/tfa/repository/collection_Sat_Feb_03_06_32_37_EST_2018_node_all/HOST01.tfa_Sat_Feb_03_06_32_37_EST_2018.zip
/u01/app/grid/tfa/repository/collection_Sat_Feb_03_06_32_37_EST_2018_node_all/HOST03.tfa_Sat_Feb_03_06_32_37_EST_2018.zip
/u01/app/grid/tfa/repository/collection_Sat_Feb_03_06_32_37_EST_2018_node_all/HOST02.tfa_Sat_Feb_03_06_32_37_EST_2018.zip
HOST01:(grid):(+ASM1)- /home/grid


[root@na2drdbadm01 ~]# /u01/app/12.2.0.1/grid/tfa/bin/tfactl diagcollect -all

WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.
The -all switch is being deprecated as collection of all components is the default behavior. TFA will continue to collect all components.

Collecting data for the last 12 hours for all components...
Collecting data for all nodes and cells
Creating ips package in master node ...
Trying ADR basepath /u01/app/oracle
Trying to use ADR homepath diag/rdbms/dbm01/dbm011 ...
Submitting request to generate package for ADR homepath /u01/app/oracle/diag/rdbms/dbm01/dbm011
Trying to use ADR homepath diag/rdbms/obixprod/DROBIXP ...
Submitting request to generate package for ADR homepath /u01/app/oracle/diag/rdbms/obixprod/DROBIXP
Trying to use ADR homepath diag/rdbms/drobixp/DROBIXP ...
Submitting request to generate package for ADR homepath /u01/app/oracle/diag/rdbms/drobixp/DROBIXP
Trying to use ADR homepath diag/rdbms/drebsxp/DREBSXP ...
Submitting request to generate package for ADR homepath /u01/app/oracle/diag/rdbms/drebsxp/DREBSXP
Trying ADR basepath /u01/app/grid
Trying to use ADR homepath diag/crs/na2drdbadm01/crs ...
Submitting request to generate package for ADR homepath /u01/app/grid/diag/crs/na2drdbadm01/crs
Collection Id : 20180405093216HOST03

Detailed Logging at : /u01/app/grid/tfa/repository/collection_Thu_Apr_05_09_32_16_EDT_2018_node_all/diagcollect_20180405093216_HOST03.log
2018/04/05 09:32:53 EDT : NOTE : Any file or directory name containing the string .com will be renamed to replace .com with dotcom
2018/04/05 09:32:53 EDT : Collection Name : tfa_Thu_Apr_05_09_32_16_EDT_2018.zip
2018/04/05 09:32:53 EDT : Collecting diagnostics from hosts : [HOST03, HOST01, HOST02]
2018/04/05 09:32:53 EDT : Scanning of files for Collection in progress...
2018/04/05 09:32:53 EDT : Collecting additional diagnostic information...
2018/04/05 09:33:03 EDT : Getting list of files satisfying time range [04/04/2018 03:51:00 EDT, 04/04/2018 06:00:00 EDT]
2018/04/05 09:33:19 EDT : Collecting ADR incident files...
2018/04/05 09:38:01 EDT : Completed collection of additional diagnostic information...
2018/04/05 09:38:06 EDT : Completed Local Collection
2018/04/05 09:38:06 EDT : Remote Collection in Progress...
.--------------------------------------.
|          Collection Summary          |
+------------+-----------+------+------+
| Host       | Status    | Size | Time |
+------------+-----------+------+------+
| HOST01 | Completed | 95MB | 311s |
| HOST02 | Completed | 80MB | 311s |
| HOST03 | Completed | 79MB | 313s |
'------------+-----------+------+------'
Logs are being collected to: /u01/app/grid/tfa/repository/collection_Thu_Apr_05_09_32_16_EDT_2018_node_all
/u01/app/grid/tfa/repository/collection_Thu_Apr_05_09_32_16_EDT_2018_node_all/HOST01.tfa_Thu_Apr_05_09_32_16_EDT_2018.zip
/u01/app/grid/tfa/repository/collection_Thu_Apr_05_09_32_16_EDT_2018_node_all/HOST02.tfa_Thu_Apr_05_09_32_16_EDT_2018.zip
/u01/app/grid/tfa/repository/collection_Thu_Apr_05_09_32_16_EDT_2018_node_all/HOST03.tfa_Thu_Apr_05_09_32_16_EDT_2018.zip
[root@HOST03 ~]$

Please do follow me and support me on,

LinkedIn: https://www.linkedin.com/in/mallik034
YouTube: https://www.youtube.com/c/mallik034
Fakebook: https://www.facebook.com/mallik034
Blog: https://mallik034.blogspot.com
twitter: https://twitter.com/mallik034
Instagram: https://www.instagram.com/mallik034
Facebook Page: https://www.facebook.com/mallik034oracledba
Telegram Group: @mallik034

Regards,
Mallikarjun Ramadurg
Mobile: +91 9880616848
WhatsApp: +91 9880616848

Email: malluramadurg@gmail.com
LinkedIn: https://www.linkedin.com/in/mallik034
YouTube: https://www.youtube.com/c/mallik034
Fakebook: https://www.facebook.com/mallik034
Blog: https://mallik034.blogspot.com
twitter: https://twitter.com/mallik034
Instagram: https://www.instagram.com/mallik034
Facebook Page: https://www.facebook.com/mallik034oracledba
Telegram Group: @mallik034
Skype: @malluramadurg

Exadata - How To Collect Sosreport on Oracle Linux?

How To Collect Sosreport on Oracle Linux?

When you are working with Oracle support engineer on OS issue, Most commonly asked report from oracle support engineer is sosreport.

What is sosreport?

“The “sosreport” is a tool to collect troubleshooting data on an Oracle Linux system. It generates a compressed zip of debugging information that gives an overview of the most important logs and configuration of a Linux system.

The sosreport includes information about the installed rpm versions, syslog, network configuration, mounted filesystems, disk partition details, loaded kernel modules and status of all services

Please refer the below MOS document for more details:
How To Collect Sosreport on Oracle Linux (Doc ID 1500235.1)

[root@HOST03 ~]# locate sosreport
/etc/selinux/targeted/modules/active/modules/sosreport.pp
/opt/oracle.cellos/validations/init.d/sosreport
/usr/lib/python2.6/site-packages/sos/sosreport.py
/usr/lib/python2.6/site-packages/sos/sosreport.pyc
/usr/lib/python2.6/site-packages/sos/sosreport.pyo
/usr/sbin/sosreport
/usr/share/man/man1/sosreport.1.gz
/usr/share/selinux/devel/include/system/sosreport.if
/usr/share/selinux/targeted/sosreport.pp.bz2
[root@HOST03 ~]#

[root@HOST03 ~]# sosreport 

sosreport (version 3.2)

This command will collect diagnostic and configuration information from
this Oracle Linux system and installed applications.

An archive containing the collected information will be generated in
/tmp/sos.BUdkox and may be provided to a Oracle USA support
representative.

Any information provided to Oracle USA will be treated in accordance
with the published support policies at:

  http://linux.oracle.com/

The generated archive may contain data considered sensitive and its
content should be reviewed by the originating organization before being
passed to any third party.

No changes will be made to system configuration.

Press ENTER to continue, or CTRL-C to quit.

Please enter your first initial and last name [HOST03.r02.xlgs.local]: 
Please enter the case id that you are generating this report for []: 3-20516635001

 Setting up archive ...
 Setting up plugins ...
 Running plugins. Please wait ...

  Running 75/75: yum...                      
Creating compressed archive...

Your sosreport has been generated and saved in:
  /tmp/sosreport-HOST03.r02.xlgs.local.3-20516635001-20190711082835.tar.xz

The checksum is: b721f41c5d69454c92915b1168a0c3e7

Please send this file to your support representative.

[root@HOST03 ~]# 

[root@HOST03 ~]# ls -ltrh /tmp/sosreport-HOST03.r02.xlgs.local.3-20516635001-20190711082835.tar.xz
-rw------- 1 root root 15M Jul 11 08:29 /tmp/sosreport-HOST03.r02.xlgs.local.3-20516635001-20190711082835.tar.xz
[root@HOST03 ~]# 

Regards,

Mallik

Exadata - What is Exadata sundiag or sundiag.sh - Collecting sundiag Information

What is Exadata sundiag or sundiag.sh 

Oracle Exadata Diagnostics Collection Tool sundiag.sh

Very often when creating a Support Request (SR) for an issue on an Oracle Exadata Database Machine, you’ll need to run the script “sundiag.sh“.  

Which is the “Oracle Exadata Database Machine – Diagnostics Collection Tool“.

The tool collects a lot of diagnostics information that assist the support analyst in diagnosing your problem, such as failed hardware like a failed disk, etc.

More information Please refer the below MOS document:

SRDC – EEST Sundiag (Doc ID 1683842.1)

Oracle Exadata Diagnostic Information required for Disk Failures and some other Hardware issues (Doc ID 761868.1)

Below is the technical steps for running or collecting sundiag report.

[root@HOST1]# locate sundiag.sh
/opt/oracle.SupportTools/sundiag.sh

 [root@HOST1]#/opt/oracle.SupportTools/sundiag.sh
Oracle Exadata Database Machine - Diagnostics Collection Tool
Gathering Linux information

error: "Input/output error" reading key "net.ipv6.conf.all.stable_secret"
error: "Input/output error" reading key "net.ipv6.conf.bond0.stable_secret"
error: "Input/output error" reading key "net.ipv6.conf.bondeth0.stable_secret"
error: "Input/output error" reading key "net.ipv6.conf.bondeth1.stable_secret"
error: "Input/output error" reading key "net.ipv6.conf.default.stable_secret"
error: "Input/output error" reading key "net.ipv6.conf.eth0.stable_secret"
error: "Input/output error" reading key "net.ipv6.conf.eth1.stable_secret"
error: "Input/output error" reading key "net.ipv6.conf.eth2.stable_secret"
error: "Input/output error" reading key "net.ipv6.conf.eth3.stable_secret"
error: "Input/output error" reading key "net.ipv6.conf.eth4.stable_secret"
error: "Input/output error" reading key "net.ipv6.conf.eth5.stable_secret"
error: "Input/output error" reading key "net.ipv6.conf.ib0.stable_secret"
error: "Input/output error" reading key "net.ipv6.conf.ib1.stable_secret"
error: "Input/output error" reading key "net.ipv6.conf.lo.stable_secret"
Skipping collection of OSWatcher/ExaWatcher logs, Cell Metrics and Traces
Skipping ILOM collection. Use the ilom or snapshot options, or login to ILOM
over the network and run Snapshot separately if necessary.

/var/log/exadatatmp/sundiag_pexdbadm01_1707NM10AX_2018_02_04_01_04
Gathering dbms information
Generating diagnostics tarball and removing temp directory
==============================================================================
Done. The report files are bzip2 compressed in /var/log/exadatatmp/sundiag_pexdbadm01_1707NM10AX_2018_02_04_01_04.tar.bz2
==============================================================================
[root@HOST1]#

Regards,

Mallik

Exadata - HCC for Exadata Servers (HCC)

HCC for Exadata servers ( HCC)

What is HCC?

Hybrid Columnar Compression on Exadata enables the highest levels of data compression and provides enterprises with tremendous cost-savings and performance improvements due to reduced I/O. HCC is optimized to use both database and storage capabilities on Exadata to deliver tremendous space savings AND revolutionary performance. Average storage savings can range from 10x to 15x depending on which Hybrid Columnar Compression level is implemented – real world customer benchmarks have resulted in storage savings of up to 204x

HCC Technology overview

Oracle’s Hybrid Columnar Compression technology is a new method for organizing data within a database block. As the name implies, this technology utilizes a combination of both row and columnar methods for storing data. This hybrid approach achieves the compression benefits of columnar storage, while avoiding the performance shortfalls of a pure columnar format. 

A logical construct called the compression unit is used to store a set of hybrid columnar compressed rows. When data is loaded, column values for a set of rows are grouped together and compressed. After the column data for a set of rows has been compressed, it is stored in a compression unit.

4 different type of HCC available are 

Query low
Query high
Archive low
Archive High

Please run reports for all 4 type of compression using script mentioned on TECH_STEP1 by mentioned any one of the below compression which will give you compression ratio.

Query low 
Query high 
Archive low 
Archive High


Technical details on gather statistic ratio and enabling HCC

TECH_STEP1: compression_stats

Compression_Stats_Script for TRANSACTIONS_STG table which will actual tells you how much compression it is going to give you.

TRANSACTIONS_STG_Stats_Query_Low.sql

spool TRANSACTIONS_STG_Query_Low.log
select name from v$database ;
set serveroutput on;
set time on
set timing on
select to_char(sysdate,'DD-MM-YYYY:HH24:MM:SS') time_now from dual;
declare
 v_blkcnt_cmp     pls_integer;
 v_blkcnt_uncmp   pls_integer;
 v_row_cmp        pls_integer;
 v_row_uncmp      pls_integer;
 v_cmp_ratio      number;
 v_comptype_str   varchar2(60);
begin
 dbms_compression.get_compression_ratio(
 scratchtbsname   => 'XXLGARCH',                          -- Tablespace Name
 ownname          => 'XXLGARCH',                          -- USER NAME
 tabname          => 'TRANSACTIONS_STG',          -- TABLE NAME
 partname         => NULL,
 comptype         => dbms_compression.comp_for_query_low, --compression type
 blkcnt_cmp       => v_blkcnt_cmp,
 blkcnt_uncmp     => v_blkcnt_uncmp,
 row_cmp          => v_row_cmp,
 row_uncmp        => v_row_uncmp,
 cmp_ratio        => v_cmp_ratio,
 comptype_str     => v_comptype_str);
 dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));
 dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));
 dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));
end;
/
select to_char(sysdate,'DD-MM-YYYY:HH24:MM:SS') time_now from dual;
spool off;
exit;

TECH_STEP2: compression_stats_script log

Below is the log collected from the above compression stat script. Which will give you compression prediction ration

XLG_AED_TRANSACTIONS_STG_Query_Low.log 

NAME                                                                            
---------                                                                       
TESTDB                                                                         


TIME_NOW                                                                        
-------------------                                                             
13-12-2017:01:12:16                                                             

Elapsed: 00:00:00.00
Compression Advisor self-check validation successful. select count(*) on both   
Uncompressed and EHCC Compressed format = 1000001 rows                          
Estimated Compression Ratio: 5.5 ----------- Compression Ratio                                    
Blocks used by compressed sample: 23527                                         
Blocks used by uncompressed sample: 130140                                      

PL/SQL procedure successfully completed.

Elapsed: 00:12:50.58

TIME_NOW                                                                        
-------------------                                                             
13-12-2017:01:12:07                                                             

Elapsed: 00:00:00.00


TECH_STEP3: Actual compression_scripts

Actual compression_scripts for TRANSACTIONS_STG table, below is the script which will do actual compression on the table.

TRANSACTIONS_STG_Compress_archive_low.sql 

set serveroutput on;
spool TRANSACTIONS_STG_Compress_archive_low.log
set timing on

select name from v$database;

col SEGMENT_NAME format a40
select segment_name, round(sum(bytes)/1024/1024/1024,2) total_space_GB  from dba_segments  where  owner='XXLGARCH' and segment_name='TRANSACTIONS_STG' 
group by segment_name;

select to_char(sysdate,'DD-MM-YYYY:HH:MI:SS') from dual;

alter table XXLGARCH.TRANSACTIONS_STG move compress for archive low;

select to_char(sysdate,'DD-MM-YYYY:HH:MI:SS') from dual;

col SEGMENT_NAME format a40
select segment_name, round(sum(bytes)/1024/1024/1024,2) total_space_GB  from dba_segments  where  owner='XXLGARCH' and segment_name='TRANSACTIONS_STG' 
group by segment_name;

set lines 300
select owner,table_name,index_name from dba_indexes where status='UNUSABLE';

spool off
exit;

Regards,

Mallik