Oracle備份與恢復【丟失控制檔案的恢復】
為加強在資料庫恢復方面的經驗，藉著空閒的時間來實踐一下各種資料檔案丟失的恢復：
【模擬單個控制檔案丟失】
rm -f control02.ctl
shutdown abort
然後複製一個控制檔案重新命名為control02.ctl即可。
【模擬全部控制檔案丟失】
rm -f control*.ctl
全部控制檔案丟失後，
執行shutdown immediate將會報錯：
sys@LYON> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/lyon/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
此時需要以shutdown abort來關閉：
sys@LYON> shutdown abort
ORACLE instance shut down.
然後啟動資料庫的時候便會發生如下錯誤：
sys@LYON> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 79694092 bytes
Database Buffers 79691776 bytes
Redo Buffers 7168000 bytes
ORA-00205: error in identifying control file, check alert log for more info
由於控制檔案載入是在mount階段，此時由於已經缺失，因此可以判斷當前資料庫已經處於nomount階段。
開始建立控制檔案(如果之前有備份過控制檔案更好)：
create controlfile reuse database lyon resetlogs archivelog
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
maxinstances 1
maxloghistory 292
logfile
group 1 '/data/lyon/redo01.log' size 50m,
group 2 '/data/lyon/redo02.log' size 50m,
group 3 '/data/lyon/redo03.log' size 50m
datafile
'/data/lyon/system01.dbf',
'/data/lyon/sysaux01.dbf',
'/data/lyon/undotbs01.dbf',
'/data/lyon/users01.dbf'
character set we8iso8859p1;
sys@LYON> create controlfile reuse database lyon resetlogs archivelog
2 maxlogfiles 16
3 maxlogmembers 3
4 maxdatafiles 100
5 maxinstances 1
6 maxloghistory 292
7 logfile
8 group 1 '/data/lyon/redo01.log' size 50m,
9 group 2 '/data/lyon/redo02.log' size 50m,
10 group 3 '/data/lyon/redo03.log' size 50m
11 datafile
12 '/data/lyon/system01.dbf',
13 '/data/lyon/sysaux01.dbf',
14 '/data/lyon/undotbs01.dbf',
15 '/data/lyon/users01.dbf'
16 character set we8iso8859p1;
Control file created.
Elapsed: 00:00:01.10
然後開始恢復資料庫：
sys@LYON> recover database using backup controlfile ;
但是出現如下報錯：
ORA-00279: change 954664 generated at 05/24/2010 14:24:27 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_1_%u_.arc
ORA-00280: change 954664 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/data/lyon/redo01.log
ORA-00342: archived log does not have expected resetlogs SCN 954663
ORA-00334: archived log: '/data/lyon/redo01.log'
意思是指定的redo log file中的scn與現有控制檔案的scn不一致
搜尋了下Google和baidu，原來是指定了錯誤的日誌檔案：
ORA-00342: archived log does not have expected resetlogs SCN string
Cause: Recovery was given a log that does not belong to current incarnation or one of the parent incarnation. There should be another log that contains the correct redo.
Action: Supply the correct log file.
於是檢查了下正確的日誌檔案：
[oracle@localhost lyon]$ ls -lrt
total 1402680
-rw-r----- 1 oracle oinstall 242229248 Mar 2 07:29 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 May 24 13:49 redo03.log
-rw-r----- 1 oracle oinstall 52429312 May 24 13:49 redo02.log
-rw-r----- 1 oracle oinstall 52429312 May 24 14:11 redo01.log
-rw-r----- 1 oracle oinstall 52429312 May 24 14:24 redo02.dbf
-rw-r----- 1 oracle oinstall 52429312 May 24 14:24 redo01.dbf
-rw-r----- 1 oracle oinstall 52429312 May 24 14:24 redo03.dbf
-rw-r----- 1 oracle oinstall 13115392 May 24 14:37 users01.dbf
-rw-r----- 1 oracle oinstall 68165632 May 24 14:37 undotbs01.dbf
-rw-r----- 1 oracle oinstall 524296192 May 24 14:37 system01.dbf
-rw-r----- 1 oracle oinstall 251666432 May 24 14:37 sysaux01.dbf
-rw-r----- 1 oracle oinstall 7258112 May 24 14:39 control03.ctl
-rw-r----- 1 oracle oinstall 7258112 May 24 14:39 control02.ctl
-rw-r----- 1 oracle oinstall 7258112 May 24 14:39 control01.ctl
好像是前面一次實驗的時候指定了dbf的字尾的redolog。
於是重新指定了下：
sys@LYON> recover database using backup controlfile until cancel;
ORA-00279: change 954664 generated at 05/24/2010 14:24:27 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_1_%u_.arc
ORA-00280: change 954664 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/data/lyon/redo01.dbf
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '/data/lyon/redo01.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/lyon/system01.dbf'
發現redo01.dbf不含有所需要的任何redo資訊。再嘗試一下redo03.dbf
sys@LYON> recover database using backup controlfile until cancel
ORA-00279: change 954664 generated at 05/24/2010 14:24:27 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_1_%u_.arc
ORA-00280: change 954664 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/data/lyon/redo03.dbf
Log applied.
Media recovery complete.
這次成功了，開啟資料庫（resetlogs）：
sys@LYON> alter database open resetlogs
2 ;
Database altered.
Elapsed: 00:00:13.40
搞定。
這次問題的解決參考了：http://space.itpub.net/67668/viewspace-353270。
總結一下：
問題的產生：
該問題主要是為了模擬控制檔案全部丟失，但是其他資料檔案仍然存在的情況。
這時shutdown immediate已經不能關閉資料庫，需要shutdown abort。
問題的解決：
1、要知道所有相關資料檔案所在的路徑或者之前有控制檔案備份，因為沒有這
些資訊便無法重建控制檔案。
（備份控制檔案：
alter database backup controlfile to trace;
alter database backup controlfile to '/data/backup/controlfile_bak.ctl';
）
2、將資料庫啟動到nomount階段，並建立控制檔案。
startup nomount
3、開始恢復資料庫，這個步驟很關鍵，要選擇正確的檔案去進行恢復：
recover database using backup controlfile until cancel;
執行那個以上命令後，會出現一個提示：
sys@LYON> recover database using backup controlfile until cancel
ORA-00279: change 954664 generated at 05/24/2010 14:24:27 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_1_%u_.arc
ORA-00280: change 954664 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
接下來是要我們指定合適的日誌檔案或歸檔檔案來進行恢復。
如果選擇了上次恢復前的日誌檔案，則會報：
ORA-00342: archived log does not have expected resetlogs SCN 954663
ORA-00334: archived log: '/data/lyon/redo01.log'
上面這兩個錯誤。
這時候要看下是不是指定的檔名錯了，我這裡是檔案的字尾指定錯了（應該是dbf，上次恢復的時候錯誤的指定了這個字尾）
於是改成正確的檔名。
此時如果對應的重做日誌中不含有恢復所需要的redo資訊：
ORA-00339: archived log does not contain any redo
則再換一個，一直換到可以完全恢復：
/data/lyon/redo03.dbf
Log applied.
Media recovery complete.
這時已經表示介質恢復已經完成了。
4、然後就是收尾的工作了：
alter database open resetlogs;
使用resetlogs的作用是重置DB的scn，使得此前所有的一致性備份都沒有用了，因此開啟資料庫後首先要做的事情就是進行完全備份。
---以下是第一次恢復的操作過程：
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/data/lyon/system01.dbf'
Elapsed: 00:00:00.03
sys@LYON> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
sys@LYON> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
sys@LYON> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
Elapsed: 00:00:00.01
sys@LYON> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Elapsed: 00:00:00.02
sys@LYON> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open
Elapsed: 00:00:00.03
sys@LYON> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/data/lyon/system01.dbf'
Elapsed: 00:00:00.02
sys@LYON> recover datafile from backup ;
ORA-02236: invalid file name
sys@LYON> recover datafile from backup '/data/backup_large/full_bak_04lefupi_1_1 ';
ORA-02236: invalid file name
sys@LYON> recover datafile from '/data/backup_large/full_bak_04lefupi_1_1';
ORA-02236: invalid file name
sys@LYON> recover datafile using backup '/data/backup_large/full_bak_04lefupi_1_ 1';
ORA-02236: invalid file name
sys@LYON> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
sys@LYON> recover database using backup controlfile;
ORA-00279: change 953574 generated at 05/24/2010 13:49:04 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_ mf_1_74_%u_.arc
ORA-00280: change 953574 for thread 1 is in sequence #74
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_74_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
sys@LYON> sys@LYON>
sys@LYON> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
sys@LYON> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 79694092 bytes
Database Buffers 79691776 bytes
Redo Buffers 7168000 bytes
Database mounted.
sys@LYON> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
sys@LYON> recover database using backup controlfile until cancel;
ORA-00279: change 953574 generated at 05/24/2010 13:49:04 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_ mf_1_74_%u_.arc
ORA-00280: change 953574 for thread 1 is in sequence #74
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_74_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/lyon/system01.dbf'
sys@LYON> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV STATUS
---------- ---------- ---------- ---------- ---------- ------ ------------------ --------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
1 1 0 52428800 1 YES UNUSED
0
3 1 0 52428800 1 YES CURRENT
0
2 1 0 52428800 1 YES UNUSED
0
Elapsed: 00:00:00.05
sys@LYON> recover database using backup controlfile until cancel;
ORA-00279: change 953574 generated at 05/24/2010 13:49:04 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_ mf_1_74_%u_.arc
ORA-00280: change 953574 for thread 1 is in sequence #74
Specify log: {=suggested | filename | AUTO | CANCEL}
/data/lyon/redo01.dbf'
ORA-01756: quoted string not properly terminated
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/lyon/system01.dbf'
sys@LYON> /
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV STATUS
---------- ---------- ---------- ---------- ---------- ------ ------------------ --------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
1 1 0 52428800 1 YES UNUSED
0
3 1 0 52428800 1 YES CURRENT
0
2 1 0 52428800 1 YES UNUSED
0
Elapsed: 00:00:00.01
sys@LYON> recover database using backup controlfile until cancel;
ORA-00279: change 953574 generated at 05/24/2010 13:49:04 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_ mf_1_74_%u_.arc
ORA-00280: change 953574 for thread 1 is in sequence #74
Specify log: {=suggested | filename | AUTO | CANCEL}
/data/lyon/redo01.dbf
ORA-00308: cannot open archived log '/data/lyon/redo01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {=suggested | filename | AUTO | CANCEL}
/data/lyon/redo01.log
Log applied.
Media recovery complete.
sys@LYON> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Elapsed: 00:00:00.01
sys@LYON> alter databse open resetlogs;
alter databse open resetlogs
*
ERROR at line 1:
ORA-00940: invalid ALTER command
Elapsed: 00:00:00.00
sys@LYON> alter database open resetlogs;
Database altered.
Elapsed: 00:00:10.72
【模擬單個控制檔案丟失】
rm -f control02.ctl
shutdown abort
然後複製一個控制檔案重新命名為control02.ctl即可。
【模擬全部控制檔案丟失】
rm -f control*.ctl
全部控制檔案丟失後，
執行shutdown immediate將會報錯：
sys@LYON> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/lyon/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
此時需要以shutdown abort來關閉：
sys@LYON> shutdown abort
ORACLE instance shut down.
然後啟動資料庫的時候便會發生如下錯誤：
sys@LYON> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 79694092 bytes
Database Buffers 79691776 bytes
Redo Buffers 7168000 bytes
ORA-00205: error in identifying control file, check alert log for more info
由於控制檔案載入是在mount階段，此時由於已經缺失，因此可以判斷當前資料庫已經處於nomount階段。
開始建立控制檔案(如果之前有備份過控制檔案更好)：
create controlfile reuse database lyon resetlogs archivelog
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
maxinstances 1
maxloghistory 292
logfile
group 1 '/data/lyon/redo01.log' size 50m,
group 2 '/data/lyon/redo02.log' size 50m,
group 3 '/data/lyon/redo03.log' size 50m
datafile
'/data/lyon/system01.dbf',
'/data/lyon/sysaux01.dbf',
'/data/lyon/undotbs01.dbf',
'/data/lyon/users01.dbf'
character set we8iso8859p1;
sys@LYON> create controlfile reuse database lyon resetlogs archivelog
2 maxlogfiles 16
3 maxlogmembers 3
4 maxdatafiles 100
5 maxinstances 1
6 maxloghistory 292
7 logfile
8 group 1 '/data/lyon/redo01.log' size 50m,
9 group 2 '/data/lyon/redo02.log' size 50m,
10 group 3 '/data/lyon/redo03.log' size 50m
11 datafile
12 '/data/lyon/system01.dbf',
13 '/data/lyon/sysaux01.dbf',
14 '/data/lyon/undotbs01.dbf',
15 '/data/lyon/users01.dbf'
16 character set we8iso8859p1;
Control file created.
Elapsed: 00:00:01.10
然後開始恢復資料庫：
sys@LYON> recover database using backup controlfile ;
但是出現如下報錯：
ORA-00279: change 954664 generated at 05/24/2010 14:24:27 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_1_%u_.arc
ORA-00280: change 954664 for thread 1 is in sequence #1
Specify log: {
/data/lyon/redo01.log
ORA-00342: archived log does not have expected resetlogs SCN 954663
ORA-00334: archived log: '/data/lyon/redo01.log'
意思是指定的redo log file中的scn與現有控制檔案的scn不一致
搜尋了下Google和baidu，原來是指定了錯誤的日誌檔案：
ORA-00342: archived log does not have expected resetlogs SCN string
Cause: Recovery was given a log that does not belong to current incarnation or one of the parent incarnation. There should be another log that contains the correct redo.
Action: Supply the correct log file.
於是檢查了下正確的日誌檔案：
[oracle@localhost lyon]$ ls -lrt
total 1402680
-rw-r----- 1 oracle oinstall 242229248 Mar 2 07:29 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 May 24 13:49 redo03.log
-rw-r----- 1 oracle oinstall 52429312 May 24 13:49 redo02.log
-rw-r----- 1 oracle oinstall 52429312 May 24 14:11 redo01.log
-rw-r----- 1 oracle oinstall 52429312 May 24 14:24 redo02.dbf
-rw-r----- 1 oracle oinstall 52429312 May 24 14:24 redo01.dbf
-rw-r----- 1 oracle oinstall 52429312 May 24 14:24 redo03.dbf
-rw-r----- 1 oracle oinstall 13115392 May 24 14:37 users01.dbf
-rw-r----- 1 oracle oinstall 68165632 May 24 14:37 undotbs01.dbf
-rw-r----- 1 oracle oinstall 524296192 May 24 14:37 system01.dbf
-rw-r----- 1 oracle oinstall 251666432 May 24 14:37 sysaux01.dbf
-rw-r----- 1 oracle oinstall 7258112 May 24 14:39 control03.ctl
-rw-r----- 1 oracle oinstall 7258112 May 24 14:39 control02.ctl
-rw-r----- 1 oracle oinstall 7258112 May 24 14:39 control01.ctl
好像是前面一次實驗的時候指定了dbf的字尾的redolog。
於是重新指定了下：
sys@LYON> recover database using backup controlfile until cancel;
ORA-00279: change 954664 generated at 05/24/2010 14:24:27 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_1_%u_.arc
ORA-00280: change 954664 for thread 1 is in sequence #1
Specify log: {
/data/lyon/redo01.dbf
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '/data/lyon/redo01.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/lyon/system01.dbf'
發現redo01.dbf不含有所需要的任何redo資訊。再嘗試一下redo03.dbf
sys@LYON> recover database using backup controlfile until cancel
ORA-00279: change 954664 generated at 05/24/2010 14:24:27 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_1_%u_.arc
ORA-00280: change 954664 for thread 1 is in sequence #1
Specify log: {
/data/lyon/redo03.dbf
Log applied.
Media recovery complete.
這次成功了，開啟資料庫（resetlogs）：
sys@LYON> alter database open resetlogs
2 ;
Database altered.
Elapsed: 00:00:13.40
搞定。
這次問題的解決參考了：http://space.itpub.net/67668/viewspace-353270。
總結一下：
問題的產生：
該問題主要是為了模擬控制檔案全部丟失，但是其他資料檔案仍然存在的情況。
這時shutdown immediate已經不能關閉資料庫，需要shutdown abort。
問題的解決：
1、要知道所有相關資料檔案所在的路徑或者之前有控制檔案備份，因為沒有這
些資訊便無法重建控制檔案。
（備份控制檔案：
alter database backup controlfile to trace;
alter database backup controlfile to '/data/backup/controlfile_bak.ctl';
）
2、將資料庫啟動到nomount階段，並建立控制檔案。
startup nomount
3、開始恢復資料庫，這個步驟很關鍵，要選擇正確的檔案去進行恢復：
recover database using backup controlfile until cancel;
執行那個以上命令後，會出現一個提示：
sys@LYON> recover database using backup controlfile until cancel
ORA-00279: change 954664 generated at 05/24/2010 14:24:27 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_1_%u_.arc
ORA-00280: change 954664 for thread 1 is in sequence #1
Specify log: {
接下來是要我們指定合適的日誌檔案或歸檔檔案來進行恢復。
如果選擇了上次恢復前的日誌檔案，則會報：
ORA-00342: archived log does not have expected resetlogs SCN 954663
ORA-00334: archived log: '/data/lyon/redo01.log'
上面這兩個錯誤。
這時候要看下是不是指定的檔名錯了，我這裡是檔案的字尾指定錯了（應該是dbf，上次恢復的時候錯誤的指定了這個字尾）
於是改成正確的檔名。
此時如果對應的重做日誌中不含有恢復所需要的redo資訊：
ORA-00339: archived log does not contain any redo
則再換一個，一直換到可以完全恢復：
/data/lyon/redo03.dbf
Log applied.
Media recovery complete.
這時已經表示介質恢復已經完成了。
4、然後就是收尾的工作了：
alter database open resetlogs;
使用resetlogs的作用是重置DB的scn，使得此前所有的一致性備份都沒有用了，因此開啟資料庫後首先要做的事情就是進行完全備份。
---以下是第一次恢復的操作過程：
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/data/lyon/system01.dbf'
Elapsed: 00:00:00.03
sys@LYON> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
sys@LYON> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
sys@LYON> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
Elapsed: 00:00:00.01
sys@LYON> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Elapsed: 00:00:00.02
sys@LYON> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open
Elapsed: 00:00:00.03
sys@LYON> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/data/lyon/system01.dbf'
Elapsed: 00:00:00.02
sys@LYON> recover datafile from backup ;
ORA-02236: invalid file name
sys@LYON> recover datafile from backup '/data/backup_large/full_bak_04lefupi_1_1 ';
ORA-02236: invalid file name
sys@LYON> recover datafile from '/data/backup_large/full_bak_04lefupi_1_1';
ORA-02236: invalid file name
sys@LYON> recover datafile using backup '/data/backup_large/full_bak_04lefupi_1_ 1';
ORA-02236: invalid file name
sys@LYON> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
sys@LYON> recover database using backup controlfile;
ORA-00279: change 953574 generated at 05/24/2010 13:49:04 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_ mf_1_74_%u_.arc
ORA-00280: change 953574 for thread 1 is in sequence #74
Specify log: {
ORA-00308: cannot open archived log
'/arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_74_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
sys@LYON> sys@LYON>
sys@LYON> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
sys@LYON> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 79694092 bytes
Database Buffers 79691776 bytes
Redo Buffers 7168000 bytes
Database mounted.
sys@LYON> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
sys@LYON> recover database using backup controlfile until cancel;
ORA-00279: change 953574 generated at 05/24/2010 13:49:04 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_ mf_1_74_%u_.arc
ORA-00280: change 953574 for thread 1 is in sequence #74
Specify log: {
ORA-00308: cannot open archived log
'/arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_74_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/lyon/system01.dbf'
sys@LYON> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV STATUS
---------- ---------- ---------- ---------- ---------- ------ ------------------ --------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
1 1 0 52428800 1 YES UNUSED
0
3 1 0 52428800 1 YES CURRENT
0
2 1 0 52428800 1 YES UNUSED
0
Elapsed: 00:00:00.05
sys@LYON> recover database using backup controlfile until cancel;
ORA-00279: change 953574 generated at 05/24/2010 13:49:04 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_ mf_1_74_%u_.arc
ORA-00280: change 953574 for thread 1 is in sequence #74
Specify log: {
/data/lyon/redo01.dbf'
ORA-01756: quoted string not properly terminated
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/lyon/system01.dbf'
sys@LYON> /
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV STATUS
---------- ---------- ---------- ---------- ---------- ------ ------------------ --------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
1 1 0 52428800 1 YES UNUSED
0
3 1 0 52428800 1 YES CURRENT
0
2 1 0 52428800 1 YES UNUSED
0
Elapsed: 00:00:00.01
sys@LYON> recover database using backup controlfile until cancel;
ORA-00279: change 953574 generated at 05/24/2010 13:49:04 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_ mf_1_74_%u_.arc
ORA-00280: change 953574 for thread 1 is in sequence #74
Specify log: {
/data/lyon/redo01.dbf
ORA-00308: cannot open archived log '/data/lyon/redo01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {
/data/lyon/redo01.log
Log applied.
Media recovery complete.
sys@LYON> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Elapsed: 00:00:00.01
sys@LYON> alter databse open resetlogs;
alter databse open resetlogs
*
ERROR at line 1:
ORA-00940: invalid ALTER command
Elapsed: 00:00:00.00
sys@LYON> alter database open resetlogs;
Database altered.
Elapsed: 00:00:10.72
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12932950/viewspace-663712/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- 【備份恢復】 丟失一個控制檔案 之恢復操作
- 【備份恢復】所有控制檔案丟失後 利用trace中的控制檔案備份執行恢復
- 【rman 備份與恢復】恢復丟失所有的控制檔案
- 備份與恢復--利用備份的控制檔案恢復
- 只有rman備份集，控制檔案丟失的恢復
- 控制檔案丟失恢復
- 【控制檔案丟失恢復】
- 備份與恢復系列 十一 控制檔案的備份與恢復
- 備份恢復實驗（1）丟失部分控制檔案
- 恢復丟失的控制檔案
- 【備份恢復】恢復 丟失已歸檔重做日誌檔案
- 【備份與恢復】控制檔案的恢復（不完全恢復）
- 恢復之丟失全部控制檔案以及備份中的控制檔案
- 非歸檔無備份下控制檔案丟失的恢復
- rman備份丟失控制檔案恢復
- 恢復之利用備份在所有控制檔案丟失情況下恢復（四）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（三）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（二）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（一）
- 控制檔案丟失恢復(二)
- 控制檔案全部丟失恢復
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- rman恢復--歸檔模式無備份，丟失資料檔案的恢復模式
- 備份恢復之資料檔案丟失
- 控制檔案全部丟失的恢復
- 控制檔案部分丟失的恢復
- 控制檔案丟失的RMAN恢復
- 備份與恢復--重建控制檔案
- 【備份與恢復】恢復受損的複用控制檔案
- 丟失全部控制檔案後從RMAN備份集中恢復示例
- 備份與恢復--資料檔案損壞或丟失
- 無備份丟失部分資料檔案和控制檔案恢復 [轉]
- 【備份恢復】丟失所有控制檔案，利用RMAN進行恢復操作
- 備份與恢復系列 九 丟失表空間資料檔案的還原與恢復
- Oracle Password檔案丟失的恢復Oracle
- RMAN備份恢復之控制檔案的恢復（三）
- RMAN備份恢復之控制檔案的恢復（二）