分类： Oracle
2014-11-11 15:27:03
RMAN基于时间点恢复不完全恢复
我们知道，对归档下的数据库做RMAN全备，当数据库挂掉之后，执行完全恢复时，可以将数据库将恢复到完全最新的状态，包括至当前时间所做的所有已提交的数据修改，保证不会丢失数据。但是执行不完全恢复时，数据库会恢复到过去的某个时间点，这意味着会缺失一些事务处理，即恢复目标时间和当前时间之间所做的所有数据修改都会丢失。在许多情况下，这就是所需要的目标，因为可能对数据库执行了某些应撤消的操作，恢复到过去某一时间点是删除那些不需要的事务处理的一种方法。
一、实验思想数据库在归档下做RMAN全备后，记录时间点后将scott用户下的EMP表删除，再次记录时间点后删除scott用户，然后执行基于时间点的不完全恢复，先恢复EMP表，恢复成功后，然后在此基础上再在恢复scott用户，看会出现什么情况，以及如何解决。
二、实验环境1）Linux系统环境
[oracle@DG1 ~]$ 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）查看数据库是否归档
[oracle@DG1 ~]$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信息
[oracle@DG1 ~]$ 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.
[oracle@DG1 ~]$ 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表是否恢复
[oracle@DG1 ~]$ 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状态。
