CASE 10: Recover Data after Dropping Table by mistake.
User D dropped one most important application table in ASM without any backup. Oracle introduced recyclebin feature in 10g. Please check whether the dropped table is in recyclebin by DBA_RECYCLEBINS view. If there is , try to recover data back by “flashback to before drop”. Or, we can use PRM-DUL for recovery.
Recovery steps by PRM-DUL
- OFFLINE the table space that the dropped table locates.
- Find the DATA_OBJECT_ID of dropped table by query data dictionary or logminer. If not successfully, then user has to recognize this table in No-dictionary mode.
- Start PRM-DUL, go to No-dictionary mode, and add all data files of dropped data file. Then SCAN DATABASE+SCAN TABLE from Extent MAP
- Locate the data table by DATA_OBJECT_ID in object tress, and insert data back by DataBridge
| SQL> select count(*) from “MACLEAN”.”TORDERDETAIL_HIS”;COUNT(*)———-984359SQL>
SQL> create table maclean.TORDERDETAIL_HIS1 as select * from maclean.TORDERDETAIL_HIS;
Table created.
SQL> drop table maclean.TORDERDETAIL_HIS;
Table dropped. |
We can find the general DATA_OBJECT_ID by logminer or similar method in “CASE 9”
| EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => ‘/oracle/logs/log1.f’, OPTIONS => DBMS_LOGMNR.NEW);EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => ‘/oracle/logs/log2.f’, OPTIONS => DBMS_LOGMNR.ADDFILE);Execute DBMS_LOGMNR.START_LOGMNR(DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.COMMITTED_DATA_ONLY);SELECT * FROM V$LOGMNR_CONTENTS ;
EXECUTE DBMS_LOGMNR.END_LOGMNR; |
Although, there is no DATA_OBJECT_ID, if the table amount is not big, we can manually recognize the data table
OFFLINE table space of dropped table
| SQL> select tablespace_name from dba_segments where segment_name=’TPAYMENT';TABLESPACE_NAME——————————USERSSQL> select file_name from dba_data_files where tablespace_name=’USERS';
FILE_NAME
—————————————————————-
+DATA1/parnassus/datafile/users.263.843694795
SQL> alter tablespace users offline;
Tablespace altered. |
Start PRM-DUL in NON-DICT mode, and add all data to SCAN DATABASE+SCAN TABLE From Extents:
Add related ASM Disks and click ASM Analyze
Select the character set in Non-Dict mode
Select the data files of dropped table, and click scan
Generate database name and right click scan tables from extents:
Recognize TORDERDETAIL_HIS table which is mapped to DATA_OBJECT_ID=82641 manually and insert back to the database by DataBridge
