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.
Startup PRM-DUL => recovery Wizard => No-Dictionary
This is No-Dictionary mode, and please select correct character set
Add the files recovered and click scan
Start from the head segments, if it can not find all table, try to use extend scan:
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.
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.