Oracle Memory usage Queries

Some Important Oracle database Memory management queries.

SGA usage by Oracle Instance:

select  round(sum(bytes)/1024/1024,2)||' MB' total_sga,round(round(sum(bytes)/1024/1024,2) - round(sum(decode(name,'free memory',bytes,0))/1024/1024,2))||' MB' used,round(sum(decode(name,'free memory',bytes,0))/1024/1024,2)||' MB' free from v$sgastat;

SGA Stat:

SELECT pool, name, bytes / 1024 / 1024 AS "MB"

FROM v$sgastat

WHERE pool IN ('java pool', 'shared pool', 'large pool', 'streams pool')

ORDER BY pool, bytes DESC;

Total SGA:

SELECT sum(bytes)/1024/1024 AS "Total SGA Size (MB)"

FROM v$sgastat;

Using V$PROCESS to see individual process memory usage

SELECT p.spid, p.program, p.pga_max_mem, p.pga_alloc_mem, p.pga_used_mem, p.pga_freeable_mem

FROM v$process p;

Using V$PGASTAT for overall PGA statistics

SELECT name, value

FROM v$pgastat;

 

 

Best Wishes!!! 

Oracle OLR corruption and restoration | CRS-1013 | CRS-6706

 Introduction:

In a RAC configuration, this is pretty common to corrupt the OLR registries under grid. So sometime we need to restore it to make things work again.

 

Error codes:

CRS-1013: The OCR location in an ASM disk group is inaccessible.

WARNING: failed to online diskgroup resource ora.ASM.dg (unable to communicate with CRSD/OHASD)

 ORA-15368: Oracle Cluster Registry operation failed with error 32.

 Running SRDC might show the exact symptoms:
eg: Verifying OLR integrity ...FAILED
Cause: OLR is corrupted

 

Fix:

Login as grid user 

go to OLR Location:

eg
/opt/app/grid/crsdata/<hostname>/olr

 <GI_HOME>/bin/ocrconfig -local -showbackup

This will show you the backups available for the OLR.
 

To restore:

First check the below process is running or not:
ps -ef| grep ohasd.bin

This should return no process, if ohasd.bin is still up and running, stop it on local node:

# <GI_HOME>/bin/crsctl stop crs -f  <========= for GI Cluster 

OR  

# <GI_HOME>/bin/crsctl stop has  <========= for GI Standalone
 

Once it's down, restore with the following command: 

# <GI_HOME>/bin/ocrconfig -local -restore <olr-backup>

(Here consider the most recent available backup file name)

NOTE:
If the command fails, create a dummy OLR, set correct ownership and permission and retry the restoration command:

# cd <OLR location>
# touch <hostname>.olr
# chmod 600 <hostname>.olr
# chown <grid>:<oinstall> <hostname>.olr

Once it's restored, GI can be brought up:

# <GI_HOME>/bin/crsctl start crs   <========= for GI Cluster 

OR  

$ <GI_HOME>/bin/crsctl start has  <========= for GI Standalone, this must be done as grid user.
 

In 12.1 onward, if patches are applied after the OLR is backed up, and later the backup is restored, the patch level will be different and GI won't start with error:

CRS-6706: Oracle Clusterware Release patch level ('1196363452') does not match Software patch level ('600291166'). Oracle Clusterware cannot be started.
To fix the above problem, run as root user:

<GI_HOME>/crs/install/rootcrs.sh -prepatch 
<GI_HOME>/crs/install/rootcrs.sh -postpatch 

In my case, I was facing another issue. So I had to follow some additional steps. the reason behind is that, my OLR was last backed up 6 months ago, and after that I did CPU patching on the database. so the backup OLR file does not have the information of the new CPU patches and we need to update that information to the current OLR file. to do so,

 

As root user unlock the GI home:
cd $ORACLE_HOME
$ORACLE_HOME/crs/install/rootcrs.sh -unlock

Apply the local patch to OLR

$ORACLE_HOME/bin/clscfg -localpatch

Lock the GI home

$ORACLE_HOME/crs/install/rootcrs.sh -lock

Start the clusterware:
crsctl start crs -wait

crsctl status resource -t
 

That it, now the OLR is fixed finally.  

 

Reference:

Oracle Support: How to backup or restore OLR:(KB137305) 

Oracle Support: CRS-6706: Oracle Clusterware Release patch level ('nnn') does not match Software patch level ('mmm') KB140963