Email: service@parnassusdata.com 7 x 24 online support!

Oracle undrop table using PRM-DUL case study

Oracle undrop table using PRM-DUL case study

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

  1. OFFLINE the table space that the dropped table locates.
  2. 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.
  3. 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
  4. 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

 

PRM-DUL-DUL74

 

 

Add related ASM Disks and click ASM Analyze

PRM-DUL-DUL75

 

Select the character set in Non-Dict mode

 

 

PRM-DUL-DUL76

 

 

Select the data files of dropped table, and click scan

PRM-DUL-DUL77

 

 

PRM-DUL-DUL78

 

Generate database name and right click scan tables from extents:

 

PRM-DUL-DUL79

 

 

PRM-DUL-DUL80

 

 

Recognize TORDERDETAIL_HIS table which is mapped to DATA_OBJECT_ID=82641 manually and insert back to the database by DataBridge

PRM-DUL-DUL81PRM-DUL-DUL82

 

PRM-DUL-DUL83