Hanganalyze Utility for tracing and Packaging Incidents for Oracle Support
-------------------------------------------------------------------------------------------------------------------

You want to create an automatic error dump file when a specific Oracle error occurs.

You can create error dumps to diagnose various problems in the database, by specifying the error
number in a hanganalyze or systemstate command. For example, diagnosing the causes for deadlocks is
often tricky. You can ask the database to dump a trace file when it hits the ORA-00060: Deadlock
detected error. To do this, specify the event number 60 with the hanganalyze or the systemstate
command:
SQL> alter session set events '60 trace name hanganalyze level 4';
SQL> alter session set events '60 trace name systemstate level 266';

Both of these commands will trigger the automatic dumping of diagnostic data when the database
next encounters the ORA-00060 error. You can use the same technique in an Oracle RAC database. For
example, you can issue the following command to generate automatic hanganalyze dumps:
SQL>alter session set events '60 trace name hanganalyze_global level 4';

This alter session statement invokes the hanganalyze command in any instance in which the
database encounters the ORA-00060 error.

SQL> ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level 3';

You can send the resulting output to Oracle so it can analyze the output for you. Note that at
this stage, you need to open a technical assistance request (SR) with Oracle technical support
through MetaLink (http://metalink.oracle.com). (The hanging database problem gets you a priority
level 1 response, so you should hear from an analyst within minutes.) Oracle technical support
may ask you for more information, such as a core dump, and ask you to run a debugger or another
diagnostic tool and FTP the output to them.


While you’re investigating such a database, you may sometimes find yourself unable to
connect and log in. In that case, use the “prelim” option to log in to the database. The prelim option
doesn’t require a real database connection. Here's an example that shows how to use the prelim option
to log into a database:
$ sqlplus /nolog
SQL> set _prelim on              --preliminary connection: lets you connect to the SGA without opening a
session
SQL> connect / as sysdba
Prelim connection established

Alternatively, you can use the command sqlplus -prelim "/ as sysdba"

You cannot even query the V$ views. However, the prelim option lets you run oradebug commands to get error dump stacks for diagnostic purposes.

SQL> oradebug hanganalyze 3         --analyzes what’s in the SGA and dumps it into a trace file
Statement processed.

In an Oracle RAC environment, specify the oradebug hanganalyze command with additional options, as shown here:
SQL> oradebug setinst all
SQL> oradebug -g def hanganalyze 3

You can repeat the oradebug hanganalyze command a couple of times to generate dump files for varying process states.

The systemstate dump will report on what the processes are doing and the resources they’re currently holding. You can get a systemstate dump from a non-RAC system by executing the following set of commands.
SQL> oradebug setmypid
Statement processed.

SQL> oradebug dump systemstate 266
Statement processed.

Issue the following commands to get a systemstate dump in a RAC environment:
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug ipc
SQL> oradebug -g all dump systemstate 266


At times like this, you may not even be able to log into the database. Your first instinct when
you realize that you can’t even log in to a database is to try shutting down and restarting, often referred
to as bouncing the database. Unfortunately, while shutting down and restarting the database may
“resolve” the issue, it’ll also disconnect all users—and you’re no wiser as to what exactly caused the
problem. If you do decide to bounce your database, quickly generate a few hanganalyze and systemstate
dumps first.


If you can find out the blocking session, you can also take a dump just for that session, by using the command ORADEBUG SETOSPID NNNN, where nnnn is the blocking session’s PID, and then invoking the oradebug command, as shown here:

SQL> oradebug setospid 9999
SQL> oradebug unlimit
SQL> oradebug dump errorstack 3

Note that you can generate the hanganalyze and systemstate dumps in a normal session (as well as in a prelim session), without using the oradebug command. You can invoke the hanganalyze command with an ALTER SESSION command, as shown here.

SQL>alter session set events 'immediate trace name hanganalyze level 3';

Similarly, you can get a systemstate dump with the following command:
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';


Exmaple:
-------
When you’re working with a trace file, another way of showing the file’s name and location is to use the oradebug command. For example:
SQL> oradebug setmypid
SQL> alter database backup controlfile to trace noresetlogs;
SQL> oradebug tracefile_name

Here is the output after running the last statement:
/oracle/app/oracle/diag/rdbms/o11r2/O11R2/trace/O11R2_ora_9628.trc
This way, you can directly display the name of the trace file that you generated with the ALTER DATABASE BACKUP statement.


Packaging Incidents for Oracle Support
--------------------------------------
http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/adrci.htm#SUTIL700


You can package the diagnostic information for one more incidents through either Database Control or through commands that you can execute from the ADRCI interface.

You can use various IPS commands to package all diagnostic files related to a specific problem in a zipped format and send the file to Oracle Support. Here are the steps to create an incident package.
1. Create an empty logical package as shown here:
adrci> ips create package
Created package 1 without any contents, correlation level typical         --created an empty package

2. Add diagnostic information to the logical package with the ips add incident command:
adrci> ips add incident 43369 package 1
Added incident 43369 to package 1
adrci>

At this point, the incident 43369 is associated with package 1, but there’s no diagnostic data in it yet.
3. Generate the physical package.
adrci> ips generate package 1 in \app\ora\diagnostics
Generated package 1 in file \app\ora\diagnostics\IPSPKG_20110419131046_COM_1.zip,
mode complete
adrci>

When you issue the generate package command, ADRCI gathers all relevant diagnostic files and adds them to a zip file in the directory you designate.
4. Send the resulting zip file to Oracle Support.
If you decide to add supplemental diagnostic data to an existing physical package (zipped file), you can do so by specifying the incremental option with
the generate package command:
adrci> ips generate package 1 in \app\ora\diagnostics incremental

However, the ipc create package command has several options: you can specify the incident number or a problem number directly when you create the logical package, and skip the add incident command. You can also create a package that contains all incidents between two points in time, as shown here:

adrci> ips create package time '2011-04-12 10:00:00.00 -06:00' to '2011-04-12 23:00:00.00 -06:00'
Created package 2 based on time range 2011-04-12 12:00:00.000000 -06:00 to 2011-
04-12 23:00:00.000000 -06:00, correlation level typical
adrci>

The package generated by the previous command contains all incidents that occurred between 10 a.m. and 11 p.m. on April 12, 2011.

Note that you can also manually add a specific diagnostic file to an existing package. To add a file,
you specify the file name in the ips add file command—you are limited to adding only those
diagnostic files that are within the ADR base directory. Here is an example:
adrci> ips add file /diag/rdbms/orcl1/orcl1/trace/orcl_ora12345.trc package 1

adrci> ips show files       --shows all the files in a package
adrci> ips show incidents   --shows all the incidents in a package