报错分析
有台数据库有些查询报错:
ORA-01578: ORACLE 数据块损坏 (文件号 15, 块号 840339)
ORA-01110: 数据文件 4: 'E:\APP\ADMINISTRATOR\ORADATA\mydbf.DBF'
查看该报错说明
racnode1->oerr ora 01578
01578, 00000, “ORACLE data block corrupted (file # %s, block # %s)”
// *Cause: The data block indicated was corrupt. This was a physical
// corruption, also called a media corruption. The cause is unknown
// but is most likely external to the database. If ORA-26040 is also
// signaled, the corruption is due to NOLOGGING or UNRECOVERABLE
// operations.
// *Action: The general method to repair a media corrupt block is to restore
// a backup and recover the backup. For databases in ARCHIVELOG
// mode, use block media recovery or media recovery. In some situations,
// you can also drop the segment and re-create it. For example, you can
// drop an index and re-create the index.
说一般是物理文件的损坏
有备份就restore+recover
没备份就drop+re-create
解决办法
没有备份,那就drop+re-create吧。
先检测下这个数据文件
E:\app\Administrator\product\11.2.0\dbhome_1\BIN>dbv file=E:\APP\ADMINISTRATOR\ORADATA\mydbf.DBF
DBVERIFY - 验证完成
检查的页总数: 614400
处理的页总数 (数据): 507565
失败的页总数 (数据): 0
处理的页总数 (索引): 100837
失败的页总数 (索引): 1
处理的页总数 (其他): 5554
处理的总页数 (段) : 0
失败的总页数 (段) : 0
空的页总数: 1
标记为损坏的总页数: 443
流入的页总数: 443
加密的总页数 : 0
最高块 SCN : 1195957692 (1984.1195957692)
可怕,有443处损坏。
这个数据文件是某个用户专属的,该用户可以重新部署。所以决定
重建用户+表空间,expdp+impdp
expdp system/xxxx directory=DATA_PUMP_DIR dumpfile=cxxx.dmp schemas=xxx CONTENT=METADATA_ONLY
drop user xxx cascade ;
drop tablespace xxxx including contents and datafiles CASCADE CONSTRAINTS;
create tablespace xxxx ....
IMP system/xxxx directory = DATA_PUMP_DIR dumpfile=cxxx.dmp
新的报错
重建完成后,又发生了新的报错:
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 3161)
ORA-01110: 数据文件 4:> ‘E:\APP\ADMINISTRATOR\ORADATA\SYSTEM01.DBF’
这次是system文件损坏了,查看文件号 1, 块号 3161上是什么对象
select tablespace_name,segment_type,owner,segment_name
from dba_extents
where file_id=1 and 4197465 between block_id AND block_id + blocks - 1;
是SYS.I_MON_MODS$_OBJ,一个index。重建!
获取ddl
SELECT
dbms_metadata.get_ddl(object_type => 'INDEX',name => 'I_MON_MODS$_OBJ',schema => 'SYS')
FROM DUAL
drop+re-create
drop index "SYS"."I_MON_MODS$_OBJ" ;
CREATE UNIQUE INDEX "SYS"."I_MON_MODS$_OBJ" ON "SYS"."MON_MODS$" ("OBJ#")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
问题解决,数据库恢复正常。