oracle dul是oracle的恢复利器， 它的传奇功能不再解释，但是dul和其它工具一样也是需要段(SEGMENT)块信息恢复数据，但是从oracle 11g开始支持了延迟段创建，那么使用dul unload table[user]|[database]默认是不会导出未生成段的表对象， 这样恢复的数据理论也会因为表不存在而丢失部分空表。但是表结构是在数据字典中可以手动生成建表语句。
创建测试表
[oracle@anbob19 ~]$ ora
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 18 20:56:09 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
5 ERPDB MOUNTED
SQL> create user weejar identified by weejar ;
SQL> grant create table ,resource to weejar;
SQL> ALTER USER WEEJAR QUOTA 100M ON USERS;
SQL> CREATE TABLE weejar.T_seg(ID NUMBER,NAME VARCHAR2(10))
SEGMENT CREATION IMMEDIATE tablespace users;
SQL> CREATE TABLE weejar.T_noseg(ID NUMBER,NAME VARCHAR2(10))
SEGMENT CREATION DEFERRED tablespace users;
SQL> SELECT SEGMENT_NAME FROM dba_SEGMENTS where owner='WEEJAR';
SEGMENT_NAME
------------------------------------------------------
T_SEG
DUL 测试
[oracle@anbob19 tools]$ ./dul
Data UnLoader: 12.0.0.0.3 - Internal Only - on Wed Dec 18 21:03:26 2019
with 64-bit io functions and the decompression option
Copyright (c) 1994 2019 Bernard van Duijnen All rights reserved.
Strictly Oracle Internal Use Only
DUL: Warning: Recreating file "dul.log"
DUL: Warning: ulimit process stack size is only 33554432
Reading USER.dat 132 entries loaded
Reading OBJ.dat 72553 entries loaded and sorted 72553 entries
Reading TAB.dat 2248 entries loaded
Reading COL.dat 124044 entries loaded and sorted 124044 entries
Reading BOOTSTRAP.dat 60 entries loaded
Found db_id = 3379558177
Found db_name = ANBOB19C
DUL> bootstrap;
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 12
OBJ$: segobjno 18, file 1 block 240
TAB$: segobjno 2, tabno 1, file 1 block 144
COL$: segobjno 2, tabno 5, file 1 block 144
USER$: segobjno 10, tabno 1, file 1 block 208
TABPART$: segobjno 812, file 1 block 5152
INDPART$: segobjno 817, file 1 block 5192
TABCOMPART$: segobjno 834, file 1 block 11608
INDCOMPART$: segobjno 839, file 0 block 0
TABSUBPART$: segobjno 824, file 1 block 11904
INDSUBPART$: segobjno 829, file 0 block 0
IND$: segobjno 2, tabno 3, file 1 block 144
ICOL$: segobjno 2, tabno 4, file 1 block 144
LOB$: segobjno 2, tabno 6, file 1 block 144
COLTYPE$: segobjno 2, tabno 7, file 1 block 144
TYPE$: segobjno 738, tabno 1, file 1 block 1664
COLLECTION$: segobjno 738, tabno 2, file 1 block 1664
ATTRIBUTE$: segobjno 738, tabno 3, file 1 block 1664
LOBFRAG$: segobjno 845, file 1 block 5240
LOBCOMPPART$: segobjno 848, file 0 block 0
UNDO$: segobjno 15, file 1 block 224
TS$: segobjno 6, tabno 2, file 1 block 176
PROPS$: segobjno 127, file 1 block 1136
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
72545 rows unloaded
. unloading table TAB$
DUL: Warning: Recreating file "TAB.ctl"
2248 rows unloaded
. unloading table COL$
DUL: Warning: Recreating file "COL.ctl"
124044 rows unloaded
. unloading table USER$
DUL: Warning: Recreating file "USER.ctl"
132 rows unloaded
. unloading table TABPART$ 333 rows unloaded
. unloading table INDPART$ 203 rows unloaded
. unloading table TABCOMPART$ 1 row unloaded
DUL: Error: dc_segment_header(dataobj#=839, ts#=0, fil=0, blk=0) failed
DUL: Warning: Nothing to unload from empty delayed segment creation table INDCOMPART$
. unloading table TABSUBPART$ 32 rows unloaded
DUL: Error: dc_segment_header(dataobj#=829, ts#=0, fil=0, blk=0) failed
DUL: Warning: Nothing to unload from empty delayed segment creation table INDSUBPART$
. unloading table IND$ 2923 rows unloaded
. unloading table ICOL$ 5006 rows unloaded
. unloading table LOB$ 697 rows unloaded
. unloading table COLTYPE$ 3035 rows unloaded
. unloading table TYPE$ 4629 rows unloaded
. unloading table COLLECTION$ 1384 rows unloaded
. unloading table ATTRIBUTE$ 15365 rows unloaded
. unloading table LOBFRAG$ 30 rows unloaded
DUL: Error: dc_segment_header(dataobj#=848, ts#=0, fil=0, blk=0) failed
DUL: Warning: Nothing to unload from empty delayed segment creation table LOBCOMPPART$
. unloading table UNDO$ 21 rows unloaded
. unloading table TS$ 8 rows unloaded
. unloading table PROPS$ 40 rows unloaded
Reading USER.dat 132 entries loaded
Reading OBJ.dat 72545 entries loaded and sorted 72545 entries
Reading TAB.dat
DUL: Error: string2ub8(618970019642690137449563136), Conversion to number (ub8) overflowed
DUL: Error: Number conversion error in file TAB.dat, line 22
DUL: Warning: Ignoring file TAB.dat cache
Reading COL.dat
DUL: Error: string2ub8(73786976294838206464), Conversion to number (ub8) overflowed
DUL: Error: Number conversion error in file COL.dat, line 114376
DUL: Warning: Ignoring file COL.dat cache
Reading TABPART.dat 333 entries loaded and sorted 333 entries
Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
Reading INDPART.dat 203 entries loaded and sorted 203 entries
Reading IND.dat 2923 entries loaded
Reading LOB.dat 697 entries loaded
Reading ICOL.dat 5006 entries loaded
Reading COLTYPE.dat 3035 entries loaded
Reading TYPE.dat
DUL: Notice: Increased the size of DC_TYPES from 4096 to 32768 entries
4629 entries loaded
Reading ATTRIBUTE.dat 15365 entries loaded
Reading COLLECTION.dat
DUL: Notice: Increased the size of DC_COLLECTIONS from 1024 to 8192 entries
1384 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 30 entries loaded and sorted 30 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 8 entries loaded
Reading PROPS.dat 40 entries loaded
Database character set is AL32UTF8
Database national character set is AL16UTF16
DUL> show datafiles;
ts# rf# start blocks offs open err file name
0 1 0 34561 0 1 0 /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf
1 4 0 47361 0 1 0 /u01/app/oracle/oradata/ANBOB19C/pdb1/sysaux01.dbf
2 9 0 12801 0 1 0 /u01/app/oracle/oradata/ANBOB19C/pdb1/undotbs01.dbf
5 12 0 88161 0 1 0 /u01/app/oracle/oradata/ANBOB19C/pdb1/users01.dbf
6 13 0 12801 0 1 0 /u01/app/oracle/oradata/ANBOB19C/pdb1/auto_index_tbs01.dbf
DUL> desc weejar.t_seg;
Table WEEJAR.T_SEG
obj#= 74058, dataobj#= 74058, ts#= 5, file#= 12, block#=82634
tab#= 0, segcols= 2, clucols= 0
Column information:
icol# 01 segcol# 01 ID len 22 type 2 NUMBER(0)
icol# 02 segcol# 02 NAME len 10 type 1 VARCHAR2 cs 873(AL32UTF8)
DUL> desc weejar.t_noseg;
Table WEEJAR.T_NOSEG
obj#= 74059, dataobj#= 74059, ts#= 5, file#= 0, block#=0
tab#= 0, segcols= 2, clucols= 0
Column information:
icol# 01 segcol# 01 ID len 22 type 2 NUMBER(0)
icol# 02 segcol# 02 NAME len 10 type 1 VARCHAR2 cs 873(AL32UTF8)
DUL> unload user weejar;
About to unload WEEJAR's tables ...
. unloading table T_SEG 0 rows unloaded
DUL: Warning: Nothing to unload from empty delayed segment creation table T_NOSEG
DUL> unload table weejar.t_noseg;
DUL: Warning: Nothing to unload from empty delayed segment creation table T_NOSEG
Note:
注意t_noseg表因为是空表延迟段创建，所以什么也没有导出。另外dul对于19c有些字段值特殊长的会报错，需要手动编辑一下字典才可以。
恢复delayed segment creation table（延迟段创建的表）
需要unload 的字典表
user$, tab$ , obj$, col$, coltype$, tabpart$
查找延迟段创建的表(分区表稍加改动)
SQL> @desc anbob.test;
Name Null? Type
------------------------------- -------- ----------------------------
1 ID NUMBER(10)
2 NAME NOT NULL VARCHAR2(10)
3 AGE NUMBER
SQL> @ddl anbob.test
PL/SQL procedure successfully completed.
DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
-------------------------------------------------------------------------------
CREATE TABLE "ANBOB"."TEST"
( "ID" NUMBER(10,0),
"NAME" VARCHAR2(10) DEFAULT 'A' NOT NULL ENABLE,
"AGE" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERTBS" ;
SQL>
1 with tab as(
2 select u.name owner,o.name table_name,
3 decode(bitand(t.property, 17179869184), 17179869184, 'NO',
4 decode(bitand(t.property, 32), 32, 'N/A', 'YES')) segment_created
5 from user$ u, tab$ t, obj$ o
6 where o.owner# = u.user#
7 and o.obj# = t.obj#
8 and bitand(t.property, 1) = 0
9 and bitand(o.flags, 128) = 0 and u.name='ANBOB')
10* select * from tab where segment_created='NO'
OWNER TABLE_NAME SEG
------------------------------ ------------------------------ ---
ANBOB TEST NO
Note:
查找列所需要的表在19c中你会发现查询依赖DBA_TAB_COLUMNS的基表时，增加了一些版本特殊的view，不在本篇范围内。记录一下依赖关系DBA_TAB_COLUMNS —{ DBA_TAB_COLS —{ dba_tab_cols_v$， dba_views改为了text_vc列而不是过去的text列， 该表是long类型只能显示4k长度，所以从cdcore_mig.sql 可以得到全部脚本。
set serveroutput on size 1000000
declare
starting boolean :=true;
r_owner varchar2(30) := 'ANBOB';
r_table_name varchar2(30) := 'TEST';
cursor c is select u.name OWNER, o.name TABLE_NAME,
c.name COLUMN_NAME,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
2, decode(c.scale, null,
decode(c.precision#, null, 'NUMBER', 'FLOAT'),
'NUMBER'),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
12, 'DATE',
23, 'RAW', 24, 'LONG RAW',
58, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
100, 'BINARY_FLOAT',
101, 'BINARY_DOUBLE',
105, 'MLSLABEL',
106, 'MLSLABEL',
111, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
122, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
123, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
178, 'TIME(' ||c.scale|| ')',
179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
180, 'TIMESTAMP(' ||c.scale|| ')',
181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',
182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
c.scale || ')',
208, 'UROWID',
'UNDEFINED') DATA_TYPE,
c.length DATA_LENGTH, c.precision# DATA_PRECISION, c.scale DATA_SCALE,
decode(sign(c.null$),-1,'D', 0, 'Y', 'N') NULLABLE,
decode(c.col#, 0, to_number(null), c.col#) COLUMN_ID,
c.default$ DATA_DEFAULT
from col$ c, obj$ o, user$ u, coltype$ ac, obj$ ot, user$ ut, tab$ t
where o.obj# = c.obj#
and o.obj# = t.obj#(+)
and o.owner# = u.user#
and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
and ac.toid = ot.oid$(+)
and ot.type#(+) = 13
and ot.owner# = ut.user#(+)
and bitand(o.flags, 128) = 0
and o.name=r_table_name and u.name=r_owner
order by decode(c.col#, 0, to_number(null), c.col#);
begin
dbms_output.put_line(lpad('*',100,'*'));
dbms_output.put_line('create table '||r_owner||'.'||r_table_name||'(');
for r in c
loop
if starting then
starting:=false;
else
dbms_output.put_line(',');
end if;
if r.data_type='NUMBER' then
if r.data_scale is null then
dbms_output.put(r.column_name||' NUMBER('||r.data_precision||')');
else
dbms_output.put(r.column_name||' NUMBER('||r.data_precision||','||r.data_scale||')');
end if;
else if r.data_type = 'DATE' then
dbms_output.put_line(r.column_name||' DATE');
else if instr(r.data_type, 'CHAR') >0 then
dbms_output.put(r.column_name||' '||r.data_type||'('||r.data_length||')');
else
dbms_output.put(r.column_name||' '||r.data_type);
end if;
end if;
end if;
if r.data_default is not null then
dbms_output.put(' DEFAULT '||r.data_default);
end if;
if r.nullable = 'N' then
dbms_output.put(' NOT NULL ');
end if;
end loop;
dbms_output.put_line(' ); ');
end;
/
输出结果
****************************************************************
create table ANBOB.TEST(
ID NUMBER(10,0),
NAME VARCHAR2(10) DEFAULT 'A' NOT NULL ,
AGE NUMBER() );
PL/SQL procedure successfully completed.
如果你恢复不了， 请联系 www.anbob.com 首页上的联系方式。