Tag Archives: ORA-600 2662

存储双活同步导致数据库异常恢复

Posted on by

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

标题：存储双活同步导致数据库异常恢复

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

客户双活存储异常之后,单个存储运行,故障存储修复之后,双活同步,出现多套系统异常,上一篇:Control file mount id mismatch!故障处理,这套是win的rac无法正常启动,ocr磁盘组异常(报ORA-600 kfrValAcd30无法正常mount)

C:\Users\Administrator>crsctl start cluster -all
CRS-2672: 尝试启动 'ora.crf' (在 'xff2' 上)
CRS-2672: 尝试启动 'ora.asm' (在 'xff2' 上)
CRS-2672: 尝试启动 'ora.crf' (在 'xff1' 上)
CRS-2672: 尝试启动 'ora.asm' (在 'xff1' 上)
CRS-2676: 成功启动 'ora.crf' (在 'xff2' 上)
CRS-2676: 成功启动 'ora.crf' (在 'xff1' 上)
CRS-5017: 资源操作 "ora.asm start" 遇到以下错误:
ORA-00600: internal error code, arguments: [kfrValAcd30], [OCR_VOTE], [1], [14], [7556], [15], [7584], [], [], [], [], []
。有关详细信息, 请参阅 "(:CLSN00107:)" (位于 "F:\app\grid\Administrator\diag\crs\xff2\crs\trace\ohasd_oraagent_system.trc" 中)。
CRS-2674: 未能启动 'ora.asm' (在 'xff2' 上)
CRS-2679: 尝试清除 'ora.asm' (在 'xff2' 上)
CRS-5017: 资源操作 "ora.asm start" 遇到以下错误:
ORA-00600: internal error code, arguments: [kfrValAcd30], [OCR_VOTE], [1], [14], [7556], [15], [7584], [], [], [], [], []
。有关详细信息, 请参阅 "(:CLSN00107:)" (位于 "F:\app\grid\Administrator\diag\crs\xff1\crs\trace\ohasd_oraagent_system.trc" 中)。
CRS-2674: 未能启动 'ora.asm' (在 'xff1' 上)
CRS-2679: 尝试清除 'ora.asm' (在 'xff1' 上)
CRS-2681: 成功清除 'ora.asm' (在 'xff2' 上)
CRS-2673: 尝试停止 'ora.crf' (在 'xff2' 上)
CRS-2677: 成功停止 'ora.crf' (在 'xff2' 上)
CRS-2681: 成功清除 'ora.asm' (在 'xff1' 上)
CRS-2673: 尝试停止 'ora.crf' (在 'xff1' 上)
CRS-2677: 成功停止 'ora.crf' (在 'xff1' 上)
CRS-4705: 无法在节点 xff1 上启动集群件。
CRS-4705: 无法在节点 xff2 上启动集群件。
CRS-4000: 命令 Start 失败, 或已完成但出现错误。

因为是ocr磁盘组操作比较简单,直接重建该磁盘组，还原ocr等即可

C:\Users\Administrator>asmtool -list
NTFS                             \Device\Harddisk0\Partition1              300M
NTFS                             \Device\Harddisk0\Partition4           599472M
NTFS                             \Device\Harddisk0\Partition5          1000000M
ORCLDISKDATA0                    \Device\Harddisk1\Partition1          1048587M
ORCLDISKDATA1                    \Device\Harddisk2\Partition1          1048587M
ORCLDISKDATA2                    \Device\Harddisk3\Partition1          1048587M
ORCLDISKDATA3                    \Device\Harddisk4\Partition1          1048587M
ORCLDISKDATA4                    \Device\Harddisk6\Partition1           460797M

C:\Users\Administrator>crsctl start crs -excl -nocrs
CRS-4123: Oracle 高可用性服务已启动。
CRS-2672: 尝试启动 'ora.evmd' (在 'xff2' 上)
CRS-2672: 尝试启动 'ora.mdnsd' (在 'xff2' 上)
CRS-2676: 成功启动 'ora.mdnsd' (在 'xff2' 上)
CRS-2676: 成功启动 'ora.evmd' (在 'xff2' 上)
CRS-2672: 尝试启动 'ora.gpnpd' (在 'xff2' 上)
CRS-2676: 成功启动 'ora.gpnpd' (在 'xff2' 上)
CRS-2672: 尝试启动 'ora.cssdmonitor' (在 'xff2' 上)
CRS-2672: 尝试启动 'ora.gipcd' (在 'xff2' 上)
CRS-2676: 成功启动 'ora.cssdmonitor' (在 'xff2' 上)
CRS-2676: 成功启动 'ora.gipcd' (在 'xff2' 上)
CRS-2672: 尝试启动 'ora.cssd' (在 'xff2' 上)
CRS-2676: 成功启动 'ora.cssd' (在 'xff2' 上)
CRS-2672: 尝试启动 'ora.ctssd' (在 'xff2' 上)
CRS-2676: 成功启动 'ora.ctssd' (在 'xff2' 上)
CRS-2672: 尝试启动 'ora.asm' (在 'xff2' 上)
CRS-5017: 资源操作 "ora.asm start" 遇到以下错误:
ORA-00600: internal error code, arguments: [kfrValAcd30], [OCR_VOTE], [1], [14], [7556], [15], [7584], [], [], [], [], []
。有关详细信息, 请参阅 "(:CLSN00107:)" (位于 "F:\app\grid\Administrator\diag\crs\xff2\crs\trace\ohasd_oraagent_system.trc" 中)。
CRS-2674: 未能启动 'ora.asm' (在 'xff2' 上)
CRS-2679: 尝试清除 'ora.asm' (在 'xff2' 上)
CRS-2681: 成功清除 'ora.asm' (在 'xff2' 上)
CRS-2673: 尝试停止 'ora.ctssd' (在 'xff2' 上)
CRS-2677: 成功停止 'ora.ctssd' (在 'xff2' 上)
CRS-4000: 命令 Start 失败, 或已完成但出现错误。

C:\Users\Administrator>sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on 星期四 5月 4 13:52:07 2023

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

已连接到空闲例程。

SQL> startup nomount pfile='f:/pfile_asm.txt';
ASM 实例已启动

Total System Global Area 1140850688 bytes
Fixed Size                  3054680 bytes
Variable Size            1112630184 bytes
ASM Cache                  25165824 bytes

SQL>  create diskgroup OCR_VOTE  external redundancy disk '\\.\ORCLDISKDATA4' force  attribute 'COMPATIBLE.ASM' = '12.1.0';

Diskgroup created.

F:\>ocrconfig -restore backup00.ocr

F:\>crsctl replace votedisk +OCR_VOTE
已成功添加表决磁盘 e2b8fdbd05ae4f9fbf3531630853dbbc。
已成功将表决磁盘组替换为 +OCR_VOTE。
CRS-4266: 已成功替换表决文件

F:\>crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   e2b8fdbd05ae4f9fbf3531630853dbbc (\\.\ORCLDISKDATA4) [OCR_VOTE]
找到了 1 个表决磁盘。

F:\>ocrcheck
Oracle 集群注册表的状态如下:
         版本                  :          4
         总空间 (KB)     :     409568
         已用空间 (KB)      :       1348
         可用空间 (KB):     408220
         ID                       :  820087446
         设备/文件名         :  +OCR_VOTE
                                    设备/文件完整性检查成功

                                    设备/文件尚未配置

                                    设备/文件尚未配置

                                    设备/文件尚未配置

                                    设备/文件尚未配置

         集群注册表完整性检查成功

         逻辑损坏检查成功

mount其他磁盘组成功

SQL> alter diskgroup arch mount;

Diskgroup altered.

SQL>


SQL> alter diskgroup data mount;

Diskgroup altered.

尝试恢复数据库失败

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on 星期四 5月 4 14:09:39 2023

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

已连接到空闲例程。

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 2.0992E+11 bytes
Fixed Size                  7797816 bytes
Variable Size            1.3798E+11 bytes
Database Buffers         7.1672E+10 bytes
Redo Buffers              260636672 bytes
数据库装载完毕。

SQL> recover database;
ORA-10562: Error occurred while applying redo to data block (file# 13, block#1033775)
ORA-10564: tablespace USERS
ORA-01110: 数据文件 13: '+DATA/XFF/users07.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 40396
ORA-00600: 内部错误代码, 参数: [kdolkr-2], [2], [1], [44], [], [], [], [], [],[], [], []


SQL> recover datafile 2;
ORA-00283: 恢复会话因错误而取消
ORA-00742: 日志读取在线程 1 序列 60656 块 1150508 中检测到写入丢失情况
ORA-00312: 联机日志 3 线程 1: '+DATA/XFF/redo03.log'


SQL> recover datafile 1;
ORA-00283: 恢复会话因错误而取消
ORA-00742: 日志读取在线程 1 序列 60656 块 1150508 中检测到写入丢失情况
ORA-00312: 联机日志 3 线程 1: '+DATA/XFF/redo03.log'

SQL> recover datafile 10;
ORA-00283: ??????????
ORA-10562: Error occurred while applying redo to data block (file# 10, block#
2899468)
ORA-10564: tablespace USERS
ORA-01110: ???? 10: '+DATA/XFF/users04.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 40396
ORA-00600: ??????, ??: [ktbair2: illegal  inheritance], [], [], [], [], [], [],[], [], [], [], []

除了ORA-00742,还有其他一些日志应用错误,比如:ORA-600 ktbair2: illegal inheritance,ORA-600 kdolkr-2等,无法正常应用日志,尝试强制打开库,报ORA-600 2662错误.

SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [8], [678024613], [8],
[678508930], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [8], [678024612], [8],
[678508930], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [8], [678024610], [8],
[678508930], [12583040], [], [], [], [], [], []
进程 ID: 4628
会话 ID: 996 序列号: 48547

通过自研的Patch_SCN工具快速解决该问题
20230507195805

open数据库成功,实现最大限度抢救客户数据.

Oracle断电故障处理

Posted on by

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

标题：Oracle断电故障处理

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

异常断电导致数据库异常恢复文件报ORA-00283 ORA-00742 ORA-00312

 
D:\check_db>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期二 5月 31 00:38:42 2022

Copyright (c) 1982, 2013, Oracle.  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

SQL> recover datafile 1;
ORA-00283: 恢复会话因错误而取消
ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况
ORA-00312: 联机日志 3 线程 1:
'D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_3_HJ32KJD5_.LOG'

这个错误比较明显是由于异常断电引起的写丢失导致.而且这种故障在没有备份的情况下，没有什么好处理方法,只能屏蔽一致性强制拉库,尝试强制拉库报错如下

SQL> startup mount pfile='d:/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area 2.0310E+10 bytes
Fixed Size                  2290000 bytes
Variable Size            3690991280 bytes
Database Buffers         1.6576E+10 bytes
Redo Buffers               40837120 bytes
数据库装载完毕。
SQL> recover database until cancel;
ORA-00279: 更改 18755939194213 (在  生成) 对于线程 1 是必需的


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_3_HJ32KJD5_.LOG
ORA-00600: internal error code, arguments: [3020], [2], [78824], [8467432], [],
[], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 78824, file
offset is 645726208 bytes)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: 'D:\ORADATA\ORCL\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 80834


ORA-01112: 未启动介质恢复


SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [krsi_al_hdr_update.15], [4294967295], [], [],[], [], [], [], [], [], [], []

ORA-600 krsi_al_hdr_update.15错误,主要是由于redo异常导致无法resetlogs成功,具体参考：Alter Database Open Resetlogs returns error ORA-00600: [krsi_al_hdr_update.15], (Doc ID 2026541.1)描述,处理这个问题之后,再次resetlogs库，报ORA-600 2662错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [4366], [4112122046],
[4366], [4112228996], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [4366], [4112122045],
[4366], [4112228996], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [4366], [4112122040],
[4366], [4112228996], [12583040], [], [], [], [], [], []
进程 ID: 4644
会话 ID: 1701 序列号: 3

这个问题比较简单,通过修改scn即可绕过去,之后数据库open报ORA-600 4194等错误

SQL> alter database open ;
alter database open 
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [4194], [

SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc  (incident=322982):
ORA-00600: internal error code, arguments: [4137], [10.33.3070116], [0], [0], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_322982\orcl_smon_5112_i322982.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
replication_dependency_tracking turned off (no async multimaster replication found)
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_3340.trc  (incident=323030):
ORA-00600: 内部错误代码, 参数: [4194], [
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_323030\orcl_ora_3340_i323030.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue May 31 09:05:04 2022
Sweep [inc][322982]: completed
ORACLE Instance orcl (pid = 13) - Error 600 encountered while recovering transaction (10, 33).
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc:
ORA-00600: internal error code, arguments: [4137], [10.33.3070116], [0], [0], [], [], [], [], [], [], [], []
Checker run found 1 new persistent data failures
Tue May 31 09:05:05 2022
Sweep [inc][323030]: completed
Sweep [inc2][322982]: completed
Tue May 31 09:05:14 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc  (incident=322983):
ORA-00600: internal error code, arguments: [4193], [10.33.3070116], [0], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_322983\orcl_smon_5112_i322983.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue May 31 09:05:14 2022
ORA-600 signalled during: alter database open...
Block recovery stopped at EOT rba 2.61.16
Block recovery completed at rba 2.61.16, scn 4366.4112429058
Block recovery from logseq 2, block 60 to scn 18755939643393
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_2_K9BSVC11_.LOG
Block recovery completed at rba 2.61.16, scn 4366.4112429058
Dumping diagnostic data in directory=[cdmp_2022053],requested by(instance=1,osid=5112(SMON)),summary=[incident=322983].
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc:
ORA-01595: error freeing extent (3) of rollback segment (1))
ORA-00600: internal error code, arguments: [4193], [10.33.3070116], [3], [], [], [], [], [], [], [], [], []

对异常undo进行处理,数据库正常open成功

SQL> shutdown immediate;
ORA-00600: 内部错误代码, 参数: [4193], [


SQL> shutdown abort;
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 2.0310E+10 bytes
Fixed Size                  2290000 bytes
Variable Size            3690991280 bytes
Database Buffers         1.6576E+10 bytes
Redo Buffers               40837120 bytes
数据库装载完毕。
SQL> alter database open;

数据库已更改。

hcheck检测有一些字典不一致,建议客户逻辑导出,然后导入到新库中

HCheck Version 07MAY18 on 31-5月 -2022 09:12:22
----------------------------------------------
Catalog Version 11.2.0.4.0 (1102000400)
db_name: ORCL

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp      Resul
t
------------------------------ ... ---------- -- ---------- -------------- -----
-
.- LobNotInObj                 ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- MissingOIDOnObjCol          ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- SourceNotInObj              ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- OversizedFiles              ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- PoorDefaultStorage          ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- PoorStorage                 ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- TabPartCountMismatch        ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- OrphanedTabComPart          ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- MissingSum$                 ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- MissingDir$                 ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- DuplicateDataobj            ... 1102000400 <=  *All Rel* 05/31 09:12:22 PASS
.- ObjSynMissing               ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- ObjSeqMissing               ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedUndo                ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndex               ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndexPartition      ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndexSubPartition   ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedTable               ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedTablePartition      ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedTableSubPartition   ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- MissingPartCol              ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedSeg$                ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndPartObj#         ... 1102000400 <=  *All Rel* 05/31 09:12:23 FAIL

HCKE-0024: Orphaned Index Partition Obj# (no OBJ$) (Doc ID 1360935.1)
ORPHAN INDPART$: OBJ#=149167 BO#=6378 - no OBJ$ row
ORPHAN INDPART$: OBJ#=149168 BO#=6378 - no OBJ$ row

.- DuplicateBlockUse           ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- FetUet                      ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- Uet0Check                   ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- SeglessUET                  ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- BadInd$                     ... 1102000400 <=  *All Rel* 05/31 09:12:23 FAIL

HCKE-0030: OBJ$ INDEX entry has no IND$ or INDPART$/INDSUBPART$ entry (Doc ID 13
60528.1)
OBJ$ INDEX PARTITION has no INDPART$ entry: Obj#=148278 SYS Name=WRH$_FILESTATXS
_PK PARTITION=WRH$_FILEST_1572571104_16462
OBJ$ INDEX PARTITION has no INDPART$ entry: Obj#=148920 SYS Name=WRH$_FILESTATXS
_PK PARTITION=WRH$_FILEST_1572571104_16678

.- BadTab$                     ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- BadIcolDepCnt               ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- ObjIndDobj                  ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- TrgAfterUpgrade             ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- ObjType0                    ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- BadOwner                    ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- StmtAuditOnCommit           ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- BadPublicObjects            ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- BadSegFreelist              ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- BadDepends                  ... 1102000400 <=  *All Rel* 05/31 09:12:23 WARN

HCKW-0016: Dependency$ p_timestamp mismatch for VALID objects (Doc ID 1361045.1)

[E] - P_OBJ#=6376 D_OBJ#=6765

.- CheckDual                   ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- ObjectNames                 ... 1102000400 <=  *All Rel* 05/31 09:12:23 PASS
.- BadCboHiLo                  ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- ChkIotTs                    ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- NoSegmentIndex              ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- BadNextObject               ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- DroppedROTS                 ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- FilBlkZero                  ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- DbmsSchemaCopy              ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- OrphanedObjError            ... 1102000400 >  1102000000 05/31 09:12:24 PASS
.- ObjNotLob                   ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- MaxControlfSeq              ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- SegNotInDeferredStg         ... 1102000400 >  1102000000 05/31 09:12:24 PASS
.- SystemNotRfile1             ... 1102000400 >   902000000 05/31 09:12:24 PASS
.- DictOwnNonDefaultSYSTEM     ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- OrphanTrigger               ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
.- ObjNotTrigger               ... 1102000400 <=  *All Rel* 05/31 09:12:24 PASS
---------------------------------------
31-5月 -2022 09:12:24  Elapsed: 2 secs
---------------------------------------
Found 4 potential problem(s) and 1 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not

PL/SQL 过程已成功完成。

commit_wait和commit_logging设置不当导致数据库无法正常启动

Posted on by

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

标题：commit_wait和commit_logging设置不当导致数据库无法正常启动

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

客户数据库设置以下参数，突然掉电之后，数据库无法正常启动

  commit_wait              = "NOWAIT"
  commit_logging           = "BATCH"

数据库open报错

alter database open
Block change tracking file is current.
Beginning crash recovery of 1 threads
 parallel recovery started with 31 processes
Started redo scan
Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28711.trc  (incident=8002955):
ORA-00353: log corruption near block 3 change 17372812227460 time 03/20/2022 00:11:51
ORA-00312: online log 12315  thread 1: '/media/oracle/redolog/redo05.log'
Aborting crash recovery due to error 399
Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28711.trc:
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 3 change 17372812227460 time 03/20/2022 00:11:51
ORA-00312: online log 12315 thread 1: '/media/oracle/redolog/redo05.log'
Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28711.trc:
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 3 change 17372812227460 time 03/20/2022 00:11:51
ORA-00312: online log 12315  thread 1: '/media/oracle/redolog/redo05.log'
ORA-399 signalled during: alter database open...

报错信息比较明显是由于redo损坏导致,尝试强制open库

Sun Mar 20 18:32:35 2022
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 17372812227456
Resetting resetlogs activation ID 1627598093 (0x61032d0d)
Sun Mar 20 18:34:08 2022
Setting recovery target incarnation to 2
Sun Mar 20 18:34:08 2022
Initializing SCN for created control file
Database SCN compatibility initialized to 3
Warning - High Database SCN: Current SCN value is 17372812227459, threshold SCN value is 0
If you have not previously reported this warning on this database, 
please notify Oracle Support so that additional diagnosis can be performed.
Sun Mar 20 18:34:08 2022
Assigning activation ID 1627615603 (0x61037173)
Thread 1 opened at log sequence 1
  Current log# 2 seq# 1 mem# 0: /media/oracle/redolog/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Mar 20 18:34:08 2022
SMON: enabling cache recovery
Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14369.trc  (incident=8003142):
ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], 
Incident details in: /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003142/orcl_ora_14369_i8003142.trc
Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14369.trc  (incident=8003143):
ORA-00353: log corruption near block 3 change 17372812227462 time 03/20/2022 18:34:10
ORA-00312: online log 2 thread 1: '/media/oracle/redolog/redo02.log'
ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], 
Incident details in: /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003143/orcl_ora_14369_i8003143.trc
Errors in file /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003142/orcl_ora_14369_i8003142.trc:
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 3 change 17372812227462 time 03/20/2022 18:34:10
ORA-00312: online log 2 thread 1: '/media/oracle/redolog/redo02.log'
ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], 
Errors in file /media/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14369.trc  (incident=8003144):
ORA-00353: log corruption near block 3 change 17372812227462 time 03/20/2022 18:34:10
ORA-00334: archived log: '/media/oracle/redolog/redo02.log'
ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344], 
Incident details in: /media/oracle/diag/rdbms/orcl/orcl/incident/incdir_8003144/orcl_ora_14369_i8003144.trc
Sun Mar 20 18:34:11 2022
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [4044], [3964482444], [4044], [3964488833], [12669344], 
ORA-00600: internal error code, arguments: [2662], [4044], [3964482443], [4044], [3964488833], [12669344], 
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [4044], [3964482439], [4044], [3964488833], [12669344],

强制open数据库报ora-600 2662错误,比较常见,通过修改scn再尝试open库

Sun Mar 20 18:38:43 2022
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 17372812227460
Resetting resetlogs activation ID 1627615603 (0x61037173)
Sun Mar 20 18:40:02 2022
Setting recovery target incarnation to 2
Sun Mar 20 18:40:02 2022
Initializing SCN for created control file
Database SCN compatibility initialized to 3
Warning - High Database SCN: Current SCN value is 17372812227463, threshold SCN value is 0
If you have not previously reported this warning on this database,
 please notify Oracle Support so that additional diagnosis can be performed.
Sun Mar 20 18:40:02 2022
Assigning activation ID 1627669665 (0x610444a1)
Thread 1 opened at log sequence 1
  Current log# 2 seq# 1 mem# 0: /media/oracle/redolog/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Mar 20 18:40:02 2022
SMON: enabling cache recovery
Undo initialization finished serial:0 start:779809538 end:779809788 diff:250 (2 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
   then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sun Mar 20 18:40:04 2022
QMNC started with pid=55, OS id=16232 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Sun Mar 20 18:40:05 2022
db_recovery_file_dest_size of 3882 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Sun Mar 20 18:40:05 2022
Starting background process CJQ0
Sun Mar 20 18:40:05 2022
CJQ0 started with pid=58, OS id=16251 
Completed: alter database open resetlogs

后续增加temp,导出数据到新库,恢复完成

ora-600 2662和ora-600 kclchkblk_4恢复

Posted on by

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

标题：ora-600 2662和ora-600 kclchkblk_4恢复

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

这两天连续处理两个case,一个是12.1.0.2版本数据库屏蔽一致性,强制open之后,报ORA-600 2662故障
20210429220218

这个错误本身是一个非常常见的错误,直接推scn即可解决,但是问题是12.1.0.2版本,oracle不允许以前常规的操作方法,就连oradebug都报错oradebug poke ORA-32521/ORA-32519故障解决,而且可以是rac环境,bbed修改文件头也相当麻烦,最后我们使用patch方法轻松解决
另外一例是11.2.0.4版本,强制open库报ora-600 kclchkblk_4

Wed Apr 28 21:25:38 2021
SMON: enabling cache recovery
Instance recovery: looking for dead threads
Instance recovery: lock domain invalid but no dead threads
Errors in file /u01/app/oracle/diag/rdbms/dc/dc1/trace/dc1_ora_27832.trc  (incident=564430):
ORA-00600: internal error code, arguments: [kclchkblk_4], [2959], [904341694], [2959], [904131717], []
Incident details in: /u01/app/oracle/diag/rdbms/dc/dc1/incident/incdir_564430/dc1_ora_27832_i564430.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/dc/dc1/trace/dc1_ora_27832.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kclchkblk_4], [2959], [904341694], [2959], [904131717], []
Errors in file /u01/app/oracle/diag/rdbms/dc/dc1/trace/dc1_ora_27832.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kclchkblk_4], [2959], [904341694], [2959], [904131717], []
Error 704 happened during db open, shutting down database
USER (ospid: 27832): terminating the instance due to error 704
Instance terminated by USER, pid = 27832
ORA-1092 signalled during: alter database open resetlogs...

这个比较简单,参考redo异常 ORA-600 kclchkblk_4 故障恢复.

raid强制上线后数据库无法启动故障处理

Posted on by

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

标题：raid强制上线后数据库无法启动故障处理

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

由于raid掉盘过多,强制raid上线,然后启动数据库报以下错误

Mon Apr 19 23:19:28 2021
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 106750 KB redo, 9080 data blocks need recovery
Mon Apr 19 23:19:45 2021
Slave exiting with ORA-1115 exception
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p000_3277.trc:
ORA-01115: IO error reading block from file 9 (block # 339)
ORA-01110: data file 9: '/u01/app/oracle/oradata/orcl/dev02.dbf'
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I/O error
Additional information: 4
Additional information: 326
Additional information: 24576
ORA-27072: File I
Mon Apr 19 23:19:45 2021
Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 106750 KB redo, 9080 data blocks need recovery
Aborting crash recovery due to error 1115
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3275.trc:
ORA-01115: IO error reading block from file 9 (block # 329)
ORA-01110: data file 9: '/u01/app/oracle/oradata/orcl/dev02.dbf'
ORA-1115 signalled during: ALTER DATABASE OPEN...

错误提示比较明显IO error,结合客户强行上线raid的操作,比较明显是由于底层io问题导致该错误,直接对此文件dbv检查

[oracle@database orcl]$ dbv file=dev02.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Mon Apr 19 23:59:03 2021

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

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/dev02.dbf

DBV-00600: Fatal Error - [28] [27061] [0] [0]

对于此类情况,通过工具进行处理

DUL> copy file from  /u01/app/oracle/oradata/orcl/dev02.dbf to /oradata/dev02.dbf
 
starting copy datafile '/u01/app/oracle/oradata/orcl/dev02.dbf' to '/oradata/dev02.dbf'
read data error from file '/u01/app/oracle/oradata/orcl/dev02.dbf'.error message:Input/output error
read block# error: 303
read data error from file '/u01/app/oracle/oradata/orcl/dev02.dbf'.error message:Input/output error
read block# error: 304
read data error from file '/u01/app/oracle/oradata/orcl/dev02.dbf'.error message:Input/output error
read block# error: 329
datafile copy completed with 2 block error.

dbv校验文件

[oracle@database oradata]$ dbv file=dev02.dbf 

DBVERIFY: Release 11.2.0.4.0 - Production on Tue Apr 20 00:28:31 2021

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

DBVERIFY - Verification starting : FILE = /oradata/dev02.dbf
Page 303 is marked corrupt
Corrupt block relative dba: 0x0240012f (file 9, block 303)
Completely zero block found during dbv: 

Page 304 is marked corrupt
Corrupt block relative dba: 0x02400130 (file 9, block 304)
Completely zero block found during dbv: 

Page 329 is marked corrupt
Corrupt block relative dba: 0x02400149 (file 9, block 329)
Completely zero block found during dbv: 



DBVERIFY - Verification complete

Total Pages Examined         : 3932160
Total Pages Processed (Data) : 3213723
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 714294
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 4139
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1
Total Pages Marked Corrupt   : 3
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 85078875 (6.85078875)

通过对io error的文件进行处理,最终损坏三个block,最大限度抢救数据.使用被恢复出来的文件,尝试open库遭遇以下错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [6], [85035771], [6],
[85084136], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [6], [85035770], [6],
[85084136], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [6], [85035764], [6],
[85084136], [12583040], [], [], [], [], [], []
Process ID: 6733
Session ID: 570 Serial number: 3

ora-600 2662这个错误比较明显,处理文件头scn,继续open库

SQL> alter database open ;
alter database open 
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 6840
Session ID: 570 Serial number: 3

查看alert日志信息

Tue Apr 20 01:22:27 2021
alter database open upgrade
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 1 KB redo, 3 data blocks need recovery
Started redo application at
 Thread 1: logseq 1, block 3
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/orcl/redo01.log
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 1, block 5, scn 25854859541
 3 data blocks read, 3 data blocks written, 1 redo k-bytes read
Tue Apr 20 01:22:28 2021
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Apr 20 01:22:28 2021
SMON: enabling cache recovery
[6840] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:5902014 end:5905574 diff:3560 (35 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_6824.trc  (incident=63970):
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_63970/orcl_smon_6824_i63970.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORACLE Instance orcl (pid = 14) - Error 600 encountered while recovering transaction (24, 2) on object 89023.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_6824.trc:
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Tue Apr 20 01:22:38 2021
ORACLE Instance orcl (pid = 14) - Error 600 encountered while recovering transaction (63, 3) on object 89023.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_6824.trc:
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_6824.trc  (incident=63974):
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_63974/orcl_smon_6824_i63974.trc
Tue Apr 20 01:22:55 2021
PMON (ospid: 6798): terminating the instance due to error 474

这个错误是比较常见的错误,参考:ORACLE Instance XFF (pid = 18) – Error 600 encountered while recovering transaction ，通过处理之后,数据库open成功

SQL> startup mount pfile='/tmp/pfile';
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size            1023413408 bytes
Database Buffers          570425344 bytes
Redo Buffers                7319552 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

后续安排逻辑导出,导入新库