在恢複過程中,Oracle利用歸檔日志進行恢複,預設情況下,Oracle會在歸檔日志的産生目錄來尋找歸檔日志檔案。
如果在恢複時發現歸檔日志被備份并從歸檔目錄删除,則Oracle在恢複過程中無法找到指定的歸檔日志檔案。[@more@]
SQL> alter tablespace users offline;
Tablespace altered.
備份目前的歸檔日志
SQL> host cp /u01/app/oracle/admin/orcl/archive/*.* /u01/backup/archive
移除目前的歸檔日志
SQL> host rm /u01/app/oracle/admin/orcl/archive/*.*
用備份的datafile替換目前的
SQL> host cp /u01/backup/users01.dbf /u01/app/oracle/oradata/orcl
SQL> recover tablespace users;
ORA-00279: change 685260 generated at 01/13/2009 09:45:47 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_23_675592897.dbf
ORA-00280: change 685260 for thread 1 is in sequence #23
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log '/u01/app/oracle/admin/orcl/archive/1_23_675592897.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u01/app/oracle/admin/orcl/archive/1_23_675592897.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
這時做recovery時提示找不到歸檔日志。
SQL> host cp /u01/backup/archive/*.* /u01/app/oracle/admin/orcl/archive
SQL> recover tablespace users;
ORA-00279: change 685260 generated at 01/13/2009 09:45:47 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_23_675592897.dbf
ORA-00280: change 685260 for thread 1 is in sequence #23
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 685550 generated at 01/13/2009 10:00:56 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_24_675592897.dbf
ORA-00280: change 685550 for thread 1 is in sequence #24
ORA-00278: log file '/u01/app/oracle/admin/orcl/archive/1_23_675592897.dbf' no longer needed
for this recovery
ORA-00279: change 685552 generated at 01/13/2009 10:00:56 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_25_675592897.dbf
ORA-00280: change 685552 for thread 1 is in sequence #25
ORA-00278: log file '/u01/app/oracle/admin/orcl/archive/1_24_675592897.dbf' no longer needed
for this recovery
ORA-00279: change 685603 generated at 01/13/2009 10:02:38 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_26_675592897.dbf
ORA-00280: change 685603 for thread 1 is in sequence #26
ORA-00278: log file '/u01/app/oracle/admin/orcl/archive/1_25_675592897.dbf' no longer needed
for this recovery
Log applied.
Media recovery complete.
将歸檔日志都拷貝回來後,recovery成功。
如果歸檔目錄的空間不足,無法将歸檔日志拷貝到歸檔目錄下,則可以使用下面三種方法進行恢複。
方法一:分别指定備份的歸檔日志
SQL> host rm /u01/app/oracle/admin/orcl/archive/*.*
SQL> host cp /u01/backup/users01.dbf /u01/app/oracle/oradata/orcl
SQL> recover tablespace users;
ORA-00279: change 685260 generated at 01/13/2009 09:45:47 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_23_675592897.dbf
ORA-00280: change 685260 for thread 1 is in sequence #23
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/backup/archive/1_23_675592897.dbf
ORA-00279: change 685550 generated at 01/13/2009 10:00:56 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_24_675592897.dbf
ORA-00280: change 685550 for thread 1 is in sequence #24
ORA-00278: log file '/u01/backup/archive/1_23_675592897.dbf' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/backup/archive/1_24_675592897.dbf
ORA-00279: change 685552 generated at 01/13/2009 10:00:56 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_25_675592897.dbf
ORA-00280: change 685552 for thread 1 is in sequence #25
ORA-00278: log file '/u01/backup/archive/1_24_675592897.dbf' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/backup/archive/1_253_675592897.dbf
ORA-00308: cannot open archived log '/u01/backup/archive/1_253_675592897.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}
/u01/backup/archive/1_25_675592897.dbf
ORA-00279: change 685603 generated at 01/13/2009 10:02:38 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/orcl/archive/1_26_675592897.dbf
ORA-00280: change 685603 for thread 1 is in sequence #26
ORA-00278: log file '/u01/backup/archive/1_25_675592897.dbf' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/backup/archive/1_26_675592897.dbf
Log applied.
Media recovery complete.
第一種方法也是最簡單的一種方法,在恢複過程中直接給出每個歸檔日志包含目前路徑的檔案名。這種方
法适合需要恢複的重做日志數量不多。
方法二:
SQL> host cp /u01/backup/users01.dbf /u01/app/oracle/oradata/orcl
SQL> alter database recover automatic from '/u01/backup/archive' tablespace users;
Database altered.
SQL> alter tablespace users online;
Tablespace altered.
SQL> select count(*) from jglu.a;
COUNT(*)
----------
2
第二種方法是使用ALTER DATABASE RECOVER FROM語句指定恢複時讀取歸檔日志的目錄。
看看日志記錄的資訊,這種方法比較快捷
Tue Jan 13 10:15:43 2009
alter database recover automatic from '/u01/backup/archive' tablespace users
Tue Jan 13 10:15:43 2009
Media Recovery Start
Tue Jan 13 10:15:43 2009
Media Recovery Log /u01/backup/archive/1_23_675592897.dbf
Tue Jan 13 10:15:43 2009
Media Recovery Log /u01/backup/archive/1_24_675592897.dbf
Tue Jan 13 10:15:43 2009
Media Recovery Log /u01/backup/archive/1_25_675592897.dbf
Tue Jan 13 10:15:43 2009
Media Recovery Log /u01/backup/archive/1_26_675592897.dbf
Tue Jan 13 10:15:43 2009
Recovery of Online Redo Log: Thread 1 Group 2 Seq 27 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo02.log
Tue Jan 13 10:15:44 2009
Recovery of Online Redo Log: Thread 1 Group 3 Seq 28 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo03.log
Tue Jan 13 10:15:44 2009
Recovery of Online Redo Log: Thread 1 Group 1 Seq 29 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo01.log
Tue Jan 13 10:15:44 2009
Media Recovery Complete (orcl)
Completed: alter database recover automatic from '/u01/backup/archive' tablespace users
第三種方法:
SQL> alter tablespace users offline;
Tablespace altered.
SQL> host cp /u01/backup/users01.dbf /u01/app/oracle/oradata/orcl
SQL> set logsource '/u01/backup/archive'
SQL> recover tablespace users;
ORA-00279: change 685260 generated at 01/13/2009 09:45:47 needed for thread 1
ORA-00289: suggestion : /u01/backup/archive/1_23_675592897.dbf
ORA-00280: change 685260 for thread 1 is in sequence #23
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 685550 generated at 01/13/2009 10:00:56 needed for thread 1
ORA-00289: suggestion : /u01/backup/archive/1_24_675592897.dbf
ORA-00280: change 685550 for thread 1 is in sequence #24
ORA-00278: log file '/u01/backup/archive/1_23_675592897.dbf' no longer needed for this
recovery
ORA-00279: change 685552 generated at 01/13/2009 10:00:56 needed for thread 1
ORA-00289: suggestion : /u01/backup/archive/1_25_675592897.dbf
ORA-00280: change 685552 for thread 1 is in sequence #25
ORA-00278: log file '/u01/backup/archive/1_24_675592897.dbf' no longer needed for this
recovery
ORA-00279: change 685603 generated at 01/13/2009 10:02:38 needed for thread 1
ORA-00289: suggestion : /u01/backup/archive/1_26_675592897.dbf
ORA-00280: change 685603 for thread 1 is in sequence #26
ORA-00278: log file '/u01/backup/archive/1_25_675592897.dbf' no longer needed for this
recovery
Log applied.
Media recovery complete.
SQL> alter tablespace users online;
Tablespace altered.
SQL> select * from jglu.a;
ID
----------
1
2
這種方法是利用SQLPLUS的SET指令設定讀取歸檔日志的目錄。