1.准备环境：RMAN全库备份
[oracle@host01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Dec 21 11:46:49 2016

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

connected to target database: PROD1 (DBID=2148633869)

RMAN> backup as compressed backupset full database;

Starting backup at 21-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/PROD1/ts_setnew01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-DEC-16
channel ORA_DISK_1: finished piece 1 at 21-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_21/o1_mf_nnndf_TAG20161221T114701_d5myso5w_.bkp tag=TAG20161221T114701 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 21-DEC-16

Starting Control File and SPFILE Autobackup at 21-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_21/o1_mf_s_931175266_d5myv290_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-DEC-16

2.准备环境：测试表及数据
SYS@PROD1>create table t1 as select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as dd from dual;

Table created.

SYS@PROD1>select * from t1;

DD
-------------------
2016-12-21 11:54:51

SYS@PROD1>select sysdate from dual;

SYSDATE
---------
21-DEC-16

SYS@PROD1>alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;

System altered.

SYS@PROD1>startup force;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.

SYS@PROD1>select sysdate from dual;

SYSDATE
-------------------
2016-12-21 11:56:52

SYS@PROD1>create table t2 as select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as dd from dual;

Table created.

SYS@PROD1>select * from t2;

DD
-------------------
2016-12-21 11:57:08

模拟几分钟后删除t1表
SYS@PROD1>drop table t1 purge;

Table dropped.

SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@host01 PROD1]$ ls *.dbf
example01.dbf sysaux01.dbf system01.dbf temp01.dbf ts_setnew01.dbf undotbs01.dbf users01.dbf
[oracle@host01 PROD1]$ rm *.dbf

3.启动到mount模式，进行基于时间的恢复（利用RMAN恢复）
SYS@PROD1>startup mount;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.

SYS@PROD1>ed 002
run{
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time = '2016-12-21 11:56:52';
restore database;
recover database;
alter database open resetlogs;
}

RMAN> run{
2> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
3> set until time = '2016-12-21 11:56:52';
4> restore database;
5> recover database;
6> alter database open resetlogs;
7> }

using target database control file instead of recovery catalog
sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"

executing command: SET until clause

Starting restore at 21-DEC-16
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/app/oracle/oradata/PROD1/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD1/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD1/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PROD1/ts_setnew01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_21/o1_mf_nnndf_TAG20161221T114701_d5myso5w_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_21/o1_mf_nnndf_TAG20161221T114701_d5myso5w_.bkp tag=TAG20161221T114701
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 21-DEC-16

Starting recover at 21-DEC-16
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 21-DEC-16

database opened

4.验证结果
SYS@PROD1>select tname from tab where tname like 'T_';

TNAME
------------------------------
T1

因为2016-12-21 11:56:52之前只创建了t1表，t2表还没有建）

ITPub博客

【备份恢复】不完全恢复基于时间点的恢复

最新文章

ITPub博客

【备份恢复】 不完全恢复 基于时间点的恢复

最新文章

【备份恢复】不完全恢复基于时间点的恢复