ORA-00600: internal error code, arguments: [kqlnrc_1], [0x70000029A6C9410], [],

今天早上做資料庫巡檢，發現alert日誌裡出現如下錯誤：

Errors in file /home/oracle/admin/exmxsbusi/udump/exmxsbusi2_ora_27477.trc:
ORA-00600: 內部錯誤程式碼, 引數: [kqlnrc_1], [0x15E465678], [], [], [], [], [], []
Mon Jun 18 10:11:31 CST 2012
Errors in file /home/oracle/admin/exmxsbusi/udump/exmxsbusi2_ora_27477.trc:
ORA-00600: 內部錯誤程式碼, 引數: [kqlnrc_1], [0x15E465678], [], [], [], [], [], []
Mon Jun 18 10:11:55 CST 2012
Errors in file /home/oracle/admin/exmxsbusi/udump/exmxsbusi2_ora_27477.trc:
ORA-00600: 內部錯誤程式碼, 引數: [kqlnrc_1], [0x15E465678], [], [], [], [], [], []

檢視trace檔案：
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /home/oracle/db
System name: Linux
Node name: cpexmxsii-coddb-02
Release: 2.6.18-194.1.AXS3
Version: #1 SMP Fri May 7 10:03:53 CST 2010
Machine: x86_64
Instance name: exmxsbusi2
Redo thread mounted by this instance: 2
Oracle process number: 504
Unix process pid: 27477, image: oracle@cpexmxsii-coddb-02

*** ACTION NAME:(Main session) 2012-06-18 10:11:14.231
*** MODULE NAME:(PL/SQL Developer) 2012-06-18 10:11:14.231
*** SERVICE NAME:(exmxsbusi) 2012-06-18 10:11:14.231
*** SESSION ID:(155.2098) 2012-06-18 10:11:14.231
*** 2012-06-18 10:11:14.231
ksedmp: internal or fatal error
ORA-00600: 內部錯誤程式碼, 引數: [kqlnrc_1], [0x15E465678], [], [], [], [], [], []
Current SQL statement for this session:
declare
  c integer := 0;
  p1 varchar2(500);
  p2 varchar2(500);
  expr varchar2(500);
  dblink varchar2(500);
  part1_type integer;
  object_number integer;
  dp integer;
begin
  :object_type := null;
  :object_owner := null;
  :object_name := null;
  :sub_object := null;
  expr := :part1;
  if :part2 is not null then expr := expr || '.' || :part2; end if;
  if :part3 is not null then expr := expr || '.' || :part3; end if;
  loop
    begin
      sys.dbms_utility.name_resolve(name => expr,
                                    context => c,
                                    schema => :object_owner,
                                    part1 => p1,
                                    part2 => p2,
                                    dblink => dblink,
                                    part1_type => part1_type,
                                    object_number => object_number);
      if part1_type = 1 then :object_type := 'INDEX'; end if;
      if part1_type = 2 then :object_type := 'TABLE'; end if;
      if part1_type = 4 then :object_type := 'VIEW'; end if;
      if part1_type = 5 then :object_type := 'SYNONYM'; end if;
      if part1_type = 6 then :object_type := 'SEQUENCE'; end if;
      if part1_type = 7 then :object_type := 'PROCEDURE'; end if;
      if part1_type = 8 then :object_type := 'FUNCTION'; end if;
      if part1_type = 9 then :object_type := 'PACKAGE'; end if;
      if part1_type = 12 then :object_type := 'TRIGGER'; end if;
      if part1_type = 13 then :object_type := 'TYPE'; end if;
      if part1_type = 28 then :object_type := 'JAVA SOURCE'; end if;
      if part1_type = 29 then :object_type := 'JAVA CLASS'; end if;
      if :object_type is null then
        select object_type into :object_type
          from sys.all_objects
         where object_id = object_number;
      end if;
    exception
      when others then null;
    end;
    c := c + 1;
    if c > 9 then
      dp := instr(expr, '.', -1);
      if dp > 0 then
        if :sub_object is not null then
          :sub_object := '.' || :sub_object;
        end if;
        :sub_object := upper(substr(expr, dp + 1)) || :sub_object;
        expr := substr(expr, 1, dp - 1);
        c := 0;
      end if;
    end if;
    exit when (:object_type is not null) or (c > 9);
  end loop;
  if :object_type is not null then
    if p1 is null then
      :object_name := p2;
    elsif p2 is null then
      :object_name := p1;
      if :object_name = :part1 and :part2 is not null then
        :sub_object := :part2;
      end if;
      if :object_name = :part2 and :part3 is not null then
        :sub_object := :part3;
      end if;  
    else
      :object_name := p1;
      :sub_object := p2;
    end if;
    return;
  end if;
  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; 
  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 = :cur_schema
           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;
end;

再看

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x15d17ad68       116  package body SYS.DBMS_UTILITY

......

......

      SO: 0x13667b708, type: 54, owner: 0x149fa2a30, flag: INIT/-/-/0x00
      LIBRARY OBJECT PIN: pin=0x13667b708 handle=0x15e465678 mode=S lock=11a0570c8
      user=15a46c3d0 session=15a46c3d0 count=1 mask=0001 savepoint=0x7675 flags=[00]
      LIBRARY OBJECT HANDLE: handle=15e465678 mtx=0x15e4657a8(0) lct=1 pct=0 cdp=0
      name=EXMXSQUERY.VW_EBAY_MAIL_CLCT@EBAYTRACK
      hash=f0cd4f621dedeac376c5bc759c015f5e timestamp=12-22-2011 17:03:45
      namespace=TABL flags=REM/KGHP/TIM/XLR/[00020020]
      kkkk-dddd-llll=0000-0001-0001 lock=S pin=S latch#=14 hpc=0002 hlc=0002
      lwt=0x15e465720[0x15e465720,0x15e465720] ltm=0x15e465730[0x15e465730,0x15e465730]
      pwt=0x15e4656e8[0x15e4656e8,0x15e4656e8] ptm=0x15e4656f8[0x15e4656f8,0x15e4656f8]
      ref=0x15e465750[0x15e465750,0x15e465750] lnd=0x15e465768[0x15e465768,0x15e465768]
        LOCK INSTANCE LOCK: id=LBf0cd4f621dedeac3
        PIN INSTANCE LOCK: id=NBf0cd4f621dedeac3 mode=S release=F flags=[00]
        LIBRARY OBJECT: bject=1432efc78
        type=SYNM flags=EXS/LOC[0005] pflags=[0000] status=INVL load=0
        DATA BLOCKS:

看到這個地方，VW_EBAY_MAIL_CLCT是EBAYTRACK庫上EXMXSQUERY使用者下的一個同義詞，應該是這個同義詞失效了，一般同義詞失效是其同義的物件刪掉重建了，或者select許可權收回了。為了證實這個認識，打電話詢問開發的工程師當時有沒有改動那個檢視，回答時肯定的，說但是那個時間點正在create or replace，這樣同義詞就自動失效了。通過如下sql查詢：

SQL> SELECT object_name,object_type,owner,status
  2            FROM   dba_objects
  3            WHERE  object_name='VW_EBAY_MAIL_CLCT';

OBJECT_NAME                    OBJECT_TYPE         OWNER      STATUS
--------------------                        -------------------         ----------          -------
VW_EBAY_MAIL_CLCT              VIEW                  EMSTRC         VALID
VW_EBAY_MAIL_CLCT              SYNONYM        EMSQUERY    INVALID

果然失效了。注意在10g中，同義詞失效，但是還是可以查詢的：

select  count(*)  from vw_ebay_mail_clct;

  COUNT(*)
------------------
         3398700

重建同義詞：

create or replace synonym VW_EBAY_MAIL_CLCT for emstrc.VW_EBAY_MAIL_CLCT;

 重新查詢狀態：

SQL> SELECT object_name,object_type,owner,status
  2            FROM   dba_objects
  3            WHERE  object_name='VW_EBAY_MAIL_CLCT';

OBJECT_NAME                    OBJECT_TYPE         OWNER      STATUS
--------------------                        -------------------         ----------          -------
VW_EBAY_MAIL_CLCT              VIEW                  EMSTRC         VALID
VW_EBAY_MAIL_CLCT              SYNONYM        EMSQUERY    VALID

狀態已經變為valid了。

參考文件：

How To Find The Object That Causing ORA-600 [kqlnrc_1] [ID 1190673.1]

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12129601/viewspace-733241/，如需轉載，請註明出處，否則將追究法律責任。