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, 10 October 2012

How to recover a DROP or TRUNCATE table using RMAN.

You can perform it either of any method but selecting the feasible solution is always important:
1.      Restore and recover the primary database to a point in time before the drop. In fact this is not a feasible solution for one table as the entire database goes back in time.
2.      Restore and recover the tablespace to a point in time before the drop. This is some how better solution, but again, it takes the entire tablespace back in time.
3.      Restore and recover a subset of the database as a DUPLICATE database to export the table data and import it into the primary database. This is the best option as only the dropped table goes back in time to before the drop.

To recover from a dropped or truncated table, a duplicate database (copy of primary) will be restored and recovered 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. This duplicate database can be a subset of the primary database. However, the ‘duplicate’ database must include the SYSTEM, UNDO (or ROLLBACK), and the tablespace where the dropped/truncated table resides.
The simplest method to create the duplicate database is to use the RMAN duplicate command. For Details check my post: Planning to Clone or Duplicate Database
RMAN TARGET SYS/oracle@trgt CATALOG catalog/catalog@RMAN auxiliary SYS/oracle@dupdb

Consider the following while going to duplicate or clone the Database:
·        The target database ‘trgt’ and duplicate database ‘dupdb’ are on different hosts but have exactly the same directory structure.
·        You want to name the duplicate database files the same as the target files.
·        You are using a recovery catalog.
·        You are using automatic channels for disk, which are already configured.
·        You want to recover the duplicate database to one week ago in order to view the data in production server as it appeared at that time (and you have the required backups and logs to recover the duplicate to that point in time).

After successful restoring the primary database to the point in time before the drop (7 days) as a duplicate database. Now you are able to export drop table from the duplicate database and import it into the primary database or using any other alternative method: Check my other post how to use import/export with Toad: Restore Schema using Import/Export with Toad
Using Import/Export:
exp HRMS/*****@sadhan file=Pay_Payment_Master.dmp log=Pay_Payment_Master.log tables=Pay_Payment_Master rows=yes indexes=no
imp HRMS/*****@orcl3 file=Pay_Payment_Master.dmp fromuser=HRMS touser=HRMS tables=Pay_Payment_Master
--or If you want to change username and schema then use this method:
exp userid=hrms/password file=pay_payment_master.dmp log=Pay_Payment_Master.log tables=Pay_payment_Master
imp userid=system/password file=pay_payment_master.dmp  fromuser=HRMS touser=xyz tables=pay_payment_master
Alternatively you can use copy table from one database to another as:
SQL>copy from hrms/****@targt to hrms/****@dupdb create pay_payment_master using select * from pay_payment_master where 1=2;
Using DB link:
Otherwise create the DB link and use the command as:
INSERT INTO table2@dupdb (SELECT * FROM table1);
Check my other post for more details about how to create DBlink: How to Create DBlink
Using Toad:
·        If you are using the Toad and you are able to connect both the database from it then:
·        Select the origin table in the schema browser and right click on it, select "Create in another schema" or "Copy Data to another schema”.
·        A dialog box appears; select your options as required then again "Destination" dialog box appears.
·        Select the connection to the destination database and schema of your choice
·        Click on execute.
·        Finally Toad will show a failure or success message.
For more step by step details of above method, you can follow the separate post on this blog: Copy Table from One database to another using Toad


  1. Quantum Binary Signals

    Professional trading signals sent to your mobile phone every day.

    Follow our signals NOW & make up to 270% daily.

  2. BlueHost is one of the best web-hosting provider for any hosting plans you might require.