dul恢复drop表测试

2019独角兽企业重金招聘Python工程师标准>>>

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

1.被删除表所属表空间(非必须)
2.被删除表结构(必须)
模拟删除表

--创建测试表 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.

使用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

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   Copyright (c) 1994 2010 Bernard van Duijnen All rights reserved.    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

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

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

导入数据

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   Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.   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

恢复数据结果

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.

· 完美解决dul处理clob字段乱码问题

· dul支持ORACLE 12C CDB数据库恢复

· dul恢复truncate表测试

· exp dmp文件损坏恢复

· 跳过obj$坏块方法

· goldengate 异常处理

· 通过修改基表(link$)让非public dblink变为public

· sqlplus 使用小技巧

· table()函数用法

· long查询结果转换为varchar2类型

· oracle 建立分区表

· 表在线重定义(无主键)

 

更多Oracle学习：http://www.wyzc.com/ocp/?tg=3006123630

转载于:https://my.oschina.net/5486002/blog/680558