ORACLE 11g AUTOMATIC DIAGNOSTIC REPOSITORY (ADR)

Oracle Database 11g had adds two more important axes i.e. Change Management and Fault Management. Here I am going to describe you some points about fault management capabilities introduced in Oracle Database 11g. As a DBA our goal is to recognize problems proactively, prevent damage, reduce problem diagnostic time, reduce problem resolution time and simplify customer contact with Oracle Support.

A special repository, named ADR (Automatic Diagnostic Repository) is automatically maintained by Oracle 11g to hold diagnostic information about critical error events. This repository is maintained in memory which enables database components to capture diagnostic data at its first failure for critical errors.

Later on an Incident package is created and sent to Oracle Support Services for investigation with the below mentioned workflow.

1. Event causes an alert to be raised in Enterprise Manager (EM).

2. The DBA will be aware of this alert via the EM Alert page.

3. The DBA can view the incident and problem details.

4. DBA or Oracle Support Services can decide or ask for that information to be packaged and sent to Oracle Support Services via Meta-link. The DBA can add files to the data to be packaged automatically.

All the data related to ASM, CRS, RAC and other oracle product or components will be stored in its own ADR Home.

For example, in Real Application Clusters (RAC) environment with shared storage and ASM, each database instance and each ASM instance have a home directory within ADR.

Structure of ADR Directory is designed in such a way that uses consistent diagnostic data formats across products and instances, and a integrated set of tools enable customers and Oracle Support to correlate and analyze diagnostic data across multiple instances.

Normally ADR’s Location is set by DIAGNOSTIC_DEST initialization parameter in INIT.ORA file but if this parameter is omitted or left null then its selects $ORACLE_HOME/LOG as its base location. Within the ADR base, there can be multiple ADR homes, where each ADR home is the root directory for all diagnostic data for a particular instance of a particular Oracle product.

ADR’s also generates 2 alert files i.e. Textual and XML.

1.    Textual: This file is located under Trace Directory of ADR Home.

2.    XML: This file is using XML Standard and is stored under Alert sub-directory of ADR Home

Both the files can be viewed with EM and ADRCI Utility.

Many sub-directories are maintained under INCIDENT directory depending on the particular incident, and where each contains dumps pertaining only to that incident. And Health Monitor Directory named HM contains the checker run reports with METADATA directory that contains important files for the repository. And we can compare this within Oracle Database Dictionary.

This data dictionary can be queried using ADRCI utility and it also enables you to view the name of the trace files in ADR and XML tags.

In addition, 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.

ADRCI Utility is a command line tool that enables us to view diagnostic data. IPS service gather package incident and problem information into a zip file to transfer to Oracle Support.

No login is required for ADRCI because the information stored in ADR is not intended to be secure. It is suggested by Oracle to always use Support Workbench of OEM to package and manage diagnostic data.

Note: For more information about ADRCI, refer to the Oracle Database Utilities guide.

Below table shows us the new location of Diagnostic trace files.

 

DIAGNOSTIC DATA	PREVIOUS LOCATION 10G	ADR LOCATION 11G
Foreground process traces	USER_DUMP_DEST	$ADR_HOME/TRACE
Background process traces	BACKGROUND_DUMP_DEST	$ADR_HOME/TRACE
Alert log data	BACKGROUND_DUMP_DEST	$ADR_HOME/ALERT&TRACE
Core Dumps	CORE_DUMP_DEST	$ADR_HOME/CDUMP
Incident dumps	USER|BACKGROUND_DUMP_DEST	$ADR_HOME/INCIDENT/INCDIR_N

 

Incident dumps are stored to separate directory named $ADR_HOME/INCIDENT/INCDIR_N (Oracle 11g New Feature).

Note: $ADR_HOME is used to denote the ADR home directory. However, there is no official environment variable called ADR_HOME.

Below steps will guide you to view alert log file with Enterprise manager and ADRCI Utility

Enterprise Manager: 

Access the Database Home page in Enterprise Manager

 Under Related Links, click Alert Log Contents

The View Alert Log Contents page appears

Select the number of entries to view, and then click Go.

 

ADRCI Utility

Go to command prompt and type ADRCI

Now to view all the available ADR Home type SHOW HOMES at ADRCI Prompt.

To set ADR Home type SET HOMEPATH

Now to see the alert type SHOW ALERT

You can also use the option SHOW ALERT –TAIL to view file in reverse order.