The Oracle Automatic Diagnostic Repository (ADR) is a built-in diagnostic infrastructure in Oracle Database. It is a hierarchical file-based repository that automatically collects, manages, and stores diagnostic data, including logs, traces, and dumps.
The ADR is organized into various directories, each serving a specific purpose:
alert: Stores the alert log for the database, including:- Any non-default initialization parameter used at startup.
- All internal errors, block corruption errors and deadlock errrors.
- DDL statements (CREATE, DROP, ALTER, etc)
- STARTUPS, SHUTDOWNS statements.
- ARCHIVELOG statements.
trace: Stores trace files for :- Background and server processes.
- Guidance for tuning the instance.
- Exceptions and internal errors details.
hm: Stores health monitor reports.cdump: Stores core dump files.incident: Stores detailed information about serious problems that have affected the database.
The locations of these directories can be displayed using the V$DIAG_INFO view and the diagnostic_dest initialization parameter.
SQL> show parameter diag NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ diagnostic_dest string /opt/oracle diagnostics_control string IGNORE SQL>
The diagnostic files are located in <diagnostic_dest>/diag/<product>/<db>/<instance>/. When a critical errors occurs an incident number is created and associated to the error, tagging the trace files with that incident number.
SQL> column name format a30 SQL> column value format a50 SQL> select name, value from v$diag_info; NAME VALUE ------------------------------ -------------------------------------------------- Diag Enabled TRUE ADR Base /opt/oracle ADR Home /opt/oracle/diag/rdbms/xe/XE Diag Trace /opt/oracle/diag/rdbms/xe/XE/trace Diag Alert /opt/oracle/diag/rdbms/xe/XE/alert Diag Incident /opt/oracle/diag/rdbms/xe/XE/incident Diag Cdump /opt/oracle/diag/rdbms/xe/XE/cdump Health Monitor /opt/oracle/diag/rdbms/xe/XE/hm Default Trace File /opt/oracle/diag/rdbms/xe/XE/trace/XE_ora_839.trc Active Problem Count 2 Active Incident Count 6 ORACLE_HOME /opt/oracle/product/21c/dbhomeXE Attention Log /opt/oracle/diag/rdbms/xe/XE/trace/attention_XE.log 13 rows selected.
As shown above, there are 2 active problems and 6 incidents found in our database. We’ll come back to them later on.
The ADR works in conjunction with tools like the Automatic Database Diagnostic Monitor (ADDM), which analyzes the data in the Automatic Workload Repository (AWR) and provides options to resolve performance problems.
Overall, the ADR is a crucial component of Oracle’s diagnostic framework, helping database administrators manage diagnostic data effectively and efficiently.
Using ADRCI
The Automatic Diagnostic Repository Command Interpreter (ADRCI) is a command-line tool that is part of the fault diagnosability infrastructure introduced in Oracle Database 11g. It is used to manage Oracle Database diagnostic data within the Automatic Diagnostic Repository (ADR). ADRCI allows you to view this diagnostic data, view Health Monitor reports, and package incident and problem information into a zip file for transmission to Oracle Support. It can be used in interactive mode or within scripts. It’s important to note that there is no need to log in to ADRCI as ADR data is secured by operating system permissions on the ADR directories.
In ADRCI, the term “homes” refers to one or more base directories known as ADR homes. These ADR homes contain the diagnostic data for a particular instance of an Oracle product. Before using ADRCI commands, it’s often helpful to set an active ADR home to work with a single instance. You can view the available homes by typing SHOW HOMES in the ADRCI prompt. In the following example, we switch to our database home and show problems, incidents and, finally, we get more details about a specific incident.
adrci> show homes ADR Homes: diag/tnslsnr/9f4de3e6e34e/listener diag/tnslsnr/0349127cbdc8/listener diag/rdbms/xe/XE adrci> set home diag/rdbms/xe/XE adrci> show problem ADR Home = /opt/oracle/diag/rdbms/xe/XE: ************************************************************************* PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME -------------------- ----------------------------------------------------------- -------------------- ---------------------------------------- 2 ORA 600 [OSDEP_INTERNAL] 28194 2024-04-10 10:25:50.164000 +00:00 1 ORA 800 28371 2024-04-10 10:25:52.968000 +00:00 2 rows fetched adrci> show incident ADR Home = /opt/oracle/diag/rdbms/xe/XE: ************************************************************************* INCIDENT_ID PROBLEM_KEY CREATE_TIME -------------------- ----------------------------------------------------------- ---------------------------------------- 25631 ORA 600 [OSDEP_INTERNAL] 2024-02-25 18:48:41.108000 +00:00 25672 ORA 800 2024-02-25 18:48:42.808000 +00:00 25808 ORA 800 2024-02-25 18:48:44.370000 +00:00 28194 ORA 600 [OSDEP_INTERNAL] 2024-04-10 10:25:50.164000 +00:00 28235 ORA 800 2024-04-10 10:25:52.041000 +00:00 28371 ORA 800 2024-04-10 10:25:52.968000 +00:00 6 rows fetched adrci> show incident -mode detail -p "incident_id=28194" ADR Home = /opt/oracle/diag/rdbms/xe/XE: ************************************************************************* ********************************************************** INCIDENT INFO RECORD 1 ********************************************************** INCIDENT_ID 28194 STATUS ready CREATE_TIME 2024-04-10 10:25:50.164000 +00:00 PROBLEM_ID 2 CLOSE_TIME <NULL> FLOOD_CONTROLLED none ERROR_FACILITY ORA ERROR_NUMBER 600 ERROR_ARG1 OSDEP_INTERNAL ERROR_ARG2 <NULL> ERROR_ARG3 <NULL> ERROR_ARG4 <NULL> ERROR_ARG5 <NULL> ERROR_ARG6 <NULL> ERROR_ARG7 <NULL> ERROR_ARG8 <NULL> ERROR_ARG9 <NULL> ERROR_ARG10 <NULL> ERROR_ARG11 <NULL> ERROR_ARG12 <NULL> SIGNALLING_COMPONENT ksu SIGNALLING_SUBCOMPONENT <NULL> SUSPECT_COMPONENT <NULL> SUSPECT_SUBCOMPONENT <NULL> ECID <NULL> IMPACTS 0 CON_UID 1 PROBLEM_KEY ORA 600 [OSDEP_INTERNAL] FIRST_INCIDENT 1 FIRSTINC_TIME 2023-08-02 18:42:27.417000 +00:00 LAST_INCIDENT 28194 LASTINC_TIME 2024-04-10 10:25:50.164000 +00:00 IMPACT1 0 IMPACT2 0 IMPACT3 0 IMPACT4 0 KEY_NAME Client ProcId KEY_VALUE oracleXE@9f4de3e6e34e (TNS V1-V3).26_140221474075392 OWNER_ID 1 INCIDENT_FILE /opt/oracle/diag/rdbms/xe/XE/trace/XE_ora_26.trc OWNER_ID 1 INCIDENT_FILE /opt/oracle/diag/rdbms/xe/XE/incident/incdir_28194/XE_ora_26_i28194.trc 1 row fetched adrci>
Using ADRCI to show the alert log.
The Alert Log in Oracle Database is a chronological log that captures a variety of messages and errors, including database startup, shutdown, log switches, and space errors. It’s often the first place to check when something goes wrong with the database and the cause is not immediately obvious. As said before, the alert log is named alert_SID.log and is located in the Automatic Diagnostic Repository (ADR) directory specified by the DIAGNOSTIC_DEST parameter in the initialization parameter file. This file should be constantly monitored to detect unexpected messages and corruptions. Let’s see how to show the alert log from within the ADRCI interface.
sh-4.2$ adrci ADRCI: Release 21.0.0.0.0 - Production on Wed Apr 10 11:05:13 2024 Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved. ADR base = "/opt/oracle" adrci> show alert Choose the home from which to view the alert log: 1: diag/tnslsnr/9f4de3e6e34e/listener 2: diag/tnslsnr/0349127cbdc8/listener 3: diag/rdbms/xe/XE Q: to quit Please select option: 3 Output the results to file: /tmp/alert_1801_14044_XE_1.ado Please select option: q adrci>
After the output is dumped, the temporary file is opened using the default editor. To setup the default editor use:
adrci> set editor nano -- or adrci> set editor vi
Manually purging the ADR repository.
The different options of the purge command allow us to manually clean the repository.
adrci> show incident ADR Home = /opt/oracle/diag/rdbms/xe/XE: ************************************************************************* INCIDENT_ID PROBLEM_KEY CREATE_TIME -------------------- ----------------------------------------------------------- ---------------------------------------- 28194 ORA 600 [OSDEP_INTERNAL] 2024-04-10 10:25:50.164000 +00:00 28235 ORA 800 2024-04-10 10:25:52.041000 +00:00 28371 ORA 800 2024-04-10 10:25:52.968000 +00:00 3 rows fetched -- Purge incident n. 2835 adrci> purge -i 28235 -- A range can also be specified, ie: Purge -i 100 200 would delete incidents between 100 and 200. adrci> show incident ADR Home = /opt/oracle/diag/rdbms/xe/XE: ************************************************************************* INCIDENT_ID PROBLEM_KEY CREATE_TIME -------------------- ----------------------------------------------------------- ---------------------------------------- 28194 ORA 600 [OSDEP_INTERNAL] 2024-04-10 10:25:50.164000 +00:00 28371 ORA 800 2024-04-10 10:25:52.968000 +00:00 2 rows fetched -- Purge until the size of the home reaches 50 MB. adrci> purge -size 50000000 -- Purge alerts older than 10080 minutes. (7 days) adrci> purge -age 10080 -type alert -- For more options use: adrci> purge help
Using ADRCI to modify the ADR retention policy.
The diagnostic repository can grow quickly so it’s important to check the retention policy to configure the automatic purge. There are three main parameters to configure:
- The long retention period, used for incidents and alert logs. The default value is 365 days.
- The short retention period, used for traces and core dumps. The default value is 30 days.
- The size-based retention, used to control the size of a Log due to limited disk space.
Since the long retencion period is normally longer, incidents, alert logs, traces and core dumps are purged in the same proportion when there is a lack of space in the repository.,
adrci> set home diag/rdbms/xe/XE adrci> show control ADR Home = /opt/oracle/diag/rdbms/xe/XE: ************************************************************************* ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME -------------------- -------------------- -------------------- ---------------------------------------- 3216998116 720 8760 2023-08-02 18:42:24.278567 +00:00 1 row fetched adrci> select shortp_policy, longp_policy from adr_control; ADR Home = /opt/oracle/diag/rdbms/xe/XE: ************************************************************************* SHORTP_POLICY LONGP_POLICY -------------------- -------------------- 720 8760 1 row fetched adrci> select sizep_policy from adr_control_aux; ADR Home = /opt/oracle/diag/rdbms/xe/XE: ************************************************************************* SIZEP_POLICY -------------------- 18446744073709551615 1 row fetched -- change shortp and longp policy to 192 hours. adrci> set control (shortp_policy = 192, longp_policy = 192) -- change sizep policy to 1 GB. adrci> set control (sizep_policy=1000000) -- We can use the estimate command to work out the different values adrci> estimate (shortp_policy = 192, longp_policy = 192) Estimate Short Policy Hours: 192 Long Policy Hours: 192 Size Policy Bytes: 8388608 adrci> estimate (sizep_policy = 1000000) Short Policy Hours: 192 Long Policy Hours: 192 Size Policy Bytes: 1000000
To wrap up, the ADR is an essential tool for database administrators, making database management as efficient and straightforward as possible. It is vital to master it beforehand to know how to make a quick diagnosis in the event of any unforeseen event in our database.
SIZEP_POLICY was introduced in Oracle Database 12c Release 2 (12.2).
In earlier version use alter system set max_dump_file_size=nnnnnnnn scope=both