belong to one of the tables accessed by
that statement.
2. Search within the trace file for the words
Plan Table. This search will return the
tables and indexes the Oracle Database
optimizer is using to access the data that
will satisfy the query being executed.
For example, the query using the plan
in Listing 1 is accessing the testtab1,
testtab2, and testtab3 tables and the
XC179S1 and XC179PO indexes.
For each of the tables used by the SQL
statement that was executing at the time of
the error, execute the following statement:
Figure 1: ORA-600/ORA-7445 lookup tool interface
SQL>analyze table <tablename> validate
structure cascade;
This will check to ensure that every value
in the index is also in the table, and vice
versa. If it finds a mismatch, it will report
ORA-1499 table/Index Cross Reference
Failure - see trace file
The trace file will be in the location indicated by the user_dump_dest or diagnostic_
dest initialization parameter and will contain
information similar to
row not found in index
tsn: 8
rdba: 0x04d01348
You can then find the index with by using
the query in Listing 2. Replace the &rdba and
&tsn values in Listing 2 with the appropriate
values. For this example, the &rdba value is
the rdba from the trace file with the 0x portion
removed and &tsn is the tablespace number
(tsn) from the trace file. (&rdba in this case
would be 04d01348,and &tsn would be 8.)
Once you have identified the index, drop
and re-create it. It is important to drop and
re-create the index rather than rebuilding it
online, because only re-creating it will reini-tialize the values in the index.
ORA-7445 [xxxxxx] [SIGBUS] [OBJEC T
SPECIFIC HARDWARE ERROR]. This ORA-
7445 error can occur with many different
functions (in place of xxxxxx). For example,
the following alert.log excerpt shows the
failing function as ksxmcln.
/u01/app/oracle/admin/prod/bdump/
prod_smon_8201.trc:
ORA-7445: exception encountered:
core dump [ksxmcln()+0] [SIGBUS]
[object specific hardware error]
[6822760] [] []
The important part of this error is the
”object specific hardware error” argument,
which indicates that there were insufficient
operating system resources to complete
the action. The most common resources
involved are swap and memory.
To diagnose the cause of an ORA-7445
error, you should first check the operating
system error log; for example, in Linux this
error log is /var/log/messages. Within the
error log, look for information with the same
time stamp as the ORA-7445 error (this will be
in the alert.log next to the error message). You
will often find an error message similar to
Jun 9 19:005:05 PRODmach1 genunix:
[ID 470503 kern.warning]
WARNING: Sorry, no swap space to grow
stack for pid 9632
If no errors are reported in the operating
system error log with the same time stamp
as the ORA-7445 error, check the ORA-7445
trace file. If there is a statement in the
trace file under the heading “Current SQL
Statement,” execute that statement again
to try to reproduce the error. If the error is
reproduced, run the statement again while
monitoring OS resources with standard
UNIX monitoring tools such as sar or vmstat
(contact your system administrator if you are
not sure which to use).
Once you’ve identified the resource
that affects the running of the statement,
increase the amount of that resource available to Oracle Database.
SUMMARY
By following the instructions in this article,
you should be able to resolve some errors
that are caused by underlying physical issues
such as file corruption or insufficient swap
space. But because ORA-600 and ORA-7445
errors are internal, many cannot be resolved
by user-led troubleshooting.
For those Oracle Database users with
Oracle support contracts, however, additional knowledge content is available
via My Oracle Support. Most notably,
the ORA-600/ORA-7445 lookup tool
[Knowledge Article 153788.1], shown in
Figure 1, enables you to enter the first argument to an ORA-600 or ORA-7445 error
message and use that information to identify known defects, workarounds, and other
knowledge targeted specifically to that
error/argument combination.
Tamzin Oscroft is a senior principal support
engineer in Oracle Database Support. She has
worked for Oracle Global Software Support
for 16 years.
NEXT STEPS
LEARN more about My Oracle Support
support.oracle.com
blogs.oracle.com/supportportal
READ more about error messages
Oracle Database Error Messages 11g Release 2
bit.ly/mqNFVQ
