Oracle undrop table using PRM-DUL case study

Posted by PDSERVICE on Nov 29, 2015 In

Download PRM-DUL http://7xl1jo.com2.z0.glb.qiniucdn.com/DUL3206.zip

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:

PRM-DUL-DUL73