TABLESPACE POINT IN TIME RECOVERY

                            TABLESPACE POINT IN TIME RECOVERY

WHAT IS TSPITR?

ü  Tablespace point in time recovery (TSPITR) with RMAN uses a technique of cloning a primary database with the minimum physical structure required to recover a tablespace to the desired point in time.

WHAT IS THE NEW THING IN ORACLE 11GR2?

ü  Recover a dropped tablespace. Before 11gr2 TSPITR had a restriction of not being able to recover a dropped tablespace.

WHAT TSPITR DOES?

ü  Creates the auxiliary dummy instance

ü  Creates a clone control file.

ü  Restores/Recovers the SYSTEM, SYSAUX, UNDO & TABLESPACE Dropped.

ü  After recover it will make the datafile online. Later it will use the export of metada from source (TRANSPORTABLE tablespace)

ü  Later auxiliary database is shutdown & metadata is imported.

ü  Finally Datafile is brought online

OVERALL STEPS

1.       Create a Tablespace Test

2.       Create a user

3.       Create some objects

4.       Backup the database with archivelog

5.       Drop the tablespace

6.       Obtain the log sequence number from alert log

7.       Restore tablespace tablespace_name until logseq seq# auxiliary destination ‘/location’;

 IMPLEMENTATION

Create a Tablespace INDD

   Create a User

       Create some objects

1.       

                                   

           Backup the database with archivelog

         Drop the tablespace

             Obtain the log sequence number from alert log

                                                                                                                                                                                                                                                                                                 

So here we see after seq 23 tablespace has dropped. Let’s restore till sequence 24.

1.       Restore tablespace tablespace_name until logseq seq# auxiliary destination ‘/location’;

   

      

       Tablespace is available now