Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
|
|
View PDF |
There are several steps to be carried out in preparing 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.
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
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.
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 of the object to be dropped. |
|
The name of the object that will be lost as a result of undergoing TSPITR |
|
Creation timestamp for the object. |
|
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 theTS_PITR_OBJECTS_TO_BE_DROPPED
view