异常断电数据库恢复-从ORA-600 2131到ORA-08102: 未找到索引关键字, 对象号 39

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：异常断电数据库恢复-从ORA-600 2131到ORA-08102: 未找到索引关键字, 对象号 39

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

数据库启动报ORA-600 2131，以前遇到过类似问题:ORA-600 2131故障处理

SQL> alter database mount;
alter database mount
*
第 1 行出现错误:
ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], []

Tue Jun 04 14:12:18 2024
RECO started with pid=15, OS id=3244 
Tue Jun 04 14:12:18 2024
MMON started with pid=16, OS id=3256 
Tue Jun 04 14:12:18 2024
MMNL started with pid=17, OS id=3432 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = E:\app\Administrator
Tue Jun 04 14:12:22 2024
alter database mount exclusive
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\XFF\XFF\trace\XFF_ora_2536.trc  (incident=427583):
ORA-00600: ??????, ??: [2131], [9], [8], [], [], [], [], [], [], [], [], []
Tue Jun 04 14:12:28 2024
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORA-600 signalled during: alter database mount exclusive...

重建ctl，然后重试recover 数据库，报ORA-600 kdourp_inorder2和ORA-600 3020错误，这些错误本质都是由于redo信息和block信息不匹配导致

SQL> recover datafile 1;
ORA-00283: 恢复会话因错误而取消
ORA-10562: Error occurred while applying redo to data block (file# 1, block# 74805)
ORA-10564: tablespace SYSTEM
ORA-01110: 数据文件 1: 'E:\ORADATA\XFF\SYSTEM01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 8
ORA-00600: 内部错误代码, 参数: [kdourp_inorder2], [16], [3], [0], [108], [], [], [], [], [], [], []


SQL> recover datafile 7;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [7], [385], [29360513], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 7, block# 385, file offset is 3153920 bytes)
ORA-10564: tablespace UNDOTBS2
ORA-01110: 数据文件 7: 'E:\ORADATA\XFF\UNDOTBS2.DBF'
ORA-10560: block type 'KTU UNDO BLOCK'

通过屏蔽一致性,修改文件头scn,强制打开数据库

SQL> recover database until cancel;
ORA-00279: 更改 56782359 (在 06/04/2024 14:00:36 生成) 对于线程 1 是必需的
ORA-00289: 建议: E:\APP\ARCHIVELOG\ARC0000005415_1165094245.0001
ORA-00280: 更改 56782359 (用于线程 1) 在序列 #5415 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'E:\ORADATA\XFF\SYSTEM01.DBF'


ORA-01112: 未启动介质恢复


SQL> alter database open resetlogs;

数据库已更改。

尝试导出数据报ORA-08102,导致数据库无法正常导出

C:\Users\Administrator>expdp "'/ as sysdba'" full=y dumpfile=full_20240604_%U.dmp DIRECTORY=expdp_dir 
logfile=full_20240604.log parallel=2 EXCLUDE=STATISTICS,AUDIT

Export: Release 11.2.0.4.0 - Production on 星期二 6月 4 18:40:26 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: 作业不存在
ORA-31633: 无法创建主表 "SYS.SYS_EXPORT_FULL_05"
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: 在 "SYS.KUPV$FT", line 1038
ORA-08102: 未找到索引关键字, 对象号 39, 文件 1, 块 97540 (2)

obj 39 为OBJ$的I_OBJ4对象报ORA-08102

SQL> select owner,object_name,object_type from dba_objects where object_id=39
  2  /

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SYS                            I_OBJ4                         INDEX

该对象属于bootstrap$中核心对象，无法直接rebuild,参考下面文章处理,然后再尝试导出数据
分享I_OBJ4 ORA-8102故障恢复案例
使用bbed 修复I_OBJ4 index 报ORA-8102错误
bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决

C:\Users\Administrator>expdp "'/ as sysdba'" full=y dumpfile=full_20240604_%U.dmp DIRECTORY=expdp_dir 
logfile=full_20240604.log parallel=2 EXCLUDE=STATISTICS,AUDIT

Export: Release 11.2.0.4.0 - Production on 星期二 6月 4 18:43:47 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: 作业不存在
ORA-31637: 无法创建作业 SYS_EXPORT_FULL_01 (用户 SYS)
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: 在 "SYS.KUPV$FT_INT", line 798
ORA-39080: 无法为数据泵作业创建队列 "KUPC$C_1_20240604184348" 和 "KUPC$S_1_20240604184348"
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: 在 "SYS.KUPC$QUE_INT", line 1534
ORA-08102: 未找到索引关键字, 对象号 53, 文件 1, 块 97715 (2)

通过类似方法分析确认为CDEF$的I_CDEF1 index,处理方法和I_OBJ4一样,然后导出数据成功,导入到新库中,在这个迁移过程中遭遇Wrapped 加密的package body无效的问题,具体参见:数据泵迁移Wrapped PLSQL之后报PLS-00753

硬件故障数据库异常恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：硬件故障数据库异常恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

硬件故障数据库crash
有客户由于硬件故障导致数据库异常ORA-00345 ORA-00312 ORA-27070 OSD-04016

Tue Feb 05 16:58:26 2019
Thread 1 advanced to log sequence 17139 (LGWR switch)
  Current log# 12 seq# 17139 mem# 0: S:\ORADATA\ORCL\REDO12A.LOG
  Current log# 12 seq# 17139 mem# 1: S:\ORADATA\ORCL\REDO12B.LOG
Tue Feb 05 19:47:24 2019
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_lgwr_2420.trc:
ORA-00345: redo log write error block 152097 count 8
ORA-00312: online log 12 thread 1: 'S:\ORADATA\ORCL\REDO12A.LOG'
ORA-27070: async read/write failed
OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 1) 函数不正确。
ORA-00345: redo log write error block 152097 count 8
ORA-00312: online log 12 thread 1: 'S:\ORADATA\ORCL\REDO12B.LOG'
ORA-27070: async read/write failed
OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 1) 函数不正确。
ORA-00345: redo log write error block 152105 count 1
ORA-00312: online log 12 thread 1: 'S:\ORADATA\ORCL\REDO12A.LOG'
ORA-27070: async read/write failed
OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 1) 函数不正确。

直接启动数据库报错
修复好硬件之后,直接启动数据库报ORA-00600 kcratr_scan_lastbwr错误

Fri Feb 08 20:58:15 2019
alter database mount exclusive
Successful mount of redo thread 1, with mount id 1527506791
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Beginning crash recovery of 1 threads
Started redo scan
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3672.trc  (incident=41353):
ORA-00600: ??????, ??: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []
Incident details in: c:\oracle\diag\rdbms\orcl\orcl\incident\incdir_41353\orcl_ora_3672_i41353.trc
Aborting crash recovery due to error 600
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3672.trc:
ORA-00600: ??????, ??: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3672.trc:
ORA-00600: ??????, ??: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []
ORA-600 signalled during: alter database open...
Fri Feb 08 20:58:24 2019
Trace dumping is performing id=[cdmp_20190208205824]
Fri Feb 08 20:59:04 2019
alter database open
Beginning crash recovery of 1 threads
Started redo scan
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_1696.trc  (incident=41354):
ORA-00600: 内部错误代码, 参数: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []
Incident details in: c:\oracle\diag\rdbms\orcl\orcl\incident\incdir_41354\orcl_ora_1696_i41354.trc
Aborting crash recovery due to error 600
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_1696.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_1696.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []
ORA-600 signalled during: alter database open ...

recover database报错
执行recover database报错ORA-00600 6101,ORA-00600 kdourp_inorder2,ORA-00600 ktbsdp1,ORA-00600 3020

Fri Feb 08 21:09:20 2019
ALTER DATABASE RECOVER  database
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 4 slaves
Fri Feb 08 21:09:21 2019
Recovery of Online Redo Log: Thread 1 Group 12 Seq 17139 Reading mem 0
  Mem# 0: S:\ORADATA\ORCL\REDO12A.LOG
  Mem# 1: S:\ORADATA\ORCL\REDO12B.LOG
Fri Feb 08 21:09:21 2019
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_pr02_3780.trc  (incident=49379):
ORA-00600: internal error code, arguments: [6101], [17], [21], [0], [], [], [], [], [], [], [], []
Incident details in: c:\oracle\diag\rdbms\orcl\orcl\incident\incdir_49379\orcl_pr02_3780_i49379.trc
Fri Feb 08 21:09:21 2019
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_pr01_2040.trc  (incident=49371):
ORA-00600: internal error code, arguments: [kdourp_inorder2], [34], [0], [0], [44], [], [], [], [], [], [], []
Incident details in: c:\oracle\diag\rdbms\orcl\orcl\incident\incdir_49371\orcl_pr01_2040_i49371.trc
Fri Feb 08 21:09:21 2019
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_pr03_1068.trc  (incident=49387):
ORA-00600: internal error code, arguments: [ktbsdp1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: c:\oracle\diag\rdbms\orcl\orcl\incident\incdir_49387\orcl_pr03_1068_i49387.trc
Fri Feb 08 21:09:24 2019
Trace dumping is performing id=[cdmp_20190208210924]
Slave exiting with ORA-10562 exception
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_pr03_1068.trc:
ORA-10562: Error occurred while applying redo to data block (file# 4, block# 1716972)
ORA-10564: tablespace USERS
ORA-01110: data file 4: 'S:\ORADATA\ORCL\USERS01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 204127
ORA-00600: internal error code, arguments: [ktbsdp1], [], [], [], [], [], [], [], [], [], [], []
Slave exiting with ORA-10562 exception
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_pr02_3780.trc:
ORA-10562: Error occurred while applying redo to data block (file# 4, block# 1738552)
ORA-10564: tablespace USERS
ORA-01110: data file 4: 'S:\ORADATA\ORCL\USERS01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 211606
ORA-00600: internal error code, arguments: [6101], [17], [21], [0], [], [], [], [], [], [], [], []
Slave exiting with ORA-10562 exception
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_pr01_2040.trc:
ORA-10562: Error occurred while applying redo to data block (file# 4, block# 1725898)
ORA-10564: tablespace USERS
ORA-01110: data file 4: 'S:\ORADATA\ORCL\USERS01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 73907
ORA-00600: internal error code, arguments: [kdourp_inorder2], [34], [0], [0], [44], [], [], [], [], [], [], []
Recovery Slave PR03 previously exited with exception 10562
Fri Feb 08 21:09:28 2019
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_pr04_2608.trc  (incident=49395):
ORA-00600: internal error code, arguments: [3020], [4], [1739291], [18516507], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 4, block# 1739291, file offset is 1363369984 bytes)
ORA-10564: tablespace USERS
ORA-01110: data file 4: 'S:\ORADATA\ORCL\USERS01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 211552
Incident details in: c:\oracle\diag\rdbms\orcl\orcl\incident\incdir_49395\orcl_pr04_2608_i49395.trc
Slave exiting with ORA-600 exception
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_pr04_2608.trc:
ORA-00600: internal error code, arguments: [3020], [4], [1739291], [18516507], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 4, block# 1739291, file offset is 1363369984 bytes)
ORA-10564: tablespace USERS
ORA-01110: data file 4: 'S:\ORADATA\ORCL\USERS01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 211552
Media Recovery failed with error 448
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_pr00_1548.trc:
ORA-00283: recovery session canceled due to errors
ORA-00448: normal completion of background process
Slave exiting with ORA-283 exception
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_pr00_1548.trc:
ORA-00283: recovery session canceled due to errors
ORA-00448: normal completion of background process
ORA-10562 signalled during: ALTER DATABASE RECOVER  database  ...

出现上述问题主要是由于硬件突然故障,数据写丢失导致相关问题.

处理思路

RMAN> recover datafile 1;
启动 recover 于 09-2月 -19
使用通道 ORA_DISK_1
正在开始介质的恢复
介质恢复完成, 用时: 00:00:01
完成 recover 于 09-2月 -19
RMAN> recover datafile 2;
启动 recover 于 09-2月 -19
使用通道 ORA_DISK_1
正在开始介质的恢复
介质恢复完成, 用时: 00:00:01
完成 recover 于 09-2月 -19
RMAN> recover datafile 3;
启动 recover 于 09-2月 -19
使用通道 ORA_DISK_1
正在开始介质的恢复
介质恢复完成, 用时: 00:00:02
完成 recover 于 09-2月 -19
RMAN> recover datafile 4;
启动 recover 于 09-2月 -19
使用通道 ORA_DISK_1
正在开始介质的恢复
无法恢复介质
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: recover 命令 (在 02/09/2019 21:48:19 上) 失败
ORA-00283: recovery session canceled due to errors
RMAN-11003: 在分析/执行 SQL 语句期间失败: alter database recover if needed
 datafile 4
ORA-00283: 恢复会话因错误而取消
ORA-10562: Error occurred while applying redo to data block (file# 4, block# 172
5913)
ORA-10564: tablespace USERS
ORA-01110: 数据文件 4: 'S:\ORADATA\ORCL\USERS01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 73907
ORA-00600: 内部错误代码, 参数: [kdourp_inorder2], [34], [43], [44], [44], [], []
, [], [], [], [], []
SQL> recover datafile 4;
ORA-00283: 恢复会话因错误而取消
ORA-10562: Error occurred while applying redo to data block (file# 4, block#
1725913)
ORA-10564: tablespace USERS
ORA-01110: 数据文件 4: 'S:\ORADATA\ORCL\USERS01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 73907
ORA-00600: 内部错误代码, 参数: [kdourp_inorder2], [34], [43], [44], [44], [],
[], [], [], [], [], []
--通过bbed修改异常文件,屏蔽文件恢复,直接open库
SQL> alter database open;
数据库已更改。

数据库open之后,逻辑方式导出数据,重建新库,导入数据.