Tag Archives: ora600

ORA-600 [kqlnrc_1]错误分析

群里朋友提出一个问题:数据库遇到ORA-00600: internal error code, arguments: [kqlnrc_1], [0x7000000DCA26B38], [], [], [], [], [], []错误,请求解决。
MOS上关于ORA-600 [kqlnrc_1]错误有详细的文档说明:How To Find The Object That Causing ORA-600 [kqlnrc_1] [ID 1190673.1]

根据文档说明,一步一步寻找问题的根源:
1.Trace文件中找到ORA-600报错,顺便看了下,是AIX上的10.2.0.4.0的库

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 ORACLE_HOME = /oracle/app/product/10.2.0/db_1
 System name: AIX
 Node name: tobadb88
 Release: 3
 Version: 5
 Machine: 00CCA0B44C00
 Instance name: han_tocco
 Redo thread mounted by this instance: 1
 Oracle process number: 85
 Unix process pid: 1737624, image: [email protected]
*** 2011-12-12 16:02:56.457
 ksedmp: internal or fatal error
 ORA-00600: internal error code, arguments: [kqlnrc_1], [0x7000000DCA26B38], [], [], [], [], [], []
 Current SQL statement for this session:

2.查询0x7000000DCA26B38
直接搜索没有任何结果,经分析,在trace文件内的handle对应的值字母为小写,故搜索不到,重新搜索即可找到INVALID对象:

SO: 7000005d0479c98, type: 53, owner: 7000005e51bf988, flag: INIT/-/-/0x00
 LIBRARY OBJECT LOCK: lock=7000005d0479c98 handle=7000000dca26b38 mode=S
 call pin=7000005d6d0e638 session pin=0 hpc=0000 hlc=0000
 htl=7000005d0479d18[7000005d9176060,7000005d9176060] htb=7000005d9176060 ssga=7000005d9175aa8
 user=70000060b786c98 session=70000060b786c98 count=1 flags=PNC/[0400] savepoint=0x1cb8
 LIBRARY OBJECT HANDLE: handle=7000000dca26b38 mtx=7000000dca26c68(0) cdp=0
 [email protected]_DBGIS
 hash=01539fc30d3e6ae740e53e09dddbce4a timestamp=04-25-2009 17:35:49
 namespace=TABL flags=REM/KGHP/TIM/SML/[02020000]
 kkkk-dddd-llll=0000-0001-0001 lock=S pin=S latch#=17 hpc=0002 hlc=0002
 lwt=7000000dca26be0[7000000dca26be0,7000000dca26be0] ltm=7000000dca26bf0[7000000dca26bf0,7000000dca26bf0]
 pwt=7000000dca26ba8[7000000dca26ba8,7000000dca26ba8] ptm=7000000dca26bb8[7000000dca26bb8,7000000dca26bb8]
 ref=7000000dca26c10[7000000dca26c10,7000000dca26c10] lnd=7000000dca26c28[7000000dca26c28,7000000dca26c28]
 LIBRARY OBJECT: object=7000000d69413b0
 type=SYNM flags=EXS/LOC[0005] pflags=[0000] status=INVL load=0

Library cache中的失效对象为CRM_INTF.NW_SYN_INTERFACE
由于不是自己的库,后面的compile过程无法实现,不过已经找到了失效对象,剩下的工作已经不再复杂。

总结:
大小写,空格,减号,下划线,每一点,一个细节,都要务必严谨。
BTW,我是通过status=INVL找到的,有时,换一种角度思考,也会有不错的效果。

Oracle 9.2.0.8.0 – ORA-600 – [17113]错误

某省级电信运营商核心生产库。
根据实例CRM1上的告警日志中记录,2011年12月1日08:37-08:43之间,出现了6次ORA-00600错误。

Thu Dec 1 08:37:52 2011
 Errors in file /oracle9/app/admin/crm/udump/crm1_ora_1995124.trc:
 ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []
 Thu Dec 1 08:37:52 2011
 Trace dumping is performing id=[cdmp_20111201083752]
 Thu Dec 1 08:38:21 2011
 Errors in file /oracle9/app/admin/crm/udump/crm1_ora_1995124.trc:
 ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []
 Thu Dec 1 08:39:47 2011
 Errors in file /oracle9/app/admin/crm/udump/crm1_ora_2982400.trc:
 ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []
 Thu Dec 1 08:41:32 2011
 Errors in file /oracle9/app/admin/crm/udump/crm1_ora_1995124.trc:
 ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []
 Thu Dec 1 08:42:13 2011
 Errors in file /oracle9/app/admin/crm/udump/crm1_ora_1995124.trc:
 ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []
 Thu Dec 1 08:43:03 2011
 Thread 1 advanced to log sequence 197157
 Current log# 2 seq# 197157 mem# 0: /dev/rcrm_redo012lv
 Current log# 2 seq# 197157 mem# 1: /dev/rcrm_redo022lv
 Thu Dec 1 08:43:03 2011
 ARC1: Evaluating archive log 5 thread 1 sequence 197156
 ARC1: Beginning to archive log 5 thread 1 sequence 197156
 Creating archive destination LOG_ARCHIVE_DEST_1: '/arch01/1_197156.dbf'
 Thu Dec 1 08:43:37 2011
 Errors in file /oracle9/app/admin/crm/udump/crm1_ora_1995124.trc:
 ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []

这个错误多为使用第三方工具(如PLSQL Developer,SQL Navigator)时出现的,尤其是工具的版本和RDBMS的版本有冲突时最可能出现。
根据错误中的提示,我们获取了跟踪文件/oracle9/app/admin/crm/udump/crm1_ora_1995124.trc,
从中可以发现,出现错误时,使用的客户端为PL/SQL Developer,其hash value与MOS上示例的值一致。

O/S info: user: Administrator, term: PC2011100510NLE, ospid: 2676:2564, machine: WORKGROUP\PC2011100510NLE
 program: plsqldev.exe
 application name: PL/SQL Developer, hash value=1190136663
 action name: Main session, hash value=1773317990

检查出现ORA-00600错误时的另一个跟踪日志/oracle9/app/admin/crm/udump/crm1_ora_2982400.trc,application name/action name记录的信息与另一个跟踪日志一致。

O/S info: user: Administrator, term: PC2011100510NLE, ospid: 3168:3188, machine: WORKGROUP\PC2011100510NLE
 program: plsqldev.exe
 application name: PL/SQL Developer, hash value=1190136663
 action name: Main session, hash value=1773317990

出现ORA-00600错误时,执行的SQL为:

Cursor 5 (110360558): CURBOUND  curiob: 11037fc78
curflg: 4c curpar: 0 curusr: 0 curses 700000c3b72b308
cursor name: 
declare
  t_owner varchar2(30);
  t_name  varchar2(30);
  procedure check_mview is
    dummy integer;
  begin
    if :object_type = 'TABLE' then
      select 1 into dummy
      from sys.all_objects
      where owner = :object_owner
      and object_name = :object_name
      and object_type = 'MATERIALIZED VIEW'
      and rownum = 1;
      :object_type := 'MATERIALIZED VIEW';
    end if;
  exception
    when others then null;
  end;
begin
  :sub_object := null;
  if :deep != 0 then
    begin
      if :part2 is null then
        select constraint_type, owner, constraint_name
          into :object_type, :object_owner, :object_name
          from sys.all_constraints c
         where c.constraint_name = :part1 and c.owner = user
           and rownum = 1;
      else
        select constraint_type, owner, constraint_name, :part3
          into :object_type, :object_owner, :object_name, :sub_object
          from sys.all_constraints c
         where c.constraint_name = :part2 and c.owner = :part1
           and rownum = 1;
      end if;
      if :object_type = 'P' then :object_type := 'PRIMARY KEY'; end if;
      if :object_type = 'U' then :object_type := 'UNIQUE KEY'; end if;
      if :object_type = 'R' then :object_type := 'FOREIGN KEY'; end if;
      if :object_type = 'C' then :object_type := 'CHECK CONSTRAINT'; end if;        
      return;
    exception
      when no_data_found then null;
    end;
  end if;
  :sub_object := :part2;
  if (:part2 is null) or (:part1 != user) then
    begin
      select object_type, user, :part1
      into :object_type, :object_owner, :object_name
      from sys.all_objects
      where owner = user
      and object_name = :part1
      and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
      and rownum = 1;
      if :object_type = 'SYNONYM' then
        select s.table_owner, s.table_name
          into t_owner, t_name
          from sys.all_synonyms s
         where s.synonym_name = :part1
           and s.owner = user
           and rownum = 1;
        select o.object_type, o.owner, o.object_name
          into :object_type, :object_owner, :object_name
          from sys.all_objects o 
         where o.owner = t_owner
           and o.object_name = t_name
           and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
           and rownum = 1;
      end if;
      :sub_object := :part2;
      if :part3 is not null then
        :sub_object := :sub_object || '.' || :part3;
      end if;
      check_mview;
      return;
    exception
      when no_data_found then null;
    end;
  end if;
  begin
    select s.table_owner, s.table_name
      into t_owner, t_name
      from sys.all_synonyms s
     where s.synonym_name = :part1
       and s.owner = 'PUBLIC'
       and rownum = 1;
    select o.object_type, o.owner, o.object_name
      into :object_type, :object_owner, :object_name
      from sys.all_objects o 
     where o.owner = t_owner
       and o.object_name = t_name
       and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
       and rownum = 1;
    check_mview;
    return;
  exception
    when no_data_found then null;
  end;
  :sub_object := :part3;
  begin
    select o.object_type, o.owner, o.object_name
      into :object_type, :object_owner, :object_name
      from sys.all_objects o
     where o.owner = :part1
       and o.object_name = :part2
       and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
       and rownum = 1;
    check_mview;
    return;
  exception
    when no_data_found then null;
  end;
  begin
    if :part2 is null and :part3 is null
    then
      select 'USER', null, :part1
      into :object_type, :object_owner, :object_name
      from sys.all_users u
      where u.username = :part1
      and rownum = 1;
      return;
    end if;
  exception
    when no_data_found then null;
  end;
  begin
    if :part2 is null and :part3 is null and :deep != 0
    then
      select 'ROLE', null, :part1
      into :object_type, :object_owner, :object_name
      from sys.session_roles r
      where r.role = :part1
      and rownum = 1;
      return;
    end if;
  exception
    when no_data_found then null;
  end;
  :object_owner := null;
  :object_type := null;
  :object_name := null;
  :sub_object := null;
end;

结论:

综合MOS和eygle博客上的信息,判断此为一个与PL/SQL Developer有关的Bug,建议客户选择合适版本的PL/SQL Developer进行操作。

参考:
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=REFERENCE&id=420844.1
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=154170.1
http://www.eygle.com/archives/2009/02/ora_07445_plsql_developer.html

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress