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

时间：2023-03-09 15:16:31
表空间基于时间点的恢复(TSPITR)

环境：RHEL 6.4 + Oracle 11.2.0.4

准备模拟环境

## 0.1 初始化表空间，用户，表，数据 ##
初始化参考 [模拟业务最小测试用例01](http://www.cnblogs.com/jyzhao/p/4952487.html) 文章中的第1，2，3，4，5步骤初始化模拟环境。
## 0.2 再建立一个表空间，模拟生产环境其他表空间 ##
```
-- 创建表空间
conn /as sysdba
create tablespace dbs_d_test datafile size 30M autoextend off;
```
## 0.3 数据库确定存在有效RMAN备份 ##
```
RMAN> backup database;
RMAN> list backup of database;
```
## 0.4 误操作截断核心表T2 ##
```
-- 误操作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;
```
## 0.5 需求 ##
恢复核心表T2（T2表数据存在DBS_D_JINGYU表空间, 索引存在DBS_I_JINGYU表空间）
采用表空间基于时间点的恢复技术，正常的话T3表（在表空间DBS_D_TEST）应该不受影响。
```
-- 查询现状：当前表T2，T3数据量（表T2已被截断，已经没有数据；表T3在T2被截断后创建，有数据）
conn jingyu/jingyu
select count(1) from t2;
select count(1) from t3;
```

1. 验证表空间的依赖性

## 1.1 查询 TS_PITR_CHECK ##
```
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'));

## 1.2 处理依赖关系 ##

可以看到，如果只恢复表空间DBS_D_JINGYU，会有T2表的索引依赖表空间DBS_I_JINGYU。

我们这里同时恢复表空间DBS_D_JINGYU,DBS_I_JINGYU，这样就解决了依赖关系。

<h1 id="2">2. 确定执行TSPITR后会丢失的对象</h1>

## 2.1 查询 TS_PITR_OBJECTS_TO_BE_DROPPED ##

-- 查询执行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');

## 2.2 预处理执行TSPITR后会丢失的对象 ##

我这里没有查出结果，如果有结果，最好先expdp导出这些对象的备份，待恢复表空间后，再导入这些对象。

当然如果确定这些对象是没有用的，可以直接忽略。

<h1 id="3">3. 自动执行TSPITR</h1>

## 3.1 RMAN自动执行TSPITR ##

恢复表空间`DBS_D_JINGYU, DBS_I_JINGYU`到`2015-11-11 15:01:46`时间点，辅助实例的临时存放目录为`/u01/orabak/auxiliary`

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

## 3.2 备份表空间然后使表空间联机 ##

-- 备份表空间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';

## 3.3 验证结果 ##

-- 最后验证

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

至此成功执行了表空间基于时间点的恢复：T2表数据已经恢复，T3表数据未受影响。

<h1 id="4">Reference</h1>

- OCP 认证考试指南 (1Z0-053)[M]. 清华大学出版社, 2010.

相关文章