ORA-29702 - Starting RAC instance in non-rac mode

Assume the following situation: Due to some OS or network related problem you are not able to start-up the CRS (this does happen!) before it impacts the database availability and subsequently your business. Your primary goal now is to make the database available as soon as possible irrespective of its mode (RAC or Non-RAC). The reason being you would like to reduce the impact to your business. If you attempt to startup the instance without CRS, you get the following error:

SQL> startup;
ORA-29702: error occurred in Cluster Group Service operation
SQL>

This error message indicates that it can't start the instance since CRS is not available. Here is the quick work-around to startup the instance. The option is to remove RAC option from your binaries and start the instance. Once you have resolved the CRS related issues, you can turn-on the RAC option back (of course, your instance needs to be down while to turn-on or turn-off the options). Here is how we turn-off:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk rac_off ioracle

Set cluster_database=false

You should now be able to startup the instance without CRS stack being up and running.

SQL> Select * from v$option where parameter like 'Real%';

PARAMETER VALUE
---------------------------------------- ----------
Real Application Clusters FALSE
Real Application Testing TRUE

Turn-on the RAC option with:

make -f ins_rdbms.mk rac_on ioracle

Alternatively, you can install a non-rac oracle binaries to quickly start the instance.


By the way, you turn off other options in similar way such as Partitioning (part_on/part_off), DB Vault (dv_on/dv_off) etc..

Oracle11g - Killing session in RAC (in remote instance)

Prior to Oracle11g, whenever you want to kill a session connected to a non-local (remote) instance, you had to make an explicit connection to that particular instance and then attempt to kill.

Example:

SQL> Select instance_number from v$instance;

INSTANCE_NUMBER
---------------
1 ---> I am connected to instance 1 (Local Instance)

1 row selected.

SQL> Select inst_id, sid, serial# from gv$session where username='CHANDRA';

INST_ID SID SERIAL#
---------- ---------- ----------
2 125 7526 ---> CHANDRA is connected to Inst# 2 (Non-local/remote Instance)

1 row selected.

With 11g, you could kill the session which is connected to instance 2, while you are connected to instance 1:

SQL> Alter system kill session '125,7526,@2' immediate; #--@2 indicates remote instance.

System altered.

SQL> Select inst_id, sid, serial# from gv$session where username='CHANDRA';

no rows selected

This really makes life easy - especially you are using SQL*Plus and not any other front-end tool.