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 

Increase Java Heap memory for OEM

 There are certain times when we observer OEM is performing slow or even unable to startup the Admin Server or OMS Server. We can look into few log files below and understand what is exactly causing the issue. Most of the times I have observed Java Heap issue or memory issue.

Sometime it happens due to an old process holding up the session and consuming the memory, the first step is to kill all the processes and remove some of the lok and temp files from the system.

Known error like:

java.lang.OutOfMemoryError: Java heap space.

Troubleshooting steps:

Kill all the old processes:

ps -ef | grep EMGC_ADMINSERVER

ps -ef | grep EMGC_OMS1

ps -ef | grep java

ps -ef | grep opmn

$kill -9 <PID>

The below temp files can be deleted:

$EM_INSTANCE_BASE/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/tmp/EMGC_ADMINSERVER.lok

$EM_INSTANCE_BASE/user_projects/domains/GCDomain/servers/EMGC_OMS1/data/ldap/ldapfiles/EmbeddedLDAP.lok

$EM_INSTANCE_BASE/user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/EMGC_OMS1.lok

$EM_INSTANCE_BASE/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/data/ldap/ldapfiles/EmbeddedLDAP.lok

$EM_INSTANCE_BASE/user_projects/domains/GCDomain/servers/EMGC_OMS1/data/store/diagnostics/WLS_DIAGNOSTICS000000.DAT

$EM_INSTANCE_BASE/user_projects/domains/GCDomain/servers/EMGC_OMS1/data/store/default/_WLS_EMGC_OMS1000000.DAT

$EM_INSTANCE_BASE/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/data/store/diagnostics/WLS_DIAGNOSTICS000000.DAT

$EM_INSTANCE_BASE/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/data/store/default/_WLS_EMGC_ADMINSERVER000000.DAT

$EM_INSTANCE_BASE/user_projects/domains/GCDomain/config/config.lok

$EM_INSTANCE_BASE/user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/EMGC_OMS1.lok

$EM_INSTANCE_BASE/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/tmp/EMGC_ADMINSERVER.lok

$EM_INSTANCE_BASE/user_projects/domains/GCDomain/servers/EMGC_OMS1/data/nodemanager/*.lck, *.pid, *.state

$EM_INSTANCE_BASE/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/data/nodemanager/*.lck, *.pid, *.state

Check current usage:

grep "JAVA Memory arguments" <OMS_INST>/user_projects/domains/GCDomain/servers/EMGC_OMS1/logs/EMGC_OMS1.out

--> This will give the output of the current OMS heap memory.

emctl get property -name OMS_HEAP_MAX

--> This needs Sysman password 

Fix:

Increase it to the desired value, example 4G.

  <OMS_HOME>/bin/emctl set property -name OMS_HEAP_MAX -value 4096M

Restart the OMS

emctl stop oms -all

emctl start oms

Hope this will fix the slowness and heap memory error.

Best wishes,

 Check progress on expdp and impdp:

In few cases we need to monitor the progress of an export or import job in oracle. below are the steps can be followed to get the details.

Datapump Client:

get the session details: 

SQL> select * from dba_datapump_jobs where state='EXECUTING';

attach the session:

from Command line:

    expdp system/********** attach=SYS_EXPORT_FULL_01

    Export > status

From Database:

Query 1: The % of work done for the running job can be found from this query:

  

set pages 200

set lines 300

col USERNAME for a15

col TARGET for a15

col OPNAME for a20

col "%DONE" for a7

SELECT b.username,
         a.sid,
         b.opname,
         b.target,
         ROUND (b.SOFAR * 100 / b.TOTALWORK, 0) || '%'       AS "%DONE",
         b.TIME_REMAINING,
         TO_CHAR (b.start_time, 'YYYY/MM/DD HH24:MI:SS')     start_time
    FROM v$session_longops b, v$session a
   WHERE a.sid = b.sid
ORDER BY 6;

Query 2: The work done so far and status of the job:

SELECT sl.sid,
       sl.serial#,
       sl.sofar,
       sl.totalwork,
       dp.owner_name,
       dp.state,
       dp.job_mode
  FROM v$session_longops sl, v$datapump_job dp
 WHERE sl.opname = dp.job_name AND sl.sofar != sl.totalwork;

Query 3: The amount of work done so far:

SELECT sl.sid,
       sl.serial#,
       sl.sofar,
       sl.MESSAGE,
       sl.totalwork,
       dp.owner_name,
       dp.state,
       dp.job_mode
  FROM v$session_longops sl, v$datapump_job dp
 WHERE sl.opname = dp.job_name;

Reference:

How To Monitor The Progress Of Datapump Jobs (Doc ID 1471766.1)

Best Wishes!!