RMAN备份与恢复之基于时间点的不完全恢复 - 数据库编程

lapsed time: 00:00:03 Finished backup at 17-JAN-14

Step 2，SQL Plus中打开显示完整时间

SQL> CONN SCOTT/tiger;
Connected.
SQL> SET TIME ON;
09:53:34 SQL>

Step 3，SQL Plus创建测试表

09:53:34 SQL> CREATE TABLE test(id NUMBER(2),name VARCHAR(20));

Table created.

09:53:59 SQL>

Step 4，SQL Plus插入数据，并提交，为了保持数据一致性，最好使用sys用户手动切换日志（ALTER SYSTEM SWITCHLOGFILE）和触发CKPT（ALTERSYSTEM CHECKPOINT）

09:53:59 SQL> INSERT INTO test VALUES(1,'justdb');

1 row created.

09:54:16 SQL> COMMIT;

Commit complete.

09:54:25 SQL> SELECT * FROM test;

  ID NAME
---------- --------------------
   1 justdb

09:54:33 SQL>

Step 5，SQL Plus中删除测试表

09:54:33 SQL> DROP TABLE test PURGE;

Table dropped.

Step 6，SQL Plus中一致性关闭数据库

10:00:09 SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
10:01:57 SQL>

Step 7，编辑基于时间点恢复的脚本，注意时间点的选取，此处选取为删除表的时间点（09:54:33），注意时间点尤其重要

[oracle@orcl ~]$ vim /home/oracle/recover_data_by_time.sql
[oracle@orcl ~]$ cat !$
cat /home/oracle/recover_data_by_time.sql
RUN {
  SET UNTIL TIME "to_date('2014-01-17 09:54:33','yyyy-mm-dd hh24:mi:ss')";
  RESTORE DATABASE;
  RECOVER DATABASE;
}

Step 8，登录到RMAN，启动数据库到MOUNT状态

[oracle@orcl ~]$ uniread rman target /
[uniread] Loaded history (6 lines)

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 17 10:02:22 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> STARTUP MOUNT;

Oracle instance started
database mounted

Total System Global Area     839282688 bytes

Fixed Size                     2233000 bytes
Variable Size                494931288 bytes
Database Buffers             339738624 bytes
Redo Buffers                   2379776 bytes

Step 9，RMAN中查看当前INCARNATION号，注意：如果有其他的操作，以后恢复时应该选取当前查看到的INCARNATION号，使用RESET DATABASE TO INCARNATION 命令

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       LARRRDB  3428598070       CURRENT 1          23-NOV-13

Step 10，RMAN中执行基于时间点的恢复脚本

RMAN> @ /home/oracle/recover_data_by_time.sql 
@ /home/oracle/recover_data_by_time.sql 

RMAN> RUN {
2>   SET UNTIL TIME "to_date('2014-01-17 09:54:33','yyyy-mm-dd hh24:mi:ss')";
3>   RESTORE DATABASE;
4>   RECOVER DATABASE;
5> }
executing command: SET until clause

Starting restore at 17-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oracle/oradata/larrrdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/larrrdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/larrrdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/larrrdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/fast_recovery_area/LARRRDB/backupset/2014_01_17/o1_mf_nnndf_TAG20140117T094447_9fk2rhv7_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/fast_recovery_area/LARRRDB/backupset/2014_0

RMAN备份与恢复之基于时间点的不完全恢复(二)