Managing Trace and Dump Files in Oracle 11g

Standard Location of Log Files:
select value from v$diag_info where name ='Diag Trace';
E:\APP\ORACLE12\diag\rdbms\seaprod\seaprod\trace




The ADR Command Interpreter (ADRCI) is a command-line tool that you use to manage Oracle Database diagnostic data.
As you know the location of the alert log files and trace files has changed on 11g:



The ADR can be managed via the 11g Enterprise Manager GUI (Database Control and not Grid Control) or via the ADR command line interpreter ADRCI.
The DIAGNOSTIC_DEST parameter identifies the location of the ADR and it defaults to the $ORACLE_HOME/log or $ORACLE_BASE if this environment variable has been set.
The alert log is stored in both XML as well as standard text format in the ADR.

The XML alert.log is located at $ORACLE_HOME/log/diag/rdbms/<SID>/<SID>/alert and is called ‘log.xml’. The current alert log contents will be found in the file ‘log.xml’ while the older entries are archived every 10 MB into files like ‘log_1.xml’, ‘log_2.xml’ and so on.

The standard text alert log file (alert_apex.log) is located at $ORACLE_HOME/log/diag/rdbms/<SID>/<SID>/trace and is mantained for backward compatability purposes.

ADRCI is a command-line tool that is part of the fault diagnosability infrastructure introduced in Oracle Database Release 11g. ADRCI enables you to:
Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more.
ADRCI has a rich command set, and can be used in interactive mode or within scripts. In addition, ADRCI can execute scripts of ADRCI commands in the same way that SQL*Plus executes scripts of SQL and PL/SQL commands.

The view V$DIAG_INFO also has information on the location of the various components of the ADR.

set linesize 121
col name format a30
col value format a75
SELECT * FROM gv$diag_info;

   INST_ID NAME                           VALUE
---------- ------------------------------ --------------------------------------
         1 Diag Enabled                   TRUE
         1 ADR Base                       c:\app
         1 ADR Home                       c:\app\diag\rdbms\die\die
         1 Diag Trace                     c:\app\diag\rdbms\die\die\trace
         1 Diag Alert                     c:\app\diag\rdbms\die\die\alert
         1 Diag Incident                  c:\app\diag\rdbms\die\die\incident
         1 Diag Cdump                     c:\app\diag\rdbms\die\die\cdump
         1 Health Monitor                 c:\app\diag\rdbms\die\die\hm
         1 Default Trace File             c:\app\diag\rdbms\die\die\trace\die_ora_11820.trc
         1 Active Problem Count           2
         1 Active Incident Count          6


Some Definitions
The following are definitions of terms used in the context of ADRCI and the Oracle Database fault diagnosability infrastructure:

Automatic Diagnostic Repository (ADR)
The ADR is a file-based repository for database diagnostic data such as traces, dumps, the alert log, health monitor reports, and more. It has a unified directory structure across multiple instances and multiple products. Beginning with Release 11g, the database, Automatic Storage Management (ASM), and other Oracle products or components store all diagnostic data in the ADR. Each instance of each product stores diagnostic data underneath its own ADR home directory (see "ADR Home"). For example, in an Oracle Real Application Clusters environment with shared storage and ASM, each database instance and each ASM instance has a home directory within the ADR. The ADR's unified directory structure enables customers and Oracle Support to correlate and analyze diagnostic data across multiple instances and multiple products.

Problem
A problem is a critical error in the database. Critical errors include internal errors such as ORA-00600 and other severe errors such as ORA-07445 (operating system exception) or ORA-04031 (out of memory in the shared pool). Problems are tracked in the ADR. Each problem has a problem key and a unique problem ID. (See "Problem Key".)

Incident
An incident is a single occurrence of a problem. When a problem occurs multiple times, an incident is created for each occurrence. Incidents are tracked in the ADR. Each incident is identified by a numeric incident ID, which is unique within the ADR. When an incident occurs, the database makes an entry in the alert log, sends an incident alert to Oracle Enterprise Manager, gathers diagnostic data about the incident in the form of dump files (incident dumps), tags the incident dumps with the incident ID, and stores the incident dumps in an ADR subdirectory created for that incident.
Diagnosis and resolution of a critical error usually starts with an incident alert. You can obtain a list of all incidents in the ADR with an ADRCI command. Each incident is mapped to a single problem only.
Incidents are flood-controlled so that a single problem does not generate too many incidents and incident dumps. See Oracle Database Administrator's Guide for more information on incident flood control.

Problem Key
Every problem has a problem key, which is a text string that includes an error code (such as ORA 600) and in some cases, one or more error parameters. Two incidents are considered to have the same root cause if their problem keys match.

Incident Package
An incident package (package) is a collection of data about incidents for one or more problems. Before sending incident data to Oracle Support it must be collected into a package using the Incident Packaging Service (IPS). After a package is created, you can add external files to the package, remove selected files from the package, or scrub (edit) selected files in the package to remove sensitive data.
A package is a logical construct only, until you create a physical file from the package contents. That is, an incident package starts out as a collection of metadata in the Automatic Diagnostic Repository (ADR). As you add and remove package contents, only the metadata is modified. When you are ready to upload the data to Oracle Support, you create a physical package using ADRCI, which saves the data into a zip file.


Using ADRCI
To execute the ADRCI you just need to type adrci at the OS prompt.
To get help on its commands, just type HELP from the adrci> Prompt.

C:\>ADRCI

ADRCI: Release 11.2.0.1.0 - Production on Fri Jul 29 09:13:03 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
ADR base = "c:\app"
adrci> help

 HELP [topic]
   Available Topics:
        CREATE REPORT
        ECHO
        EXIT
        HELP
        HOST
        IPS
        PURGE
        RUN
        SET BASE
        SET BROWSER
        SET CONTROL
        SET ECHO
        SET EDITOR
        SET HOMES | HOME | HOMEPATH
        SET TERMOUT
        SHOW ALERT
        SHOW BASE
        SHOW CONTROL
        SHOW HM_RUN
        SHOW HOMES | HOME | HOMEPATH
        SHOW INCDIR
        SHOW INCIDENT
        SHOW PROBLEM
        SHOW REPORT
        SHOW TRACEFILE
        SPOOL

There are other commands intended to be used directly by Oracle, type "HELP EXTENDED" to see the list


You can also type ADRCI -HELP from the OS prompt to get help to use it in Batch Mode:

C:\>ADRCI -HELP
Syntax:
   adrci [-help] [script=script_filename]
         [exec = "one_command [;one_command;...]"]

Options      Description                     (Default)
-----------------------------------------------------------------
script       script file name                (None)
help         help on the command options     (None)
exec         exec a set of commands          (None)
-----------------------------------------------------------------

As an example, to run the SHOW HOMES command followed by the SHOW INCIDENT command, enter the following:

C:\>ADRCI EXEC="SHOW HOMES; SHOW INCIDENT"

ADR Homes:
diag\clients\user_dpafumi\host_1009254443_76
diag\clients\user_system\host_1009254443_76
diag\rdbms\die\die
diag\tnslsnr\vop-dpafumi\listener

ADR Home = c:\app\diag\clients\user_dpafumi\host_1009254443_76:
*************************************************************************
0 rows fetched

ADR Home = c:\app\diag\clients\user_system\host_1009254443_76:
*************************************************************************
0 rows fetched

ADR Home = c:\app\diag\rdbms\die\die:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY
 CREATE_TIME
-------------------- -----------------------------------------------------------
 ----------------------------------------
31201                ORA 4030
 2011-06-14 14:16:42.231000 -07:00
30201                ORA 4030
 2011-06-14 14:16:42.303000 -07:00
30249                ORA 4030
 2011-06-14 14:16:42.387000 -07:00
31202                ORA 7445 [ksmpgd_get_exclusive()+44]
 2011-06-14 14:16:42.920000 -07:00
30202                ORA 4030
 2011-06-14 14:16:46.575000 -07:00
30203                ORA 4030
 2011-06-14 14:16:49.294000 -07:00
6 rows fetched


Setting the ADRCI Homepath Before Using ADRCI Commands
When diagnosing a problem, you may want to work with diagnostic data from multiple database instances or components, or you may want to focus on diagnostic data from one instance or component. To work with diagnostic data from multiple instances or components, you must ensure that the ADR homes for all of these instances or components are "current."

You use the SET HOMEPATH command to set one or more ADR homes to be current. If ADR base is /u01/app/oracle and you want to set the homepath to /u01/app/oracle/diag/rdbms/orclbi/orclbi2/, you use this command:
adrci> set homepath diag/rdbms/orclbi/orclbi2

To see the current homes you can use:
adrci> show homes
ADR Homes:
diag\clients\user_dpafumi\host_1009254443_76
diag\clients\user_system\host_1009254443_76
diag\rdbms\die\die
diag\tnslsnr\vop-dpafumi\listener


Viewing the Alert Log
At the ADRCI prompt, enter the following command:
adrci> show alert

If more than one ADR home is current, you are prompted to select a single ADR home from a list. The alert log is displayed, with XML tags stripped, in your default editor.

SHOW ALERT -TAIL
This displays the last 10 entries from the alert log in your terminal session.

SHOW ALERT -TAIL 50
This displays the last 50 entries from the alert log in your terminal session.

SHOW ALERT -TAIL -F
This displays the last 10 entries in the alert log, and then waits for more messages to arrive in the alert log. As each message arrives, it is appended to the display. This command enables you to perform "live monitoring" of the alert log. Press CTRL-C to stop waiting and return to the ADRCI prompt.

SPOOL /home/steve/MYALERT.LOG
SHOW ALERT -TERM
SPOOL OFF
This outputs the alert log, without XML tags, to the file /home/steve/MYALERT.LOG.

SHOW ALERT -P "MESSAGE_TEXT LIKE '%ORA-600%'"
This displays only alert log messages that contain the string 'ORA-600'.


Finding Trace Files
ADRCI enables you to view the names of trace files that are currently in the automatic diagnostic repository (ADR). You can view the names of all trace files in the ADR, or you can apply filters to view a subset of names. For example, ADRCI has commands that enable you to:
You can combine filtering functions by using the proper command line parameters.
The following statement lists the names of all trace files in the current ADR homes, without any filtering:
SHOW TRACEFILE

The following statement lists the name of every trace file that has the string 'mmon' in its file name. The percent sign (%) is used as a wildcard character, and the search string is case sensitive.
SHOW TRACEFILE %mmon%

This statement lists the name of every trace file that is located in the /home/steve/temp directory and that has the string 'mmon' in its file name:
SHOW TRACEFILE %mmon% -PATH /home/steve/temp

This statement lists the names of trace files in reverse order of last modified time. That is, the most recently modified trace files are listed first.
SHOW TRACEFILE -RT

This statement lists the names of all trace files related to incident number 1681:
SHOW TRACEFILE -I 1681


Viewing Incidents
The ADRCI SHOW INCIDENT command displays information about open incidents. For each incident, the incident ID, problem key, and incident creation time are shown. If the ADRCI homepath is set so that there are multiple current ADR homes, the report includes incidents from all of them.

SHOW INCIDENT


ADR Home = c:\app\diag\clients\user_dpafumi\host_1009254443_76:
*************************************************************************
0 rows fetched

ADR Home = c:\app\diag\clients\user_system\host_1009254443_76:
*************************************************************************
0 rows fetched

ADR Home = c:\app\diag\rdbms\die\die:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY
 CREATE_TIME
-------------------- ----------------------------------------------------------
 ----------------------------------------
31201                ORA 4030
 2011-06-14 14:16:42.231000 -07:00
30201                ORA 4030
 2011-06-14 14:16:42.303000 -07:00
30249                ORA 4030
 2011-06-14 14:16:42.387000 -07:00
31202                ORA 7445 [ksmpgd_get_exclusive()+44]
 2011-06-14 14:16:42.920000 -07:00
30202                ORA 4030
 2011-06-14 14:16:46.575000 -07:00
30203                ORA 4030
 2011-06-14 14:16:49.294000 -07:00

The following are variations on the SHOW INCIDENT command:

SHOW INCIDENT -MODE BRIEF
SHOW INCIDENT -MODE DETAIL
These commands produce more detailed versions of the incident report.

SHOW INCIDENT -MODE DETAIL -P "INCIDENT_ID=1681"
This shows a detailed incident report for incident 1681 only.


Packaging Incidents
You can use ADRCI commands to package one or more incidents for transmission to Oracle Support for analysis.
More information HERE


PURGE
Purges diagnostic data in the current ADR home, according to current purging policies. Only ADR contents that are due to be purged are purged.
Diagnostic data in the ADR has a default lifecycle. For example, information about incidents and problems is subject to purging after one year, whereas the associated dump files (dumps) are subject to purging after only 30 days.
The adrci command ‘purge’ can be used to purge entries from the alert log. Note that this purge will only apply to the XML based alert log and not the text file based alert log which still has to be maintained using OS commands.
The purge command takes the input in minutes and specifies the number of minutes for which records should be retained.

purge
This example purges all diagnostic data in the current ADR home based on the default purging policies:

purge -i 123 456
This example purges all diagnostic data for all incidents between 123 and 456:

purge -age 60 -type incident
This example purges all incident data from the last hour:

So to purge all alert log entries older than 7 days the following command will be used:
purge -age 10080 -type ALERT

This automatic PURGE is done by the MMON background process.
There are two time attributes which are used to manage the retention of information in ADR. Both attributes correspond to a number of hours after which the MMON background process purges the expired ADR data.
The ADRCI command show control will show us what the current purge settings are as shown below.
adrci> show control
ADR Home = /u01/app/oracle/diag/rdbms/ttrlwiki/ttrlwiki: ************************************************************************* ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 1095473802 720 8760 2010-07-07 08:46:56.405618 +08:00 2010-08-22 22:14:11.443356 +08:00 1 2 76 1 2010-07-07 08:46:56.405618 +08:00

In this case it is set to the defaults of 720 hours (30 days) for the Short Term and 8760 hours (One year) for the long term category.
We can change this by using the ADRCI command ‘set control’
In this example we are changing the retention to 15 days for the Short Term policy attribute (note it is defined in Hours)
adrci> set control (SHORTP_POLICY =360)

More resources
http://uhesse.wordpress.com/2011/06/01/adrci-a-survival-guide-for-the-dba/
http://psoug.org/reference/adr.html