ORA-00600: internal error code, arguments: [%s], [%s],[%s], [%s], [%s] | How to resolve ORA-00600 error?

ORA-00600: internal error code, arguments: [%s], [%s],[%s], [%s], [%s] :

In my previous article i have explained about the most common error ORA-12154.In This article i will try to explain another most common error which has been searched approximately 50000 times in a month by oracle developers. While working with a database and performing different scenarios of database every developer or dba might have faced error called as ORA-00600: internal error code.While working with databases i have frequently faced ORA-00600: internal error code and struggled to solve and debug this issue.I would like to share my experience working and debugging with this error.

“ORA-600 error has been searched on google approximately 50 k times per month”

The generic error code will be displayed as :

ORA-00600: internal error code, arguments: [%s], [%s],[%s], [%s], [%s] 

where %s stands for string.

Why ORA-00600 error will come?

There are multiple reasons for which this error will occur.Actually ORA-00600 is generic internal error number for oracle program exceptions.This actually indicates that the process has unexpected conditions.The first argument is internal message number and other arguments are various numbers,names and character strings.The numbers may change meanings of different oracle versions.There should be multiple reasons of this error.I will mention few of them:

Reason 1 :

Timeouts 

While connecting with the database the timeout occurred or the query is running for long time and timeout occurred then this error will come.

Reason 2:

File Corruption 

When any oracle supported file has been corrupted then this error will come.

Reason 3:

Hardware/Memory/IO

When the memory is full or hardware is not compatible with oracle version then this error will come.

Reason 4:

Failed Data checks in the memory 

Because of failed data checks in the memory this error will occur.

Reason 5:

Incorrectly restored files.

If the oracle internal files restored not properly then this error will come.

There should be lot of reasons for ORA-00600 like compatibility problem to data block error.Most of the times these kind of errors remains unresolved.We can not be able to find the root cause of this issue.We need to call oracle support to resolve this issue in 60-70 percent of times.

NO TIME TO READ CLICK HERE TO GET THIS ARTICLE

Resolution of Error :

There is no specific reason for which ” ORA-00600: internal error code “error will come.For these kind of internal errors following activities is done by oracle engine :

1.Writes Error message in Alert Log :

The oracle engine will write the error in alert.log file.

2.Trace file :

Write detailed message in trace file.The trace file location will find in alert.log file.

3.Often you will see multiple errors reported within the space of a few minutes, typically starting with an ORA-600. It is usually, but not always, the case that the first is the significant error and the others are side effects.

If you are facing these kind of error first step is to check trace files and alert logs.There is no way to resolve these kind of issues so we need to do trial and error to resolve this issue.Finding the source of the problem is important. Sometimes you won’t be finding the source by checking the alert or trace logs. And neither these errors give you clear information about what exactly has happened with your server.

Following are some examples where i have faced this error and resolved.These examples might help developers and DBAs to resolve this issue.

Example 1 :

Error Code :

ORA-00600: internal error code, arguments: [15851], [3], [3499], [2], [1], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [15851], [3], [3499], [2], [1], [], [], [], [], [], [], []
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at “SIEBEL_ODS.MS_CCM_UTILITIES”, line 2866
ORA-04088: error during execution of trigger ‘SIEBEL_ODS.MS_CCM_TI_INVESTIGATION’
00600. 00000 –  “internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]”
*Cause:    This is the generic internal error number for Oracle program
exceptions.            This indicates that a process has encountered an
exceptional condition.
*Action:   Report as a bug – the first argument is the internal error number

Resolution :

one of the function used in trigger has variable size issue due to this we got above error,
this trigger created on merge statement table used in “_P” package. This error we will not track in any of the error log tables until if you run each and every statement outside of the package, we got socket error in si_application_error_log.

Example 2 :

ORA-00600: internal error code, arguments: [kxtotolc_lobopt], [], [], [], [], [], [], [], [], [], [], []
00600. 00000 –  “internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]”
*Cause:    This is the generic internal error number for Oracle program
exceptions.             This indicates that a process has encountered an
exceptional condition.
*Action:   Report as a bug – the first argument is the internal error number

Resolution :
If we are inserting/updating CLOB column value as null using merge statement, and merge statement table has trigger,same null CLOB column value if we try to insert/update CLOB value in another table using trigger then we will get the above error,also session is automatically closed, we got this error in both Oracle 11g and 12c.

Steps :

1) create table Clob_table(sno number, c_clob clob);

2) create table test_Clob_table_stg as select * from test_Clob_table;

3) Create a trigger on Clob_table table and insert data in staging table test_Clob_table_stg using trriger

CREATE OR REPLACE trigger test_Clob_table_trg AFTER INSERT ON Clob_table
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO test_Clob_table_stg VALUES(:new.sno,:new.c_clob);
EXCEPTION
WHEN others THEN
NULL;
END;
/
4) Run the below query.
MERGE INTO Clob_table a USING (
select 1 AS sno, null AS C_CLOB from dual) b
ON (a.sno = b.sno)
WHEN MATCHED THEN
UPDATE SET C_CLOB = b.C_CLOB
WHEN NOT MATCHED THEN
INSERT (a.sno,a.C_CLOB) VALUES (b.sno,b.C_CLOB);

Example 3 :

SQL Error: ORA-00600: internal error code, arguments: [13013], [5001], [158796], [30437895], [0], [30437895], [17], [], [], [], [], []

Resolution :

We have created index on one table to improve performance of query, on same table we are updating data in some stages.
It worked fine and improved performance  in local instances and customer testing instance, but we got above error in UAT after applying patch, whenever they has done update statement stage flow. After 3 days of debugging ,we suspect some data blocks are corrupted in UAT due that we got the error and then we dropped the newly index and check the flow, it worked fine. We got the below update statement highlighted in trace file with error message, it helped us to debug the issue and also we will get some more information in alert log for any internal error.

Query for the same :

UPDATE MS_ITC_AUDIT_V SET STATUS = ’38’, AUDIT_STATUS = ’38’ WHERE AUDIT_ID = :B1 AND INSTANCE_ID = (SELECT MAX(INSTANCE_ID) FROM MS_ITC_AUDIT_V WHERE AUDIT_ID = :B1 );

These are some examples of the ORA-00600: internal error code.Hope you like this article.If you like this article dont forget to share it with everyone.