冷備份還原資料庫能不能前滾恢複

對於冷備份還原資料庫能不能進行前滾恢複的問題，itpub論壇上相關連結在http://www.itpub.net/showthread.php?s=&threadid=220971
 
通過實驗證明，冷備份還原資料庫可以進行前滾恢複：
以下是實驗過程：

SQL> connect  internal/oracle
Connected.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/arch/ORCL
Oldest online log sequence     747
Next log sequence to archive   749
Current log sequence           749

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1        748   51200000          1 YES INACTIVE
      5209950 21-SEP-04

         2          1        749   51200000          1 NO  CURRENT
      5222702 23-SEP-04

         3          1        747   51200000          1 YES INACTIVE
      5205433 21-SEP-04

SQL> select * from v$logfile;

    GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
         1
/opt/oracle/db02/oradata/ORCL/redo01.log

         2
/opt/oracle/db03/oradata/ORCL/redo02.log

         3
/opt/oracle/db04/oradata/ORCL/redo03.log

SQL> connect test/test
Connected.
SQL> select * from user_tables;

no rows selected

SQL> create table t(a varchar2(10));

Table created.

SQL> alter system switch logfile;     (切換日誌，產生一個歸檔記錄檔)

System altered.

SQL> insert into t values('1'); 

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;      (切換日誌，產生一個歸檔記錄檔)

System altered.
SQL> insert into t values('2');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;       (切換日誌，產生一個歸檔記錄檔)

System altered.

SQL> insert into t values('3');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;     (切換日誌，產生一個歸檔記錄檔)    

System altered.   

SQL> host
$ cd /opt/oracle/arch/ORCL

$ls    (總共產生了四個歸檔日誌)
arch_1_749.arc  arch_1_750.arc  arch_1_751.arc  arch_1_752.arc
SQL>exit
 
SQL> select * from t;

A
----------
1
2
3

SQL> insert  into t values('4');   (插入第四條記錄4，但不切換日誌,這條記錄記錄在redo03.log裡)

1 row created.

SQL> commit;

Commit complete.

關閉資料庫：
SQL> connect internal/oracle
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> host
$cp /opt/oracle/db04/oradata/ORCL/redo03.log /opt/oracle/      （單獨備份當前日誌redo03.log到/opt/oracle目錄)
$ rm /opt/oracle/db02/oradata/ORCL/*  
$ rm /opt/oracle/db03/oradata/ORCL/* 
$ rm /opt/oracle/db04/oradata/ORCL/*

此時cp冷備份時的所有資料檔案（包括線上記錄檔和控制檔案）回去，此實驗之前做的冷備份.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1134141116 bytes
Fixed Size                   102076 bytes
Variable Size             311750656 bytes
Database Buffers          819200000 bytes
Redo Buffers                3088384 bytes
Database mounted.

SQL> recover database using backup controlfile until cancel;  (注意這裡一定要用using backup controlfile until cancel命令） 
ORA-00279: change 5224492 generated at 09/23/2004 11:00:18 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_749.arc
ORA-00280: change 5224492 for thread 1 is in sequence #749

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 5224671 generated at 09/24/2004 10:27:50 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_750.arc
ORA-00280: change 5224671 for thread 1 is in sequence #750
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_749.arc' no longer needed for
this recoveryORA-00279: change 5224683 generated at 09/24/2004 10:31:23 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_753.arc
ORA-00280: change 5224683 for thread 1 is in sequence #753

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/redo03.log   (這裡指定備份的那個/opt/oracle/redo03.log檔案)
Log applied.
Media recovery complete.

SQL> conn test/test
Connected.
SQL> select * from t;

ORA-00279: change 5224675 generated at 09/24/2004 10:29:00 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_751.arc
ORA-00280: change 5224675 for thread 1 is in sequence #751
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_750.arc' no longer needed for
this recovery

ORA-00279: change 5224679 generated at 09/24/2004 10:30:44 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_752.arc
ORA-00280: change 5224679 for thread 1 is in sequence #752
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_751.arc' no longer needed for
this recovery

ORA-00279: change 5224683 generated at 09/24/2004 10:31:23 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_753.arc
ORA-00280: change 5224683 for thread 1 is in sequence #753
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_752.arc' no longer needed for
this recovery

ORA-00308: cannot open archived log '/opt/oracle/arch/ORCL/arch_1_753.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

SQL> alter database open resetlogs;  （此時用open resetlogs開啟資料庫)

Database altered.

SQL> connect test/test
Connected.
SQL> select * from t;

A
----------
1
2
3

結果探索資料庫做了不完全恢複，只恢複了3條記錄，第4條記錄丟失了，因為冷備份後把redo03.log覆蓋了,而冷備份時的redo03.log裡無此記錄，最後一條記錄4記錄實際上記錄在冷備份後到出故障前的redo03.log裡了，因此只能恢複3條記錄。
如果應用了備份在/opt/oracle目錄下的那個redo03.log，資料庫就可以做完全恢複了。

在恢複的時候，最後一次恢複，指定備份的redo03.log:
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 5224492 generated at 09/23/2004 11:00:18 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_749.arc
ORA-00280: change 5224492 for thread 1 is in sequence #749

SQL> recover database using backup controlfile until cancel;

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 5224671 generated at 09/24/2004 10:27:50 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_750.arc
ORA-00280: change 5224671 for thread 1 is in sequence #750
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_749.arc' no longer needed for
this recovery

ORA-00279: change 5224675 generated at 09/24/2004 10:29:00 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_751.arc
ORA-00280: change 5224675 for thread 1 is in sequence #751
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_750.arc' no longer needed for
this recovery

ORA-00279: change 5224679 generated at 09/24/2004 10:30:44 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_752.arc
ORA-00280: change 5224679 for thread 1 is in sequence #752
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_751.arc' no longer needed for
this recovery

ORA-00279: change 5224683 generated at 09/24/2004 10:31:23 needed for thread 1
ORA-00289: suggestion : /opt/oracle/arch/ORCL/arch_1_753.arc
ORA-00280: change 5224683 for thread 1 is in sequence #753
ORA-00278: log file '/opt/oracle/arch/ORCL/arch_1_752.arc' no longer needed for
this recovery

ORA-00308: cannot open archived log '/opt/oracle/arch/ORCL/arch_1_753.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

A
----------
1
2
3
4

發現完全恢複了資料。

總結：用冷備份時候的所有檔案前滾恢複資料庫，恢複的時候要指定 recover database using backup controlfile util cancel,直到所有的歸檔，應用完畢再手工apply 聯機日誌就可以了，否則會報控制檔案老了不需要恢複的錯誤,注意最後一個日誌要使用線上日誌來恢複。