表空间基于时间点的恢复(TSPITR)

-- 创建表空间
conn /as sysdba
create tablespace dbs_d_test datafile size 30M autoextend off;

RMAN> backup database;
RMAN> list backup of database;

-- 误操作truncate核心业务表T2
conn jingyu/jingyu
alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:Mi:SS";
select sysdate from dual;
truncate table t2;
-- 之后创建T3表在dbs_d_test表空间
create table T3 tablespace dbs_d_test as select * from t1;

-- 查询现状：当前表T2，T3数据量（表T2已被截断，已经没有数据；表T3在T2被截断后创建，有数据）
conn jingyu/jingyu
select count(1) from t2;
select count(1) from t3;

conn / as sysdba
set linesize 1000
col obj1_owner for a10
col obj2_owner for a10

-- 如果只恢复表空间DBS_D_JINGYU
select OBJ1_OWNER, OBJ1_NAME, TS1_NAME, OBJ2_OWNER, OBJ2_NAME, TS2_NAME
from TS_PITR_CHECK
where (ts1_name = 'DBS_D_JINGYU' and ts2_name != 'DBS_D_JINGYU') or (ts1_name != 'DBS_D_JINGYU' and ts2_name = 'DBS_D_JINGYU');

-- 如果恢复表空间DBS_D_JINGYU,DBS_I_JINGYU
select OBJ1_OWNER, OBJ1_NAME, TS1_NAME, OBJ2_OWNER, OBJ2_NAME, TS2_NAME
from TS_PITR_CHECK
where (ts1_name in ('DBS_D_JINGYU','DBS_I_JINGYU') and ts2_name not in ('DBS_D_JINGYU','DBS_I_JINGYU')) or (ts1_name not in ('DBS_D_JINGYU','DBS_I_JINGYU') and ts2_name in ('DBS_D_JINGYU','DBS_I_JINGYU'));

-- 查询执行TSPITR后会丢失的对象
select owner, name, tablespace_name, creation_time
from TS_PITR_OBJECTS_TO_BE_DROPPED
where tablespace_name in ('DBS_D_JINGYU','DBS_I_JINGYU')
and creation_time > to_date('2015-11-11 15:01:46','yyyy-mm-dd hh24:mi:ss');

recover tablespace DBS_D_JINGYU, DBS_I_JINGYU until time '2015-11-11 15:01:46' auxiliary destination '/u01/orabak/auxiliary';

$ rman target / catalog vpc1/vpc1@catdb

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 11 15:39:27 2015

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

connected to target database: ORADB (DBID=2647366668)
connected to recovery catalog database

RMAN> 
RMAN> recover tablespace DBS_D_JINGYU, DBS_I_JINGYU until time '2015-11-11 15:01:46' auxiliary destination '/u01/orabak/auxiliary';

Starting recover at 2015-11-11 15:03:29
using channel ORA_DISK_1

Creating automatic instance, with SID='nysi'

initialization parameters used for automatic instance:
db_name=ORADB
db_unique_name=nysi_tspitr_ORADB
compatible=11.2.0.4.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
db_create_file_dest=/u01/orabak/auxiliary
log_archive_dest_1='location=/u01/orabak/auxiliary'
#No auxiliary parameter file used
...此省略大量输出内容
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/orabak/auxiliary/ORADB/datafile/o1_mf_temp_jin_c45t4498_.tmp deleted
auxiliary instance file /u01/orabak/auxiliary/ORADB/datafile/o1_mf_temp_c45t4486_.tmp deleted
auxiliary instance file /u01/orabak/auxiliary/ORADB/onlinelog/o1_mf_3_c45t43b3_.log deleted
auxiliary instance file /u01/orabak/auxiliary/ORADB/onlinelog/o1_mf_2_c45t42z3_.log deleted
auxiliary instance file /u01/orabak/auxiliary/ORADB/onlinelog/o1_mf_1_c45t42gx_.log deleted
auxiliary instance file /u01/orabak/auxiliary/ORADB/datafile/o1_mf_sysaux_c45t2wr3_.dbf deleted
auxiliary instance file /u01/orabak/auxiliary/ORADB/datafile/o1_mf_undotbs1_c45t2wrf_.dbf deleted
auxiliary instance file /u01/orabak/auxiliary/ORADB/datafile/o1_mf_system_c45t2wr9_.dbf deleted
auxiliary instance file /u01/orabak/auxiliary/ORADB/controlfile/o1_mf_c45t2hn2_.ctl deleted
Finished recover at 2015-11-11 15:06:22

-- 备份表空间DBS_D_JINGYU, DBS_I_JINGYU
RMAN> backup tablespace dbs_d_jingyu, dbs_i_jingyu;
-- 表空间DBS_D_JINGYU联机
RMAN>  sql 'alter tablespace dbs_d_jingyu online';
-- 表空间DBS_I_JINGYU联机
RMAN> sql 'alter tablespace dbs_i_jingyu online';

-- 最后验证
conn jingyu/jingyu
select count(1) from t2;
select count(1) from t3;

SQL> conn jingyu/jingyu
SQL> select count(1) from t2;

  COUNT(1)
----------
    100000

Elapsed: 00:00:00.01
SQL> select count(1) from t3;

  COUNT(1)
----------
       100

Elapsed: 00:00:00.01