Oracle控制檔案在缺失歸檔日誌的情況下的恢復
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等命令再新增回去。
當前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: {=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>
最後不要忘記全備資料庫。
About Me
...............................................................................................................................
● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（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/
● QQ群：230161599 微信群：私聊
● 聯絡我請加QQ好友(646634621)，註明新增緣由
● 於 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
...............................................................................................................................
拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-1426552/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle dg 歸檔日誌恢復情況Oracle
- DG歸檔日誌缺失恢復
- 在歸檔模式下丟失日誌檔案的恢復模式
- 測試在丟失歸檔日誌的情況下，跳過部分歸檔日誌進行資料恢復資料恢復
- 恢復歸檔日誌檔案的常用方法
- 恢復案例：無歸檔，丟失全部控制檔案、日誌檔案恢復案例
- 誤刪資料檔案在沒有歸檔的情況下恢復實驗
- 【DATAGUARD】物理dg在主庫丟失歸檔檔案的情況下的恢復(七)
- 非歸檔丟失日誌檔案的恢復
- Oracle使用備份檔案集恢復歸檔日誌Oracle
- Oracle主庫歸檔丟失，備庫日誌有gap，在不重建備庫的情況下，恢復備庫Oracle
- 恢復控制檔案後，沒有最後一個歸檔日誌的備份，也沒新增歸檔日誌資訊，怎麼恢復？
- 控制檔案/歸檔日誌
- Oracle-無備份情況下，如何手動恢復控制檔案Oracle
- 丟失已歸檔日誌檔案下恢復資料庫資料庫
- 【備份恢復】恢復 丟失已歸檔重做日誌檔案
- 全備份情況下，刪除控制檔案及恢復
- 【BBED】丟失歸檔檔案情況下的恢復
- 冷備份+歸檔日誌的恢復
- Oracle歸檔日誌比聯機重做日誌小很多的情況總結Oracle
- ORACLE非歸檔下的恢復Oracle
- oracle丟失日誌檔案的恢復( 轉)Oracle
- RMAN備份與恢復（新舊控制檔案及歸檔日誌）測試
- 非歸檔模式下，丟失日誌檔案的一次恢復過程模式
- 恢復之利用備份在所有控制檔案丟失情況下恢復（四）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（三）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（二）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（一）
- 非歸檔無備份下控制檔案丟失的恢復
- oracle歸檔日誌丟失後的資料庫恢復Oracle資料庫
- RMAN恢復案例:無恢復目錄，丟失全部資料檔案、控制檔案、日誌檔案恢復
- 在歸檔下恢復系統資料檔案
- RMAN備份與恢復（新舊控制檔案及歸檔日誌）測試（敘）
- 【恢復】Redo日誌檔案丟失的恢復
- 沒有自動備份的情況下控制檔案全部丟失的恢復
- 【故障處理】DG環境主庫丟失歸檔情況下資料檔案的恢復
- 非歸檔模式下非當前日誌檔案的丟失的恢復模式
- 沒備份，歸檔日誌存在，丟失資料檔案的恢復