Sunday, February 07, 2010

ORA-00600: internal error code, arguments: [17069]

Found this error, after changed meta data on TABLES and recompiled many stored procedures.
Errors in file /oracle/admin/db/udump/db1_ora_11420.trc:
ORA-00600: internal error code, arguments: [17069], [0x08071B8D0], [], [], [], [], [], []
In trace file.

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORACLE_HOME = /oracle/10g
.
.
.
Unix process pid: 11420
.
.
.
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17069], [0x08071B8D0], [], [], [], [], [], []
Current SQL statement for this session:
BEGIN :1 := PKG_TMP01.PRC_TMP01(
:2 ,
:3 )
; END;
----- PL/SQL Call Stack -----

Check PKG_TMP01 package on Database.
SQL> select object_name,object_type, status from dba_objects where object_name='PKG_TMP01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
PKG_TMP01 PACKAGE VALID
PKG_TMP01 PACKAGE BODY INVALID
SQL> alter package PKG_TMP01 compile body;

Package body altered.
But still Invalid...
SQL> select object_name,object_type, status from dba_objects where object_name='PKG_TMP01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
PKG_TMP01 PACKAGE VALID
PKG_TMP01 PACKAGE BODY INVALID
Check Oracle Support report Bug 4587556 (10.2.0.3), Fixed in 10.2.0.4 (Server Patch Set),11.1.0.6 (Base Release)

However, I can not patch yet, so checked Workaround: Compile packages which have a self referencing synonym explicitly from SQL before compiling any other dependant packages.
But I didn't find synonym invalid status and made script to compile all:

set lines 130
set pages 0
spool recompile.sql
select 'alter '|| object_type||' '||owner||'.'||object_name ||' compile;' from dba_objects where status='INVALID' and object_type != 'PACKAGE BODY';
select 'alter package '|| owner||'.'||object_name ||' compile body;' from dba_objects where status='INVALID' and object_type = 'PACKAGE BODY';
select 'alter '|| object_type||' "'||owner||'"."'||object_name ||'" compile;' from dba_objects where status='INVALID' and object_type != 'PACKAGE BODY' and owner='PUBLIC';
spool off;
@recompile.sql

Checked again:
SQL> select object_name,object_type, status from dba_objects where object_name='PKG_TMP01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
PKG_TMP01 PACKAGE VALID
PKG_TMP01 PACKAGE BODY INVALID
Package body status was invalid. Then got idea about this bug report.
ERROR:
ORA-600 [17069] [a]

DESCRIPTION:
Failed to pin a library cache object after 50 attempts.

ARGUMENTS:
Arg [a] Library Cache Object Handle.
This bug is about library cache, So Flushed shared pool and recompiled again.
SQL> alter system flush shared_pool;

SQL> alter package PKG_TMP01 compile body;

Package body altered.

SQL> select object_name,object_type, status from dba_objects where object_name='PKG_TMP01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
PKG_TMP01 PACKAGE VALID
PKG_TMP01 PACKAGE BODY VALID
That's a good thing to solve this issue. Perhaps I recompiled synonyms refer this package before. And flush shared pool helped database be able to recompile.

No comments: