Oracle不完全恢复

Oracle不完全恢复指的是恢复的数据有丢失，部分数据恢复不了。

环境：

        OS: rhel 6.0

DB:oracle 10.2.0.1.0

1，首先有数据库的备份

这里采用的是rman的全备
[oracle@rhel ~]$ rman target /


Recovery Manager: Release 10.2.0.1.0 - Production on Fri Apr 27 09:26:26 2012


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


connected to target database: ORCL10 (DBID=3989340674)


RMAN> backup database;
2，创建测试表
SQL> create table emp2 as select * from emp;
查看当前的日志状况
SQL> select * from v$log;


GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS    FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1    1       2   52428800     1 YES ACTIVE         584685 27-APR-12
2    1       1   52428800     1 YES ACTIVE         584369 27-APR-12
3    1       3   52428800     1 NO  CURRENT         584693 27-APR-12


3，插入数据，提交并且切换日志
SQL> insert into emp2  select * from emp2;


14 rows created.


SQL> commit;
SQL> alter system switch logfile;
SQL> insert into emp2  select * from emp2;


28 rows created.


SQL> commit;


Commit complete.
4，模拟断点，丢失online redolog
SQL> shutdown abort
[oracle@rhel orcl10]$ rm -rf redo0*  
尝试打开数据库，提示：
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/product/10.2.0/oradata/orcl10/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
查看online redolog日志的状态
SQL> select * from v$log;


GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS    FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1    1       2   52428800     1 YES INACTIVE      584685 27-APR-12
3    1       3   52428800     1 YES ACTIVE      584693 27-APR-12
2    1       4   52428800     1 NO  CURRENT
active的状态表示在恢复的时候数据不同步
尝试对数据库进行不完全恢复，进行recover
SQL> recover database until cancel;
ORA-00279: change 584693 generated at 04/27/2012 09:28:19 needed for thread 1
ORA-00289: suggestion : /opt/oracle/product/10.2.0/flash_recovery_area/ORCL10/archivelog/2012_04_27/o1_mf_1_3_%u_.arc
ORA-00280: change 584693 for thread 1 is in sequence #3




Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 584825 generated at 04/27/2012 09:33:36 needed for thread 1
ORA-00289: suggestion : /opt/oracle/product/10.2.0/flash_recovery_area/ORCL10/archivelog/2012_04_27/o1_mf_1_4_%u_.arc
ORA-00280: change 584825 for thread 1 is in sequence #4
ORA-00278: log file '/opt/oracle/product/10.2.0/flash_recovery_area/ORCL10/archivelog/2012_04_27/o1_mf_1_3_7so81kbk_.arc' no longer needed for this recovery




ORA-00308: cannot open archived log '/opt/oracle/product/10.2.0/flash_recovery_area/ORCL10/archivelog/2012_04_27/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-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: '/opt/oracle/product/10.2.0/oradata/orcl10/system01.dbf'
后面提示的错误表示存在数据文件不同步
5，从最近的备份中转储所有的数据文件，除offline文件和readonly文件以后，转储到目标位置
[oracle@rhel ~]$ rman target /


Recovery Manager: Release 10.2.0.1.0 - Production on Fri Apr 27 09:40:57 2012


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


connected to target database: ORCL10 (DBID=3989340674, not open)


RMAN> restore database;


6，对数据库进行recover
SQL> recover database until cancel;
ORA-00279: change 584626 generated at 04/27/2012 09:26:32 needed for thread 1
ORA-00289: suggestion : /opt/oracle/product/10.2.0/flash_recovery_area/ORCL10/archivelog/2012_04_27/o1_mf_1_1_%u_.arc
ORA-00280: change 584626 for thread 1 is in sequence #1




Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 584685 generated at 04/27/2012 09:28:13 needed for thread 1
ORA-00289: suggestion : /opt/oracle/product/10.2.0/flash_recovery_area/ORCL10/archivelog/2012_04_27/o1_mf_1_2_%u_.arc
ORA-00280: change 584685 for thread 1 is in sequence #2
ORA-00278: log file '/opt/oracle/product/10.2.0/flash_recovery_area/ORCL10/archivelog/2012_04_27/o1_mf_1_1_7so7qh5g_.arc' no longer needed for this recovery




ORA-00279: change 584693 generated at 04/27/2012 09:28:19 needed for thread 1
ORA-00289: suggestion : /opt/oracle/product/10.2.0/flash_recovery_area/ORCL10/archivelog/2012_04_27/o1_mf_1_3_%u_.arc
ORA-00280: change 584693 for thread 1 is in sequence #3
ORA-00278: log file '/opt/oracle/product/10.2.0/flash_recovery_area/ORCL10/archivelog/2012_04_27/o1_mf_1_2_7so7qn5h_.arc' no longer needed for this recovery




ORA-00279: change 584825 generated at 04/27/2012 09:33:36 needed for thread 1
ORA-00289: suggestion : /opt/oracle/product/10.2.0/flash_recovery_area/ORCL10/archivelog/2012_04_27/o1_mf_1_4_%u_.arc
ORA-00280: change 584825 for thread 1 is in sequence #4
ORA-00278: log file '/opt/oracle/product/10.2.0/flash_recovery_area/ORCL10/archivelog/2012_04_27/o1_mf_1_3_7so81kbk_.arc' no longer needed for this recovery




ORA-00308: cannot open archived log '/opt/oracle/product/10.2.0/flash_recovery_area/ORCL10/archivelog/2012_04_27/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-01194: file 1 needs more recovery to be consistent"的提示，我们可以打开数据库。
7，打开数据库
SQL> alter database open resetlogs;


Database altered.
SQL> select * from v$log;


GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS    FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1    1       0   52428800     1 YES UNUSED 0
2    1       1   52428800     1 NO  CURRENT   584826 27-APR-12
3    1       0   52428800     1 YES UNUSED 0
8，查看数据
SQL> select count(*) from emp2;


 COUNT(*)
----------
28
显示的数据是我们在切换日志之前的数据，切换日志之后的日志丢失。
9.基于时间点的不完全恢复
我们需要从alert日志中或是从online redolog 日志中查询到需要恢复的时间点，转储备份文件到目标数据库
执行
recover database until time '2012-04-27 12:46:23';
alter database open resetlogs;