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

最新推荐文章于 2025-09-29 15:49:47 发布

原创最新推荐文章于 2025-09-29 15:49:47 发布 · 3.2k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

在服务器经历停电并重启后，Oracle数据库出现ORA-01578错误，表示数据块损坏。错误涉及文件号12的NNC_DATA01A.DBF和文件号5的NNC_DATA01.DBF。系统生成了多个事故日志，并建议保留redo日志以帮助诊断。解决方案包括查询DBA_EXTENTS以识别损坏块对应的表空间和对象，以及使用备份进行数据导入以恢复受影响的数据。

数据坏块：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)