Tspitr (tablespace recovery based on time points)

Introduction to tspitr (tablespace point-in-time recovery)

Tspitr 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) The secondary database 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.
(7) The auxiliary set (auiiary set) is 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 violations of the self-contained tablespace set are as follows:
1. The tablespace set contains the Sys solution object.
2. The tablespace set contains the tablespace of the index, but does not contain the tablespace of the base table of the index.
3. The tablespace set does not contain all partitions of the partition table.
4. The tablespace set contains the tablespace of the table but does not contain the tablespace of its lob column.

The following are the specific experiment steps:

Create auxiliary destination
[Oracle @ even ~] $ Mkdir/u01/tspitr

Create a test table space tspitr_tbs
SQL> Create tablespace tspitr_tbs datafile '/u01/APP/Oracle/oradata/test/tspitr_tbs01.dbf' size 10 m;

Tablespace created.

Create a table in the test table space
SQL> conn HR/hr
Connected.
SQL> Create Table tspitr tablespace tspitr_tbs as select * From dba_objects;

Table created.

SQL> select count (*) from tspitr;

Count (*)
----------
50324

Are you sure you want to restore it to the following time point:
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 to truncate the tspitr table.
SQL> truncate table tspitr;

Table truncated.

The data record is empty.
SQL> select count (*) from tspitr;

Count (*)
----------
0

 

Check whether the table space is self-contained:
Note: Only self-contained tablespaces can be completely restored independently. 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 tables.

Space. The lob columns of some tables in this table are placed in other tablespaces.

SQL> Conn/As sysdba
Connected.
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
It 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 removed
Auxiliary instance file/u01/tspitr/cntrl_tspitr_test_xdfa.f deleted
Auxiliary instance file/u01/tspitr/tspitr_test_xdfa/datafile/o1_mf_system_8g4n4xw3 _. DBF deleted
Auxiliary instance file/u01/tspitr/tspitr_test_xdfa/datafile/o1_mf_undotbs1_8g4n4y78 _. DBF deleted
Auxiliary instance file/u01/tspitr/tspitr_test_xdfa/datafile/o1_mf_temp_8g4nccog _. tmp deleted
Auxiliary instance file/u01/tspitr/tspitr_test_xdfa/onlinelog/o1_mf_1_8g4nbywr _. Log deleted
Auxiliary instance file/u01/tspitr/tspitr_test_xdfa/onlinelog/o1_mf_2_8g4nc1q3 _. Log deleted
Auxiliary instance file/u01/tspitr/tspitr_test_xdfa/onlinelog/o1_mf_3_8g4nc49y _. Log deleted
Finished recover at 11:02:10

Use tspitr online to check whether the tspitr data is retrieved.
SQL> alter tablespace tspitr_tbs online;

Tablespace altered.

SQL> select count (*) from tspitr;

Count (*)
----------
50324
No record is lost. Success !!