AskDba.org Weblog

Yesterday, I faced an interesting scenario while upgrading 2 Node RAC Database. I tried setting up CLUSTER_DATABASE=FALSE in spfile from Node 1,but it displayed value as TRUE after restarting database.Same was true for Database startup entries in Alert log. If I did the same setting in Node 2 and started database from node 2, it started in shared mode.I was using shared spfile (on OCFS) for both the systems.

CLUSTER_DATABASE is a Real Application Clusters parameter that specifies whether or not Real Application Clusters is enabled. It is mostly used for starting database in Exclusive mode during operations which will require updating dictionary. e.g Upgrading database,Enabling Archivelog and also for changing Database characterset.

I had ignored this error  few times but today I wanted to find the cause and resolve it. To diagnose further, I set CLUSTER_DATABASE=FALSE from Node 1 with following command

ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;

Then I used srvctl to start the database

[oracle@blrraclnx1 bdump]$ srvctl start database -d orcl

Node 1 Alert log had CLUSTER_DATABASE=TRUE

cluster_database         = TRUE
cluster_database_instances= 2
db_create_file_dest      = +DATA
db_recovery_file_dest    = +FRA
db_recovery_file_dest_size= 4294967296
thread                   = 1
instance_number          = 1

But on checking Alert Log from Node 2, I found CLUSTER_DATABASE=FALSE

cluster_database         = FALSE
cluster_database_instances= 1
db_create_file_dest      = +DATA
db_recovery_file_dest    = +FRA
db_recovery_file_dest_size= 4294967296
thread                   = 2
instance_number          = 2

Moreover Instance 2 had crashed with following errors

  Tue Sep  2 21:10:14 2008
lmon registered with NM - instance id 2 (internal mem no 1)
 Warning: cluster_database_instances (1) is <= my node id (1)
    This instance wants to mount exclusive when instance 0 has mounted shared.  Exiting!
Tue Sep  2 21:10:15 2008
USER: terminating instance due to error 29707
Instance terminated by USER, pid = 2807

Error says that Instance 0 (Node 1) has already mounted in SHARED Mode and this instance (Node 2) wants to start in Exclusive Mode.

To check the values I decided to use V$SPPARAMETER view. Till now I was using show parameter cluster_database command to check the values.

SQL> SELECT  SID,NAME,VALUE,DISPLAY_VALUE FROM V$SPPARAMETER WHERE NAME='cluster_database';

SID        NAME                           VALUE           DISPLAY_VALUE
---------- ------------------------------ --------------- ---------------
orcl1      cluster_database               TRUE            FALSE
*          cluster_database               FALSE            FALSE

This is strange!! I had never specified any specific value for node 1. Anyways to resolve it, I used reset command to clear the orcl1 entry from spfile

SQL> alter system reset cluster_database sid='orcl1';

System altered.

Then I re-checked the V$SPPARAMETER view

SQL> SELECT SID,NAME,VALUE FROM V$SPPARAMETER WHERE NAME='cluster_database';

SID        NAME                VALUE
-------- ------------------ --------
*        cluster_database      FALSE

Restarting the database next time allowed it to be started in Exclusive mode. Issue was resolved but question was “Why was CLUSTER_DATABASE variable different for Node 1″
I remember that as part of setup I had created Node 1 and added Node 2 later. There could be some missed steps there or it could be that this parameter was set explicitly with sid=’orcl1′ option.I really had no clue on why it was like that.If anyone has experienced this, then do let me know.