【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
真題1、如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
答案：如果控制檔案有多個，而只損壞了單個控制檔案，那麼只需要關閉資料庫，複製其它好的控制檔案覆蓋掉壞的控制檔案即可。也可以修改引數檔案，只保留1個控制檔案。如果損壞了全部控制檔案，那麼需要重新建立控制檔案或從備份恢復。在有控制檔案備份的情況下，restore controlfile命令可以用來還原控制檔案。在還原控制檔案後需要對資料庫執行完全介質恢復並以resetlogs選項來開啟資料庫。
RMAN可以將控制檔案還原到它的預設儲存位置，也可以使用restore controlfile ... to destination來指定控制檔案的恢復位置。當還原控制檔案時，控制檔案的預設位置是由引數control_files控制的。如果沒有設定control_files引數，那麼資料庫判斷還原控制檔案儲存位置的規則將會與沒有設定control_files引數時建立控制檔案時使用的規則一樣。
如下命令可以從備份集中恢復控制檔案：
restore controlfile from '/bak/OCPLHR1/ctl_OCPLHR1_20180322_64_1.bak';
restore controlfile to '/home/oracle/a.ctl' from '/bak/OCPLHR1/ctl_OCPLHR1_20180322_64_1.bak' ;
在將控制檔案還原到預設位置時，資料庫必須處於nomount狀態。如果從自動備份中還原控制檔案，那麼必須首先設定資料庫DBID，然後執行restore controlfile from autobackup命令。
最後，可以考慮使用控制檔案快照進行恢復。如果沒有任何備份的控制檔案，那麼需要重建控制檔案。重建控制檔案的指令碼可以透過命令“ALTER DATABASE BACKUP CONTROLFILE TO TRACE;”獲取。
在啟動資料庫的時候，如果報控制檔案的版本不一致（ORA-00214），那麼只需要將高版本的資料庫的控制檔案覆蓋低版本的資料庫的控制檔案即可，如下所示：
SYS@OCPLHR1> startup
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2229944 bytes
Variable Size 301992264 bytes
Database Buffers 209715200 bytes
Redo Buffers 7999488 bytes
ORA-00214: control file '/u01/app/oracle/fast_recovery_area/OCPLHR1/control02.ctl' version 2701 inconsistent with file
'/u01/app/oracle/oradata/OCPLHR1/control01.ctl' version 2699
SYS@OCPLHR1> ! cp /u01/app/oracle/fast_recovery_area/OCPLHR1/control02.ctl /u01/app/oracle/oradata/OCPLHR1/control01.ctl
SYS@OCPLHR1> alter database mount;
Database altered.
SYS@OCPLHR1> alter database open;
& 說明：
有關控制檔案在缺失歸檔日誌的情況下的恢復可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152506/
真題1、Oracle的控制檔案在缺失歸檔日誌的情況下的恢復步驟有哪些？
在恢復控制檔案時“recover database”命令可能需要使用歸檔日誌。所謂缺失歸檔日誌，是指控制檔案從備份還原之後，在執行“recover database”命令恢復時報告找不到相應的日誌導致恢復終止的情況。
這種情況下的恢復操作主要步驟如下：
① 首先還原控制檔案，方式不限。
② 執行“recover database”命令將報RMAN-06054錯誤，即找不到某歸檔日誌。
③ 檢視相關的動態效能檢視，對問題定位，確認問題與控制檔案，而不是資料檔案相關（與資料檔案相關必須進行不完全恢復）。
④ 利用create controlfile 命令重建控制檔案。
⑤ 再次執行“recover database”命令，還會報RMAN-06054錯誤，這次是找不到另一個歸檔日誌，其序列號應該大於第二步中的。
⑥ 檢視v$log檢視確定第5步中所要的是哪個日誌。
⑦ 執行SQLPLUS的”recover database using backup controlfile“命令，等”Specify log：“提示符出現後給出正確的線上日誌路徑，直到命令成功結束。
⑧ 以resetlogs方式開啟資料庫。
⑨ 由於建立的控制檔案內不會有臨時資料檔案的資訊，需要重新將其新增回臨時表空間。
⑩ 將控制檔案內其他丟失的資訊用catalog和configure等命令再新增回去。
& 說明：
有關控制檔案在缺失歸檔日誌的情況下的恢復可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152115/
一.1.1.1 丟失了控制檔案
|
丟失了控制檔案 如果控制檔案丟失或損壞，則例項通常會中止。 ? 如果控制檔案儲存在 ASM 磁碟組中，則恢復方案如下： – 使用 Enterprise Manager 執行指導式恢復。 – 將資料庫置於NOMOUNT模式，然後使用 RMAN 命令從現有控制檔案恢復控制檔案。
? 如果控制檔案儲存為常規檔案系統檔案，則： – 關閉資料庫。 – 複製現有的控制檔案來替代丟失的控制檔案。 成功恢復控制檔案後，開啟資料庫。
版權所有 © 2010，Oracle。保留所有權利。 |
丟失了控制檔案後，可選的恢復方案取決於控制檔案的儲存配置以及是至少還有一個控制檔案還是丟失了所有檔案。
如果使用ASM儲存，並且至少還有一個控制檔案副本，您可以使用Enterprise Manager執行指導式恢復，或者使用RMAN執行手動恢復，如下所示：
將資料庫置於NOMOUNT模式。
連線到 RMAN 併發出restore controlfile命令來從現有的控制檔案恢復控制檔案，例如：
restore controlfile from '+DATA/orcl/controlfile/current.260.695209463';
成功恢復控制檔案後，開啟資料庫。
如果您的控制檔案儲存為常規檔案系統檔案並且至少還有一個控制檔案副本，這樣，在資料庫處於關閉狀態時，您只需將剩餘的控制檔案中的一個複製到丟失檔案的位置。如果介質故障是由於磁碟驅動器或控制器缺失而造成的，則將剩餘的控制檔案中的一個複製到其它某個位置，然後透過更新例項的引數檔案來指向新位置。或者，可從初始化引數檔案中刪除對丟失的控制檔案的引用。請注意：Oracle 建議始終至少保留兩個控制檔案。
注：《Oracle Database 11g：資料庫管理－課堂練習 II》課程中介紹瞭如何在丟失了所有控制檔案後進行恢復。
一.1.1.2 控制檔案恢復前的準備
為了恢復控制檔案，例項應該處於nomount狀態，如果發現問題的時候例項還未關閉，首先應該使用“shutdown abort”命令關閉例項，接著雖然可以使用“startup nomount”命令，但是建議使用“startup” 命令啟動例項，使其自然卡在 “nomount”狀態，這樣做可能會在警告日誌和追蹤日誌中產生更多有用有價值的資訊，並且對資料恢復顧問也有好處。
----跳過某個已經刪除的表空間：
控制檔案自動備份開啟的情況下：
一.1.1.3 有備份情況下的恢復
一、 控制檔案之一丟失(單個控制檔案丟失或損壞)正確的處理步驟：
① 關閉資料庫
② 從其它位置複製一個
③ 啟動資料庫
我們知道資料庫的控制檔案都不止一個（一般為3個），這些控制檔案互相為映象，所以只需要將其他沒損壞的控 制 檔案重新命名為損壞的控制檔案即可。
我現在有三個控制檔案
-rw-r----- 1 oracle oinstall 7258112 Mar 13 15:18 control01.ctl
-rw-r----- 1 oracle oinstall 7258112 Mar 13 15:18 control02.ctl
-rw-r----- 1 oracle oinstall 7258112 Mar 13 15:18 control03.ctl
現在刪除一個控制檔案control01.ctl
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 104859216 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
將control02.ctl複製為control01.ctl
[oracle@localhost orcl]$ cp control02.ctl control01.ctl
成功啟動資料庫
SQL> alter database mount;
Database altered.
SQL> alter database open；
Database altered.
注：單個控制檔案損壞也可以使用下面所講的全部控制檔案損壞時的恢復方法，只是這裡直接重新命名其他控制檔案的方法比較快，也是推薦的一種恢復方法。
二、 各種情況下的丟失
1、 已知備份檔案位置
restore controlfile from '/bak/OCPLHR1/ctl_OCPLHR1_20180322_64_1.bak';
restore controlfile to '/home/oracle/a.ctl' from '/bak/OCPLHR1/ctl_OCPLHR1_20180322_64_1.bak' ;
如果丟失或損壞所有的控制檔案就需要從備份中還原控制檔案。restore controlfile命令用來還原控制檔案。在還原控制檔案後需要對資料庫執行完全介質恢復並以resetlog選項來開啟資料庫。RMAN可以將控制檔案還原到它的預設儲存位置，也可以使用restore controlfile ... to destination來指定位置。
從已經知的控制檔案備份中還原控制檔案
SQL> show parameter control_files NAME TYPE VALUE------------------------------------ ----------- ------------------------------control_files string /u01/app/oracle/oradata/test/c ontrol01.ctl, /u01/app/oracle/ oradata/test/control02.ctl, /u 01/app/oracle/oradata/test/con trol03.ctl
顯示當前可用的備份
RMAN> list backup; List of Backup Sets=================== BS Key Size Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ ---------------77 693.50K DISK 00:00:02 28-JAN-15 BP Key: 75 Status: AVAILABLE Compressed: YES Tag: TAG20150128T131713 Piece Name: /u02/test_df870182233_s95_s1 List of Archived Logs in backup set 77 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 13 2928236 28-JAN-15 2928830 28-JAN-15 BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------78 Full 166.91M DISK 00:01:19 28-JAN-15 BP Key: 76 Status: AVAILABLE Compressed: YES Tag: TAG20150128T131716 Piece Name: /u02/test_df870182236_s96_s1 List of Datafiles in backup set 78 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/system01.dbf 2 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/undotbs01.dbf 3 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/sysaux01.dbf 4 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/users01.dbf 5 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/example01.dbf 6 Full 2928835 28-JAN-15 /u01/app/oracle/oradata/test/test01.dbf BS Key Size Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ ---------------79 7.50K DISK 00:00:01 28-JAN-15 BP Key: 77 Status: AVAILABLE Compressed: YES Tag: TAG20150128T131841 Piece Name: /u02/test_df870182321_s97_s1 List of Archived Logs in backup set 79 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 14 2928830 28-JAN-15 2928868 28-JAN-15 BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------80 Full 9.42M DISK 00:00:02 28-JAN-15 BP Key: 78 Status: AVAILABLE Compressed: NO Tag: TAG20150128T131843 Piece Name: /u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150128-0d Control File Included: Ckp SCN: 2928874 Ckp time: 28-JAN-15 SPFILE Included: Modification time: 28-JAN-15
從上面的資訊可以看到備份集80是控制檔案與spfile檔案的備份
下面來刪除當前資料庫的所有控制檔案：
[root@oracle11g ~]# cd /u01/app/oracle/oradata/test/[root@oracle11g test]# ls -lrttotal 2213868-rw-r----- 1 oracle oinstall 11804672 Feb 1 11:36 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 11:36 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 11:36 redo02.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 11:36 redo01.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 11:36 example01.dbf-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 19:05 system01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 19:05 undotbs01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 19:05 sysaux01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 19:10 redo03.log-rw-r----- 1 oracle oinstall 9814016 Feb 1 19:11 control03.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 19:11 control02.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 19:11 control01.ctl[root@oracle11g test]# rm -rf control*.ctl[root@oracle11g test]# ls -lrttotal 2185068-rw-r----- 1 oracle oinstall 52429312 Feb 1 11:36 redo02.log-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 19:13 redo03.log-rw-r----- 1 oracle oinstall 11804672 Feb 1 19:14 users01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 19:14 undotbs01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 19:14 test01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 19:14 system01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 19:14 sysaux01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 19:14 redo01.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 19:14 example01.dbf
向測試表t2中插入一些資料庫
SQL> insert into t2 select * from dba_objects; 51319 rows created. SQL> select count(*) from t2; COUNT(*)---------- 102560 SQL> commit; Commit complete.
這裡因為是從linux作業系統層面刪除了所有控制檔案，因為在資料庫沒有關閉的情況下檔案的控制程式碼沒有釋放所以資料庫還能執行。
人為將資料庫異常終止
[root@oracle11g test]# ps -ef | grep smonoracle 3463 1 0 22:30 ? 00:00:00 ora_smon_testroot 3179 3123 0 22:45 pts/3 00:00:00 grep smon[root@oracle11g test]# kill -9 3463
啟動資料庫：
SQL> startupORACLE instance started. Total System Global Area 327155712 bytesFixed Size 1273516 bytesVariable Size 138412372 bytesDatabase Buffers 184549376 bytesRedo Buffers 2920448 bytesORA-00205: error in identifying control file, check alert log for more info
alert日誌的內容如下:
ORA-00210: cannot open the specified control fileORA-00202: control file: '/u01/app/oracle/oradata/test/control01.ctl'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3Sun Feb 01 19:18:18 CST 2015ORA-205 signalled during: ALTER DATABASE MOUNT...
找不到控制檔案不能將資料庫置於mount狀態.現在透過備份來還原控制檔案執行完全資料庫恢復:
RMAN> restore controlfile from '/u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150128-0d'; Starting restore at 01-FEB-15using channel ORA_DISK_1 channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:05output filename=/u01/app/oracle/oradata/test/control01.ctloutput filename=/u01/app/oracle/oradata/test/control02.ctloutput filename=/u01/app/oracle/oradata/test/control03.ctlFinished restore at 01-FEB-15 RMAN> sql 'alter database mount'; sql statement: alter database mountreleased channel: ORA_DISK_1 SQL> select status from v$instance; STATUS------------------------MOUNTED
執行完全恢復
RMAN> recover database; Starting recover at 01-FEB-15Starting implicit crosscheck backup at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked 3 objectsFinished implicit crosscheck backup at 01-FEB-15 Starting implicit crosscheck copy at 01-FEB-15using channel ORA_DISK_1Crosschecked 6 objectsFinished implicit crosscheck copy at 01-FEB-15 searching for all files in the recovery areacataloging files...no files cataloged using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 17 is already on disk as file /u01/app/oracle/oradata/test/redo02.logarchive log thread 1 sequence 18 is already on disk as file /u01/app/oracle/oradata/test/redo03.logarchive log thread 1 sequence 19 is already on disk as file /u01/app/oracle/oradata/test/redo01.logarchive log filename=/u02/1_15_870133266.dbf thread=1 sequence=15archive log filename=/u02/1_16_870133266.dbf thread=1 sequence=16archive log filename=/u01/app/oracle/oradata/test/redo02.log thread=1 sequence=17archive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=18archive log filename=/u01/app/oracle/oradata/test/redo01.log thread=1 sequence=19media recovery complete, elapsed time: 00:00:06Finished recover at 01-FEB-15 RMAN> sql 'alter database open resetlogs'; sql statement: alter database open resetlogs SQL> select status from v$instance; STATUS------------OPEN SQL> select count(*) from t2; COUNT(*)---------- 102560
表t2中的記錄與恢復之前相同，說明恢復成功。
當還原控制檔案時，控制檔案的預設位置是由引數control_files控制的。如果沒有設定control_files引數，那麼資料庫判斷還原控制檔案儲存位置的規則將會與沒有設定control_files引數時建立控制檔案時使用的規則一樣。
2、 使用了恢復目錄
當沒有使用恢復目錄時，必須從控制檔案自動備份中還原控制檔案。如果從控制檔案自動備份中還原控制檔案，資料庫必須置於nomount狀態。必須首先設定資料庫的DBID，然後執行restore controlfile from autobackup命令
1.人為刪除所有控制檔案
[root@oracle11g test]# ls -lrttotal 2213868-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control03.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control02.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control01.ctl[root@oracle11g test]# rm -rf control*.ctl[root@oracle11g test]# ls -lrttotal 2185068-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log
2.人為將資料庫異常終止
[root@oracle11g test]# ps -ef | grep smonoracle 3063 1 0 22:30 ? 00:00:00 ora_smon_testroot 3179 3123 0 22:45 pts/3 00:00:00 grep smon[root@oracle11g test]# kill -9 3063
3.將資料庫啟動到nomount狀態
SQL> startup nomountORACLE instance started. Total System Global Area 327155712 bytesFixed Size 1273516 bytesVariable Size 138412372 bytesDatabase Buffers 184549376 bytesRedo Buffers 2920448 bytes
4.從以前的備份資訊中可以找到如下資訊，其中c-2155613261-20150201-03中的2155613261就是DBID
Starting Control File and SPFILE Autobackup at 01-FEB-15piece handle=/u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150201-03 comment=NONEFinished Control File and SPFILE Autobackup at 01-FEB-15
5.還原控制檔案
RMAN> show controlfile autobackup format; RMAN configuration parameters are:CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'STB' TO '%F';CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F'; RMAN> set dbid 2155613261; executing command: SET DBID RMAN> restore controlfile from autobackup; Starting restore at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISK recovery area destination: /u01/app/oracle/flash_recovery_areadatabase name (or database unique name) used for search: TESTchannel ORA_DISK_1: no autobackups found in the recovery areachannel ORA_DISK_1: looking for autobackup on day: 20150201channel ORA_DISK_1: autobackup found: c-2155613261-20150201-03channel ORA_DISK_1: control file restore from autobackup completeoutput filename=/u01/app/oracle/oradata/test/control01.ctloutput filename=/u01/app/oracle/oradata/test/control02.ctloutput filename=/u01/app/oracle/oradata/test/control03.ctlFinished restore at 01-FEB-15
6.恢復資料庫
RMAN> sql 'alter database mount'; sql statement: alter database mountreleased channel: ORA_DISK_1 RMAN> recover database; Starting recover at 01-FEB-15Starting implicit crosscheck backup at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked 13 objectsFinished implicit crosscheck backup at 01-FEB-15 Starting implicit crosscheck copy at 01-FEB-15using channel ORA_DISK_1Crosschecked 6 objectsFinished implicit crosscheck copy at 01-FEB-15 searching for all files in the recovery areacataloging files...no files cataloged using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/oradata/test/redo02.logarchive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/oradata/test/redo03.logarchive log filename=/u01/app/oracle/oradata/test/redo02.log thread=1 sequence=2archive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=3media recovery complete, elapsed time: 00:00:03Finished recover at 01-FEB-15 RMAN> sql 'alter database open resetlogs'; sql statement: alter database open resetlogs
RMAN會使用自動備份的格式與DBID來判斷在什麼儲存位置來搜尋控制檔案自動備份。如果找到，RMAN就會從備份中將控制檔案還原到由control_files引數所指定的所有位置
RMAN使用恢復目錄還原控制檔案
1.人為刪除所有控制檔案
[root@oracle11g test]# ls -lrt
total 2213868
-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf
-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf
-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf
-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log
-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf
-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf
-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf
-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf
-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log
-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control03.ctl
-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control02.ctl
-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control01.ctl
[root@oracle11g test]# rm -rf control*.ctl
[root@oracle11g test]# ls -lrt
total 2185068
-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf
-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf
-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf
-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log
-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf
-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf
-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf
-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf
-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log
2.人為將資料庫異常終止
[root@oracle11g test]# ps -ef | grep smonoracle 4135 1 0 22:30 ? 00:00:00 ora_smon_testroot 3179 3123 0 22:45 pts/3 00:00:00 grep smon[root@oracle11g test]# kill -9 4135
3.將資料庫啟動到nomount狀態
SQL> startup nomountORACLE instance started. Total System Global Area 327155712 bytesFixed Size 1273516 bytesVariable Size 138412372 bytesDatabase Buffers 184549376 bytesRedo Buffers 2920448 bytes
4.還原控制檔案
[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jy Recovery Manager: Release 10.2.0.5.0 - Production on Sun Feb 1 23:04:03 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: test (not mounted)connected to recovery catalog database RMAN> restore controlfile; Starting restore at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISK channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: reading from backup piece /u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150201-01channel ORA_DISK_1: restored backup piece 1piece handle=/u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150201-01 tag=TAG20150201T213315channel ORA_DISK_1: restore complete, elapsed time: 00:00:04output filename=/u01/app/oracle/oradata/test/control01.ctloutput filename=/u01/app/oracle/oradata/test/control02.ctloutput filename=/u01/app/oracle/oradata/test/control03.ctlFinished restore at 01-FEB-15
5.執行完全恢復
RMAN> sql 'alter database mount'; sql statement: alter database mountreleased channel: ORA_DISK_1 RMAN> recover database; Starting recover at 01-FEB-15Starting implicit crosscheck backup at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked 8 objectsFinished implicit crosscheck backup at 01-FEB-15 Starting implicit crosscheck copy at 01-FEB-15using channel ORA_DISK_1Crosschecked 6 objectsFinished implicit crosscheck copy at 01-FEB-15 searching for all files in the recovery areacataloging files...no files cataloged using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/oradata/test/redo03.logarchive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/oradata/test/redo01.logarchive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=3archive log filename=/u01/app/oracle/oradata/test/redo01.log thread=1 sequence=4media recovery complete, elapsed time: 00:00:01Finished recover at 01-FEB-15 RMAN> sql 'alter database open resetlogs'; sql statement: alter database open resetlogsnew incarnation of database registered in recovery catalogstarting full resync of recovery catalogfull resync complete
①、 將控制檔案還原到新目錄
有一種將控制檔案還原到一個或多個新目錄的方法是修改control_files引數，然後用沒有任何引數的restore controlfile命令將控制檔案還原到預設位置。例如，如果在有些控制檔案目錄所在的磁碟出現故障還原控制檔案，可以修改control_files引數將出現故障的磁碟使用其它的磁碟來替代，然後執行restore controlfile命令來還原控制檔案。
如果不修改control_files引數也可以使用restore controlfile to 'filename' [from autobackup]命令來將控制檔案還原到你所指定的位置。
示例：
RESTORE CONTROLFILE TO '/tmp/my_controlfile';
下面的命令將使用自動備份將控制檔案還原到'/u01/app/oracle/‘目錄下
RMAN> restore controlfile to '/u01/app/oracle/control_temp.ctl' from autobackup; Starting restore at 02-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=148 devtype=DISK recovery area destination: /u01/app/oracle/flash_recovery_areadatabase name (or database unique name) used for search: TESTchannel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_02/o1_mf_s_870567448_bdwndtqk_.bkpchannel ORA_DISK_1: control file restore from autobackup completeFinished restore at 02-FEB-15
上面的命令可以在資料庫為nomount,mount,open狀態下進行，因為不會覆蓋任何當前使用的控制檔案。在將控制檔案還原到新目錄後，可以修改control_files引數來引用新目錄下的控制檔案。
使用備份控制檔案的限制
在使用備份控制檔案還原資料庫後，你必須執行recover database來恢復資料庫並且必須執行alter database open resetlogs來開啟資料庫。
3、 設定了設定閃回區
設定閃回區的情況下還原控制檔案所使用的命令是相同的。然而如果當前資料庫正在使用閃回區，RMAN透過對所有基於控制檔案中的基於磁碟的備份和映象副本和任何在閃回區中而不在還原的控制檔案中的備份執行隱式的crosscheck來更新從備份中還原的控制檔案。因此還原後的控制檔案會完整的和精確的記錄在閃回區中的所有備份和其它任何在備份該控制檔案時所知道的備份。這提高了在資料庫還原操作中的可用性。
下面來看一個使用閃回區還原控制檔案的例項：
1.環境檢查，看是否已經啟用閃回區與設定控制檔案自動備份
SQL> show parameter db_recover NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest string /u01/app/oracle/flash_recovery_areadb_recovery_file_dest_size big integer 2G SQL> select flashback_on from v$database; FLASHBACK_ON------------------YES RMAN> show controlfile autobackup; RMAN configuration parameters are:CONFIGURE CONTROLFILE AUTOBACKUP ON;
從上面資訊可知已經啟用了閃回區並設定了控制檔案自動備份
2.建立一個表空間,在資料庫結構發生變化時，就會自動備份控制檔案
SQL> create tablespace testbak datafile '/u01/app/oracle/oradata/test/testbak.dbf' size 10M extent management local segment space management auto; Tablespace created.
從alert日誌中可以看到產生的控制檔案自動備份的檔案資訊
create tablespace testbak datafile '/u01/app/oracle/oradata/test/testbak.dbf' size 10M extent management local segment space management autoMon Feb 02 00:17:28 CST 2015Starting control autobackupControl autobackup written to DISK device handle '/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_02/o1_mf_s_870567448_bdwndtqk_.bkp'Completed: create tablespace testbak datafile '/u01/app/oracle/oradata/test/testbak.dbf' size 10M extent management local segment space management auto
其實控制檔案和spfile同時被自動備份了
檢視閃回區是否存在自動備份檔案
[root@oracle11g 2015_02_02]# ls -lrttotal 19360-rw-r----- 1 oracle oinstall 9895936 Feb 2 00:17 o1_mf_s_870567448_bdwndtqk_.bkp
3.人為刪除所有控制檔案
[root@oracle11g test]# ls -lrttotal 2213868-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control03.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control02.ctl-rw-r----- 1 oracle oinstall 9814016 Feb 1 22:42 control01.ctl[root@oracle11g test]# rm -rf control*.ctl[root@oracle11g test]# ls -lrttotal 2185068-rw-r----- 1 oracle oinstall 20979712 Feb 1 11:37 temp01.dbf-rw-r----- 1 oracle oinstall 11804672 Feb 1 22:31 users01.dbf-rw-r----- 1 oracle oinstall 52436992 Feb 1 22:31 test01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo03.log-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:31 redo02.log-rw-r----- 1 oracle oinstall 104865792 Feb 1 22:31 example01.dbf-rw-r----- 1 oracle oinstall 576724992 Feb 1 22:37 sysaux01.dbf-rw-r----- 1 oracle oinstall 492838912 Feb 1 22:42 undotbs01.dbf-rw-r----- 1 oracle oinstall 838868992 Feb 1 22:42 system01.dbf-rw-r----- 1 oracle oinstall 52429312 Feb 1 22:42 redo01.log
4.人為將資料庫異常終止
[root@oracle11g test]# ps -ef | grep smonoracle 3068 1 0 22:30 ? 00:00:00 ora_smon_testroot 3179 3123 0 22:45 pts/3 00:00:00 grep smon[root@oracle11g test]# kill -9 3068
5.將資料庫啟動到nomount狀態
SQL> startup nomountORACLE instance started. Total System Global Area 327155712 bytesFixed Size 1273516 bytesVariable Size 138412372 bytesDatabase Buffers 184549376 bytesRedo Buffers 2920448 bytes
6.恢復控制檔案
RMAN> restore controlfile from autobackup; Starting restore at 02-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=148 devtype=DISK recovery area destination: /u01/app/oracle/flash_recovery_areadatabase name (or database unique name) used for search: TESTchannel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_02/o1_mf_s_870567448_bdwndtqk_.bkpchannel ORA_DISK_1: control file restore from autobackup completeFinished restore at 02-FEB-15
在上面的還原控制檔案的過程可以看到如下內容說明是使用儲存在閃回區中的控制檔案自動備份來還原控制檔案
channel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_02/o1_mf_s_870567448_bdwndtqk_.bkp
磁帶備份在還原控制檔案後不會自動執行crosscheck。如果正使用磁帶備份，那麼在還原控制檔案並將資料庫置於mount狀態後，必須手工執行crosscheck.
RMAN> CROSSCHECK BACKUP DEVICE TYPE SBT;
7.執行完全恢復
RMAN> sql 'alter database mount'; sql statement: alter database mountreleased channel: ORA_DISK_1 RMAN> recover database; Starting recover at 01-FEB-15Starting implicit crosscheck backup at 01-FEB-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked 8 objectsFinished implicit crosscheck backup at 01-FEB-15 Starting implicit crosscheck copy at 01-FEB-15using channel ORA_DISK_1Crosschecked 6 objectsFinished implicit crosscheck copy at 01-FEB-15 searching for all files in the recovery areacataloging files...no files cataloged using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/oradata/test/redo03.logarchive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/oradata/test/redo01.logarchive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=3archive log filename=/u01/app/oracle/oradata/test/redo01.log thread=1 sequence=4media recovery complete, elapsed time: 00:00:01Finished recover at 01-FEB-15 RMAN> sql 'alter database open resetlogs'; sql statement: alter database open resetlogsnew incarnation of database registered in recovery catalogstarting full resync of recovery catalogfull resync complete
4、 缺失歸檔日誌的情況下的恢復
眾所周知，恢復控制檔案時“recover database”命令可能需要使用歸檔日誌。所謂缺失歸檔日誌，是指控制檔案從備份還原之後，在執行“recover database”命令恢復時報告找不到相應的日誌導致恢復終止的情況。
這種情況下的恢復操作主要步驟如下：
① 首先還原控制檔案，方式不限
② 執行“recover database”命令將報RMAN-06054錯誤，即找不到某歸檔日誌
③ 檢視相關的動態效能檢視，對問題定位，確認問題與控制檔案，而不是資料檔案相關（與資料檔案相關必須進行不完全恢復）
④ 利用create controlfile 命令重建控制檔案
⑤ 再次執行“recover database”命令，還會報RMAN-06054錯誤，這次是找不到另一個歸檔日誌，其序列號應該大於第二步中的
⑥ 檢視v$log檢視確定第5步中所要的是哪個日誌
⑦ 執行SQLPLUS的”recover database using backup controlfile“命令，等”Specify log：“提示符出現後給出正確的線上日誌路徑，直到命令成功結束。
⑧ 以resetlogs方式開啟資料庫
⑨ 由於建立的控制檔案內不會有臨時資料檔案的資訊，需要重新將其新增回臨時表空間
⑩ 將控制檔案內其他丟失的資訊用catalog和configure等命令再新增回去。
一、當前current日誌序列號為：5，此時進行控制檔案備份
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL>
RMAN> backup current controlfile;
Starting backup at 2015-02-04 16:28:13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2015-02-04 16:28:14
channel ORA_DISK_1: finished piece 1 at 2015-02-04 16:28:15
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1vpuel4t_1_1 tag=TAG20150204T162813 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-02-04 16:28:15
RMAN>
二、多次切換日誌後，現在的CURRENT日誌是20號，所有控制檔案丟失並且第15號歸檔日誌丟失，資料庫啟動後停留在了nomount狀態：
SQL> alter system switch logfile;
。。。。。。。。
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL>
RMAN> delete archivelog sequence 15;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=257 device type=DISK
List of Archived Log Copies for database with db_unique_name LILOVE
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - -------------------
44 1 15 X 2015-02-04 16:29:58
Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_15_870711361.dbf
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_15_870711361.dbf RECID=44 STAMP=870798598
Deleted 1 objects
RMAN>
[root@rhel6_lhr ~]# ll /u01/app/oracle/oradata/utf8test/*
-rw-r----- 1 oracle asmadmin 10076160 Feb 4 16:40 /u01/app/oracle/oradata/utf8test/control01.ctl
-rw-r----- 1 oracle oinstall 10076160 Feb 4 16:40 /u01/app/oracle/oradata/utf8test/control02.ctl
-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/redo01.log
-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:40 /u01/app/oracle/oradata/utf8test/redo02.log
-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/redo03.log
-rw-r----- 1 oracle asmadmin 608182272 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/sysaux01.dbf
-rw-r----- 1 oracle asmadmin 775954432 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/system01.dbf
-rw-r----- 1 oracle asmadmin 10493952 Feb 3 16:15 /u01/app/oracle/oradata/utf8test/tbs_read01.dbf
-rw-r----- 1 oracle asmadmin 20979712 Feb 4 11:15 /u01/app/oracle/oradata/utf8test/temp01.dbf
-rw-r----- 1 oracle asmadmin 52436992 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/undotbs01.dbf
-rw-r----- 1 oracle asmadmin 10493952 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/users01.dbf
[root@rhel6_lhr ~]# rm -rf /u01/app/oracle/oradata/utf8test/control0*
[root@rhel6_lhr ~]# ll /u01/app/oracle/oradata/utf8test/*
-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/redo01.log
-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:40 /u01/app/oracle/oradata/utf8test/redo02.log
-rw-r----- 1 oracle asmadmin 52429312 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/redo03.log
-rw-r----- 1 oracle asmadmin 608182272 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/sysaux01.dbf
-rw-r----- 1 oracle asmadmin 775954432 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/system01.dbf
-rw-r----- 1 oracle asmadmin 10493952 Feb 3 16:15 /u01/app/oracle/oradata/utf8test/tbs_read01.dbf
-rw-r----- 1 oracle asmadmin 20979712 Feb 4 11:15 /u01/app/oracle/oradata/utf8test/temp01.dbf
-rw-r----- 1 oracle asmadmin 52436992 Feb 4 16:39 /u01/app/oracle/oradata/utf8test/undotbs01.dbf
-rw-r----- 1 oracle asmadmin 10493952 Feb 4 16:30 /u01/app/oracle/oradata/utf8test/users01.dbf
[root@rhel6_lhr ~]#
SQL> startup force;
ORACLE instance started.
Total System Global Area 501059584 bytes
Fixed Size 2229744 bytes
Variable Size 356518416 bytes
Database Buffers 134217728 bytes
Redo Buffers 8093696 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL>
告警檔案報錯：
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/utf8test/control02.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/utf8test/control01.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
三、下面，我們開始恢復：
RMAN> restore controlfile from '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1vpuel4t_1_1';
Starting restore at 2015-02-04 16:44:10
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/utf8test/control01.ctl
output file name=/u01/app/oracle/oradata/utf8test/control02.ctl
Finished restore at 2015-02-04 16:44:11
RMAN>
檢視控制檔案的確已經恢復：
[root@rhel6_lhr ~]# ll /u01/app/oracle/oradata/utf8test/con*
-rw-r----- 1 oracle asmadmin 10076160 Feb 4 16:44 /u01/app/oracle/oradata/utf8test/control01.ctl
-rw-r----- 1 oracle asmadmin 10076160 Feb 4 16:44 /u01/app/oracle/oradata/utf8test/control02.ctl
[root@rhel6_lhr ~]#
四、下面我們掛載資料庫：
RMAN> mount database;
database mounted
released channel: ORA_DISK_1
RMAN>
五、下邊恢復資料庫將報錯，表示找不到15號歸檔檔案：
RMAN> recover database;
Starting recover at 2015-02-04 16:47:55
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
datafile 5 not processed because file is read-only
starting media recovery
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/oradata/utf8test/redo03.log
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/oradata/utf8test/redo01.log
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/oradata/utf8test/redo02.log
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_870711361.dbf thread=1 sequence=5
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_870711361.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_7_870711361.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_8_870711361.dbf thread=1 sequence=8
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_9_870711361.dbf thread=1 sequence=9
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_10_870711361.dbf thread=1 sequence=10
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_11_870711361.dbf thread=1 sequence=11
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_12_870711361.dbf thread=1 sequence=12
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_13_870711361.dbf thread=1 sequence=13
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_14_870711361.dbf thread=1 sequence=14
unable to find archived log
archived log thread=1 sequence=15
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/04/2015 16:47:58
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 15 and starting SCN of 1927288
RMAN>
若此時開啟資料庫，將報很多的錯誤：
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/04/2015 16:50:38
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/04/2015 16:50:49
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/utf8test/system01.dbf'
六、分析原因，首先檢視目前已知的歸檔檔案最大的日誌序列號是多少？
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
20
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL> select GROUP#,SEQUENCE#,MEMBERS,STATUS,ARCHIVED from v$log;
GROUP# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------------- ---
1 4 1 INACTIVE YES
3 3 1 INACTIVE YES
2 5 1 CURRENT NO
SQL>
答案為20，如果歸檔已經是20了，那麼current日誌一定是大於20的，而我的資料庫的線上日誌組數量為3個，也就是說線上日誌的最小序列號大於17，進而得知所有資料檔案的完全檢查點必然超過了17號日誌的最後一條重做記錄。那麼結論就是資料檔案最多隻需要17號之後的日誌就能將恢復完成。
那麼控制檔案是從幾號開始恢復的呢？由v$log可知是從5號開始恢復的，恢復到15號日誌的時候報錯了，所以我們只需要讓控制檔案放棄17號就可以順利過關了。這個方法就是使用”create controlfile“建立一個新的控制檔案。這個新的控制檔案不知道current日誌的序列號，不會強制所要任何日誌對其恢復。
首先生成建立命令並重啟至nomount狀態：
SQL> alter database backup controlfile to trace as '/home/oracle/ctl.txt';
Database altered.
SQL> startup force nomount;
ORACLE instance started.
Total System Global Area 501059584 bytes
Fixed Size 2229744 bytes
Variable Size 356518416 bytes
Database Buffers 134217728 bytes
Redo Buffers 8093696 bytes
SQL>
我們在trace檔案中得到並執行noresetlogs版本的”create controlfile“命令：
CREATE CONTROLFILE REUSE DATABASE "lilove" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/utf8test/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/utf8test/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/utf8test/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/utf8test/system01.dbf',
'/u01/app/oracle/oradata/utf8test/sysaux01.dbf',
'/u01/app/oracle/oradata/utf8test/undotbs01.dbf',
'/u01/app/oracle/oradata/utf8test/users01.dbf'
CHARACTER SET AL32UTF8
;
將以上命令在sqlplus中執行，等”Control file created.“出現，資料庫已經自動mount了。然後再執行recover database命令就將至少從17號日誌開始，越過了15號這個阻礙：
RMAN> recover database;
Starting recover at 2015-02-04 17:21:17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
starting media recovery
unable to find archived log
archived log thread=1 sequence=20
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/04/2015 17:21:18
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 20 and starting SCN of 1927308
RMAN>
從結果得到，15號不用了，但是報20號找不到，而20號歸檔是存在的，是線上日誌，導致此問題的原因是新建立的控制檔案有一個缺陷：使用這種控制檔案恢復時RMAN通道只會一直地找歸檔日誌，而無視線上日誌。所以，恢復到尾聲階段的時候一定會報RMAN-06054錯誤，此時再查下v$log：
SQL> select GROUP#,SEQUENCE#,MEMBERS,STATUS,ARCHIVED from v$log;
GROUP# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------------- ---
1 19 1 INACTIVE NO
3 18 1 INACTIVE NO
2 20 1 CURRENT NO
SQL>
原來20號是線上日誌，接下來使用sqlplus的”recover database using backup controlfile“命令，可以手動指定恢復過程中所使用的日誌，然後resetlogs開啟資料庫：
SQL> recover database using backup controlfile;
ORA-00279: change 1927308 generated at 02/04/2015 16:30:05 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_20_870711361.dbf
ORA-00280: change 1927308 for thread 1 is in sequence #20
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/utf8test/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
最後根據得到的控制檔案trace中的內容執行如下語句：
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 3 DAYS');
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/app/oracle/changetracking/rman_change_track.ctf' REUSE;
ALTER DATABASE RENAME FILE 'MISSING00005' TO '/u01/app/oracle/oradata/utf8test/tbs_read01.dbf';
ALTER TABLESPACE "TBS_READ" ONLINE;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/utf8test/temp01.dbf' REUSE;
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
VARIABLE RECNO NUMBER;
SQL>
PL/SQL procedure successfully completed.
SQL> SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 3 DAYS');
PL/SQL procedure successfully completed.
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/app/oracle/changetracking/rman_change_track.ctf' REUSE;
Database altered.
SQL> ALTER DATABASE RENAME FILE 'MISSING00005' TO '/u01/app/oracle/oradata/utf8test/tbs_read01.dbf';
Database altered.
SQL> ALTER TABLESPACE "TBS_READ" ONLINE;
Tablespace altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/utf8test/temp01.dbf' REUSE;
Tablespace altered.
SQL>
最後不要忘記全備資料庫。
①、 以noresetlogs結尾
使用控制檔案的快照進行恢復，其實也是一種採用備份來進行恢復的策略。
SYS@OCPLHR1> select name from v$controlfile;
NAME
----------------------------------------------------------------------------
/u01/app/oracle/oradata/OCPLHR1/control01.ctl
/u01/app/oracle/fast_recovery_area/OCPLHR1/control02.ctl
[oracle@OCPLHR dbs]$ ll
total 9796
-rw-rw---- 1 oracle oinstall 1544 Mar 25 21:00 hc_OCPLHR1.dat
-rw-rw---- 1 oracle oinstall 1544 Mar 22 20:02 hc_OCPLHR2.dat
-rw-r----- 1 oracle oinstall 24 Mar 25 20:26 lkDUMMY
-rw-r----- 1 oracle oinstall 24 Jan 17 20:08 lkOCPLHR1
-rw-r----- 1 oracle oinstall 24 Jan 17 20:16 lkOCPLHR2
-rw-r----- 1 oracle oinstall 1536 Mar 25 20:09 orapwOCPLHR1
-rw-r----- 1 oracle oinstall 1536 Jan 17 20:25 orapwOCPLHR2
-rw-r----- 1 oracle oinstall 9977856 Mar 25 20:58 snapcf_OCPLHR1.f
-rw-r----- 1 oracle oinstall 3584 Mar 25 20:54 spfileOCPLHR1.ora
-rw-r----- 1 oracle oinstall 2560 Mar 25 19:54 spfileOCPLHR2.ora
[oracle@OCPLHR dbs]$ cp snapcf_OCPLHR1.f /u01/app/oracle/oradata/OCPLHR1/control01.ctl
[oracle@OCPLHR dbs]$ cp snapcf_OCPLHR1.f /u01/app/oracle/fast_recovery_area/OCPLHR1/control02.ctl
[oracle@OCPLHR dbs]$
[oracle@OCPLHR dbs]$
[oracle@OCPLHR dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 25 21:01:41 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: OCPLHR1 (not mounted)
RMAN> alter database mount;
using target database control file instead of recovery catalog
database mounted
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/25/2018 21:02:09
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open RESETLOGS;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/25/2018 21:02:17
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/OCPLHR1/system01.dbf'
RMAN> recover datafile 1;
Starting recover at 2018-03-25 21:02:30
Starting implicit crosscheck backup at 2018-03-25 21:02:30
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
Crosschecked 28 objects
Finished implicit crosscheck backup at 2018-03-25 21:02:31
Starting implicit crosscheck copy at 2018-03-25 21:02:31
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 2018-03-25 21:02:32
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/25/2018 21:02:32
RMAN-06067: RECOVER DATABASE required with a backup or created control file
RMAN> recover database;
Starting recover at 2018-03-25 21:03:17
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/OCPLHR1/redo01.log
archived log file name=/u01/app/oracle/oradata/OCPLHR1/redo01.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:01
Finished recover at 2018-03-25 21:03:18
RMAN> alter database open ;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/25/2018 21:03:32
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open NORESETLOGS;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "resetlogs, ;"
RMAN-01008: the bad identifier was: NORESETLOGS
RMAN-01007: at line 1 column 21 file: standard input
RMAN> alter database open RESETLOGS;
database opened
RMAN>
一.1.1.4 重建控制檔案---無備份情況下的恢復
alter database backup controlfile to trace as '/home/oracle/aa.txt';
告警資訊：
Sun Mar 25 21:06:42 2018
alter database backup controlfile to trace as '/home/oracle/aa.txt'
Completed: alter database backup controlfile to trace as '/home/oracle/aa.txt'
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OCPLHR1" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/OCPLHR1/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/OCPLHR1/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/OCPLHR1/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/OCPLHR1/system01.dbf',
'/u01/app/oracle/oradata/OCPLHR1/sysaux01.dbf',
'/u01/app/oracle/oradata/OCPLHR1/undotbs01.dbf',
'/u01/app/oracle/oradata/OCPLHR1/users01.dbf',
'/u01/app/oracle/oradata/OCPLHR1/example01.dbf'
CHARACTER SET ZHS16GBK
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 3');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/bak/cf_%F.ctl''');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 6
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DATAFILE BACKUP COPIES FOR DEVICE TYPE','DISK TO 1');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/OCPLHR1/archivelog/2018_03_25/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/OCPLHR1/archivelog/2018_03_25/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/OCPLHR1/archivelog/2018_03_25/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/OCPLHR1/archivelog/2018_03_25/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/OCPLHR1/archivelog/2018_03_25/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Block change tracking was enabled, so re-enable it now.
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/home/oracle/bct_ocplhr1.log' REUSE;
-- Set Database Guard and/or Supplemental Logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/OCPLHR1/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
一、 重建控制檔案resetlogs和noresetlogs的區別重建控制檔案又分兩種情況：resetlogs 和noresetlogs。使用使用resetlogs 重建控制檔案後，RMAN恢復又會涉及到incarnation 問題，resetlogs只需要資料檔案在位，而Noresetlogs 就不會出現incarnation的問題，但是必須是資料檔案和online log檔案都在位。
如何選擇哪個指令碼來恢復控制檔案的關鍵就在於：
1.Set NORESETLOGS case
The following commands will create a new control file and use it to open the database.
Data used by Recovery Manager will be lost.
Additional logs may be required for media recovery of offline.
Use this only if the current versions of all online logs are available.
2.Set RESETLOGS case
The following commands will create a new control file and use it to open the database.
Data used by Recovery Manager will be lost.
The contents of online logs will be lost and all backups will be invalidated.
Use this only if online logs are damaged.
一：注意事項：
1 指定reuse
表明被初始化引數CONTROL_FILES 識別的控制檔案能夠被覆蓋使用。如果忽略該引數，任何已經存在的控制檔案被資料庫檢測到，則返回一個報錯。
2 指定SET DATABASE
表明要更改資料庫的名字，名字長度能達到8個位元組。除此之外，你必須指定resetlogs語句，如果你想重新命名資料庫的名字，並保留已經存在的日誌檔案，則建立控制檔案語句執行後
使用alter database recover using bakcup controlfile 語句執行一個完全資料庫恢復。
3 指定resetlogs
indicate 忽略日誌檔案內容，或者日誌檔案不存在。
指定datafile
除了只讀表空間的檔案（可以之後新增）和臨時表空間的資料檔案，列出所有資料檔案，就算這些檔案需要進行恢復。
4 ARCHIVELOG | NOARCHIVELOG
如果忽略了ARCHIVELOG | NOARCHIVELOG oracle預設採用非歸檔模式。
二實驗步驟：
1 以noresetlogs方式建立控制檔案，控制檔案內容
[oracle@oracle backup]$ cat control.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CRM" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/CRM/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/oracle/CRM/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/oracle/CRM/redo03.log' SIZE 200M BLOCKSIZE 512,
GROUP 4 '/oracle/CRM/redo02.dbf' SIZE 200M BLOCKSIZE 512
DATAFILE
'/oracle/CRM/system01.dbf',
'/oracle/CRM/sysaux01.dbf',
'/oracle/CRM/undotbs01.dbf',
'/backup/users01.dbf',
'/oracle/CRM/pos.dbf',
'/oracle/CRM/erp.dbf',
'/oracle/CRM/user01.dbf',
'/oracle/CRM/undotbs02.dbf'
CHARACTER SET ZHS16GBK
;
2 以resetlogs方式建立控制檔案，控制檔案內容
cat control.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CRM" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/CRM/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/oracle/CRM/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/oracle/CRM/redo03.log' SIZE 200M BLOCKSIZE 512,
GROUP 4 '/oracle/CRM/redo02.dbf' SIZE 200M BLOCKSIZE 512
DATAFILE
'/oracle/CRM/system01.dbf',
'/oracle/CRM/sysaux01.dbf',
'/oracle/CRM/undotbs01.dbf',
'/backup/users01.dbf',
'/oracle/CRM/pos.dbf',
'/oracle/CRM/erp.dbf',
'/oracle/CRM/user01.dbf',
'/oracle/CRM/undotbs02.dbf'
CHARACTER SET ZHS16GBK
;
3 恢復過程
noresetlogs方式
SQL> @/backup/control.sql
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2226072 bytes
Variable Size 1006635112 bytes
Database Buffers 234881024 bytes
Redo Buffers 8921088 bytes
Control file created.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> alter database open;
Database altered.
resetlogs方式
SQL> @/backup/control.sql
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2226072 bytes
Variable Size 1006635112 bytes
Database Buffers 234881024 bytes
Redo Buffers 8921088 bytes
Control file created.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile;
ORA-00279: change 2526007 generated at 08/26/2012 01:13:10 needed for thread 1
ORA-00289: suggestion : /oracle/archive/1_8_791790817.dbf
ORA-00280: change 2526007 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/oracle/archive/1_8_791790817.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/oracle/archive/1_8_791790817.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2526007 generated at 08/26/2012 01:13:10 needed for thread 1
ORA-00289: suggestion : /oracle/archive/1_8_791790817.dbf
ORA-00280: change 2526007 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
重建控制檔案時resetlogs與noresetlogs的使用情況
控制檔案中記錄著資料庫的資料檔案,日誌檔案,備份資料等資訊,更為重要的,控制檔案中還記錄了資料庫的檢查點
和scn資訊,這些資訊在資料恢復的過程中將起到關鍵性作用.
一個正常執行的資料庫,通常控制檔案都存在多份映象,這些映象的內容是完全相同的,oracle預設就建立多份控制
檔案更說明了控制檔案的重要:
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/oradata/jingyong/control01.ctl
/u01/app/oracle/product/11.2.0/oradata/jingyong/control02.ctl
可以透過如下一條命令將控制檔案的建立語句備份到跟蹤檔案中:
SQL> alter database backup controlfile to trace;
Database altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_18818.trc
SQL> host sz /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_18818.trc
rz
Starting zmodem transfer. Press Ctrl+C to cancel.
100% 8 KB 8 KB/s 00:00:01 0 Errors
此跟蹤檔案中會記錄控制檔案的建立指令碼,指令碼包含兩個主要的段落,其中一段如下所示:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JINGYONG" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'
CHARACTER SET ZHS16GBK
;
當資料庫處於nomount狀態下時,可以透過執行這段指令碼建立控制檔案,控制檔案會自動建立到引數檔案中
記錄控制檔案的位置(原來的控制檔案在建立過程會被覆蓋).這裡需要理解的一個主要選項是:
noresetlogs/resetlogs.在跟蹤檔案中包含如下注釋,詳細解釋了這兩個選項的含義:
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script. file, edited as necessary, and executed when there is a
-- need to re-create the control file.
當資料庫當前的redo log都可用時,可以透過noresetlogs引數重建控制檔案,此時oracle能夠從日誌檔案中
讀取redo資訊,記錄到控制檔案中,由於redo中記錄的資訊足以重演所有提交成功的事務,所以最終能夠實現
完全恢復,成功開啟資料庫,這時的資料庫就如同進行了一次斷電之後的例項恢復,資料沒有損失,重做日誌
可以繼續向前寫入:
下面測試來看一下以noresetlogs重建控制檔案進行資料庫恢復的過程
先在資料庫正常執行狀態下對控制檔案執行一次轉儲:
SQL> alter session set events 'immediate trace name controlf level 12';
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19350.trc
這個轉儲檔案中將包含資料庫的檢查點,redo thread資訊,資料檔案等資訊,看一下
log file records內容:
***************************************************************************
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 3,
last-recid= 3, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x00000010 hws: 0x2 bsz: 512 nab: 0x1e flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000ea466
Low scn: 0x0000.000ea474 05/02/2013 11:40:58
Next scn: 0x0000.000ea4db 05/02/2013 11:44:07
LOG FILE #2:
name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x00000011 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000ea474
Low scn: 0x0000.000ea4db 05/02/2013 11:44:07
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x0000000f hws: 0x2 bsz: 512 nab: 0x2 flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000e8ed8
Low scn: 0x0000.000ea466 05/02/2013 11:40:52
Next scn: 0x0000.000ea474 05/02/2013 11:40:58
從記錄資訊中我們可以看到redo02.log檔案的next scn:0xffff.ffffffff,所以redo02.log檔案是當前的
日誌檔案,我們可以從v$log檢視中檢視當前的重做日誌組
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
接下來透過shutdown abort模擬一次資料庫故障:
SQL> shutdown abort;
ORACLE instance shut down.
啟動資料庫到nomount狀態,再來使用noresetlogs引數來重建控制檔案:
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 238530560 bytes
Fixed Size 1335724 bytes
Variable Size 150998612 bytes
Database Buffers 83886080 bytes
Redo Buffers 2310144 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "JINGYONG" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',
14 '/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',
15 '/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',
16 '/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',
17 '/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',
18 '/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'
19 CHARACTER SET ZHS16GBK
20 ;
Control file created.
此時再來對控制檔案進行一次轉儲,檢查log file records部分:
SQL> alter session set events 'immediate trace name controlf level 12';
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19438.trc
***************************************************************************
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 3,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x00000010 hws: 0x2 bsz: 512 nab: 0x1e flg: 0x0 dup: 1
Archive links: fwrd: 2 back: 3 Prev scn: 0x0000.000ea466
Low scn: 0x0000.000ea474 05/02/2013 11:40:58
Next scn: 0x0000.000ea4db 05/02/2013 11:44:07
LOG FILE #2:
name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x00000011 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0xa dup: 1
Archive links: fwrd: 0 back: 1 Prev scn: 0x0000.000ea474
Low scn: 0x0000.000ea4db 05/02/2013 11:44:07
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x0000000f hws: 0x2 bsz: 512 nab: 0x2 flg: 0x0 dup: 1
Archive links: fwrd: 1 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.000ea466 05/02/2013 11:40:52
Next scn: 0x0000.000ea474 05/02/2013 11:40:58
從上面的記錄我們可以看到重建的控檔案能夠從當前的日誌檔案獲得正確的SCN及時間點等資訊.同樣地,控制
檔案也能夠從資料檔案中獲得詳細的檢查點資訊:
***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max = 100, section in-use = 6,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0x12 head=9 tail=9 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:119 scn: 0x0000.000ea4db 05/02/2013 11:44:07
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.00000007 08/13/2009 23:00:53
thread:0 rba:(0x0.0.0)
.....
DATA FILE #2:
name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
creation size=0 block size=8192 status=0x12 head=8 tail=8 dup=1
tablespace 1, index=2 krfil=2 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:119 scn: 0x0000.000ea4db 05/02/2013 11:44:07
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.00000874 08/13/2009 23:00:57
thread:0 rba:(0x0.0.0)
.....
DATA FILE #3:
name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
creation size=0 block size=8192 status=0x12 head=7 tail=7 dup=1
tablespace 2, index=3 krfil=3 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:47 scn: 0x0000.000ea4db 05/02/2013 11:44:07
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.000b7982 08/13/2009 23:56:54
thread:0 rba:(0x0.0.0)
.....
DATA FILE #4:
name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0x12 head=6 tail=6 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:118 scn: 0x0000.000ea4db 05/02/2013 11:44:07
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.00004743 08/13/2009 23:01:06
thread:0 rba:(0x0.0.0)
....
DATA FILE #5:
name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
creation size=0 block size=8192 status=0x12 head=5 tail=5 dup=1
tablespace 6, index=5 krfil=5 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:43 scn: 0x0000.000ea4db 05/02/2013 11:44:07
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.000bf3fe 04/25/2013 14:05:52
thread:0 rba:(0x0.0.0)
....
DATA FILE #6:
name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
creation size=0 block size=8192 status=0x12 head=4 tail=4 dup=1
tablespace 7, index=6 krfil=6 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:11 scn: 0x0000.000ea96d 05/02/2013 12:00:47
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.000e9b4f 05/02/2013 08:43:22
thread:0 rba:(0x0.0.0)
.....
從上面的資訊可以知道由於資料庫是異常關閉的,所以資料檔案的Stop scn:為無窮大:
Stop scn: 0xffff.ffffffff,接下來對資料庫執行恢復,當恢復完成後再對控制檔案進行轉儲:
SQL> recover database;
Media recovery complete.
SQL> alter session set events 'immediate trace name controlf level 12';
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19450.trc
來觀察此跟蹤檔案中的資料檔案資訊:
***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max = 100, section in-use = 6,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0x2 head=9 tail=9 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:120 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.00000007 08/13/2009 23:00:53
thread:0 rba:(0x0.0.0)
....
DATA FILE #2:
name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
creation size=0 block size=8192 status=0x2 head=8 tail=8 dup=1
tablespace 1, index=2 krfil=2 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:120 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.00000874 08/13/2009 23:00:57
thread:0 rba:(0x0.0.0)
....
DATA FILE #3:
name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
creation size=0 block size=8192 status=0x2 head=7 tail=7 dup=1
tablespace 2, index=3 krfil=3 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:48 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.000b7982 08/13/2009 23:56:54
thread:0 rba:(0x0.0.0)
....
DATA FILE #4:
name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0x2 head=6 tail=6 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:119 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.00004743 08/13/2009 23:01:06
thread:0 rba:(0x0.0.0)
....
DATA FILE #5:
name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
creation size=0 block size=8192 status=0x2 head=5 tail=5 dup=1
tablespace 6, index=5 krfil=5 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:44 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.000bf3fe 04/25/2013 14:05:52
thread:0 rba:(0x0.0.0)
....
DATA FILE #6:
name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
creation size=0 block size=8192 status=0x2 head=4 tail=4 dup=1
tablespace 7, index=6 krfil=6 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:12 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.000e9b4f 05/02/2013 08:43:22
thread:0 rba:(0x0.0.0)
....
經過恢復之後,資料檔案達到了一致狀態,checkpoint scn(0x0000.000efd7d)和Stop scn(0x0000.000efd7d)
達到了一致,此時資料庫就完成了恢復,資料庫可以順利啟動:
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/11.2.0/oradata/jingyong/temp01.dbf'
2 SIZE 30M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 40M;
Tablespace altered.
現在我們來實驗使用resetlogs方式來重建控制檔案:
模擬資料庫故障
SQL> shutdown abort;
ORACLE instance shut down.
以resetlogs來重建控制檔案
SQL> startup nomount
ORACLE instance started.
Total System Global Area 238530560 bytes
Fixed Size 1335724 bytes
Variable Size 150998612 bytes
Database Buffers 83886080 bytes
Redo Buffers 2310144 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "JINGYONG" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',
14 '/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',
15 '/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',
16 '/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',
17 '/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',
18 '/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'
19 CHARACTER SET ZHS16GBK
20 ;
Control file created.
此時對控制檔案進行一次轉儲
SQL> alter session set events 'immediate trace name controlf level 12';
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19598.trc
觀察轉儲的跟蹤檔案中的log file record的資訊:
***************************************************************************
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 3,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00000000 01/01/1988 00:00:00
Next scn: 0x0000.00000000 01/01/1988 00:00:00
LOG FILE #2:
name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00000000 01/01/1988 00:00:00
Next scn: 0x0000.00000000 01/01/1988 00:00:00
LOG FILE #3:
name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x2 flg: 0xb dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00000000 01/01/1988 00:00:00
Next scn: 0x0000.00000000 01/01/1988 00:00:00
從上面的資訊可以看到此時控制檔案中的日誌資訊都是空的,oracle認為resetlogs方式下,當前的日誌檔案
已經損壞,那麼就意味著oracle可能會丟失提交成功的資料,恢復將是一次不完全的介質恢復.
此時的資料檔案資訊如下:
***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max = 100, section in-use = 6,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0x12 head=9 tail=9 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:123 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.00000007 08/13/2009 23:00:53
thread:0 rba:(0x0.0.0)
....
DATA FILE #2:
name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
creation size=0 block size=8192 status=0x12 head=8 tail=8 dup=1
tablespace 1, index=2 krfil=2 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:123 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.00000874 08/13/2009 23:00:57
thread:0 rba:(0x0.0.0)
....
DATA FILE #3:
name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
creation size=0 block size=8192 status=0x12 head=7 tail=7 dup=1
tablespace 2, index=3 krfil=3 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:51 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.000b7982 08/13/2009 23:56:54
thread:0 rba:(0x0.0.0)
....
DATA FILE #4:
name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0x12 head=6 tail=6 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:122 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.00004743 08/13/2009 23:01:06
thread:0 rba:(0x0.0.0)
....
DATA FILE #5:
name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
creation size=0 block size=8192 status=0x12 head=5 tail=5 dup=1
tablespace 6, index=5 krfil=5 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:47 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.000bf3fe 04/25/2013 14:05:52
thread:0 rba:(0x0.0.0)
....
DATA FILE #6:
name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
creation size=0 block size=8192 status=0x12 head=4 tail=4 dup=1
tablespace 7, index=6 krfil=6 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:15 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.000e9b4f 05/02/2013 08:43:22
thread:0 rba:(0x0.0.0)
....
從上面的資訊可以知道由於資料庫是異常關閉的,所以資料檔案的Stop scn:為無窮大:
Stop scn: 0xffff.ffffffff
不完全恢復最終要求資料庫透過resetlogs方式開啟,resetlogs將會強制清空或重建聯機重做日誌檔案.
此時執行恢復必須使用backup controlfile選項,否則將會報錯:
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile;
ORA-00279: change 982400 generated at 05/02/2013 12:53:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/flash_recovery_area/JINGYONG/archivelog/2013_05_0
2/o1_mf_1_18_%u_.arc
ORA-00280: change 982400 for thread 1 is in sequence #18
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
如果線上日誌操壞,那麼恢復到最後,oracle提示的最後一個歸檔日誌將是不存在的(如果線上日誌沒有
損壞,則可以指定線上日誌檔案執行恢復),此時可以輸入cancel取消恢復,然後可以強制開啟資料庫:
alter database open resetlogs;
SQL> recover database using backup controlfile;
ORA-00279: change 1003572 generated at 05/02/2013 13:20:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/flash_recovery_area/JINGYONG/archivelog/2013_05_0
2/o1_mf_1_19_%u_.arc
ORA-00280: change 1003572 for thread 1 is in sequence #19
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/11.2.0/oradata/jingyong/temp01.dbf'
2 SIZE 30M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 40M;
Tablespace altered.
二、 線上日誌無損情況下的重建
然後就是新增臨時資料檔案，註冊備份的資訊，最後做備份等後續操作。
三、 線上日誌損壞情況下的重建
一.1.1.1 OCP
【OCP真題1】 Are using Recovery Manager (RMAN) with a recovery catalog to perform the backup operation at regular intervals使用恢復管理器（RMAN）對恢復目錄定期執行備份操作
Set the control file autobackup to “on” 設定控制檔案自動備份為“開”
Are maintaining image copies of the database files維持資料庫映像檔案的副本
You have lost the server parameter file (SPFILE) and the control file. Which option must you consider before restoring the SPFILIE: and the control file by using the control file autobackup? 你已經失去了伺服器引數檔案（SPFILE）和控制檔案。恢復SPFILIE之前你認為哪個選項必須考慮：使用控制檔案自動備份的控制檔案？
A. setting DBID for the database
B. using the RMAN SWITCH command
C. using the RMAN SWITCH command使用RMAN切換命令 B和C一樣
D. starting up the database Instance In the NOMOUNT state
Answer: C
【OCP真題2】 Your database is up and running and one of your three control files is accidentally erased. You start RMAN and run the following command:
RESTORE CONTROLFILE FROM AUTOBACKUP;
Which of the following statements is true? (Choose all that apply.)
A. The command restores only the missing control file.
B. The command restores all the control files.
C. The command fails because the database is running.
D. This is the correct way to address this problem.
E. This is not the correct way to address this problem.
Answer: CE
【OCP真題3】 You have discovered that one of three control files has been lost. What steps would you follow to recover that control file? 你已經發現三個控制檔案之一已丟失。為恢復控制檔案，你會遵循以下什麼樣的步驟？
a. Shut down the database. 關閉資料庫
b. Restore a control-file copy from backup media.
c. Use the create control file command to create a new control file.
d. Copy the backup control file into place.
e. Create a new copy of the control file from one of the surviving control files. 從一個倖存的控制檔案建立一個新的控制檔案的副本。
f. Recover the database using the recover database using backup controlfile command.
g. Start up the database. 啟動資料庫。
A. a, b, f, g
B. c, f, g
C. a, d, f, g
D. a, f, g
E. a, e, g
關閉資料庫; 從一個倖存的控制檔案建立一個新的控制檔案的副本; 啟動資料庫
Answer: E
【OCP真題4】 Your database is up and running and one of your three control files is accidentally erased. You start RMAN and run the following command: 你的資料庫在啟動和執行，你的三個控制檔案之一被意外刪除。啟動RMAN並執行以下命令：
RESTORE CONTROLFILE FROM AUTOBACKUP;從自動備份中還原控制檔案
Which of the following statements is true? (Choose all that apply .)
A. The command restores only the missing control file.
B. The command restores all the control files.
C. The command fails because the database is running. 該命令失敗，因為資料庫正在執行。
D. This is the correct way to address this problem.
E. This is not the correct way to address this problem. 這不是正確的方法來解決這個問題
This is not the correct way to address this problem. The command will fail because the database is
running. Additionally, this is not the correct way to approach the loss of one of several control files.
The better way to approach this loss is to shut down the database and simply copy one of the
surviving control files over to where the missing control file existed. 這是不是正確的方法來解決這個問題，該命令將失敗，因為資料庫正在執行。此外，這是不正確的方式來處理多個控制檔案的損失之一。更好的方式來處理這方面的損失是關閉資料庫，並簡單地複製一個倖存的控制檔案存在丟失的控制檔案的地方。
Answer:
C,E
【OCP真題5】 You have lost all your database control files. To recover them, you are going to use the results of the alter database backup controlfile to trace command. Your datafiles and your online redo logs are all intact.
Which of the following is true regarding your recovery?
A. You will need to open the database with the resetlogs command.
B. All you need to do is execute the trace file from SQL*Plus and it will perform the recovery for you
C. You will use the resetlogs version of the create controlfile command.
D. You will use the noresetlogs version of the create controlfile command.
E. You will use the trace file to create a backup control file, and then you will recover the database with the recover database using backup controlfile command
Answer: D
About Me
.............................................................................................................................................
● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除
● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址：http://www.cnblogs.com/lhrbest
● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● DBA寶典今日頭條號地址：
.............................................................................................................................................
● QQ群號：230161599（滿）、618766405
● 微信群：可加我微信，我拉大家進群，非誠勿擾
● 聯絡我請加QQ好友（646634621），註明新增緣由
● 於 2018-04-01 06:00 ~ 2018-04-31 24:00 在魔都完成
● 最新修改時間：2018-04-01 06:00 ~ 2018-04-31 24:00
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
.............................................................................................................................................
● 小麥苗的微店：
● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/
● 小麥苗OCP、OCM、高可用網路班：http://blog.itpub.net/26736162/viewspace-2148098/
.............................................................................................................................................
使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。
小麥苗的微信公眾號 小麥苗的DBA寶典QQ群2 《DBA筆試面試寶典》讀者群 小麥苗的微店
.............................................................................................................................................
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2152506/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 損壞控制檔案的恢復方法
- 所有控制檔案損壞的恢復--resetlogs方式
- 所有控制檔案損壞的恢復--noresetlogs方式
- 單個控制檔案損壞的恢復
- RMAN恢復控制檔案
- 恢復之單個控制檔案損壞
- 某個控制檔案損壞的恢復案例
- 一次控制檔案損壞的恢復
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- rman恢復--丟失控制檔案的恢復
- 使用rman恢復控制檔案
- cp方式恢復控制檔案
- 備份＆恢復之十三：損壞全部控制檔案
- RMAN備份恢復之控制檔案的恢復（三）
- RMAN備份恢復之控制檔案的恢復（二）
- RMAN備份恢復之控制檔案的恢復（一）
- RMAN - "丟失控制檔案的恢復"
- 控制檔案丟失的RMAN恢復
- 控制檔案恢復—從trace檔案中恢復
- 【RMAN】Oracle中如何備份控制檔案？備份控制檔案的方式有哪幾種？Oracle
- 控制檔案損壞恢復（20100402）
- 備份＆恢復之十二：損壞單個控制檔案
- rman恢復控制檔案測試--log
- 一次控制檔案損壞後的恢復經歷
- 磁碟損壞造成RMAN備份檔案有壞塊的恢復案例
- rman 恢復---歸檔丟失and資料檔案損壞
- RMAN恢復案例:無恢復目錄，丟失全部資料檔案、控制檔案、日誌檔案恢復
- 【備份與恢復】恢復受損的複用控制檔案
- 與控制檔案有關的恢復
- 手工恢復控制檔案
- 控制檔案恢復—從快照中恢復
- 控制檔案快照和rman利用快照恢復
- RMAN 恢復之控制檔案篇(1/5)
- RMAN 恢復之控制檔案篇(2/5)
- RMAN 恢復之控制檔案篇(3/5)
- RMAN 恢復之控制檔案篇(4/5)
- RMAN 恢復之控制檔案篇(5/5)
- rman備份丟失控制檔案恢復