Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 4 April 2012

Recovery of Dropped Tablespace (TSPITR‎) Method

After Dropping Tablespace by mistake we need to restore that tablespace. For this purpose we can use any of the listed solution:
1. Database Point in Time Recovery (DBPITR)
2. Tablespace Point in Time Recovery (TSPITR)
3. Use DUPLICATE database concept to export the table and import it into primary database.
4. Transportable Tablespace Method (TTS).

1. DBPITR Concepts: DBPITR enables you to recover a database to some time in the past. For Example, if a logical error occurred today at 7:30 AM, DBPITR would enable you to restore the entire database to the state it was in 07:29 AM there by removing the effect of the error but also remove all other valid updates that  occurred since 07:29 AM.
2. TSPITR Concepts: TSPITR enables you to recover that particular tablespace to some time in the past, leaving the remainder of database at the current time.
3. Use DUPLICATE database concept: To recover from a drop and truncated table, a duplicated database (copy of primary) will be restored and recover to point in time, so the table can be exported. Once the table export is complete, the table can be imported into the primary database.
4. TTS: TTS enable to transport of large amount of data from one oracle database to another database instead of creating DDL and DML statements for each row and object in the tablespaces.
Note: Check the Alert.log file to find exact drop time.
SQL> shutdown abort;
startup nomount;
SET UNTIL TIME "TO_DATE ('01-04-12 04:10:00', 'DD-MM-YY HH24:MI:SS')";
restore controlfile;
alter database mount;
restore database;
recover database;
alter database open resetlogs;

To perform DBPITR step during the TSPITR, RMAN requires the database to be in archivelog mode and existing backup of database that was created before the point in time recovery, and all the archivelogs and online logs created from the time of the backup until the point in time recovery . Also the controlfile recovery (from autobackup) must matches with the database physical schema at the point in time when recovery ends.

Caution:‎ The first option will require an outage of the entire database and ‎the entire database will be rolled back in time in order to recover ‎the tablespace. The third option can be performed online, but we will ‎need to factor in the disk space requirements to create a clone of ‎the database from which the tablespace has been dropped.‎ The fourth or last but not least  is Transportable tablespace, is the very good or feasable solution especially when you run multiple application against particular database. Later we will discuss how to perform tablespace recovery using TTS. check the below link: How to recover a DROP or TRUNCATE table using RMAN


Post a Comment