咨询微信： dbservice1234 7 x 24 在线支持！

首页

下载 DBRECOVER

oracle undrop tablespace using PRM-DUL case study

oracle undrop tablespace using PRM-DUL case study

Posted by PDSERVICE on 11月 29, 2015 In

Recover DROP TABLESPACE Data

 

User D dropped a tablespace(“DROP TABLESAPCE INCLUDING CONTENTS”) by mistake. They want to recover data resided in that tablespace, but there is no RMAN backup.

Therefore, we can use PRM-DUL No-Dictionary mode to recover data. In this way, we can extract most data. However, the data is not mapping to the dictionary. Users need to manually recognize the table. Since it changed data dictionary by DROPPING TABLE and deleted objects in OBJ$, we can not have the relationship between DATA_OBJECT_ID and OBJECT_NAME. Below is the instruction of getting mapping.

 

 

 

select tablespace_name,segment_type,count(*) from dba_segments where owner=’PARNASSUSDATA’  group by tablespace_name,segment_type;

 

TABLESPACE SEGMENT_TYPE      COUNT(*)

———- ————— ———-

USERS      TABLE                  126

USERS      INDEX                  136

 

SQL> select count(*) from obj$;

 

COUNT(*)

———-

75698

 

 

SQL> select current_scn, systimestamp from v$database;

 

CURRENT_SCN

———–

SYSTIMESTAMP

—————————————————————————

1895940

25-4月 -14 09.18.00.628000 下午 +08:00

 

 

 

SQL> select file_name from dba_data_files where tablespace_name=’USERS';

 

FILE_NAME

——————————————————————————–

H:\PP\MACLEAN\ORADATA\PARNASSUS\DATAFILE\O1_MF_USERS_9MNBMJYJ_.DBF

 

 

SQL> drop tablespace users including contents;

 

 

C:\Users\maclean>dir H:\APP\MACLEAN\ORADATA\PARNASSUS\DATAFILE\O1_MF_USERS_9MNBMJYJ_.DBF

 

The volume is entertainment in drive H and SN is A87E-B792

 

H:\APP\MACLEAN\ORADATA\PARNASSUS\DATAFILE

 

The drive can not find the file

 

 

Here, we can use other file recovery tool for data file recovery, for example: Undeleter on Windows.

 

 

PRM-DUL-DUL65

 

 

Startup PRM-DUL => recovery Wizard => No-Dictionary

 

PRM-DUL-DUL66PRM-DUL-DUL67

 

 

This is No-Dictionary mode, and please select correct character set

 

 

PRM-DUL-DUL68

 

 

Add the files recovered and click scan

 

 

PRM-DUL-DUL69

 

PRM-DUL-DUL70

 

 

Start from the head segments, if it can not find all table, try to use extend scan:

 

PRM-DUL-DUL71

 

 

You can find lots of node named OBJXXXXX，this name is combination of “OBJ” and DATA_OBJECT_ID.  We need some guy who is familiar with schema design and application data, he can clarify the relationship between data and table.

 

PRM-DUL-DUL72

 

 

If there is no body can clarify the relationship between data and table, try below methods:

 

In this case, only user tablespace had been dropped and Oracle still works, and to get the mapping of DATA_OBJECT_ID and table name by FLASHBACK QUERY.

 

SQL>  select count(*) from sys.obj$;COUNT(*)———-75436 

 

 

 

 

 

 

 

SQL> select count(*) from sys.obj$ as of scn 1895940;

select count(*) from sys.obj$ as of scn 1895940

*

Error:

ORA-01555: Snapshot is too old,

 

Try to use DBA_HIST_SQL_PLAN of AWR and find the mapping between OBJECT# and OBJECT_NAME in recent 7 days.

 

SQL> desc DBA_HIST_SQL_PLAN

NAME                                        NULL? TYPE

—————————————– ——– ———————–

DBID                                      NOT NULL NUMBER

SQL_ID                                    NOT NULL VARCHAR2(13)

PLAN_HASH_VALUE                           NOT NULL NUMBER

ID                                        NOT NULL NUMBER

OPERATION                                          VARCHAR2(30)

OPTIONS                                            VARCHAR2(30)

OBJECT_NODE                                        VARCHAR2(128)

OBJECT#                                            NUMBER

OBJECT_OWNER                                       VARCHAR2(30)

OBJECT_NAME                                        VARCHAR2(31)

OBJECT_ALIAS                                       VARCHAR2(65)

OBJECT_TYPE                                        VARCHAR2(20)

OPTIMIZER                                          VARCHAR2(20)

PARENT_ID                                          NUMBER

DEPTH                                              NUMBER

POSITION                                           NUMBER

SEARCH_COLUMNS                                     NUMBER

COST                                               NUMBER

CARDINALITY                                        NUMBER

BYTES                                              NUMBER

OTHER_TAG                                          VARCHAR2(35)

PARTITION_START                                    VARCHAR2(64)

PARTITION_STOP                                     VARCHAR2(64)

PARTITION_ID                                       NUMBER

OTHER                                              VARCHAR2(4000)

DISTRIBUTION                                       VARCHAR2(20)

CPU_COST                                           NUMBER

IO_COST                                            NUMBER

TEMP_SPACE                                         NUMBER

ACCESS_PREDICATES                                  VARCHAR2(4000)

FILTER_PREDICATES                                  VARCHAR2(4000)

PROJECTION                                         VARCHAR2(4000)

TIME                                               NUMBER

QBLOCK_NAME                                        VARCHAR2(31)

REMARKS                                            VARCHAR2(4000)

TIMESTAMP                                          DATE

OTHER_XML                                          CLOB

 

 

For exmaple：

 

select object_owner,object_name,object# from DBA_HIST_SQL_PLAN where sql_id=’avwjc02vb10j4′

 

OBJECT_OWNER         OBJECT_NAME                                 OBJECT#

——————– —————————————- ———-

 

PARNASSUSDATA        TORDERDETAIL_HIS                              78688

 

 

 

Use below scrip for the mapping relationship between OBJECT_ID and OBJECT_NAME

 

Select * from

(select object_name,object# from DBA_HIST_SQL_PLAN

UNION select object_name,object# from GV$SQL_PLAN) V1 where V1.OBJECT# IS NOT NULL minus select name,obj# from sys.obj$;

 

select obj#,dataobj#, object_name from WRH$_SEG_STAT_OBJ where object_name not in (select name from sys.obJ$) order by object_name desc;

 

 

another script：

SELECT tab1.SQL_ID,

current_obj#,

tab2.sql_text

FROM DBA_HIST_ACTIVE_SESS_HISTORY tab1,

dba_hist_sqltext tab2

WHERE tab1.current_obj# NOT IN

(SELECT obj# FROM sys.obj$

)

AND current_obj#!=-1

AND tab1.sql_id  =tab2.sql_id(+);

 

 

 

Attention: Since it relies on AWR repository, the mapping table is not that accurate and exact.