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

ORA-25153: Temporary Tablespace is Empty

When you recreate a control file or restore a controlfile from backup, you must recreate the tempfile in the temporary tablespace.

Alter temporary_tablespace add tempfile size xM reuse;

otherwise you will encounter the following error,

ORA-25153: Temporary Tablespace is Empty

Thanks

Restore Vs Recovery

Restore

Restore means using the backup files to replace the original files after a media failure.

Recovery

Recovery means bringing the database up to date using the restored files, archive logs and online redo logs.

Thanks

Crash Vs Instance Recovery

Crash Recovery Vs Instance Recovery

When the instance suddenly fail due to power outage or issuing shut abort, the instance requires recovery during next startup. Oracle will perform crash recovery up on restarting the database.

Crash Recovery involves two steps Cache recovery and transaction recovery.

Cache Recovery (Roll Forward) : The committed and Uncommitted data from the online redolog files are applied to the datafiles.

Transaction Recovery (Roll Back): The uncommitted data are rolled back from the datafiles.

In an RAC environment, one of the surviving instance performs the crash recovery of failed instance. This is known as instance recovery. In a single instance database, crash recovery and instance recovery are synonymous.


Thanks