Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
|
|
View PDF |
This section describes how to perform TSPITR on partitioned tables when a partition has been dropped. It includes the following steps:
Step 1: Find the Low and High Range of the Partition that Was Dropped
Step 10: Back Up the Recovered Tablespaces in the Primary Database
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;
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.
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;
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 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.
Drop the indexes on the partition you wish to recover, or create identical, nonpartitioned indexes that exist on the partition you wish to recover.
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;
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".
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).
Note:
If the partition that has been dropped is the last partition in the table, then add it with theALTER
TABLE
ADD
PARTITION
statement.