RAC, Archivelog mode and cluster_database

In version prior to 10.2 one had to set the parameter cluster_database
to FALSE to switch the archivelog mode of a database

Starting with 10.2 you no longer need to modify the CLUSTER_DATABASE parameter setting to
change the archiving mode in RAC. You can change archivelog mode as long as
the database is mounted in the local instance and not open in any instances.

Lets' test the same...

elephant-> srvctl status database -d F8902PRD
Instance F8902PRD1 is running on node elephant
Instance F8902PRD2 is running on node rhino
Instance F8902PRD3 is running on node hippo

elephant-> sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 19 09:12:21 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u03/backup/F8902PRD/arch/
Oldest online log sequence 1103
Next log sequence to archive 1104
Current log sequence 1104

elephant-> srvctl stop database -d F8902PRD

elephant-> srvctl start instance -d F8902PRD -i F8902PRD1 -o mount
elephant-> srvctl status database -d F8902PRD
Instance F8902PRD1 is running on node elephant
Instance F8902PRD2 is not running on node rhino
Instance F8902PRD3 is not running on node hippo

SQL> show parameter cluster_database

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 3
SQL> alter database noarchivelog;

Database altered.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u03/backup/F8902PRD/arch/
Oldest online log sequence 1103
Current log sequence 1104

elephant-> srvctl stop instance -d F8902PRD -i F8902PRD1

Now lets try to change back to archivelog mode by mounting the database from more
than one instance.

elephant-> srvctl start instance -d F8902PRD -i F8902PRD1 -o mount
elephant-> srvctl start instance -d F8902PRD -i F8902PRD2 -o mount
elephant-> srvctl status database -d F8902PRD
Instance F8902PRD1 is running on node elephant
Instance F8902PRD2 is running on node rhino
Instance F8902PRD3 is not running on node hippo

SQL> show parameter cluster_database

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 3
SQL> alter database archivelog;

Database altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u03/backup/F8902PRD/arch/
Oldest online log sequence 1103
Next log sequence to archive 1104
Current log sequence 1104


elephant-> srvctl stop database -d F8902PRD
elephant-> srvctl start instance -d F8902PRD -i F8902PRD1 -o mount
elephant-> srvctl start instance -d F8902PRD -i F8902PRD2 -o mount
elephant-> srvctl start instance -d F8902PRD -i F8902PRD2 -o open ==> I made a typo here. It should have been F8902PRD3
instead of F8902PRD2 but notice srvctl does not generate any errors. So u don't know if instance 2 has actually opened the database.
elephant-> srvctl start instance -d F8902PRD -i F8902PRD3 -o open

SQL> select inst_id,open_mode from gv$database;

INST_ID OPEN_MODE
---------- ----------
1 MOUNTED
2 MOUNTED
3 READ WRITE

So instance 3 has the db open while instances 1 and 2 have just mounted the database.

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance


*****************************************************************************************
However if you need to recreate the controlfile you need to set cluster_database to false

elephant-> srvctl stop database -d F8902PRD
elephant-> srvctl start instance -d F8902PRD -i F8902PRD1 -o nomount
elephant-> srvctl status database -d F8902PRD
Instance F8902PRD1 is running on node elephant
Instance F8902PRD2 is not running on node rhino
Instance F8902PRD3 is not running on node hippo

SQL> select inst_id,DATABASE_STATUS from gv$instance;

INST_ID DATABASE_STATUS
---------- -----------------
1 ACTIVE

SQL> @controlfile.sql
CREATE CONTROLFILE REUSE DATABASE "F8902PRD" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode