oracle 延迟段,DUL 支持Oracle 19c , 如何手动处理延迟段创建的表

超、凢脫俗 2022-10-21 04:58 160阅读 0赞

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

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

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;

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 首页上的联系方式。

打赏

![8732971891f4ba05583674ca6b8145ac.png][]微信扫一扫，打赏作者吧～

[8732971891f4ba05583674ca6b8145ac.png]: /images/20221021/6ccc2816197e45ffb834ec1246ea9083.png

oracle 延迟段,DUL 支持Oracle 19c , 如何手动处理延迟段创建的表

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle表数据更新延迟问题分析

相关 Oracle表更新操作延迟：解决策略

相关 Oracle表级锁定导致的系统延迟案例

相关 Oracle创建表、删除表、修改表（添加字段、修改字段、删除字段）语句总结

相关 Oracle创建表、删除表、修改表（添加字段、修改字段、删除字段）语句总结

相关 Oracle数据库创建表ID字段的自动递增

相关 oracle 延迟段,DUL 支持Oracle 19c , 如何手动处理延迟段创建的表

相关 oracle非延迟约束,oracle 延迟性约束

相关程序延迟一段时间解决方案

相关延迟段创建失败