冷备份还原数据库能不能前滚恢复

对于冷备份还原数据库能不能进行前滚恢复的问题，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 联机日志就可以了，否则会报控制文件老了不需要恢复的错误,注意最后一个日志要使用在线日志来恢复。