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)
