生产环境巡检发现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进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【MySQL 30周年庆】MySQL 8.0 OCP考试限时免费！教你免费领考券
墨天轮小教习
3263次阅读
2025-04-25 18:53:11
2025年4月国产数据库大事记：4个千万级中标项目诞生！2024年达梦净利3.6亿、金仓净利8006.6万……
墨天轮编辑部
2251次阅读
2025-04-30 17:39:54
外国CTO也感兴趣的开源数据库项目——openHalo
小满未满、
1966次阅读
2025-04-21 16:58:09
数据库国产化替代深化：DBA的机遇与挑战
代晓磊
1300次阅读
2025-04-27 16:53:22
2025 DBA 薪资观察：做 DBA 还香吗？
墨天轮编辑部
1148次阅读
2025-04-24 15:53:21
【专家有话说第六期】数据库考证到底有用么？国产时代DBA如何构建真实竞争力
墨天轮编辑部
1091次阅读
2025-05-06 17:50:06
2025年5月中国数据库流行度排行榜：OB一枝独秀破 800，金仓奋起直追跻四强
墨天轮编辑部
970次阅读
2025-05-13 10:55:54
MySQL 30 周年庆！MySQL 8.4 认证免费考！这次是认真的。。。
严少安
904次阅读
2025-04-25 15:30:58
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
786次阅读
2025-04-18 14:18:38
2025年4月国产数据库中标情况一览：4个千万元级项目，GaussDB与OceanBase大放异彩！
通讯员
776次阅读
2025-04-30 15:24:06
