Problem Diagnosis - Expert Oracle RAC Performance Diagnostics and Tuning - page 622

Step 8
After generating the package, which brings together all the required files, the next step is to finalize the package so the
output can be sent to Oracle support:

adrci> IPS FINALIZE PACKAGE 1
Finalized package 1

This step makes the package ready for upload to Oracle support. The ADR keeps track of all components used at
the point in time when the error occurred. The zip file generated in Step 7 has a complete list of all log files required by
Oracle support for analyzing of the problem.

SQL Test Case Builder
In Oracle Database 11g Release 2, Oracle has introduced a SQL Test Case Builder (TCB). The test case builder helps
in gathering and reproducing as much information as possible about a problem and the environment in which the
problem had occurred. This helps to reproduce the problem on a separate Oracle database.
The incident-related files are stored in the ADR, and the incident directories are under the ADR_HOME . In the case
of incidents in which an SQL query was involved, the trace files also include the query being executed, table and index
definitions, PL/SQL functions, procedures and packages, optimizer statistics, and initialization parameter settings.

Step 1
There could be several incidents that happen in a production database environment. Not all incidents need to
be related to an SQL query. Whereas all incidents get listed as incidents at the top level under the SHOW INCIDENT
command, incidents that are related to an SQL query need to be isolated:

adrci> SET HOME /app/oracle/diag/rdbms/prddb/PRDDB_1
adrci> SHOW INCIDENT
ADR Home = /app/oracle/diag/rdbms/prddb/PRDDB_1:
*********************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
------------- ---------------------------- --------------------------
52777 ORA 600 [kkqsMapExp:badleaf] 2010-11-19 16:58:13.938000
52849 ORA 600 [kkqsMapExp:badleaf] 2010-11-19 17:12:28.076000
88515 ORA 600 [kjbrasr:pkey] 2010-12-02 01:04:06.313000
3 rows fetched

There are two incidents with an identical problem key. When ADR encounters more than one incident with the
same problem key, it maintains just one problem entry for up to five incidents.

Step 2

1. One way to determine if there are any SQL statements associated with the incident is

to check all the trace files generated during the process and examine those moved to
the “incident” directory. For example, the incident directory created for 52777 contains
relevant trace files and can be examined manually if it contains any SQL statements: