ORA-01578ORA-01110坏块解决方法
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了ORA-01578ORA-01110坏块解决方法，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3981字，纯文字阅读大概需要6分钟。
内容图文
一个案例,查看跟踪文件发现如下错误信息 d:\Oracle\product\10.2.0\admin\dbserver\udump\orcl_ora_5888.trc Corrupt block re
一个案例,查看跟踪文件发现如下错误信息
d:\Oracle\product\10.2.0\admin\dbserver\udump\orcl_ora_5888.trc
Corrupt block relative dba: 0x09848269 (file 38, block 295529)
Bad header found during buffer read
Data in bad block:
type: 1 format: 5 rdba: 0x30322d2d
last change scn: 0xfffe.fffefffe seq: 0xfe flg: 0xff
spare1: 0x0 spare2: 0x30 spare3: 0x0
consistency value in tail: 0x2d2d3533
check value in block header: 0xfffe
computed block checksum: 0x91f9
Reread of rdba: 0x09848269 (file 38, block 295529) found same corrupted data
Fri Dec 27 08:28:08 2013
Corrupt Block Found
TSN = 36, TSNAME = HGHIS
RFN = 38, BLK = 295529, RDBA = 159679081
OBJN = 119544, OBJD = 119544, OBJECT = EMR_MONITOR_RESULT, SUBOBJECT =
SEGMENT OWNER = HGHIS, SEGMENT TYPE = Table Segment
Fri Dec 27 08:54:18 2013
Hex dump of (file 38, block 295561) in trace file d:\oracle\product\10.2.0\admin\dbserver\udump\orcl_ora_6796.trc
Corrupt block relative dba: 0x09848289 (file 38, block 295561)
Bad header found during buffer read
Data in bad block:
type: 48 format: 0 rdba: 0x020cc100
last change scn: 0xb500.080cc100 seq: 0xa5 flg: 0xba
spare1: 0x34 spare2: 0x2 spare3: 0xb0b8
consistency value in tail: 0x395e3031
check value in block header: 0xcfcb
block checksum disabled
Reread of rdba: 0x09848289 (file 38, block 295561) found same corrupted data
Fri Dec 27 08:54:19 2013
Corrupt Block Found
TSN = 36, TSNAME = HGHIS
RFN = 38, BLK = 295561, RDBA = 159679113
OBJN = 119494, OBJD = 119494, OBJECT = SYS_LOB0000119493C00006$$, SUBOBJECT =
SEGMENT OWNER = HGHIS, SEGMENT TYPE = Lob Segment
Fri Dec 27 09:11:20 2013
Hex dump of (file 38, block 295563) in trace file d:\oracle\product\10.2.0\admin\dbserver\udump\orcl_ora_5584.trc
Corrupt block relative dba: 0x0984828b (file 38, block 295563)
Bad header found during buffer read
Data in bad block:
type: 71 format: 7 rdba: 0x064d0001
last change scn: 0x3038.43584400 seq: 0x31 flg: 0x07
spare1: 0x44 spare2: 0x4c spare3: 0x771
consistency value in tail: 0x000a0000
check value in block header: 0x7800
computed block checksum: 0xcbf8
Reread of rdba: 0x0984828b (file 38, block 295563) found same corrupted data
Fri Dec 27 09:11:21 2013
Corrupt Block Found
TSN = 36, TSNAME = HGHIS
RFN = 38, BLK = 295563, RDBA = 159679115
OBJN = 119494, OBJD = 119494, OBJECT = SYS_LOB0000119493C00006$$, SUBOBJECT =
SEGMENT OWNER = HGHIS, SEGMENT TYPE = Lob Segment
Fri Dec 27 09:27:59 2013
根据上述信息得知38号数据文件的295529、295561、295563为坏块，可以使用DBV工具或者RMAN来检查坏块信息
DBV FILE="d:\oradata\DATA.DBF" blocksize=8192
or
rman target /
backup validate check logical database;
select * from V$DATABASE_BLOCK_CORRUPTION ;
可以根据文件号和块号查出损坏的是对象,表还是LOB segment
select tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=38 and 295529 between block_id AND block_id + blocks - 1;
38是文件号,295529是block号
如果是对象，可以重建
alter index indexname rebuild
如果是表，可以使用10231事件忽略坏块，然后使用CTAS方式重建表最后rename table，别忘记rebuild index
alter session SET EVENTS '10231 trace name context forever,level 10';
create table tab_new as select * from tab;
rename tab to tab_bak;
rename tab_new to new;
alter index indexname rebuild;
alter session SET EVENTS '10231 trace name context off';
如果损坏的是LOB segment,先找出segment信息
select owner, segment_name, segment_type from dba_extents where file_id = 38 and 295563 between block_id and block_id + blocks - 1;
输出如下
owner=HGHIS
segment_name=SYS_LOB0000119493C00006$$
segment_type=LOBSEGMENT
找到表明和LOB字段
select table_name, column_name from dba_lobs where segment_name = 'SYS_LOB0000119493C00006$$' and owner = 'HGHIS';
输出如下
table_name = EMR_CASE
column_name = WORD
找到坏块的bad rowid,使用以下plsql脚本
create table bad_rows (row_id ROWID,oracle_error_code number);
内容总结
以上是互联网集市为您收集整理的ORA-01578ORA-01110坏块解决方法全部内容，希望文章能够帮你解决ORA-01578ORA-01110坏块解决方法所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。