首页 » Cloud, ORACLE 9i-23ai » DUL 支持Oracle 19c , 如何手动处理延迟段创建的表

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

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

打赏

对不起,这篇文章暂时关闭评论。