TSPITR Recovery Set Dependencies

Before performing TSPITR you must confirm whether the recovery set is fully contained. That means whether the objects present in the tablespaces that are included in the recovery set have any references to the objects that are present in the tablespaces that are not included in the recovery set.

To find out the objects that overlap the recovery set boundaries use the TS_PITR_CHECK view. For eg, if the recovery set contains two tablespaces (TS_RAIMS and TS_CMSCRM) then use the following query,

SQL> Select *from sys.ts_pitr_check
where ( ts1_name in ('TS_RAIMS', 'TS_CMSCRM')
and ts2_name not in ('TS_RAIMS', 'TS_CMSCRM') )
or ( ts1_name not in ('TS_RAIMS', 'TS_CMSCRM')
and ts2_name in ('TS_RAIMS', 'TS_CMSCRM') );

Proceed with TSPITR only when this query returns no rows.

Use the following query to check on all the tablespaces in the database,

SQL> 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') ;

Thanks

Objects Lost after TSPITR

When TSPITR is performed on a tablespace the objects created after the recovery time will be lost. To view the objects that will be lost use the following query,

SQL> 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 (‘SCOTT’)
And creation_time > TO_DATE(’01-JAN-09:07:05:15’,’YY-MON-DD:HH24:MI:SS’);


Thanks