Planning and Preparing for 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.