5 Things That Every Good DBA Needs To Know About The ADR

If you are an Oracle DBA, most likely you know what Automatic Diagnostic Repository aka ADR is, or at least you heard of it.
If you are OCP certified, then you must have studied the ADR for your certification.

Let me ask you a question.
Do you remember much about it, beside the fact that somehow ADR replaces the *dump* locations such as bdump, cdump?
If you don’t remember, don’t worry, you are not the only one.

That is why I wrote this article today, completed with a mind map, for the DBA that knew, but forgot the basics of ADR, and for the DBA that never heard of ADR, and for the DBA that knows everything!

1. What Is ADR?
2. Where Is ADR Located?
3. What Is The Difference Between ADR_BASE and ADR_HOME?
4. What Information Is Stored In The ADR?
5. Where Can I Get Information About ADR For My Database?

1. What Is ADR?

In version 11g, Oracle introduced the Automatic Diagnostic Repository, or ADR for short.
The ADR is a file based repository, that stores diagnostic data, such as the alert log, trace files, health monitor reports, incident reports and more.
It really stores all the diagnostic data that you need to send to Oracle support, in case of troubleshooting problems.

2. Where Is ADR Located?

The init.ora parameter diagnostic_dest will specify the location for the Automatic Diagnostic Repository.
This parameter is optional, if you don’t set it, its value defaults to the value of $ORACLE_BASE location.
If $ORACLE_BASE is not set, it defaults to $ORACLE_HOME/log.

3. What Is The Difference Between ADR_BASE and ADR_HOME?

Remember this:
ADR_BASE is the root directory of the Automatic Diagnostic Repository. The diagnostic_dest parameter tells you where the ADR_BASE is.
ADR_HOME is the path to the trace files for a specific database, instance, Oracle product or component.
The ADR_HOME has the following path, relative to the ADR_BASE:

diag/product_type/product_id/instance_id

Let me give you an example:

ADR_BASE=/usr/oracle
ADR_HOME=/usr/oracle/diag/rdbms/hrtst/hrtst1  -- ADR_HOME for instance1 of the database hrtst
ADR_HOME=/usr/oracle/diag/rdbms/hrtst/hrtst2  -- ADR_HOME for instance2 of the database hrtst

4. What Information Is Stored In The ADR?

The most important directories within the ADR are:

• alert – stores the XML version of the alert log
• hm – stores reports generated by the Health Monitor (more on that in a future post)
• incident – stores incident reports for the database instance.
• trace – stores the regular alert log, the one all of us are used to, plus a whole bunch of trace files.
• ddl – stores DDL statements logged into a plain text file and an XML file, when ENABLE_DDL_LOGGING=TRUE (12c only)

5. Where Can I Get Information About ADR For My Database?

The parameter diagnostic_dest will tell you the location for ADR_BASE.

What do you do when you quickly need to find out the location of the alert log, so you can troubleshoot a problem?
I bet you won’t remember the ADR_HOME location format of: diag/product_type/product_id/instance_id. If you do, then kudos to you!

It’s good thing that you have other options.

• The old “deprecated” parameters of *dump* are still showing valuable information.
  Let me confess here, when I quickly need to know where my alert log is, I just do a quick show parameter dump, and it still works like a charm.
• Another option is to check V$DIAG_INFO or GV$DIAG_INFO in case of a RAC environment.

As promised at the beginning, here is a summary of today’s post in a mind map format, so you’ll remember it!

Stay tuned for more ADR talk next week, as we dive into the adrci tool!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana