Performing Partial TSPITR of Partitioned Tables

Step 1: Create a Table on the Primary Database for Each Partition Being Recovered

This table should have the exact same column names and column datatypes as the partitioned table you are recovering. Create the table using the following template:

CREATE TABLE new_table AS 
  SELECT * FROM partitioned_table  
  WHERE 1=2;
 

These tables are used to swap each recovery set partition (see "Step 3: Exchange Partitions with Standalone Tables").

Step 2: Drop the Indexes on the Partition Being Recovered

Drop the indexes on the partition you wish to recover, or create identical, nonpartitioned indexes that exist on the partition you wish to recover. If you drop the indexes on the partition being recovered, then you need to drop them on the auxiliary database (see "Step 6: Drop Indexes on Partitions Being Recovered"). Rebuild the indexes after TSPITR is complete.

Step 3: Exchange Partitions with Standalone Tables

Exchange each partition in the recovery set with its associated standalone table (created in "Step 1: Create a Table on the Primary Database for Each Partition Being Recovered") by issuing the following statement, replacing the variables with the names of the appropriate objects:

ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE table_name; 

Step 4: Drop the Recovery Set Tablespace

On the primary database, drop each tablespace in the recovery set. For example, enter the following, replacing tablespace_name with the name of the tablespace:

DROP TABLESPACE tablespace_name INCLUDING CONTENTS;

Step 5: Create Tables at Auxiliary Database

After recovering the auxiliary database and opening it with the RESETLOGS option, create a table in the SYSTEM tablespace that has the same column names and column data types as the partitioned table you are recovering­. You must create the table in the SYSTEM tablespace: otherwise, Oracle issues the ORA-01552 error.

Create a table for each partition you wish to recover. These tables are used later to swap each recovery set partition.

Step 6: Drop Indexes on Partitions Being Recovered

Drop the indexes on the partition you wish to recover, or create identical, non-partitioned indexes that exist on the partition you wish to recover (on the table created in "Step 1: Create a Table on the Primary Database for Each Partition Being Recovered").

Step 7: Exchange Partitions with Standalone Tables on the Auxiliary Database

For each partition in the auxiliary database recovery set, exchange the partitions with the standalone tables (created in "Step 5: Create Tables at Auxiliary Database") by executing the following SQL script, replacing the variables with the appropriate object names:

ALTER TABLE partitioned_table_name 
EXCHANGE PARTITION partition_name 
WITH TABLE table_name; 

Step 8: Transport the Recovery Set Tablespaces

Export the recovery set tablespaces from the auxiliary database and then import them into the primary database as described in "Performing User-Managed TSPITR with Transportable Tablespaces".

Step 9: Exchange Partitions with Standalone Tables on the Primary Database

For each recovered partition on the primary database, swap its associated standalone table with the following statement, replacing the variables with the appropriate object names:

ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE table_name;
 

If the associated indexes have been dropped, then re-create them.

Step 10: Back Up the Recovered Tablespaces in the Primary Database

Back up the recovered tablespaces on the primary database. Failure to do so results in loss of data in the event of media failure.