Change the Archiving Mode in a RAC Environment

For Oracle 10g R2, you can run the  ALTER DATABASE  SQL statement to change the archiving mode in Oracle RAC as long as the database is mounted by the local instance but not open in any instances. You do not need to modify parameter settings to run this statement.

Oracle 的生產庫都是啟動在歸檔模式下，RAC下歸檔非歸檔的切換和單例項也是一樣的，都是在MOUNT模式下執行ALTER DATABASE ARCHIVELOG/NOARCHIVELOG;命令。不同的是：RAC環境下所有例項都必須處於非OPEN狀態，然後在任意一個處於MOUNT狀態的例項執行ALTER DATABASE命令，操作成功後，再正常啟動其它例項即可。

一、切換歸檔

SQL> show parameter cluster

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
cluster_database             boolean     TRUE
cluster_database_instances         integer     4
cluster_interconnects             string
SQL>

###check out archive default dest "log_archive_dest_1"
SQL> show parameter log_archive_dest_1

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1             string     LOCATION=+BACKUP_DG/
### issue the following command on  all of nodes  if the destination is null
SQL> alter system set log_archive_dest_1='location=+BACKUP_DG/' sid='STW8AIA1';

System altered.

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
wirac01 | STW8AIA1 | /export/home/oracle > srvctl stop  database -d STW8AIA
wirac01 | STW8AIA1 | /export/home/oracle >

###注意事項： 所有節點都必須處於mount狀態。在其中一個節點修改模式，然後在其他節點正常啟動即可。
wirac01 | STW8AIA1 | /export/home/oracle > srvctl start database -d STW8AIA -o mount
wirac01 | STW8AIA1 | /export/home/oracle > dba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 5 07:03:40 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

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

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           +BACKUP_DG
Oldest online log sequence     33
Next log sequence to archive   34
Current log sequence           34

####最後一步需要 alter database open其他節點DB; 或srvctl stop database 再start 亦可。

Note: You can also change the archive log mode by using the Recovery Settings page in the Maintenance tab of the Enterprise Manager Oracle RAC Database Home Page.

二、切換成非歸檔

在一個節點上執行：
SQL>ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;
wirac01 | STW8AIA1 | /export/home/oracle > srvctl stop database -d STW8AIA

SQL>startup mount;
SQL>alter database noarchivelog;
SQL>ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
SQL>shutdown immediate;

wirac01 | STW8AIA1 | /export/home/oracle > srvctl start database -d STW8AIA

三、10g R2之前版本的模式切換
RAC資料庫從noarchivelog模式轉換到archivelog模式，步驟：
在一個節點上執行：
####first, set cluster_database=false and log_archive_start=true; set log_archive_* appropriately
SQL> alter system set cluster_database=false scope=spfile sid='STW8AIA1';
SQL> alter system set log_archive_start=true scope=spfile sid='STW8AIA1';
wirac01 | STW8AIA1 | /export/home/oracle > srvctl stop database -d STW8AIA
SQL> startup mount exclusive;
SQL> alter database archivelog;
SQL> alter system set cluster_database=true scope=spfile sid='STW8AIA1';
SQL>shutdown immediate;
wirac01 | STW8AIA1 | /export/home/oracle > srvctl start database -d STW8AIA