Oracle8 Backup and Recovery Guide Release 8.0 A58396-01 |
Library | Product | Contents | Index

# 13
Performing Tablespace Point-in-Time Recovery

Attention: Due to the complex nature of tablespace point-in-time recovery, Oracle recommends that you contact and work with Worldwide Customer Support Services before using the procedures described here.

This chapter describes how to perform tablespace point-in-time recovery (TSPITR), and includes the following topics:

- Introduction to Tablespace Point-in-Time Recovery
- Planning for Tablespace Point-in-Time Recovery
- Performing Tablespace Point-In-Time Recovery
- 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 Been Split
- TSPITR Tuning Considerations

## 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:

1. Creating a copy of the database
2. Rolling the copied database forward to the desired point in time
3. Exporting the desired objects from the copied database
4. Dropping the relevant objects from the production database
5. 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';

See Also: For more details about the TS_PITR_CHECK view, see "Step 2: Research and Resolve Dependencies on the Primary Database".

#### 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.

See Also: For more information see "Performing Partial TSPITR of Partitioned 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.

See Also: For more information see "Step 2: Research and Resolve Dependencies on the Primary Database", and "TS_PITR_CHECK Does Not Check for Objects Owned by SYS".

### 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).
- You must have enough real memory to start up the clone instance.

See Also: For more information, see "Step 4: Prepare the Parameter Files for the Clone Database".

## Performing Tablespace Point-In-Time Recovery

This section describes how to perform TSPITR, and includes the following steps:

- Step 1: Find Out if Objects Will be Lost when Performing TSPITR
- Step 2: Research and Resolve Dependencies on the Primary Database
- Step 3: Prepare the Primary Database for TSPITR
- Step 4: Prepare the Parameter Files for the Clone Database
- Step 5: Prepare Clone Database for TSPITR
- Step 6: Recover the Clone Database
- Step 7: Open the Clone Database
- Step 8: Prepare the Clone Database for Export
- Step 9: Export the Clone Database
- Step 10: Copy the Recovery Set Clone Files to the Primary Database
- Step 11: Import into the Primary Database
- Step 12: Prepare the Primary Database for Use
- Step 13: Back Up the Recovered Tablespaces in the Primary Database

### 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" ).
- The following must be fully contained in the recovery set:
  - Tables (and their indexes, partitioned or non-partitioned)
  - Clusters (and their indexes, partitioned or non-partitioned)
  - Primary key and foreign key relationships
  - All elements of a LOB (lob segment, lob index and lob locator)
- The following object types must not exist in the recovery set:
  - replicated master tables
  - tables with varray columns
  - tables with nested tables
  - tables with external Bfiles
  - snapshot logs
  - snapshot tables

#### Querying the TS_PITR_CHECK View

Table 13-2 describes the contents of the TS_PITR_CHECK view.

##### Table 13-2 TS_PITR_CHECK View

Column | Datatype | NULL | Description
--- | --- | --- | ---
OBJ1_OWNER | VARCHAR2(30) | NOT NULL | 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:

    column OBJ1_OWNER heading "owner1"
    column OBJ1_OWNER format a6
    column OBJ1_NAME heading "name1"
    column OBJ1_NAME format a5
    column OBJ1_SUBNAME heading "subname1"
    column OBJ1_SUBNAME format a8
    column OBJ1_TYPE heading "obj1type"
    column OBJ1_TYPE format a8 word_wrapped
    column TS1_NAME heading "ts1_name"
    column TS1_NAME format a8
    column OBJ2_NAME heading "name2"
    column OBJ2_NAME format a5
    column OBJ2_SUBNAME heading "subname2"
    column OBJ2_SUBNAME format a8
    column OBJ2_TYPE heading "obj2type"
    column OBJ2_TYPE format a8 word_wrapped
    column OBJ2_OWNER heading "owner2"
    column OBJ2_OWNER format a6
    column TS2_NAME heading "ts2_name"
    column TS2_NAME format a8
    column CONSTRAINT_NAME heading "cname"
    column CONSTRAINT_NAME format a5
    column REASON heading "reason"
    column REASON format a57 word_wrapped

##### 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):

    owner1  name1  subname1   obj1type   ts1_name   name2  subname2  obj2type  owner2   ts2_name  cname
    reason
    -----  ----   -----     ------   -------   ----   ------  --------  -------  ------  -----
    SYSTEM  TP     P1        TABLE     TS1       TPIND   IP1     INDEX     PARTITION PARTITION  SYS ID1
    Partitioned Objects not fully contained in the recovery set

    SYSTEM   TP     P1       TABLE      TS1      TPIND IP2      INDEX    PARTITION PARTITION SYS ID2
    Partitioned Objects not fully contained in the recovery set

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:

1. 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:

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

   Note: It is possible, although not recommended, to place the recovery set files over their corresponding files on the primary database. For more information see "Performing Partial TSPITR of Partitioned Tables".

2. Set up your environment so that you can start up the clone database (for example, on UNIX, set ORACLE_SID to the name of the clone).
3. Startup nomount the clone database, specifying pfile if necessary (for example,'STARTUP NOMOUNT PFILE=/path/initCLONE.ora).
4. Mount the clone database using the following statement:

              ALTER DATABASE MOUNT CLONE 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 8: Prepare the Clone Database for Export

Prepare the clone database for export using the TS_PITR_CHECK view and resolving the dependencies just as you did for the primary database (see "Step 2: Research and Resolve Dependencies on the Primary Database"). Only when TS_PITR_CHECK returns no rows will the export phase of TSPITR complete.

### Step 9: Export the Clone Database

Export the meta-data for the recovery set tablespaces using the following statement:

    exp sys/<password> point_in_time_recover=y
    recovery_tablespaces=<tablespace1>,<tablespace2>,<tablespaceN>

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 12: Prepare the Primary Database for Use

First bring the recovery set tablespaces online in the primary database. Then change the recovery set tablespaces to READ WRITE (if they had been altered to READ ONLY, see "Step 3: Prepare the Primary Database for TSPITR").

To prepare the primary database for use, undo all the steps taken to resolve dependencies; for example, rebuild indexes or re-enable constraints (see "Step 2: Research and Resolve Dependencies on the Primary Database"). If statistics existed on the recovery set objects before TSPITR was performed, you will need to recalculate them. For partitioned tables, you have to exchange the stand-alone tables into the partitions of their partitioned tables (for more information, see "Performing Partial TSPITR of Partitioned Tables").

### 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 2: Drop the Indexes on the Partition Being Recovered
- Step 3: Exchange Partitions with Stand-Alone Tables
- Step 4:Take the Recovery Set Tablespace Offline
- Step 5: Create Tables at Clone Database
- Step 6: Drop Indexes on Partitions Being Recovered
- Step 7: Exchange Partitions with Stand-Alone Tables
- Step 8: Export the Clone Database
- Step 9: Copy the Recovery Set Datafiles to the Primary Database
- Step 10: Import into the Primary Database
- Step 11: Bring Recovery Set Tablespace Online
- Step 12: Exchange Partitions with Stand-Alone Tables
- Step 13: Back Up the Recovered Tablespaces in the Primary Database

  Note: Often, along with recovering a partition whose range has expanded, you will also have to recover the dropped partition. If this is the case, see "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

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:

    CREATE TABLE <new table> AS SELECT * FROM <partitioned table>  where 1=2;

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:

    ALTER TABLE <table name> EXCHANGE PARTITION <partition name> WITH TABLE <table name>;

### 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:

    ALTER TABLE <partitioned_table_name> EXCHANGE PARTITION <partition_name> WITH TABLE
    <table_name>;

### Step 8: Export the Clone Database

Execute export against the clone database for the recovery set tablespaces using the following statement:

    exp sys/<password> point_in_time_recover=y
    recovery_tablespaces=<tablespace1>,<tablespace2>,<tablespaceN>

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:

    ALTER TABLE <table name> EXCHANGE PARTITION <partition name> WITH TABLE <table name>;

If the associated indexes have been dropped, you must re-create them.

### 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
- Step 2: Create a Temporary Table
- Step 3: Delete Records From Partitioned Table
- Step 4: Take Recovery Set Tablespaces Offline
- Step 5: Create Tables at Clone Database
- Step 6: Drop Indexes on Partitions Being Recovered
- Step 7: Exchange Partitions with Stand-Alone Tables
- Step 8: Export the Clone Database
- Step 9: Copy the Recovery Set Datafiles to the Primary Database
- Step 10: Import into the Primary Database
- Step 11: Bring Recovery Set Tablespace Online
- Step 12: Insert Stand-Alone Tables into Partitioned Tables
- Step 13: 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 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:

     ALTER TABLE <partitioned_table_name> EXCHANGE PARTITION <partition_name> WITH TABLE
    <table_name>;

### Step 8: Export the Clone Database

Execute export against the clone database for the recovery set tablespaces using the following statement:

    exp sys/<password> point_in_time_recover=y
    recovery_tablespaces=<tablespace1>,<tablespace2>,<tablespaceN>

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:

    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 stand-alone table into the partition:

     ALTER TABLE EXCHANGE PARTITION <partition name> WITH TABLE <table name>;

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
- Step 2: Drop Indexes of Partitions Being Recovered
- Step 3: Exchange Partitions with Stand-Alone Tables
- Step 4: Take Recovery Set Tablespaces Offline
- Step 5: Create Tables at Clone Database
- Step 6: Drop Indexes in Partitions Being Recovered
- Step 7: Exchange Partitions with Stand-Alone Tables
- Step 8: Export the Clone Database
- Step 9: Copy the Recovery Set Datafiles to the Primary Database
- Step 10: Import into the Primary Database
- Step 11: Bring Recovery Set Tablespace Online
- Step 12: Exchange Partitions with Stand-Alone Tables
- Step 13: Back Up the Recovered Tablespaces in the Primary Database

### 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:

    CREATE TABLE <new table> AS SELECT * FROM <partitioned table> where 1=2;

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:

     ALTER TABLE <table name> EXCHANGE PARTITION <partition name> WITH TABLE <table name>;

### 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:

    ALTER TABLE <partitioned_table_name> EXCHANGE PARTITION <partition_name> WITH TABLE
    <table_name>;

### Step 8: Export the Clone Database

Execute export against the clone database for the recovery set tablespaces using the following statement:

    exp sys/<password> point_in_time_recover=y
    recovery_tablespaces=<tablespace1>,<tablespace2>,<tablespaceN>

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:

    ALTER TABLE <table name> EXCHANGE PARTITION <partition name> WITH TABLE <table name>;

If the associated indexes have been dropped, you must re-create them.

### 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.

See Also: For more information see "Performing TSPITR of Partitioned Tables When a Partition Has Been Dropped".

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

## TSPITR Tuning Considerations

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

- Recovery Set Location Considerations
- Backup Control File Considerations

### 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:

    SVRMGRL> recover database using backup controlfile until cancel;
    SVRMGRL> cancel;
    SVRMGRL> open database resetlogs;

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.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index