New Features of Oracle 10 GB: rman tspitr features

Oracle uses Incomplete Database-level recovery to solve the logical errors caused by data loss or human error before 10 Gb. After 10 Gb, RMAN introduces a new feature called "tablespace point-in-time recovery", TSPITR for short, which can actually achieve Incomplete recovery at the tablespace level, of course we know that after 10 Gb, the flashback feature can also solve the above problems. Since I have never used this feature before, I will take a look at the entire operation process (For details, refer to the Database Backup and Recovery Advanced User's Guide document)

1. Simulate the environment
1.1 create a test table space and test objects
[Oracle @ sourcedb oradata] $ sqlplus/as sysdba
SQL> create tablespace test_tspitr datafile '/oradata/wilson/test_tspitr01.dbf' size 10 M;
SQL> create table test (id int );
SQL> begin
For I in 1 .. 10 loop
Insert into test values (I );
End loop;
Commit;
End;
/
1.2 back up a database
[Oracle @ sourcedb oradata] $ rman target/
RMAN> backup database format'/tmp/dbf _ % U' plus archivelog format'/tmp/arch _ % U' delete input;
1.3 Accidental deletion of data
SQL> select count (*) from test;

COUNT (*)
----------
10

SQL> delete test;

SQL> commit;

SQL> select count (*) from test;

COUNT (*)
----------
0

2. Determine the TSPITR target time
There are multiple methods, such as using flashback query and logmnr. I have confirmed this through flashback qery.
SQL> select count (*) from test;

COUNT (*)
----------
0
SQL> select count (*) from test as of timestamp to_timestamp ('2017-02-23 21:40:34 ', 'yyyy-MM-DD: HH24: MI: ss ');
COUNT (*)
----------
10

3. Solve the dependencies in the tablespace that requires recover
If you want to play a tablespace, you know that the tablespace must be self-contained. The dependency here is similar to this one.

3.1 identify Dependencies

SQL> conn/as sysdba
SQL> SELECT * FROM SYS. TS_PITR_CHECK
WHERE (
TS1_NAME IN ('test _ tspitr ')
AND TS2_NAME not in ('test _ tspitr ')
);
Own1 name1 subname1 obj1type ts1_na name2 subname2 obj2type own2 ts2_na cname reason
-------------------------------------------------------------------------------------------------------
Scott test table TEST_TSPITR TEST_IDX index scott users Tables and associated
Indexes not fully
Contained in
Recovery set
From the above output, we can judge that the index test_idx of the test table is not in the same tablespace as the table.
3.2 resolve dependency

There are two methods: first, the index is killed, and second, the index tablespace is added to the recovery queue. Here I select the first one.
SQL> drop index test_idx;

1 2 Next Page