Rman—实验—基于时间点的恢复
来源：互联网 发布：mac能装盗版软件吗 编辑：程序博客网 时间：2024/05/17 05:02
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:
Release:
Codename:
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
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
Automatic archival
Archive destination
Oldest online log sequence
Next log sequence to archive
Current log sequence
三、实验过程
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
------- ------- -------- ---------------- --- ---------- -------------- -----------
1
2
3
此时数据库的状态是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.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers
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.
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
------------------------------ ------- ----------
BIN$wy94vNyjb13gQAAKCwFAHg==$0 TABLE
DEPT
BONUS
SALGRADE
可见恢复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
Fixed Size
Variable Size
Database Buffers
Redo Buffers
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
------- ------- -------- ---------------- --- ---------- ----------
1
2
3
4
可以清楚的看见数据库当前的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
------------------------------ ------- ----------
DEPT
EMP
BONUS
SALGRADE
由此可见，EMP表恢复成功
我们顺便来查看一下数据库的incarnation信息
RMAN> list incarnation;
List of Database Incarnations
DB Key
------- ------- -------- ---------------- --- ---------- ----------
1
2
3
5
4
果然又增加DB Key=5的incarnation信息
在这里可以得出一个结论：当对数据库执行恢复操作后，数据库就会更新incarnation状态。
- Rman—实验—基于时间点的恢复
- RMAN基于时间点的不完全恢复
- 基于时间点RMAN恢复
- RMAN备份与恢复—基于时间的不完全恢复
- RMAN基于时间点恢复不完全恢复
- RMAN基于时间点恢复不完全恢复
- RMAN—实验—基于SCN的恢复
- rman表空间基于时间点的不完全恢复
- RMAN TSPITR 使用rman进行表空间基于时间点的恢复
- 【RMAN】TSPITR--RMAN表空间基于时间点的自动恢复
- RMAN备份与恢复之基于时间点的不完全恢复
- RMAN备份与恢复（6）——基于时间的不完全恢复！
- rman 时间点的不完全恢复
- rman 基于时间的不完全恢复
- rman—实验—基于日志序列号的不完整恢复
- RMAN基于时间点恢复不完全恢…
- RMAN 基于时间点的原机和异机恢复
- RMAN 时间点完全恢复
- java concurrent 探秘（经典的线程个数，是否完成等的控制） 线程池
- linux基础知识
- 学习php需要了解的点3
- javascript 获得上个月、获取下个月日期
- web meta
- Rman—实验—基于时间点的恢复
- hdoj2019 数列有序！
- Linux下清除缓存 drop_caches，sysctl
- ajax异步跨域请求
- java移位操作
- 选择排序 直接插入排序 希尔排序 堆排序
- 我以为十分简单，呜呜，杭电2054
- 学习php需要了解的点2 - PHP 一组加密解密算法
- STM32外部中断
