很好的理論文章。非常感謝。 原文位址：RMAN基于時間點恢複不完全恢複 作者：Jialin.Lee
RMAN 基于時間點恢複不完全恢複我們知道，對歸檔下的資料庫做RMAN全備，當資料庫挂掉之後，執行完全恢複時，可以将資料庫将恢複到完全最新的狀态，包括至目前時間所做的所有已送出的資料修改，保證不會丢失資料。但是執行不完全恢複時，資料庫會恢複到過去的某個時間點，這意味着會缺失一些事務處理，即恢複目标時間和目前時間之間所做的所有資料修改都會丢失。在許多情況下，這就是所需要的目标，因為可能對資料庫執行了某些應撤消的操作，恢複到過去某一時間點是删除那些不需要的事務處理的一種方法。
一、實驗思想
資料庫在歸檔下做RMAN全備後，記錄時間點後将scott使用者下的EMP表删除，再次記錄時間點後删除scott使用者，然後執行基于時間點的不完全恢複，先恢複EMP表，恢複成功後，然後在此基礎上再在恢複scott使用者，看會出現什麼情況，以及如何解決。
二、實驗環境
1）Linux系統環境
[[email protected] ~]$ lsb_release -a
LSB Version: :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch
Distributor ID: RedHatEnterpriseServer
Description: Red Hat Enterprise Linux Server release 5.4 (Tikanga)
Release: 5.4
Codename: Tikanga
2）Oracle資料庫版本資訊
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
3）檢視資料庫是否歸檔
[[email protected] ~]$sqlplus / as sysdba
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
三、實驗過程
1）在歸檔下對資料庫做RMAN全備
RMAN> backup database plus archivelog delete all input;
2）删除EMP表及scott使用者
檢視目前時間
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual;
TIME
-------------------
2012-06-24 10:17:07
連接配接scott使用者，删除scott使用者的EMP表
SQL> conn scott/tiger
Connected.
SQL> drop table emp;
Table dropped.
檢視目前時間
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual;
TIME
-------------------
2012-06-24 10:18:07
連接配接sys使用者，删除掉scott使用者
SQL> conn / as sysdba
Connected.
SQL> drop user scott cascade;
User dropped.
3）基于時間點的恢複
在用RMAN恢複資料庫之前，我們先檢視一下資料庫的incarnation資訊
[[email protected] ~]$ rman target /
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- -------------- -----------
1 1 DG1 1762320829 PARENT 1 30-JUN-05
2 2 DG1 1762320829 PARENT 446075 18-APR-12
3 3 DG1 1762320829 CURRENT 699141 13-JUN-12
此時資料庫的狀态是DB Key=3
關庫、起庫到MOUNT狀态，連接配接到RMAN，做基于時間點的不完全恢複，恢複到删除scott使用者時間點之前（2012-06-24 10:18:07）的狀态，即恢複scott使用者但不恢複EMP表。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[[email protected] ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Jun 24 10:42:53 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 104859216 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
RMAN> run{
2> allocate channel t1 device type disk;
3> set until time "to_date('2012-06-24 10:18:07','yyyy-mm-dd hh24:mi:ss')";
4> restore database;
5> recover database;}
allocated channel: t1
channel t1: sid=157 devtype=DISK
executing command: SET until clause
Starting restore at 24-JUN-12
channel t1: restoring datafile 00001
input datafile copy recid=9 stamp=786792068 filename=/home/oracle/FlashRecovery/DG1/datafile/o1_mf_system_7ydtmt75_.dbf
destination for restore of datafile 00001: /u01/app/oracle/oradata/DG1/system01.dbf
channel t1: copied datafile copy of datafile 00001
output filename=/u01/app/oracle/oradata/DG1/system01.dbf recid=16 stamp=786797207
channel t1: restoring datafile 00002
input datafile copy recid=13 stamp=786792291 filename=/home/oracle/FlashRecovery/DG1/datafile/o1_mf_undotbs1_7ydv1b6o_.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/DG1/undotbs01.dbf
channel t1: copied datafile copy of datafile 00002
output filename=/u01/app/oracle/oradata/DG1/undotbs01.dbf recid=17 stamp=786797217
channel t1: restoring datafile 00003
input datafile copy recid=11 stamp=786792223 filename=/home/oracle/FlashRecovery/DG1/datafile/o1_mf_sysaux_7ydtwjfm_.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/DG1/sysaux01.dbf
channel t1: copied datafile copy of datafile 00003
output filename=/u01/app/oracle/oradata/DG1/sysaux01.dbf recid=18 stamp=786797252
channel t1: restoring datafile 00004
input datafile copy recid=14 stamp=786792307 filename=/home/oracle/FlashRecovery/DG1/datafile/o1_mf_users_7ydv3ggf_.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/DG1/users01.dbf
channel t1: copied datafile copy of datafile 00004
output filename=/u01/app/oracle/oradata/DG1/users01.dbf recid=19 stamp=786797255
channel t1: restoring datafile 00005
input datafile copy recid=6 stamp=786791796 filename=/home/oracle/FlashRecovery/DG1/datafile/o1_mf_soe_7ydsodx7_.dbf
destination for restore of datafile 00005: /u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf
channel t1: copied datafile copy of datafile 00005
output filename=/u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf recid=20 stamp=786797658
Finished restore at 24-JUN-12
Starting recover at 24-JUN-12
starting media recovery
media recovery complete, elapsed time: 00:00:09
Finished recover at 24-JUN-12
released channel: t1
RMAN> alter database open resetlogs;
database opened
連接配接到資料庫，檢視scott使用者是否存在，以及EMP表是否恢複
[[email protected] ~]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jun 24 11:05:14 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$wy94vNyjb13gQAAKCwFAHg==$0 TABLE
DEPT TABLE
BONUS TABLE
SALGRADE TABLE
可見恢複scott使用者成功，EMP表尚未恢複
現在我們來做恢複EMP表：
關庫、起庫到MOUNT狀态，在剛成功恢複資料庫的基礎上，使用RMAN做基于時間點的不完全恢複，恢複到删除scott使用者EMP表之前的時間點（2012-06-19 14:58:09）的狀态。
RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 109053520 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
RMAN> run{
2> allocate channel t1 device type disk;
3> set until time "to_date('2012-06-24 10:17:07','yyyy-mm-dd hh24:mi:ss')";
4> restore database;
5> recover database;}
allocated channel: t1
channel t1: sid=157 devtype=DISK
executing command: SET until clause
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 06/24/2012 11:11:16
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
在這報錯，為什麼會報錯？如果對Oracle資料庫體系結構了解很清楚，那麼這個問題就不難了解了，我們先檢視一下資料庫此時的incarnation狀态。
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DG1 1762320829 PARENT 1 30-JUN-05
2 2 DG1 1762320829 PARENT 446075 18-APR-12
3 3 DG1 1762320829 PARENT 699141 13-JUN-12
4 4 DG1 1762320829 CURRENT 729844 24-JUN-12
可以清楚的看見資料庫目前的incarnation資訊從恢複前的DB Key=3變為DB Key=4
下面用圖來解釋報錯原因
圖中紅色線箭頭條代表資料庫在某個incarnation狀态下的整個運作過程，當我們将資料庫恢複到删除scott使用者之前EMP表之後（即恢複點1），然後以resetlogs方式打開資料庫，此時資料庫就會以incarnation DB Key=3此時間點狀态打開一個新的incarnation DB Key=4資料庫（當資料庫恢複成功，隻要沒有執行alter database open resetlogs，那麼資料庫incarnation就會一直處于DB Key=3的狀态，此時由于歸檔日志和重做日志都是完整的，是以可以在此狀态下恢複到自RMAN全備後的任一時間點）。4這個狀态資料庫的歸檔日志和重做日志都是空的，且隻能從他打開的該時間點往後記錄資料庫的所有操作，而不能從該時間點回退資料庫操做，因為在4狀态下，沒有歸檔日志和重做日志，是以當恢複scott過戶之後，在此基礎上在恢複EMP表，由于資料庫處于新的狀态4，沒有歸檔日志和重組日志，是以無法在此基礎上恢複EMP表。解決辦法就是重置資料庫到狀态3，然後直接恢複到删除EMP表之前的時間點即可。
報錯解決辦法：
RMAN>RESET DATABASE TO INCARNATION 3;
RMAN>run{
2> allocate channel t1 device type disk;
3> set until time "to_date('2012-06-24 10:17:07','yyyy-mm-dd hh24:mi:ss')";
4> restore database;
5> recover database;}
allocated channel: t1
channel t1: sid=157 devtype=DISK
executing command: SET until clause
Starting restore at 24-JUN-12
channel t1: restoring datafile 00001
input datafile copy recid=9 stamp=786792068 filename=/home/oracle/FlashRecovery/DG1/datafile/o1_mf_system_7ydtmt75_.dbf
destination for restore of datafile 00001: /u01/app/oracle/oradata/DG1/system01.dbf
channel t1: copied datafile copy of datafile 00001
output filename=/u01/app/oracle/oradata/DG1/system01.dbf recid=21 stamp=786799670
channel t1: restoring datafile 00002
input datafile copy recid=13 stamp=786792291 filename=/home/oracle/FlashRecovery/DG1/datafile/o1_mf_undotbs1_7ydv1b6o_.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/DG1/undotbs01.dbf
channel t1: copied datafile copy of datafile 00002
output filename=/u01/app/oracle/oradata/DG1/undotbs01.dbf recid=22 stamp=786799689
channel t1: restoring datafile 00003
input datafile copy recid=11 stamp=786792223 filename=/home/oracle/FlashRecovery/DG1/datafile/o1_mf_sysaux_7ydtwjfm_.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/DG1/sysaux01.dbf
channel t1: copied datafile copy of datafile 00003
output filename=/u01/app/oracle/oradata/DG1/sysaux01.dbf recid=23 stamp=786799719
channel t1: restoring datafile 00004
input datafile copy recid=14 stamp=786792307 filename=/home/oracle/FlashRecovery/DG1/datafile/o1_mf_users_7ydv3ggf_.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/DG1/users01.dbf
channel t1: copied datafile copy of datafile 00004
output filename=/u01/app/oracle/oradata/DG1/users01.dbf recid=24 stamp=786799721
channel t1: restoring datafile 00005
input datafile copy recid=6 stamp=786791796 filename=/home/oracle/FlashRecovery/DG1/datafile/o1_mf_soe_7ydsodx7_.dbf
destination for restore of datafile 00005: /u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf
channel t1: copied datafile copy of datafile 00005
output filename=/u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf recid=25 stamp=786800011
Finished restore at 24-JUN-12
Starting recover at 24-JUN-12
starting media recovery
archive log thread 1 sequence 3 is already on disk as file /home/oracle/FlashRecovery/DG1/archivelog/2012_06_24/o1_mf_1_3_7ydvf9b5_.arc
archive log thread 1 sequence 4 is already on disk as file /home/oracle/FlashRecovery/DG1/archivelog/2012_06_24/o1_mf_1_4_7ydvfm3g_.arc
archive log thread 1 sequence 5 is already on disk as file /home/oracle/FlashRecovery/DG1/archivelog/2012_06_24/o1_mf_1_5_7yf0djgh_.arc
archive log filename=/home/oracle/FlashRecovery/DG1/archivelog/2012_06_24/o1_mf_1_3_7ydvf9b5_.arc thread=1 sequence=3
archive log filename=/home/oracle/FlashRecovery/DG1/archivelog/2012_06_24/o1_mf_1_4_7ydvfm3g_.arc thread=1 sequence=4
archive log filename=/home/oracle/FlashRecovery/DG1/archivelog/2012_06_24/o1_mf_1_5_7yf0djgh_.arc thread=1 sequence=5
media recovery complete, elapsed time: 00:00:09
Finished recover at 24-JUN-12
released channel: t1
RMAN> alter database open resetlogs;
database opened
連接配接到資料庫，檢視scott使用者的EMP表是否存在
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
由此可見，EMP表恢複成功
我們順便來檢視一下資料庫的incarnation資訊
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DG1 1762320829 PARENT 1 30-JUN-05
2 2 DG1 1762320829 PARENT 446075 18-APR-12
3 3 DG1 1762320829 PARENT 699141 13-JUN-12
5 5 DG1 1762320829 CURRENT 729763 24-JUN-12
4 4 DG1 1762320829 ORPHAN 729844 24-JUN-12
果然又增加DB Key=5的incarnation資訊
在這裡可以得出一個結論：當對資料庫執行恢複操作後，資料庫就會更新incarnation狀态。
----Jialin.Lee
2012-06-24
![oracle備份還原到本地_Oracle 11g R2 RAC資料庫備份通過RMAN恢複到單執行個體資料庫實作...[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)