techblog 

Recently we had an experience of a completely hung Oracle instance. This was a RAC database running version 10.2.0.3, only 1 instance in the 2 node cluster was affected, but there was no way of logging onto the affected instance either remotely or even as sysdba locally. Perhaps, we should have done a more thorough root cause analysis to find out why the system was hung using some of the techniques from this excerpt. I had never heard of the -prelim option to sqlplus before and it may have been interesting to perform a systemstate dump, but this was a busy production system and we had a really good idea what had caused it and just wanted to get back up and running again.

The system had been running along fine, until a colleague was examining an issue with a 3rd party piece of software and was using DBMS_MONITOR to obtain trace information of the connecting session. He noticed that every time he traced a connection from this application, it would suddenly make a new connection, which he then promptly would run DBMS_MONITOR on. This went on for around 6 sessions until eventually the instance hung to new connections and the helpdesk phones started to ring.

It was pretty clear we had to stop tracing ASAP, and right enough, once we had worked out which pid’s to kill server side the instance returned back to normal, just killing the sqlplus sessions on the client side was not actually doing the trick. Thankfully DBMS_MONITOR puts the pid number into the filename it uses to output the trace data to. However another way of obtaining which processes to kill would have been to run the following (we knew the traces were being run remotely from a specific client) on the still working node:

SQL> select machine, process
from gv$session;

It seems you can even kill a session running on a different node in a RAC cluster, so we probably could have even tried doing this via the sid, serial# information available from gv$session.

It turns out there is a bug with DBMS_MONITOR in Oracle 10.2.0.3, it is Bug 5485914 available in metalink, the notes for this state it may cause the instance to run slowly/hang. There is a patch available, also hopefully fixed in the imminent 10.2.0.4. I’m quite convinced it was the multiple times DBMS_MONITOR was running that caused the instance hang, so if you are on 10.2.0.3 be careful how many sessions you monitor at once.

This is really just a quick post about the cause of the Oracle error CRS-0213 which I encountered recently and found while doing a google search as well as looking this error up on metalink that there was very little information about this error out there, and nothing that actually helped me solve the problem.

I encountered this error as I was upgrading a RAC database from 10.2.0.3 to 11.1.0.6 and I was attempting to ensure CRS used the new ORACLE_HOME environment, i.e. the 11g one rather than the old 10g one. To do this for the listener you do the following:

/opt/oracle/product/crs/bin/srvctl remove listener -n nodename

/opt/oracle/product/crs/bin/srvctl add listener -n nodename -o NEW ORACLE_HOME

Note this is using the CRS_HOME rather than the ORACLE_HOME. However, when I ran the add part I encountered the following error:

[oracle@linuxrac1 ~]$ /opt/oracle/product/crs/bin/srvctl add listener -n linuxrac1 -o /opt/oracle/product/11.1.0
CRS-0213: Could not register resource 'ora.linuxrac1.LISTENER_LINUXRAC1.lsnr'.

Now the solution to this problem is not exactly earth shattering, but as I said I thought I would blog it anyway as CRS-0213 is not all well known and it might help someone out of a hole. We had the incorrect ORACLE_HOME, in true OFA style I included db_1 onto the end of the 11g ORACLE_HOME, so the above should read -o /opt/oracle/product/11.1.0/db_1. Running with the correct home works fine.

With having 3 separate Oracle software directories now, one for CRS, one for ASM and one for the RDBMS instance, I highly recommend you all look at implementing OFA if you do not already do so!

Some time you may have a requirement to copy an ORACLE_HOME from one location to another using OS level commands. However, one thing this will not do is update the Oracle Inventory. Having an incomplete Inventory will screw up any attempts at subsequent patching of this cloned ORACLE_HOME.

oracle@bdb ~]$ opatch lsinventory
Invoking OPatch 10.2.0.4.2

Oracle Interim Patch Installer version 10.2.0.4.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.

Oracle Home       : /opt/oracle/product/10.2.0.2
Central Inventory : /home/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.2
OUI version       : 10.2.0.2.0
OUI location      : /opt/oracle/product/10.2.0.2/oui
Log file location : /opt/oracle/product/10.2.0.2/cfgtoollogs/opatch/opatch2008-02-08_11-58-44AM.log

List of Homes on this system:

  Home name= OraDb10g_home1, Location= "/opt/oracle/product/10.2.0"
  Home name= OraDb10g_home2, Location= "/opt/oracle/product/11.1.0"
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

Oracle clearly support the ability to clone an ORACLE_HOME as the oui installer has an option to attach a cloned home to the Inventory:

 runInstaller -attachHome ORACLE_HOME="/opt/oracle/product/10.2.0.2" ORACLE_HOME_NAME="ORADB_102"

After you have attached the cloned home you can quite happily apply interim patches and patchsets to your newly cloned ORACLE_HOME.