Performing User-Managed TSPITR of Partitioned Tables With a Dropped Partition

Step 1: Find the Low and High Range of the Partition that Was Dropped

When a partition is dropped, the range of the partition preceding it expands downwards. Therefore, there may be records in the preceding partition that should actually be in the dropped partition after it has been recovered. To ascertain this, run the following SQL script at the primary database, replacing the variables with the appropriate values:

SELECT * FROM partitioned_table 
  WHERE relevant_key 
  BETWEEN low_range_of_partition_that_was_dropped 
  AND high_range_of_partition_that_was_dropped;

Step 2: Create a Temporary Table

If any records are returned, then create a temporary table in which to store these records so that if necessary they can be inserted into the recovered partition later.

Step 3: Delete Records From the Partitioned Table

Delete all the records stored in the temporary table from the partitioned table.

DELETE FROM partitioned_table 
  WHERE relevant_key 
  BETWEEN low_range_of_partition_that_was_dropped 
  AND high_range_of_partition_that_was_dropped;

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 the Auxiliary Database

After opening the auxiliary database 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 that you want to recover. These tables will be 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, nonpartitioned indexes that exist on the partition you wish to recover.

Step 7: Exchange Partitions with Standalone Tables

For each partition in the auxiliary recovery set, exchange the partitions into the standalone tables created in "Step 5: Create Tables at the Auxiliary Database" by issuing the following statement, replacing the variables with the appropriate values:

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: Insert Standalone Tables into Partitioned Tables

At this point you must insert the standalone tables into the partitioned tables; you can do this by first issuing the following statement, replacing the variables with the appropriate values:

ALTER TABLE table_name 
  SPLIT PARTITION partition_name AT (key_value) 
  INTO 
  (PARTITION partition_1_name TABLESPACE tablespace_name, 
   PARTITION partition_2_name TABLESPACE tablespace_name);
 

At this point, partition 2 is empty because keys in that range have already been deleted from the table.

Issue the following statement to swap the standalone table into the partition, replacing the variables with the appropriate values:

ALTER TABLE EXCHANGE PARTITION partition_name WITH TABLE table_name; 

Now insert the records saved in "Step 2: Create a Temporary Table" into the recovered partition (if desired).

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

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