Automatic Diagnostic Repository (ADR) – What it Can Do for You

posted May 21, 2014, 1:17 PM by

Tom Roehr, Senior Consultant

Introduced in Oracle Database 11G, Automatic Diagnostic Repository (ADR) is a file-based repository that aids the DBA in identifying, diagnosing, and resolving problems. Oracle’s stated goals for ADR are:

  •  Providing first-failure diagnosis
  •  Allowing for problem prevention
  •  Limiting damage and interruptions after a problem is detected
  •  Reducing problem diagnostic time
  •  Reducing problem resolution time
  •  Simplifying customer interaction with Oracle Support

ADR accomplishes this with new features like an always-on memory-based tracing system to capture diagnosis information from many different database components when a problem is detected, similar to an aircraft’s “black box”.

Another new feature, Incident Packaging Services (IPS), simplifies the task of collecting diagnostic data (traces, dumps, log files) related to a critical error. ADR assigns an incident number to a detected error and adds it to all diagnostic information that’s related to it. A DBA can then easily package all related information into a zip file to upload to Oracle Support.

ADR defines a problem as an error such as an ORA-00600 internal error. Problems are tracked inside of ADR by a problem key, which consists of a text string, an error code and parameters that describe the problem.

An incident is a specific occurrence of a problem. ADR assigns a unique number for each incident, writes an entry in the alert log, sends an alert to OEM, gathers diagnostic information, and stores that information in an ADR sub-folder.

Using the ADRCI command-line application, you can then see the information saved for an incident, add or remove files from the incident inventory, and save all the related files into a zip file.

To use ADRCI, you just need execute permissions. Since ADR is outside of the database, you can access it without having the instance available.

To use ADRCI, just enter it at the command line.

 

oracle$ adrci

ADRCI: Release 11.2.0.3.0 - Production on Mon May 19 16:15:35 2014

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

ADR base = "/u01/oracle"

adrci>

 

Help regarding command syntax is available by entering help.

 

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>

 

You can Llst problems that have been recorded using the SHOW PROBLEMS command:

 

adrci> show problems

 ADR Home = /oracle/product/diag/tnslsnr/linuxdb01/listener:

*************************************************************************

0 rows fetched

 

ADR Home = /oracle/product/diag/rdbms/demodb/demodb:

*************************************************************************

PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                            

-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------

1                            ORA 3137 [12333]                                            36353                2013-08-09 00:03:29.431000 -05:00       

adrci>

 

ADR stores the repository information in a specific “home” for each instance of the product type, product id and instance id.

You can limit the ADRCI commands to a specific ADR home with the SET HOME command. View the set of ADR homes with the SHOW HOMES command in order to obain the correct path to use with SET HOME.

 

adrci> show homes

ADR Homes:

diag/tnslsnr/linuxdb01/listener

diag/rdbms/demodb/demodb

adrci> set home diag/rdbms/demodb/demodb

adrci> show incidents

 

ADR Home = /oracle/product/diag/rdbms/demodb/demodb:

*************************************************************************

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME                             

-------------------- ----------------------------------------------------------- ----------------------------------------

36353                ORA 3137 [12333]                                            2013-08-09 00:03:29.431000 -05:00      

1 rows fetched

 

adrci>

adrci> show problems

 

ADR Home = /oracle/product/diag/rdbms/demodb/demodb:

*************************************************************************

PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                            

-------------------- ------------------------------------------------------------ -------------------- ----------------------------------------

1                    ORA 3137 [12333]                                            36353                2013-08-09 00:03:29.431000 -05:00      

1 rows fetched

 

To prepare the ADR information you want to save, you need to create a package. A package can be created from using a problem ID, problem Key or even just a time range. This example creates a package from a problem id using IPS GENERATE PACKAGE:

adrci> ips create package problem 1

Created package 1 based on problem id 1, correlation level typical

adrci>

 

To show the contents of the package, use the IPS SHOW FILES command:

 

adrci> ips show files package 1

   FILE_ID                1

   FILE_LOCATION          <ADR_HOME>/alert

   FILE_NAME              log.xml

   LAST_SEQUENCE          1

   EXCLUDE                Included

   FILE_ID                2

   FILE_LOCATION          <ADR_HOME>/trace

   FILE_NAME              alert_svfe.log

   LAST_SEQUENCE          1

   EXCLUDE                Included

. . .

   FILE_ID                27

   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1

   FILE_NAME              manifest_1_1.txt

   LAST_SEQUENCE          1

   EXCLUDE                Included

 adrci>

 

Next, save the package to a zip file using the IPS GENERATE PACKAGE command:

 

adrci> ips generate package 1 in /home/oracle/diagnostics

Generated package 1 in file /home/oracle/diagnostics/IPSPKG_20140519155358_COM_1.zip, mode complete

adrci>

 

You now have all the incident diagnostic files related to the problem in one file read, which is ready to be uploaded to Oracle Support. In addition to IPS, ADR offers more tools for the DBA. I will cover additional topics like Using ADR’s Health Checks, Data Recovery Advisor and SQL Test Case Builder in future blogs.

 

References:

About the Oracle Database Fault Diagnosability Infrastructure

11g Understanding Automatic Diagnostic Repository. (Doc ID 422893.1)

ADR Different Methods to Create IPS Package (Doc ID 411.1)

Share with your networkTweet about this on TwitterShare on LinkedInShare on FacebookDigg thisEmail this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *

WANT TO LEARN MORE?

Share with your networkTweet about this on TwitterShare on LinkedInShare on FacebookDigg thisEmail this to someone