Skip Headers
Oracle® Database Backup and Recovery Advanced User's Guide
10g Release 2 (10.2)

Part Number B14191-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Planning and Preparing for TSPITR

There are several steps to be carried out in preparing for TSPITR:

Choosing the Right Target Time for TSPITR

It is extremely important that you choose the right target time or SCN for your TSPITR. As noted already, once you bring a tablespace online after TSPITR, you cannot use any backup from a time earlier than the moment you brought the tablespace online. In practice, this means that you cannot make a second attempt at TSPITR if you choose the wrong target time the first time, unless you are using a recovery catalog. (If you have a recovery catalog, however, you can perform repeated TSPITRs to different target times.)

For example, assume that you are not using a recovery catalog, and you run TSPITR on a tablespace, and then bring the tablespace online at 5PM on Friday. Backups of the tablespace created before 5PM Friday are no longer usable for recovery with a current control file. You cannot run TSPITR again on this tablespace with a target time earlier than 5PM Friday, nor can you use the current control file to recover the database to any time earlier than 5PM Friday. Your only option will be point-in-time recovery of your entire database using a restored control file.

To investigate past states of your data to identify the target time for TSPITR, you can use features of Oracle such as Oracle Flashback Query, Oracle Transaction Query and Oracle Flashback Version Query to find the point in time when unwanted database changes occurred. See Oracle Database Backup and Recovery Basics for more details on Flashback Query, and Oracle Database Application Developer's Guide - Fundamentals for more information on Flashback Transaction Query and Flashback Version Query.

Determining the Recovery Set: Analyzing Data Relationships

Your recovery set starts out including the datafiles for the tablespaces you wish to recover. If, however, objects in the tablespaces you need have relationships (such as constraints) to objects in other tablespaces, you will have to account for this relationship before you can perform TSPITR. You have three choices when faced with such a relationship:

  • Add the tablespace including the related objects to your recovery set

  • Remove the relationship

  • Suspend the relationship for the duration of TSPITR

Identifying and Resolving Dependencies on the Primary Database

The TS_PITR_CHECK view lets you identify relationships between objects that span the recovery set boundaries. If this view returns rows when queried, then investigate and correct the problem. Proceed with TSPITR only when TS_PITR_CHECK view returns no rows for the tablespaces not in the recovery set. Record all actions performed during this step so that you can re-create any suspended or removed relationships after completing TSPITR.

The following query illustrates how to use the TS_PITR_CHECK view. For an example with an initial recovery set consisting of tools and users, the SELECT statement against TS_PITR_CHECK would be as follows:

SELECT * 
FROM SYS.TS_PITR_CHECK 
WHERE (
        TS1_NAME IN ('USERS','TOOLS') 
        AND TS2_NAME NOT IN ('USERS','TOOLS')
      )
OR    (
        TS1_NAME NOT IN ('USERS','TOOLS') 
        AND TS2_NAME IN ('USERS','TOOLS')
      );

To run a complete TSPITR check on all the tablespaces in the database (not just the tablespaces in the recovery set), you can run the following query:

SELECT *
FROM SYS.TS_PITR_CHECK
WHERE (
         'SYSTEM' IN (TS1_NAME, TS2_NAME)
         AND TS1_NAME <> TS2_NAME
         AND TS2_NAME <> '-1'
      )
OR    (
         TS1_NAME <> 'SYSTEM' 
         AND TS2_NAME = '-1'
      );

Because of the number and width of the columns in the TS_PITR_CHECK view, you may want to format the columns as follows when running the query:

SET LINESIZE 120
COLUMN OBJ1_OWNER HEADING "own1"
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 a6
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 a6
COLUMN TS2_NAME HEADING "ts2_name"
COLUMN TS2_NAME FORMAT a6
COLUMN CONSTRAINT_NAME HEADING "cname"
COLUMN CONSTRAINT_NAME FORMAT a5
COLUMN REASON HEADING "reason"
COLUMN REASON FORMAT a25 word_wrapped

Assume a case in which the partitioned table tp has two partitions, p1 and p2, that exist in tablespaces users and tools respectively. Also assume that a partitioned index called tpind is defined on tp, and that the index has two partitions id1 and id2 (that exist in tablespaces id1 and id2 respectively). In this case, you would get the following output when TS_PITR_CHECK is queried against tablespaces users and tools (assuming appropriate formatting):

own1   name1 subname1 obj1type ts1_name name2 subname2 obj2type own2      ts2_name   cname reason
---    ----  -----    ------   -------  ----  ------   -------- ---       --------   ---   ------ 
SYSTEM  TP   P1       TABLE    USER     TPIND IP1      INDEX    PARTITION PARTITION  SYS   ID1 Partitioned Objects not fully contained in the recovery set
SYSTEM  TP   P2       TABLE    TOOLS    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. To perform TSPITR, you must either drop tpind or include id1 and id2 in the recovery set.

See Also:

Oracle Database Reference for more information about the TS_PITR_CHECK view

Identifying and Preserving Objects That Will Be Lost After TSPITR

When RMAN TSPITR is performed on a tablespace, any objects created after the target recovery time are lost. You can preserve such objects, once they are identified, by exporting them before TSPITR using an Oracle export utility (Data Pump Export or Original Export) and re-importing them afterwards using the corresponding import utility.

To see which objects will be lost in TSPITR, query the TS_PITR_OBJECTS_TO_BE_DROPPED view on the primary database. The contents of the view are described in Table 8-1.

Table 8-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 undergoing TSPITR

CREATION_TIME

Creation timestamp for the object.

TABLESPACE_NAME

Name of the tablespace containing the object.


Filter the view for objects whose CREATION_TIME is after the target time for TSPITR. For example, with a recovery set consisting of users and tools, and a recovery point in time of November 2, 2002, 7:03:11 AM, issue the following statement:

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 ('USERS','TOOLS') 
AND CREATION_TIME > TO_DATE('02-NOV-02:07:03:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;

(The TO_CHAR and TO_DATE functions are used to avoid issues with different national date formats. You can, of course, use local date formats in your own work.)

See Also:

Oracle Database Reference for more information about the TS_PITR_OBJECTS_TO_BE_DROPPED view