Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
|
|
View PDF |
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
Step 7: Exchange Partitions with Standalone Tables on the Auxiliary Database
Step 9: Exchange Partitions with Standalone Tables on the Primary Database
Step 10: Back Up the Recovered Tablespaces in the Primary Database
Note:
Often you have to recover the dropped partition along with recovering a partition whose range has expanded. Refer to "Performing User-Managed TSPITR of Partitioned Tables With a Dropped Partition".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.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.
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;
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;
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.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").
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;
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".
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.