数据坏块：ORA-01578、ORA-01110

数据坏块：ORA-01578、ORA-01110

一、问题现象

公司停电，服务器重启后，审批单据报错:sql:UPDATE sm msg user SET isread = "Y' WHERE smmsg user.isdelete = N' AND sm msg user.destinatior= inbox' AND sm msg user,pk message IN (1001S3100000042U4ZU') AND sm_msg _user.isreject='N' or smmsg user.isreject is null ORA-01578: ORACLE 数据块损坏(文件号 12,块号 1120751)ORA-01110: 数据文件 12:D:XAPPVADMINISTRATORWORADATAVORCL\\NNC_DATA01A.DBF

二、分析思路

Dumping current patch information

===========================================================

No patches have been applied

===========================================================

2023-05-04T12:52:06.187757+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_13044.trc  (incident=493073):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_493073\orcl_ora_13044_i493073.trc

2023-05-04T12:52:08.655893+08:00

*****************************************************************

An internal routine has requested a dump of selected redo.

This usually happens following a specific internal error, when

analysis of the redo logs will help Oracle Support with the

diagnosis.

It is recommended that you retain all the redo logs generated (by

all the instances) during the past 12 hours, in case additional

redo dumps are required to help with the diagnosis.

*****************************************************************

2023-05-04T12:52:12.248750+08:00

Checker run found 1 new persistent data failures

2023-05-04T12:53:02.059195+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_8796.trc  (incident=493089):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_493089\orcl_ora_8796_i493089.trc

2023-05-04T12:53:07.729660+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_8796.trc  (incident=493090):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_493090\orcl_ora_8796_i493090.trc

2023-05-04T12:53:10.072827+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_12544.trc  (incident=493081):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_493081\orcl_ora_12544_i493081.trc

2023-05-04T12:54:30.863012+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_11336.trc  (incident=493145):

ORA-01578: ORACLE 数据块损坏 (文件号 5, 块号 3479347)

ORA-01110: 数据文件 5: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01.DBF'

Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_493145\orcl_ora_11336_i493145.trc

2023-05-04T12:54:30.987981+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_8796.trc  (incident=493091):

ORA-01578: ORACLE 数据块损坏 (文件号 5, 块号 3479347)

ORA-01110: 数据文件 5: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01.DBF'

2023-05-04T12:54:33.540707+08:00

Checker run found 1 new persistent data failures

2023-05-04T12:54:34.477974+08:00

*****************************************************************

An internal routine has requested a dump of selected redo.

This usually happens following a specific internal error, when

analysis of the redo logs will help Oracle Support with the

diagnosis.

It is recommended that you retain all the redo logs generated (by

all the instances) during the past 12 hours, in case additional

redo dumps are required to help with the diagnosis.

*****************************************************************

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_13044.trc  (incident=493074):

ORA-00600: 内部错误代码, 参数: [kdliReadPos], [26920], [24468493], [0], [0], [], [], [], [], [], [], []

Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_493074\orcl_ora_13044_i493074.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

*****************************************************************

An internal routine has requested a dump of selected redo.

This usually happens following a specific internal error, when

analysis of the redo logs will help Oracle Support with the

diagnosis.

It is recommended that you retain all the redo logs generated (by

all the instances) during the past 12 hours, in case additional

redo dumps are required to help with the diagnosis.

*****************************************************************

2023-05-04T12:54:39.079514+08:00

Dumping diagnostic data in directory=[cdmp_20230504125439], requested by (instance=1, osid=13044), summary=[incident=493074].

2023-05-04T12:54:43.259069+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_5636.trc  (incident=493097):

ORA-01578: ORACLE 数据块损坏 (文件号 5, 块号 3479347)

ORA-01110: 数据文件 5: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01.DBF'

2023-05-04T12:55:23.237366+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_8796.trc  (incident=493092):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T12:56:51.549653+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_2164.trc  (incident=493137):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T12:57:17.609400+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_12544.trc  (incident=493082):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T12:57:23.259059+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_2164.trc  (incident=493138):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T12:57:44.364956+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_2164.trc  (incident=493139):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T12:58:05.995737+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_11472.trc  (incident=493129):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T12:58:06.276917+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_11336.trc  (incident=493146):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T12:58:34.332101+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_4424.trc  (incident=493121):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T12:58:44.059946+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_9228.trc  (incident=493113):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T12:58:47.963776+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_1952.trc  (incident=493105):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T12:59:14.091241+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_1952.trc  (incident=493106):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:01:57.750046+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_5636.trc  (incident=493098):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:02:05.184323+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_2164.trc  (incident=493140):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:02:34.432232+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_11336.trc  (incident=493147):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:02:57.076758+08:00

Thread 1 advanced to log sequence 21511 (LGWR switch)

  Current log# 1 seq# 21511 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG

2023-05-04T13:07:06.787953+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_8796.trc  (incident=493093):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:07:14.206733+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_9688.trc  (incident=493945):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:08:40.709288+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_12544.trc  (incident=493083):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:09:19.034050+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_8796.trc  (incident=493094):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:10:01.010721+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_11336.trc  (incident=493148):

ORA-01578: ORACLE 数据块损坏 (文件号 5, 块号 3479347)

ORA-01110: 数据文件 5: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01.DBF'

2023-05-04T13:10:01.303320+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_1952.trc  (incident=493107):

ORA-01578: ORACLE 数据块损坏 (文件号 5, 块号 3479347)

ORA-01110: 数据文件 5: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01.DBF'

2023-05-04T13:10:34.924690+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_8796.trc  (incident=493095):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:11:00.567224+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_1952.trc  (incident=493108):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:12:24.623039+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_2164.trc  (incident=493141):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:12:33.713652+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_11472.trc  (incident=493130):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:12:50.330267+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_5636.trc  (incident=493099):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:13:01.075802+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_12544.trc  (incident=493084):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:13:10.309446+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_8796.trc  (incident=493096):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:13:46.352520+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_11336.trc  (incident=493149):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:13:47.862685+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_4424.trc  (incident=493122):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:13:52.666816+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_9228.trc  (incident=493114):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:14:36.042139+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_9228.trc  (incident=493115):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:14:42.813535+08:00

Thread 1 advanced to log sequence 21512 (LGWR switch)

  Current log# 2 seq# 21512 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG

2023-05-04T13:15:53.497918+08:00

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_2164.trc  (incident=493142):

ORA-01578: ORACLE 数据块损坏 (文件号 12, 块号 1120751)

ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01A.DBF'

2023-05-04T13:53:27.855857+08:00

三、解决方案

SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = 12 and 1120751 between block_id AND block_id + blocks – 1

SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = 5 and 3479347 between block_id AND block_id + blocks – 1

找到之前的备份

impdp LSTH/lsth dumpfile=BACK20230504.DMP logfile=BACK20230504impdp.log directory=EXPDP_DIR table_exists_action=truncate tables=(SM_MSG_USER)