Skip to content


February 20, 2012

11g Understanding Automatic Diagnostic Repository.

What is New in 11g?
In 11g, RDBMS diagnostic data has been reorganized and is stored inside a common directory structure, the Automatic Diagnostic Repository (ADR). An ADR is a centralized directory structure where one can find trace files, alert messages, incident dumps, core files, etc.

Automatic Diagnostic Repository ( ADR ) :

All trace files, core files, and the alert files are now organized into a directory structure comprising the Automatic Diagnostic Repository (ADR).

The ADR is a file-based repository for database diagnostic data. It has a unified directory structure across multiple instances and multiple products.
Beginning with Release 11g, the database, Automatic Storage Management (ASM), Cluster Ready Services (CRS), 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.
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. ADR’s unified directory structure, consistent diagnostic data formats across products and instances, and a unified set of tools enable customers and Oracle Support to correlate and analyze diagnostic data across multiple instances.

Problems and Incidents :

Problem : is a critical error in the database
Eg : ora-600 , ora-7445 , ora-4031 etc.

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.
Eg: ‘ORA 4030’ , ‘ORA 600 [ktfacht1-0]’

Incident : is a single occurance of a problem . Each incident has a numeric incident id.

Where is ADR located :

The location of the ADR is controlled by the Oracle “diagnostic_dest” parameter.
Path specified in the ‘diagnostic_dest’ parameter defines the ADR root directory,ADR BASE.
The first subdirectory inside an ADR (under the <adr_base> directory) is always named “diag”

For example, if the ‘diagnostic_dest’ and thus the <adr_base> is specified as “$ORACLE_HOME/log”, then expect to find the subdirectory “$ORACLE_HOME/log/diag”. Below this will be <adr_home> .
Any number of instances/components can share same ADR BASE. Under ADR BASE there will be individual ADR HOMES.
Under ADR BASE ,the address of an <adr_home> will be similar to :
Inside each ADR home, you can find several subdirectories, each for storing a specific type of diagnostic data. Among the subdirectories, you should be able to find TRACE, ALERT, INCIDENT, CDUMP etc.

ADR HOME contents :

You will find the following directories under ADR HOME –

Alert : The alert directory contains the XML alert log

Cdump : core dumps are stored in this directory

Trace : Process trace files and Alert.log are stored in the trace directory. ‘Background_dump_dest’ and ‘user_dump_dest’ are now ignored in 11g. Now all the trace files will be generated in ‘trace’ directory.

Incident : The incident directory stores dump files created when critical errors are encountered.
Each occurrence of a critical error( incident ) is given its own incident directory, with the incident ID used to form the directory name.

Metadata : The metadata directory stores a series of files that contain diagnostic metadata.

HM : The hm directory stores reports for health checks

Incpkg, ir, lck, sweep : These directories contain internal diagnosability framework state.

DIAGNOSTIC_DEST – Default value :

If environment variable ORACLE_BASE is set, DIAGNOSTIC_DEST is set to the directory designated by ORACLE_BASE.
If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is set to ORACLE_HOME/log


For each database , you can query v$diag_info to check its ADR locations.
This shows us the ADR BASE , ADR home , trace file locations , XML Alert location , incident dump locations , core dump and health monitor reports.
Also gives us the default session trace ( for the current session ) and number of problems , incidents reported in the database.

ADR Command Interpreter (ADRCI) :

ADRCI is the command line utility using which ADR is accessed.

Enter the following command at the operating system command prompt:


The utility starts and displays the following prompt:


There are various commands that can be executed from ‘adrci’ to view Alert log , trace files , incidents reported , etc.

There is no need to log in to ADRCI, because the data in ADR is just for diagnostic purposes and not intended to be secure. ADR data is secured only by operating system permissions on the ADR directories.

Sample Practical Usage:

col VALUE for a70
col NAME for a30
set line 200
select name, value from v$diag_info;

SQL> select name, value from v$diag_info;
NAME                           VALUE
------------------------------ ----------------------------------------------------------------------
Diag Enabled TRUE
ADR Base /u01/app/oracle
ADR Home /u01/app/oracle/diag/rdbms/test11g/test11G
Diag Trace /u01/app/oracle/diag/rdbms/test11g/test11G/trace
Diag Alert /u01/app/oracle/diag/rdbms/test11g/test11G/alert
Diag Incident /u01/app/oracle/diag/rdbms/test11g/test11G/incident
Diag Cdump /u01/app/oracle/diag/rdbms/test11g/test11G/cdump
Health Monitor /u01/app/oracle/diag/rdbms/test11g/test11G/hm
Default Trace File /u01/app/oracle/diag/rdbms/test11g/test11G/trace/test11G_ora_14503.trc
Active Incident Count 3
11 rows selected.

maldevsrv:/opt/oracle: adrci

ADRCI: Release - Production on Thu Mar 31 10:17:49 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"

adrci> show homes

ADR Homes:



adrci> set homepath diag/rdbms/test11g/test11G
adrci> help
 HELP [topic]
   Available Topics:
        SET BASE
        SET ECHO
        SET EDITOR
        SHOW ALERT
        SHOW BASE
        SHOW HM_RUN
 There are other commands intended to be used directly by Oracle, type
 "HELP EXTENDED" to see the list
adrci> help show incident
adrci> show incident -mode basic
ADR Home = /u01/app/oracle/diag/rdbms/test11g/TEST11G:
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
7394                 ORA 7445 [kgdsdst()+32]                                     2011-02-22 09:17:46.543000 -05:00
7393                 ORA 4030                                                    2011-02-22 09:17:41.309000 -05:00
7361                 ORA 7445 [sdbgrfcvp_convert_pathinfo()+480]                 2011-02-22 09:17:46.703000 -05:00
3 rows fetched
adrci> show incident -mode detail -p "incident_id=7394"

ADR Home = /u01/app/oracle/diag/rdbms/test11g/TEST11G:
   INCIDENT_ID                   7394
   STATUS                        ready
   CREATE_TIME                   2011-02-22 09:17:46.543000 -05:00
   PROBLEM_ID                    1
   CLOSE_TIME                    <NULL>
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  7445
   ERROR_ARG1                    kgdsdst()+32
   ERROR_ARG2                    SIGSEGV
   ERROR_ARG3                    ADDR:0xFFFFFFFF7FFC6DC8
   ERROR_ARG4                    PC:0x106C1E320
   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>
   ECID                          <NULL>
   IMPACTS                       0
   PROBLEM_KEY                   ORA 7445 [kgdsdst()+32]
   FIRST_INCIDENT                7394
   FIRSTINC_TIME                 2011-02-22 09:17:46.543000 -05:00
   LAST_INCIDENT                 7394
   LASTINC_TIME                  2011-02-22 09:17:46.543000 -05:00
   IMPACT1                       0
   IMPACT2                       0
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      PQ
   KEY_VALUE                     (16777254, 1298384257)
   KEY_NAME                      Client ProcId
   KEY_VALUE                     oracle@maldevsrv(TNS V1-V3).14569_1
   KEY_NAME                      SID
   KEY_VALUE                     361.1931
   KEY_NAME                      ProcId
   KEY_VALUE                     24.168
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/test11g/TEST11G/trace/TEST11G_ora_14569.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/test11g/TEST11G/incident/incdir_7394/TEST11G_ora_14569_i7394.trc
1 rows fetched

adrci> show alert

adrci> show alert -tail 10

adrci> show alert -tail 10

cat >

set homepath diag/rdbms/test11g/test11G
show alert -tail 10

maldevsrv:/opt/oracle/scripts: adrci

ADRCI: Release - Production on Thu Mar 31 10:31:19 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
2011-03-30 22:00:00.014000 -04:00
Starting background process VKRM
VKRM started with pid=29, OS id=3689
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
2011-03-30 22:00:21.309000 -04:00
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
2011-03-30 22:01:35.761000 -04:00
Thread 1 cannot allocate new log, sequence 45
Private strand flush not complete
  Current log# 2 seq# 44 mem# 0: /d05/oradata/TEST11G/redo02.log
2011-03-30 22:01:38.855000 -04:00
Thread 1 advanced to log sequence 45 (LGWR switch)
  Current log# 3 seq# 45 mem# 0: /d05/oradata/TEST11G/redo03.log
2011-03-31 02:00:00.006000 -04:00
Clearing Resource Manager plan via parameter
2011-03-31 03:14:05.381000 -04:00
Stopping background process CJQ0

adrci exec="show homes; show catalog"


adrci>> spool a
adrci>> show alert -tail 50
adrci>> spool off

adrci>>describe alert_ext

Now that the information is structured, you can search with precision. Suppose you want to search for lines in the
alert logs that match a specific value in a field. Here is an example:
adrci>> show alert -p “module_id=’DBMS_SCHEDULER’”
This shows all the lines written by processes with the module id dbms_scheduler. You can also use the inequality
operator (not containing DBMS_SCHEDULER):
adrci>>show alert -p “module_id != ‘DBMS_SCHEDULER’”
Likewise you can use the pattern-matching operators:
adrci>>show alert -p “module_id like ‘%SCHEDULER’”
The spool command works just like its namesake command in SQL*Plus. You can spool the output to a file:
adrci>> spool a
adrci>> show alert -tail 50
adrci>> spool off
 It creates a file (a.ado) containing the last 50 lines of the alert log. A great use of this option is to extract specific
types of messages from the alert log. If you want to extract the Streams related statements from the alert log,
you would use:
adrci> show alert -p “message_text like ‘%STREAM%’”
 You can see all the trace files generated in the ADR base directory from the adrci command prompt as well.
adrci>> show tracefile
 The above command shows a list of all the trace files generated in the ADR directory. To show specific types of
trace files( “reco”, for example) in reverse chronological order:
 adrci>>show tracefile %reco% -rt
 18-JUL-07 22:59:50 diag\rdbms\lapdb11\lapdb11\trace\lapdb11_reco_4604.trc
12-JUL-07 09:48:23 diag\rdbms\lapdb11\lapdb11\trace\lapdb11_reco_4236.trc
11-JUL-07 10:30:22 diag\rdbms\lapdb11\lapdb11\trace\lapdb11_reco_3256.trc
adrci offers many more options to view the alert log and related files in the most efficient manner. For a complete
description of adrci commands, see the documentation.
Listener log
adrci>  show tracefile
adrci> show trace listener_11.log
spool a.txt
show trace tail -10000 listener_11.log
spool off
show incident -p "problem_key='ORA 600 [ktspfmdb:objdchk_kcbnew_3]'"

show problem -orderby problem_key, problem_id 

show problem -p ORA-03137 -orderby problem_key, problem_id 

show problem [-p predicate_string] [-last num | -all]
[-orderby field1, field2, ...] [ASC|DSC]] 
ORA-03137: TTC protocol internal error : [12333] [4] [80] [82] [] [] [] []

adrci> set homepath diag/rdbms/maldev/MALDEV1
adrci> show alert -p "MESSAGE_TEXT LIKE '%ORA-03137%'" -term
ADR Home = /u01/app/oracle/diag/rdbms/maldev/MALDEV1:
2011-03-16 04:55:16.530000 -04:00
Errors in file /u01/app/oracle/diag/rdbms/maldev/MALDEV1/trace/MALDEV1_ora_13677.trc  (incident=192686):
ORA-03137: TTC protocol internal error : [12333] [9] [70] [73] [] [] [] []

adrci> show problem -orderby problem_key, problem_id
ADR Home = /u01/app/oracle/diag/rdbms/maldev/MALDEV1:
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
45                   ORA 29771                                                   438411               2011-08-09 05:15:41.802000 -04:00
15                   ORA 3137 [12333]                                            458514               2011-08-23 07:04:03.852000 -04:00
20                   ORA 7445 [vsnprintf()+8]                                    423232               2011-07-28 10:39:45.611000 -04:00
48 rows fetched

show problem -p "problem_id=27" 
adrci> show problem -p "problem_id=27"
ADR Home = /u01/app/oracle/diag/rdbms/maldev/MALDEV1:
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
27                   ORA 3137 [3120]                                             456194               2011-08-22 09:12:02.244000 -04:00

show problem -p "PROBLEM_KEY LIKE '%3137%'" 
adrci> show problem -p "PROBLEM_KEY LIKE '%3137%'"

ADR Home = /u01/app/oracle/diag/rdbms/maldev/MALDEV1:
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
15                   ORA 3137 [12333]                                            458514               2011-08-23 07:04:03.852000 -04:00
27                   ORA 3137 [3120]                                             456194               2011-08-22 09:12:02.244000 -04:00
2 rows fetched

adrci> show incident -p "PROBLEM_KEY LIKE '%3137%'"
ADR Home = /u01/app/oracle/diag/rdbms/rikdev/RIKDEV1:
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
192686               ORA 3137 [12333]                                            2011-03-16 04:55:16.530000 -04:00
192926               ORA 3137 [12333]                                            2011-03-16 05:01:01.323000 -04:00
192182               ORA 3137 [12333]                                            2011-03-16 16:28:05.240000 -04:00
192206               ORA 3137 [12333]                                            2011-03-458514               ORA 3137 [12333]                                            2011-08-23 07:04:03.852000 -04:00
42 rows fetched
adrci> SHOW TRACEFILE -p "PROBLEM_KEY LIKE '%3137%'"  => Listing all trace files 
SHOW INCIDENT -MODE DETAIL -P "ERROR_NUMBER=3137" => this is good shows tracefiles as well
adrci> show tracefile -i 192686,192926
show tracefile %3137% -rt

OS Commands from ADRCI Prompt:

adrci> set autoshell on
adrci> pwd
adrci> ls -ltr
total 8
drwxr-xr-x   2 oracle   dba           96 Mar 25  2010 crs
drwxrwxr-x  11 oracle   dba         1024 Mar 26  2010 diag
drwxr-xr-x   2 oracle   dba           96 Apr 21  2010 orasoft
drwxr-xr-x   6 oracle   dba           96 Jul  6  2010 cfgtoollogs
drwxr-xr-x   5 oracle   dba         1024 Feb 24  2011 emagent
drwxr-x---  21 oracle   dba         1024 Mar 28 07:28 admin
drwxr-x---   3 oracle   dba           96 Mar 28 07:28 flash_recovery_area
drwxr-xr-x   6 oracle   dba         1024 May 18 11:26 product
drwxr-xr-x   2 oracle   dba           96 Jun  1 14:57 checkpoints


adrci> host "pwd"

From → Oracle

One Comment
  1. Hareesh permalink


    Please find enhanced commoand (show alert) which will help us to find out issue related to particualr key word (in this case we can find easily trace files realted to Deadlock (Keyw word is deadlock)

    adrci>show alert -p “message_text like ‘%Deadlock%'”

    Output is similat to below:

    2012-03-12 18:37:56.141000 -04:00
    Global Enqueue Services Deadlock detected. More info in file
    2012-03-15 04:00:52.037000 -04:00
    Global Enqueue Services Deadlock detected. More info in file
    2012-03-18 18:54:34.518000 -04:00



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: