備份與恢復--從備份的歸檔日誌中恢復資料
在恢復過程中，Oracle利用歸檔日誌進行恢復，預設情況下，Oracle會在歸檔日誌的產生目錄來尋找歸檔日誌檔案。
如果在恢復時發現歸檔日誌被備份並從歸檔目錄刪除，則Oracle在恢復過程中無法找到指定的歸檔日誌檔案。
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: {
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: {
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: {
/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: {
/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: {
/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: {
/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: {
/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: {
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命令設定讀取歸檔日誌的目錄。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/271283/viewspace-1016137/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 備份與恢復：polardb資料庫備份與恢復資料庫
- Oracle使用備份檔案集恢復歸檔日誌Oracle
- MySQL 日誌管理、備份與恢復MySql
- RAC備份恢復之Voting備份與恢復
- Mysql資料備份與恢復MySql
- MySQL日誌管理，備份和恢復MySql
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- 資料庫備份恢復資料庫
- RabbitMQ如何備份與恢復資料MQ
- postgresql備份與恢復資料庫SQL資料庫
- Jenkins備份與恢復Jenkins
- Postgresql 備份與恢復SQL
- MySQL 備份與恢復MySql
- Mysql備份與恢復(1)---物理備份MySql
- DB的備份與恢復
- GitLab的備份與恢復Gitlab
- 從備份片中恢復某個指定得歸檔或者資料檔案
- MySQL備份與恢復——基於MyDumper/MyLoader 邏輯備份恢復MySql
- MySQL 非常規恢復與物理備份恢復MySql
- MySQL備份與恢復——基於OUTFILE /LOAD DATA 邏輯備份恢復MySql
- 資料庫備份與恢復技術資料庫
- Mysql備份與恢復(2)---邏輯備份MySql
- GitLab的自動備份、清理備份與恢復Gitlab
- gitlab的資料備份和恢復Gitlab
- mydumper備份恢復
- Mysql備份恢復MySql
- 備份和恢復
- Oracle 備份 與 恢復 概述Oracle
- 如何從Active Backup for Business備份中恢復檔案
- dg丟失歸檔，使用rman增量備份恢復
- 從dataguard備份的恢復機制
- MySQL-19.資料庫備份與恢復MySql資料庫
- Mysql資料庫備份及恢復MySql資料庫
- 達夢資料庫備份恢復資料庫
- 磁碟資料恢復及備份工具資料恢復
- 詳解叢集級備份恢復：物理細粒度備份恢復
- 備份與恢復：Polardb資料庫資料基於時間點恢復資料庫
- Linux下MySQL資料庫的備份與恢復LinuxMySql資料庫
- 如何恢復Hyper Backup備份的資料