又一例ORA-600 kcbzpbuf_1恢复

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

标题:又一例ORA-600 kcbzpbuf_1恢复

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

数据库突然报ORA-600 kdddgb1和ORA-600 kcl_snd_cur_2错误,并且导致实例crash

Tue May 09 22:29:40 2023
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_338012.trc  (incident=962050):
ORA-00600: internal error code, arguments: [kdddgb1], [0], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_962050/orcl1_ora_338012_i962050.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 09 22:29:43 2023
Hex dump of (file 75, block 1154926) in trace file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms3_217928.trc
Corrupt block relative dba: 0x12d19f6e (file 75, block 1154926)
Bad header found during preparing block for transfer
Data in bad block:
 type: 0 format: 2 rdba: 0x1affe051
 last change scn: 0x0009.a2266e65 seq: 0x2 flg: 0x10
 spare1: 0x83 spare2: 0x36 spare3: 0x3700
 consistency value in tail: 0x6e650002
 check value in block header: 0x0
 block checksum disabled
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms3_217928.trc  (incident=960186):
ORA-00600: internal error code, arguments: [kcl_snd_cur_2], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_960186/orcl1_lms3_217928_i960186.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 09 22:29:43 2023
Sweep [inc][962050]: completed
Sweep [inc][960186]: completed
Sweep [inc2][962050]: completed
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms3_217928.trc:
ORA-00600: internal error code, arguments: [kcl_snd_cur_2], [], [], [], [], [], [], [], [], [], [], []
LMS3 (ospid: 217928): terminating the instance due to error 484
System state dump requested by (instance=1, osid=217928 (LMS3)), summary=[abnormal instance termination].
System State dumped to trace file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_diag_217897_20230509222949.trc
Tue May 09 22:29:52 2023
ORA-1092 : opitsk aborting process
Tue May 09 22:29:53 2023
ORA-1092 : opitsk aborting process
Tue May 09 22:29:54 2023
Instance terminated by LMS3, pid = 217928

另外一个正在运行的实例做instance recovery,然后节点报ORA-600 kcbzpbuf_1,节点也crash,再次启动一直该错误无法正常启动.

Wed May 10 08:17:07 2023
Hex dump of (file 75, block 1154926) in trace file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_dbw9_134621.trc
Corrupt block relative dba: 0x12d19f6e (file 75, block 1154926)
Bad header found during preparing block for write
Data in bad block:
 type: 0 format: 2 rdba: 0x1affe051
 last change scn: 0x0009.a2266e65 seq: 0x2 flg: 0x34
 spare1: 0x83 spare2: 0x36 spare3: 0x3700
 consistency value in tail: 0x6e650002
 check value in block header: 0xf894
 computed block checksum: 0x0
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_dbw9_134621.trc  (incident=2240402):
ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_2240402/orcl1_dbw9_134621_i2240402.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 /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_dbw9_134621.trc:
ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], [], [], [], [], []
DBW9 (ospid: 134621): terminating the instance due to error 471
Wed May 10 08:17:08 2023
System state dump requested by (instance=1, osid=134621 (DBW9)), summary=[abnormal instance termination].
System State dumped to trace file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_diag_134555_20230510081708.trc
Instance terminated by DBW9, pid = 134621

尝试直接recover datafile 75失败,报ORA-03113

SQL> recover datafile 75;
ORA-03113: end-of-file on communication channel
Process ID: 281304
Session ID: 14161 Serial number: 1503

dbv检查file 75,发现15个block逻辑坏块

[oracle@oradb21 ~]$ dbv userid=xxx/xxx file=+datadg/orcl/datafile/xifenfei01.377.1130539753

DBVERIFY: Release 11.2.0.4.0 - Production on Wed May 10 08:29:44 2023

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

DBVERIFY - Verification starting : FILE = +datadg/orcl/datafile/xifenfei01.377.1130539753
Block Checking: DBA = 314866909, Block Type = KTB-managed data block
data header at 0x7f852b573064
kdbchk: row locked by non-existent transaction
        table=0   slot=13
        lockid=101   ktbbhitc=2
Page 294109 failed with check code 6101
Block Checking: DBA = 314866928, Block Type = KTB-managed data block
data header at 0x7f852b599064
kdbchk: row locked by non-existent transaction
        table=0   slot=18
        lockid=101   ktbbhitc=2
Page 294128 failed with check code 6101
Block Checking: DBA = 315415269, Block Type = KTB-managed data block
data header at 0x7f852b583064
kdbchk: the amount of space used is not equal to block size
        used=7470 fsc=0 avsp=625 dtl=8088
Page 842469 failed with check code 6110
Block Checking: DBA = 315415302, Block Type = KTB-managed data block
data header at 0x7f852b3c3064
kdbchk: row locked by non-existent transaction
        table=0   slot=13
        lockid=101   ktbbhitc=2
Page 842502 failed with check code 6101
Block Checking: DBA = 315415350, Block Type = KTB-managed data block
data header at 0x7f852b423064
kdbchk: row locked by non-existent transaction
        table=0   slot=14
        lockid=101   ktbbhitc=2
Page 842550 failed with check code 6101
Block Checking: DBA = 315415351, Block Type = KTB-managed data block
data header at 0x7f852b425064
kdbchk: row locked by non-existent transaction
        table=0   slot=10
        lockid=101   ktbbhitc=2
Page 842551 failed with check code 6101
Block Checking: DBA = 315415397, Block Type = KTB-managed data block
data header at 0x7f852b481064
kdbchk: row locked by non-existent transaction
        table=0   slot=14
        lockid=101   ktbbhitc=2
Page 842597 failed with check code 6101
Block Checking: DBA = 315415414, Block Type = KTB-managed data block
data header at 0x7f852b4a3064
kdbchk: row locked by non-existent transaction
        table=0   slot=14
        lockid=101   ktbbhitc=2
Page 842614 failed with check code 6101
Block Checking: DBA = 315665300, Block Type = KTB-managed data block
data header at 0x7f852b2dd0ac
kdbchk: the amount of space used is not equal to block size
        used=7191 fsc=0 avsp=832 dtl=8016
Page 1092500 failed with check code 6110
Block Checking: DBA = 315665302, Block Type = KTB-managed data block
data header at 0x7f852b2e10ac
kdbchk: row locked by non-existent transaction
        table=0   slot=14
        lockid=101   ktbbhitc=5
Page 1092502 failed with check code 6101
Block Checking: DBA = 315665316, Block Type = KTB-managed data block
data header at 0x7f852b2fd0ac
kdbchk: the amount of space used is not equal to block size
        used=7140 fsc=0 avsp=883 dtl=8016
Page 1092516 failed with check code 6110
Block Checking: DBA = 315665491, Block Type = KTB-managed data block
data header at 0x7f852f4170c4
kdbchk: row locked by non-existent transaction
        table=0   slot=3
        lockid=101   ktbbhitc=6
Page 1092691 failed with check code 6101
Block Checking: DBA = 315727518, Block Type = KTB-managed data block
data header at 0x7f852b4f50c4
kdbchk: row locked by non-existent transaction
        table=0   slot=8
        lockid=101   ktbbhitc=6
Page 1154718 failed with check code 6101
Block Checking: DBA = 315727614, Block Type = KTB-managed data block
data header at 0x7f852b5b50ac
kdbchk: row locked by non-existent transaction
        table=0   slot=15
        lockid=101   ktbbhitc=5
Page 1154814 failed with check code 6101
Block Checking: DBA = 315727646, Block Type = KTB-managed data block
data header at 0x7f852b3f30ac
kdbchk: row locked by non-existent transaction
        table=0   slot=3
        lockid=101   ktbbhitc=5
Page 1154846 failed with check code 6101


DBVERIFY - Verification complete

Total Pages Examined         : 1835008
Total Pages Processed (Data) : 250749
Total Pages Failing   (Data) : 15
Total Pages Processed (Index): 74532
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1244181
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 265546
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2720428335 (9.2720428335)

通过对坏块一些处理,数据库open成功,以前有过类似恢复ORA-600 kcbzpbuf_1故障恢复

SQL> alter database open;

Database altered.

alert日志报事务异常

ORACLE Instance orcl1 (pid = 34) - Error 1578 encountered while recovering transaction (697, 6) on object 170692.
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_301450.trc:
ORA-01578: ORACLE data block corrupted (file # 75, block # 1154926)
ORA-01110: data file 75: '+DATADG/orcl/datafile/xifenfei01.377.1130539753'
Archived Log entry 9299 added for thread 1 sequence 4781 ID 0x5f4a1865 dest 1:
Wed May 10 08:24:03 2023
NOTE: dependency between database orcl and diskgroup resource ora.ARCHDG.dg is established
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Wed May 10 08:24:04 2023
Starting background process EMNC
Wed May 10 08:24:04 2023
EMNC started with pid=49, OS id=305303 
Archived Log entry 9300 added for thread 2 sequence 4530 ID 0x5f4a1865 dest 1:
ARC2: Archiving disabled thread 2 sequence 4531
Archived Log entry 9301 added for thread 2 sequence 4531 ID 0x5f4a1865 dest 1:
Wed May 10 08:24:13 2023
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_p000_305307.trc  (incident=2560578):
ORA-01578: ORACLE data block corrupted (file # 75, block # 1154926)
ORA-01110: data file 75: '+DATADG/orcl/datafile/xifenfei01.377.1130539753'
Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_2560578/orcl1_p000_305307_i2560578.trc
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_p000_305307.trc  (incident=2560579):
ORA-01578: ORACLE data block corrupted (file # , block # )
Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_2560579/orcl1_p000_305307_i2560579.trc
Wed May 10 08:24:15 2023
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_301450.trc  (incident=2560427):
ORA-01578: ORACLE data block corrupted (file # 75, block # 1154926)
ORA-01110: data file 75: '+DATADG/orcl/datafile/xifenfei01.377.1130539753'
Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_2560427/orcl1_smon_301450_i2560427.trc
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_301450.trc  (incident=2560432):
ORA-01578: ORACLE data block corrupted (file # 75, block # 1154926)
ORA-01110: data file 75: '+DATADG/orcl/datafile/xifenfei01.377.1130539753'
ORACLE Instance orcl1 (pid = 34) - Error 1578 encountered while recovering transaction (717, 20) on object 170692.
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_301450.trc:
ORA-01578: ORACLE data block corrupted (file # 75, block # 1154926)
ORA-01110: data file 75: '+DATADG/orcl/datafile/xifenfei01.377.1130539753'

处理异常事务,并且定位异常对象表

SQL> select owner,object_name,object_type from dba_objects where object_id=170692;

OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
---------------------------------------------------------
XFF
T_XIFENFEI
TABLE

rman检测逻辑坏块所属对象也是这个表(15个坏块均为该表),对该表数据进行重建抛弃损坏数据,完成本次恢复

ORA-600 kcbzpbuf_1故障恢复

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

标题:ORA-600 kcbzpbuf_1故障恢复

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

数据库启动报错ORA-03113

SQL> startup;
ORACLE instance started.

Total System Global Area 5.1310E+10 bytes
Fixed Size                  2265224 bytes
Variable Size            1.8119E+10 bytes
Database Buffers         3.3152E+10 bytes
Redo Buffers               36069376 bytes
Database mounted.

ORA-03113: end-of-file on communication channel
Process ID: 117892
Session ID: 568 Serial number: 3

分析alert日志发现ORA-600 kcbzpbuf_1报错

Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 4 Seq 4744 Reading mem 0
  Mem# 0: /home/oradata/redo04.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4745 Reading mem 0
  Mem# 0: /home/oradata/redo01.log
Wed Jan 11 14:44:35 2023
Hex dump of (file 87, block 3143379) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_116740.trc
Corrupt block relative dba: 0x15eff6d3 (file 87, block 3143379)
Bad header found during preparing block for write
Data in bad block:
 type: 0 format: 2 rdba: 0x00000000
 last change scn: 0x0b7e.593518d5 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x18d50001
 check value in block header: 0x342b
 computed block checksum: 0x0
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_116740.trc  (incident=553128):
ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_553128/orcl_dbw0_116740_i553128.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/orcl/orcl/trace/orcl_dbw0_116740.trc:
ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], [], [], [], [], []
DBW0 (ospid: 116740): terminating the instance due to error 471
Wed Jan 11 14:44:36 2023
System state dump requested by (instance=1, osid=116740 (DBW0)), summary=[abnormal instance termination].
Instance terminated by DBW0, pid = 116740

错误比较明显,在应用日志的时候,redo和数据文件的block不匹配,从而出现Corrupt block relative dba: 0x15eff6d3 (file 87, block 3143379)问题,通过bbed对该block进行修复,数据库直接recover成功

RMAN> recover database;

Starting recover at 2023-01-11 14:53:44
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 2023-01-11 14:53:45

数据库open成功

SQL> alter database open;

Database altered.

数据库报ORACLE Instance orcl (pid = 14)类似错误

Thread 1 opened at log sequence 4745
  Current log# 1 seq# 4745 mem# 0: /home/oradata/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Jan 11 14:54:10 2023
SMON: enabling cache recovery
[108954] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:2313624 end:2313634 diff:10 (0 seconds)
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_110633.trc  (incident=577160):
ORA-01578: ORACLE data block corrupted (file # 87, block # 3143379)
ORA-01110: data file 87: '/home/oradata/xifenfei04.dbf'
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Jan 11 14:54:10 2023
QMNC started with pid=80, OS id=114315
Completed: alter database open
Wed Jan 11 14:54:10 2023
db_recovery_file_dest_size of 4182 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.
ORACLE Instance orcl (pid = 14) - Error 1578 encountered while recovering transaction (10, 0) on object 156475.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_110633.trc:
ORA-01578: ORACLE data block corrupted (file # 87, block # 3143379)
ORA-01110: data file 87: '/home/oradata/xifenfei04.dbf'

对其异常对象进行分析,确认是回收站对象,清理回收站
20230111181445


数据库后续运行正常【alert日志没有其他报错】,该恢复完成,业务数据可以直接使用,数据0丢失
20230111181642