Finding Oracle allocated semaphores in Linux...

As I always say, there is always something to know/learn.

Having some issues with high usage of semaphores in our environment and digging more found this nice utility from Oracle (yeah its been there since 8.1 but today I came to know about it) called "sysresv".

If you have access to metalink then here is the ID#123322.1

BTW, you can still get all semaphores allocated by oracle with a simple command:
ipcs -s |grep oracle --This will list out all semaphores allocated for all instances on the server.

sysresv utility gives the list of semaphores only for the instance that you pointed to...

Once you get the semaphore id (semid) try below to get more info on that semid like process id (pid):
ipcs -si semid

This will give detailed info like below:


Semaphore Array semid=331382805
uid=80   gid=80  cuid=80         cgid=80
mode=0660, access_perms=0660
nsems = 128
otime = Tue Feb  5 16:45:46 2013
ctime = Tue Feb  5 16:45:46 2013
semnum     value      ncount     zcount     pid
0                 1          0             0          1315
1             18380      0             0          1315
2             18703      0             0          1315

From the above try to ps -ef |grep pid and see if this shows any OS process that is related to Oracle (in our case). If ps -ef does not return anything then that is a dead process and time clear that semaphore allocation using ipcrm -s semid.

Here is the info about this nice utility if you do not have metalink access:


SYSRESV Utility [ID 123322.1]

The sysresv utility included with Oracle 8.1.5 and above provides instance
status (and OS resources used) for specified ORACLE_SIDs.  This utility is
especially useful when multiple instances are running.  OS resources can be
removed using this utility if the specified instance is detected to be dead.

This utility may be useful when an instance has crashed or was aborted
and memory and semaphores related to this instance were not cleaned up
automatically.  This utility is also helpful in determining which instance
is running.

The sysresv utility, located in $O_H/bin, can be used from locations other
than $O_H/bin.

Point your environment to the instance of interest before using sysresv.

Usage:
------

sysresv:
usage   : sysresv [-if] [-d ] [-l sid1 ...]
          -i : Prompt before removing ipc resources for each sid
          -f : Remove ipc resources silently, oevrrides -i option
          -d : List ipc resources for each sid if on
          -l sid1 .. : 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 "X816" :
   Shared Memory:
   ID              KEY
   16437           0xe4efa8dc
   Semaphores:
   ID              KEY
   12320802        0x09d48346
   Oracle Instance alive for sid "X816"


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 "X816" :
   Shared Memory:
   ID              KEY
   16437           0xe4efa8dc
   Semaphores:
   ID              KEY
   12320802        0x09d48346
   Oracle Instance alive for sid "X816"
   SYSRESV-005: Warning
           Instance maybe alive - aborting remove for sid "X816"


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 "X816" :
   Shared Memory:
   ID              KEY
   16837           0xe4efa8dc
   Semaphores:
   ID              KEY
   12714018        0x09d48346
   Oracle Instance not alive for sid "X816"


o  Removing IPC resources using sysresv:

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

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "X816"
   Remove ipc resources for sid "X816" (y/n)?y
   Done removing ipc resources for sid "X816"
   /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 "X816" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "X816"

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

   % ipcrm -m

   Where 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

   where is the semaphore id shown in the sysresv output.

   Example:
   % ipcrm -s 12320802

Faster way to get a row count on a huge table...

We all know how to get a row count of a table:
select count(1)from table_name;

What if the table is so huge!!!
Quick way we generally tend to go around to get the count is from user_tables data dictionary NUM_ROWS column value... wait... that wont give you the accurate number of rows!!! meaning that value is updated in that dictionary when the stats were generated...

Gathering stats to get that value populated is an option? depends on how big the table how long that stats gathering takes place.

So, is there any other way to get number of records faster in a table!!!

Yes...

There is a data dictionary named all_tab_modifications. You can read more about this from the link below (from where i learned this):

http://mwidlake.wordpress.com/2009/05/26/counting-the-cost-4-the-speedy-way/

Basically, once the insert opeartion is done you have to flush the statistics (which is what Oracle does every often) as below:

exec dbms_stats.flush_database_monitoring_info

Once the flush is done you can query all_tab_modifications table for the results:

select dbta.owner||'.'||dbta.table_name tab_name ,dbta.num_rows anlyzd_rows,
       to_char(dbta.last_analyzed,'yymmdd hh24:mi:ss')  last_anlzd,
  nvl(dbta.num_rows,0)+nvl(dtm.inserts,0) - nvl(dtm.deletes,0) tot_rows,
  nvl(dtm.inserts,0)+nvl(dtm.deletes,0)+nvl(dtm.updates,0) chngs,
  nvl(dtm.inserts,0)+nvl(dtm.deletes,0)+nvl(dtm.updates,0)) / greatest(nvl(dbta.num_rows,0),1) pct_c,
  dtm.truncated trn
  from dba_tables dbta
  -- replace below with all_tab_modifications if you need
       left outer join sys.dba_tab_modifications dtm
  on dbta.owner = dtm.table_owner
     and dbta.table_name = dtm.table_name
          and dtm.partition_name is null
 where dbta.table_name ='PERSON'
   and dbta.owner     ='TRAINER'
/

OPatch failed with error code 135

OK, I am in process of upgrading one of our 11.2.0.2 RAC Environment to 11.2.0.3 and as a pre-req I have to install a patch 12539000.

First we need to create OCM Response file which is mandatory so I created the file under "/opt/grid/app/11.2.0/grid/OPatch/ocm/bin" using emocmrsp from the grid home OPatch/ocm/bin folder.

Now, when I try to apply a patch using "opatch auto" it asks me:
"OPatch  is bundled with OCM, Enter the absolute OCM response file path:"

As a response I gave the path "/opt/grid/app/11.2.0/grid/OPatch/ocm/bin" where the response file was created.

Now, opatch comes back saying "apply failed for home"

Looking into the log, I see the following:


Argument(s) Error... Given 'ocmrf' file is a directory and not a file.
Please check the arguments and try again.
 OPatch failed with error code 135

The problem is that when it asks for OCM Response file path, it is actually expecting the file name also in the path like this "/opt/grid/app/11.2.0/grid/OPatch/ocm/bin/ocm.rsp". So, try again with that path including the file name and you will be fine applying a patch...

What I do not understand here is:

emocmrsp command does not asks for the file name and it creates a standard file named "ocm.rsp" so, why is OPatch needed the file name in the path!!!

Anyways, just thought of blogging....