Skip Headers
Oracle® Database Backup and Recovery Advanced User's Guide
10g Release 2 (10.2)

Part Number B14191-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Performing Partial TSPITR of Partitioned Tables

Partitioned tables can span multiple tablespaces. Follow this procedure only if the recovery set does not fully contain all of the partitions.

This section describes how to perform partial TSPITR of partitioned tables that have a range that has not changed or expanded, and includes the following steps:

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").

Note:

The table and the partition must belong to the same schema.

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.

Note:

The table and the partition must belong to the same schema.

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.