Understanding and Managing the Oracle Automatic Diagnostic Repository (ADR)

Efficient error analysis is essential for maintaining Oracle Database operations. This article explains the fundamental concepts of the “Oracle Automatic Diagnostic Repository (ADR)”—a centralized repository for diagnostic information—and outlines efficient troubleshooting procedures using ADR.

What is the ADR (Automatic Diagnostic Repository)?
Key Information Managed by ADR
Verifying ADR and Path Configuration (DIAGNOSTIC_DEST)
1. Parameter Verification
ADR Directory Structure
Retrieving Diagnostic Information via SQL (V$DIAG_INFO)
1. Example: Querying V$DIAG_INFO
Log Management with the adrci Command
1. Common Operational Procedures
Basic Troubleshooting Workflow
Frequently Asked Questions (FAQ)
Summary

What is the ADR (Automatic Diagnostic Repository)?

The Automatic Diagnostic Repository (ADR) is a file-based directory structure that centrally manages information related to database faults and errors. It hierarchically organizes alert logs, trace files, dump files, and more, allowing DBAs to quickly assess the database’s status by referencing these files.

Key Information Managed by ADR

Alert log (alert.log): Records critical database events and errors.
Trace files: Detailed logs of session and background process activities.
Dump files: Memory information captured during crashes, etc.
Health Monitor reports: Results of periodic health checks.

Verifying ADR and Path Configuration (DIAGNOSTIC_DEST)

The base location of the ADR is defined by the DIAGNOSTIC_DEST initialization parameter. By default, this is set to $ORACLE_BASE/diag.

Parameter Verification

Execute the following SQL to verify the current setting:

-- Check the current diagnostic information storage location
SHOW PARAMETER DIAGNOSTIC_DEST;

ADR Directory Structure

The directories are categorized by component as follows:

Path	Content
rdbms/	Database diagnostic information
tnslsnr/	Listener diagnostic information
asm/	ASM instance diagnostic information

Retrieving Diagnostic Information via SQL (V$DIAG_INFO)

Detailed ADR path information can be retrieved via SQL using the dynamic performance view V$DIAG_INFO.

Example: Querying V$DIAG_INFO

-- Display a list of ADR-related paths
SELECT NAME, VALUE FROM V$DIAG_INFO;

-- Retrieve only the absolute path of the alert log
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Alert';

Log Management with the adrci Command

For ADR operations, use the dedicated command-line tool adrci (Automatic Diagnostic Repository Command Interpreter).

Common Operational Procedures

Verifying Diagnostic Homes

adrci> show homes

Viewing the Alert Log

# View all
adrci> show alert

# Extract ORA errors
adrci> show alert -p "message_text like '%ORA-%'"

Deleting Old Diagnostic Information (Reclaiming Space)

Periodic deletion is necessary because unnecessary files can accumulate and consume disk space.

# Delete old data older than 1440 minutes (24 hours)
adrci> purge -age 1440

Basic Troubleshooting Workflow

When a fault occurs, it is standard practice to proceed with the investigation in the following order:

Locate Information: Verify various log paths using SELECT * FROM V$DIAG_INFO;.
Review Logs: Check the alert log using adrci to identify the time and details of the fault.
Check Incident Details: Execute related show incident or show problem commands.
Create Package: If necessary, create a package for submission to Oracle Support (e.g., ips pack).

Frequently Asked Questions (FAQ)

Q1. Is it safe to delete ADR data?

A. Yes. Using the adrci purge command allows you to delete old information while maintaining data integrity. Manually executing the rm command is discouraged.

Q2. What is the impact of changing DIAGNOSTIC_DEST?

A. If changed, new logs will be generated in the new location. When changing this during operation, ensure that OS-level permissions and directory creation are completed in advance.

Q3. The log files are very large. What should I do?

A. Use adrci to purge old information. If the files remain large, you should review whether the trace file output level (e.g., SQL_TRACE) is set too high.

Summary

ADR is the centralized diagnostic information management foundation and an essential tool for DBAs.
Use V$DIAG_INFO to verify paths and adrci for log searching and management.
Periodic execution of purge is crucial to prevent storage bloat.

This article targets Oracle Database 19c (other versions may have different screens or default values).

[reference]
ADRCI: ADR Command Interpreter