标签归档：oracle dul

Oracle dul支持Oracle 12.2(12c)

发表于 2017 年 2 月 22 日由惜分飞

随着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$对象的时候有对象字符太长导致加载失败

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

特殊处理后的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 | 标签为 dul, dul 12.2, dul 12c, oracle dul | 评论关闭

oracle dul 11 正式发布

发表于 2016 年 6 月 13 日由惜分飞

千呼万唤oracle dul 11终于发布了,迫不及待的下载来测试,现在版本号为dul 11.2.0.0.1,目前只发布了for linux,其他版本估计要等等.期待该版本有引进新功能
oracle dul 11发布
dul-11.2
oracle-dul-11

dul 11 测试恢复oracle 12c版本

[root@ora1221 dul11]# ./dul

Data UnLoader: 11.2.0.0.1 - Internal Only - on Mon Jun 13 00:04:50 2016
with 64-bit io functions

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

 Strictly Oracle Internal Use Only


DUL: Warning: Recreating file "dul.log"
Found db_id = 1414800805
Found db_name = ORCL

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
      60 rows unloaded
DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
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$   72547 rows unloaded
. unloading table                      TAB$    2137 rows unloaded
. unloading table                      COL$  115645 rows unloaded
. unloading table                     USER$     129 rows unloaded
Reading USER.dat 129 entries loaded
Reading OBJ.dat
DUL: FATAL Error: File OBJ.dat, line 22094: identifier too long

dul 11 恢复oracle 12c 报错分析
看报错OBJ.dat报错数据,从定义上判断是长度过长

[root@ora1221 dul11]# vi OBJ.dat 
  22091 "22435" "" "0" "JVMFCB" "" "9" ""
  22092 "22436" "" "0" "JVMFCB" "" "11" ""
  22093 "22437" "56417" "0" "JAVA$CBOBJ$" "" "2" ""
----  22094 "22439" "" "0" "sun/util/xml/PlatformXmlPropertiesProvider$Resolver" "" "29" ""
  22095 "22440" "" "0" "sun/util/xml/PlatformXmlPropertiesProvider$EH" "" "29" ""
  22096 "22441" "" "0" "sun/util/xml/PlatformXmlPropertiesProvider$1" "" "29" ""
  22097 "22442" "" "0" "sun/util/resources/LocaleNamesBundle" "" "29" ""

对象名长度分析

[oracle@ora1221 ~]$ ss

SQL*Plus: Release 12.2.0.0.3 Production on Fri Aug 7 02:50:35 2015

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


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

SQL> select length(object_name) from dba_objects 
  2  where object_name='sun/util/xml/PlatformXmlPropertiesProvider$Resolver';

LENGTH(OBJECT_NAME)
-------------------
                 51

SQL> select max(length(object_name)) from dba_objects;

MAX(LENGTH(OBJECT_NAME))
------------------------
                     123

SQL> desc dba_objects
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(128)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(128)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(23)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(128)
 SHARING                                            VARCHAR2(18)
 EDITIONABLE                                        VARCHAR2(1)
 ORACLE_MAINTAINED                                  VARCHAR2(1)
 APPLICATION                                        VARCHAR2(1)
 DEFAULT_COLLATION                                  VARCHAR2(100)
 DUPLICATED                                         VARCHAR2(1)
 SHARDED                                            VARCHAR2(1)
 CREATED_APPID                                      NUMBER
 CREATED_VSNID                                      NUMBER
 MODIFIED_APPID                                     NUMBER
 MODIFIED_VSNID                                     NUMBER

在oracle 12c中对象名长度已经变为128,但是dul依旧不能支持列长度，既然作者已经在init.dul的Compatible参数曾增加了对12c的支持,那对这样明显的bug应该在不久的将来即可修复。当然这个问题,可以通过一些人工技巧绕过去

发表在非常规恢复 | 标签为 dul, dul 11, oracle dul | 评论关闭

ORA-01157: cannot identify/lock data file导致表空间无法online

发表于 2014 年 12 月 27 日由惜分飞

朋友和我聊到一个案例,一个表空间本来有一个数据文件(已经有大量数据进入),然后他加入7个数据文件,这个时候他发现他加多了文件,想删除这些数据文件,可是又不怎么懂数据库,直接offline 表空间,然后rm掉相关数据文件,最后表空间无法online.我对其场景进行了模拟恢复测试,使用bbed和dul对其进行恢复

模拟场景
表空间有一个数据文件,里面有数据,然后加入一个新数据文件,进入offline tbs,然后rm数据文件
offline表空间后,该表空间存在的数据文件也会offline

SQL> create tablespace xifenfei datafile 
  2  '/u01/oracle/ora9i/oradata/xifenfei01.dbf' size 10M autoextend on next 16M;

Tablespace created.

SQL> create table chf.t_xifenfei01 tablespace xifenfei
  2  as select * from dba_objects;

Table created.

SQL> create table chf.t_xifenfei02 tablespace xifenfei
  2  as select * from dba_tables;

Table created.

SQL> SELECT COUNT(*) FROM CHF.T_XIFENFEI01;

  COUNT(*)
----------
     30758

SQL> C/01/02
  1* SELECT COUNT(*) FROM CHF.T_XIFENFEI02
SQL> /

  COUNT(*)
----------
       865

SQL> select tablespace_name,segment_name from dba_segments             
  2  where segment_name like 'T_XIFENFEI%';

TABLESPACE_NAME                SEGMENT_NAME
------------------------------ -------------------------
XIFENFEI                       T_XIFENFEI01
XIFENFEI                       T_XIFENFEI02

SQL> ALTER tablespace xifenfei add datafile 
  2  '/u01/oracle/ora9i/oradata/xifenfei02.dbf' size 16m;

Tablespace altered.

SQL> alter tablespace xifenfei offline;

Tablespace altered.

SQL> select file#,status$ from file$;

     FILE#    STATUS$
---------- ----------
         1          2
         2          2
         3          2
         4          2
         5          2
         6          2
         7          2
         8          2
         9          2
        10          2
        11          2
        12          2

12 rows selected.

SQL> set pages 1000
SQL> /

     FILE#    STATUS$
---------- ----------
         1          2
         2          2
         3          2
         4          2
         5          2
         6          2
         7          2
         8          2
         9          2
        10          2
        11          2
        12          2

12 rows selected.

SQL> select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 ONLINE
         7 ONLINE
         8 ONLINE
         9 ONLINE
        10 ONLINE
        11 OFFLINE
        12 OFFLINE

12 rows selected.

SQL> select file#,status from v$datafile_header;

     FILE# STATUS
---------- -------
         1 ONLINE
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 ONLINE
         7 ONLINE
         8 ONLINE
         9 ONLINE
        10 ONLINE
        11 OFFLINE
        12 OFFLINE

12 rows selected.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
CWMLITE                        ONLINE
DRSYS                          ONLINE
EXAMPLE                        ONLINE
INDX                           ONLINE
ODM                            ONLINE
TOOLS                          ONLINE
USERS                          ONLINE
XDB                            ONLINE
XIFENFEI                       OFFLINE

12 rows selected.


SQL> select ts#,name,online$ from ts$;

       TS# NAME                              ONLINE$
---------- ------------------------------ ----------
         0 SYSTEM                                  1
         1 UNDOTBS1                                1
         2 TEMP                                    1
         3 CWMLITE                                 1
         4 DRSYS                                   1
         5 EXAMPLE                                 1
         6 INDX                                    1
         7 ODM                                     1
         8 TOOLS                                   1
         9 USERS                                   1
        10 XDB                                     1
        11 UNDOTBS2                                3
        12 XIFENFEI                                2

13 rows selected.

SQL> col name for a50
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 /u01/oracle/ora9i/oradata/ora9i/system01.dbf
         2 /u01/oracle/ora9i/oradata/ora9i/undotbs01.dbf
         3 /u01/oracle/ora9i/oradata/ora9i/cwmlite01.dbf
         4 /u01/oracle/ora9i/oradata/ora9i/drsys01.dbf
         5 /u01/oracle/ora9i/oradata/ora9i/example01.dbf
         6 /u01/oracle/ora9i/oradata/ora9i/indx01.dbf
         7 /u01/oracle/ora9i/oradata/ora9i/odm01.dbf
         8 /u01/oracle/ora9i/oradata/ora9i/tools01.dbf
         9 /u01/oracle/ora9i/oradata/ora9i/users01.dbf
        10 /u01/oracle/ora9i/oradata/ora9i/xdb01.dbf
        11 /u01/oracle/ora9i/oradata/xifenfei01.dbf
        12 /u01/oracle/ora9i/oradata/xifenfei02.dbf

12 rows selected.

SQL> !rm /u01/oracle/ora9i/oradata/xifenfei02.dbf

SQL> !ls -l /u01/oracle/ora9i/oradata/xifenfei*
-rw-r-----  1 ora9i oinstall 10493952 Dec 25 20:19 /u01/oracle/ora9i/oradata/xifenfei01.dbf

SQL> alter tablespace xifenfei online;
alter tablespace xifenfei online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/u01/oracle/ora9i/oradata/xifenfei02.dbf'

这里已经构造出来,由于由于数据文件丢失导致表空间online报ORA-01157: cannot identify/lock data file的错误

online存在数据文件
存在的数据文件online之后，表空间依然offline

SQL> alter database datafile 11 online;

Database altered.

SQL> select file#,status from v$datafile_header;

     FILE# STATUS
---------- -------
         1 ONLINE
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 ONLINE
         7 ONLINE
         8 ONLINE
         9 ONLINE
        10 ONLINE
        11 ONLINE
        12 OFFLINE

12 rows selected.

SQL> select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 ONLINE
         7 ONLINE
         8 ONLINE
         9 ONLINE
        10 ONLINE
        11 ONLINE
        12 OFFLINE

12 rows selected.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
CWMLITE                        ONLINE
DRSYS                          ONLINE
EXAMPLE                        ONLINE
INDX                           ONLINE
ODM                            ONLINE
TOOLS                          ONLINE
USERS                          ONLINE
XDB                            ONLINE
XIFENFEI                       OFFLINE

12 rows selected.

SQL> select ts#,name,online$ from ts$;

       TS# NAME                                                  ONLINE$
---------- -------------------------------------------------- ----------
         0 SYSTEM                                                      1
         1 UNDOTBS1                                                    1
         2 TEMP                                                        1
         3 CWMLITE                                                     1
         4 DRSYS                                                       1
         5 EXAMPLE                                                     1
         6 INDX                                                        1
         7 ODM                                                         1
         8 TOOLS                                                       1
         9 USERS                                                       1
        10 XDB                                                         1
        11 UNDOTBS2                                                    3
        12 XIFENFEI                                                    2

13 rows selected.

SQL> create table chf.t_1 tablespace xifenfei
  2  as select * from dual;
as select * from dual
                 *
ERROR at line 2:
ORA-01542: tablespace 'XIFENFEI' is offline, cannot allocate space in it

由于表空间无法online,因此对该表空间分配报ORA-01542错误

使用bbed构造数据文件欺骗数据库

[ora9i@xifenfei tmp]$ dd if=/dev/zero of=/u01/oracle/ora9i/oradata/xifenfei02.dbf
> bs=8192 count=2049
2049+0 records in
2049+0 records out

BBED> copy file 11 block 1 to file 12 block 1
 File: /u01/oracle/ora9i/oradata/xifenfei02.dbf (12)
 Block: 1                Offsets:    0 to  511           Dba:0x03000001
------------------------------------------------------------------------
 0b020000 0100c002 00000000 00000104 1deb0000 00002009 00000008 329bf29e 
 4f524139 49000000 11010000 00050000 00200000 0b000300 00000000 00000000 

 <32 bytes per line>


BBED> set count 32
        COUNT           32

BBED> m /x 0c
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/ora9i/oradata/xifenfei02.dbf (12)
 Block: 1                Offsets:  280 to  311           Dba:0x03000001
------------------------------------------------------------------------
 0c000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> m /x 0c
 File: /u01/oracle/ora9i/oradata/xifenfei02.dbf (12)
 Block: 1                Offsets:   52 to   83           Dba:0x03000001
------------------------------------------------------------------------
 0c000300 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> sum apply
Check value for File 12, Block 1:
current = 0xad21, required = 0xad21


SQL> alter tablespace xifenfei online;
alter tablespace xifenfei online
*
ERROR at line 1:
ORA-01122: database file 12 failed verification check
ORA-01110: data file 12: '/u01/oracle/ora9i/oradata/xifenfei02.dbf'
ORA-01251: Unknown File Header Version read for file number 12

SQL> !oerr ora 01251
01251, 00000, "Unknown File Header Version read for file number %s"
// *Cause:  Read of the file header returned a record but its version cannot  
//         be identified.  Either the header has been corrupted, or the file
//         is not a valid database file.
// *Action: Have the operating system make the correct file available to  
//         the database, or recover the file.  

BBED> m /x 01000003
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/ora9i/oradata/xifenfei02.dbf (12)
 Block: 1                Offsets:    4 to  36           Dba:0x03000001
------------------------------------------------------------------------
 01000003 00000000 00000104 21ad0000 00002009 00000008 329bf29e 4f524139 

 <32 bytes per line>

BBED> sum apply
Check value for File 12, Block 1:
current = 0xace1, required = 0xace1


SQL> alter tablespace xifenfei online;
alter tablespace xifenfei online
*
ERROR at line 1:
ORA-01122: database file 12 failed verification check
ORA-01110: data file 12: '/u01/oracle/ora9i/oradata/xifenfei02.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN

BBED> m /x 613B0300
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/ora9i/oradata/xifenfei02.dbf (12)
 Block: 1                Offsets:  100 to  131           Dba:0x03000001
------------------------------------------------------------------------
 613b0300 00000000 f35bb133 f39bae33 b46c0200 00000000 00000000 00000000 

 <32 bytes per line>

BBED> sum apply
Check value for File 12, Block 1:
current = 0xa0c9, required = 0xa0c9


--提示数据文件scn比控制文件新(由于正常数据文件online过)
SQL> alter tablespace xifenfei online;
alter tablespace xifenfei online
*
ERROR at line 1:
ORA-01122: database file 12 failed verification check
ORA-01110: data file 12: '/u01/oracle/ora9i/oradata/xifenfei02.dbf'
ORA-01207: file is more recent than controlfile - old controlfile

SQL> set lines 160
SQL> col name for a50
SQL> select ts#,file#,BYTES/1024/1024/1024 status,enabled,
  2  to_char(checkpoint_change#,'999999999999999') "SCN",
  3  to_char(last_change#,'999999999999999')"STOP_SCN",
  4  name from v$datafile;

  TS#  FILE#     STATUS ENABLED SCN      STOP_SCN         NAME
----- --- ---------- ---------- -------- ------------- ---------------------------------------------
    0   1  .37109375 READ WRITE   198231               /u01/oracle/ora9i/oradata/ora9i/system01.dbf
    1   2   .1953125 READ WRITE   198231               /u01/oracle/ora9i/oradata/ora9i/undotbs01.dbf
    3   3  .01953125 READ WRITE   198231               /u01/oracle/ora9i/oradata/ora9i/cwmlite01.dbf
    4   4  .01953125 READ WRITE   198231               /u01/oracle/ora9i/oradata/ora9i/drsys01.dbf
    5   5 .145874023 READ WRITE   198231               /u01/oracle/ora9i/oradata/ora9i/example01.dbf
    6   6 .024414063 READ WRITE   198231               /u01/oracle/ora9i/oradata/ora9i/indx01.dbf
    7   7  .01953125 READ WRITE   198231               /u01/oracle/ora9i/oradata/ora9i/odm01.dbf
    8   8 .009765625 READ WRITE   198231               /u01/oracle/ora9i/oradata/ora9i/tools01.dbf
    9   9 .024414063 READ WRITE   198231               /u01/oracle/ora9i/oradata/ora9i/users01.dbf
   10  10 .043945313 READ WRITE   198231               /u01/oracle/ora9i/oradata/ora9i/xdb01.dbf
   12  11 .009765625 DISABLED     228353        228353 /u01/oracle/ora9i/oradata/xifenfei01.dbf
   12  12          0 DISABLED     213917        213917 /u01/oracle/ora9i/oradata/xifenfei02.dbf

12 rows selected.

SQL> set pages 100
SQL> set linesize 150
SQL> select ts#,file#,TABLESPACE_NAME,status,
  2  to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATE_TIME,
  3  to_char(checkpoint_change#,'9999999999999999') "SCN",
  4  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN",FUZZY
from v$datafile_header;  5  

TS# FILE# TABLESPACE_NAME  STATUS  CREATE_TIME         SCN       RESETLOGS SCN     FUZ
--- ----- ---------------- ------- ------------------- --------- ----------------- ---
  0     1 SYSTEM           ONLINE  2004-03-10 12:17:49    198231            158900 YES
  1     2 UNDOTBS1         ONLINE  2004-03-10 20:30:20    198231            158900 YES
  3     3 CWMLITE          ONLINE  2004-03-10 12:18:10    198231            158900 YES
  4     4 DRSYS            ONLINE  2004-03-10 12:18:11    198231            158900 YES
  5     5 EXAMPLE          ONLINE  2004-03-10 12:18:12    198231            158900 YES
  6     6 INDX             ONLINE  2004-03-10 12:18:16    198231            158900 YES
  7     7 ODM              ONLINE  2004-03-10 12:18:17    198231            158900 YES
  8     8 TOOLS            ONLINE  2004-03-10 12:18:19    198231            158900 YES
  9     9 USERS            ONLINE  2004-03-10 12:18:19    198231            158900 YES
 10    10 XDB              ONLINE  2004-03-10 12:18:20    198231            158900 YES
 12    11 XIFENFEI         OFFLINE 2014-12-25 17:57:07    228353            158900 NO
  0    12                  OFFLINE                             0                 0

12 rows selected.

BBED> m /x 9D430300
 File: /u01/oracle/ora9i/oradata/xifenfei02.dbf (12)
 Block: 1                Offsets:  140 to  171           Dba:0x03000001
------------------------------------------------------------------------
 9d430300 00000000 f670b133 01000000 02000000 fc4e0000 10000000 02000000 

 <32 bytes per line>

BBED> SUM APPLY
Check value for File 12, Block 1:
current = 0x9982, required = 0x9982


SQL> ALTER TABLESPACE XIFENFEI ONLINE;

Tablespace altered.

在bbed的恢复过程中出现以下常见错误:
ORA-01122: database file 12 failed verification check
ORA-01110: data file 12: ‘/u01/oracle/ora9i/oradata/xifenfei02.dbf’
ORA-01251: Unknown File Header Version read for file number 12
这个主要是块号和块地址不一匹配,导致数据库无法识别

ORA-01122: database file 12 failed verification check
ORA-01110: data file 12: ‘/u01/oracle/ora9i/oradata/xifenfei02.dbf’
ORA-01203: wrong incarnation of this file – wrong creation SCN
这个由于数据文件头的创建scn和控制文件以及file$中的记录不一致

ORA-01122: database file 12 failed verification check
ORA-01110: data file 12: ‘/u01/oracle/ora9i/oradata/xifenfei02.dbf’
ORA-01207: file is more recent than controlfile – old controlfile
这个由于正常数据文件online过,或者你构造文件头使用的scn大于需要构造文件在控制文件中记录的scn值

后续处理

SQL> SELECT COUNT(*) FROM CHF.T_XIFENFEI01;

  COUNT(*)
----------
     30758

SQL> C/01/02
  1* SELECT COUNT(*) FROM CHF.T_XIFENFEI02
SQL> /

  COUNT(*)
----------
       865

SQL> ALTER DATABASE DATAFILE 12 OFFLINE DROP;

Database altered.

证明通过bbed,模拟数据文件,使用完美恢复数据内容

dul恢复

SQL> ALTER TABLESPACE XIFENFEI OFFLINE;

Tablespace altered.

[ora9i@xifenfei dul]$ ./dul

Data UnLoader: 10.2.0.5.33 - Internal Only - on Thu Dec 25 21:18:54 2014
with 64-bit io functions

Copyright (c) 1994 2014 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 = 2666699570
Found db_name = ORA9I
DUL> bootstrap;
Probing file = 1, block = 417
  database version 9 bootstrap$ at file 1, block 377
. unloading table                BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
      57 rows unloaded
DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
Reading BOOTSTRAP.dat 57 entries loaded
Parsing Bootstrap$ contents
Generating dict.ddl for version 9
 OBJ$: segobjno 18, file 1 block 121
 TAB$: segobjno 2, tabno 1, file 1  block 25
 COL$: segobjno 2, tabno 5, file 1  block 25
 USER$: segobjno 10, tabno 1, file 1  block 89
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$   31490 rows unloaded
. unloading table                      TAB$     909 rows unloaded
. unloading table                      COL$   35582 rows unloaded
. unloading table                     USER$      63 rows unloaded
Reading USER.dat 63 entries loaded
Reading OBJ.dat 31490 entries loaded and sorted 31490 entries
Reading TAB.dat 909 entries loaded
Reading COL.dat 35582 entries loaded and sorted 35582 entries
Reading BOOTSTRAP.dat 57 entries loaded

DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 9
 OBJ$: segobjno 18, file 1 block 121
 TAB$: segobjno 2, tabno 1, file 1  block 25
 COL$: segobjno 2, tabno 5, file 1  block 25
 USER$: segobjno 10, tabno 1, file 1  block 89
 TABPART$: segobjno 230, file 1 block 1657
 INDPART$: segobjno 234, file 1 block 1689
 TABCOMPART$: segobjno 249, file 1 block 1809
 INDCOMPART$: segobjno 253, file 1 block 1841
 TABSUBPART$: segobjno 240, file 1 block 1737
 INDSUBPART$: segobjno 245, file 1 block 1777
 IND$: segobjno 2, tabno 3, file 1  block 25
 ICOL$: segobjno 2, tabno 4, file 1  block 25
 LOB$: segobjno 2, tabno 6, file 1  block 25
 COLTYPE$: segobjno 2, tabno 7, file 1  block 25
 TYPE$: segobjno 296, tabno 1, file 1  block 2129
 COLLECTION$: segobjno 296, tabno 2, file 1  block 2129
 ATTRIBUTE$: segobjno 296, tabno 3, file 1  block 2129
 LOBFRAG$: segobjno 258, file 1 block 1881
 LOBCOMPPART$: segobjno 261, file 1 block 1905
 UNDO$: segobjno 15, file 1 block 105
 TS$: segobjno 6, tabno 2, file 1  block 57
 PROPS$: segobjno 101, file 1 block 769
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
   31490 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
     909 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
   35582 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
      63 rows unloaded
. unloading table                  TABPART$      55 rows unloaded
. unloading table                  INDPART$     128 rows unloaded
. unloading table               TABCOMPART$       0 rows unloaded
. unloading table               INDCOMPART$       0 rows unloaded
. unloading table               TABSUBPART$       0 rows unloaded
. unloading table               INDSUBPART$       0 rows unloaded
. unloading table                      IND$    1396 rows unloaded
. unloading table                     ICOL$    1842 rows unloaded
. unloading table                      LOB$     427 rows unloaded
. unloading table                  COLTYPE$    1160 rows unloaded
. unloading table                     TYPE$     956 rows unloaded
. unloading table               COLLECTION$     250 rows unloaded
. unloading table                ATTRIBUTE$    3623 rows unloaded
. unloading table                  LOBFRAG$       0 rows unloaded
. unloading table              LOBCOMPPART$       0 rows unloaded
. unloading table                     UNDO$      21 rows unloaded
. unloading table                       TS$      13 rows unloaded
. unloading table                    PROPS$      25 rows unloaded
Reading USER.dat 63 entries loaded
Reading OBJ.dat 31490 entries loaded and sorted 31490 entries
Reading TAB.dat 909 entries loaded
Reading COL.dat 35582 entries loaded and sorted 35582 entries
Reading TABPART.dat 55 entries loaded and sorted 55 entries
Reading TABCOMPART.dat 0 entries loaded and sorted 0 entries
Reading TABSUBPART.dat 0 entries loaded and sorted 0 entries
Reading INDPART.dat 128 entries loaded and sorted 128 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 1396 entries loaded
Reading LOB.dat 427 entries loaded
Reading ICOL.dat 1842 entries loaded
Reading COLTYPE.dat 1160 entries loaded
Reading TYPE.dat 956 entries loaded
Reading ATTRIBUTE.dat 3623 entries loaded
Reading COLLECTION.dat 250 entries loaded
Reading BOOTSTRAP.dat 57 entries loaded
Reading LOBFRAG.dat 0 entries loaded and sorted 0 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 13 entries loaded
Reading PROPS.dat 25 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16
DUL> unload table chf.t_xifenfei01;
. unloading table              T_XIFENFEI01   30758 rows unloaded
DUL> unload table chf.t_xifenfei02;
. unloading table              T_XIFENFEI02     865 rows unloaded
DUL>

这里证明,在表空间不能online的情况下,dul处理类似故障很轻松(但是需要重建表空间,bbed处理的从原理上可以不重建表空间),当然如果涉及的对象很多,而丢失的文件很少,可能bbed也不失为一种很不错的选择.
另外补充:如果表空间丢失的文件包含数据,也可以通过类似bbed和dul方法处理:bbed 让表空间online之后需要考虑跳过丢失文件;dul处理完全相同

发表在非常规恢复 | 标签为 ORA-01122，ORA-01110，ORA-01251，ORA-01203，ORA-01207，ORA-01157，dul, oracle dul, 表空间不能online，数据库恢复 | 一条评论

13429648788

QQ 咨询

标签归档：oracle dul

Oracle dul支持Oracle 12.2(12c)

oracle dul 11 正式发布

ORA-01157: cannot identify/lock data file导致表空间无法online

文章分类

最近发表

友情链接

标签归档：oracle dul

Oracle dul支持Oracle 12.2(12c)

oracle dul 11 正式发布

ORA-01157: cannot identify/lock data file导致表空间无法online

标签云

文章分类

最近发表

友情链接