Oracle RAC 歸檔與非歸檔切換

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

注意：RAC資料庫由於擁有多個例項，要注意每個例項相關初始化引數：LOG_ARCHIVE_DEST_n的設定，務必需要確保該引數設定的路徑合法有效，歸檔也能順利進行。

[@more@]

[oracle@node1pub ~]$ echo $ORACLE_SID
orcl1
[oracle@node1pub ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 24 14:05:12 2012

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

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 No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 56
Current log sequence 57
SQL> select inst_id,instance_name,version,archiver,status from gv$instance;

INST_ID INSTANCE_NAME VERSION ARCHIVE STATUS
---------- ---------------- ----------------- ------- ------------
1 orcl1 10.2.0.1.0 STOPPED OPEN
2 orcl2 10.2.0.1.0 STOPPED OPEN

修改歸檔的預設路徑

在兩個節點分別建立歸檔的目錄

[oracle@node1pub u01]$ mkdir orcl1_arch

[oracle@node2pub u01]$ mkdir orcl2_arch

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> alter system set log_archive_dest_1='location=/u01/orcl1_arch' sid='orcl1';

System altered.

SQL> alter system set log_archive_dest_1='location=/u01/orcl2_arch' sid='orcl2';

System altered.

SQL> show parameter log_archive_dest_1

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=/u01/orcl1_arch
log_archive_dest_10 string

登出第二個節點檢視：

[oracle@node2pub u01]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 24 13:52:44 2012

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

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> show parameter log_archive_dest_1

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=/u01/orcl2_arch
log_archive_dest_10 string

切換歸檔模式：

注意事項： 所有節點都必須處於mount狀態。 在其中一個節點修改模式，然後在其他節點正常啟動即可。

node1:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1218968 bytes
Variable Size 100664936 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
Database mounted.

node2:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1218968 bytes
Variable Size 100664936 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
Database mounted.

node1:

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

node2:

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/orcl2_arch
Oldest online log sequence 17
Next log sequence to archive 18
Current log sequence 18

node1:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/orcl1_arch
Oldest online log sequence 56
Next log sequence to archive 57
Current log sequence 57

至此，現在RAC已經切換到歸檔模式了。

下面來試試如何切換成非歸檔：

node1:

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;

System altered.

====================================================
CLUSTER_DATABASE的意思是：
如果掛接資料庫的第一個例項的CLUSTER_DATABASE為false，那麼只有這個例項可以掛接這個資料庫。如果第一個例項

的CLUSTER_DATABASE引數為true，那麼其他引數CLUSTER_DATABASE值為true的例項也可以掛接這個資料庫。
也就是保證在下面的步驟中node2不能連線到資料庫。

如果node1的CLUSTER_DATABASE=FALSE，node2試圖打圖資料庫時，則會出錯：

SQL> startup mount
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1218968 bytes
Variable Size 104859240 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode

======================================================

關閉兩邊的資料庫：

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

node2:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

node1:

SQL> startup mount
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1218968 bytes
Variable Size 104859240 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

現在兩個節點可正常開啟了：

node1:

SQL> startup
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1218968 bytes
Variable Size 104859240 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/orcl1_arch
Oldest online log sequence 56
Current log sequence 57

node2:

SQL> startup
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1218968 bytes
Variable Size 104859240 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/orcl2_arch
Oldest online log sequence 17
Current log sequence 18