1. 所有online Control file毀損
2. ControlFile Autobackup OFF
3. 備份完Control file後，DBA手動刪除了一個tablespace包含Data File，所以上次的Control file備份就沒有剛刪除的Data File
環境準備：
1. 新增一個Tablespace包含一個Datafile
SQL> create tablespace "myTest01" nologging
2 datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/myTest01_DT01.dbf'
3 size 10m;
Tablespace created.
2. Switch LOG
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
3. 手動備份controlfile
RMAN> backup current controlfile;
Starting backup at 12-APR-2016 14:18:03
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=197 device type=DISK
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 12-APR-2016 14:18:05
channel ORA_DISK_1: finished piece 1 at 12-APR-2016 14:18:06
piece handle=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/01r2rr8s_1_1 tag=TAG20160412T141803 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-APR-2016 14:18:06
4. 刪除剛剛建立的Tablespace
SQL> drop tablespace "myTest01" including contents and datafiles;
Tablespace dropped.
5. 手動刪除controlfile，模擬controlfile毀損
[oracle@nofra orclnofra]$ rm control01.ctl
[oracle@nofra orclnofra]$ rm control02.ctl
6. 重新啟動DB會出現如下錯誤
SQL> startup;
ORACLE instance started.
Total System Global Area 3340451840 bytes
Fixed Size 2257840 bytes
Variable Size 1879051344 bytes
Database Buffers 1442840576 bytes
Redo Buffers 16302080 bytes
ORA-00205: error in identifying control file, check alert log for more info
還原語法如下：
1. 在RMAN
restore controlfile from '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/01r2rr8s_1_1';
2. 在RMAN
mount database;
3. 復原資料庫，解法有兩種
解法一
3-1-1. 在SQLPLUS(在此範例中是編號5的datafile有問題)
alter database datafile 5 offline;
3-1-2. 在SQLPLUS
recover database using backup controlfile;
輸入：AUTO
3-1-2 在SQLPLUS
recover database using backup controlfile;
輸入redo log檔案路徑
3-1-3. 重複執行recover database using backup controlfile，並輸入redo log檔案路徑直到出現Media recovery complete
解法二
3-2-1 在RMAN
recover database skip tablespace "myTest01";
4. 在RMAN
alter database open resetlogs;
完整執行過程：
1. 還原Control File
RMAN> restore controlfile from '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/01r2rr8s_1_1';
Starting restore at 12-APR-2016 14:21:56
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
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/orclnofra/control01.ctl
output file name=/u01/app/oracle/oradata/orclnofra/control02.ctl
Finished restore at 12-APR-2016 14:21:57
2. 將DB啟動在mount模式
RMAN> mount database;
database mounted
released channel: ORA_DISK_1
3. 恢復資料庫，會出現錯誤原因是在Control file中紀錄有剛剛建立的Tablespace但是實際上已經是不需要且被刪除了，但recover還是會一直要復原該檔案。
RMAN> recover database;
Starting recover at 12-APR-2016 14:22:48
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/12/2016 14:22:48
RMAN-06094: datafile 5 must be restored
解法一
4-1-1. 將步驟三錯誤訊息中的datafile離線
SQL> alter database datafile 5 offline;
Database altered.
4-1-2. 透過using backup controlfile來復原資料庫，在出現[Specify log]提示時輸入[AUTO]，在此例中又出現ORA-00308及ORA-27037的錯誤，但實際上並沒有損壞或是遭刪除，那就表示該資料尚未產生archive log，實際資料還在redo log中
SQL> recover database using backup controlfile;
ORA-00279: change 978477 generated at 04/12/2016 14:17:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_8_908881411.dbf
ORA-00280: change 978477 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_8_908881411.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
'/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_8_908881411.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
4-1-3. 再次透過using backup controlfile來復原資料庫，在出現[Specify log]提示時輸入redo01.log的位置，結果發現並不存在於redo01.log
SQL> recover database using backup controlfile;
ORA-00279: change 978477 generated at 04/12/2016 14:17:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_8_908881411.dbf
ORA-00280: change 978477 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orclnofra/redo01.log
ORA-00328: archived log ends at change 978453, need later change 978477
ORA-00334: archived log: '/u01/app/oracle/oradata/orclnofra/redo01.log'
4-1-4. 再次透過using backup controlfile來復原資料庫，在出現[Specify log]提示時輸入redo02.log的位置，若成功便會出現[Media recovery complete.]的訊息
ORA-00279: change 978477 generated at 04/12/2016 14:17:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_8_908881411.dbf
ORA-00280: change 978477 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orclnofra/redo02.log
Log applied.
Media recovery complete.
解法二
4-2-1. 跳過有問題的Tablespace直接復原
RMAN> recover database skip tablespace "myTest01";
Starting recover at 11-APR-2016 16:45:10using channel ORA_DISK_1
Executing: alter database datafile 6 offline
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/orclnofra/redo01.log
archived log file name=/u01/app/oracle/oradata/orclnofra/redo01.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:00
Finished recover at 11-APR-2016 16:45:10
5. 將資料庫開啟
RMAN> alter database open resetlogs;
database opened
沒有留言:
張貼留言