Oracle sysresv utility to release shared memory / semaphores -- No need for ipcs and ipcrm

 


After shutdown of Oracle database , shared memory segments staying behind is known issues   in Linux .  Ideally Dba use Ipcs and Ipcrm  to release  shared  memory segments .


Also there is less known Linux utility pmap 
Eg :  [oracle]$ pmap `pgrep -f lgwr`



There is  Oracle utility sysresv  that can  be used to release shared memory segments and there is no need  to use Ipcs and Ipcrm .  

Eg :  sysresv -l instance1 

 
Usage:
------

sysresv: 
usage   : sysresv [-if] [-d <on/off>] [-l sid1 <sid2> ...]
          -i : Prompt before removing ipc resources for each sid
          -f : Remove ipc resources silently, oevrrides -i option
          -d <on/off> : List ipc resources for each sid if on
          -l sid1 <sid2> .. : apply sysresv to each sid
Default : sysresv -d on -l $ORACLE_SID
Note    : ipc resources are attempted to be deleted for a
          sid only if there is no currently running instance
          with that sid. 


   
Examples:
---------

o  Instance is not running:

   /u02/app/oracle/product/8.1.7> sysresv

   IPC Resources for ORACLE_SID "R817" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "R817" 


o  Instance is running:

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "ORCL" :
   Shared Memory:
   ID              KEY
   16437           0xe4efa8dc
   Semaphores:
   ID              KEY
   12320802        0x09d48346
   Oracle Instance alive for sid "ORCL"


o  Attempting to remove memory and semphores using sysresv when Oracle 
   detects an instance is running:

   /u03/app/oracle/product/8.1.6> sysresv -f

   IPC Resources for ORACLE_SID "ORCL" :
   Shared Memory:
   ID              KEY
   16437           0xe4efa8dc
   Semaphores:
   ID              KEY
   12320802        0x09d48346
   Oracle Instance alive for sid "ORCL"
   SYSRESV-005: Warning
           Instance maybe alive - aborting remove for sid "ORCL" 


o  Removing IPC resources:

   [Sysresv shows memory and semaphores exist but Oracle determines the 
    instance is not alive.  Cleanup is needed.]

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "ORCL" :
   Shared Memory:
   ID              KEY
   16837           0xe4efa8dc
   Semaphores:
   ID              KEY
   12714018        0x09d48346
   Oracle Instance not alive for sid "ORCL" 
 

o  Removing IPC resources using sysresv:

   /u03/app/oracle/product/8.1.6> sysresv -i

   IPC Resources for ORACLE_SID "ORCL" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "ORCL"
   Remove ipc resources for sid "ORCL" (y/n)?y
   Done removing ipc resources for sid "ORCL"
   /u03/app/oracle/product/8.1.6


   Verify the resources were removed:

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "ORCL" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "ORCL"  

     
o  If you need to remove memory segments, and Oracle detects the
   instance is alive through sysresv:

   % ipcrm -m <memid>

   Where <memid> is the memory id shown in the sysresv output.

   Example:
   % ipcrm -m 16437

   If you need to remove semaphores, and Oracle detects the
   instance is alive through sysresv:

   % ipcrm -s <semid>

   where <semid> is the semaphore id shown in the sysresv output.

   Example:
   % ipcrm -s 12320802



Reference :

SYSRESV Utility (Doc ID 123322.1)

Startup fail with ORA-29701 & ORA-29702 after the DB crash. (Doc ID 2160481.1)


Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more