生产环境巡检发现alert日志发现ora-600 [kpncxcc-1], [12], [5], [7]，mos搜索未发现相关文档自己分析测试一下。
一、数据库报错
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_126023.trc (incident=3297342):
ORA-00600: 内部错误代码, 参数: [kpncxcc-1], [12], [5], [7], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_3297342/orcl1_ora_126023_i3297342.trc
2023-03-30T22:21:27.468661+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-03-30T22:22:01.689354+08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2023-03-30T22:22:02.041203+08:00
Dumping diagnostic data in directory=[cdmp_20230330222202], requested by (instance=1, osid=126023), summary=[incident=3297342].
2023-03-30T22:22:25.222870+08:00
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_126023.trc (incident=3297343):
ORA-00600: 内部错误代码, 参数: [kpncxcc-1], [12], [5], [7], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_3297343/orcl1_ora_126023_i3297343.trc
2023-03-30T22:22:26.812063+08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2023-03-30T22:22:33.145742+08:00
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_126023.trc (incident=3297344):
ORA-00600: 内部错误代码, 参数: [kpncxcc-1], [12], [5], [7], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_3297344/orcl1_ora_126023_i3297344.trc
2023-03-30T22:22:34.716414+08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2023-03-30T22:23:04.762995+08:00
LGWR (PID:58568): SRL selected to archive T-1.S-108890
LGWR (PID:58568): SRL selected for T-1.S-108890 for LAD:2
2023-03-30T22:23:04.796678+08:00
Thread 1 advanced to log sequence 108890 (LGWR switch), current SCN: 16366158269708
Current log# 3 seq# 108890 mem# 0: +DATADG/orcl/ONLINELOG/group_3.263.1082216491
Current log# 3 seq# 108890 mem# 1: +ARCHDG/orcl/ONLINELOG/group_3.389.1082216495
2023-03-30T22:23:06.378766+08:00
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_126023.trc (incident=3297345):
ORA-00600: 内部错误代码, 参数: [kpncxcc-1], [12], [5], [7], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_3297345/orcl1_ora_126023_i3297345.trc
2023-03-30T22:23:07.794571+08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2023-03-30T22:23:08.171584+08:00
Dumping diagnostic data in directory=[cdmp_20230330222308], requested by (instance=1, osid=126023), summary=[incident=3297345].
二、检查incident日志，找到报错sql
----- Current SQL Statement for this session (sql_id=1z2zftmbax53q) -----
select * from cy.test1@to_cy
where rowid = :plsqldev_rowid
三、测试该sql
SQL> select count(*) from cy.test1@to_cy;
COUNT(*)
----------
10853
SQL> select * from cy.test1@to_cy;
STORE_ID ORG_ID STATUS STATUS_DATE SHARE_TYPE EXP_DATE EFF_DATE
-------------------------------- -------------------------------- -------------------------------- ------------------- -------------------------------- ------------------- -------------------
test.03.S01 test.03.05.02.23 VALID 2023-03-30 15:54:47 SPECIFIED
test.03.S01 test.03.05.02.24 VALID 2023-03-30 15:54:47 SPECIFIED
test.03.S01 test.03.05.02.25 VALID 2023-03-30 15:54:47 SPECIFIED
test.03.S01 test.03.05.02.26 VALID 2023-03-30 15:54:47 SPECIFIED
test.03.S01 test.03.05.02.27 VALID 2023-03-30 15:54:47 SPECIFIED
test.03.S01 test.03.05.02.28 VALID 2023-03-30 15:54:47 SPECIFIED
test.03.S01 test.03.05.02.29 VALID 2023-03-30 15:54:47 SPECIFIED
test.03.S01 test.03.05.06 VALID 2023-03-30 15:54:47 SPECIFIED
test.03.S01 test.03.06 VALID 2023-03-30 15:54:47 SPECIFIED
ERROR:
ORA-00600: internal error code, arguments: [kpncxcc-1], [12], [5], [7], [], [], [], [], [], [], [], []
四、按列测试
SQL> select distinct STATUS_DATE from cy.test1@to_cy;
STATUS_DATE
-------------------
2023-03-30 15:54:50
2021-06-15 10:45:49
2022-08-19 11:19:33
2022-08-16 18:25:08
2022-10-01 16:42:59
2022-08-16 18:01:48
2021-06-15 10:47:21
2021-12-01 15:30:02
^CERROR:
ORA-01013: user requested cancel of current operation
ORA-00600: internal error code, arguments: [kpncxcc-1], [12], [5], [7], [], [], [], [], [], [], [], []
45 rows selected.
五、去目标库测试
SQL> select distinct STATUS_DATE from cy.test1;
STATUS_DA
---------
01-DEC-21
ERROR:
ORA-01858: a non-numeric character was found where a numeric was expected
45 rows selected.
六、带上rowid测试
SQL> select rowid,rownum, STATUS_DATE from cy.test1 ;
AAIwcpABUAAOwmoAAE 2278 18-AUG-22
AAIwcpABUAAOwmoAAF 2279 18-AUG-22
AAIwcpABUAAOwmoAAG 2280 18-AUG-22
ERROR:
ORA-01858: a non-numeric character was found where a numeric was expected
SQL> select STATUS_DATE from cy.test1 where rowid='AAIwcpABUAAOwmoAAH';
ERROR:
ORA-01858: a non-numeric character was found where a numeric was expected
七、dump看下
SQL> select dump(ORG_ID),dump(STATUS_DATE) from cy.test1 where rowid='AAIwcpABUAAOwmoAAH';
DUMP(ORG_ID)
--------------------------------------------------------------------------------
DUMP(STATUS_DATE)
--------------------------------------------------------------------------------
Typ=1 Len=15: 78,88,46,48,49,46,48,54,46,48,51,46,49,48,48 -->test.01.06.03.100-->4e 58 2e 30 36 2e 30 06 2e 31 30 30
Typ=12 Len=5: 86,65,76,73,68 -->VALID
SQL> select dump(ORG_ID),dump(STATUS_DATE) from cy.test1 where rowid='AAIwcpABUAAOwmoAAA';
DUMP(ORG_ID)
--------------------------------------------------------------------------------
DUMP(STATUS_DATE)
--------------------------------------------------------------------------------
Typ=1 Len=15: 78,88,46,48,49,46,48,55,46,48,51,46,52,51,56 -->test.01.07.03.438 -->4e 58 2e 30 31 2e 30 37 2e 30 33 2e 34 33 38
Typ=12 Len=7: 120,122,8,16,18,38,10 -->时间
SQL> select dump(ORG_ID),dump(STATUS_DATE),ORG_ID,STATUS_DATE from cy.test1 where rowid='AAIwcpABUAAOwmoAAA';
DUMP(ORG_ID)
--------------------------------------------------------------------------------
DUMP(STATUS_DATE)
--------------------------------------------------------------------------------
ORG_ID STATUS_DA
-------------------------------- ---------
Typ=1 Len=15: 78,88,46,48,49,46,48,55,46,48,51,46,52,51,56
Typ=12 Len=7: 120,122,8,16,18,38,10
test.01.07.03.438 16-AUG-22
select * from cy.test1 where STATUS_DATE='VALID';
SQL> select * from cy.test1 where STATUS_DATE='VALID';
select * from cy.test1 where STATUS_DATE='VALID'
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
select rowid,dump(STATUS_DATE) from cy.test1;
SQL> select rowid,dump(STATUS_DATE) from cy.test1;
ROWID DUMP(STATUS_DATE)
------------------ ------------------------------------------------------------
AAIwcpABUAAOwmoAAG Typ=12 Len=7: 120,122,8,18,11,35,51
AAIwcpABUAAOwmoAAH Typ=12 Len=5: 86,65,76,73,68
AAIwcpABUAAOwmpAAD Typ=12 Len=7: 120,122,6,23,10,53,29
只有这行的STATUS_DATE列存储为’86,65,76,73,68’,转换卫字符是VALID。对比所有相关行，只有这行dump结果为86,65,76,73,68。
SQL> select dbms_rowid.ROWID_OBJECT(rowid) data_object_id#,dbms_rowid.ROWID_RELATIVE_FNO(rowid) rfile#, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#, dbms_rowid.ROWID_ROW_NUMBER(rowid) row#, rowid from cy.test1 where rowid='AAIwcpABUAAOwmoAAH';
DATA_OBJECT_ID# RFILE# BLOCK# ROW# ROWID
--------------- ---------- ---------- ---------- ------------------
2295593 84 3869096 7 AAIwcpABUAAOwmoAAH
SQL> alter system dump datafile 84 block 3869096;
System altered.
数据块搜索4e 58 2e 30 36 2e 30 06 2e 31 30 30，为发现，应该是还没有写入
col 0: [ 3] c2 0a 34
col 1: [17] 31 30 34 35 35 32 39 30 31 32 33 30 36 33 33 33 38
col 2: [12] 72 73 63 6c 52 44 5a 43 7a 6a 33 30
col 3: [ 8] 4f 55 54 53 54 4f 52 45
col 4: [ 6] 4e 4f 52 4d 41 4c
col 5: [ 7] 78 6f 02 15 11 10 31
col 6: [ 4] 53 41 4c 45
col 7: [ 7] 33 33 33 38 32 39 38
col 8: [ 1] 31
col 9: [14] 4e 58 2e 30 31 2e 30 36 2e 30 33 2e 35 33 --test.01.06.03.53
col 10: [14] 4e 58 2e 30 31 2e 30 36 2e 30 33 2e 35 33
col 11: [ 6] 4e 4f 52 4d 41 4c
col 12: [ 4] 55 53 45 44
col 13: *NULL*
col 14: *NULL*
col 15: *NULL*
col 16: *NULL*
col 17: [ 7] 78 6f 0c 08 01 01 01
col 18: [ 7] 78 6e 0c 09 01 01 01
col 19: [ 7] 78 70 03 1f 01 01 01
col 20: *NULL*
col 21: *NULL*
col 22: [ 4] 55 53 45 44
col 23: [ 6] 61 6b 66 30 30 31
col 24: [ 2] 4e 58 -->test
col 25: [ 7] 78 6e 0c 0a 02 16 2b
col 26: [11] 32 30 30 38 72 66 74 74 65 73 74
col 27: *NULL*
col 28: [ 1] 80
col 29: [ 1] 80
col 30: *NULL*
col 31: *NULL*
col 32: *NULL*
col 33: [11] 31 35 30 30 38 36 36 36 34 34 36
col 34: *NULL*
col 35: [11] 31 35 30 30 38 36 36 36 34 34 36
col 36: *NULL*
col 37: [ 7] 78 6f 02 1a 18 33 2c
col 38: *NULL*
col 39: *NULL*
col 40: [ 6] 4e 4f 52 4d 41 4c'
那么这里推测[kpncxcc-1], [12], [5], [7]分别代表块类型type，目前行长度length和目标行长度7
八、复现
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
1、确定数据所在块
SQL> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid),dbms_rowid.ROWID_RELATIVE_FNO(rowid) from emp;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
5406 7
5406 7
5406 7
5406 7
5406 7
5406 7
5406 7
5406 7
5406 7
5406 7
5406 7
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
5406 7
5406 7
5406 7
14 rows selected.
2、bbed测试
BBED> set dba 7,5403
DBA 0x01c0151b (29365531 7,5403)
BBED> map /v
File: /u01/app/oracle/oradata/ORCL/users01.dbf (7)
Block: 5403 Dba:0x01c0151b
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub2 wrp2_kcbh @2
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
3、修改数据
BBED> p kdbr
sb2 kdbr[0] @118 8050
sb2 kdbr[1] @120 8007
sb2 kdbr[2] @122 7964
sb2 kdbr[3] @124 7923
sb2 kdbr[4] @126 7878
sb2 kdbr[5] @128 7837
sb2 kdbr[6] @130 7796
sb2 kdbr[7] @132 7756
sb2 kdbr[8] @134 7718
sb2 kdbr[9] @136 7675
sb2 kdbr[10] @138 7637
sb2 kdbr[11] @140 7599
sb2 kdbr[12] @142 7560
sb2 kdbr[13] @144 7521
BBED> p *kdbr[0]
rowdata[529]
------------
ub1 rowdata[529] @8150 0x2c
BBED> x /rnccntnnnnnnnnnn
rowdata[529] @8150
------------
flag@8150: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8151: 0x01
cols@8152: 8
col 0[3] @8153: 7369
col 1[5] @8157: SMITH
col 2[5] @8163: CLERK
col 3[3] @8169: 7902
col 4[7] @8173: 17-DEC-80
col 5[2] @8181: 800
col 6[0] @8184: *NULL*
col 7[2] @8185: 20
BBED> set offset 8174
OFFSET 8174
BBED> d
File: /u01/app/oracle/oradata/ORCL/users01.dbf (7)
Block: 5406 Offsets: 8174 to 8191 Dba:0x01c0151e
------------------------------------------------------------------------
77b40c11 01010102 c209ff02 c1150106 efbf
BBED> modify /c VALID
File: /u01/app/oracle/oradata/ORCL/users01.dbf (7)
Block: 5406 Offsets: 8174 to 8191 Dba:0x01c0151e
------------------------------------------------------------------------
56414c49 44303030 3030ff02 c1150106 efbf
<32 bytes per line>
BBED> sum apply
Check value for File 7, Block 5406:
current = 0x962b, required = 0x962b
BBED>
4、查询
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from scott.emp;
ERROR:
ORA-01858: a non-numeric character was found where a numeric was expected
no rows selected
SQL> select dump(HIREDATE) from scott.emp where empno=7369;
DUMP(HIREDATE)
-------------------------------------
Typ=12 Len=7: 86,65,76,73,68,48,48
最后修改时间：2023-04-19 11:19:19
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
