oracle恢复drop建的表首次,案例:Oracle dul数据挖掘没有备份情况下非常规恢复drop删除的数据表...

最新推荐文章于 2021-04-10 13:24:47 发布

weixin_39987138

最新推荐文章于 2021-04-10 13:24:47 发布

阅读量166

点赞数

文章标签： oracle恢复drop建的表首次

本文详细介绍了如何使用DUL工具在Oracle数据库中恢复被DROP删除的数据表，包括通过Logminer找到data_object_id，使用DUL进行扫描和恢复，以及通过SQL*Loader导入数据，最终成功恢复了数据表并验证了恢复数据的完整性。

摘要由CSDN通过智能技术生成

天萃荷净

通过Oracle dul工具在没有备份情况下进行非常规恢复，找出drop删除的Oracle数据表中的数据进行恢复

dul对被drop对象进行恢复，需要提供两个信息

1.被删除表所属表空间(非必须)

2.被删除表结构(必须)

1.Oracle数据库中模拟删除表

--创建测试表

SQL> create table t_dul_drop tablespace czum

2 as

3 select * from dba_tables;

Table created.

--备份被删除表数据，便于比较和提供测试表结构

SQL> create table t_dul_drop_bak tablespace users

2 as select * from t_dul_drop;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> select count(*) from t_dul_drop;

COUNT(*)

----------

1785

SQL> drop table chf.t_dul_drop purge;

Table dropped.

SQL> alter system checkpoint;

System altered.

2.使用logminer找到data_object_id

delete from "SYS"."OBJ$" where "OBJ#" = '68474' and "DATAOBJ#" = '68474'

and "OWNER#" = '61' and "NAME" = 'T_DUL_DROP' and "NAMESPACE" = '1' and

"SUBNAME" IS NULL and "TYPE#" = '2' and "CTIME" = TO_DATE('04-FEB-13', 'DD-MON-RR')

and "MTIME" = TO_DATE('04-FEB-13', 'DD-MON-RR') and "STIME" = TO_DATE('04-FEB-13', 'DD-MON-RR')

and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0'

and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3" = '61' and

"SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAASAABAAAPzCAAV';

这里可以知道，被删除表的data_object_id为68474

3.DUL恢复被删除表

--dul版本

E:\dul10>dul.exe

Data UnLoader 10.2.4.37 - Oracle Internal Only - on Mon Feb 04 23:49:50 2013

with 64-bit io functions

Strictly Oracle Internal use Only

DUL> ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;

Parameter altered

--扫描所属表空间

DUL> scan tablespace 6;

Scanning tablespace 6, data file 6 ...

13 segment header and 331 data blocks

tablespace 6, data file 6: 1279 blocks scanned

Reading EXT.dat 13 entries loaded and sorted 13 entries

Reading SEG.dat 13 entries loaded

Reading COMPATSEG.dat 0 entries loaded

Reading SCANNEDLOBPAGE.dat 0 entries loaded and sorted 0 entries

--scan tables得到需求表(可以核对数据样例)

DUL> scan tables;

UNLOAD TABLE OBJNO68474 ( COL001 VARCHAR2(11), COL002 VARCHAR2(30), COL003 VARCHAR2(6)

, COL004 VARCHAR2(20), COL005 VARCHAR2(30), COL006 VARCHAR2(5), COL007 NUMBER

, COL008 NUMBER, COL009 NUMBER, COL010 NUMBER, COL011 NUMBER

, COL012 NUMBER, COL013 NUMBER, COL014 NUMBER, COL015 CHAR

, COL016 NUMBER, COL017 NUMBER, COL018 VARCHAR2(3), COL019 VARCHAR2(1)

, COL020 NUMBER, COL021 NUMBER, COL022 NUMBER, COL023 NUMBER

, COL024 NUMBER, COL025 NUMBER, COL026 NUMBER, COL027 NUMBER

, COL028 VARCHAR2(10), COL029 VARCHAR2(10), COL030 VARCHAR2(5), COL031 VARCHAR2(7)

, COL032 NUMBER, COL033 DATE, COL034 VARCHAR2(3), COL035 VARCHAR2(12)

, COL036 VARCHAR2(1), COL037 VARCHAR2(1), COL038 VARCHAR2(3), COL039 VARCHAR2(7)

, COL040 VARCHAR2(7), COL041 VARCHAR2(7), COL042 VARCHAR2(8), COL043 VARCHAR2(3)

, COL044 VARCHAR2(2), COL045 VARCHAR2(15), COL046 VARCHAR2(8), COL047 VARCHAR2(3)

, COL048 VARCHAR2(3), COL049 VARCHAR2(8), COL050 VARCHAR2(8), COL051 VARCHAR2(5)

, COL052 VARCHAR2(2), COL053 VARCHAR2(2), COL054 VARCHAR2(3), COL055 VARCHAR2(7) )

STORAGE( DATAOBJNO 68474 );

--恢复删除表(业务提供表结构)

DUL> unload table t_dul_drop(

2 OWNER VARCHAR2(30),

3 TABLE_NAME VARCHAR2(30),

4 TABLESPACE_NAME VARCHAR2(30),

5 CLUSTER_NAME VARCHAR2(30),

6 IOT_NAME VARCHAR2(30),

7 STATUS VARCHAR2(8) ,

8 PCT_FREE NUMBER ,

9 PCT_USED NUMBER ,

10 INI_TRANS NUMBER ,

11 MAX_TRANS NUMBER ,

12 INITIAL_EXTENT NUMBER ,

13 NEXT_EXTENT NUMBER ,

14 MIN_EXTENTS NUMBER ,

15 MAX_EXTENTS NUMBER ,

16 PCT_INCREASE NUMBER ,

17 FREELISTS NUMBER ,

18 FREELIST_GROUPS NUMBER ,

19 LOGGING VARCHAR2(3) ,

20 BACKED_UP VARCHAR2(1) ,

21 NUM_ROWS NUMBER ,

22 BLOCKS NUMBER ,

23 EMPTY_BLOCKS NUMBER ,

24 AVG_SPACE NUMBER ,

25 CHAIN_CNT NUMBER ,

26 AVG_ROW_LEN NUMBER ,

27 AVG_SPACE_FREELIST_BLOCKS NUMBER ,

28 NUM_FREELIST_BLOCKS NUMBER ,

29 DEGREE VARCHAR2(20),

30 INSTANCES VARCHAR2(20),

31 CACHE VARCHAR2(10),

32 TABLE_LOCK VARCHAR2(8) ,

33 SAMPLE_SIZE NUMBER ,

34 LAST_ANALYZED DATE ,

35 PARTITIONED VARCHAR2(3) ,

36 IOT_TYPE VARCHAR2(12),

37 TEMPORARY VARCHAR2(1) ,

38 SECONDARY VARCHAR2(1) ,

39 NESTED VARCHAR2(3) ,

40 BUFFER_POOL VARCHAR2(7) ,

41 FLASH_CACHE VARCHAR2(7) ,

42 CELL_FLASH_CACHE VARCHAR2(7) ,

43 ROW_MOVEMENT VARCHAR2(8) ,

44 GLOBAL_STATS VARCHAR2(3) ,

45 USER_STATS VARCHAR2(3) ,

46 DURATION VARCHAR2(15),

47 SKIP_CORRUPT VARCHAR2(8) ,

48 MONITORING VARCHAR2(3) ,

49 CLUSTER_OWNER VARCHAR2(30),

50 DEPENDENCIES VARCHAR2(8) ,

51 COMPRESSION VARCHAR2(8) ,

52 COMPRESS_FOR VARCHAR2(12),

53 DROPPED VARCHAR2(3) ,

54 READ_ONLY VARCHAR2(3) ,

55 SEGMENT_CREATED VARCHAR2(3) ,

56 RESULT_CACHE VARCHAR2(7))

57 STORAGE( DATAOBJNO 68474 );

. unloading table T_DUL_DROP

DUL: Warning: Recreating file "T_DUL_DROP.ctl"

1785 rows unloaded

4.模拟业务规则提供，创建表

SQL> create table t_dul_drop as select * from t_dul_drop_bak where 1=0;

Table created.

5.导入Oracle被删除的数据

e:\dul10>sqlldr chf/xifenfei control=T_DUL_DROP.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Mon Feb 4 23:35:57 2013

Commit point reached - logical record count 64

Commit point reached - logical record count 128

Commit point reached - logical record count 192

Commit point reached - logical record count 256

Commit point reached - logical record count 320

Commit point reached - logical record count 384

Commit point reached - logical record count 448

Commit point reached - logical record count 512

Commit point reached - logical record count 576

Commit point reached - logical record count 640

Commit point reached - logical record count 704

Commit point reached - logical record count 768

Commit point reached - logical record count 832

Commit point reached - logical record count 896

Commit point reached - logical record count 960

Commit point reached - logical record count 1024

Commit point reached - logical record count 1088

Commit point reached - logical record count 1152

Commit point reached - logical record count 1216

Commit point reached - logical record count 1280

Commit point reached - logical record count 1344

Commit point reached - logical record count 1408

Commit point reached - logical record count 1472

Commit point reached - logical record count 1536

Commit point reached - logical record count 1600

Commit point reached - logical record count 1664

Commit point reached - logical record count 1728

Commit point reached - logical record count 1785

6.验证恢复数据结果

SQL> select count(*) from t_dul_drop;

COUNT(*)

----------

1785

SQL> select owner,table_name from t_dul_drop where rownum<10;

OWNER TABLE_NAME

------------------------------ ------------------------------

SYS IDL_CHAR$

SYS IDL_UB2$

SYS IDL_SB4$

SYS ERROR$

SYS SETTINGS$

SYS NCOMP_DLL$

SYS PROCEDUREJAVA$

SYS PROCEDUREC$

SYS PROCEDUREPLSQL$

9 rows selected.

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle dul数据挖掘没有备份情况下非常规恢复drop删除的数据表

weixin_39987138

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

oracle恢复drop建的表首次,案例:Oracle dul数据挖掘 没有备份情况下非常规恢复drop删除的数据表...

oracle恢复drop建的表首次,案例:Oracle dul数据挖掘没有备份情况下非常规恢复drop删除的数据表...