obj$坏块exp/expdp导出不能正常执行

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:obj$坏块exp/expdp导出不能正常执行

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

今天有个朋友的多个库同时出现了obj$表出现坏块,总计数据量在100T-200T之间,而且是非归档模式,幸好数据不是很重要,不然将是一个非常大的悲剧。
我通过试验模拟证明obj$表如果出现坏块(具体方法见:bbed破坏数据文件),数据库的不能通过逻辑导出,然后建库。
一.alert发现坏块

Sat Jan 14 17:36:53 2012
Errors in file /opt/oracle/diag/rdbms/chf/chf/trace/chf_smon_493.trc:
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

二.检查坏块对象

[oracle@node1 chf]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 14 17:40:29 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> col owner for a10
SQL> col SEGMENT_NAME for a15
SQL> col SEGMENT_TYPE for a10
SQL> col TABLESPACE_NAME for a10
SQL> col PARTITION_NAME for a10
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
Enter value for file_id: 1
old   3:  WHERE FILE_ID = &FILE_ID
new   3:  WHERE FILE_ID = 1
Enter value for block_id: 95369
old   4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new   4:    AND 95369 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER      SEGMENT_NAME    SEGMENT_TY TABLESPACE PARTITION_
---------- --------------- ---------- ---------- ----------
SYS        OBJ$            TABLE      SYSTEM

三.验证坏块方法
1.sql查询

SQL> select /*+ full(obj$) */ count(*) from obj$;
select /*+ full(obj$) */ count(*) from obj$
                                       *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

2.dump数据文件

SQL> alter system dump datafile 1 block 95369;

System altered.

--查看dump文件
Start dump data blocks tsn: 0 file#:1 minblk 95369 maxblk 95369
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0 rdba=4289673
BH (0x6aff6a88) file#: 1 rdba: 0x00417489 (1/95369) class: 1 ba: 0x6af3c000
  set: 19 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 25,19
  dbwrid: 0 obj: 90724 objn: 90724 tsn: 0 afn: 1 hint: f
  hash: [0x6d7f6088,0x838655e0] lru: [0x6aff6ca0,0x6aff6a40]
  ckptq: [NULL] fileq: [NULL] objq: [0x6b3f2458,0x6a3e03c8] objaq: [0x6b3f2468,0x6a3e03d8]
  st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 0
  flags: only_sequential_access auto_bmr_tried
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
BH (0x6d7f5fd8) file#: 1 rdba: 0x00417489 (1/95369) class: 1 ba: 0x6d72a000
  set: 23 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 16,28
  dbwrid: 0 obj: 90724 objn: 90724 tsn: 0 afn: 1 hint: f
  hash: [0x838655e0,0x6aff6b38] lru: [0x60ff3ac0,0x83b346e8]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: FREE md: NULL fpin: 'kdswh11: kdst_fetch' tch: 0 lfb: 33
  flags:
Block dump from disk:
buffer tsn: 0 rdba: 0x00417489 (1/95369)
scn: 0x0000.00db0299 seq: 0x01 flg: 0x06 tail: 0x39393332
frmt: 0x02 chkval: 0x05f8 type: 0x06=trans data
Hex dump of corrupt header 2 = BROKEN
Dump of memory from 0x00002B5631A02A00 to 0x00002B5631A02A14
2B5631A02A00 0000A206 00417489 00DB0299 06010000  [.....tA.........]
2B5631A02A10 000005F8                             [....]     

SQL>   select object_name from dba_objects where object_id=90724;

OBJECT_NAME
----------------------------------
OBJ$
       

3.bbed

[oracle@node1 chf]$ bbed
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Sat Jan 14 18:28:25 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename 'system01.dbf'
        FILENAME        ./system01.dbf

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> set block 95369
        BLOCK#          95369

BBED> verify
DBVERIFY - Verification starting
FILE = ././system01.dbf
BLOCK = 95368

Block 95368 is corrupt
Corrupt block relative dba: 0x00417489 (file 0, block 95369)
Fractured block found during verification
Data in bad block:
 type: 6 format: 2 rdba: 0x00417488
 last change scn: 0x0000.00da8bce seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x65636238
 check value in block header: 0x9925
 computed block checksum: 0x8a94


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 2
Message 531 not found;  product=RDBMS; facility=BBED

4.dbv

[oracle@node1 chf]$ dbv file=system01.dbf

DBVERIFY: Release 11.2.0.3.0 - Production on Sat Jan 14 18:29:43 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /opt/oracle/oradata/chf/system01.dbf
Page 95369 is influx - most likely media corrupt
Corrupt block relative dba: 0x00417489 (file 1, block 95369)
Fractured block found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x00417489
 last change scn: 0x0000.00db0299 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x39393332
 check value in block header: 0x5f8
 computed block checksum: 0xe93



DBVERIFY - Verification complete

Total Pages Examined         : 172800
Total Pages Processed (Data) : 132246
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 15726
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 3548
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 21278
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 1
Total Pages Encrypted        : 0
Highest block SCN            : 16682372 (0.16682372)

5.rman

[oracle@node1 chf]$ rman target / 

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jan 14 18:30:54 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CHF (DBID=3444205684)

RMAN> backup check logical validate datafile 1;

Starting backup at 2012-01-14 18:31:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=223 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/chf/system01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    FAILED 0              21278        172802          16682540  
  File Name: /opt/oracle/oradata/chf/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       1              132247                  
  Other      0              3548            

validate found one or more corrupt blocks
See trace file /opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_2429.trc for details
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2               
Control File OK     0              882             
Finished backup at 2012-01-14 18:31:34


SQL>  select file#,block#,blocks from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS
---------- ---------- ----------
         1      95369          1

6.ANALYZE

SQL> ANALYZE TABLE sys.OBJ$ VALIDATE STRUCTURE;
ANALYZE TABLE sys.OBJ$ VALIDATE STRUCTURE
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

三.测试逻辑导出数据
1.exp导出单个表

[oracle@node1 chf]$ exp "'/ as sysdba'" tables=chf.t_undo file=/tmp/chf.dmp log=/tmp/chf.log

Export: Release 11.2.0.3.0 - Production on Sat Jan 14 17:41:35 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
EXP-00008: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'
EXP-00000: Export terminated unsuccessfully

2.expdp导出单个表

[oracle@node1 chf]$ expdp "'/ as sysdba'" dumpfile=xifenfei.dmp  tables=chf.t_odu

Export: Release 11.2.0.3.0 - Production on Sat Jan 14 17:55:09 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" dumpfile=xifenfei.dmp tables=chf.t_odu 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [TABLE:"CHF"."T_ODU"] 
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9001

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x7a8608a8     20462  package body SYS.KUPW$WORKER
0x7a8608a8      9028  package body SYS.KUPW$WORKER
0x7a8608a8     10935  package body SYS.KUPW$WORKER
0x7a8608a8      2728  package body SYS.KUPW$WORKER
0x7a8608a8      9697  package body SYS.KUPW$WORKER
0x7a8608a8      1775  package body SYS.KUPW$WORKER
0x7a864160         2  anonymous block

Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [TABLE:"CHF"."T_ODU"] 
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9001

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x7a8608a8     20462  package body SYS.KUPW$WORKER
0x7a8608a8      9028  package body SYS.KUPW$WORKER
0x7a8608a8     10935  package body SYS.KUPW$WORKER
0x7a8608a8      2728  package body SYS.KUPW$WORKER
0x7a8608a8      9697  package body SYS.KUPW$WORKER
0x7a8608a8      1775  package body SYS.KUPW$WORKER
0x7a864160         2  anonymous block

Job "SYS"."SYS_EXPORT_TABLE_01" stopped due to fatal error at 17:55:24

3.exp表空间传输

[oracle@node1 chf]$ exp userid=\'/ as sysdba\' tablespaces=users file=/tmp/users.dmp transport_tablespace=y

Export: Release 11.2.0.3.0 - Production on Sat Jan 14 18:00:21 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
EXP-00008: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'
EXP-00000: Export terminated unsuccessfully

4.expdp表空间传输

[oracle@node1 chf]$ expdp userid=\'/ as sysdba\' dumpfile=xienfei.dmp  transport_tablespaces=users

Export: Release 11.2.0.3.0 - Production on Sat Jan 14 18:12:12 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  userid="/******** AS SYSDBA" dumpfile=xienfei.dmp transport_tablespaces=users 
ORA-39123: Data Pump transportable tablespace job aborted
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 18:12:14

四.总结obj$表坏块
1.在执行导出过程中,应该会去查询obj$表,而该表因出现坏块,不能被正常方法,导致逻辑备份异常终止。
2.如果你真的出现了obj$坏块,而你没有备份,那么恭喜你,悲剧的人生开始了。该对象出现问题,逻辑备份都不能工作,就算你有心重建库也不会给你这个机会,可能你不得不借助odu/dul之类的工具去解决问题。再次提醒各位,备份重于一切,安全重于泰山。

此条目发表在 ORA-xxxxx, 逻辑备份/恢复 分类目录,贴了 标签。将固定链接加入收藏夹。

obj$坏块exp/expdp导出不能正常执行》有 2 条评论

  1. 惜 分飞 说:

    bbed模拟坏块

    SQL>  select blocks,block_id,file_id from dba_extents where segment_name='OBJ$'
      2  order by block_id;
    
        BLOCKS   BLOCK_ID    FILE_ID
    ---------- ---------- ----------
             8      94505          1
             8      94585          1
             8      95001          1
             8      95025          1
             8      95057          1
             8      95073          1
             8      95097          1
             8      95121          1
             8      95137          1
             8      95185          1
             8      95193          1
    
        BLOCKS   BLOCK_ID    FILE_ID
    ---------- ---------- ----------
             8      95225          1
             8      95233          1
             8      95257          1
             8      95297          1
             8      95321          1
           128      95369          1
           128      95881          1
           128      96521          1
           128      96905          1
           128      97417          1
           128      97673          1
    
        BLOCKS   BLOCK_ID    FILE_ID
    ---------- ---------- ----------
           128      98057          1
    
    23 rows selected.
    
    
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> exit
    
    
    [oracle@node1 chf]$ bbed
    Password: 
    
    BBED: Release 2.0.0.0.0 - Limited Production on Sun Jan 15 23:16:46 2012
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    ************* !!! For Oracle Internal Use only !!! ***************
    
    BBED> set filename 'system01.dbf'
            FILENAME        ./system01.dbf
    
    
    BBED> set block 95370
            BLOCK#          95370
    
    
    BBED> set mode edit
            MODE            Edit
    
    BBED> set blocksize 8192
            BLOCKSIZE       8192
    
    
    BBED> p tailchk
    ub4 tailchk                                 @8188     0x02990601
    
    
    BBED> show offset;
            OFFSET          8188
    
    
    BBED> d
     File: ././././system01.dbf (0)
     Block: 95370            Offsets: 8188 to 8191           Dba:0x00000000
    ------------------------------------------------------------------------
     01069902 
    
     <32 bytes per line>
    
    BBED> m /c 11112222
     File: ././././system01.dbf (0)
     Block: 95370            Offsets: 8188 to 8191           Dba:0x00000000
    ------------------------------------------------------------------------
     31313131 
    
     <32 bytes per line>
    
    BBED> sum
    Check value for File 0, Block 95370:
    current = 0x05f8, required = 0x0160
    
    BBED> verify
    DBVERIFY - Verification starting
    FILE = ././././system01.dbf
    BLOCK = 95369
    
    Block 95369 is corrupt
    Corrupt block relative dba: 0x00417489 (file 0, block 95369)
    Fractured block found during verification
    Data in bad block:
     type: 6 format: 2 rdba: 0x00417489
     last change scn: 0x0000.00db0299 seq: 0x1 flg: 0x06
     spare1: 0x0 spare2: 0x0 spare3: 0x0
     consistency value in tail: 0x31313131
     check value in block header: 0x5f8
     computed block checksum: 0x498
    
    
    DBVERIFY - Verification complete
    
    Total Blocks Examined         : 1
    Total Blocks Processed (Data) : 0
    Total Blocks Failing   (Data) : 0
    Total Blocks Processed (Index): 0
    Total Blocks Failing   (Index): 0
    Total Blocks Empty            : 0
    Total Blocks Marked Corrupt   : 1
    Total Blocks Influx           : 2
    Message 531 not found;  product=RDBMS; facility=BBED
    
    BBED> exit
    
    [oracle@node1 chf]$ dbv file=system01.dbf
    
    DBVERIFY: Release 11.2.0.3.0 - Production on Sun Jan 15 23:28:54 2012
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    DBVERIFY - Verification starting : FILE = /opt/oracle/oradata/chf/system01.dbf
    Page 95369 is influx - most likely media corrupt
    Corrupt block relative dba: 0x00417489 (file 1, block 95369)
    Fractured block found during dbv: 
    Data in bad block:
     type: 6 format: 2 rdba: 0x00417489
     last change scn: 0x0000.00db0299 seq: 0x1 flg: 0x06
     spare1: 0x0 spare2: 0x0 spare3: 0x0
     consistency value in tail: 0x31313131
     check value in block header: 0x5f8
     computed block checksum: 0x498
    
    
    
    DBVERIFY - Verification complete
    
    Total Pages Examined         : 172800
    Total Pages Processed (Data) : 132214
    Total Pages Failing   (Data) : 0
    Total Pages Processed (Index): 15768
    Total Pages Failing   (Index): 0
    Total Pages Processed (Other): 3542
    Total Pages Processed (Seg)  : 1
    Total Pages Failing   (Seg)  : 0
    Total Pages Empty            : 21275
    Total Pages Marked Corrupt   : 1
    Total Pages Influx           : 1
    Total Pages Encrypted        : 0
    Highest block SCN            : 16959419 (0.16959419)
    
  2. 惜 分飞 说:

    使用更多参数证明job$出现坏块exp不能工作

    [oracle@node1 chf]$ exp "'/ as sysdba'" tables=chf.t_undo file=/tmp/chf.dmp log=/tmp/chf.log INDEXES =n \
    > COMPRESS =n CONSISTENT =n GRANTS =n STATISTICS =none TRIGGERS =n CONSTRAINTS =n
    
    Export: Release 11.2.0.3.0 - Production on Sat Jan 14 19:04:19 2012
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Oracle Label Security, OLAP, Data Mining,
    Oracle Database Vault and Real Application Testing option
    EXP-00008: ORACLE error 1578 encountered
    ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
    ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'
    EXP-00000: Export terminated unsuccessfully