Automatic Diagnostic Repository

Automatic Diagnostic Repository

ADR – Automatic Diagnostic Repository (introduced in Oracle 11g) is a system managed repository for storing database alert logs, trace files, and any other diagnostic data previously controlled by any other init.ora parameters.
DIAGNOSTIC_DEST is the base location for all diagnostic directories. There are separate directories for the ASM database and the database (rdbms) instance. This repository is maintained in memory which enables database components to capture diagnostic data at its first failure for critical errors.

• In Oracle 11g, the init.ora parameters like user_dump_dest and background_dump_dest are deprecated. They have been replaced by the single parameter DIAGNOSTIC_DEST which identifies the location of the ADR.
• We can use V$DIAG_INFO view to list some important ADR locations such as ADR Base, ADR Home, Diagnostic Trace, Diagnostic Alert, Default Trace file, etc.
• Structure of ADR Directory is designed in such a way that uses consistent diagnostic data formats across products and instances, and an integrated set of tools enable customers and Oracle Support to correlate and analyse diagnostic data across multiple instances . ADR is a directory structure that is stored outside of the database. It is therefore available for problem diagnosis when the database is down.
• The ADR root directory is known as ADR base. Its location is set by the DIAGNOSTIC_DEST initialization parameter. If this parameter is omitted or left null, the database sets DIAGNOSTIC_DEST upon start-up as follows:
• 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

Note :

Within ADR base, there can be multiple ADR homes, where each ADR home is the root directory for all diagnostic data—traces, dumps, the alert log, and so on—for a particular instance of a particular Oracle product or component. For example, in an Oracle Real Application Clusters environment with Oracle ASM, each database instance, Oracle ASM instance, and listener has an ADR home.

• ADR homes reside in ADR base subdirectories that are named according to the product or component type.

The locations of the various diagnostics directories can be displayed using the below command:

To understand easily, please look into below table

Look into below flow chart to understand diagnostics directories location

Self-managing tasks by ADR that we need to know

• Trace files purged after 1 month (configurable)
• Incident/Problem metadata purged after 1 year (configurable)
• Note: Incident can be flagged as “don’t purge” to override purging

Repeated incidents are flood controlled (5 dumps per hour per problem)

• The ADR location is specified using the diagnostic_dest initialisation parameter. If you haven’t set yet then see below that how we can set ADR location:
• SQL> alter system set diagnostic_dest='/u01/app/ora12c'; System altered. SQL> show parameter diagnostic_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ diagnostic_dest string /u01/app/ora12c

  1 2 3 4 5 6

  SQL>  alter system set diagnostic_dest='/u01/app/ora12c'; System altered. SQL> show parameter diagnostic_dest NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ diagnostic_dest                      string      /u01/app/ora12c

  
  For the listener, the ADR location is set by editing the listener file to include the following entry.

  ADR_BASE_LISTENER= /u01/app/ora12c

  To add this entry type below command and save the listener file.

  [ora12c@CentOS7 ~]$ vi $ORACLE_HOME/network/admin/listener.ora

  1	[ora12c@CentOS7 ~]$ vi $ORACLE_HOME/network/admin/listener.ora

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

  [ora12c@CentOS7 ~]$ lsnrctl stop

  [ora12c@CentOS7 ~]$ lsnrctl start

Automatic Diagnostic Repository Command Interpreter (ADRCI)

The ADR Command Interpreter (ADRCI) is a command-line tool that we use to manage Oracle Database diagnostic data. ADRCI is a command-line tool that is part of the fault diagnosability infrastructure introduced in Oracle Database Release 11g. ADRCI enables:

• Viewing diagnostic data within the Automatic Diagnostic Repository (ADR).
• Viewing Health Monitor reports.
• Packaging of incident and problem information into a zip file for transmission to Oracle Support.

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.

To use ADRCI in interactive mode:

Set your environment properly, start ADRCI by issuing the command “adrci”

Start  using ADRCI by following command :

In this case, any ADRCI command that you run, assuming that the command supports more than one current ADR home, works with diagnostic data from both ADR homes. If you were to set the home path to /diag/rdbms/orcl/orcl, only the ADR home for the instance with SID orcl would be current.

Now set home path

In this case, any ADRCI command that you run would work with diagnostic data from this single ADR home only.

To get list of adrci command type help command as below:

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

Viewing the Alert Log:

The alert log is written as both an XML-formatted file and as a text file. We can view either format of the file with any text editor, or we can run an ADRCI command to view the XML-formatted alert log with the XML tags stripped. By default, ADRCI displays the alert log in your default editor

The following are variations on the SHOW ALERT command:

This displays the last portion of the alert log (the last 10 entries) in your terminal session.

This displays the last 50 entries in the alert log in your terminal session.

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.

If we want to filter the alert log file then we can filter as below:

===========================================================================

ADR Home = /u01/app/ora12c/diag/rdbms/orcl/orcl:

Finding Trace Files: 

ADRCI enables us to view the names of trace files that are currently in the automatic diagnostic repository (ADR). We can view the names of all trace files in the ADR, or we can apply filters to view a subset of names. For example, ADRCI has commands that enable us to:

• Obtain a list of trace files whose file name matches a search string.
• Obtain a list of trace files in a particular directory.
• Obtain a list of trace files that pertain to a particular incident.

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.

Checking a particular trace file :

Automatically purging

Sometimes we are facing with space related issues due to the huge number of trace file generation. Automatic purging can help us in this situations if set short retention time for trace files.

The automatic purging runs on schedule defined in retention policy .To check current policy for home, we can use two methods:

Or

The above command will show the shortp_policy and longp_policy and this policy can the changed as below:

By default retention is 720 hours for short policy and 8760 hours for long policy.
Short policy include the following files: (Trace files, Core dump files, Packaging information)
Long policy include the following files 🙁 Incident information, Incident dumps, Alert logs)
To change the retention policy :

–check changes

Manual purging

To purge diagnostic data that is older than the amount of time (minutes) given in the purge command. For ex to purge diagnostic data that is over 1 day old (1440 minutes).

–You can also purge trace files as the same method:

To remove all data older than one minute use:

Problem:

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

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