TSPITR (tablespace recovery based on time points)

TSPITR (Recovery of Tablespace Based on TIme points) TSPITR (Tablespace Point-In-TIme Recovery) Introduction to www.2cto.com TSPITR (Recovery of Tablespace TIme points) it is used to restore one or more tablespaces to a previous time point, while other tablespaces remain in the existing state. (1) TSPITR (Tablespace Point-In-Time Recovery), only applicable to ARCHIVELOG mode. (2) TSPITR implementation method. We recommend that you use RMAN to restore the tablespace at a time point. (3) DBPITR (Database Point-In-TIme Recovery) indicates that all tablespaces In the Database are restored to the Russian state after the past TIme, which is only applicable to ARCHIVELOG mode. (4) Primary Database: the Oracle Database used to store application system data. When TSPITR is executed, the primary database refers to the database that contains the restored tablespace. (5) Recovery Set refers to the table space Set on the master database that needs to execute TSPITR. Note: When TSPITR is executed on the tablespace of the recovery set, these tablespaces must be self-contained. (6) Auxiliary Database (Auxiliary Database), which is a copy Database of the primary Database. When TSPITR is executed, the auxiliary database is used to restore the tablespace of the recovery set to the past time point. Note: All physical files in the secondary database are obtained from the primary database backup, and the secondary database must contain the SYSTEM tablespace, UNDO tablespace, and recovery set tablespace backup files. Www.2cto.com (7) Auxiliary Set (auiiary Set) refers to a collection of files required by the Auxiliary database, except for the tablespace files in the recovery Set. When TSPITR is executed, in addition to restoring the tablespace backup file, the auxiliary database also needs to back up the control file, the SYSTEM tablespace backup file, and the UNDO tablespace backup file. Note: The tablespace in the recovery set must be self-contained. common situations that violate the self-contained tablespace set are as follows: 1. The tablespace set contains the SYS solution object 2. The tablespace set contains the tablespace where the index is located, but it does not contain the tablespace 3 where the base table of the index is located. The tablespace set does not contain all partitions 4 of the partition table. The tablespace set contains the tablespace where the table is located, the following describes how to create a tablespace that does not contain its LOB column: create auxiliary destination [oracle @ even ~] $ Mkdir/u01/tspitr create test table space tspitr_tbsSQL> create tablespace tspitr_tbs datafile '/u01/app/oracle/oradata/test/tspitr_tbs01.dbf' size 10 M; Tablespace created. create a table SQL> conn hr/hrConnected in the test table space. SQL> create table tspitr tablespace tspitr_tbs as select * from dba_objects; Table created. SQL> select count (*) from tspitr; COUNT (*) ---------- 50324 determine the time point for Incomplete recovery: SQL> select sysdate from dual; SYSDATE ------------- ------ 10:42:25 switch log: SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. test truncate SQL> truncate table tspitr; Table truncated. SQL> select count (*) from tspitr; COUNT (*) ---------- 0 indicates whether the table space is self-contained. Note: Only the self-contained table space exists, can be based on individual Incomplete recovery. The so-called self-contained means that the objects in the tablespace do not depend on the objects in other tablespaces. For example, the objects indexed in the tablespace are basically in other tablespaces, And the lob columns of some tables in the table are placed in other tablespaces. SQL> conn/as sysdbaConnected. SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK ('tspitr _ tbs ', true, true); PL/SQL procedure successfully completed. SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; no rows selected indicates that tspitr is a self-contained tablespace. Determine the objects lost after Incomplete recovery: select owner, NAME, TABLESPACE_NAME, TO_CHAR (CREATION_TIME, 'yyyy-MM-DD: HH24: MI: ss ') FROM TS_PITR_OBJECTS_TO_BE_DROPPED WHERE TABLESPACE_NAME IN ('tspitr _ TBS ') AND CREATION_TIME> TO_DATE ('2017-01-01 10:42:25', 'yyyy-mm-dd HH24: MI: ss ') order by TABLESPACE_NAME, CREATION_TIME; no rows selected Start TSPITR: RMAN> run {recover tablespace TSPITR_TBS until time "to_date ('2017-01-01 10:42:25 ', 'Yyyy-mm-dd hh24: mi: ss') "auxiliary destination '/u01/tspitr ';}............... automatic instance removedauxiliary instance file/u01/tspitr/cntrl_tspitr_TEST_xDfa.f deletedauxiliary instance file/u01/tspitr/TSPITR_TEST_XDFA/datafile/o1_mf_system_8g4n4xw3 _. dbf deletedauxiliary instance file/u01/tspitr/TSPITR_TEST_XDFA/datafile/o1_mf_undotbs1_8g4n4y78 _. dbf deletedauxiliary instance file/u01/tspi Tr/TSPITR_TEST_XDFA/datafile/o1_mf_temp_8g4nccog _. tmp deletedauxiliary instance file/u01/tspitr/TSPITR_TEST_XDFA/onlinelog/o1_mf_1_8g4nbywr _. log deletedauxiliary instance file/u01/tspitr/TSPITR_TEST_XDFA/onlinelog/o1_mf_2_8g4nc1q3 _. log deletedauxiliary instance file/u01/tspitr/TSPITR_TEST_XDFA/onlinelog/o1_mf_3_8g4nc49y _. log deletedFinished recover at 11:02:10 change the tablespace tspitr online, check table t Whether the spitr data is retrieved. SQL> alter tablespace TSPITR_TBS online; Tablespace altered. SQL> select count (*) from tspitr; COUNT (*) ---------- one record in 50324 is not lost. Success !!