标签归档:dul

dul支持Oracle 19C

Oracle 19C

[oracle@localhost ~]$ ss

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 2 07:02:18 2019
Version 19.2.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0

SQL> select name from v$datafile where rownum<3;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA19C/system01.dbf
/u01/app/oracle/oradata/ORA19C/sysaux01.dbf

dul支持19C

[root@localhost dul]# ./dul

Data UnLoader: 11.3.0.0.2 - Internal Only - on Sat Mar  2 08:26:11 2019
with 64-bit io functions and the decompression option

Copyright (c) 1994 2018 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


DUL: Warning: Recreating file "dul.log"
Found db_id = 1054612630
Found db_name = ORA19C
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 814, file 1 block 5424
 INDPART$: segobjno 819, file 1 block 5464
 TABCOMPART$: segobjno 836, file 1 block 5600
 INDCOMPART$: segobjno 841, file 1 block 5640
 TABSUBPART$: segobjno 826, file 1 block 5520
 INDSUBPART$: segobjno 831, file 1 block 5560
 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 740, tabno 1, file 1  block 4888
 COLLECTION$: segobjno 740, tabno 2, file 1  block 4888
 ATTRIBUTE$: segobjno 740, tabno 3, file 1  block 4888
 LOBFRAG$: segobjno 847, file 1 block 5688
 LOBCOMPPART$: segobjno 850, file 1 block 5720
 UNDO$: segobjno 15, file 1 block 224
 TS$: segobjno 6, tabno 2, file 1  block 176
 PROPS$: segobjno 127, file 1 block 1320
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
   72388 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
    2218 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
  123175 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
     127 rows unloaded
. unloading table                  TABPART$
DUL: Warning: Recreating file "TABPART.ctl"
     294 rows unloaded
. unloading table                  INDPART$
DUL: Warning: Recreating file "INDPART.ctl"
     195 rows unloaded
. unloading table               TABCOMPART$
DUL: Warning: Recreating file "TABCOMPART.ctl"
       1 row  unloaded
. unloading table               INDCOMPART$
DUL: Warning: Recreating file "INDCOMPART.ctl"
       0 rows unloaded
. unloading table               TABSUBPART$
DUL: Warning: Recreating file "TABSUBPART.ctl"
      32 rows unloaded
. unloading table               INDSUBPART$
DUL: Warning: Recreating file "INDSUBPART.ctl"
       0 rows unloaded
. unloading table                      IND$
DUL: Warning: Recreating file "IND.ctl"
    2878 rows unloaded
. unloading table                     ICOL$
DUL: Warning: Recreating file "ICOL.ctl"
    4958 rows unloaded
. unloading table                      LOB$
DUL: Warning: Recreating file "LOB.ctl"
     678 rows unloaded
. unloading table                  COLTYPE$
DUL: Warning: Recreating file "COLTYPE.ctl"
    2999 rows unloaded
. unloading table                     TYPE$
DUL: Warning: Recreating file "TYPE.ctl"
    5895 rows unloaded
. unloading table               COLLECTION$
DUL: Warning: Recreating file "COLLECTION.ctl"
    1384 rows unloaded
. unloading table                ATTRIBUTE$
DUL: Warning: Recreating file "ATTRIBUTE.ctl"
   15365 rows unloaded
. unloading table                  LOBFRAG$
DUL: Warning: Recreating file "LOBFRAG.ctl"
      14 rows unloaded
. unloading table              LOBCOMPPART$
DUL: Warning: Recreating file "LOBCOMPPART.ctl"
       0 rows unloaded
. unloading table                     UNDO$
DUL: Warning: Recreating file "UNDO.ctl"
      21 rows unloaded
. unloading table                       TS$
DUL: Warning: Recreating file "TS.ctl"
       6 rows unloaded
. unloading table                    PROPS$
DUL: Warning: Recreating file "PROPS.ctl"
      42 rows unloaded
Reading USER.dat 127 entries loaded
Reading OBJ.dat 72388 entries loaded and sorted 72388 entries
Reading TAB.dat 2201 entries loaded
Reading COL.dat
DUL: Notice: Increased the size of DC_COLUMNS from 100000 to 132768 entries
 123148 entries loaded and sorted 123148 entries
Reading TABPART.dat 294 entries loaded and sorted 294 entries
Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
Reading INDPART.dat 195 entries loaded and sorted 195 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 2878 entries loaded
Reading LOB.dat 678 entries loaded
Reading ICOL.dat 4958 entries loaded
Reading COLTYPE.dat 2999 entries loaded
Reading TYPE.dat 5895 entries loaded
Reading ATTRIBUTE.dat 15365 entries loaded
Reading COLLECTION.dat 1384 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 14 entries loaded and sorted 14 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 6 entries loaded
Reading PROPS.dat 42 entries loaded
DUL> desc sys.tab$;
Table SYS.TAB$
obj#= 4, dataobj#= 2, ts#= 0, file#= 1, block#=144
      tab#= 1, segcols= 45, clucols= 1
Column information:
icol# 01 segcol# 01         OBJ# len   22 type  2 NUMBER(0)
icol# 02 segcol# 02     DATAOBJ# len   22 type  2 NUMBER(0)
icol# 03 segcol# 03          TS# len   22 type  2 NUMBER(0)
icol# 04 segcol# 04        FILE# len   22 type  2 NUMBER(0)
icol# 05 segcol# 05       BLOCK# len   22 type  2 NUMBER(0)
icol# 06 segcol# 06        BOBJ# len   22 type  2 NUMBER(0)
icol# 07 segcol# 07         TAB# len   22 type  2 NUMBER(0)
icol# 08 segcol# 08         COLS len   22 type  2 NUMBER(0)
icol# 09 segcol# 09      CLUCOLS len   22 type  2 NUMBER(0)
icol# 10 segcol# 10     PCTFREE$ len   22 type  2 NUMBER(0)
icol# 11 segcol# 11     PCTUSED$ len   22 type  2 NUMBER(0)
icol# 12 segcol# 12     INITRANS len   22 type  2 NUMBER(0)
icol# 13 segcol# 13     MAXTRANS len   22 type  2 NUMBER(0)
icol# 14 segcol# 14        FLAGS len   22 type  2 NUMBER(0)
icol# 15 segcol# 15       AUDIT$ len   38 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 16 segcol# 16       ROWCNT len   22 type  2 NUMBER(0)
icol# 17 segcol# 17       BLKCNT len   22 type  2 NUMBER(0)
icol# 18 segcol# 18       EMPCNT len   22 type  2 NUMBER(0)
icol# 19 segcol# 19       AVGSPC len   22 type  2 NUMBER(0)
icol# 20 segcol# 20       CHNCNT len   22 type  2 NUMBER(0)
icol# 21 segcol# 21       AVGRLN len   22 type  2 NUMBER(0)
icol# 22 segcol# 22   AVGSPC_FLB len   22 type  2 NUMBER(0)
icol# 23 segcol# 23       FLBCNT len   22 type  2 NUMBER(0)
icol# 24 segcol# 24  ANALYZETIME len    7 type 12 DATE
icol# 25 segcol# 25   SAMPLESIZE len   22 type  2 NUMBER(0)
icol# 26 segcol# 26       DEGREE len   22 type  2 NUMBER(0)
icol# 27 segcol# 27    INSTANCES len   22 type  2 NUMBER(0)
icol# 28 segcol# 28      INTCOLS len   22 type  2 NUMBER(0)
icol# 29 segcol# 29   KERNELCOLS len   22 type  2 NUMBER(0)
icol# 30 segcol# 30     PROPERTY len   22 type  2 NUMBER(0)
icol# 31 segcol# 31     TRIGFLAG len   22 type  2 NUMBER(0)
icol# 32 segcol# 32       SPARE1 len   22 type  2 NUMBER(0)
icol# 33 segcol# 33       SPARE2 len   22 type  2 NUMBER(0)
icol# 34 segcol# 34       SPARE3 len   22 type  2 NUMBER(0)
icol# 35 segcol# 35       SPARE4 len 1000 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 36 segcol# 36       SPARE5 len 1000 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 37 segcol# 37       SPARE6 len    7 type 12 DATE
icol# 38 segcol# 38       SPARE7 len   22 type  2 NUMBER(0)
icol# 39 segcol# 39       SPARE8 len   22 type  2 NUMBER(0)
icol# 40 segcol# 40       SPARE9 len 1000 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 41 segcol# 41      SPARE10 len 1000 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 42 segcol# 42    ACDRFLAGS len   22 type  2 NUMBER(0)
icol# 43 segcol# 43   ACDRTSOBJ# len   22 type  2 NUMBER(0)
icol# 44 segcol# 44 ACDRDEFAULTTIME len   11 type 180 TIMESTAMP(9)
icol# 45 segcol# 45 ACDRROWTSINTCOL# len   22 type  2 NUMBER(0)

可能遇到错误

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 114321
DUL: Warning: Ignoring file COL.dat cache
发表在 ORACLE 19C, Oracle备份恢复 | 标签为 , | 评论关闭

truncate table 无论drop storage还是reuse storage不影响数据恢复

今天有朋友咨询,truncate table有drop storage和reuse storage方式,是否影响后续的数据恢复(在没有其他覆盖的情况下),我做了一个简单的测试证明,这些都不影响truncate table的数据库恢复
创建测试环境

SQL> create table t_xifenfei tablespace USERNEW
  2   as select * from dba_objects;

Table created.

SQL> create table t_xifenfei2  tablespace USERNEW
  2   as select * from dba_objects;

Table created.

SQL>  create table t_xifenfei3  tablespace USERNEW
  2  as select * from dba_objects;

Table created.

SQL> alter system checkpoint;

System altered.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     86348

SQL>  select count(*) from t_xifenfei2;

  COUNT(*)
----------
     86349

SQL>  select count(*) from t_xifenfei3;

  COUNT(*)
----------
     86350

SQL> select object_id,data_object_id,object_name from dba_objects where object_name like 't_xifenfei%';

 OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- ------------------------------
     88205          88205 t_xifenfei
     88206          88206 t_xifenfei2
     88207          88207 t_xifenfei3

SQL> truncate table t_xifenfei;

Table truncated.

SQL> truncate table t_xifenfei2 drop storage;

Table truncated.

SQL> truncate table t_xifenfei3 reuse storage;

Table truncated.

使用dul进行恢复

  
DUL>  bootstrap;
DUL> scan database;

scanning database...
scanning database finished.

DUL> unload table sys.t_xifenfei object 88205;

Unloading table: t_xifenfei,object ID: 88205
Unloading segment,storage(Obj#=88205 DataObj#=88205 TS#=6 File#=5 Block#=1410 Cluster=0)
86348 rows unloaded

DUL> unload table sys.t_xifenfei2 object 88206;

Unloading table: t_xifenfei2,object ID: 88206
Unloading segment,storage(Obj#=88206 DataObj#=88206 TS#=6 File#=5 Block#=2690 Cluster=0)
86349 rows unloaded

DUL> unload table sys.t_xifenfei3 object 88207;

Unloading table: t_xifenfei3,object ID: 88207
Unloading segment,storage(Obj#=88207 DataObj#=88207 TS#=6 File#=5 Block#=3970 Cluster=0)
86350 rows unloaded

这里证明truncate table不管是drop storage还是reuse storage或者默认,在没有被覆盖的情况下,数据均完全恢复出来

发表在 Oracle备份恢复 | 标签为 , , | 评论关闭

Oracle dul支持Oracle 12.2(12c)

随着oracle 12.2正式版的临近,通过最新版dul(Data UnLoader: 11.2.0.1.1)的测试,发现对于12c的版本支持依旧有问题,虽然作者在版本中较为明确的表示了支持12c但是测试效果不尽人意.
dul 11对oracle 12c的支持(Compatible参数表明支持12c)

[oracle@localhost dul]$ ./dul

Data UnLoader: 11.2.0.1.1 - Internal Only - on Tue Feb 21 23:39:08 2017
with 64-bit io functions and the decompression option

Copyright (c) 1994 2016 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


Within one week you will need a more recent DUL version for this os
DUL: Warning: Compatible is set to 11 Values can be 6|7|8|9|10|11|12

在12.2的版本中运行dul结果如下

[oracle@xifenfei ~]$ ss

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 21 23:54:43 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set lines 150
SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                        0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0


[root@xifenfei dul]# ./dul

Data UnLoader: 11.2.0.1.1 - Internal Only - on Tue Feb 21 23:53:37 2017
with 64-bit io functions and the decompression option

Copyright (c) 1994 2016 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


Within one week you will need a more recent DUL version for this os
Found db_id = 766547632
Found db_name = ORCL12C
DUL> bootstrap;
Probing file = 1, block = 520
. unloading table                BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow

DUL: Warning: Recreating file "BOOTSTRAP.ctl"
      60 rows unloaded
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
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
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
   72655 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
    2162 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
  114785 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
     130 rows unloaded
Reading USER.dat 130 entries loaded
Reading OBJ.dat
DUL: FATAL Error: File OBJ.dat, line 22079: identifier too long

这个报错DUL: FATAL Error: File OBJ.dat比较明显由于dul在加载obj$对象的时候有对象字符太长导致加载失败
dul-error


这个是由于在oracle 12c中已经允许对象名/列名程度为128,而不是以前版本的30,从而引起了这个问题,虽然dul的作者已经在dict.ddl中做了限制,但是目前看尚未生效
dul-clear

特殊处理后的dul可以完美支持12c

[root@xifenfei dul]# ./dul

Data UnLoader: 11.2.0.1.1 - Internal Only - on Wed Feb 22 00:04:40 2017
with 64-bit io functions and the decompression option

Copyright (c) 1994 2016 Bernard van Duijnen All rights reserved.


 Strictly Oracle Internal Use Only


Within one week you will need a more recent DUL version for this os
Found db_id = 766547632
Found db_name = ORCL12C

DUL> bootstrap;
Probing file = 1, block = 520
. unloading table                BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow

DUL: Warning: Recreating file "BOOTSTRAP.ctl"
      60 rows unloaded
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
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
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
   72655 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
    2162 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
  114785 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
     130 rows unloaded
Reading USER.dat 130 entries loaded
Reading OBJ.dat 72655 entries loaded and sorted 72655 entries
Reading TAB.dat 2162 entries loaded
Reading COL.dat 114785 entries loaded and sorted 114785 entries
DUL> unload table sys.undo$;
. unloading table                     UNDO$      21 rows unloaded
DUL> exit

Life is DUL without it

这里是通过特殊处理之后dul才能够支持12c版本,期待作者在后续版本中完善相关工作,在恢复的经验中dul还是一大利器.

发表在 Oracle | 标签为 , , , | 评论关闭