Oracle不完全恢複__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;