Oracle表无法expdp,案例:Oracle出现obj$坏块exp/expdp导出不能导出的解决办法ORA-01578 ORA-01110...

天萃荷净

在执行exp/expdp时不能导出，提示数据库数据文件中存在obj$坏块，出现ORA-01578 ORA-01110的解决办法

今天有个朋友的多个库同时出现了obj$表出现坏块，总计数据量在100T-200T之间，而且是非归档模式，幸好数据不是很重要，不然将是一个非常大的悲剧。

我通过试验模拟证明obj$表如果出现坏块，数据库的不能通过逻辑导出，然后建库。

一.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坏块对象

[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

三.验证oracle坏块方法

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

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle出现obj$坏块exp/expdp导出不能导出的解决办法ORA-01578 ORA-01110