Oracle 11g 数据块问题处理记录
2022/6/14 2:21:19
本文主要是介绍Oracle 11g 数据块问题处理记录,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
环境:oracle rac 11g,node1和node2,java程序直接连的node1
现象:业务系统流程处理中,提交时失败,查看后台日志报错:
ORA-01115: 从文件 读取块时出现 IO 错误 (块 # ) ORA-01110: 数据文件 6: '+DATA/orcl/cms.dbf' ORA-15081: 无法将 I/O 操作提交到磁盘 ORA-27072: 文件 I/O 错误 Linux-x86_64 Error: 5: Input/output error Additional information: 4 Additional information: 43291184 Additional information: -1 01115. 00000 - "IO error reading block from file %s (block # %s)" *Cause: Device on which the file resides is probably offline *Action: Restore access to the device
查看磁盘,发现根磁盘已100%使用,于是清理一部分根磁盘没用的文件,问题依旧。
DBV检查数据文件:
dbv file='+DATA/orcl/cms.dbf' userid=grid/grid
直接报IO错误,基本上磁盘硬件出现了问题
通过java报错定位到对应是业务表处理报错,通过select count(*) from 流程表 就会如上的错误,刚开始怀疑是磁盘满导致的,最后发现是硬盘故障,磁盘阵列有几块盘坏了。
工程师换完硬盘后并对硬盘逻辑坏块做了修复,dbv后能列出具体坏块了:
DBVERIFY - Verification complete Total Pages Examined : 4187648 Total Pages Processed (Data) : 1665462 Total Pages Failing (Data) : 1 Total Pages Processed (Index): 1489155 Total Pages Failing (Index): 0 Total Pages Processed (Other): 938505 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 94487 Total Pages Marked Corrupt : 39 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 0 (0.0)
通过rman统计哪些对象有坏块
RMAN>backup check logical validate datafile
最后通过sql查询坏块对应的对象列表
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file# , greatest(e.block_id, c.block#) corr_start_block# , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block# , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) - greatest(e.block_id, c.block#) + 1 blocks_corrupted , null description FROM dba_extents e, v$database_block_corruption c WHERE e.file_id = c.file# AND e.block_id <= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 >= c.block# UNION SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file# , header_block corr_start_block# , header_block corr_end_block# , 1 blocks_corrupted , 'Segment Header' description FROM dba_segments s, v$database_block_corruption c WHERE s.header_file = c.file# AND s.header_block between c.block# and c.block# + c.blocks - 1 UNION SELECT null owner, null segment_type, null segment_name, null partition_name, c.file# , greatest(f.block_id, c.block#) corr_start_block# , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block# , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) - greatest(f.block_id, c.block#) + 1 blocks_corrupted , 'Free Block' description FROM dba_free_space f, v$database_block_corruption c WHERE f.file_id = c.file# AND f.block_id <= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 >= c.block# ORDER BY file#, corr_start_block#;
输出结果为坏块的表、索引等内容
解决办法:
1.索引直接重建
2.业务表可以把可用的数据导出,然后重新建表还原数据;有备份直接从备份恢复
这篇关于Oracle 11g 数据块问题处理记录的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-15PingCAP 黄东旭参与 CCF 秀湖会议,共探开源教育未来
- 2024-05-13PingCAP 戴涛:构建面向未来的金融核心系统
- 2024-05-09flutter3.x_macos桌面os实战
- 2024-05-09Rust中的并发性:Sync 和 Send Traits
- 2024-05-08使用Ollama和OpenWebUI在CPU上玩转Meta Llama3-8B
- 2024-05-08完工标准(DoD)与验收条件(AC)究竟有什么不同?
- 2024-05-084万 star 的 NocoDB 在 sealos 上一键起,轻松把数据库编程智能表格
- 2024-05-08Mac 版Stable Diffusion WebUI的安装
- 2024-05-08解锁CodeGeeX智能问答中3项独有的隐藏技能
- 2024-05-08RAG算法优化+新增代码仓库支持,CodeGeeX的@repo功能效果提升