Pages

Monday, August 17, 2015

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

=========================
General
=========================
Oracle version is 11.1.x
In alert.log there are constantly, every minute these error messages:
ORA-00600: internal error code, arguments: [17059],[],[]

For each error message a trc file is generated, until system is 100% full.

=========================
Solution
=========================
The issue is related to Oracle bug, resolved in 11.2.x.
The problem is with SQL statement(s) having too many child.
The solution is to flush the shared pool:

ALTER SYSTEM FLUSH SHARED_POOL;

After the shared pool was flushed, trc files stopped to be generated.

=========================
Evidences
=========================

From alert.log
Fri Aug 14 00:33:29 2015
Errors in file /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_ora_16366.trc  (incident=289308):
ORA-00600: internal error code, arguments: [17059], [0x247F4DA60], [0x247F4D428], [0x257A51948], [], [], [], [], [], [], [], []
Fri Aug 14 00:33:59 2015
Errors in file /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_ora_18479.trc  (incident=289309):
ORA-00600: internal error code, arguments: [17059], [0x247F4DA60], [0x247F4D428], [0x257A51948], [], [], [], [], [], [], [], []
Fri Aug 14 00:34:00 2015
Sweep Incident[289309]: completed

files under /software/oracle/diag/rdbms/igt/igt/trace/
-rw-r----- 1 oracle dba      170 Aug 17 13:36 orainst_ora_18080.trm
-rw-r----- 1 oracle dba  1280223 Aug 17 13:36 orainst_ora_18080.trc
-rw-r----- 1 oracle dba      148 Aug 17 13:37 orainst_ora_20183.trm
-rw-r----- 1 oracle dba  1280194 Aug 17 13:37 orainst_ora_20183.trc
-rw-r----- 1 oracle dba      160 Aug 17 13:37 orainst_ora_23175.trm
-rw-r----- 1 oracle dba  1280194 Aug 17 13:37 orainst_ora_23175.trc
-rw-r----- 1 oracle dba      148 Aug 17 13:38 orainst_ora_25198.trm
-rw-r----- 1 oracle dba  1280194 Aug 17 13:38 orainst_ora_25198.trc
-rw-r----- 1 oracle dba      149 Aug 17 13:38 orainst_ora_28348.trm
-rw-r----- 1 oracle dba  1280223 Aug 17 13:38 orainst_ora_28348.trc
-rw-r----- 1 oracle dba      148 Aug 17 13:39 orainst_ora_30470.trm
-rw-r----- 1 oracle dba  1280223 Aug 17 13:39 orainst_ora_30470.trc
-rw-r----- 1 oracle dba      148 Aug 17 13:39 orainst_ora_31577.trm
-rw-r----- 1 oracle dba  1280194 Aug 17 13:39 orainst_ora_31577.trc
-rw-r----- 1 oracle dba      147 Aug 17 13:40 orainst_ora_3278.trm
-rw-r----- 1 oracle dba  1280192 Aug 17 13:40 orainst_ora_3278.trc
-rw-r----- 1 oracle dba      147 Aug 17 13:40 orainst_ora_6237.trm
-rw-r----- 1 oracle dba  1280221 Aug 17 13:40 orainst_ora_6237.trc
-rw-r--r-- 1 oracle dba        0 Aug 17 13:41 time_of_shared_pool_flush.txt

After the Shared pool was flushed, no more trc files:
oracle@lx263:/software/oracle/diag/rdbms/orainst/orainst/trace>% date
Mon Aug 17 13:51:09 CEST 2015
oracle@lx263:/software/oracle/diag/rdbms/orainst/orainst/trace>%


=========================
Making this run via crontab
=========================
crontab entry, to run once a month at 08:00
0 8 1 * * /software/oracle/oracle/scripts/flush_shared_pool.sh


flush_shared_pool.sh bash script, logging execution times and calling to sqlplus
oracle@my_server:~>% less /software/oracle/oracle/scripts/flush_shared_pool.sh
#!/bin/bash
. /software/oracle/oracle/.set_profile
export RUN_DATE=`date +"%Y%m%d"_"%H%M%S"`
export LOG_FILE=/software/oracle/oracle/scripts/flush_shared_pool.log

touch $LOG_FILE
echo "============================================" >> $LOG_FILE
echo Starting flush_shared_pool.sh at $RUN_DATE>> $LOG_FILE
sqlplus / as sysdba @/software/oracle/oracle/scripts/flush_shared_pool.sql
echo Finished flush_shared_pool.sh at $RUN_DATE>> $LOG_FILE
echo "============================================" >> $LOG_FILE

flush_shared_pool.sql file, doing actual work
oracle@my_server:~>% less /software/oracle/oracle/scripts/flush_shared_pool.sql 
ALTER SYSTEM FLUSH SHARED_POOL;
EXIT;

.set_profile file. 
It is needed because crontab is not aware of environment variables.
oracle@my_server:~>% less .set_profile 

export ORACLE_HOME=/software/oracle/111
export ORACLE_SID=ora_inst
export PATH=$PATH:/software/oracle/111/bin

=========================
Reference
=========================
In Oracle Metalink there are several technotes related to ORA-00600: internal error code, arguments: [17059]

Doc ID 973149.1: Select Fails With ORA-600 [17059] 

No comments:

Post a Comment