201810301635 Oracle DB 执行表空间时间点恢复

https://blog.csdn.net/rlhua/article/details/12659769

何时使用TSPITR

• TSPITR 可在下列情况下使用：

– 恢复因错误的TRUNCATE TABLE语句而丢失的数据

– 从表逻辑损坏中恢复

– 撤消只影响部分数据库的批作业或DML 语句的结果

– 将逻辑方案恢复到与物理数据库其余部分不同的时间点

• TSPITR 使用可移动表空间和数据泵，提供了以下新功能和特性：

– TSPITR 可用于恢复已删除的表空间

– 可反复执行TSPITR 恢复到表空间联机之前的多个时间点，而无需使用恢复目录

准备工作：
SQL> create tablespace aa datafile ‘/u01/app/oracle/oradata/dup1/aa.dbf’ size 100M;
SQL> create user LHR identified by lhr;
SQL> grant resource,connect to lhr;

1、全备数据库
RMAN> backup database plus archivelog;

2、建立测试表并做truncate误操作
注意做误操作之前先切换一下日志。
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> CREATE TABLE LHR.TSPITR_TEST TABLESPACE AA AS SELECT * FROM SCOTT.EMP;
SQL> CREATE TABLE LHR.TSPITR_TEST1 TABLESPACE AA AS SELECT * FROM SCOTT.EMP where rownum<=2;
SQL> create index lhr.TSPITR_test_index on LHR.TSPITR_TEST(empno) tablespace users;
SQL> select * from LHR.TSPITR_TEST ;
SQL> alter system switch logfile;
SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC

---

STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

---

 1        1          67   52428800       512        2 NO

CURRENT 7696236 21-OCT-18 2.8147E+14

 2        1          65   52428800       512        1 YES

ACTIVE 7696152 21-OCT-18 7696155 21-OCT-18

 3        1          66   52428800       512        1 YES

ACTIVE 7696155 21-OCT-18 7696236 21-OCT-18
SQL> truncate table LHR.TSPITR_TEST ;
SQL> select * from LHR.TEST_TSPITR;
select * from LHR.TEST_TSPITR
*
ERROR at line 1:
ORA-00942: table or view does not exist
可以看到表已经没有了
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_

---

 3       ONLINE  /u01/app/oracle/oradata/dup1/redo03.log                                 NO
 2       ONLINE  /u01/app/oracle/oradata/dup1/redo02.log                                 NO
 1       ONLINE  /u01/app/oracle/oradata/dup1/redo01.log                                 NO
 1 INVALID ONLINE  /home/oracle/redo01.log                                         NO

SQL> CREATE TABLE LHR.TSPITR_TEST2 TABLESPACE AA AS SELECT * FROM SCOTT.EMP;
SQL> CREATE TABLE LHR.TSPITR_TEST3 TABLESPACE AA AS SELECT * FROM SCOTT.EMP;
SQL> CREATE TABLE LHR.TSPITR_TEST4 TABLESPACE AA AS SELECT * FROM SCOTT.EMP;

这里可以看出执行误操作的时候sql的时间是20:11:33 左右，而且由于我们是测试库，没有什么用户操作，所以redo很少，从v$log中可以看出，误操作肯定在67号日志中。

生产环境下为了尽量减少对数据的影响，我们需要准确定位到误操作的时间点，所以可以采用logminer来找回误操作的时间点

3、采用logminer找回误删除的时间点

SQL> execute dbms_logmnr.add_logfile(’/u01/app/oracle/oradata/dup1/redo02.log’,dbms_logmnr.new);
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
SQL> alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;
SQL> select a.SCN,a.TIMESTAMP,a.SQL_REDO from v$logmnr_contents A
where table_name=‘TSPITR_TEST’ and OPERATION=‘DDL’ order by a.SCN;
SCN TIMESTAMP SQL_REDO

---

7696284 2018-10-21 19:48:27 truncate table LHR.TSPITR_TEST ;

可以看出执行TRUNCATE的时间为2018-10-22 19:48:27， 我们恢复的时候恢复到19:48:27 。

4、执行TSPITR之前的检查
检查是否自包含

SQL>BEGIN
SYS.DBMS_TTS.TRANSPORT_SET_CHECK(‘AA’, TRUE, TRUE);
END;
/
SQL> SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
VIOLATIONS

ORA-39907: Index LHR.TSPITR_TEST_INDEX in tablespace USERS points to table LHR.TSPITR_TEST in tablespace AA.
说明，有一个索引 LHR…TSPITR_TEST_INDEX在users表空间中，那么我们删除这个索引，等待恢复完成后再重建该索引。

SQL> drop index LHR.TSPITR_TEST_INDEX;

SQL> BEGIN
SYS.DBMS_TTS.TRANSPORT_SET_CHECK(‘AA’, TRUE, TRUE);
END;
/
SQL> SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
至此，自包含问题已解决。

5、 检查哪些对象执行TSPITR后将被删除
select * from SYS.TS_PITR_OBJECTS_TO_BE_DROPPED A WHERE a.tablespace_name=‘AA’ and a.creation_time>=‘2018-10-22 19:48:27’;

则说明若执行TSPITR后，AA表空间中的表TSPITR_TEST2、TSPITR_TEST3、TSPITR_TEST4将被删除，那么为了保存这些对象我们将其利用expdp导出备份，等待tspitr执行完毕后再导入回来即可。

$ expdp lhr/lhr directory=EXPDUMP dumpfile=exptable_tspitrlhr.dmp TABLES=TSPITR_TEST2,TSPITR_TEST3,TSPITR_TEST4 LOGFILE=exptable_tspitrlhr.log

6、执行TSPITR

RMAN> RECOVER TABLESPACE AA UNTIL time “to_date(‘2018-10-22 19:48:27’,‘YYYY-MM-DD HH24:mi:ss’)” auxiliary destination ‘/home/oracle’;

注意：一定要保证auxiliary destination '/home/oracle’存在然后会在下面创建文件目录DUP1并在下面存放辅助数据库的文件，最后会自动删掉。
[oracle@localhost DUP1]$ ls
controlfile datafile onlinelog
7、online表空间并且导入丢失的对象
执行完恢复之后，表空间处于offline状态，需要将表空间online。
SQL> select * from LHR.TSPITR_TEST;
SQL> alter tablespace aa online;
SQL> select * from LHR.TSPITR_TEST ;
SQL> select * from LHR.TSPITR_TEST2 ;
SQL>
数据已经恢复，可是LHR.TSPITR_TEST2丢失，下边我们来导入备份的数据：
$ impdp lhr/lhr directory=EXPDUMP dumpfile=exptable_tspitrlhr.dmp TABLES=TSPITR_TEST2,TSPITR_TEST3,TSPITR_TEST4 LOGFILE=imptable_tspitrlhr.log

SQL> select * from LHR.TSPITR_TEST2 ;