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

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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之类的工具去解决问题。再次提醒各位，备份重于一切，安全重于泰山。

One thought on “obj$坏块exp/expdp导出不能正常执行”

  1. 使用更多参数证明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
    回复

  2. 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)
    回复

发表评论

邮箱地址不会被公开。 必填项已用*标注

11 + 19 =