Introduction
In operating Oracle Database, quickly detecting and resolving errors or failures is essential for maintaining system stability and reliability. ADR (Automatic Diagnostic Repository) is a powerful tool provided by Oracle to support this goal. This article offers a comprehensive guide—from basics to advanced usage—aimed at helping database administrators and engineers effectively utilize ADR.
1. Fundamentals of ADR
1.1 What Is ADR?
ADR is a framework that centrally manages error and failure information generated by Oracle Database and related components. It offers:
- Automated error collection: Automatically gathers and archives various error information occurring within the system.
- Unified management: Centralizes failure information from multiple Oracle components, such as the database, listener, and ASM.
- Rapid troubleshooting: Provides detailed error information to assist in quick identification and resolution of issues.
1.2 Background of ADR’s Introduction
Traditionally, troubleshooting Oracle Database errors required manually checking various log files, which was time-consuming. ADR was introduced to streamline the collection, management, and analysis of error information.
2. ADR Architecture and Structure
2.1 Directory Structure
ADR has a standardized directory layout, organized as follows:
- ADR Base: The root directory storing ADR data for the entire system.
- ADR Home: Subdirectories for each instance or listener that store component-specific information.
2.2 Key Files and Directories
- alert directory: Stores the alert log.
- trace directory: Contains various trace files.
- incident directory: Holds information related to major errors (incidents).
- hm directory: Saves health monitor reports.
2.3 Relationships Among ADR Components
ADR closely integrates with Oracle components such as the database instance, listener, and ASM instance. Each component has its own ADR home to manage related diagnostic information.
3. Main ADR Features and Practical Use
3.1 Utilizing the Alert Log
The alert log records critical events like database startup/shutdown, errors, and configuration changes. Periodic monitoring helps detect potential issues early.
- Location:
$ADR_BASE/diag/rdbms/$ORACLE_SID/trace/alert_$ORACLE_SID.log - Important entries: ORA-errors, data file additions, instance restarts, etc.
3.2 Analyzing Trace Files
Trace files contain detailed error information and execution details of SQL statements, useful for diagnosing problems linked to specific sessions or processes.
- Enabling SQL trace:
ALTER SESSION SET SQL_TRACE = TRUE; - Event tracing: Specify events to gather detailed diagnostic data.
3.3 Incident Management
Incidents represent significant errors (e.g., ORA-600, ORA-7445). ADR automatically collects related diagnostic data per incident.
- Creating an incident package: For submission to Oracle Support.
- Managing via ADRCI: View incident lists and their details.
3.4 Health Monitor
The health monitor checks database consistency and availability.
- Automatic diagnostics: Triggered upon error occurrence.
- Manual diagnostics: Executable using the
DBMS_HMpackage.
4. Detailed Usage of ADRCI
4.1 Launching ADRCI and Basic Setup
- Start ADRCI: Enter
adrciin the command line. - List ADR homes:
show homes - Set ADR home:
set homepath diag/rdbms/<DB_NAME>/<SID>
4.2 Common Commands and Options
- Show incidents:
show incident [-mode {basic | detail}] [-p "filter conditions"] - Show problems:
show problem - Show trace files:
show tracefile - Show alert log:
show alert [-tail] [-f]
Command execution example
[oracle@orcl19c ~]$ adrci
ADRCI: Release 19.0.0.0.0 - Production on Thu Aug 7 23:01:41 2025
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
No ADR base is set
adrci> set base /u01/app/oracle
adrci> show home
ADR Homes:
diag/rdbms/orcl/orcl
adrci> set home diag/rdbms/orcl/orcl
adrci> show incident
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
9953 ORA 600 [KGL-heap-size-exceeded] 2025-08-07 23:00:08.512000 -04:00
9954 ORA 600 [KGL-heap-size-exceeded] 2025-08-07 23:00:18.989000 -04:00
9955 ORA 600 [KGL-heap-size-exceeded] 2025-08-07 23:00:28.363000 -04:00
9956 ORA 600 [KGL-heap-size-exceeded] 2025-08-07 23:00:37.136000 -04:00
12266 ORA 600 [KGL-heap-size-exceeded] 2025-08-07 23:00:50.839000 -04:00
12267 ORA 600 [KGL-heap-size-exceeded] 2025-08-07 23:01:05.977000 -04:00
6 rows fetched
adrci> show tracefile
diag/rdbms/orcl/orcl/trace/orcl_vktm_3395.trc
diag/rdbms/orcl/orcl/trace/orcl_vktm_2211.trc
diag/rdbms/orcl/orcl/trace/orcl_vktm_16674.trc
diag/rdbms/orcl/orcl/trace/orcl_vktm_14648.trc
diag/rdbms/orcl/orcl/trace/orcl_vktm_14548.trc
diag/rdbms/orcl/orcl/trace/orcl_vktm_14343.trc
diag/rdbms/orcl/orcl/trace/orcl_vkrm_3415.trc
diag/rdbms/orcl/orcl/trace/orcl_vkrm_2239.trc
:
adrci> show problem
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1 ORA 600 [KGL-heap-size-exceeded] 12267 2025-08-07 23:01:05.977000 -04:00
1 row fetched
adrci>
4.3 Creating Incident Packages
- Create package:
IPS CREATE PACKAGE [problem_id] - Add file to package:
IPS ADD FILE package_id path_to_file - Pack and verify:
IPS PACK package_id, thenIPS SHOW PACKAGE CONTENT package_id
5. ADR Maintenance and Best Practices
5.1 Configuring Data Retention Periods
By default, ADR retains data for 30 days, but this can be adjusted based on system requirements.
- Check retention settings:
show control - Set retention policy:
set control (SHORTP_POLICY = 90, LONGP_POLICY = 365)
5.2 Managing Disk Space
Long-term retention may consume significant disk space, so regular purging is necessary.
- Manual purge:
purge -age 90 -type ALERT - Enable auto-purge:
set control (AUTO_PURGE = ON)
5.3 Security and Access Control
ADR may contain sensitive diagnostic data. Proper file permissions and access controls are crucial.
6. Practical Troubleshooting Examples
6.1 Handling ORA-600 Errors
- Assess the situation: Use ADRCI to view incidents and error details.
- Create incident package: For Oracle Support.
- Run health monitor: Check database consistency.
6.2 Solving Listener Issues
- Set listener ADR home:
set homepath diag/tnslsnr/hostname/listener - Check error log:
show alert -tail - Enable listener tracing for deeper diagnostics.
7. Advanced ADR Utilization
7.1 Custom Health Checks
- Use
DBMS_HM: Create custom health check tasks and automate execution. - Schedule execution: Use Oracle Scheduler.
7.2 Integration with Enterprise Manager
- Unified monitoring: Integrate ADR with Oracle Enterprise Manager (OEM) for GUI-based management.
- Alert notifications: Send email or SNMP alerts on specific errors.
7.3 Automation via Scripting
- Automate ADRCI tasks: Batch scripts for routine maintenance.
- Log analysis and reporting: Parse logs and trace files to create custom reports.
8. Best Practices and Cautions
8.1 Regular Monitoring and Maintenance
- Set up schedules: Establish checklists and routines.
- Log rotation: Prevent log files from growing excessively.
8.2 Training and Knowledge Sharing
- Educate your team: Share ADR usage and troubleshooting methods.
- Documentation: Record issues and resolutions for future reference.
8.3 Compliance with Security Standards
- Protect sensitive data: Manage ADR files containing confidential information, and track access logs.
9. Summary and Future Outlook
ADR is a robust tool that greatly enhances Oracle Database failure management. By understanding its usage—from basics to advanced applications—and incorporating it into daily operations, you can improve your system’s reliability and stability.
Key takeaways:
- ADR automates error collection and management to support efficient troubleshooting.
- ADRCI enables effective management of incidents and trace files.
- Maintenance tasks such as data retention and disk management are vital.
- Practical examples help contextualize ADR usage.
- Advanced features offer automation and monitoring enhancements.
As Oracle continues evolving, ADR functionality is likely to expand. Stay updated to continually improve your operations.
[reference]
20 ADRCI: ADR Command Interpreter
コメント