ORA-00756 ORA-10567故障处理---惜分飞

avatar
作者
猴君
阅读量:0

数据库异常断电之后,recover 报ORA-00756 ORA-10567等错

SQL> recover database;

ORA-00756: 恢复操作检测到数据块写入丢失

ORA-10567: Redo isinconsistent withdata block (file# 1,block# 113855,file offset is932700160 bytes)

ORA-10564: tablespace SYSTEM

ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'

ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 67

alert日志报大量block逻辑错误

2024-07-16T13:16:31.050599+08:00

Slave exiting with ORA-10562 exception

2024-07-16T13:16:31.050599+08:00

Errors infileC:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr01_43460.trc:

ORA-10562: Error occurred whileapplying redo to data block (file# 3, block# 107952)

ORA-10564: tablespace SYSAUX

ORA-01110: 数据文件 3: 'H:\BAIDUNETDISK\XIFENFEI\SYSAUX01.DBF'

ORA-10561: block type'TRANSACTION MANAGED DATA BLOCK', data object# 8689

ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], , [], [], [], []

2024-07-16T13:16:31.088497+08:00

Slave exiting with ORA-10562 exception

2024-07-16T13:16:31.088497+08:00

Errors infileC:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr0e_10596.trc:

ORA-10562: Error occurred whileapplying redo to data block (file# 1, block# 755)

ORA-10564: tablespace SYSTEM

ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'

ORA-10561: block type'TRANSACTION MANAGED DATA BLOCK', data object# 64

ORA-00600: 内部错误代码, 参数: [kdolkr-2], [2], [155], [26], , []

2024-07-16T13:16:31.106449+08:00

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support forerror and packaging details.

2024-07-16T13:16:31.130385+08:00

Slave exiting with ORA-10562 exception

2024-07-16T13:16:31.130385+08:00

Errors infileC:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr0i_40632.trc:

ORA-10562: Error occurred whileapplying redo to data block (file# 1, block# 110095)

ORA-10564: tablespace SYSTEM

ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'

ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK', data object# 40

ORA-00600: 内部错误代码, 参数: [kdxdBlkCheckError], [1], [4304399], [6401], , []

2024-07-16T13:16:31.157313+08:00

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support forerror and packaging details.

2024-07-16T13:16:31.181249+08:00

Slave exiting with ORA-10562 exception

2024-07-16T13:16:31.182247+08:00

Errors infileC:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr09_15592.trc:

ORA-10562: Error occurred whileapplying redo to data block (file# 1, block# 5490)

ORA-10564: tablespace SYSTEM

ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'

ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK', data object# 822

ORA-00600: 内部错误代码, 参数: [kdxdBlkCheckError], [1], [4199794], [6401], , []

2024-07-16T13:16:31.242087+08:00

Slave exiting with ORA-10562 exception

2024-07-16T13:16:31.242087+08:00

Errors infileC:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr05_28908.trc:

ORA-10562: Error occurred whileapplying redo to data block (file# 3, block# 3935)

ORA-10564: tablespace SYSAUX

ORA-01110: 数据文件 3: 'H:\BAIDUNETDISK\XIFENFEI\SYSAUX01.DBF'

ORA-10561: block type'TRANSACTION MANAGED INDEX BLOCK', data object# 8694

ORA-00600: 内部错误代码, 参数: [6102], [27], [2], , [], []

2024-07-16T13:16:31.265025+08:00

Slave exiting with ORA-10562 exception

2024-07-16T13:16:31.266023+08:00

Errors infileC:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr0d_24400.trc:

ORA-10562: Error occurred whileapplying redo to data block (file# 1, block# 51243)

ORA-10564: tablespace SYSTEM

ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'

ORA-10561: block type'TRANSACTION MANAGED DATA BLOCK', data object# 8

ORA-00600: 内部错误代码, 参数: [ktbair2: illegal  inheritance], , [], [], [], []

2024-07-16T13:16:31.272007+08:00

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support forerror and packaging details.

2024-07-16T13:16:31.293948+08:00

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support forerror and packaging details.

2024-07-16T13:16:31.294946+08:00

Slave exiting with ORA-10562 exception

2024-07-16T13:16:31.294946+08:00

Errors infileC:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr02_24168.trc:

ORA-10562: Error occurred whileapplying redo to data block (file# 1, block# 114402)

ORA-10564: tablespace SYSTEM

ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'

ORA-10561: block type'TRANSACTION MANAGED DATA BLOCK', data object# 64

ORA-00600: 内部错误代码, 参数: [kdbBlkCheckError], [1], [4308706], [6124], , []

2024-07-16T13:16:31.307911+08:00

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support forerror and packaging details.

2024-07-16T13:16:31.315890+08:00

Slave exiting with ORA-10562 exception

2024-07-16T13:16:31.316916+08:00

Errors infileC:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr0h_37312.trc:

ORA-10562: Error occurred whileapplying redo to data block (file# 1, block# 116359)

ORA-10564: tablespace SYSTEM

ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'

ORA-10561: block type'TRANSACTION MANAGED DATA BLOCK', data object# 64

ORA-00600: 内部错误代码, 参数: [kdbBlkCheckError], [1], [4310663], [6124], , []

2024-07-16T13:16:31.329881+08:00

Slave exiting with ORA-10562 exception

2024-07-16T13:16:31.329881+08:00

Errors infileC:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr0g_38356.trc:

ORA-10562: Error occurred whileapplying redo to data block (file# 1, block# 115210)

ORA-10564: tablespace SYSTEM

ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'

ORA-10561: block type'TRANSACTION MANAGED DATA BLOCK', data object# 64

ORA-00600: 内部错误代码, 参数: [kdbBlkCheckError], [1], [4309514], [6124], , []

2024-07-16T13:16:49.657116+08:00

Corrupt block relative dba: 0x01000c1d (file4, block 3101)

Fractured block found during in-flux buffer recovery

Data inbad block:

 type: 2 format: 2 rdba: 0x01000c1d

 last change scn: 0x0000.0000.00ddfe50 seq: 0x1 flg: 0x04

 spare3: 0x0

 consistency value intail: 0xcaae0205

 check value inblock header: 0x2ebc

 computed block checksum: 0xee94

Reread (file4, block 3101) found same corrupt data (no logical check)

2024-07-16T13:16:49.893484+08:00

Errors infileC:\APP\XFF\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_pr00_23116.trc:

ORA-00283: 恢复会话因错误而取消

ORA-00448: 后台进程正常结束

dbv检查system文件报有坏块

C:\Users\XFF>dbv file=H:\BaiduNetdisk\XIFENFEI\system01.dbf

DBVERIFY: Release 19.0.0.0.0 - Production on 星期二 7月 16 13:17:32 2024

Copyright (c) 1982, 2019, Oracle and/orits affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF

页 11290 流入 - 很可能是介质损坏

Corrupt block relative dba: 0x00402c1a (file1, block 11290)

Fractured block found during dbv:

Data inbad block:

 type: 6 format: 2 rdba: 0x00402c1a

 last change scn: 0x0000.0000.00dec9ca seq: 0x1 flg: 0x06

 spare3: 0x0

 consistency value intail: 0x56e00601

 check value inblock header: 0xaf3c

 computed block checksum: 0xdc2d

页 50842 流入 - 很可能是介质损坏

Corrupt block relative dba: 0x0040c69a (file1, block 50842)

Fractured block found during dbv:

Data inbad block:

 type: 6 format: 2 rdba: 0x0040c69a

 last change scn: 0x0000.0000.00de200e seq: 0x1 flg: 0x06

 spare3: 0x0

 consistency value intail: 0x799a0601

 check value inblock header: 0x68ef

 computed block checksum: 0x5994

页 113852 流入 - 很可能是介质损坏

Corrupt block relative dba: 0x0041bcbc (file1, block 113852)

Fractured block found during dbv:

Data inbad block:

 type: 6 format: 2 rdba: 0x0041bcbc

 last change scn: 0x0000.0000.00df78b9 seq: 0x1 flg: 0x06

 spare3: 0x0

 consistency value intail: 0x1f1c0601

 check value inblock header: 0xf5fc

 computed block checksum: 0x46af

DBVERIFY - 验证完成

检查的页总数: 119040

处理的页总数 (数据): 82822

失败的页总数 (数据): 0

处理的页总数 (索引): 14268

失败的页总数 (索引): 0

处理的页总数 (其他): 4570

处理的总页数 (段)  : 1

失败的总页数 (段)  : 0

空的页总数: 17377

标记为损坏的总页数: 3

流入的页总数: 3

加密的总页数        : 0

最高块 SCN            : 14645988 (0.14645988)

由于无法直接应用日志打开库,尝试屏蔽一致性,强制打开库,报ORA-600 kcbzib_kcrsds_1错误

SQL> startup mount pfile='d:/pfile.txt';

ORACLE 例程已经启动。

Total System GlobalArea 5167381760 bytes

Fixed Size                  9039104 bytes

Variable Size             989855744 bytes

DatabaseBuffers         4160749568 bytes

Redo Buffers                7737344 bytes

数据库装载完毕。

SQL>

SQL>

SQL>

SQL> recover databaseuntil cancel;

ORA-00279: 更改 14599839 (在  生成) 对于线程 1 是必需的

指定日志: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: 警告: RECOVER 成功但 OPENRESETLOGS 将出现如下错误

ORA-01194: 文件 1 需要更多的恢复来保持一致性

ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'

ORA-01112: 未启动介质恢复

SQL> alterdatabaseopenresetlogs;

alterdatabaseopenresetlogs

*

第 1 行出现错误:

ORA-00603: ORACLE server session terminated byfatal error

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00704: bootstrap process failure

ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],

进程 ID: 23392

会话 ID: 618 序列号: 30029

使用Patch_SCN工具修改scn(修改oracle scn小工具(patch scn)),然后打开库,报ORA-600 6711
 

patch_scn-kcbzib_kcrsds_1


SQL> alterdatabaseopen;

alterdatabaseopen

*

第 1 行出现错误:

ORA-00603: ORACLE server session terminated byfatal error

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00600: internal error code, arguments: [6711], [4310861], [1], [4309052],

[0],

进程 ID: 40100

会话 ID: 618 序列号: 10845

这个故障最近刚刚处理过一次,见:数据库启动报ORA-600 6711故障分析处理,open数据库之后,尝试导出数据,报各种错误
ORA-600 6711报错

C:\Users\XFF>exp "'/ as sysdba'"owner=XIFENFEI file=e:/XIFENFEI.dmp log=e:/XIFENFEI.log 

Export: Release 19.0.0.0.0 - Production on 星期二 7月 16 13:33:11 2024

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/orits affiliates.  All rights reserved.

连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的用户...

. 正在导出 pre-schema 过程对象和操作

EXP-00008: 遇到 ORACLE 错误 600

ORA-00600: 内部错误代码, 参数: [6711], [4310861], [1], [4309052], [0],

EXP-00083: 调用 SYS.DBMS_AW_EXP.schema_info_exp 时出现前一问题

. 正在导出用户 XIFENFEI 的外部函数库名

. 导出 PUBLIC 类型同义词

. 正在导出专用类型同义词

EXP-00008: 遇到 ORACLE 错误 1578

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

ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'

EXP-00000: 导出终止失败

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 星期二 7月 16 13:33:23 2024

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

连接到:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> selectobject_name,object_type from dba_objects where object_id=64;

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_TYPE

-----------------------

C_OBJ#_INTCOL#

CLUSTER

ORA-01578报错

C:\Users\XFF>exp "'/ as sysdba'"owner=XIFENFEI file=e:/XIFENFEI.dmp log=e:/XIFENFEI.log

Export: Release 19.0.0.0.0 - Production on 星期二 7月 16 13:34:07 2024

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/orits affiliates.  All rights reserved.

连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的用户...

. 正在导出 pre-schema 过程对象和操作

. 正在导出用户 XIFENFEI 的外部函数库名

. 导出 PUBLIC 类型同义词

. 正在导出专用类型同义词

EXP-00008: 遇到 ORACLE 错误 1578

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

ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'

EXP-00000: 导出终止失败

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 星期二 7月 16 13:34:21 2024

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

连接到:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME

  2    FROM DBA_EXTENTS A

  3   WHERE FILE_ID = &FILE_ID

  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

输入 file_id 的值:  1

原值    3:  WHERE FILE_ID = &FILE_ID

新值    3:  WHERE FILE_ID = 1

输入 block_id 的值:  11290

原值    4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

新值    4:    AND 11290 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER

--------------------------------------------------------------------------------

SEGMENT_NAME

--------------------------------------------------------------------------------

SEGMENT_TYPE       TABLESPACE_NAME

------------------ ------------------------------

PARTITION_NAME

--------------------------------------------------------------------------------

SYS

I_OBJ2

INDEX              SYSTEM

SQL> create table t1 as select* from dba_objects;

create table t1 as select* from dba_objects

                                 *

第 1 行出现错误:

ORA-00604: 递归 SQL 级别 1 出现错误

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

ORA-01110: 数据文件 1: 'H:\BAIDUNETDISK\XIFENFEI\SYSTEM01.DBF'

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME

  2    FROM DBA_EXTENTS A

  3   WHERE FILE_ID = &FILE_ID

  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

输入 file_id 的值:  1

原值    3:  WHERE FILE_ID = &FILE_ID

新值    3:  WHERE FILE_ID = 1

输入 block_id 的值:  50842

原值    4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

新值    4:    AND 50842 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER

--------------------------------------------------------------------------------

SEGMENT_NAME

--------------------------------------------------------------------------------

SEGMENT_TYPE       TABLESPACE_NAME

------------------ ------------------------------

PARTITION_NAME

--------------------------------------------------------------------------------

SYS

I_COL3

INDEX              SYSTEM

通过上述分析,确认还有I_OBJ2和I_COL3这两个核心index异常,参考:bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决 进行处理,数据库可以正常导出

连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的用户...

. 正在导出 pre-schema 过程对象和操作

. 正在导出用户 XIFENFEI 的外部函数库名

. 导出 PUBLIC 类型同义词

. 正在导出专用类型同义词

. 正在导出用户 XIFENFEI 的对象类型定义

即将导出 XIFENFEI 的对象...

. 正在导出数据库链接

. 正在导出序号

. 正在导出簇定义

. 即将导出 XIFENFEI 的表通过常规路径...

. . 正在导出表                    标准诊断明细

.....

导出了                                                         50213 行

…………

. . 正在导出表                    诊断旁支分类

导出了                                                             1 行

. 正在导出同义词

. 正在导出视图

. 正在导出存储过程

. 正在导出运算符

. 正在导出引用完整性约束条件

. 正在导出触发器

. 正在导出索引类型

. 正在导出位图, 功能性索引和可扩展索引

. 正在导出后期表活动

. 正在导出实体化视图

. 正在导出快照日志

. 正在导出作业队列

. 正在导出刷新组和子组

. 正在导出维

. 正在导出 post-schema 过程对象和操作

. 正在导出统计信息

成功终止导出, 没有出现警告。

广告一刻

为您即时展示最新活动产品广告消息,让您随时掌握产品活动新动态!