Skip Headers

Oracle9i User-Managed Backup and Recovery Guide
Release 2 (9.2)

Part Number A96572-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

7
Performing User-Managed TSPITR

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

This chapter includes the following topics:

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

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

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

TSPITR Terminology

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

TSPITR

Tablespace point-in-time recovery

Primary Database

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

Auxiliary Database

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

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

Recovery Set

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

Recovery Set Self-Containment Check

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

Auxiliary Set

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

Transportable Tablespace

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

TSPITR Methods

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

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

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

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

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

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

    See Also:

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

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

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

This section contains the following topics:

Step 1: Review TSPITR Requirements

Satisfy the following requirements before performing TSPITR:

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

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

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

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

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

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

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

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

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

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

Step 3: Determine Whether Objects Will Be Lost

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

Table 7-1 TS_PITR_OBJECTS_TO_BE_DROPPED View  
Column Name Meaning

OWNER

Owner of the object to be dropped.

NAME

The name of the object that will be lost as a result of TSPITR

CREATION_TIME

Creation time stamp for the object.

TABLESPACE_NAME

Name of the tablespace containing the object.

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

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

Oracle9i Database Reference for more information about the TS_PITR_OBJECTS_TO_BE_DROPPED view

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

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

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

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

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

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

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

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

Caution:

If the auxiliary and primary database are on the same machine, then failing to rename the online redo log files may cause primary database corruption.

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

Table 7-2 Auxiliary Initialization Parameters  
Parameter Purpose

DB_NAME

Names the auxiliary database. Leave the name of the auxiliary database the same as the primary database.

CONTROL_FILES

Identifies auxiliary control files. Set to the filename of the auxiliary control file. Make sure the control file name is different from the primary database control file name.

LOCK_NAME_SPACE

Allows the auxiliary database to start even though it has the same name as the primary database. Set to any unique value, for example, = AUX. This parameter is only needed if the auxiliary and primary database are on the same host.

DB_FILE_NAME_CONVERT

Uses patterns to convert filenames for the datafiles of the auxiliary database. This parameter is only necessary if you are either restoring the auxiliary database on the same host as the primary host, or on a different host that uses different path names from the primary host.

LOG_FILE_NAME_CONVERT

Uses patterns to convert filenames for the online redo logs of the auxiliary database. This parameter is mandatory.

LOG_ARCHIVE_DEST_1

Specifies the default directory containing the archived redo logs required for recovery.This parameter specifies the location on the auxiliary host in which the archived logs will be located.

LOG_ARCHIVE_FORMAT

Specifies the format of the archived logs. You should use the same format setting used in the primary initialization parameter file.

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

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

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

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

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

Restoring and Recovering the Auxiliary Database: Basic Steps

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

The different cases are described in the following sections:

Restoring and Recovering the Auxiliary Database on the Same Host

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

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

  1. Restore the auxiliary set and the recovery set to a location different from that of the primary database. For example, assume that the auxiliary set consists of the following files:
    /oracle/dbs/cf1.f      # control file
    /oracle/dbs/undo.f     # datafile in undo tablespace
    /oracle/dbs/system.f   # datafile in SYSTEM tablespace
    
    

    And the recovery set consists of the following datafiles:

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

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

    cp /backup/cf1.f /aux/cf1.f
    cp /backup/undo.f /aux/undo.f
    cp /backup/system.f /aux/system.f
    cp /backup/sales_1.f /aux/sales_1.f
    cp /backup/sales_2.f /aux/sales_2.f
    
    
  2. Start the auxiliary database without mounting it, specifying the initialization parameter file if necessary. For example, enter:
    STARTUP NOMOUNT PFILE=/aux/initAUX.ora
    
    
  3. Mount the auxiliary database, specifying the CLONE keyword:
    ALTER DATABASE MOUNT CLONE DATABASE;
    
    

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

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

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

  6. Bring only the datafiles in the auxiliary and recovery set tablespaces online. For example, bring the four datafiles in the recovery and auxiliary sets online:
    ALTER DATABASE DATAFILE /oracle/dbs/aux/system.f ONLINE;
    ALTER DATABASE DATAFILE /oracle/dbs/aux/sales_1.f ONLINE;
    ALTER DATABASE DATAFILE /oracle/dbs/aux/sales_2.f ONLINE;
    ALTER DATABASE DATAFILE /oracle/dbs/aux/undo.f ONLINE;
    

    Note:

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


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

  7. Recover the auxiliary database to the specified point in time with the USING BACKUP CONTROLFILE option. Use any form of incomplete recovery as described in "Performing Incomplete User-Managed Media Recovery". The following example uses cancel-based incomplete recovery:
    RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
    
    
  8. Open the auxiliary database with the RESETLOGS option using the following statement:
    ALTER DATABASE OPEN RESETLOGS;
    

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

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

To restore and recover the auxiliary database:

  1. Restore the auxiliary set and the recovery set to the auxiliary host. For example, assume that the auxiliary set consists of the following files:
    /oracle/dbs/cf1.f      # control file
    /oracle/dbs/undo.f     # datafile in undo tablespace
    /oracle/dbs/system.f   # datafile in SYSTEM tablespace
    
    

    And the recovery set consists of the following datafiles:

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

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

  2. Start the auxiliary database without mounting it, specifying the initialization parameter file if necessary. For example, enter:
    STARTUP NOMOUNT PFILE=/aux/initAUX.ora
    
    
  3. Mount the auxiliary database, specifying the CLONE keyword:
    ALTER DATABASE MOUNT CLONE DATABASE;
    
    

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

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

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

  6. Bring all datafiles in the auxiliary and recovery set tablespaces online. For example, bring the four datafiles in the recovery and auxiliary sets online:
    ALTER DATABASE DATAFILE /oracle/dbs/system.f ONLINE;
    ALTER DATABASE DATAFILE /oracle/dbs/sales_1.f ONLINE;
    ALTER DATABASE DATAFILE /oracle/dbs/sales_2.f ONLINE;
    ALTER DATABASE DATAFILE /oracle/dbs/undo.f ONLINE;
    

    Note:

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


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

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

    ALTER DATABASE OPEN RESETLOGS;

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

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

Performing TSPITR with Transportable Tablespaces

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

Step 1: Unplugging the Tablespaces from the Auxiliary Database

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

To unplug the auxiliary database tablespaces:

  1. Make the tablespaces in the recovery set read-only by running the ALTER TABLESPACE ... READ ONLY statement. For example, make sales_1 and sales_2 read-only as follows:
    ALTER TABLESPACE sales_1 READ ONLY;
    ALTER TABLESPACE sales_2 READ ONLY;
    
    
  2. Ensure that the recovery set is self-contained. For example:
    EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2',TRUE,TRUE);
    
    
  3. Query the transportable tablespace violations table to manage any dependencies. For example:
    SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
    
    

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

  4. Generate the transportable set by running the Export utility as described in Oracle9i Database Administrator's Guide. Include all tablespaces in the recovery set, as in the following example:
    % exp SYS/pwd TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2) TTS_FULL_CHECK=y
    
    

    This command generates an export file named expdat.dmp.

Step 2: Transporting the Tablespaces into the Primary Database

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

To plug the recovery set tablespaces into the primary database:

  1. In the primary database (not the auxiliary database), drop the tablespaces in the recovery set through the DROP TABLESPACE statement. For example:
    DROP TABLESPACE sales_1 INCLUDING CONTENTS;
    DROP TABLESPACE sales_2 INCLUDING CONTENTS;
    
    
  2. Restore the recovery set datafiles from the auxiliary database to the recovery set file locations in the primary database. For example:
    % cp /net/aux_host/aux/sales_1.f /net/primary_host/oracle/dbs/sales_1.f
    % cp /net/aux_host/aux/sales_2.f /net/primary_host/oracle/dbs/sales_2.f
    
    
  3. Move the export file expdat.dmp to the primary host. For example, enter:
    % cp /net/aux_host/aux/expdat.dmp /net/primary_host/oracle/dbs/expdat.dmp
    
    
  4. Plug in the transportable set into the primary database by running Import as described in Oracle9i Database Administrator's Guide. For example:
    % imp TRANSPORT_TABLESPACE=y FILE=expat.dmp
       DATAFILES=('/oracle/dbs/sales_1.f','/oracle/dbs/sales_2.f')
    
    
  5. Make the recovered tablespaces read write by issuing the ALTER TABLESPACE READ WRITE statement. For example:
    ALTER TABLESPACE sales_1 READ WRITE;
    ALTER TABLESPACE sales_2 READ WRITE;
    
    
  6. Back up the recovered tablespaces with an operating system utility as described in "Making User-Managed Backups of Online Tablespaces and Datafiles".


    Caution:

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


Performing Partial TSPITR of Partitioned Tables

Partitioned tables can span multiple tablespaces. Follow this procedure only if the recovery set does not fully contain all of the partitions.

This section describes how to perform partial TSPITR of partitioned tables that have a range that has not changed or expanded, and includes the following steps:

Step 1: Create a Table on the Primary Database for Each Partition Being Recovered

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

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

These tables are used to swap each recovery set partition (see "Step 3: Exchange Partitions with Standalone Tables").


Note:

The table and the partition must belong to the same schema.


Step 2: Drop the Indexes on the Partition Being Recovered

Drop the indexes on the partition you wish to recover, or create identical, non-partitioned indexes that exist on the partition you wish to recover. If you drop the indexes on the partition being recovered, then you need to drop them on the auxiliary database (see "Step 6: Drop Indexes on Partitions Being Recovered"). Rebuild the indexes after TSPITR is complete.

Step 3: Exchange Partitions with Standalone Tables

Exchange each partition in the recovery set with its associated standalone table (created in Step 1) by issuing the following statement, replacing the variables with the names of the appropriate objects:

ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE table_name; 

Step 4: Drop the Recovery Set Tablespace

On the primary database, drop each tablespace in the recovery set. For example, enter the following, replacing tablespace_name with the name of the tablespace:

DROP TABLESPACE tablespace_name INCLUDING CONTENTS;

Step 5: Create Tables at Auxiliary Database

After recovering the auxiliary database and opening it with the RESETLOGS option, create a table in the SYSTEM tablespace that has the same column names and column data types as the partitioned table you are recovering. You must create the table in the SYSTEM tablespace: otherwise, Oracle issues the ORA-01552 error.

Create a table for each partition you wish to recover. These tables are used later to swap each recovery set partition.


Note:

The table and the partition must belong to the same schema.


Step 6: Drop Indexes on Partitions Being Recovered

Drop the indexes on the partition you wish to recover, or create identical, non-partitioned indexes that exist on the partition you wish to recover (on the table created in Step 1).

Step 7: Exchange Partitions with Standalone Tables on the Auxiliary Database

For each partition in the auxiliary database recovery set, exchange the partitions with the standalone tables (created in Step 5) by executing the following SQL script, replacing the variables with the appropriate object names:

ALTER TABLE partitioned_table_name 
EXCHANGE PARTITION partition_name 
WITH TABLE table_name; 

Step 8: Transport the Recovery Set Tablespaces

Export the recovery set tablespaces from the auxiliary database and then import them into the primary database as described in "Performing TSPITR with Transportable Tablespaces".

Step 9: Exchange Partitions with Standalone Tables on the Primary Database

For each recovered partition on the primary database, swap its associated standalone table using the following statement, replacing the variables with the appropriate object names:

ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE table_name;
 

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

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

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

Performing TSPITR of Partitioned Tables When a Partition Has Been Dropped

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

Step 1: Find the Low and High Range of the Partition that Was Dropped

When a partition is dropped, the range of the partition preceding it expands downwards. Therefore, there may be records in the preceding partition that should actually be in the dropped partition after it has been recovered. To ascertain this, run the following SQL script at the primary database, replacing the variables with the appropriate values:

SELECT * FROM partitioned_table 
  WHERE relevant_key 
  BETWEEN low_range_of_partition_that_was_dropped 
  AND high_range_of_partition_that_was_dropped;

Step 2: Create a Temporary Table

If any records are returned, then create a temporary table in which to store these records so that if necessary they can be inserted into the recovered partition later.

Step 3: Delete Records From the Partitioned Table

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

Step 4: Drop the Recovery Set Tablespace

On the primary database, drop each tablespace in the recovery set. For example, enter the following, replacing tablespace_name with the name of the tablespace:

DROP TABLESPACE tablespace_name INCLUDING CONTENTS;

Step 5: Create Tables at the Auxiliary Database

After opening the auxiliary database with the RESETLOGS option, create a table in the SYSTEM tablespace that has the same column names and column data types as the partitioned table you are recovering. You must create the table in the SYSTEM tablespace: otherwise, Oracle issues the ORA-01552 error. Create a table for each partition that you want to recover. These tables will be used later to swap each recovery set partition.

Step 6: Drop Indexes on Partitions Being Recovered

Drop the indexes on the partition you wish to recover, or create identical, nonpartitioned indexes that exist on the partition you wish to recover.

Step 7: Exchange Partitions with Standalone Tables

For each partition in the auxiliary recovery set, exchange the partitions into the standalone tables created in Step 5 by issuing the following statement, replacing the variables with the appropriate values:

ALTER TABLE partitioned_table_name 
EXCHANGE PARTITION partition_name 
WITH TABLE table_name;

Step 8: Transport the Recovery Set Tablespaces

Export the recovery set tablespaces from the auxiliary database and then import them into the primary database as described in "Performing TSPITR with Transportable Tablespaces".

Step 9: Insert Standalone Tables into Partitioned Tables

At this point you must insert the standalone tables into the partitioned tables; you can do this by first issuing the following statement, replacing the variables with the appropriate values:

ALTER TABLE table_name SPLIT PARTITION partition_name AT (key_value) INTO 
(PARTITION partition_1_name TABLESPACE tablespace_name, 
PARTITION partition_2_name TABLESPACE tablespace_name);
 

Note that at this point, partition 2 is empty because keys in that range have already been deleted from the table.

Issue the following statement to swap the standalone table into the partition, replacing the variables with the appropriate values:

ALTER TABLE EXCHANGE PARTITION partition_name WITH TABLE table_name; 

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


Note:

If the partition that has been dropped is the last partition in the table, then add it with the ALTER TABLE ADD PARTITION statement.


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

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

Performing TSPITR of Partitioned Tables When a Partition Has Split

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

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

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

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

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

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

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

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


Go to previous page Go to next page
Oracle
Copyright © 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback