Subject: ORA-600 Internal Error Code Described
Author: RBEARD
Modified: 11 Sep 94 08:47:10
_______________________________________________________________________________
ORA-600 Internal Error Code Described
This bulletin discusses the ORA-600 internal error code, what the ORA-600
error is, what to do if you get the error, and what information is needed
to report the error to Oracle Support. Because ORA-600 errors are
reported in the database alert and trace files, this bulletin also explains
what these files are, when they are generated and how to find them.
WHAT IS AN ORA-600 ERROR AND HOW TO TREAT IT:
============================================
ORA-600 is an internal error generated by the generic kernel code of the
Oracle RDBMS software. It is different from other Oracle errors in many
ways. The following is a list of these differences.
1. An ORA-600 error may or may not be displayed on the screen. Therefore,
screen output should not be relied on for capturing information on this
error. Information on ORA-600 errors are found in the database alert
and trace files. We recommend that you check these files frequently for
database errors. (See the Alert and Trace Files section for more
information.)
2. Each ORA-600 error comes with a list of arguments usually enclosed in
square brackets and follow the error on the same line for example:
ORA-00600 [14000][51202][1][51200][][]
Each argument has a specific meaning which can only be interpreted by
an Oracle support analyst. The arguments may also change meaning from
version to version therefore customers are not advised to memorize them.
3. Every occurrence of an ORA-600 should be reported to Oracle Support.
Unlike other errors, you can not find help text for these errors. Only
Oracle technical support should diagnose and take actions to prevent or
resolve damage to the database.
4. Each ORA-600 error generates a database trace file. (See the Alert and
Trace Files section for more information.)
REPORTING THE ERROR TO ORACLE SUPPORT
=====================================
Gathering pertinent information:
-------------------------------
As was previously stated, only Oracle Support can diagnose and interpret the
ORA-600 error, however, they can only do this with certain information.
Therefore, it is recommended that you have the following information
available before calling Oracle Support.
1. Determine the status of your database so that proper priority is placed on
your call to Oracle Support. Is the database up but not responding to your
commands, or does the error occur only when issuing a specific query, or
has the error caused the Oracle background processes to die? (See
Checking the operating system and database section for more information.)
2. Check the alert log first and locate the trace file that contains the
ORA-600 error. You should note all the arguments for the error. If the
error appears on the screen, you may not see all the arguments on the
screen. Therefore, screen output should not be relied on for capturing
information on this error. Instead, you must find the database alert or
trace files which contain the error. (See the Alert and Trace Files
section for more information.)
3. If the database is up and running, log in as a DBA and check for the
following:
a) Status of rollback segments, issue the following query:
select segment_name, owner, status from dba_rollback_segs;
NOTE: Owner of 'SYS' represents a private rollback segment, where
as Owner of 'PUBLIC' indicates a public rollback segment.
b) Status of the tablespaces, issue the following query:
select tablespace_name, status from dba_tablespaces;
c) Connected processes and running programs, issue the following query:
select username, program from v$process;
d) Location of datafiles, issue the following query:
select file_id, file_name, tablespace_name from dba_data_files;
e) Location of the redo log files, issue the following query:
select * from v$logfile;
4. Keep a copy of the init.ora file handy to fax to Oracle Support.
The init.ora is a file that contains initialization parameters needed to
startup an instance. The location and name varies depending on the
operating system. In addition, it is important to check the init.ora for
an 'ifile' parameter. If this parameter exists, then Oracle reads both
files on instance startup, and both these files need to be analyzed.
Default names and location of the parameter files:
Note: Default location and naming convention for this file
may change from version to version. Please refer to your
Installation and User's Guide for detailed information.
UNIX (both V6 and Oracle7)
location - $ORACLE_HOME/dbs
name - init<$ORACLE_SID>.ora
APPLE UX (Oracle7)
location - $ORACLE_HOME/dbs
name - init<$ORACLE_SID>.ora
VMS (both V6.0.35+ and Oracle7)
location - ORACLE_ROOT:[DB_<database_name>]
name - <node_name>_<ora_sid>_INIT.ORA
NOTE: ORA_PARAMS logical points to the init.ora used for startup.
DESKTOP (V6 - NETWARE, OS2, DOS)
location - oracle6\dbs
name - INIT.ORA
DESKTOP (Oracle7 - DOES NOT APPLY TO DOS/MAC)
location - %oracle_home%\rdbms70
name - INIT.ORA
MAC (V6)
location - database folder
MVS (both V6 and Oracle7)
location - n/a
name - see the INITORA DD in the ORACLE Procedure
5. Locate the controlfiles, you can find the control files in one of three
ways:
a) locate the init.ora, see above for location and name, the control
files are listed under the parameter 'control_files'.
b) login to SQL*DBA and issue a 'show parameter control_files' command.
c) issue the following query while logged in as a DBA:
SELECT VALUE FROM V$PARAMETER WHERE NAME ='control_files';
d) starting with version 7.0.16, there is a view called v$controlfile
which gives the full list of controlfile locations
6. Call Oracle Support and request for a Priority 2 call if the database
is running or a Priority 1 call if the database is frozen or down and the
database is a business critical database.
Checking the operating system and database:
------------------------------------------
In addition to specific information about the error, it is important to
convey specific information about your database and operating system to
Oracle Support. Furthermore, certain actions need to be taken, or not
taken. When calling Oracle Support, you should bear in mind the
information you have gathered, above, as well as the following:
1. DO NOT shutdown the database. Furthermore , DO NOT shutdown the database
using the ABORT option under such circumstances unless directed to do
so by Oracle Support.
2. At the operating system level, check to make sure that the Oracle
background processes which belong to your database instance are alive.
Make a note of them and observe their status.
NOTE: Under some operating systems, such as DOS, the background
processes is the same as the user process. In addition, some platforms
have no method of checking the process status.
Below is some information on checking background process status for some
operating systems:
In the Unix environment (SYSTEM V) use:
ps -ef | grep $ORACLE_SID
In the Unix environment (BSD) use:
ps -aux | grep $ORACLE_SID
In the VMS environment use:
show system - then look for the process names
In the NLM environment use:
the NLM monitor
In the OS/2 environment use:
the PSTAT utility
In the MVS environment use:
the MPMCMD, DISPLAY STATUS
3. If the background processes are not alive on the operating system, and
you determine that the ORA-600 error caused these processes to die, or
the instance to crash. Try starting up the database and note its success
or any errors that arise.
4. Taking a backup under these conditions is not recommended by Oracle. If
you do decide to take a backup anyway, the only reason for restoring it
will be to take a database export.
5. Once an ORA-600 error has been reported, check the alert.log frequently
for the error recurrence, or other errors following it. Make a note of
any errors found.
6. Talk to users and inquire about system performance and response times.
Try to locate an application or table which caused the error. If you can
identify the process, do not terminate the process at the o/s level if
it is not causing system problems. Oracle Support may be able to obtain
diagnostics information from that process.
7. You may need to prevent users from starting new jobs on the database
before you have talked to Oracle Support. Consult your system administrator
if you are in doubt and you need to make a decision before talking to
Oracle Support. If the database is behaving normally and the error has
not caused other Oracle user or background processes to misbehave, you
may start other new jobs.
8. Check console log to see if there is a hardware problem. Talk to your
system administrator to see if there has been a change to the system.
ALERT AND TRACE FILES
=====================
As stated above, ORA-600 internal errors are not always reported to the
screen, most of the information regarding these errors can be found in
either the alert or the trace files which are automatically generated
by the database.
What are trace files?:
---------------------
For every database, Oracle creates an alert file which contains information on
everything that occurs within this database. For example, the alert file
contains information on all startups and shutdowns. In addition, all
database structure changes are recorded in the alert file. Finally, all
unusual errors are also listed in this file, including read/write and network
errors.
In addition, Oracle also generates trace files for the background
processes, or even for a user trace file that encountered a fatal error.
When a server or background process detects an internal error, it dumps
information regarding the error in these trace files. The alert.log is always
generated, and information is appended to the same file.
NOTE: A user trace file can also be generated if that user issues the
'alter session set sql_trace true' command. These files are used
for query optimization.
V6 and Oracle7 generate background process trace files differently, In V6,
Oracle generated a trace file for each background process every time the
database was started. Whereas, Oracle7 only generates a file when
an error occurs with the background process. In addition, under Oracle7,
the LGWR log switches are written to the alert.log, where as in V6 they are
only found in the LGWR trace file.
It is recommended that the DBA clean up the trace area every so often. The
trace location can become full of files which makes finding the right
trace file more difficult. It is further recommended that any trace
file containing an error be backed up rather than deleted. In addition,
it is best to backup the alert.log periodically. A database startup
generates a new alert log file if one does not exist.
NOTE: Under the MVS environment, a new alert file is NOT generated if one
does not exist, it must e pre-allocated.
NOTE: Trace files generated under VMS must be formatted before they can
be analyzed. See the VMS Installation and User's Guide on formatting
trace files.
Finding the trace files:
-----------------------
There are two init.ora parameters that determine the location of these
files, BACKGROUND_DUMP_DEST and USER_DUMP_DEST, each can be equated to a
directory name which determines where the database puts these trace
files. It is recommended that these parameters be set to a trace directory
below Oracle home rather than relying on the default location.
NOTE: The ALERT.LOG file is placed in the BACKGROUND_DUMP_DEST location.
NOTE: BACKGROUND_DUMP_DEST and USER_DUMP_DEST are not supported on MVS.
Each operating system handles the naming and default location of these files
differently. In addition, there may be differences in V6 and Oracle7 in
the handling of these files. Below is some specific operating system
information which can also be found in your installation guide or
operating system specific documentation.
Note: Default location and naming convention for these files
may change from version to version. Please refer to your
Installation and User's Guide for detailed information.
NOTE: DESKTOP, below, refers to the Netware, OS2, Dos, Mac, and Oracle
Workgroup Server. If one of these operating systems is different
than what is specified for DESKTOP, that operating system will
be listed.
UNIX (V6):
Default Location - $ORACLE_HOME/rdbms/log
Trace file Naming - <oracle_process_id>_<unix_proc_id>.trc
Alert file Naming - alert_<$ORACLE_SID>.log
UNIX (Oracle7 installed without ORACLE_BASE) and APPLE UX:
Default Location - $ORACLE_HOME/rdbms/log
Background trace file Naming - <oracle_process_name>_<unix_proc_id>.trc
User trace file Naming - ORA_<unix_proc_id>.trc
Alert file Naming - alert_<$ORACLE_SID>.log
VMS (both V6 and Oracle7):
Default Location - ORA_ROOT:[DB_<database name>.TRACE]
or ORA_DUMP logical
Alert file Naming - <node_name>_<ORA_SID>_alert.log
Background process trace file naming convention:
<node_name>_<ORA_SID>_BG_<oracle_process_name>_<oracle_process_id>.trc
User process trace file naming convention:
<node_name>_<ORA_SID>_FG_<executible_running>_<oracle_process_id>.trc
DESKTOP (V6):
Default Location - There is no default location for either the trace
or alert files. In order to generate trace files, you must specify
background_dump_dest (for background trace files and the alert.log)
and user_dump_dest (for user trace files).
Background process trace file naming convention:
<oracle_process_name>.trc
MAC - ora<process_id>
DOS - <process_id>.trc
User process trace file naming convention:
<oracle_process_id>.trc
MAC - ora<process_id>
DOS - <process_id>.trc
Alert file Naming - ALERT.LOG
DESKTOP (Oracle7 - DOES NOT APPLY TO DOS/MAC):
Default Location - %oracle_home%\rdbms70\trace
ORACLE WORKGROUP SERVER - rdbms\70
Background process trace file naming convention:
<oracle_process_name>.trc
ORACLE WORKGROUP SERVER - ora<process_id>.trc
User process trace file naming convention:
ORA<process_id>.trc
ORACLE WORKGROUP SERVER - ora<process_id>.trc
Alert file Naming - alert.log
ORACLE WORKGROUP SERVER - ALRT<oracle_sid>.LOG
MVS (V6 and V7):
Default Naming for trace - ssn.TRACEnnn (ssn is the Oracle subsystem)
Trace file Naming - see the MPM Parameter TRACEDS
Alert file Naming - see the SYSPRINT DD in the ORACLE Procedure
If the trace files are not in the default location, you can find this location
in one of three ways:
1) locate the init.ora, see above for location and name, the control
files are listed under the parameter 'background_dump_dest' or
'user_dump_dest'.
2) login to SQL*DBA and issue a 'show parameter background_dump_dest'
or 'show parameter user_dump_dest' commands.
3) issue the following query while logged in as a DBA:
SELECT VALUE FROM V$PARAMETER WHERE NAME ='backgound_dump_dest';
or
SELECT VALUE FROM V$PARAMETER WHERE NAME ='user_dump_dest';