7
Performing User-Managed TSPITR

This chapter describes how to perform user-managed tablespace point-in-time recovery (TSPITR) with the transportable tablespace feature.

This chapter includes the following topics:

• Introduction to User-Managed Tablespace Point-in-Time Recovery
• Preparing for Tablespace Point-in-Time Recovery: Basic Steps
• Restoring and Recovering the Auxiliary Database: Basic Steps
• Performing TSPITR with Transportable Tablespaces
• Performing Partial TSPITR of Partitioned Tables
• Performing TSPITR of Partitioned Tables When a Partition Has Been Dropped
• Performing TSPITR of Partitioned Tables When a Partition Has Split

Introduction to User-Managed Tablespace Point-in-Time Recovery

Tablespace point-in-time recovery (TSPITR) with the transportable tablespace feature enables you to quickly recover one or more tablespaces (other than the SYSTEM tablespace) to a time that is prior to the rest of the database.

User-managed TSPITR is most useful for recovering the following:

• An erroneous DROP TABLE or TRUNCATE TABLE operation
• An erroneous DROP TABLESPACE operation
• A table that is logically corrupted
• An incorrect batch job or other DML statement that has affected only a subset of the database
• A logical schema to a point different from the rest of the physical database when multiple schemas exist in separate tablespaces of one physical database
• A tablespace in a VLDB (very large database) when TSPITR is more efficient than restoring the whole database from a backup and rolling it forward (refer to "Preparing for Tablespace Point-in-Time Recovery: Basic Steps" before making any decisions)

TSPITR Terminology

Familiarize yourself with the following terms and abbreviations, which are used throughout this chapter:

TSPITR

Tablespace point-in-time recovery

Primary Database

The database containing the tablespace or tablespaces that you want to recover to a prior point in time.

Auxiliary Database

A copy of the current database that is restored from a backup. It includes restored backups of the following:

• Datafiles belonging to the SYSTEM tablespace
• Datafiles in the set of tablespaces to be recovered
• Datafiles belonging to a system managed undo tablespace (when you run the database in automatic undo management mode) or tablespace that contains rollback segments (when you run the database in manual undo management mode)

All backups must be from a point in time prior to the desired recovery time.

Recovery Set

All the tablespaces that require point-in-time recovery to be performed on them.

Recovery Set Self-Containment Check

All objects that are part of the recovery set must be self-contained: there can be no dependencies on objects outside the recovery set. For example, if a table is part of the recovery set and its indexes are in a separate tablespace, then the recovery set must include the tablespace containing the index. Alternatively, the index can be dropped. The recovery set tablespaces can be checked for self-containment with the procedure DBMS_TTS.TRANSPORT_SET_CHECK.

Auxiliary Set

Any other items required for restoring the auxiliary database, including:

• Backup control file
• Datafiles from the SYSTEM tablespace
• Datafiles in an undo tablespace or datafiles containing rollback segments

Transportable Tablespace

A rapid method of transporting tablespaces across databases by unplugging them from a source database and plugging them into a target database. The unplugging and plugging is done with the Export and Import utilities. Note that there is no actual export and import of the table data, but simply an export and import of internal metadata. During the procedure, the datafiles of the transported tablespaces are made part of the target database.

TSPITR Methods

In releases prior to Oracle9i, you had the following two methods for performing user-managed TSPITR:

• Traditional user-managed TSPITR, which required you to create a special type of database called a clone database
• User-managed TSPITR with the transportable tablespace feature

Oracle9i TSPITR should be performed by using the transportable tablespace feature. This procedure is relatively easy to use and is less error prone than the traditional method, which is currently deprecated (although not yet unsupported).

Conceptually, TSPITR is performed by dropping the tablespaces to be recovered from the primary database, restoring a copy of the database called an auxiliary database and recovering it to the desired point in time, then transporting the relevant tablespaces from the auxiliary database to the current version of the primary database.

For ease of use, it is highly recommended that you place the auxiliary and primary databases on different hosts. Nevertheless, you can also perform TSPITR when the databases are located on the same host.

The basic procedure for performing user-managed TSPITR is as follows:

1. Take the tablespaces requiring TSPITR offline
2. Plan the setup of the auxiliary database.
3. Create the auxiliary database and recover it to the desired point in time.
4. Drop the tablespaces requiring TSPITR from the primary database.
5. Use the transportable tablespace feature to transport the set of tablespaces from the auxiliary database to the primary database.

   See Also: Oracle9i Database Administrator's Guide for a complete account of how to use the transportable tablespace feature

Preparing for Tablespace Point-in-Time Recovery: Basic Steps

TSPITR requires careful planning. Before proceeding you should read this chapter thoroughly.

This section contains the following topics:

• Step 1: Review TSPITR Requirements
• Step 2: Identify All of the Files in the Recovery and Auxiliary Set Tablespaces
• Step 3: Determine Whether Objects Will Be Lost
• Step 4: Choose a Method for Connecting to the Auxiliary Instance
• Step 5: Create an Oracle Password File for the Auxiliary Instance
• Step 6: Create the Initialization Parameter File for the Auxiliary Instance

  Caution: You should not perform TSPITR for the first time on a production system, or when there is a time constraint.

Step 1: Review TSPITR Requirements

Satisfy the following requirements before performing TSPITR:

• Ensure that you have backups of all datafiles in the recovery and auxiliary set tablespaces. The datafile backups must have been created before the desired TSPITR time.
• Ensure that you have a control file backup that is usable on the auxiliary database. To be usable, the control file must meet these requirements:
  • The control file must have been backed up before the desired TSPITR time.
  • The control file must have been backed up with the following SQL statement, where cf_name refers to the fully specified filename:

    ALTER DATABASE BACKUP CONTROLFILE TO 'cf_name';
    

• Ensure that all files constituting the recovery set tablespaces are in the recovery set on the auxiliary database; otherwise, the export phase during tablespace transport fails.
• Allocate enough disk space on the auxiliary host to accommodate the auxiliary database.
• Provide enough real memory to start the auxiliary instance.

  See Also: "Step 6: Create the Initialization Parameter File for the Auxiliary Instance"

Step 2: Identify All of the Files in the Recovery and Auxiliary Set Tablespaces

Before you create the auxiliary database, make sure that you connect to the primary database with administrator privileges and obtain all of the following information about the primary database:

• The filenames of the datafiles in the recovery set tablespaces
• The filenames of the datafiles in the SYSTEM tablespace
• The filenames of the datafiles in an undo tablespace or datafiles containing rollback segments
• The filenames of the control files

The following useful query displays the filenames of all datafiles, control files, and online redo logs in the database:

SELECT NAME FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE;

To determine the filenames of the datafiles in the SYSTEM and recovery set tablespaces, execute the following query and replace RECO_TBS_1, RECO_TBS_2, and so forth with the names of the recovery set tablespaces:

SELECT t.NAME AS "reco_tbs", d.NAME AS "dbf_name"
       FROM V$DATAFILE d, V$TABLESPACE t 
WHERE t.TS# = d.TS#
AND t.NAME IN ('SYSTEM', 'RECO_TBS_1', 'RECO_TBS_2');

If you run the database in manual undo management mode, then the following query displays the names of the tablespaces containing rollback segments as well as the names of the datafiles in the tablespaces:

SELECT r.TABLESPACE_NAME AS "rbs_tbs", d.FILE_NAME AS "dbf_name"
       FROM DBA_ROLLBACK_SEGS r, DBA_DATA_FILES d
WHERE r.TABLESPACE_NAME=d.TABLESPACE_NAME;

If you run the database in automatic undo management mode, then the following query displays the names of the undo tablespaces as well as the names of the datafiles in the tablespaces:

SELECT u.TABLESPACE_NAME AS "undo_tbs", d.FILE_NAME AS "dbf_name"
       FROM DBA_UNDO_EXTENTS u, DBA_DATA_FILES d
WHERE u.TABLESPACE_NAME=d.TABLESPACE_NAME;

Step 3: Determine Whether Objects Will Be Lost

When TSPITR is performed on a tablespace, any objects created after the recovery time are lost. To determine which objects will be lost, query the TS_PITR_OBJECTS_TO_BE_DROPPED view on the primary database. The contents of the view are described in Table 7-1.

Table 7-1 TS_PITR_OBJECTS_TO_BE_DROPPED View  

Column Name	Meaning
OWNER	Owner of the object to be dropped.
NAME	The name of the object that will be lost as a result of TSPITR
CREATION_TIME	Creation time stamp for the object.
TABLESPACE_NAME	Name of the tablespace containing the object.

When querying this view, supply all the elements of the date field, otherwise the default setting is used. Also, use the TO_CHAR and TO_DATE functions. For example, with a recovery set consisting of sales_1 and sales_2, and a recovery point in time of '2000-06-02:07:03:11', execute the following SQL script:

SELECT OWNER, NAME, TABLESPACE_NAME, TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS')
       FROM SYS.TS_PITR_OBJECTS_TO_BE_DROPPED 
WHERE TABLESPACE_NAME IN ('SALES_1','SALES_2') 
AND CREATION_TIME > TO_DATE('00-JUN-02:07:03:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;

Step 4: Choose a Method for Connecting to the Auxiliary Instance

You must be able to connect to the auxiliary instance. You can either use Oracle Net or operating system authentication. To learn how to configure networking files, refer to Oracle9i Net Services Administrator's Guide.

Step 5: Create an Oracle Password File for the Auxiliary Instance

For information about creating and maintaining Oracle password files, refer to the Oracle9i Database Administrator's Guide. If you do not use a password file, then you can skip this step.

Step 6: Create the Initialization Parameter File for the Auxiliary Instance

Create a new initialization parameter file rather than copying and then editing the production database initialization parameter file. Save memory by using low settings for parameters such as the following:

• DB_CACHE_SIZE
• SHARED_POOL_SIZE
• LARGE_POOL_SIZE

Note that reducing the preceding parameter settings can prevent the auxiliary database from starting when other dependent parameters are set too high--for example, the initialization parameter ENQUEUE_RESOURCES, which allocates memory from within the shared pool.

The auxiliary database can be either on the same host as the primary database or on a different host. Because the auxiliary database filenames are identical to the primary database filenames in the auxiliary control file, you must rename them in this control file so that they point to the restored locations. If the auxiliary database is on the same machine as the primary database, or if the auxiliary database is on a different machine that uses different path names, then you must rename the control files, datafiles, and online redo logs. If the auxiliary database is on a different machine with the same path names, then you can rename just the online redo logs.

Set the parameters shown in Table 7-2 in the auxiliary initialization parameter file.

Table 7-2 Auxiliary Initialization Parameters  

Parameter	Purpose
DB_NAME	Names the auxiliary database. Leave the name of the auxiliary database the same as the primary database.
CONTROL_FILES	Identifies auxiliary control files. Set to the filename of the auxiliary control file. Make sure the control file name is different from the primary database control file name.
LOCK_NAME_SPACE	Allows the auxiliary database to start even though it has the same name as the primary database. Set to any unique value, for example, = AUX. This parameter is only needed if the auxiliary and primary database are on the same host.
DB_FILE_NAME_CONVERT	Uses patterns to convert filenames for the datafiles of the auxiliary database. This parameter is only necessary if you are either restoring the auxiliary database on the same host as the primary host, or on a different host that uses different path names from the primary host.
LOG_FILE_NAME_CONVERT	Uses patterns to convert filenames for the online redo logs of the auxiliary database. This parameter is mandatory.
LOG_ARCHIVE_DEST_1	Specifies the default directory containing the archived redo logs required for recovery.This parameter specifies the location on the auxiliary host in which the archived logs will be located.
LOG_ARCHIVE_FORMAT	Specifies the format of the archived logs. You should use the same format setting used in the primary initialization parameter file.

Set other parameters as needed, including the parameters that allow you to connect as SYSDBA through Oracle Net.

For example, the auxiliary parameter file for a database on the same host as the primary could look like the following:

DB_NAME = prod1
CONTROL_FILES = /oracle/aux/cf1.f
LOCK_NAME_SPACE = aux
DB_FILE_NAME_CONVERT=("/oracle/dbs/","/oracle/aux/")
LOG_FILE_NAME_CONVERT=("/oracle/dbs/","/oracle/aux/")
LOG_ARCHIVE_DEST_1 = 'LOCATION=/oracle/work/arc_dest/arc'
LOG_ARCHIVE_FORMAT = r_%t_%s.arc

The auxiliary parameter file for a database on a different host with the same path names as the primary could look like the following:

DB_NAME = prod1
CONTROL_FILES = /oracle/aux/cf1.f
LOG_FILE_NAME_CONVERT=("/oracle/dbs/","/oracle/aux/")
LOG_ARCHIVE_DEST_1 = 'LOCATION=/oracle/work/arc_dest/arc'
LOG_ARCHIVE_FORMAT = r_%t_%s.arc

Restoring and Recovering the Auxiliary Database: Basic Steps

The procedure for restore and recovery of the auxiliary database differs depending on whether the auxiliary database is on the same host as the primary database. The examples in this section assume:

• You are performing TSPITR on production database called prod1 located on host prim_host.
• The recovery set tablespaces are sales_1 and sales_2. Tablespace sales_1 contains datafile /oracle/dbs/sales_1.f and tablespace sales_2 contains datafile /fs2/sales_2.f.
• The auxiliary set contains the SYSTEM tablespace datafile /oracle/dbs/system.f, the undo tablespace datafile /oracle/dbs/undo.f, and the control file /oracle/dbs/cf1.f.
• The online redo logs are named /oracle/dbs/log1.f and /oracle/dbs/log2.f.
• All the primary database files are contained in /oracle/dbs

The different cases are described in the following sections:

• Restoring and Recovering the Auxiliary Database on the Same Host
• Restoring the Auxiliary Database on a Different Host with the Same Path Names
• Restoring the Auxiliary Database on a Different Host with Different Path Names

Restoring and Recovering the Auxiliary Database on the Same Host

The following examples assume the case in which you restore the auxiliary database to the same host as the primary database. In this scenario, all of the primary database files are contained in /oracle/dbs, and you want to restore the auxiliary database to /oracle/dbs/aux. So, you set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT to convert the filenames from /oracle/dbs to /oracle/dbs/aux.

Perform the following tasks to restore and recover the auxiliary database:

1. Restore the auxiliary set and the recovery set to a location different from that of the primary database. For example, assume that the auxiliary set consists of the following files:

   /oracle/dbs/cf1.f      # control file
   /oracle/dbs/undo.f     # datafile in undo tablespace
   /oracle/dbs/system.f   # datafile in SYSTEM tablespace
   
   

   And the recovery set consists of the following datafiles:

   /oracle/dbs/sales_1.f  # datafile in sales_1 tablespace
   /oracle/dbs/sales_2.f  # datafile in sales_2 tablespace
   
   

   You can restore backups of the auxiliary set files and recovery set files to a new location as follows:

   cp /backup/cf1.f /aux/cf1.f
   cp /backup/undo.f /aux/undo.f
   cp /backup/system.f /aux/system.f
   cp /backup/sales_1.f /aux/sales_1.f
   cp /backup/sales_2.f /aux/sales_2.f
   
   

2. Start the auxiliary database without mounting it, specifying the initialization parameter file if necessary. For example, enter:

   STARTUP NOMOUNT PFILE=/aux/initAUX.ora
   
   

3. Mount the auxiliary database, specifying the CLONE keyword:

   ALTER DATABASE MOUNT CLONE DATABASE;
   
   

   The CLONE keyword causes Oracle to take all datafiles offline automatically.

4. Manually rename all auxiliary database files to reflect their new locations only if these files are not renamed by DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT. In our scenario, all datafiles and online redo logs are renamed by initialization parameters, so no manual renaming is necessary.
5. Run the following SQL script to ensure that all datafiles are named correctly:

   SELECT NAME FROM V$DATAFILE
   UNION ALL
   SELECT MEMBER FROM V$LOGFILE
   UNION ALL
   SELECT NAME FROM V$CONTROLFILE
   /
   
   

   If not, then rename the files manually as in the previous step.

6. Bring only the datafiles in the auxiliary and recovery set tablespaces online. For example, bring the four datafiles in the recovery and auxiliary sets online:

   ALTER DATABASE DATAFILE /oracle/dbs/aux/system.f ONLINE;
   ALTER DATABASE DATAFILE /oracle/dbs/aux/sales_1.f ONLINE;
   ALTER DATABASE DATAFILE /oracle/dbs/aux/sales_2.f ONLINE;
   ALTER DATABASE DATAFILE /oracle/dbs/aux/undo.f ONLINE;
   

   Note: The export phase of TSPITR will not work if all the files of each recovery set tablespace are not online.

   At this point, the auxiliary database is mounted and ready for media recovery.

7. Recover the auxiliary database to the specified point in time with the USING BACKUP CONTROLFILE option. Use any form of incomplete recovery as described in "Performing Incomplete User-Managed Media Recovery". The following example uses cancel-based incomplete recovery:

   RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
   
   

8. Open the auxiliary database with the RESETLOGS option using the following statement:

   ALTER DATABASE OPEN RESETLOGS;
   

Restoring the Auxiliary Database on a Different Host with the Same Path Names

The following example assumes that you create the auxiliary database on a different host called aux_host. The auxiliary host has the same path names as the primary host. Hence, you do not need to rename the auxiliary database datafiles. So, you do not need to set DB_FILE_NAME_CONVERT, although you should set LOG_FILE_NAME_CONVERT.

To restore and recover the auxiliary database:

1. Restore the auxiliary set and the recovery set to the auxiliary host. For example, assume that the auxiliary set consists of the following files:

   /oracle/dbs/cf1.f      # control file
   /oracle/dbs/undo.f     # datafile in undo tablespace
   /oracle/dbs/system.f   # datafile in SYSTEM tablespace
   
   

   And the recovery set consists of the following datafiles:

   /oracle/dbs/sales_1.f  # 1st datafile in sales_1 tablespace
   /oracle/dbs/sales_2.f  # 2nd datafile in sales_2 tablespace
   
   

   These files will occupy the same locations in the auxiliary host.

2. Start the auxiliary database without mounting it, specifying the initialization parameter file if necessary. For example, enter:

   STARTUP NOMOUNT PFILE=/aux/initAUX.ora
   
   

3. Mount the auxiliary database, specifying the CLONE keyword:

   ALTER DATABASE MOUNT CLONE DATABASE;
   
   

   The CLONE keyword causes Oracle to take all datafiles offline automatically.

4. Rename all auxiliary database files to reflect their new locations only if these files are not renamed by DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT. In our scenario, the datafiles do not require renaming, and the logs are converted with LOG_FILE_NAME_CONVERT. So, no manual renaming is necessary.
5. Run the following script in SQL*Plus to ensure that all datafiles are named correctly.

   SELECT NAME FROM V$DATAFILE
   UNION ALL
   SELECT MEMBER FROM V$LOGFILE
   UNION ALL
   SELECT NAME FROM V$CONTROLFILE
   /
   
   

   If not, then rename them manually as in the previous step.

6. Bring all datafiles in the auxiliary and recovery set tablespaces online. For example, bring the four datafiles in the recovery and auxiliary sets online:

   ALTER DATABASE DATAFILE /oracle/dbs/system.f ONLINE;
   ALTER DATABASE DATAFILE /oracle/dbs/sales_1.f ONLINE;
   ALTER DATABASE DATAFILE /oracle/dbs/sales_2.f ONLINE;
   ALTER DATABASE DATAFILE /oracle/dbs/undo.f ONLINE;
   

   Note: The export phase of TSPITR will not work if all the files of each recovery set tablespace are not online.

   At this point, the auxiliary database is mounted and ready for media recovery.

7. Recover the auxiliary database to the specified point in time with the USING BACKUP CONTROLFILE option. Use any form of incomplete recovery as described in "Performing Incomplete User-Managed Media Recovery". The following example uses cancel-based incomplete recovery:

   RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
   
   

8. Open the auxiliary database with the RESETLOGS option using the following statement:

   ALTER DATABASE OPEN RESETLOGS;

Restoring the Auxiliary Database on a Different Host with Different Path Names

This case should be treated exactly like "Restoring and Recovering the Auxiliary Database on the Same Host". The same guidelines for renaming files apply in both cases.

Performing TSPITR with Transportable Tablespaces

After you have completed the preparation stage, begin the actual TSPITR procedure as described in Oracle9i Database Administrator's Guide. The procedure occurs in the following steps:

• Step 1: Unplugging the Tablespaces from the Auxiliary Database
• Step 2: Transporting the Tablespaces into the Primary Database

Step 1: Unplugging the Tablespaces from the Auxiliary Database

In this step, you recover the auxiliary database to the desired noncurrent time, then unplug the desired tablespaces.

To unplug the auxiliary database tablespaces:

1. Make the tablespaces in the recovery set read-only by running the ALTER TABLESPACE ... READ ONLY statement. For example, make sales_1 and sales_2 read-only as follows:

   ALTER TABLESPACE sales_1 READ ONLY;
   ALTER TABLESPACE sales_2 READ ONLY;
   
   

2. Ensure that the recovery set is self-contained. For example:

   EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2',TRUE,TRUE);
   
   

3. Query the transportable tablespace violations table to manage any dependencies. For example:

   SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
   
   

   This query should return no rows after all dependencies are managed. Refer to Oracle9i Database Administrator's Guide for more information about this table.

4. Generate the transportable set by running the Export utility as described in Oracle9i Database Administrator's Guide. Include all tablespaces in the recovery set, as in the following example:

   % exp SYS/pwd TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2) TTS_FULL_CHECK=y
   
   

   This command generates an export file named expdat.dmp.

Step 2: Transporting the Tablespaces into the Primary Database

In this step, you transport the recovery set tablespaces into the primary database.

To plug the recovery set tablespaces into the primary database:

1. In the primary database (not the auxiliary database), drop the tablespaces in the recovery set through the DROP TABLESPACE statement. For example:

   DROP TABLESPACE sales_1 INCLUDING CONTENTS;
   DROP TABLESPACE sales_2 INCLUDING CONTENTS;
   
   

2. Restore the recovery set datafiles from the auxiliary database to the recovery set file locations in the primary database. For example:

   % cp /net/aux_host/aux/sales_1.f /net/primary_host/oracle/dbs/sales_1.f
   % cp /net/aux_host/aux/sales_2.f /net/primary_host/oracle/dbs/sales_2.f
   
   

3. Move the export file expdat.dmp to the primary host. For example, enter:

   % cp /net/aux_host/aux/expdat.dmp /net/primary_host/oracle/dbs/expdat.dmp
   
   

4. Plug in the transportable set into the primary database by running Import as described in Oracle9i Database Administrator's Guide. For example:

   % imp TRANSPORT_TABLESPACE=y FILE=expat.dmp
      DATAFILES=('/oracle/dbs/sales_1.f','/oracle/dbs/sales_2.f')
   
   

5. Make the recovered tablespaces read write by issuing the ALTER TABLESPACE READ WRITE statement. For example:

   ALTER TABLESPACE sales_1 READ WRITE;
   ALTER TABLESPACE sales_2 READ WRITE;
   
   

6. Back up the recovered tablespaces with an operating system utility as described in "Making User-Managed Backups of Online Tablespaces and Datafiles".

   Caution: You must back up the tablespace because otherwise you might lose it. For example, a media failure occurs, but the archived logs from the last backup of the database do not logically link to the recovered tablespaces. If you attempt to recover any recovery set tablespaces from a backup taken before TSPITR, then recovery fails.

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
• Step 2: Drop the Indexes on the Partition Being Recovered
• Step 3: Exchange Partitions with Standalone Tables
• Step 4: Drop the Recovery Set Tablespace
• Step 5: Create Tables at Auxiliary Database
• Step 6: Drop Indexes on Partitions Being Recovered
• Step 7: Exchange Partitions with Standalone Tables on the Auxiliary Database
• Step 8: Transport the Recovery Set Tablespaces
• 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 TSPITR of Partitioned Tables When a Partition Has Been Dropped".

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, non-partitioned 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) 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).

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

Performing TSPITR of Partitioned Tables When a Partition Has Been Dropped

This section describes how to perform TSPITR on partitioned tables when a partition has been dropped, and includes the following steps:

• Step 1: Find the Low and High Range of the Partition that Was Dropped
• Step 2: Create a Temporary Table
• Step 3: Delete Records From the Partitioned Table
• Step 4: Drop the Recovery Set Tablespace
• Step 5: Create Tables at the Auxiliary Database
• Step 6: Drop Indexes on Partitions Being Recovered
• Step 7: Exchange Partitions with Standalone Tables
• Step 8: Transport the Recovery Set Tablespaces
• Step 9: Insert Standalone Tables into Partitioned Tables
• Step 10: Back Up the Recovered Tablespaces in the Primary Database

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.

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 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 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);
 

Note that 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 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.

Performing TSPITR of Partitioned Tables When a Partition Has Split

This section describes how to recover partitioned tables when a partition has been split, and includes the following sections:

• Step 1: Drop the Lower of the Two Partitions at the Primary Database
• Steps 2: Follow Same Procedure as for Partial TSPITR of Partitioned Tablespaces

Step 1: Drop the Lower of the Two Partitions at the Primary Database

For each partition you wish to recover whose range has been split, drop the lower of the two partitions so that the higher expands downwards. In other words, the higher partition has the same range as before the split. For example, if P1 was split into partitions P1A and P1B, then P1B must be dropped, meaning that partition P1A now has the same range as P1.

For each partition that you wish to recover whose range has split, create a table that has exactly the same column names and column datatypes as the partitioned table you are recovering. For example, execute the following, replacing the variables with the appropriate values:

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

These tables will be used to exchange each recovery set partition in Step 3.

Steps 2: Follow Same Procedure as for Partial TSPITR of Partitioned Tablespaces

Follow the same procedure as for "Performing Partial TSPITR of Partitioned Tables", but skip the first step of this procedure. In other words, start with "Step 2: Drop the Indexes on the Partition Being Recovered" and follow all subsequent steps.