如何重建RAC的控制檔案

如何重建RAC的控制檔案

在下面的一些情況下，可能需要重建控制檔案：

o 所有控制檔案都已損壞或丟失
o 沒有針對控制檔案的備份或者備份已損壞

下面是針對RAC環境下重建控制檔案的具體過程， 包括兩個例子。一個是以noresetlogs模式來重建控制檔案，一個是以resetlogs模式來重建控制檔案。如果redo logs都存在而且沒有被損壞，那麼可以採用noresetlogs。 使用resetlogs會將所有redo log清空而且重設log sequence為1.

在RAC上重建控制檔案與單一實例有一些小區別： 在重建控制檔案前必須設定cluster_database=false，而且只啟動一個執行個體來執行操作，否則會報錯
ORA-01503: CREATE CONTROLFILE failed
 ORA-12720: operation requires database is in EXCLUSIVE mode

重建完控制檔案後，需要再將cluster_database設為true.

TESTCASE1
 ---------------------------
用noresetlog模式重建控制檔案

 過程：

1.首先產生重建控制檔案的指令碼：
SQL> alter database backup controlfile to trace;

 Database altered.

 2. 所產生的控制檔案的指令碼會在udump下：

SQL> show parameter user_dump_dest

 NAME                                TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 user_dump_dest                      string      /u01/app/Oracle/diag/rdbms/racdb/RACDB1/trace

資料庫的Alert log中也會詳細輸出這個檔案的路徑和名字：
alter database backup controlfile to trace
 Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/racdb/RACDB1/trace/RACDB1_ora_10076.trc
 

（注意： 例子中使用到的具體指令碼，比如建立控制檔案和添加臨時資料檔案的命令都在上面產生的trace檔案中，其它步驟和命令也基本都在這個trace中）。

3. 停止所有資料庫執行個體：
[oracle@rac1 trace]$ srvctl stop database -d RACDB
 

4. 用noresetlog重建控制檔案：
[oracle@rac1 trace]$ sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 8 11:23:44 2014

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

 Connected to an idle instance.

 SQL> startup nomount; <==啟動一個執行個體
ORACLE instance started.

 Total System Global Area  739065856 bytes
 Fixed Size                  2232032 bytes
 Variable Size            549454112 bytes
 Database Buffers          184549376 bytes
 Redo Buffers                2830336 bytes
 SQL> CREATE CONTROLFILE REUSE DATABASE "RACDB" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 192
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 32
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 (
  9      '+DATA/racdb/onlinelog/group_1.261.783272805',
 10      '+RECO/racdb/onlinelog/group_1.257.783272807'
 11    ) SIZE 50M BLOCKSIZE 512,
 12    GROUP 2 (
 13      '+DATA/racdb/onlinelog/group_2.262.783272807',
 14      '+RECO/racdb/onlinelog/group_2.258.783272809'
 15    ) SIZE 50M BLOCKSIZE 512,
 16    GROUP 3 (
 17      '+DATA/racdb/onlinelog/group_3.269.804115405',
 18      '+RECO/racdb/onlinelog/group_3.261.804115405'
 19    ) SIZE 50M BLOCKSIZE 512,
 20    GROUP 4 (
 21      '+DATA/racdb/onlinelog/group_4.270.804115405',
 22      '+RECO/racdb/onlinelog/group_4.263.804115407'
 23    ) SIZE 50M BLOCKSIZE 512
 24  -- STANDBY LOGFILE
 25  DATAFILE
 26    '+DATA/racdb/datafile/system.256.783272707',
 27    '+DATA/racdb/datafile/sysaux.257.783272707',
 28    '+DATA/racdb/datafile/undotbs1.258.783272707',
 29    '+DATA/racdb/datafile/users.259.783272707',
 30    '+DATA/racdb/datafile/example.264.783272831',
 31    '+DATA/racdb/datafile/undotbs2.265.783273081'
 32  CHARACTER SET AL32UTF8
 33  ;
 CREATE CONTROLFILE REUSE DATABASE "RACDB" NORESETLOGS  ARCHIVELOG
 *
 ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode  <============由於沒有設定cluster_database=false，所以報錯

 需要將設定cluster_database=false：
SQL> alter system set cluster_database=false scope=spfile;

 System altered.

 SQL> shutdown immediate
 ORA-01507: database not mounted

 ORACLE instance shut down.

 SQL> startup nomount;
 ORACLE instance started.

 Total System Global Area  739065856 bytes
 Fixed Size                  2232032 bytes
 Variable Size            549454112 bytes
 Database Buffers          184549376 bytes
 Redo Buffers                2830336 bytes
 SQL> CREATE CONTROLFILE REUSE DATABASE "RACDB" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 192
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 32
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 (
  9      '+DATA/racdb/onlinelog/group_1.261.783272805',
 10      '+RECO/racdb/onlinelog/group_1.257.783272807'
 11    ) SIZE 50M BLOCKSIZE 512,
 12    GROUP 2 (
 13      '+DATA/racdb/onlinelog/group_2.262.783272807',
 14      '+RECO/racdb/onlinelog/group_2.258.783272809'
 15    ) SIZE 50M BLOCKSIZE 512,
 16    GROUP 3 (
 17      '+DATA/racdb/onlinelog/group_3.269.804115405',
 18      '+RECO/racdb/onlinelog/group_3.261.804115405'
 19    ) SIZE 50M BLOCKSIZE 512,
 20    GROUP 4 (
 21      '+DATA/racdb/onlinelog/group_4.270.804115405',
 22      '+RECO/racdb/onlinelog/group_4.263.804115407'
 23    ) SIZE 50M BLOCKSIZE 512
 24  -- STANDBY LOGFILE
 25  DATAFILE
 26    '+DATA/racdb/datafile/system.256.783272707',
 27    '+DATA/racdb/datafile/sysaux.257.783272707',
 28    '+DATA/racdb/datafile/undotbs1.258.783272707',
 29    '+DATA/racdb/datafile/users.259.783272707',
 30    '+DATA/racdb/datafile/example.264.783272831',
 31    '+DATA/racdb/datafile/undotbs2.265.783273081'
 32  CHARACTER SET AL32UTF8
 33  ;

 Control file created.

 SQL> select * from v$log; <===確認redo log，thread#是正確的

  GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
 ---------- ---------- ---------- ---------- ---------- ---------- ---
 STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
 ---------------- ------------- --------- ------------ ---------
        1          1        11  52428800        512          2 NO
 CURRENT                6876599 08-JAN-13  2.8147E+14

        2          1        10  52428800        512          2 NO
 INACTIVE              6825446 07-JAN-13      6876599 08-JAN-13

        3          2          7  52428800        512          2 NO
 INACTIVE              6877338 08-JAN-13      6960724 08-JAN-13

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
 ---------- ---------- ---------- ---------- ---------- ---------- ---
 STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
 ---------------- ------------- --------- ------------ ---------
        4          2        6  52428800        512          2 NO
 INACTIVE              6815353 07-JAN-13      6877338 08-JAN-13

SQL> recover database;
 ORA-00283: recovery session canceled due to errors
 ORA-00264: no recovery required

 SQL> alter system  archive log all;

 System altered.

 SQL> alter database open;

 Database altered.

 SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/racdb/tempfile/temp.263.783272821'
  2      SIZE 39845888  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M; 《==別忘了添加原來的臨時檔案到暫存資料表空間中

Tablespace altered.

 6.將cluster_database設為true :
 SQL> alter system set cluster_database=true scope=spfile;

 System altered.

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

 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
 With the Partitioning, Real Application Clusters, OLAP, Data Mining
 and Real Application Testing options

 7. 啟動所有執行個體：
[oracle@rac1 trace]$ srvctl start database -d RACDB
 [oracle@rac1 trace]$ srvctl status database -d RACDB
 Instance RACDB1 is running on node rac1
 Instance RACDB2 is running on node rac2

TESTCASE2
 ---------------------------
用resetlogs模式重建控制檔案

Test Process:

 1.首先產生重建控制檔案的指令碼：
SQL> alter database backup controlfile to trace;

 Database altered.

 2. 資料庫的Alert log中也會詳細輸出這個檔案的路徑和名字：
alter database backup controlfile to trace
 Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/racdb/RACDB1/trace/RACDB1_ora_5649.trc

 3. 設定cluster_database=false：
SQL> alter system set cluster_database=false scope=spfile;
 System altered.

否則，在重建控制檔案的時候會報下面的錯誤：

CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS  ARCHIVELOG
 *
 ERROR at line 1:
 ORA-01503: CREATE CONTROLFILE failed
 ORA-12720: operation requires database is in EXCLUSIVE mode

 4. 停止所有資料庫執行個體：
[oracle@rac1 trace]$ srvctl stop database -d RACDB
 [oracle@rac1 trace]$ srvctl status database -d RACDB
 Instance RACDB1 is not running on node rac1
 Instance RACDB2 is not running on node rac2

 5. 用resetlogs模式重建控制檔案：

[oracle@rac1 trace]$ sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 8 12:45:25 2013

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

 Connected to an idle instance.

 SQL> startup nomount;
 ORACLE instance started.

 Total System Global Area  739065856 bytes
 Fixed Size                  2232032 bytes
 Variable Size            549454112 bytes
 Database Buffers          184549376 bytes
 Redo Buffers                2830336 bytes

 SQL> CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 192
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 32
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 (
  9      '+DATA/racdb/onlinelog/group_1.261.783272805',
 10      '+RECO/racdb/onlinelog/group_1.257.783272807'
 11    ) SIZE 50M BLOCKSIZE 512,
 12    GROUP 2 (
 13      '+DATA/racdb/onlinelog/group_2.262.783272807',
 14      '+RECO/racdb/onlinelog/group_2.258.783272809'
 15    ) SIZE 50M BLOCKSIZE 512
 16  -- STANDBY LOGFILE
 17  DATAFILE
 18    '+DATA/racdb/datafile/system.256.783272707',
 19    '+DATA/racdb/datafile/sysaux.257.783272707',
 20    '+DATA/racdb/datafile/undotbs1.258.783272707',
 21    '+DATA/racdb/datafile/users.259.783272707',
 22    '+DATA/racdb/datafile/example.264.783272831',
 23    '+DATA/racdb/datafile/undotbs2.265.783273081'
 24  CHARACTER SET AL32UTF8
 25  ;

 Control file created.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;  <=========必須使用UNTIL CANCEL，否則資料庫無法open
 ORA-00279: change 6976933 generated at 01/08/2013 12:45:12 needed for thread 1
 ORA-00289: suggestion : +RECO
 ORA-00280: change 6976933 for thread 1 is in sequence #2

 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL 
 Media recovery cancelled.
 

接下來需要??加其他thread，因為用resetlogs重建controlfile只是增加了thread為1的redo log：

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
  2    GROUP 3 (
  3      '+DATA/racdb/onlinelog/group_3.269.804115405',
  4      '+RECO/racdb/onlinelog/group_3.261.804115405'
  5    ) SIZE 50M BLOCKSIZE 512 REUSE,
  6    GROUP 4 (
  7      '+DATA/racdb/onlinelog/group_4.270.804115405',
  8      '+RECO/racdb/onlinelog/group_4.263.804115407'
  9    ) SIZE 50M BLOCKSIZE 512 REUSE;
 ALTER DATABASE ADD LOGFILE THREAD 2
 *
 ERROR at line 1:
 ORA-01276: Cannot add file +DATA/racdb/onlinelog/group_3.269.804115405.  File
 has an Oracle Managed Files file name. 

對於ASM，使用了OMF命名規則時不能指定具體的檔案名稱，只需要指定diskgroup名即可：

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
  2    GROUP 3 (
 3      '+DATA',
 4      '+RECO'
  5    ) SIZE 50M BLOCKSIZE 512 REUSE,
  6    GROUP 4 (
 7      '+DATA',
 8      '+RECO'
  9    ) SIZE 50M BLOCKSIZE 512 REUSE;

 Database altered.

 SQL> ALTER DATABASE OPEN RESETLOGS;

 Database altered.

 SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/racdb/tempfile/temp.263.783272821'
  2          SIZE 39845888  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

 Tablespace altered.

 6. 設定cluster_database=true :
 SQL> alter system set cluster_database=true scope=spfile;

 System altered.

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

 7. 啟動所有執行個體：

[oracle@rac1 trace]$ srvctl start database -d RACDB
 [oracle@rac1 trace]$ srvctl status database -d RACDB
 Instance RACDB1 is running on node rac1
 Instance RACDB2 is running on node rac2