Oracle 学习之RMAN（十四）恢复实战--基于时间点恢复

1. 我们先做一个全备

RMAN> backup database ;Starting backup at 2015/07/09 13:40:47allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=28 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/devdb/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/devdb/sysaux01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/devdb/example01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/devdb/undotbs01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/devdb/users01.dbfchannel ORA_DISK_1: starting piece 1 at 2015/07/09 13:40:48channel ORA_DISK_1: finished piece 1 at 2015/07/09 13:42:34piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_nnndf_TAG20150709T134048_bsw2c0xq_.bkp tag=TAG20150709T134048 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:46channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 2015/07/09 13:42:35channel ORA_DISK_1: finished piece 1 at 2015/07/09 13:42:36piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_ncsnf_TAG20150709T134048_bsw2gcly_.bkp tag=TAG20150709T134048 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 2015/07/09 13:42:36

2. 创建一个表，往里面插入一些数据。记录下时间，等待一会再将表truncate。

SQL> alter user scott account unlock;User altered.SQL> alter user scott identified by tiger;User altered.SQL> conn scott/tigerConnected.SQL> select sysdate from dual;SYSDATE-------------------2015/07/09 13:44:30SQL> create table t1 as select * from emp;Table created.SQL> select sysdate from dual;SYSDATE-------------------2015/07/09 13:45:32SQL> truncate table t1;Table truncated.

在2015/07/09 13:45:32以前，表t1中有14条数据。之后做了truncate。我们现在来看看能不能将数据库恢复到2015/07/09 13:45:32这一时刻。也就是t1被truncate掉之前。

3. 关闭数据库，再启动到mount状态

SQL> conn / as sysdbaConnected.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area  839282688 bytesFixed Size             2233000 bytesVariable Size                494931288 bytesDatabase Buffers     339738624 bytesRedo Buffers                   2379776 bytesDatabase mounted.

4. 恢复数据库

RMAN> RUN {  ALLOCATE CHANNEL c1 TYPE DISK;  ALLOCATE CHANNEL c2 TYPE DISK;  SET UNTIL TIME = '2015/07/09 13:45:32';  RESTORE DATABASE;  RECOVER DATABASE;  ALTER DATABASE OPEN RESETLOGS; }2> 3> 4> 5> 6> 7> 8> using target database control file instead of recovery catalogallocated channel: c1channel c1: SID=20 device type=DISKallocated channel: c2channel c2: SID=21 device type=DISKexecuting command: SET until clauseStarting restore at 2015/07/09 13:50:59channel c1: starting datafile backup set restorechannel c1: specifying datafile(s) to restore from backup setchannel c1: restoring datafile 00001 to /u01/app/oracle/oradata/devdb/system01.dbfchannel c1: restoring datafile 00002 to /u01/app/oracle/oradata/devdb/sysaux01.dbfchannel c1: restoring datafile 00003 to /u01/app/oracle/oradata/devdb/undotbs01.dbfchannel c1: restoring datafile 00004 to /u01/app/oracle/oradata/devdb/users01.dbfchannel c1: restoring datafile 00005 to /u01/app/oracle/oradata/devdb/example01.dbfchannel c1: reading from backup piece /u01/app/oracle/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_nnndf_TAG20150709T134048_bsw2c0xq_.bkpchannel c1: piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/backupset/2015_07_09/o1_mf_nnndf_TAG20150709T134048_bsw2c0xq_.bkp tag=TAG20150709T134048channel c1: restored backup piece 1channel c1: restore complete, elapsed time: 00:01:15Finished restore at 2015/07/09 13:52:14Starting recover at 2015/07/09 13:52:15starting media recoverymedia recovery complete, elapsed time: 00:00:01Finished recover at 2015/07/09 13:52:16database openedreleased channel: c1released channel: c2

5. 验证数据是否存在。

SQL> conn scott/tiger Connected.SQL> select count(*) from t1;  COUNT(*)----------        14SQL>

可见数据已经找回。

基于时间点的恢复，是不完全恢复的一种，我们还可以基于SCN和日志sequence恢复。

UNTIL SEQUENCE or UNTIL SCN