Gather Statistics

 

Oracle Gather Stat 

Gather Table, Index and Schema Statistics

 

DBMS_STATS.GATHER_TABLE_STATS is used to gather stats for a single table

EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES');

EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES',cascade=>TRUE);

( Note: Cascade gathers Index stats associated with the table )

How to check RMAN progress?

Check RMAN job progress 

The below query can be used to identify the progress of any rman job. Either backup or restore.

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

set line 200;

set pages 500;

set long 5000;

select sl.sid, sl.opname,

to_char(100*(sofar/totalwork), '990.9')||'%' pct_done,

sysdate+(TIME_REMAINING/60/60/24) done_by

from v$session_longops sl, v$session s

where sl.sid = s.sid

and sl.serial# = s.serial#

and sl.sid in (select sid from v$session where module like 'backup%' or module like 'restore%' or module like 'rman%')

and sofar != totalwork

and totalwork > 0

/

Basic ASM Commands

Most commonly used ASM Commands

Failed to start ASMB (connection failed) state=0x1

Issue with Oracle ASM

OPatch Failed with error code 73

Oracle OPatch  Error

Error:

Oracle Home       : <ORACLE_HOME> Path 

Central Inventory : The inventory that is being used by this opatch session

   from           : The inventory that is stored in the Oracle Home

OPatch version    : 12.2.0.1.44

OUI version       : 12.2.0.7.0

Log file location : /u01/oracle/product/19.3.0/cfgtoollogs/opatch/opatch<date>.log

OPatchSession cannot load inventory for the given Oracle Home <oracle_home>. Possible causes are:

   No read or write permission to ORACLE_HOME/.patch_storage

   Central Inventory is locked by another OUI instance

   No read permission to Central Inventory

   The lock file exists in ORACLE_HOME/.patch_storage

   The Oracle Home does not exist in Central Inventory

UtilSession failed: OPatch failed to locate Central Inventory.

Possible causes are:

    The Central Inventory is corrupted

    The oraInst.loc file specified is not valid.

This is because, sometime the /etc/oraInst.loc is pointing to the ora inventory in a temp location which the $ORACLE_HOME/oraInst.loc is not aware.

cat /etc/oraInst.loc

cat $ORACLE_HOME/oraInst.loc

check if both the files are pointing to the same location. if not, take a backup of the file and update the central inventory in $ORACLE_HOME/oraInst.loc.

cp -p $ORACLE_HOME/oraInst.loc $ORACLE_HOME/oraInst.loc.old

Modify the file accordingly for parameter inventory_loc=<Full PATH of the inventory, which can be copied from /etc/oraInst.loc>

Note: One more observation on the file permission, if you are running the grid with different usernames, the same group and sharing the same inventory, make sure, the files under /u01/oraInventory/ContentsXML have 644 permissions. So that while doing patching with Oracle users, it can modify those XML files.

Hope this helps resolve the error, happy patching!!

Best Wishes!!

OPatch Session Hung Forever | CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX

Performance Issue with OPatch

Opatch Hung forever

CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX

If you ever face issues with opatch utility is taking long time to run the prerequisite check and in the log you see the below line.

CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX
CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX
CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX
CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX
CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX

It is time to cleanup some inactive patches from opatch inventory, to do that please follow the below instructions.

cd $ORACLE_HOME/OPatch

--This will list the inactive patches

./opatch util listorderedinactivepatches

--This will remove the inactive patches

./opatch util deleteinactivepatches

This will take some time depending on how many old inactive patches is present in the inventory.

Reference:

OPatch 12.2.0.1.37+ Introduces a New Feature to Delete Inactive Patches in the ORACLE_HOME/.patch_storage Directory (Doc ID 2942102.1)