Introduction to ORA-600/ORA-7445 Internal Error Analysis

How to deal with ORA-600 Internal Errors 

PURPOSE

This note provides a description of how to handle ORA-600 Internal Errors.It is intended to be used by the Database Administrator when reporting ORA-600 Internal Errors to Oracle Support.

TROUBLESHOOTING STEPS

How to deal with ORA-600 Internal Errors

Unless you are able to identify the cause and possible fix for an ORA-600 error using the references mentioned below, it should be considered as a potentially serious issue and reported to Oracle Support for identification. 

As mentioned in note:146580.1 every ORA-600 error will generate a trace file. The name and location of the trace file is also written to the alert.log. To help Oracle Support determine what caused the ORA-600 you should open a Service Request and supply the following information: 
 

1. The database alert log located in BACKGROUND_DUMP_DEST (for Oracle Real Application Clusters supply the alert log for all instances in the environment):
   SQL> show parameter background_dump_dest
2. The trace file mentioned in the alert log.
   • In case of multiple occurrences of the same ORA-600 error (ie. identical first arguments) you should only supply the first 2 or 3 trace files but do not delete any of the remaining traces as they might be required at a later stage.
   • In case of multiple different ORA-600 errors you should only supply the trace file from the first error. Oracle Support will request any other file that may be needed.
   • For Oracle RDBMS 11g, provide both the trace file and the incident trace file referenced
3. Recent changes to the system environment, eg. hardware modifications, operating system upgrades, restore of datafiles, power failures, etc.
4. Use RDA (Remote Diagnostic Agent) to collect important information:
   note:314422.1 "Remote Diagnostic Agent (RDA) 4 - Getting Started"

ORA-600 errors are raised from the kernel code of the Oracle RDBMS software when an internal inconsistency is detected or an unexpected condition is met. This situation is not necessarily a bug as it might be caused by problems with the Operating System, lack of resources, hardware failures, etc. 

With the ORA-600 error comes a list of arguments in square brackets. The first of these arguments tells us from where in the code the error was caught and thus is the key information in identifying the problem. This argument is either a number or a character string. The remaining arguments are used to supply further information (e.g. values of internal variables etc). 

Whenever an ORA-600 error is raised a trace file is generated and an entry written to the alert.log with details of the trace file location. Starting with Oracle Database 11g Release 1, the diagnosability infrastructure was introduced which places the trace and core files into a location controlled by the DIAGNOSTIC_DEST initialization parameter when an incident, such as an ORA-600 is created. For earlier versions, the trace file will be written to either USER_DUMP_DEST (if the error was caught in a user process) or BACKGROUND_DUMP_DEST (if the error was caught in a background process like PMON or SMON). The trace file contains vital information about what led to the error condition.

The purpose of this troubleshooting article is to provide an insight into key areas of internal 
600/7445 trace files to assist in deriving to a known bug or to highlight what might be needed 
by ORACLE Support to progress an issue further.  Whilst the article can be used to some 
extent on 11g it is primarily written for versions <V10204 due to :-

a) The worked example is based on a bug fixed in V10204 and higher releases
b) The alert/trace file structure in 11g has changed

The article will make reference to other notes so that the traces for 11g can also
be proactively reviewed if wished.

The nature of Internal errors means no one approach can guarantee getting to a solution but again
the intention is to provide an indication on notes that support make available to customers and 
a simple workflow process that assists for a vast majority of SRs. A simple example is illustrated 
to generate a known bug that is harmless to the database when generated, that said it
should ONLY be used on a TEST database as it's not encouraged to willingly raise such errors in
'LIVE' environments. This example will then be used to highlight various sections and how each
section can be useful for general 600/7445 analysis and again to provide the best chance of 
identifying a known bugfix should one be available.

The article also makes links to a number of others for completeness including Note:232963.1 
should a testcase be required as is the ideal case for all internal errors but understandably 
is not always possible dependent on the problem area.  Tests were made on SUN but the
bug is not platform specific, it may however show slightly different messages within the alert
log and for Windows based platforms some tools/notes might not be applicable.  However this
does not distract from the primary aim of how a trace file can be analyzed.

An Internal Error whether ORA-00600 or ORA-07445 can fall into many categories :-

Issue reproducible and testcase can be provided
Issue reproducible but no simple testcase perhaps due to code being oracle SQL, 3rd party SQL
Issue not reproducible but persistent and formulates to some pattern e.g. once a day 
Issue not reproducible and random pattern to occurrences

By definition if an issue is not reproducible at will in a customer environment a testcase may
be very difficult to obtain but should always be attempted where possible.

These are a simplified subset, depending on root cause there can be many 600/7445 errors.
Typically the argument(s) of the error when unique should mean each are different problems
but if occurring on same timestamp or one always soon follows another there might be some
relationship and this will be for support to determine.

TROUBLESHOOTING STEPS

Worked Example
------------------
A real life example will follow and a working methodology will then be provided. Conclusions
will then be made at the end of the analysis and some standard questions will be commented
on that should always be appropriate to analysis of Internal errors and perhaps any SR raised 
into support.  The bug number identified will be reported in a later section so as to show the 
workflow/analysis methodology used without knowing the solution in advance.  This article
is only suitable for <V10204 databases in terms of the testcase as the bug it relates to 
if fixed in the V10204PSR and higher RDBMS versions.  The article is still appropriate as an
introduction to analysis for 11g RDBMS and we will mention the main differences for when an internal error is seen in 11g.  Due to the nature of Internal errors it is possible that the error
is reported differently between versions/PSRs/platforms making analysis more complex.  The 
example and trace results for this article came from V10201/64bitSUN but the same principles
apply to all.

sqlplus scott/tiger
drop table a;
drop table b;

create table A(col11 number, col12 number);
create table B(col21 number, col22 number);

insert into a values (-3,-7);
insert into a values (null,-1);
insert into b values ( -7,-3);

update a sET col11 =
(select avg(b.col22) keep (dense_rank first order by (col22)) FROM b where 
b.col21= a.col12);

The UPDATE will fail at session level with ORA-03113 error. A trace file will also be written
to the UDUMP destination of the database and will now be highlighted, where appropriate
if a section can be found using keyword searches in the trace it will be mentioned.

Before any analysis of UDUMP/BDUMP traces take place there should be an understanding of 
how the error(s) are reported in the alert log.

ALERT LOG
---------------
As this is a worked example we know what to expect in advance but the alert and an understanding
of what actions take place around an internal error can be fundamental to resolution. It is 
certainly possible for internal errors to be a consequence of a problem rather than the cause.

Errors in file xxxx/xxxx_ora_24779.trc:
ORA-07445: exception encountered: core dump [_memcpy()+592] [SIGSEGV] 
[Address not mapped to object] [0xFFFFFFFF7B180000] [] []
ORA-00600: internal error code, arguments: [kgh_heap_sizes:ds], 
[0xFFFFFFFF7B179E98], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [_memcpy()+592] [SIGSEGV] 
[Address not mapped to object] [0xFFFFFFFF7B180000] [] []
ORA-00600: internal error code, arguments: [kghGetHpSz1], [0xFFFFFFFF7B179EA8]

The errors continue in the alert and as we can see >1 error is raised but all are reported
to be within the same tracefile.
  There will be cases where >1 error and >1 trace exist but this is
beyond the scope of this 'Introduction'.

Various sections of the trace are now reported based on this example, not all 600/7445 traces
will allow for each section to be reviewed. Again this is a very simple example and often a
combination of traces will need to be understood which is beyond the scope of this article.

Worked Example for 11g
---------------------------------

The general principles of internal 600/7445 error analysis holds
true for 11g but there are some differences in how traces get reported.

To illustrate Note.808071.1 provides an excellent testcase to reproduce
the error ORA-00600[16613]. As stated already if there is a desire to 
reproduce this error so that the alert/trace can be looked at in a proactive
manner then it should ONLY be used on a TEST database.

The example will not be reported in the same detail but instead we can use
it to highlight the key differences.

After the error has been reproduced we need to look at the alert log which
can be located via :-

show parameter dump_dest 

background_dump_dest string c:\app\diag\rdbms\orcl\orcl\trace
core_dump_dest               string c:\app\diag\rdbms\orcl\orcl\cdump
user_dump_dest               string c:\app\diag\rdbms\orcl\orcl\trace

It is the background and user destinations for the 'trace' directory that we will
need to look.

Tue Nov 15 09:33:59 2011
Errors in file c:\app\diag\rdbms\orcl\orcl\trace\orcl_ora_3896.trc (incident=74841):
ORA-00600: internal error code, arguments: [16613], [0x37D727F8], [2], [], [], [], [], []
Incident details in: c:\app\diag\rdbms\orcl\orcl\incident\incdir_74841\orcl_ora_3896_i74841.trc

For more details on how critical/internal errors are reported within 11g Note.443536.1
can be used. For further analysis of the ORA-00600 we are interested in the 'Incident Trace'
and the full path to this file is still provided by the alert log. Such traces can also 
be located via 'ADRCI' but is beyond the scope of this article.

If we look at the appropriate incident trace file we will see very similar traces to
the 10.2 example and the same analytical approach to these traces can be followed. 

To provide 11g files to support it is vital to provide the appropriate incident traces
and Note.443529.1/Note.411.1 can be reviewed but again is outside of the scope
of how to try and diagnose the internal error itself.

ORA-00600/ORA-07445 Analysis : Key Questions
-------------------------------------------------------------------
If trying to research an Internal error before raising a SR to ORACLE Support the following
questions will help avoid further questions from analysts.  These questions are asked in the
MOS template when raising a SR and if answered as clearly as possible this can greatly assist
in getting to a solution.

Is the error reproducible?

If an issue is reproducible where possible it should also be tested from SQLPLUS
(ref Section 5).  It is possible that a known bug/new bug might only occur on JDBC 
based clients for example and time needs to be spent understanding the minimum
configuration needed to reproduce a problem.

If an issue is indeed reproducible in a controlled manner a testcase for the majority
of issues should be possible and if a SR or pre-analysis is not matched to a known bug
very rarely can a problem be fixed from a trace file alone.  For this reason please be 
aware of Note:232963.1

Has this worked before?

If functionality that was working has just stopped it would tend to suggest either some
issue of data within one or more objects or some more significant change.  If a 600/7445
is seen within Development particularly on higher PSRs/releases it could well be that
you have found a new bug.  Again depending on reproducibility a testcase ideally would
be required.

Has any new change been made recently e.g new code/new PSR or DB version/higher DB load?

Rarely do 600/7445 errors just start to occur due to a database being open for a certain
amount of time.  Even a small change of init/spfile parameters can influence how the database operates 
so if any change is known it is better to mention asap no matter how small.

What is frequency of the error, when did it start?

On many occasions a 600/7445 will be seen as a oneoff and not to be seen again.  If pre-analysis
cannot be matched to a known issue and if in any doubt about an error a SR should always
be raised.  It is however necessary to be aware that if the error is a oneoff or sporadic there is a
tradeoff between impact of error and further analysis, as covered below.

On other occasions we might see the error on the hour or some consistent period of time, in 
these cases root cause might be some job that runs or linked to an ORACLE process that 
gets invoked at certain times.  Finding some pattern can sometimes be vital to solving an issue
and knowing when an issue truly started is very important to analysis.

What is the known visible impact?

Of course any 600/7445 [or any issue for all product support] should be raised as a SR to 
ORACLE if there is any concern.  Sometimes an Internal errors impact is very clear in that 
we might see an error in a controlled manner only when a session logsoff the database or the 
database closes down.  It is equally possible that a database may seem to be running stable
with no users reporting problems and only by proactively looking at the alert can errors be seen.

In all cases if a SR is raised to Support we will need to try and assess the impact of a given 
error and to make a confirmation asap if linked to something serious within the database eg corruption.

In many cases of analysis there soon becomes a tradeoff between seeing the error in the alert
and the further diagnostics needed to try and get to the root cause.  For this reason if an error
is a oneoff or very infrequent and determined not to visibly affect users a decision may need to 
be made on how far to go especially if running on a desupported release or not on the latest PSR.

The above questions relate to Support asking this information from the customer, one of the key questions asked by customers to Support is :-

'Why am I being asked to go to the latest PSR when a bug has not been identified?'

The primary answers for this are twofold :-

a) Note:209768.1 explains the backport policy for new bugs. So if Support could not match analysis to a known issue, a new bug can be filed. Sometimes Development can release 'diagnostics patches' to better analyze new issue but it's recommended to use it on latest patchset. Also if on older PSRs it is far more likely for the customer to be running with >1 'oneoff' fix placing the customer into a configuration that is probably not seen with many other customers.

b) Perhaps more importantly each PSR contains many fixes and can solve many issues without
Support being able to determine why.  For this reason the latest PSR is seen as a statistically faster
option to solve a problem when an error cannot be matched to a known bug.

Of course if considering the latest PSR appropriate backups and testing need to be made in advance.

Summary and Conclusions
---------------------------

The article can be summarised into key sections :-

1. Most importantly always raise a SR if anything is unclear 
2. Consider the ORA600/7445 tool : Note:153788.1
3. Make use of the key articles surrounding internal errors ie OERI notes and BUGTAGS
4. Understand basic trace information to be able to use MOS better for 'efficient' searching
5. If a SR is to be be raised provide as much information as possible based on the MOS
template questions as these will often be vital to solving the SR.

In conclusion 600/7445 errors are not hopefully a common event to be seen by DBA's and
analysis of these errors is not something that would be expected by themselves.  However it is often of 
interest to many customers as to what is looked at within support and how we make a formal analysis.
Therefore the articles purpose is to highlight some of these processes with the hope that some
self analysis can be made and to assist customer/support interaction on these issues.