Introduction to Tablespace Point-in-Time Recovery

Tablespace Point-In-Time Recovery (TSPITR) enables you to quickly recover one or more tablespaces to a point-in-time that is different from that of the rest of the database. TSPITR is most useful in the following situations:

To recover from an erroneous drop or truncate table operation.

To recover a table that has become logically corrupted.

To recover from an incorrect batch job or other DML statement that has affected only a subset of the database.

In cases where there are multiple logical databases in separate tablespaces of one physical database, and where one logical database must be recovered to a point different from that of the rest of the physical database.

For VLDBs (very large databases) even if a full database point-in-time recovery would suffice, you might choose to do tablespace point-in-time recovery rather than restore the whole database from a backup and perform a complete database roll-forward (see "Planning for Tablespace Point-in-Time Recovery" before making any decisions).

Similar to a table export, TSPITR enables you to recover a consistent data set; however, the data set is the entire tablespace rather than just one object.

Prior to Oracle8, point-in-time recovery could only be used on a subset of a database by:

Creating a copy of the database

Rolling the copied database forward to the desired point in time

Exporting the desired objects from the copied database

Dropping the relevant objects from the production database

Importing the objects into the production database

However, there was a performance overhead associated with exporting and importing large objects.

TSPITR enables you to recover a subset of a database, and optimizes the export/import phase by enabling you to make an operating system-level datafile copy (of the relevant files of the recovered database) to the production database. Data dictionary information about the file's content (for example, the recovered segments within the file) is transferred to the production database by means of a meta-data export/import from the copied database. The copied file is also added to the production database via this import.

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

TSPITR

Tablespace Point-in-Time Recovery

Clone Database

The copied database used for recovery in Oracle 8 TSPITR is called a "clone database", and has various substantive differences from a regular database.

Recovery Set

Tablespaces that require point-in-time recovery to be performed on them.

Auxiliary Set

Any other items required for TSPITR, including:

backup control file

system tablespaces

datafiles containing rollback segments

temporary tablespace (optional)

A small amount of space is required by export for sort operations. If a copy of the temporary tablespace is not included in the auxiliary set, then you must provide sort space either by creating a new temporary tablespace after the clone has been started up, or by setting autoextend to ON on the system tablespace files.

Planning for Tablespace Point-in-Time Recovery

TSPITR is a complicated procedure and requires careful planning. Before proceeding you should read all of this chapter thoroughly.

warning:

You should not perform TSPITR for the first time on a production system, or during circumstances where there is a time constraint.

Limitations Advisory

This section describes the limitations of TSPITR.

The primary issue you should consider when deciding whether or not to perform TSPITR is the possibility of application-level inconsistencies between tables in recovered and unrecovered tablespaces (due to implicit rather than explicit referential dependencies). You must understand these dependencies, and also have the means to resolve any possible inconsistencies before proceeding.

TS_PITR_CHECK Does Not Check for Objects Owned by SYS

The TS_PITR_CHECK view provides information on dependencies and restrictions that can prevent TSPITR from proceeding. However, TS_PITR_CHECK does not provide information about dependencies and restrictions for objects owned by SYS.

If there are any objects, including undo segments, owned by SYS in the recovery set, there is no guarantee that you can successfully recover these objects (because TSPITR utilizes the Export and Import utilities, which do not operate on objects owned by SYS). To find out which recovery set objects are owned by SYS, issue the following statement:

SELECT OBJECT_NAME, OBJECT_TYPE
FROM SYS.DBA_OBJECTS
WHERE TABLESPACE_NAME IN ('<tablespacename1>','<tablespacename','
<tablespace name N') and owner = 'SYS';

TS_PITR_CHECK Does Not Detect Snapshot Tables

The TS_PITR_CHECK view does not detect snapshot tables (it does detect snapshot logs); they are exported as stand-alone tables. Thus, if a snapshot is dropped at time 3, and a backup from time 1 is used to roll forward to time 2, after TSPITR is complete the snapshot table will have been created as a stand-alone table, but without its associated snapshot view.

Partitioned Tables and TS_PITR_CHECK

If any of the tablespaces supplied to the predicate contain the first segment of a partitioned table, then the result set of the TS_PITR_CHECK view is inverted. If tablespaces supplied to the predicate do not include the first segment of a partitioned table, then one row is returned for the partition in question. If the tablespaces supplied to the predicate contain the first segment of a partitioned table, the results are inverted (for example, one row is returned for every tablespace containing partitions of that partitioned table, but not the tablespace that was supplied to the predicate). Returned rows indicate that there is a conflict that you must resolve by exchanging the partitions with stand-alone tables.

Bitmap Indexes

You must drop and re-create bitmap indexes after you complete TSPITR. If you don't, they will be unusable. If any bitmap indexes exist on the tables, imports will fail even if the bitmap indexes have been dropped from the primary database. An incorrect index segment will also be created, despite the failure, and you will have to drop and re-build the index.

Non-Partitioned Global Indexes

The TS_PITR_CHECK view does not detect non-partitioned global indexes of partitioned tables that are outside the recovery set. This is apparent when the view is queried manually and also during the export and import phase of TSPITR. After TSPITR completes, the old index still exists on the recovered table, even though no errors are returned. You must drop and re-create the index.

Note:

Because the index is still valid, queries that use the index will return incorrect rows.

General Restrictions

In addition to the preceding limitations, TSPITR has the following restrictions:

You cannot use TSPITR to recover dropped tablespaces.

You cannot use TSPITR to recover a tablespace that has been dropped and re-created with the same name.

You cannot use TSPITR to remove a datafile that has been added to the wrong tablespace. If the file was added after the point to which TSPITR is being performed, then the file will still be part of the tablespace (and will be empty) after TSPITR is complete.

You cannot use DML statements on the clone database--the clone database is for recovery purposes only.

After TSPITR is complete, you cannot use existing backups of the recovery set datafiles for recovery; instead, you must take fresh backups of the recovered files. If you attempt to recover using a backup taken prior to performing TSPITR, recovery will fail (with the error message ORA 1247, 00000, "database recovery through TSPITR of tablespace %s").

TSPITR does not recover optimizer statistics for objects that have had statistics calculated on them; statistics must be re-calculated after performing TSPITR.

The following object types are not allowed within the TSPITR recovery set:

replicated master tables

tables with varray columns

tables with nested tables

tables with external Bfiles

snapshot logs

snapshot tables

objects owned by SYS (including rollback segments)

Data Consistency and TSPITR

TSPITR provides views that can detect any data relationships between objects that are in the tablespaces being recovered and objects in the rest of the database. TSPITR will not successfully complete unless these relationships are managed, either by removing or suspending the relationship, or by including the related object within the recovery set.

TSPITR Requirements

You must satisfy the following requirements before performing TSPITR.

All files that comprise the recovery set tablespaces must be present in the recovery set, otherwise the export phase of TSPITR will fail (with the error message "1230: cannot make read only, file %s is offline").

You must create the control file backup in the auxiliary set using the following SQL statement:

ALTER DATABASE BACKUP CONTROLFILE TO '<controlfile_name>'

This control file backup must be created at a later time than the backup that is being used. If it's not, then you may encounter an error message (ORA-01152, file 1 was not restored from a sufficiently old backup).

You must have enough disk space to accommodate the clone database (for example, the auxiliary set and the recovery set).

Step 1: Find Out if Objects Will be Lost when Performing TSPITR

When TSPITR is performed on a tablespace, any objects created after the point to which TSPITR is being performed will be lost. To see 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 13-1:

Table 13-1 TS_PITR_OBJECTS_TO_BE_DROPPED View

Column Name

Null?

Type

OWNER

NOT NULL

VARCHAR2(30)

NAME

NOT NULL

VARCHAR2(30)

CREATION_TIME

NOT NULL

DATE

TABLESPACE_NAME

VARCHAR2(30)

When querying TS_PITR_OBJECTS_TO_BE_DROPPED, you must supply all the elements of the date field, otherwise the default setting will be used. You should also use the to_char and to_date functions. For example, with a recovery set consisting of TS1 and TS2, and a recovery point in time of '1997-06-02:07:03:11', you should issue the following query:

SVRMGR1> select owner, name, tablespace_name,
2> to_char(creation_time, 'YYYY-MM-DD:HH24:MI:SS'),
3> from ts_pitr_objects_to_be_dropped
4> where tablespace_name in ('TS1','TS2')
5> and
6> creation_time > to_date('97-JUN-02:07:03:11','YY-MON- DD:HH24:MI:SS')
7> order by tablespace_name, creation_time
8> / The information you find in TS_PITR_OBJECTS_TO_BE_DROPPED and TS_PITR_CHECK
can help you decide whether or not to perform TSPITR.

Step 2: Research and Resolve Dependencies on the Primary Database

You can use the TS_PITR_CHECK view to identify relationships between objects that overlap the recovery set boundaries. If this view returns rows when queried, you must investigate and correct the problem. TSPITR can proceed only when TS_PITR_CHECK view returns no rows. You should record all actions performed during this step so that you can retrace these relationships after completing TSPITR.

The TS_PITR_CHECK view will return rows unless you meet the following requirements:

All partitions of a partitioned table should be fully contained in the recovery set. If you need to recover only 1 or more of the partitions of a partitioned table, convert them to stand-alone tables (see "Performing Partial TSPITR of Partitioned Tables").

Querying the TS_PITR_CHECK View

Table 13-2 TS_PITR_CHECK View

The owner of the object preventing tablespace point-in-time recovery (see the REASON column for details)

OBJ1_NAME

VARCHAR2(30)

NOT NULL

The name of the object preventing tablespace point-in-time recovery

OBJ1_TYPE

VARCHAR2(15)

The object type for the object preventing tablespace point-in-time recovery

OBJ1_SUBNAME

VARCHAR2(30)

Subordinate to OBJ1_NAME

TS1_NAME

VARCHAR2(30)

NOT NULL

Name of the tablespace containing the object preventing tablespace point-in-time recovery

OBJ2_NAME

VARCHAR2(30)

The name of a second object which may be preventing tablespace point-in-time recovery (if NULL, object 1 is the only object preventing recovery)

OBJ2_TYPE

VARCHAR2(15)

The object type for the second object (will be NULL if OBJ2_NAME is NULL)

OBJ2_OWNER

VARCHAR2(30)

The owner of the second object (will be NULL if OBJ2_NAME is NULL)

OBJ2_SUBNAME

VARCHAR2(30)

Subordinate to OBJ2_NAME

TS2_NAME

VARCHAR2(30)

Name of the tablespace containing second object that may be preventing tablespace point-in-time recovery (-1 indicates not applicable)

CONSTRAINT_NAME

VARCHAR2(30)

Name of the constraint

REASON

VARCHAR2(78)

Reason why tablespace point-in-time recovery cannot proceed

You must supply a four-line predicate detailing the recovery set tablespace to query the TS_PITR_CHECK view. For example, with a recovery set consisting of TS1 and TS2, the SELECT statement against TS_PITR_CHECK would be as follows:

SVRMGR 1> SELECT *
2> FROM sys.ts_pitr_check
3> WHERE
4> (ts1_name in ('TS1','TS2')
5> AND ts2_name not in ('TS1','TS2'))
6> OR (ts1_name not in ('TS1','TS2')
7> AND ts2_name in ('TS1','TS2'))
8> /

Due to the number and width of the columns in the TS_PITR_CHECK view, you may want to format the columns as follows:

Sample Output

If the partitioned table TP has two partitions, P1 and P2, which exist in tablespaces TS1 and TS2 respectively, and there is a partitioned index defined on TP called TPIND, which has two partitions ID1 and ID2 (that exist in tablespaces ID1 and ID2 respectively) you would get the following output when TS_PITR_CHECK is queried against tablespaces TS1 and TS2 (assuming appropriate formatting):

You can see here that the table SYSTEM.TP has a partitioned index TPIND, which consists of two partitions, IP1 in tablespace ID1 and IP2 in tablespace ID2. Thus, you must decide to either drop TPIND or include ID1 and ID2 in the recovery set.

Step 3: Prepare the Primary Database for TSPITR

To prepare the primary database for TSPITR, perform the following tasks:

Issue the following statement on the primary database:

ALTER SYSTEM ARCHIVE LOG CURRENT;

Take offline any rollback segments in the recovery set (you do not have to take auxiliary set rollback segments offline) using the following statement:
ALTER ROLLBACK SEGMENT <segment name> OFFLINE;

Take offline immediate the recovery set tablespaces on the primary database using the following statement:
ALTER TABLESPACE <tablespace name> OFFLINE IMMEDIATE;

This prevents changes being made to the recovery set before TSPITR is complete.

Note:

If there is a subset of data (that is not physically or logically corrupt) you want to query within the recovery set tablespaces, you can alter the recovery set tablespaces on the primary database as READ ONLY for the duration of the recovery of the clone (this allows them to be queried but not altered). The recovery set tablespaces must be taken offline before integrating the clone files with the primary database (see "Step 10: Copy the Recovery Set Clone Files to the Primary Database").

Step 4: Prepare the Parameter Files for the Clone Database

Create the parameter file from a new init.ora file (rather than using the file from the production instance); you can save memory by using "small" settings for parameters like DB_BLOCK_BUFFERS, SHARED_POOL_SIZE, or LARGE_POOL_SIZE. However, if the production parameter files are used for the clone database, it's possible that reducing these parameters would prevent the clone database from starting up when other parameters are set too high (such as the parameter ENQUEUE_RESOURCES, which allocates memory from within the shared pool). You must change the following parameters:

CONTROL_FILES must point to the name and location of the clone control files

LOCK_NAME_SPACE must be set to a unique value, for example, lock_name_space=CLONE

Note:

LOCK_NAME_SPACE allows the clone database to start up even though it has the same name as the primary database (otherwise it would fail because the mount lock is hashed from the database name). You should not change the DB_NAME parameter.

Change the following parameters if required:

DB_FILE_NAME_CONVERT

LOG_FILE_NAME_CONVERT

These parameters are used to update the clone database control file with the locations of the clone database files.

For example, if the datafiles of the primary database reside in the directory /ora/primary, and the clone will reside in the directory /ora/clone, then the value of DB_FILE_NAME_CONVERT should be set to "primary","clone".

Step 5: Prepare Clone Database for TSPITR

Perform the following tasks to prepare the clone database for TSPITR:

Restore the auxiliary set and the recovery set to a location different from that of the primary database.

At this point, the database is automatically taken out of archivelog mode because it is a clone. All files are offline at this point as well. Even if the file name conversion parameters DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT have been set, you cannot assume that all the files of the clone database will be in the locations specified by these parameters--there may be some clone database files that have been restored to different locations due to constraints of disk space. Additionally, the only files necessary to the clone database are in the recovery set and the auxiliary set; there may be many other database files that do not fall into these two sets that you can leave offline.

If DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT have not been set, you must use the following statement to rename the files to reflect their new locations:
ALTER DATABASE RENAME FILE '<name of file in primary location>'
TO '<name of corresponding file in clone location>';

If DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT have been set, but there are files that have been restored to different locations, then they must be renamed at this point. Bring online all recovery set and auxiliary set files using the following SQL statement:
ALTER DATABASE DATAFILE '<datafile name>' ONLINE
;

Note:

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

Step 6: Recover the Clone Database

Recover the clone database up to the desired point by specifying the USING BACKUP CONTROLFILE option. You can use any form of interrupted recovery, including time-based or cancel-based recovery, as follows:

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL TIME 'YYYY-MM-DD:HH24:MI:SS';
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

If the clone database files are not online you will get an error message ('ORA 264: no recovery required').

Step 7: Open the Clone Database

Alter the clone database open resetlogs using the following statement:

ALTER DATABASE OPEN RESETLOGS;

Because the database is a clone database, only the SYSTEM rollback segment is brought online at this point; this prevents you from executing DML statements against any user tablespace. Any attempt to bring a user rollback segment online will fail (message ORA 1698'a clone database may only have SYSTEM rollback segment online').

Step 9: Export the Clone Database

If the export phase fails (with the error message "ORA 29308 view TS_PITR_CHECK failure"), re-query TS_PITR_CHECK, resolve the problem, and re-run the export. You need to perform the export phase of TSPITR as the user SYS, otherwise the export will fail (with the error message "ORA-29303: user does not login as SYS"). Shut down the clone database after a successful export.

Step 10: Copy the Recovery Set Clone Files to the Primary Database

If any recovery set tablespaces are READ ONLY on the primary database, you should change them to OFFLINE. Copy the recovery set files from the clone database to the primary database, taking care not to overwrite any auxiliary set files on the primary database.

Step 11: Import into the Primary Database

Import the recovery set meta-data into the primary database using the following command:

imp sys/<password> point_in_time_recover=true

This import also updates the copied file's file headers and integrates them with the primary database.

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

After TSPITR on a tablespace is complete, back up the tablespace.

warning:

it is critical that you back up the tablespace. Failure to do so could result in the loss of that tablespace. For example, you could lose the tablespace in the event of media failure because the archived logs from the last backup of that database do not logically link to the recovered tablespaces. If you attempt to recover any recovery set tablespaces from a backup taken before TSPITR, you will fail (and receive error message "ORA 1246, recovering files through TSPITR of tablespace %s").

Performing Partial TSPITR of Partitioned Tables

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 1: Create a Table on the Primary Database for Each Partition Being Recovered

Create a table on the primary database for each partition you wish to recover. This table should have the exact same column names and column datatypes as the partitioned table you are recovering. You can create the table using the following statement:

These tables will be used to swap each recovery set partition (see Step 3).

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 (on the table created in Step 1). If you drop the indexes on the partition being recovered you will also need to drop them on the clone database (see Step 6). You will also have to rebuild the indexes after TSPITR is complete.

Step 3: Exchange Partitions with Stand-Alone Tables

Exchange each partition in the recovery set with its associated stand-alone table (created in Step 1) by issuing the following command:

Step 4:Take the Recovery Set Tablespace Offline

On the primary database, take each recovery set tablespace offline by issuing the following statement:

ALTER TABLESPACE <tablespace name> OFFLINE IMMEDIATE;

This prevents any further changes to the recovery set tablespaces on the primary database.

Step 5: Create Tables at Clone Database

After recovering the clone and opening resetlogs, create a table that has the exact same column names and column datatypes as the partitioned table you are recovering--do this for each partition you wish 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, non-partitioned indexes that exist on the partition you wish to recover (on the table created in Step 1).

Step 7: Exchange Partitions with Stand-Alone Tables

For each partition in the clone database recovery set, exchange the partitions with the stand-alone tables (created in Step 5) by issuing the following statement:

If the export phase fails (with the error message "ORA 29308 view TS_PITR_CHECK failure"), re-query TS_PITR_CHECK, resolve the problem, and re-run the export. You need to perform the export phase of TSPITR as the user SYS, otherwise the export will fail (with the error message "ORA-29303: user does not login as SYS"). Shut down the clone database after a successful export.

Step 9: Copy the Recovery Set Datafiles to the Primary Database

If any recovery set tablespaces are READ ONLY on the primary database, you should change them to OFFLINE. Copy the recovery set datafiles from the clone database to the primary database, taking care not to overwrite any auxiliary set files on the primary database.

Step 10: Import into the Primary Database

Import the recovery set meta-data into the primary database using the following command:

imp sys/<password> point_in_time_recover=true

This import also updates the copied file's file headers and integrates them with the primary database.

Step 11: Bring Recovery Set Tablespace Online

At the primary database, bring each recovery set tablespace online by issuing the following statement:

ALTER TABLESPACE <tablespace name> ONLINE;

Step 12: Exchange Partitions with Stand-Alone Tables

For each recovered partition on the primary database, swap its associated stand-alone table back in using the following statement:

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

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

warning:

It is critical that you back up the tablespace. Failure to do so could result in the loss of that tablespace. For example, you could lose the tablespace in the event of media failure because the archived logs from the last backup of that database do not logically link to the recovered tablespaces. If you attempt to recover any recovery set tablespaces from a backup taken before TSPITR, you will fail (and receive error message "ORA 1246, recovering files through TSPITR of tablespace %s").

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

When a partition is dropped, the range of the partition above it expands downwards. Therefore, there may be records in the partition above that should actually be in the dropped partition after it has been recovered. To ascertain this, issue the following command at the primary database:

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, create a temporary table in which to store these records so that they can be inserted into the recovered partition later (if required).

Step 3: Delete Records From Partitioned Table

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

Step 4: Take Recovery Set Tablespaces Offline

At the primary database, take each recovery set tablespace offline by issuing the following statement:

ALTER TABLESPACE <tablespace name> OFFLINE IMMEDIATE;

Step 5: Create Tables at Clone Database

After recovering the clone and opening resetlogs, create a table that has the exact same column names and column datatypes as the partitioned table you are recovering--do this for each partition you wish 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, non-partitioned indexes that exist on the partition you wish to recover.

Step 7: Exchange Partitions with Stand-Alone Tables

For each partition in the clone recovery set, exchange the partitions into the stand-alone tables created in Step 5 by issuing the following statement:

If the export phase fails (with the error message "ORA 29308 view TS_PITR_CHECK failure"), re-query TS_PITR_CHECK, resolve the problem, and re-run the export. You need to perform the export phase of TSPITR as the user SYS, otherwise the export will fail (with the error message "ORA-29303: user does not login as SYS"). Shut down the clone database after a successful export.

Step 9: Copy the Recovery Set Datafiles to the Primary Database

If any recovery set tablespaces are READ ONLY on the primary database, you should change them to OFFLINE. Copy the recovery set datafiles from the clone database to the primary database, taking care not to overwrite any auxiliary set files on the primary database.

Step 10: Import into the Primary Database

Import the recovery set meta-data into the primary database using the following command:

imp sys/<password> point_in_time_recover=true

This import also updates the copied file's file headers and integrates them with the primary database.

Step 11: Bring Recovery Set Tablespace Online

Online each recovery set tablespace at the primary database by issuing the following statement:

ALTER TABLESPACE <tablespace name> ONLINE;

Step 12: Insert Stand-Alone Tables into Partitioned Tables

At this point you must insert the stand-alone tables into the partitioned tables; you can do this by first issuing the following statement:

Now insert the records saved in Step 2 into the recovered partition (if desired).

Note:

If the partition that has been dropped is the last partition in the table, it can be added using the following statement:

ALTER TABLE ADD PARTITION;

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

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

warning:

It is critical that you back up the tablespace. Failure to do so could result in the loss of that tablespace. For example, you could lose the tablespace in the event of media failure because the archived logs from the last backup of that database do not logically link to the recovered tablespaces. If you attempt to recover any recovery set tablespaces from a backup taken before TSPITR, you will fail (and receive error message "ORA 1246, recovering files through TSPITR of tablespace %s").

Note:

As described in "Limitations Advisory", TSPITR cannot be used to recover a tablespace that has been dropped. Therefore, if the associated tablespace of the partition has been dropped as well as the partition, you cannot recover that partition using TSPITR. You will have to perform ordinary export/import recovery. Specifically, you will have to:

Make a copy of the database

Roll it forward

Open the database

Exchange the partition for a stand-alone table

Make a table-level export of the stand-alone table

Import the table into the primary database and insert it into the partitioned table using the following statement:

ALTER TABLE SPLIT PARTITION or ALTER TABLE ADD
PARTITION;

Performing TSPITR of Partitioned Tables When a Partition Has Been 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

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 of the two partitions expands downwards (in other words, has the same range as before the split). For example, if P1 was split into two 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 by issuing the following statement:

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

Step 2: Drop Indexes of Partitions Being Recovered

Either drop the indexes of the partition you wish to recover or create identical, non-partitioned indexes on the table created in Step 1 as the indexes that exist on the partition you wish to recover. Dropping the indexes on the partition you wish to recover means that you will need also to drop them an the clone database (see Step 6), and will of course mean that they need to be rebuilt once the recovery is complete.

Step 3: Exchange Partitions with Stand-Alone Tables

Exchange each partition in the recovery set with its associated stand-alone table (created in Step 1) by issuing the following command:

Step 4: Take Recovery Set Tablespaces Offline

At the primary database, take each recovery set tablespace offline by issuing the following statement:

ALTER TABLESPACE <tablespace name> OFFLINE IMMEDIATE;

This prevents any further changes to the recovery set tablespaces at the primary database.

Step 5: Create Tables at Clone Database

At the clone database, after recovering the clone and opening resetlogs: For each partition you wish to recover, create a table that has exactly the same column names and column datatypes as the partitioned table you are recovering. These tables will be used to swap each recovery set partition (see Step 7).

Step 6: Drop Indexes in Partitions Being Recovered

Either drop the indexes on the partition you wish to recover, or create identical, non-partitioned indexes on the table created in Step 1 as the indexes that exist on the partition you wish to recover.

Step 7: Exchange Partitions with Stand-Alone Tables

For each partition in the clone recovery set, exchange the partitions into the stand-alone tables created in Step 5 by issuing the following statement:

If the export phase fails (with the error message "ORA 29308 view TS_PITR_CHECK failure"), re-query TS_PITR_CHECK, resolve the problem, and re-run the export. You need to perform the export phase of TSPITR as the user SYS, otherwise the export will fail (with the error message "ORA-29303: user does not login as SYS"). Shut down the clone database after a successful export.

Step 9: Copy the Recovery Set Datafiles to the Primary Database

If any recovery set tablespaces are READ ONLY on the primary database, you should change them to OFFLINE. Copy the recovery set datafiles from the clone database to the primary database, taking care not to overwrite any auxiliary set files on the primary database.

Step 10: Import into the Primary Database

Import the recovery set meta-data into the primary database using the following command:

imp sys/<password> point_in_time_recover=true

This import also updates the copied file's file headers and integrates them with the primary database.

Step 11: Bring Recovery Set Tablespace Online

Bring each recovery set tablespace at the primary database online by issuing the following statement:

ALTER TABLESPACE <tablespace name> ONLINE;

Step 12: Exchange Partitions with Stand-Alone Tables

For each recovered partition at the primary database, exchange its associated stand-alone table using the following statement:

it is critical that you back up the tablespace. Failure to do so could result in the loss of that tablespace. For example, you could lose the tablespace in the event of media failure because the archived logs from the last backup of that database do not logically link to the recovered tablespaces. If you attempt to recover any recovery set tablespaces from a backup taken before TSPITR, you will fail (and receive error message "ORA 1246, recovering files through TSPITR of tablespace %s").

TSPITR Tuning Considerations

This section describes tuning issues relevant to TSPITR, and includes the following topics:

Recovery Set Location Considerations

If space is at a premium, it is possible to recover the recovery set files 'in place', in other words, over their corresponding files on the primary database. This is not the recommended best practice--the recommended best practice is that you restore the files to a separate location and then copy across before the import phase of TSPITR is complete (see "Step 11: Import into the Primary Database").

Following are advantages and disadvantages of the two approaches.

Advantages and Disadvantages of Recovering to a Separate Location

An advantage of recovering to a separate location is basically greater availability and flexibility. If the recovery is abandoned at a point before integrating the recovery set with the primary database then there is no need to restore the recovery set files on the primary database and recover them using normal means. Also, the recovery set tablespaces can be accessible on the primary database while recovery occurs on the clone. For example, there may be a subset of undamaged data within the recovery set tablespaces that you wish to access (see "Step 3: Prepare the Primary Database for TSPITR"). If this is the case, you can change the recovery set tablespaces to READ ONLY on the primary database so that you can query them while preventing any further changes to them. If the files are recovered in place this is not possible.

A disadvantage of recovering to s separate location is that more space is required for the clone database.

Advantages and Disadvantages of Recovering in Place

An advantage of recovering in place is that the amount of space taken up by the recovery set files is saved. After recovery of the clone is complete, there is no need to copy the recovery set files over to the primary database.

If the recovery is abandoned at a point before integrating the recovery set with the primary database (see "Step 11: Import into the Primary Database") then the overwritten recovery set files of the primary database must be restored from a backup and recovered by normal means, prolonging data unavailability--this is a disadvantage. You cannot query any undamaged data within the recovery set tablespaces while recovery is going on.

Backup Control File Considerations

The error "ORA-01152 file 1 was not restored from a sufficiently old backup" will be encountered in the situation where no recovery is performed on the clone before grafting it to the primary. For example, if a backup is taken at time A, and a database at time B requires TSPITR to be done on a particular tablespace to take that tablespace to time A, what actually happens is that the clone database is opened resetlogs without any recovery having been done, i.e. when recovering the clone, the commands would be:

At this point no logs have been applied, but we wish to open the database. However, since we save checkpoints to the control file in Oracle 8, it is a requirement for clone and standby databases that the backup control files need to be taken at a point after the rest of the backup was taken. Unless this is the case, "ORA-01152 file 1 was not restored from a sufficiently old backup" will be encountered on open, not because file 1 is too recent (because it is in sync with the rest of the database), but because it is more recent than the control file.

Note:

A resetlogs would work with a regular database if a clean, consistent backup and an old backup control file is used, otherwise the behavior would not be compatible with existing backup scripts.