Loss of control files
前言：預設建立好的RAC叢集只有一個控制檔案，而在單例項資料庫中有三個控制檔案，基於控制檔案的重要性，我們手動新增兩個控制檔案，RAC環境下新增控制檔案可以參考文章：http://blog.itpub.net/29634949/viewspace-1259969/
1.檢視RAC環境下的控制檔案資訊
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+RAC_GROUP/hhpen1/controlfile/current.262.857093601
+RAC_GROUP/hhpen1/controlfile/control02.ctl
+RAC_GROUP/hhpen1/controlfile/control03.ctl
2.模擬控制檔案丟失
由於控制檔案在ORACLE資料庫執行期間會被ORACLE程式鎖定，無法直接刪除，因此我們首先在兩個節點shutdown資料庫，然後再刪除控制檔案；
[oracle@rac1 log]$ srvctl status database -d HHPEN1
Instance HHPEN11 is not running on node rac1
Instance HHPEN12 is not running on node rac2

[oracle@rac1 ~]$ export ORACLE_SID=+ASM1
[oracle@rac1 ~]$ asmcmd
ASMCMD> cd RAC_GROUP/HHPEN1/CONTROLFILE/
ASMCMD> ls
Current.262.857093601
backup.271.857132459
backup.291.857132429
control02.ctl
control03.ctl
ASMCMD> rm -rf control02.ctl
ASMCMD> ls
Current.262.857093601
backup.271.857132459
control03.ctl

3.測試能否開啟資料庫
SQL> startup --要是能開啟的話，以後資料庫可以不要控制檔案了，也就沒有控制檔案損壞恢復這一說了
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 2022144 bytes
Variable Size 188744960 bytes
Database Buffers 343932928 bytes
Redo Buffers 2170880 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
-------------------------------- ------------------------
HHPEN11 STARTED

4.使用RMAN備份對控制檔案執行恢復

1)首先要知道資料庫的DBID
如果是資料庫開啟狀態下可以使用select DBID from v$database;
但是現在控制檔案損壞了，只能啟動到nomount狀態，我們可以使用如下方式檢視資料庫的DBID
[oracle@rac2 dbs]$ pwd
/u01/app/oracle/db_1/dbs
[oracle@rac2 dbs]$ ls c-*
c-2744404128-20140901-01 c-2744404128-20140901-02 --2744404128就是資料庫的DBID
2)對控制檔案執行恢復操作
[oracle@rac1 ~]$ export ORACLE_SID=HHPEN11
[oracle@rac1 ~]$ rman target /
RMAN> set DBID=2744404128;
executing command: SET DBID

RMAN> restore controlfile from autobackup;
Starting restore at 01-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20140901
channel ORA_DISK_1: autobackup found: c-2744404128-20140901-00
channel ORA_DISK_1: control file restore from autobackup complete
output filename=+RAC_GROUP/hhpen1/controlfile/current.262.857093601
output filename=+RAC_GROUP/hhpen1/controlfile/control02.ctl
output filename=+RAC_GROUP/hhpen1/controlfile/control03.ctl
Finished restore at 01-SEP-14

SQL> alter database mount; --有了控制檔案，就可以將資料庫啟動到mount狀態了
Database altered.
由於只是控制檔案丟失，資料檔案仍在，因此並不需要對整個資料庫進行修復操作，只需要執行recover命令，重新應用備份的控制檔案後生成的那些重做
日誌即可；
RMAN> recover database;
Starting recover at 01-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 instance=HHPEN11 devtype=DISK

starting media recovery

archive log thread 1 sequence 6 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_1.261.857093607
archive log thread 2 sequence 69 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_3.258.857093745
archive log thread 2 sequence 70 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_4.257.857093747
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_1.261.857093607 thread=1 sequence=6
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_3.258.857093745 thread=2 sequence=69
creating datafile fno=6 name=+RAC_GROUP/hhpen1/datafile/test.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/01/2014 13:45:41
ORA-01119: error in creating database file '+RAC_GROUP/hhpen1/datafile/test.dbf'
ORA-17502: ksfdcre:4 Failed to create file +RAC_GROUP/hhpen1/datafile/test.dbf
ORA-15005: name "hhpen1/datafile/test.dbf" is already used by an existing alias
由於之前模擬了資料檔案的丟失操作，其他控制檔案中已經有了其配置資訊，對資料庫進行備份的時候並沒有建立test表空間，而只對資料庫執行修復操作
的話，肯定會報錯。
RMAN> restore datafile 6; --對資料檔案執行修復操作
Starting restore at 01-SEP-14
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/01/2014 13:49:20
RMAN-06085: must use SET NEWNAME command to restore datafile /u01/app/oracle/db_1/dbs/UNNAMED00006

登入資料庫檢視資料檔案資訊，如下：
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
HHPEN12 MOUNTED

SQL> col name for a50;
SQL> select FILE#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 +RAC_GROUP/hhpen1/datafile/system.267.857093505 SYSTEM
2 +RAC_GROUP/hhpen1/datafile/undotbs1.266.857093509 ONLINE
3 +RAC_GROUP/hhpen1/datafile/sysaux.263.857093507 ONLINE
4 +RAC_GROUP/hhpen1/datafile/users.265.857093511 ONLINE
5 +RAC_GROUP/hhpen1/datafile/undotbs2.259.857093701 ONLINE
6 /u01/app/oracle/db_1/dbs/UNNAMED00006 RECOVER

6 rows selected.

檢視當前資料庫需要恢復的資料檔案，如下：
SQL> select * from v$recover_file where error like '%FILE%';
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------- ---------- ------------
6 ONLINE ONLINE FILE MISSING 0
SQL> select file#,name from v$datafile where file#=6;
FILE# NAME
---------- --------------------------------------------------
6 /u01/app/oracle/db_1/dbs/UNNAMED00006

現在需要對資料檔案進行改名字，如果說你忘記之前設定的資料檔案的名字了，不要緊，使用如下SQL語句查詢
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- -------------------------------------------------- --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
5 UNDOTBS2 YES NO YES
6 TEST YES NO YES

7 rows selected.

SQL> alter database create datafile '/u01/app/oracle/db_1/dbs/UNNAMED00006' as '+RAC_GROUP/hhpen1/datafile/test.dbf';
Database altered.

在RMAN執行資料庫的恢復操作，如下，顯然是OK了：
RMAN> recover database;
Starting recover at 01-SEP-14
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 6 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_1.261.857093607
archive log thread 2 sequence 69 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_3.258.857093745
archive log thread 2 sequence 70 is already on disk as file +RAC_GROUP/hhpen1/onlinelog/group_4.257.857093747
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_3.258.857093745 thread=2 sequence=69
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_1.261.857093607 thread=1 sequence=6
archive log filename=+RAC_GROUP/hhpen1/onlinelog/group_4.257.857093747 thread=2 sequence=70
media recovery complete, elapsed time: 00:00:03
Finished recover at 01-SEP-14

SQL> alter database open resetlogs; --以resetlogs方式開啟資料庫
alter database open resetlogs
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active --看來是好事多磨啊，我喜歡

此時檢視資料檔案狀態，發現test表空間是online狀態，如下：
SQL> select FILE#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 +RAC_GROUP/hhpen1/datafile/system.267.857093505 SYSTEM
2 +RAC_GROUP/hhpen1/datafile/undotbs1.266.857093509 ONLINE
3 +RAC_GROUP/hhpen1/datafile/sysaux.263.857093507 ONLINE
4 +RAC_GROUP/hhpen1/datafile/users.265.857093511 ONLINE
5 +RAC_GROUP/hhpen1/datafile/undotbs2.259.857093701 ONLINE
6 +RAC_GROUP/hhpen1/datafile/test.dbf ONLINE

6 rows selected.

原因分析：RMAN備份或恢復的時候，資料庫不能以resetlogs方式開啟，關閉當前操作的會話，開啟新的會話使用resetlogs方式開啟資料庫，結果成功了。
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
HHPEN11 OPEN
HHPEN12 OPEN

SQL> conn dayong/dayong; --備份之後建立的使用者還在，表還在，表中資料還在
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MYTABLE TABLE
SQL> select * from mytable;
ID NAME
---------- ----------
1 dayong
2 xiaoru

[WK-T]ORACLE RAC +ASM Backup and Recovery(三)

相關文章