Oracle ADRCI (ADR Command Interpreter) - Complete Diagnostic Guide

Oracle ADRCI (ADR Command Interpreter) - Complete Diagnostic Guide

Oracle Automatic Diagnostic Repository Command Interpreter (ADRCI) is a command-line tool for managing diagnostic data in the Automatic Diagnostic Repository (ADR). Available from Oracle 11g onwards, ADRCI provides powerful capabilities for incident investigation, diagnostic data management, and health monitoring.

🎯 Overview

What is ADRCI?

ADRCI provides:

• Centralized diagnostic data management across Oracle products
• Incident investigation tools for problem analysis
• Health monitoring reports and checks
• Diagnostic data packaging for Oracle Support
• Alert log management and analysis
• Automated diagnostic collection and organization

ADR Architecture

ADR Structure

# ADR Base directory structure
$ORACLE_BASE/diag/
├── rdbms/
│   └── <db_name>/
│       └── <instance_name>/
│           ├── alert/         # Alert logs
│           ├── incident/      # Incident data
│           ├── trace/         # Trace files
│           ├── cdump/         # Core dumps
│           ├── hm/            # Health Monitor
│           └── metadata/      # ADR metadata
├── tnslsnr/
│   └── <host>/
│       └── <listener>/
└── clients/

🔧 Basic ADRCI Operations

Starting and Basic Commands

ADRCI Session Management

# Start ADRCI
adrci

# Show help
ADRCI> help

# Set editor for viewing files
ADRCI> set editor vi

# Show current working directory
ADRCI> pwd

# Show ADR homes
ADRCI> show homes

Basic Information Commands

# Show database information
ADRCI> show homes
ADRCI> set home diag/rdbms/mydb/mydb1

# Show database parameters
ADRCI> show parameter

# Show alert log location
ADRCI> show alert

# Show control information
ADRCI> show control

Alert Log Management

Viewing Alert Logs

# Show recent alert log entries
ADRCI> show alert

# Show alert log with timestamps
ADRCI> show alert -t

# Show last N lines
ADRCI> show alert -tail 50

# Show alerts for specific time period
ADRCI> show alert -start "2024-01-15 08:00:00" -end "2024-01-15 18:00:00"

# Show alerts containing specific text
ADRCI> show alert | grep -i "ORA-"

# Show alert in XML format
ADRCI> show alert -xml

Alert Log Analysis

# Search for specific errors
ADRCI> show alert -message_text "ORA-00600"

# Show critical alerts
ADRCI> show alert -level "critical"

# Export alert log to file
ADRCI> spool /tmp/alert_export.log
ADRCI> show alert -start "2024-01-15 00:00:00"
ADRCI> spool off

# Show alert log statistics
ADRCI> show alert -stats

🚨 Incident Management

Incident Investigation

Viewing Incidents

# Show all incidents
ADRCI> show incident

# Show incident details
ADRCI> show incident -id 12345

# Show incidents by status
ADRCI> show incident -status open

# Show incidents for specific problem
ADRCI> show incident -problem_id 1

# Show recent incidents
ADRCI> show incident -last 10

Incident Analysis

# Describe incident structure
ADRCI> describe incident

# Show incident files
ADRCI> show incident -id 12345 -file

# View specific incident file
ADRCI> show incident -id 12345 -file alert_mydb1_12345.trc

# Show incident progression
ADRCI> show incident -id 12345 -timeline

# Show related incidents
ADRCI> show incident -correlate -id 12345

Problem Management

Problem Investigation

# Show all problems
ADRCI> show problem

# Show problem details
ADRCI> show problem -id 1

# Show problem impact
ADRCI> show problem -id 1 -impact

# Close resolved problem
ADRCI> set problem -id 1 -status closed

# Show problem timeline
ADRCI> show problem -id 1 -detail

Advanced Problem Analysis

# Show problem correlation
ADRCI> show problem -correlate

# Show problem key
ADRCI> show problem -key "ORA 600"

# Export problem information
ADRCI> show problem -id 1 > /tmp/problem_1.txt

# Show problem statistics
ADRCI> show problem -stats -last 30

📊 Health Monitoring

Health Monitor Reports

Running Health Checks

# Show available health checks
ADRCI> show hm_check

# Run specific health check
ADRCI> run hm_check db_structure_integrity

# Show health check results
ADRCI> show hm_run

# Show specific run details
ADRCI> show hm_run -id 123

# Show health check recommendations
ADRCI> show hm_run -id 123 -recommendation

Custom Health Monitoring

# Create custom health check
ADRCI> create hm_check -name custom_space_check -check_name tablespace_usage

# Schedule health check
ADRCI> run hm_check custom_space_check -interval 3600

# Show scheduled checks
ADRCI> show hm_check -scheduled

# Cancel scheduled check
ADRCI> cancel hm_check -name custom_space_check

System Health Analysis

# Database health summary
ADRCI> show hm_info

# Show health metrics
ADRCI> show hm_metric

# Show health findings
ADRCI> show hm_finding

# Export health report
ADRCI> show hm_run -id 123 -format html > /tmp/health_report.html

# Show health trends
ADRCI> show hm_metric -trend -last 7

📦 Diagnostic Data Packaging

Creating Support Packages

IPS (Incident Packaging Service)

# Create logical package
ADRCI> ips create package incident 12345

# Add related incidents to package
ADRCI> ips add incident 12346 package 1

# Add time-based data
ADRCI> ips add package 1 time -start "2024-01-15 08:00:00" -end "2024-01-15 10:00:00"

# Show package contents
ADRCI> ips show package 1

# Generate package
ADRCI> ips generate package 1 in /tmp/oracle_support

# Show package status
ADRCI> ips show package 1 -detail

Advanced Packaging

# Create package with specific correlation
ADRCI> ips create package problem 1

# Add external files
ADRCI> ips add file /u01/oracle/trace/custom.trc package 1

# Add database information
ADRCI> ips add database package 1

# Finalize package
ADRCI> ips finalize package 1

# Remove sensitive data
ADRCI> ips sanitize package 1

Package Management

# List all packages
ADRCI> ips show packages

# Show package information
ADRCI> ips show package 1 -info

# Delete package
ADRCI> ips delete package 1

# Export package metadata
ADRCI> ips show package 1 -metadata > package_info.xml

# Validate package
ADRCI> ips validate package 1

🔍 Trace File Management

Trace File Operations

Viewing Trace Files

# Show trace files
ADRCI> show tracefile

# Show trace files for specific time
ADRCI> show tracefile -rt -start "2024-01-15 08:00:00"

# Show large trace files
ADRCI> show tracefile -size 100000

# View specific trace file
ADRCI> show tracefile alert_mydb1_12345.trc

# Search in trace files
ADRCI> show tracefile -grep "ORA-00600"

Trace File Analysis

# Show trace file details
ADRCI> describe tracefile alert_mydb1_12345.trc

# Extract trace file sections
ADRCI> show tracefile alert_mydb1_12345.trc -section 2

# Show trace file statistics
ADRCI> show tracefile -stats

# Purge old trace files
ADRCI> purge -age 7 -type trace

# Archive trace files
ADRCI> archive tracefile -older_than 30

Core Dump Analysis

# Show core dumps
ADRCI> show core

# Analyze core dump
ADRCI> show core -id 12345 -analysis

# Extract core dump information
ADRCI> show core -id 12345 -stack

# Show core dump summary
ADRCI> show core -summary

# Package core dump for analysis
ADRCI> ips create package core 12345

📈 ADR Maintenance

Space Management

ADR Purging Policies

# Show purge policy
ADRCI> show control

# Set purge policy
ADRCI> set control (SHORTP_POLICY = 168)  # 7 days
ADRCI> set control (LONGP_POLICY = 8760)  # 1 year

# Manual purging
ADRCI> purge -age 1440 -type incident      # 1 day
ADRCI> purge -age 4320 -type trace         # 3 days
ADRCI> purge -age 10080 -type alert        # 1 week

# Show space usage
ADRCI> show homes -usage

# Purge by size
ADRCI> purge -size 1000000 -type trace     # 1MB

Automated Maintenance

# Enable automatic purging
ADRCI> set control (PURGE_ENABLE = TRUE)

# Set maximum ADR size
ADRCI> set control (MAX_ADR_SIZE = 2048)   # 2GB

# Show purge history
ADRCI> show purge

# Force immediate purge
ADRCI> purge -force

# Show disk usage by type
ADRCI> show homes -diskusage

Configuration Management

# Show all control parameters
ADRCI> show control -all

# Backup ADR configuration
ADRCI> show control > /tmp/adr_config_backup.txt

# Export ADR structure
ADRCI> show homes -detail > /tmp/adr_structure.txt

# Reset to defaults
ADRCI> set control (DEFAULT_POLICY = TRUE)

# Show configuration changes
ADRCI> show control -history

🔧 Advanced ADRCI Features

Cross-Component Analysis

Multi-Component Investigation

# Set multiple homes
ADRCI> set homes diag/rdbms/mydb/mydb1 diag/tnslsnr/server1/listener

# Show incidents across components
ADRCI> show incident -all_homes

# Correlate incidents across components
ADRCI> show incident -correlate -all_homes

# Generate comprehensive package
ADRCI> ips create package comprehensive -all_homes

Timeline Analysis

# Show system timeline
ADRCI> show timeline -start "2024-01-15 08:00:00" -end "2024-01-15 10:00:00"

# Show incident timeline
ADRCI> show incident -timeline -start "2024-01-15 08:00:00"

# Show cross-component timeline
ADRCI> show timeline -all_homes -start "2024-01-15 08:00:00"

# Export timeline data
ADRCI> show timeline -xml > /tmp/timeline.xml

Automated Diagnostics

Diagnostic Collection

# Collect comprehensive diagnostics
ADRCI> collect diagnostics -problem_id 1 -time 1440

# Collect custom diagnostic set
ADRCI> collect diagnostics -name "performance_issue" -components db,listener

# Schedule diagnostic collection
ADRCI> schedule collect -name daily_health -interval 86400

# Show collection status
ADRCI> show collect -status

# Export collected data
ADRCI> export collect -name performance_issue -format tgz

🛡️ Security and Best Practices

Access Control

ADRCI Security

# Check ADR permissions
ls -la $ORACLE_BASE/diag/

# Secure ADR directory
chmod 750 $ORACLE_BASE/diag/

# Audit ADRCI usage
ADRCI> set logging on
ADRCI> show logging

# Show access history
ADRCI> show access -history

# Configure access restrictions
ADRCI> set access -restrict -users dba_group

Data Protection

# Sanitize sensitive data
ADRCI> sanitize package 1 -remove_sql -remove_binds

# Mask sensitive information
ADRCI> set control (SANITIZE_AUTO = TRUE)

# Show data classification
ADRCI> show classification

# Export sanitized data
ADRCI> export package 1 -sanitized

Best Practices

Regular Maintenance

-- Create ADRCI maintenance script
#!/bin/bash

# Set environment
export ORACLE_HOME=/u01/oracle/product/19c
export PATH=$ORACLE_HOME/bin:$PATH

# ADRCI maintenance commands
adrci exec="
set home diag/rdbms/mydb/mydb1;
purge -age 1440 -type incident;
purge -age 4320 -type trace;
show homes -usage;
exit"

# Log results
echo "ADR maintenance completed at $(date)" >> /var/log/adr_maintenance.log

Monitoring Integration

# Health check automation
ADRCI> run hm_check db_structure_integrity -auto
ADRCI> run hm_check undo_integrity -auto
ADRCI> run hm_check txn_integrity -auto

# Alert integration
ADRCI> set alert_notification on
ADRCI> set alert_email [email protected]

# Performance monitoring
ADRCI> show hm_metric -trend -component database

🚨 Troubleshooting Common Issues

ADRCI Problems

Access Issues

# ADRCI permission errors
chown -R oracle:dba $ORACLE_BASE/diag/
chmod -R 750 $ORACLE_BASE/diag/

# ADR corruption issues
ADRCI> validate repository
ADRCI> repair repository

# Missing ADR base
mkdir -p $ORACLE_BASE/diag
ADRCI> create home diag/rdbms/mydb/mydb1

Performance Issues

# Large ADR size
ADRCI> show homes -diskusage
ADRCI> purge -age 720 -type all    # Purge 30 days
ADRCI> set control (MAX_ADR_SIZE = 1024)

# Slow ADRCI operations
ADRCI> rebuild index
ADRCI> optimize repository

# ADR fragmentation
ADRCI> compress repository

Error Resolution

Common Error Codes

# ADR-00101: ADR base not set
export ORACLE_BASE=/u01/oracle
adrci

# ADR-00102: ADR home does not exist
ADRCI> create home diag/rdbms/mydb/mydb1

# ADR-00110: Repository corruption
ADRCI> validate repository
ADRCI> repair repository -force

# ADR-00115: Insufficient space
ADRCI> purge -force
ADRCI> set control (MAX_ADR_SIZE = 2048)

📊 Reporting and Analysis

Custom Reports

Diagnostic Reports

# Generate comprehensive health report
ADRCI> set home diag/rdbms/mydb/mydb1
ADRCI> show hm_run -last 10 -format html > /tmp/health_summary.html

# Create incident summary report
ADRCI> show incident -last 30 -detail > /tmp/incident_summary.txt

# Generate alert analysis
ADRCI> show alert -stats -last 7 > /tmp/alert_analysis.txt

# Export problem trends
ADRCI> show problem -trend -last 30 -format xml > /tmp/problem_trends.xml

Performance Analysis

# Database performance report
ADRCI> show hm_metric -component database -last 24

# System resource analysis
ADRCI> show hm_metric -component system -trend

# Application performance
ADRCI> show hm_metric -component application -summary

# Export performance data
ADRCI> export metrics -component all -format csv > /tmp/performance_data.csv

Oracle ADRCI provides comprehensive diagnostic capabilities that are essential for proactive database management and effective problem resolution. Regular use of ADRCI helps maintain database health and enables rapid incident response.

📖 Related Topics

• Oracle Alert Log Analysis - Alert log monitoring scripts
• AWR Performance Monitoring - Performance data analysis
• RMAN Backup and Recovery - Database backup strategies
• Oracle Architecture - Understanding Oracle components