Oracle基于Cancel的不完全恢复

oracle

实验时间：2011-10-14　环境：RED HAT4，Oracle 10.0.20

基于Cancel的不完全恢复适用场景：Recover时，所需的某个归档日志损坏，或主机断电，current状态的联机日志损坏。

一、环境准备
以SCOTT用户登录，新建表EMP1，切换日志，再新建表EMP2，主机断电，删除当前日志文件，模拟文件损坏。具体命令如下：
SQL> conn scott/oracle
Connected.
SQL> create table emp1 as select * from emp;

Table created.

SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.
SQL> conn scott/oracle
Connected.
SQL> create table emp2 as select * from emp;

Table created.
SQL> conn / as sysdba
Connected.

确认日志的位置。
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          5   52428800          1 NO  CURRENT                 916653 14-OCT-11
         2          1          4   52428800          1 YES ACTIVE                  915569 14-OCT-11
         3          1          3   52428800          1 YES INACTIVE                915566 14-OCT-11

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         3         ONLINE  /home/oracle/oracle/product/oradata/orcl/redo03.log          NO
         2         ONLINE  /home/oracle/oracle/product/oradata/orcl/redo02.log          NO
         1         ONLINE  /home/oracle/oracle/product/oradata/orcl/redo01.log          NO     
        
SQL> shutdown abort
ORACLE instance shut down.
SQL> !rm -f /home/oracle/oracle/product/oradata/orcl/redo02.log

二、恢复步骤
１、尝试打开数据库
SQL> startup
ORACLE instance started.

Total System Global Area  130023424 bytes
Fixed Size                  1218100 bytes
Variable Size              67111372 bytes
Database Buffers           58720256 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/home/oracle/oracle/product/oradata/orcl/redo02.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

２、尝试Clear
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 2 thread 1: '/home/oracle/oracle/product/oradata/orcl/redo02.log'
结果提示日志有用，不能Clear

３、在当前库做基于Cancel的不完全恢复（这个想法是错误的）
SQL> recover database until cancel;
ORA-00279: change 916503 generated at 10/14/2011 23:26:00 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_4_764549776.dbf
ORA-00280: change 916503 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 916653 generated at 10/14/2011 23:26:58 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_5_764549776.dbf
ORA-00280: change 916653 for thread 1 is in sequence #5
ORA-00278: log file '/home/oracle/oracle/product/archive/1_4_764549776.dbf' no longer needed for this recovery


ORA-00308: cannot open archived log '/home/oracle/oracle/product/archive/1_5_764549776.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/home/oracle/oracle/product/oradata/orcl/system01.dbf'


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/home/oracle/oracle/product/oradata/orcl/system01.dbf'
恢复之后，使用Resetlogs选项，仍无法打开数据库，提示数据文件不一致，System表空间需要进一步的恢复。
当前日志损坏时，不能基于当前的数据库做不完全恢复。只能用以前的备份，做一个基于Cancel的不完全恢复。

４、用以前的备份，做基于Cancel的不完全恢复(其实就是恢复到不能恢复为止)
SQL> shutdown abort
ORACLE instance shut down.
SQL> !cp /disk2/oracle/coldbak/*.dbf $ORACLE_BASE/oradata/orcl/

SQL> startup mount
ORACLE instance started.

Total System Global Area  130023424 bytes
Fixed Size                  1218100 bytes
Variable Size              67111372 bytes
Database Buffers           58720256 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 916502 generated at 10/14/2011 23:13:45 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_4_764549776.dbf
ORA-00280: change 916502 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 916653 generated at 10/14/2011 23:26:58 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_5_764549776.dbf
ORA-00280: change 916653 for thread 1 is in sequence #5
ORA-00278: log file '/home/oracle/oracle/product/archive/1_4_764549776.dbf' no longer needed for this recovery


ORA-00308: cannot open archived log '/home/oracle/oracle/product/archive/1_5_764549776.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> alter database open resetlogs;

Database altered.
可以看到，最后数据可以打开了。

５、检查数据
SQL> conn scott/oracle
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
RECOVER_TEST                   TABLE
RECOVER_TEST2                  TABLE
EMP_BAK                        TABLE
EMP1                           TABLE

8 rows selected.
可以看到表EMP1存在，表EMP2不存在。因为EMP2的创建，是保存在当前REDO日志中的，而当前REDO日志损坏，所有当前日志中保存的操作全部丢失了。
归档日志、或者REDO日志损失，数据库就只能恢复到丢失的日志之前了。

数据库打开的前提之一：数据文件保持一致。当前数据库不一致无法打开数据库，只能根据以前一致的备份，恢复到最近的一致的时间点。