Table space-based Incomplete recovery of RMAN (TSPITR)

RMAN tablespace-based Incomplete recovery (TSPITR) tablespace time point recovery (TSPITR): by executing TSPITR, one or more tablespaces can be quickly restored to a previous time. Executing TSPITR will not affect the database

RMAN tablespace-based Incomplete recovery (TSPITR) tablespace time point recovery (TSPITR): by executing TSPITR, one or more tablespaces can be quickly restored to a previous time. Executing TSPITR will not affect the database

Table space-based Incomplete recovery of RMAN (TSPITR)

Automatic Execution of TSPITR using RMAN:

1. Work Environment

At 12:21:32 SCOTT @ prod> create table emp1 as select * from emp; Table created. elapsed: 00:00:00. 6514: 07: 41 SCOTT @ prod> insert into emp1 select * from emp where rownum <5; 4 rows created. elapsed: 00:00:00. 02:08:18 SCOTT @ prod> commit; Commit complete. elapsed: 00:00:00. 0414: 08: 19 SCOTT @ prod> select count (*) from emp1; COUNT (*) ---------- 18 Elapsed: 00:00:00. 02emp1 table data is accidentally deleted: 14: 08: 20 SCOTT @ prod> truncate Table emp1; table truncated. elapsed: 00:00:00. 3214: 08: 30 SCOTT @ prod> insert into emp1 select * from emp where empno = 7788; 1 row created. elapsed: 00:00:00. 0314: 08: 46 SCOTT @ prod> commit; Commit complete. elapsed: 00:00:00. 0614: 08: 48 SCOTT @ prod> select * from emp1; empno ename job mgr hiredate sal comm deptno ---------- --------- ---------- 7788 scott analyst 7566 19-APR-87 3000 20 Elapsed: 00:00:00. 0814: 08: 55 SCOTT @ prod> update emp1 set empno = 8888; 1 row updated. elapsed: 00:00:00. 02:09:06 SCOTT @ prod> commit; Commit complete. elapsed: 00:00:00. explain SCOTT @ prod> select * from emp1; empno ename job mgr hiredate sal deptno ---------- --------- ---------- ----------- ------------ ---------- 8888 scott analyst 7566 19-APR-87 3000 20 Elapsed: 00:00:00. 011:09:12 SCOTT @ prod>

2. perform pre-recovery detection

Check whether tablespace contains itself: 10: 39: 16 SCOTT @ prod> CONN/as sysdbaConnected.10: 39: 25 SYS @ prod> exec DBMS_TTS.TRANSPORT_SET_CHECK ('tbs1', TRUE ); PL/SQL procedure successfully completed. elapsed: 00:01:58. 4510: 41: 43 SYS @ prod> select * from TRANSPORT_SET_VIOLATIONS t; no rows selected confirm the data file to be restored: 14: 13: 34 SYS @ prod> select * from dba_data_files t where T. TABLESPACE_NAME = 'tbs1 '; FILE_NAME upload FILE_ID when bytes blocks status when aut maxbytes maxblocks ---------- then ------------ ----------- then --- ---------- too many ONLINE _ offline ---------- else -------/u01/app/oracle/oradata/prod/ tbs1.dbf 6 TBS1 10485760 1280 AVAILABLE 6 NO 0 0 0 9437184 1152 ONLINEElapsed: 00:00:00. 02sys @ prod> check whether the auxiliary set system undo and CONTROLFILE: 14: 14: 38 SYS @ prod> select file_name name from dba_data_files t where T. TABLESPACE_NAME IN ('system', 'undotbs1') 14:14:40 2 union14: 14: 41 3 select max (name) from v $ controlfile t; NAME users/u01/app/oracle/oradata/prod/control01.ctl/u01/app/oracle/oradata/prod/system01.dbf/u01/app/oracle/oradata/prod/undotbs01.dbfElapsed: 00:00:00. 03 objects that may be lost after the TSP check: 14: 14: 42 SYS @ prod> select * from TS_PITR_OBJECTS_TO_BE_DROPPED T where T. OWNER = 'tbs1'; no rows selectedElapsed: 00:00:00. 17

3. Execute RMAN-based TSPITR

[Root @ rh6 prod] # mkdir/home/oracle/prod