只有冷備份+歸檔檔案,是否可以恢復最近狀態【花費10個小時徹底解決】

anycall2010發表於2008-08-02

根據這個帖子http://www.itpub.net/viewthread.php?tid=1027951&extra=&page=1,進行模擬冷備份冷備份+歸檔檔案,是否能恢復到初始狀態。

試驗的想法:

先對資料庫進行冷備份,保證資料庫開啟歸檔,然後建立一個表。隨後關閉資料庫,將資料檔案和控制檔案拷貝到原來的位置,實現冷備還原。

試驗的目的:

冷備還原後,能將建立的表恢復。

測試結果:失敗!

主要做法:

1.冷備初始的所有檔案,包括資料檔案,日誌檔案,控制檔案,引數檔案等等,所有的檔案。

2.檢查歸檔日誌:

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4

3.建立一個表:


SQL> select * from xx
  2  ;

XX
----------
glf

4.關閉資料庫:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


5.將原來冷備的控制檔案,資料檔案拷貝到現在目錄下。

6.開啟資料庫:

SQL> startup

ORACLE instance started.

Total System Global Area  293601280 bytes
Fixed Size                  1219040 bytes
Variable Size             100664864 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00338: log 1 of thread 1 is more recent than control file
ORA-00312: online log 1 thread 1: '/oracle/oradata/orcl/redo01.log'

7.當控制檔案和日誌檔案不一致的時候:

startup mount
ORACLE instance started.

Total System Global Area  293601280 bytes
Fixed Size                  1219040 bytes
Variable Size             100664864 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.

紅色標註是錯誤的做法:這樣只是不完全恢復!

SQL> recover database using backup controlfile until cancel
ORA-00279: change 537267 generated at 08/01/2008 11:46:29 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2008_08_01/o1_mf_1_4_%u_.arc
ORA-00280: change 537267 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/oracle/flash_recovery_area/ORCL/archivelog/2008_08_01/o1_mf_1_4_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/oracle/flash_recovery_area/ORCL/archivelog/2008_08_01/o1_mf_1_4_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

上面這樣做其實是錯誤的,因為沒有找到歸檔日誌。應該從線上redolog入手,進行日誌恢復。

SQL> conn sys/oracle as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area  293601280 bytes
Fixed Size                  1219040 bytes
Variable Size             100664864 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes


Database mounted.
SQL> alter database open resetlogs;

Database altered.


8.尋找以前那張表:

SQL> select * from xx;
select * from xx
              *
ERROR at line 1:
ORA-00942: table or view does not exist

試驗和預期結果不一致

 

正確做法:

SQL> recover database using backup controlfile;
ORA-00279: change 559252 generated at 08/01/2008 15:28:02 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2008_08_02/o1_mf_1_2_%u_.arc
ORA-00280: change 559252 for thread 1 is in sequence #2


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/oradata/orcl/redo01.log
Log applied.
Media recovery complete.

然後SQL> alter database open resetlogs;

Database altered.

SQL> select * from xx
  2  ;

XX
----------
glf

 

試驗和預期結果一致。

總結:實際這個是一個控制檔案和日誌檔案不一致問題,如果直接使用"alter database open resetlogs" 顯然是不完全恢復,那麼一定會丟資料。如果先恢復聯機日誌,在重置日誌組,實現完全恢復。
 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8334342/viewspace-417488/,如需轉載,請註明出處,否則將追究法律責任。

相關文章