Automatic Diagnostic Repository in Oracle

Back to: Oracle DBA Tutorials

Automatic Diagnostic Repository (ADR) in Oracle with Examples

In this article, I am going to discuss the Automatic Diagnostic Repository in Oracle with Examples. Please read our previous article where we discussed Container Clause in Oracle with Examples.

Automatic Diagnostic Repository in Oracle

This is a file-based repository outside the database. It is a collection of files and folders. The Automatic Diagnostic Repository is a system-wide central tracing and logging repository. It stores database diagnostic data such as

• Traces
• Alert Logs
• Health Monitor Reports

So, this information is required for the database administrator to trace files, investigate errors, and find out some internal problems. So, the purpose of ADR is to provide trace files, alert logs, and health monitor reports.

The typical installation will have ADR_BASE set to ORACLE_BASE. If you open the Linux machine and enter the command “echo $ORACLE_BASE” you will get the location as “/u01/app/oracle”. To make sure that this is the correct location there is a parameter called “diagnostic_dest”. You can also look at the complete ADR HOME path as <ADR BASE>/diag/product_type/db_id/instance_id.

To prove this let’s log in to the virtual machine and open the terminal. Once the virtual machine and terminal are opened use the command “echo $ORACLE_BASE”.

The path “/u01/app/oracle” is the oracle base and this path is the same as the ADR_BASE path. To make sure ADR_PATH is the same as ORACLE_BASE connect to the database as “Sqlplus / as sysdba”.

Once connected to the database, use the command “show parameter dia”. This command will provide the output with parameters having dia in common.

We can see the path diagnostic_dest as /u01/app/oracle which is the same as the oracle_base path. Now, let’s go ahead and check the same location in the files. Open files and select other locations and go to the below path.

From the above files, we can see this path is oracle_base and ADR_PATH. But we discussed earlier that the ADR home path is /u01/app/oracle/diag/rdbms/jayadb/jayadb/. So, we can see the path below.

In the path below are files and folders present.

There is an alert folder which is very important and it has an incident folder and hm a health monitor folder and a trace folder. These folders contain files that are most important to database errors and tracing. We will discuss more on this in further articles.

In the next article, I am going to discuss Alert log Files in Oracle with Examples. Here, in this article, I try to explain the Automatic Diagnostic Repository in Oracle with Examples and I hope you enjoy this Automatic Diagnostic Repository in Oracle with Examples article.