It is common to hear that I accidentally deleted a table. Now how I can get back my table? Or I accidentally deleted some rows and committed. How I can get back my table to previous stage? Though it is takes almost no time to delete but sometimes it takes significant time to recover that table.
In the following section I will demonstrate possible solutions that we can have in order to recover a table.
1)If your database recyclebin parameter is on then if you dropped the table then it is best if you can recover it from recyclebin.
In order to know recylcebin on or off issue,
SQL> SHOW PARAMETER RECYCLEBIN;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
However if you previously on and after dropping off it then still object will be stored in recyclebin unless the tablespace are in space pressure. Now in order to get back your the dropped table simply issuing,
SQL>FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;
Simply an example.
SQL> drop table test2;
Table dropped.
SQL> flashback table test2 to before drop;
Flashback complete.
In order to get back more about flashback drop have a look at Flashback-Section Flashback Drop
2)Import the Table if you have good known logical backup.
If you previous export the table by exp or expdp and after that nothing is modified on the table then you can simply import your dropped table from the dump. Note that if a row in changed after taken dump that row will be not found after import. In order to know how export and import can be done please have a look at,
Data Pump Export/Import
Export/Import from Enterprise Manager
Original Export/Import
3)If you erroneously updated a subset of data and you have higher UNDO RETENTION parameter settings that is your undo data is not aged out; then using flashback query restore old stage of the table. In order to know it have a look at,
Restore old data using flashback query
If you erroneously deleted data of the table and now you want to get back to a previous state of the whole table you can have a look at,
Flashback Table Part, of this link
4)Export Data up to a previous point-in-time.
You can set FLASHBACK_SCN or FLASHBACK_TIME with exp or expdp in order to export table before erroneous update or erroneous deletion of table data. After export test import it in another schema whether exported data is acceptable or not. If you are satisfied then import the data to your primary database schema. The detail procedure is discussed on Export Data up to a previous point-in-time
5)Restore and recover a subset of the database as a DUMMY database to a point-in-time. Then export the table data from that dummy database and import it into the primary database. This is very good option as only the dropped table goes back in time to before the drop.
In order to perform this operation you can have a look at RMAN DUPLICATION database in which rman will duplicate subset of database to a point-in-time of the erroneous operation of the table. And then from that database export the table. RMAN database duplication is discussed on
Performing Database Duplication.
After duplication export the data of the table by using exp or expdp utility and import it to primary database. Export and Import is discussed on,
Data Pump Export/Import
Export/Import from Enterprise Manager
Original Export/Import
6)If you don't have enough undo to restore or don't have good logical backup or you don't have recylecbin parameter on(In case of drop table) or you have truncated the table but you have physical backup then you can do Tablespace point-in-time recovery. In order to know about TSPITR have a look at,
Performing TSPITR . Also have a look at its limitation performing performing. Limitations are described in Limitations of TSPITR
In case of TSPITR the entire tablespace will go back to a previous stage.
7)If you have flashback feature enabled then you can do flashback database to get back the entire database to a prior time.
Like, in mount stage run FLASHBACK DATABASE TO TIMESTAMP SYSTIMESTAMP-1/24/60*30; in order to get back whole database 30 minutes ago. After peforming flashback open the database READ ONLY , export the data , shutdown the database, and start the database with RESETLOGS option, import the data. The flashback database feature is discussed on,
Performing Flashback Database
8)If you are unable to perform any one of the above then you can use RMAN DBPITR to a point in time before the drop. This is an extreme measure for one table as the entire database goes back in time. DBPITR recovery is discussed on How to perform DBPITR
No comments:
Post a Comment