備份與恢復--從備份的歸檔日誌中恢復資料

在恢復過程中，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命令設定讀取歸檔日誌的目錄。