联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
今天有个朋友的多个库同时出现了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之类的工具去解决问题。再次提醒各位，备份重于一切，安全重于泰山。
加我QQ
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)使用更多参数证明job$出现坏块exp不能工作