我们用户连接到数据库执行存储过程时报坏块ORA-01578ORACLE data block corrupted错误。
从错误提示信息来看,确实该数据库遭遇到坏块,通过查询数据字典,如下图:
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
53 88510 1 0 FRACTURED
54 2048 1 0 CORRUPT
54 771072 512 0 CORRUPT
54 856239 23 0 CORRUPT
54 856262 1 0 FRACTURED
54 856263 85 0 CORRUPT
54 856352 137 0 CORRUPT
54 856496 80 0 CORRUPT
54 856064 172 0 CORRUPT
54 856492 3 0 CORRUPT
54 839168 334 0 CORRUPT
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
54 839504 6 0 CORRUPT
54 839511 1 0 FRACTURED
54 839512 6 0 CORRUPT
54 839520 22 0 CORRUPT
54 839543 1 0 FRACTURED
54 839544 56 0 CORRUPT
54 839600 1 0 FRACTURED
54 839601 79 0 CORRUPT
54 1112064 512 0 CORRUPT
53 2625308 3 0 CORRUPT
53 2625393 1 0 FRACTURED
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
53 2625394 6 0 CORRUPT
53 2625408 3 0 CORRUPT
24 rows selected.
我们看到53、54号文件共出现1000多个坏块,出现这么多坏块用户今天才发现,说明是突发的。
正在检查数据字典时,突然当前连接的这个实例无法连接了,检查数据库进程,发现没有pmon等进程。该主机上共有两个实例,而这两个实例的pmon进程都不存在了,说明应该不是数据库的问题,这时我检查了一下集群资源。
bjscwbdb01:/home/grid$crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
OFFLINE OFFLINE bjscwbdb01
ONLINE ONLINE bjscwbdb02
ora.FRA.dg
ONLINE ONLINE bjscwbdb01
ONLINE ONLINE bjscwbdb02
ora.GRID.dg
ONLINE ONLINE bjscwbdb01
ONLINE ONLINE bjscwbdb02
ora.LISTENER.lsnr
ONLINE ONLINE bjscwbdb01
ONLINE ONLINE bjscwbdb02
ora.asm
ONLINE ONLINE bjscwbdb01 Started
ONLINE ONLINE bjscwbdb02 Started
ora.gsd
OFFLINE OFFLINE bjscwbdb01
OFFLINE OFFLINE bjscwbdb02
ora.net1.network
ONLINE ONLINE bjscwbdb01
ONLINE ONLINE bjscwb