Learning to Love the ADR - Part 1: Location, Location, Location!

Now in prior versions of Oracle, you would expect to find these files under $ORACLE_BASE/admin/$ORACLE_SID in bdump, cdump or udump, by default. You could always specify different locations with the %_DUMP_DEST initialization parameters, of course. The log files would grow ad infinitum if not properly rotated, compressed, archived and/or truncated, possibly causing some headaches with disk space management.

As of Oracle 11g, however, much (but not all) of this is managed by the instance now. As an added bonus, Oracle moved the locations of these files, as I'm sure many of you with alert log monitoring software have found out by now. The thing that catches most DBAs off-guard when upgrading to 11g is the change in location for these files, especially the alert log.



Well, Oracle Corporation has seen fit to unify all these various alert, trace and dump files under the ADR location, which is specified in the DIAGNOSTIC_DEST initialization parameter. This one parameter replaces all of the %_DUMP_DEST parameters, and defaults to the value of the $ORACLE_BASE environment variable at the time of instance startup. If the $ORACLE_BASE environment variable is unset, then DIAGNOSTIC_DEST defaults to $ORACLE_HOME/log.

Note that I emphasized "at the time of instance startup" earlier. This is important because you may come across servers where the ORACLE_BASE variable might not be defined when the instance is first started. In this case the ADR will be under $ORACLE_HOME/log. Then a DBA consultant might come along and standardize the login profile with an ORACLE_BASE setting, and upon next startup the DIAGNOSTIC_DEST will move. This can make things confusing for DBAs trying to find older diagnostic files in the new ADR location if they don't know (and rarely expect) this kind of situation. Yet it is one that I have personally witnessed and was one of the initial motivations for me to put this presentation together and learn more about the ADR for my own benefit.

Let's demonstrate this quickly. I have my working 11.2.0.1 sandbox on Oracle Linux. I set the full slate of environment vars via oraenv:

Then I launch sqlplus and start the instance, then check the diagnostic_dest parameter value:

That is as expected. I can verify that my ADR files exist under there

Now, shutdown the database and create a pfile from the spfile (if you use an spfile). Then be sure rename the spfile so it isn't used during startup. Next we unset the necessary initialization parameters and environment variable.

Note I also had to comment out the __oracle_base instance parameter value in the pfile. At this point our instance will have no knowledge or ties to the old ORACLE_BASE. Now, log back into sqlplus and startup (or even just startup nomount) and check again:

You can see we're now using the value of $ORACLE_HOME/log. Look under that directory and you'll indeed find the full set of ADR files you would expect:

The only problem is that it doesn't have any of the history contained in the files in the old ADR.

The bottom line? Be careful and make sure your $ORACLE_BASE is properly set and I strongly advise you to explicitly set your diagnostic_dest initialization parameter so it doesn't rely on environment variables.

Other than checking the diagnostic_dest value, you can also query the view V$DIAG_INFO to see where everything is going:

Now the old %_DUMP_DEST initialization parameters are deprecated and will default to values based on the DIAGNOSTIC_DEST value. But, while I'm here, let's test another question that's rattled around my brain: What happens if we set both DIAGNOSTIC_DEST and BACKGROUND_DUMP_DEST to different paths?

To test, I quickly made a bdump directory in my user home and set the old background_dump_dest value in the pfile:

I'll note that I've also uncommented the diagnosted_dest value from before. Now, we startup and we immediately get a note that we're using a parameter that some might call "old and busted", but the instance does start as normal:

Now when we check, we see that Oracle just completely ignored the setting we specified for the old parameter: