26 October 2012

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

This post is the first in a short series about Oracle 11g ADR. These posts are based on the presentations I gave at the NoCOUG Summer Conference in August 2011 and at the RMOUG Training Days in February 2012.

The Automatic Diagnostic Repository, or "ADR", is a location on disk where an Oracle instance keeps many files of diagnostic data that tools like Enterprise Manager or ADR Command Interpreter (adrci) use for gathering and presenting information to the user. When we refer to diagnostic files, we are talking about:
  • Alert logs (RDBMS instance, ASM instance, Listener)
  • Trace files (e.g. 10046 or 10053 as well as system-generated)
  • Core dumps
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.

Beware the ORACLE_BASE

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:

[oracle@localhost ~]$ . oraenv
ORACLE_SID = [oracle] ? ora11201
The Oracle base for ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_1 is /oracle/app/oracle

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

SQL> show parameter diagnost

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /oracle/app/oracle

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

[oracle@localhost ~]$ ls /oracle/app/oracle/diag/rdbms/ora11201/ora11201/
alert  cdump  hm  incident  incpkg  ir  lck  metadata  stage  sweep  trace

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.

[oracle@localhost dbs]$ echo $ORACLE_BASE
/oracle/app/oracle
[oracle@localhost dbs]$ unset ORACLE_BASE
[oracle@localhost dbs]$ echo $ORACLE_BASE

[oracle@localhost dbs]$ vi initora11201.ora 
[oracle@localhost dbs]$ grep "#" initora11201.ora 
#ora11201.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
#*.diagnostic_dest='/oracle/app/oracle'

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:

SQL> show parameter diagnostic_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /oracle/app/oracle/product/11.
                                                 2.0/dbhome_1/log

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:

[oracle@localhost dbs]$ ls /oracle/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/ora11201/ora11201/
alert  cdump  hm  incident  incpkg  ir  lck  metadata  stage  sweep  trace

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.

V$DIAG_INFO

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

SQL> select name, value from v$diag_info;

NAME                  VALUE
--------------------- ---------------------------------------------------------
Diag Enabled          TRUE
ADR Base              /oracle/app/oracle
ADR Home              /oracle/app/oracle/diag/rdbms/ora11201/ora11201
Diag Trace            /oracle/app/oracle/diag/rdbms/ora11201/ora11201/trace
Diag Alert            /oracle/app/oracle/diag/rdbms/ora11201/ora11201/alert
Diag Incident         /oracle/app/oracle/diag/rdbms/ora11201/ora11201/incident
Diag Cdump            /oracle/app/oracle/diag/rdbms/ora11201/ora11201/cdump
Health Monitor        /oracle/app/oracle/diag/rdbms/ora11201/ora11201/hm
Default Trace File    /oracle/app/oracle/diag/rdbms/ora11201/ora11201/trace/
                      ora11201_ora_16064.trc


NAME                  VALUE
--------------------- ---------------------------------------------------------
Active Problem Count  0
Active Incident Count 0

11 rows selected.

Deprecated Variables

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:

[oracle@localhost dbs]$ grep background_dump_dest initora11201.ora 
*.background_dump_dest='/home/oracle/test_bdump';

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:

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.


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

SQL> show parameter diagnostic_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /oracle/app/oracle

SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /oracle/app/oracle/diag/rdbms/
                                                 ora11201/ora11201/trace

Disabling ADR

Now, it is possible to disable the ADR and go back to the way things were prior to Oracle 11g. First you'll need to set the hidden parameter _diag_adr_enabled to FALSE, for example if using spfile:


SQL> alter system set "_diag_adr_enabled"=false scope=spfile;

System altered.

Then you MUST set the old %_DUMP_DEST parameters to point to the desired locations. These directories must exist and be writable by oracle, obviously.


SQL> alter system set background_dump_dest='/oracle/app/oracle/admin/ora11201/bdump' scope=spfile;

System altered.

SQL> alter system set core_dump_dest='/oracle/app/oracle/admin/ora11201/cdump' scope=spfile;

System altered.

SQL> alter system set user_dump_dest='/oracle/app/oracle/admin/ora11201/udump' scope=spfile;

System altered.




Then we restart the database instance for the settings to take effect. I've tested this on my 11.2.0.1 sandbox (these are the commands from that very session) and it works. Note that you will again see the ORA-32004 warning about having a deprecated parameter set, that is to be expected. However, this time the settings take effect:


SQL> show parameter dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /oracle/app/oracle/admin/ora11
                                                 201/bdump
core_dump_dest                       string      /oracle/app/oracle/admin/ora11
                                                 201/cdump
user_dump_dest                       string      /oracle/app/oracle/admin/ora11
                                                 201/udump
SQL> show parameter "_diag_adr_enabled"

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_diag_adr_enabled                    boolean     FALSE


HOWEVER, I do not advise doing this in production at all, and certainly not without the blessing of Oracle Support for any reason. I simply don't think there is anything to be gained by reverting to this method, and you lose the flexibility and power of having the alert data in XML format, which we'll see how to harness in a future post!


This ends Part 1 of my ADR intro series, which focused ADR location(s). Part 2 will focus on diagnostic files and how Oracle uses and maintains them.

1 comment:

  1. Pretty nice post and detailed guideline on setting up the testing case with different situations.

    Thanks
    Stone

    ReplyDelete