Oracle8i Backup and Recovery Guide
Release 8.1.5

A67773-01

Library

Product

Contents

Index

Prev Next

B
Performing Operating System Tablespace Point-in-Time Recovery


Note:

Due to the complex nature of tablespace point-in-time recovery, Oracle recommends that you contact Worldwide Customer Support Services before using these procedures.  


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

Introduction to O/S Tablespace Point-in-Time Recovery

Tablespace Point-in-Time Recovery (TSPITR) enables you to quickly recover one or more non-SYSTEM tablespaces to a time that is different from that of the rest of the database. Like 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.

TSPITR is most useful for recovering:

This section contains the following topics:

TSPITR Advantages

Prior to Oracle8, point-in-time recovery could only be performed 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.

There was a performance overhead associated with exporting and importing large objects, however, which created a need for a new method. TSPITR enables you to do the following:

  1. Make a temporary copy of the database, called a clone database.

  2. Recover a subset of a clone database.

  3. Copy the relevant datafiles from the recovered database to the production database using an O/S utility.

  4. Export data dictionary metadata about the datafile's content (for example, the recovered segments within the file) from the clone database to the production database. The copied file is also added to the production database via a special import option.

TSPITR Methods

You can perform O/S TSPITR in two different ways:

Method   Consequence  

Traditional O/S TSPITR  

You must follow special procedures for creating clone init.ora files, mounting the clone database, etc. The procedures provide error checks to prevent the corruption of the primary database on the same computer while recovering the clone database.  

TSPITR using the transportable tablespace feature  

This method differs from standard O/S TSPITR mainly in using transported tablespaces to perform the last step of TSPITR. You must set the COMPATIBLE initialization parameter to 8.1 or higher to use this method.  

The one major difference between the two methods is that performing TSPITR via transportable tablespaces relaxes some of O/S TSPITR's special procedures. If you restore backups to a different host separate from the primary database, you can start the clone database as if it were the primary database using the normal database MOUNT command instead of the clone database MOUNT command.

See Also: For more information about the transportable tablespace feature, see the Oracle8i Administrator's Guide.

TSPITR Terminology

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 TSPITR. It 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:

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 provide sort space either by creating a new temporary tablespace after the clone has been started or by setting AUTOEXTEND to ON on the SYSTEM tablespace files.

Transportable Tablespace

A feature that enables you to take a tablespace from one database and plug it in to another database. For more information, see "Recovering Transported Tablespaces". For a detailed account, see the Oracle8i Administrator's Guide.

Planning for Tablespace Point-in-Time Recovery

TSPITR is a complicated procedure and requires careful planning. Before proceeding you should read 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.  


TSPITR Limitations

The primary issue you should consider is the possibility of application-level inconsistencies between tables in recovered and unrecovered tablespaces due to implicit rather than explicit referential dependencies. Understand these dependencies and find the means to resolve any possible inconsistencies before proceeding.

This section deals with the following topics:

General Restrictions

TSPITR has several restrictions. You cannot use TSPITR to do the following:

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 you manage these relationships, 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".

TSPITR Requirements

Satisfy the following requirements before performing TSPITR.

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

Preparing the Databases for TSPITR

This section describes how to prepare the clone database for TSPITR, and includes the following steps:

Step 1: Determine Whether Objects Will Be Lost

When TSPITR is performed on a tablespace, any objects created after the recovery time 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 16-3:

Table 16-3 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 this view, supply all the elements of the date field, otherwise the default setting will be used. 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', issue the following:

SELECT owner, name, tablespace_name, to_char(creation_time, 'YYYY-MM-DD:HH24:MI:SS'), 
FROM ts_pitr_objects_to_be_dropped 
WHERE tablespace_name IN ('TS1','TS2') 
AND creation_time > to_date('97-JUN-02:07:03:11','YY-MON-  DD:HH24:MI:SS')
ORDER BY tablespace_name, creation_time;

See Also: For more information about the TS_PITR_OBJECTS_TO_BE_DROPPED view, see the Oracle8i Reference.

Step 2: Research and Resolve Dependencies on the Primary Database

Use the TS_PITR_CHECK view to identify relationships between objects that overlap the recovery set boundaries. If this view returns rows when queried, investigate and correct the problem. Proceed with TSPITR only when TS_PITR_CHECK view returns no rows. 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:

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:

SELECT * FROM sys.ts_pitr_check 
WHERE (ts1_name IN ('TS1','TS2') AND ts2_name NOT IN ('TS1','TS2')) 
OR (ts1_name NOT IN ('TS1','TS2') AND ts2_name IN ('TS1','TS2'));

Because of 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 "own1"
column OBJ1_OWNER format a4
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 "own2"
column OBJ2_OWNER format a4
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

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

own1   name1 subname1 obj1type ts1_name name2 subname2 obj2type own2 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 

The table SYSTEM.TP has a partitioned index TPIND that consists of two partitions, IP1 in tablespace ID1 and IP2 in tablespace ID2. Either drop TPIND or include ID1 and ID2 in the recovery set.

See Also: For more information about the TS_PITR_CHECK view, see the Oracle8i Reference.

Step 3: Prepare the Primary Database

Perform the following tasks:

  1. Archive the current online redo log:

    ALTER SYSTEM ARCHIVE LOG CURRENT;
        
    
    
    
  2. Take offline any rollback segments in the recovery set (you do not have to take auxiliary set rollback segments offline):

    ALTER ROLLBACK SEGMENT segment_name OFFLINE; 
        
    
    
    
  3. Take the recovery set tablespaces on the primary database offline normal. Use the OFFLINE FOR RECOVER option if you cannot write to the file due to I/O errors or if the file is unavailable. You can use OFFLINE FOR RECOVER for performance reasons, e.g., if you have a large number of datafiles and you do not care about updating the file headers since they are being recovered back to a point in time anyway:

    ALTER TABLESPACE tablespace_name OFFLINE FOR RECOVER; 
        
    
    
    

    This statement 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 that you want to query within the recovery set tablespaces, alter the recovery set tablespaces on the primary database as READ ONLY for the duration of the recovery of the clone. Take the recovery set tablespaces offline before integrating the clone files with the primary database (see "Step 5: Copy the Recovery Set Clone Files to the Primary Database").  


See Also: For more information about the ALTER SYSTEM and ALTER ROLLBACK SEGMENT statements, see the Oracle8i SQL Reference.

Step 4: Prepare the Clone Parameter Files

Create the parameter file from a new init.ora file rather than using the production database init.ora file. Save memory by using low settings for parameters such as DB_BLOCK_BUFFERS, SHARED_POOL_SIZE, or LARGE_POOL_SIZE. If the production parameter files are used for the clone database, however, reducing these parameters can prevent the clone database from starting when other parameters are set too high--for example, the parameter ENQUEUE_RESOURCES, which allocates memory from within the shared pool.

Set the following parameters in your clone init.ora file:

Parameter   Purpose  

CONTROL_FILES  

Identifies clone control files. Set to the name and location of the clone control files.  

LOCK_NAME_SPACE  

Allows the clone database to start even though it has the same name as the primary database. Set to a unique value, e.g., = CLONE.

Note: Do not change the DB_NAME parameter.  

DB_FILE_NAME_CONVERT  

Converts datafile filenames. Set to new values if necessary.  

LOG_FILE_NAME_CONVERT  

Renames redo logs files. For example, if the datafiles of the primary database reside in the directory /ora/primary, and the clone will reside in /ora/clone, set DB_FILE_NAME_CONVERT to "primary","clone".

Note: You can also rename the redo logs with the ALTER DATABASE RENAME FILE statement. See "Step 5: Prepare the Clone Database".  

Step 5: Prepare the Clone Database

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. Configure 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. Start the clone database without mounting it, specifying the parameter file if necessary:

    STARTUP NOMOUNT PFILE=/path/initCLONE.ora;
        
    
    
    
  4. Mount the clone database:

    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.

  5. If you did not set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT, 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 you did set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT but there are files that have been restored to different locations, then rename them.

  6. Even if you have set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT, do not assume that all the files of the clone database will be in the specified locations--some clone files may have been restored to different locations due to constraints of disk space. 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.  


Performing TSPITR

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

Step 1: Recover the Clone Database

Recover the clone database to the desired point by specifying the USING BACKUP CONTROLFILE option. Use any form of incomplete 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, Oracle issues an error message.

Step 2: Open the Clone Database

Open the clone database with the RESETLOGS option 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, which prevents you from executing DML statements against any user tablespace. Any attempt to bring a user rollback segment online will fail and generate an error message.

Step 3: 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 4: Export the Metadata

Export the metadata for the recovery set tablespaces using the following statement:

exp sys/password point_in_time_recover=y 
recovery_tablespaces=tablespace_1,tablespace_2,tablespace_n 

If the export phase fails and generates an error message, re-query TS_PITR_CHECK, resolve the problem, and re-run the export. Perform the export phase of TSPITR as the user SYS, otherwise the export will fail.

Shut down the clone database after a successful export:

SHUTDOWN IMMEDIATE;

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

If any recovery set tablespaces are read-only on the primary database, you should take them offline. Use an O/S utility to 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 6: Import the Metadata into the Primary Database

Import the recovery set metadata 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.


Note:

Object name conflicts may arise if objects of the same name exist already in primary database. Resolve these conflicts explicitly.  


See Also: For more information about Export, see Oracle8i Tuning.

Step 7: Prepare the Primary Database for Use

To prepare the primary database for use, follow these steps:

  1. Bring the recovery set tablespaces online in the primary database.

  2. Change the recovery set tablespaces to read-write (if they had been altered to read-only, see "Step 3: Prepare the Primary Database").

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

  4. 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 8: Back Up the Recovered Tablespaces in the Primary Database

After TSPITR on a tablespace is complete, use an O/S utility to back up the tablespace.


WARNING: You must back up the tablespace, because otherwise you might lose it. For example, a media failure occurs, but the archived redo 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, you will fail.:  

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

This table should have the exact same column names and column datatypes as the partitioned table you are recovering. Create the table as follows:

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: Exchange Partitions with Stand-Alone 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 you will also need to drop them on the clone database (see "Step 6: Drop Indexes on Partitions Being Recovered"). 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:

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 it with the RESETLOGS option, create a table that has the same column names and column data types as the partitioned table you are recovering. Create a table 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=tablespace_1,tablespace_2,tablespace_n

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. 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, change them to OFFLINE. Copy the recovery set datafiles from the clone database to the primary database, taking care not to overwrite auxiliary set files on the primary database.

Step 10: Import into the Primary Database

Import the recovery set metadata 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:

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 using the following statement:

ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE table_name;
 

If the associated indexes have been dropped, 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:

You must back up the tablespace, because otherwise you might lose it. For example, a media failure occurs, but the archived redo 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, you will fail.  


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 if necessary they can be inserted into the recovered partition later.

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:

ALTER TABLESPACE tablespace_name OFFLINE IMMEDIATE; 

Step 5: Create Tables at Clone Database

After opening the clone with the RESETLOGS option, create a table that has the exact same column names and column datatypes as the partitioned table you are recovering. Create a table 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=tablespace_1,tablespace_2,tablespace_n

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, add it using the ALTER TABLE ADD PARTITION statement.  


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:

You must back up the tablespace, because otherwise you might lose it. For example, a media failure occurs, but the archived redo 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, you will fail.  



Note:

As described in "TSPITR Limitations", 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 ALTER TABLE SPLIT PARTITION or ALTER TABLE ADD PARTITION statements.  


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

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:

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-13: Follow Same Steps as for Partial TSPITR of Partitioned Tablespaces

Follow steps 2-13 in the procedure for "Performing Partial TSPITR of Partitioned Tables".

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, recover them over their corresponding files on the primary database. Note that the recommended practice is to restore the files to a separate location and then copy across before the import phase of TSPITR is complete (see "Step 6: Import the Metadata into the Primary Database").

Advantages and Disadvantages of Recovering to a Separate Location

The advantages of recovering to a separate location are:

The disadvantage of recovering to a 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.

The disadvantage is that if the recovery is abandoned at a point before integrating the recovery set with the primary database (see "Step 6: Import the Metadata into the Primary Database" ), then you must restore the overwritten recovery set files of the primary database from a backup and recover by normal means, prolonging data unavailability. You cannot query any undamaged data within the recovery set tablespaces during recovery.

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. When recovering the clone, the SQL*Plus commands would be:

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; 
CANCEL; 
OPEN DATABASE RESETLOGS;
 

At this point no redo 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.  


Performing TSPITR Using Transportable Tablespaces

You can use the transportable tablespace feature to perform tablespace point-in-time recovery. This method is similar to the O/S TSPITR described in previous sections, except you use the transportable tablespace feature to move recovered tablespaces from the clone database to the primary database. To learn how to transport tablespaces between databases, see the Oracle8i Administrator's Guide.

The major difference between O/S TSPITR and TSPITR via transportable tablespaces is that for the former you must follow the special procedures for creating clone init.ora files, mounting the clone database, etc. O/S TSPITR assumes that the user may place the clone database on the same computer as the primary database; the special clone database commands provide error checks to prevent the corruption of the primary database on the same computer while recovering the clone database.

Performing TSPITR via transportable tablespaces relaxes this requirement. If you restore backups to a different computer separate from the primary database, you can start the clone database as if it were the primary database, using the normal database MOUNT command instead of the clone database MOUNT command. If you restore backups on the same computer as the primary database, however, follow the special procedure to create the clone database as described in O/S TSPITR, since this procedure helps prevent accidental corruption of the primary database while recovering the clone database on the same computer.

TSPITR via transportable tablespaces provides basically the same functionality as O/S TSPITR, but is more flexible since:

To perform TSPITR using the transportable tablespace feature:

  1. Restore the backup to construct the clone database. Create the clone database on the same computer as the primary database or on a different computer.

  2. If you create the clone database using the special clone database procedure, place all recovery set and auxiliary set files online:

    ALTER DATABASE DATAFILE 'datafile_name' ONLINE;
        
    
    
    

    If you create the clone database as a normal database (on a computer different from the primary database), take all datafiles not in the recovery and auxiliary set offline:

    ALTER DATABASE DATAFILE 'datafile_name' OFFLINE;
        
    
    
    
  3. Recover the clone database to the specified point in time.

  4. Open the clone database with the RESETLOGS option.

  5. Make the tablespaces in the recovery set read-only by issuing the ALTER TABLESPACE READ ONLY command.

  6. Generate the transportable set by running EXPORT. Include all tablespaces in the recovery set.

  7. In the primary database, drop the tablespaces in the recovery set via the DROP TABLESPACE command.

  8. Plug in the transportable set into the primary database by running IMPORT.

  9. If necessary, make the recovered tablespaces read write by issuing the ALTER TABLESPACE READ WRITE command.




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index