冷備控制檔案 & Using Backup Controlfile

sqlplus命令的recover database的using backup controlfile子句的意義，就是告訴恢復程式，控制檔案不是最新的，控制檔案中也沒有包含最新的日誌資訊，需要指定歸檔的位置給恢復程式，讓其恢復，直到達到指定的位置或者其發現當前是最後的重做條目

 

rman的recover database原則上封裝了using backup controlfile,其能夠自動判斷，但是有一個除外：冷備的控制檔案

 

冷備份的控制檔案在恢復上簡直就是個坑，輕則報個莫名其妙的錯誤，重則少恢復資料，資料丟失。

 

情況一：

ABORT資料庫，CP一個以前的冷備控制檔案過來啟庫做恢復

當前 控制檔案中SCN < 資料檔案頭SCN

 

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01122: database file 1 failed verification check

ORA-01110: data file 1: '/u01/app/oracle/oradata/o11203/system01.dbf'

ORA-01207: file is more recent than control file - old control file

 

RMAN> recover database;

 

Starting recover at 2013-06-25 15:47:52

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=96 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 06/25/2013 15:47:54

RMAN-06094: datafile 1 must be restored

 

這個時候，就算從以前的備份中RESTORE了資料庫，還是無法RECOVER,一樣的報錯。

這種情況下，很容易讓人產生困惑

 

情況二：

ABORT資料庫，CP以前資料庫&控制檔案冷備恢復，企圖外加後面的歸檔恢復到最近狀態

當前 控制檔案中SCN = 資料檔案頭SCN

 

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-00264: no recovery required

 

RMAN> recover database;

 

Starting recover at 2013-06-25 15:52:28

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=96 device type=DISK

 

starting media recovery

media recovery complete, elapsed time: 00:00:02

 

Finished recover at 2013-06-25 15:52:30

 

Tue Jun 25 15:52:28 2013

alter database recover datafile list clear

Completed: alter database recover datafile list clear

alter database recover datafile list

 1 , 2 , 3 , 4 , 5

Completed: alter database recover datafile list

 1 , 2 , 3 , 4 , 5

alter database recover if needed

 start

Media Recovery Start

 started logmerger process

Tue Jun 25 15:52:28 2013

Slave exiting with ORA-264 exception

Errors in file /u01/app/oracle/diag/rdbms/o11203/o11203/trace/o11203_pr00_14769.trc:

ORA-00264: no recovery required

Recovery Slave PR00 previously exited with exception 264

ORA-264 signalled during: alter database recover if needed

 start

...

 

SQL恢復報錯提示無需恢復，RMAN恢復正常，但是ALERT日誌也顯示無需恢復

就算把後續的歸檔catalog進去，也不會去進行恢復

這種情況下，RMAN沒有任何報錯，很容易讓人以為已經成功以用了歸檔，如果沒有監控alert日誌，一旦open resetlogs了，後果就比較嚴重了

 

情況三

先建立一張表，然後abort庫

CP一個冷備的控制檔案，以及在備份控制檔案前一次備份的資料檔案

看能否恢復到最新

當前 控制檔案中SCN > 資料檔案頭SCN

 

SQL> recover database;

ORA-00279: change 4154720 generated at 06/25/2013 15:41:52 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/O11203/archivelog/2013_06_25/o1_mf_1_1_8wlp1v

72_.arc

ORA-00280: change 4154720 for thread 1 is in sequence #1

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 4154848 generated at 06/25/2013 15:43:39 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/O11203/archivelog/2013_06_25/o1_mf_1_2_8wlp1v

79_.arc

ORA-00280: change 4154848 for thread 1 is in sequence #2

 

Log applied.

Media recovery complete.

 

SQL>  alter database open read only;

 

Database altered.

 

SQL> select *from ctais2.test_reco1;

select *from ctais2.test_reco1

                    *

ERROR at line 1:

ORA-00942: table or view does not exist

 

可以看到，表是沒有恢復出來的。

再次執行recover

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01124: cannot recover data file 1 - file is in use or recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/o11203/system01.dbf'

 

RMAN> recover database;

 

Starting recover at 2013-06-25 18:13:42

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=222 device type=DISK

 

starting media recovery

media recovery failed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 06/25/2013 18:13:44

ORA-00283: recovery session canceled due to errors

RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed

 start

ORA-00283: recovery session canceled due to errors

ORA-01124: cannot recover data file 1 - file is in use or recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/o11203/system01.dbf'

 

在使用了冷備控制檔案來恢復的情況下，要完成恢復，解決的辦法只有一個：

recover database using backup controlfile;

 

否則，就要考慮重建控制檔案，或者找一個熱備的控制檔案副本

 

從實驗的結果來看：

1.   冷備的控制檔案，如果不使用using backup controlfile做recover，那麼控制檔案中的db ckpt scn就是恢復的終點

2.   RMAN的RECOVER無法繞過冷備控制檔案的這種限制

 

如何判斷一個控制檔案備份，是否是一個正常shutdown情況下的副本？很簡單，看執行緒是否都CLOSED

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-764803/，如需轉載，請註明出處，否則將追究法律責任。