Oracle undrop table using PRM-DUL case study

Download PRM-DUL http://zcdn.parnassusdata.com/DUL5108.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

Comment

*

沪ICP备14014813号

沪公网安备 31010802001379号