Automatic Diagnostic Repository (ADR) in Oracle Database 11g Release 1 (ADRCI)

The Automatic Diagnostics Repository (ADR) is a hierarchical file-based repository for handling diagnostic information.

Related articles.

Organisation

The Automatic Diagnostics Repository (ADR) is a hierarchical file-based repository for diagnostic information, made up of a directory structure like the following.

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/alert
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/cdump
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/hm
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/incident
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/trace
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/{others}

Typical installations will have the ADR_BASE set to the ORACLE_BASE . For example, a database called "orcl" might have a structure like the following.

/u01/app/oracle/diag/rdbms/orcl/orcl/alert
/u01/app/oracle/diag/rdbms/orcl/orcl/cdump
/u01/app/oracle/diag/rdbms/orcl/orcl/hm
/u01/app/oracle/diag/rdbms/orcl/orcl/incident
/u01/app/oracle/diag/rdbms/orcl/orcl/trace

The locations of the various diagnostics directories can be displayed using the V$DIAG_INFO view.

COLUMN name FORMAT A25
COLUMN value FORMAT A65
SELECT name, value FROM v$diag_info;
NAME                      VALUE
------------------------- -----------------------------------------------------------------
Diag Enabled              TRUE
ADR Base                  /u01/app/oracle
ADR Home                  /u01/app/oracle/diag/rdbms/orcl/orcl
Diag Trace                /u01/app/oracle/diag/rdbms/orcl/orcl/trace
Diag Alert                /u01/app/oracle/diag/rdbms/orcl/orcl/alert
Diag Incident             /u01/app/oracle/diag/rdbms/orcl/orcl/incident
Diag Cdump                /u01/app/oracle/diag/rdbms/orcl/orcl/cdump
Health Monitor            /u01/app/oracle/diag/rdbms/orcl/orcl/hm
Default Trace File        /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19975.trc
Active Problem Count      0
Active Incident Count     0
11 rows selected.
SQL>

Setting the ADR Location

For the database, the ADR location is specified using the diagnostic_dest initialisation parameter.

SQL> ALTER SYSTEM SET diagnostic_dest='/u01/app/oracle';
System altered.
SQL> show parameter diagnostic_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/app/oracle/
SQL>

For the listener, the ADR location is set by editing the "$ORACLE_HOME/network/admin/listener.ora" file to include the following entry.

ADR_BASE_LISTENER = /u01/app/oracle

The listener must be restarted for the change to take effect.

$ lsnrctl stop
$ lsnrctl start

If the "diag" directory is not already present, it will be created when the listener or database are started with the new setting.

ADR Command Interpreter (ADRCI)

You can navigate the contents of ADR using operating systems command line or file browsing tools, but Oracle also provide the ADR Command Interpreter (ADRCI), which is probably a better solution for many tasks.

Assuming your environment is set correctly, you should be able to start ADRCI by issuing the "adrci" command.

$ adrci
ADRCI: Release 11.2.0.3.0 - Production on Mon Jun 9 13:41:29 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
ADR base = "/u01/app/oracle"
adrci>

When started, you should see the "ADR base =" message. If instead you get a message saying no base is set, you can manually set it using the SET BASE command.

adrci> set base /u01/app/oracle

Typing the HELP command provides a list of help topics related to available commands.

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
adrci>

As the help suggests, you can drill down by asking for help on specific commands.

adrci> help show alert
  Usage: SHOW ALERT [-p <predicate_string>]  [-term]
                    [ [-tail [num] [-f]] | [-file <alert_file_name>] ]
  Purpose: Show alert messages.
  Options:
    [-p <predicate_string>]: The predicate string must be double-quoted.
    The fields in the predicate are the fields:
        ORIGINATING_TIMESTAMP         timestamp
        NORMALIZED_TIMESTAMP          timestamp
        ORGANIZATION_ID               text(65)
        COMPONENT_ID                  text(65)
        HOST_ID                       text(65)
        HOST_ADDRESS                  text(17)
        MESSAGE_TYPE                  number
        MESSAGE_LEVEL                 number
        MESSAGE_ID                    text(65)
        MESSAGE_GROUP                 text(65)
        CLIENT_ID                     text(65)
        MODULE_ID                     text(65)
        PROCESS_ID                    text(33)
        THREAD_ID                     text(65)
        USER_ID                       text(65)
        INSTANCE_ID                   text(65)
        DETAILED_LOCATION             text(161)
        UPSTREAM_COMP_ID              text(101)
        DOWNSTREAM_COMP_ID            text(101)
        EXECUTION_CONTEXT_ID          text(101)
        EXECUTION_CONTEXT_SEQUENCE    number
        ERROR_INSTANCE_ID             number
        ERROR_INSTANCE_SEQUENCE       number
        MESSAGE_TEXT                  text(2049)
        MESSAGE_ARGUMENTS             text(129)
        SUPPLEMENTAL_ATTRIBUTES       text(129)
        SUPPLEMENTAL_DETAILS          text(129)
        PROBLEM_KEY                   text(65)
    [-tail [num] [-f]]: Output last part of the alert messages and
    output latest messages as the alert log grows. If num is not specified,
    the last 10 messages are displayed. If "-f" is specified, new data
    will append at the end as new alert messages are generated.
    [-term]: Direct results to terminal. If this option is not specified,
    the results will be open in an editor.
    By default, it will open in emacs, but "set editor" can be used
    to set other editors.
    [-file <alert_file_name>]: Allow users to specify an alert file which
    may not be in ADR. <alert_file_name> must be specified with full path.
    Note that this option cannot be used with the -tail option
  Examples:
    show alert
    show alert -p "message_text like '%incident%'"
    show alert -tail 20
adrci>

Viewing the Alert Log

Depending on your setup, you may have multiple homes (Database, Listener, Grid Infrastructure etc.). It is important you point to the correct home before issuing any commands.

adrci> show home
ADR Homes:
diag/rdbms/orcl/orcl
diag/tnslsnr/ol6-112/listener
adrci> set home diag/rdbms/orcl/orcl
adrci>

With the home set, we can look at the contents of the alert log. The help text for the SHOW ALERT command provides some examples.

Examples:
  show alert
  show alert -p "message_text like '%incident%'"
  show alert -tail 20

We might decide to look for errors in the alert log with a command like the following.

adrci> show alert -p "message_text like '%ORA-%'"
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
Output the results to file: /tmp/alert_1781_13990_orcl_1.ado
adrci>

We could also do a UNIX-style "tail -f" of the alert log using the following command, even on Windows.

adrci> show alert -tail -f

Viewing Trace Files

The alert log will often make reference to trace files. These can be viewed directly using the SHOW TRACE command.

adrci> show trace /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72697/orcl_ora_18310_i72697.trc

Managing Diagnostic Information (Purging Trace Files)

The PURGE command can be used to remove some or all of the diagnostic information from the repository. The HELP PURGE command describes the usage.

adrci> help purge
  Usage: PURGE [[-i <id1> | <id1> <id2>] |
               [-age <mins> [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]]]:
  Purpose: Purge the diagnostic data in the current ADR home. If no
           option is specified, the default purging policy will be used.
  Options:
    [-i id1 | id1 id2]: Users can input a single incident ID, or a
    range of incidents to purge.
    [-age <mins>]: Users can specify the purging policy either to all
    the diagnostic data or the specified type. The data older than <mins>
    ago will be purged
    [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]: Users can specify what type of
    data to be purged.
  Examples:
    purge
    purge -i 123 456
    purge -age 60 -type incident
adrci>

So to purge all diagnostic information, including trace files, older than 1 month you would issue the following.

adrci> purge -age 43200
adrci>

Problems and Incidents

When a problem occurs on the database, it is logged in the alert log and an incident is created. Doing a search of the alert log with the following command will reveal some information about the incident.

adrci> show alert -p "message_text like '%incident%'"
2013-10-09 10:05:17.154000 +01:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18310.trc  (incident=72697):
ORA-07445: exception encountered: core dump [kghalo()] [SIGSEGV] [ADDR:0x9FFFFFFFBFFFF000]
  [PC:0x40000000065AE680] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72697/orcl_ora_18310_i72697.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2013-10-09 10:05:36.501000 +01:00
Dumping diagnostic data in directory=[cdmp_20131009100536], requested by (instance=1, osid=18310), summary=[incident=72697].

A simpler solution is to display the problem from the command line directly using the SHOW PROBLEM command.

adrci> show problem
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1                    ORA 3137 [3120]                                             71593                2013-08-09 10:16:43.714000 +01:00
2                    ORA 7445 [kghalo()]                                         72697                2013-10-09 10:05:17.154000 +01:00
2 rows fetched
adrci>

The same problem can occur multiple times, so a single problem may result in multiple incidents. Incidents are displayed using the SHOW INCIDENT command.

adrci> show incident
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
71593                ORA 3137 [3120]                                             2013-08-09 10:16:43.714000 +01:00
72697                ORA 7445 [kghalo()]                                         2013-10-09 10:05:17.154000 +01:00
2 rows fetched
adrci>

Once you've identified an incident of interest, you can look at it in more detail by altering the mode in the SHOW INCIDENT command.

adrci> show incident -mode detail -p "incident_id=72697"
ADR Home = /u01/app/oracle/diag/rdbms/lstu/lstu:
*************************************************************************
**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
   INCIDENT_ID                   72697
   STATUS                        ready
   CREATE_TIME                   2013-10-09 10:05:17.154000 +01:00
   PROBLEM_ID                    2
   CLOSE_TIME                    <NULL>
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  7445
   ERROR_ARG1                    kghalo()
   ERROR_ARG2                    SIGSEGV
   ERROR_ARG3                    ADDR:0x9FFFFFFFBFFFF000
   ERROR_ARG4                    PC:0x40000000065AE680
   ERROR_ARG5                    Address not mapped to object
   ERROR_ARG6                    <NULL>
   ERROR_ARG7                    <NULL>
   ERROR_ARG8                    <NULL>
   ERROR_ARG9                    <NULL>
   ERROR_ARG10                   <NULL>
   ERROR_ARG11                   <NULL>
   ERROR_ARG12                   <NULL>
   SIGNALLING_COMPONENT          <NULL>
   SIGNALLING_SUBCOMPONENT       <NULL>
   SUSPECT_COMPONENT             <NULL>
   SUSPECT_SUBCOMPONENT          <NULL>
   ECID                          <NULL>
   IMPACTS                       0
   PROBLEM_KEY                   ORA 7445 [kghalo()]
   FIRST_INCIDENT                72697
   FIRSTINC_TIME                 2013-10-09 10:05:17.154000 +01:00
   LAST_INCIDENT                 72697
   LASTINC_TIME                  2013-10-09 10:05:17.154000 +01:00
   IMPACT1                       0
   IMPACT2                       0
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      ProcId
   KEY_VALUE                     287.6
   KEY_NAME                      Client ProcId
   KEY_VALUE                     oracle@biro01 (TNS V1-V3).18310_1
   KEY_NAME                      ECID
   KEY_VALUE                     55774dc6ecfa57a3:2a5146fe:13ffc25ce34:-8000-0000000000055601.1
   KEY_NAME                      PQ
   KEY_VALUE                     (0, 1381309514)
   KEY_NAME                      SID
   KEY_VALUE                     1125.45841
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/lstu/trace/orcl_ora_18310.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72697/orcl_ora_18310_i72697.trc
1 rows fetched
adrci>

Creating Packages to Send to Oracle Support

If you can't solve the problem yourself, you can use the Incident Packaging Service (IPS) to gather all pertinent information so it can be sent to Oracle Support. This should reduce the amount of time you waste trying to identify what information is necessary for them to identify and solve the problem.

First, create the package using the problem ID displayed by the SHOW PROBLEM command.

adrci> ips create package problem 2 correlate all
Created package 1 based on problem id 2, correlation level all
adrci>

Next, create a zip to send to Oracle Support by specifying the package number displayed by the above command.

adrci> ips generate package 1 in "/tmp"
Generated package 1 in file /tmp/IPSPKG_20140610100342_COM_1.zip, mode complete
adrci>

The package is now zipped and ready to upload to Oracle Support.

$ ls /tmp/IPSPKG*.zip
/tmp/IPSPKG_20140610100342_COM_1.zip
$

Cloud Control Integration

Incident management is integrated into Enterprise Manager Cloud Control. Navigate to the database of interest, the use the "Oracle Database > Diagnostics > Support Workbench" menu option. Incidents can be selected, packaged and uploaded directly to Oracle Support from Cloud Control.

For more information see:

Hope this helps. Regards Tim...