Oracle’s ADR Command Interpreter – Part 1

Oracle’s new ADR with command interface shows promise as a
way for DBAs to quickly investigate diagnostic information.

New to Oracle Release 11g, Oracle has beefed up, revamped,
and made better its infrastructure for DBAs to handle, diagnose, collect,
report, and manage diagnostic information. Most of the information that falls
into this category we have all been introduced to before: alert logs, trace
files, dumps, core files, etc. The newness of this diagnostic information is how
Oracle has structured a repository and provided a tool to investigate issues.
Now we can more easily identify, track, and resolve problems. Let’s take a
quick look in part I of a series that will take us from conception of ADR
through using ADR’s command interpreter.

Before we get too excited, Oracle’s Automatic Diagnostic Repository (ADR) is
not any new embedded database or even a hook into the current engine. In fact,
it is a file-based repository. We are used to this format and it is actually an
efficient way to keep diagnostic information. If it were in a database, we
would be subject to a database’s availability. With a file-based system, we are
also able to do other activities, such as hook the data into external reporting
systems. Because this is a new feature, we now have a new initialization
parameter. The BACKGROUND_DUMP_DEST and USER_DUMP_DEST are now deprecated and
replaced by DIAGNOSTIC_DEST. To see your current setting, just issue the
following SHOW command.

SQL> show parameter diag
——————— ———– —————-
diagnostic_dest string /opt/app/oracle

This DIAGNOSTIC_DEST parameter points to the top directory
in a structure stored outside the Oracle database and has the general structure
as follows. As there can be multiple directory structures under a single
DIAGNOSTIC_DEST it is good to know that it is segregated by product, product
id, and instance name

`– db11
`– db11
|– alert
|– cdump
|– hm
|– incident
|– incpkg
|– ir
|– lck
|– metadata
|– stage
|– sweep
`– trace

A couple of the
key subdirectories and their contents, most of which we are familiar with would




Find the XML alert log here


Location for core files


This one is new to me and we will surely be looking closely
at it. But it contains multiple subdirectories where each subdirectory is an
incident and contains multiple dump files for that incident.


Location for background, server, and SQL trace files

Also contains the old alert.log (non-XML) file


hm, incpkg, ir, lck, metadata, stage, sweep

There is a view called V$DIAG_INFO that can also help you
find these locations if you just can’t remember the structure. Alternatively,
in the case of scripting with Oracle, you can use the contents of this view to
intelligently script reports through the ADR command interface. To view the
contents, displaying the locations of the subdirectories of ADR, use the
following SQL.

SQL> SELECT name,value FROM v$diag_info;
————————- ————————————————————
Diag Enabled TRUE
ADR Base /opt/app/oracle
ADR Home /opt/app/oracle/diag/rdbms/db11/db11
Diag Trace /opt/app/oracle/diag/rdbms/db11/db11/trace
Diag Alert /opt/app/oracle/diag/rdbms/db11/db11/alert
Diag Incident /opt/app/oracle/diag/rdbms/db11/db11/incident
Diag Cdump /opt/app/oracle/diag/rdbms/db11/db11/cdump
Health Monitor /opt/app/oracle/diag/rdbms/db11/db11/hm
Default Trace File /opt/app/oracle/diag/rdbms/db11/db11/trace/db11_ora_3014.trc
Active Problem Count 0
Active Incident Count 0
11 rows selected.

Here you can see that ADR Base is the same as the DIAGNOSTIC_DEST
initialization parameter. Since we will almost always go to the ADR Home
subdirectory for an instance, this is a query you will want to have handy. It
is also handy to notice the current trace file that will be used for your
current session. If I were to connect with, or look at a different session,
this default trace file would be different and unique to that session.

While we
will get into the different types of data stored in ADR, I thought it
appropriate to introduce you to how all this looks for something we are all
familiar with, the alert log. In past Oracle releases, in order to view the
alert log, you would need to carry out the following steps (assuming you’re
from the old school and not using Enterprise Manager):

to the database with SQL*Plus

V$DIAG_INFO view to find the Diag Trace directory

into the database server

directory to the Diag Trace directory

the alert.log with a text editor

The above five steps are not difficult to do but we are not
really left with much after we open the alert file, and, if we want to do any
analysis on the alert.log file we have to write our own scripts. With the ADR
command interface, the steps to view the alert log now become:

into the database server

the ADR command interface (adrci)

the SHOW ALERT command

Three steps compared to five isn’t much of a comparison. However,
do notice I didn’t need to know where my trace directory existed. I just used
the ADR command interface. After the SHOW ALERT command, I am immediately put
into the vi editor and can browse my alert log. Now this might not be the best
interface (vi) but since I’ve been using vi for over 20 years, I’m pretty
happy. You might want to brush up on vi. If you just can’t live with vi,
there is a SET EDITOR command where you can change the default editor. Aside from
vi commands, the ADR command interface does have more powerful SHOW commands
that will probably get you close to what you want. We will be investigating
these in a future article but I found these following commands very quickly,
very helpful, straight out of the documentation, and very much like a Unix tail

SHOW ALERT -TAIL; To display the last 10 lines of the alert log.

SHOW ALERT -TAIL 50; To display the last 50 lines of the alert log.

SHOW ALERT -TAIL -F; To display the last 10 lines of the alert log then wait for additional message to be written to the log, also displaying them to your session as they arrive. Use CTRL_C to exit this mode.

SHOW ALERT -P "MESSAGE_TEXT LIKE '%ORA-%'"; search for alert lines that have ORA- in them.

Oracle’s new ADR, with command interface looks very promising. The repository seems to be well structured and the command interface shows promise for DBAs to quickly view diagnostic information. Next time we will dive deeper into the different diagnostic information contained, the command interface.


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.

Latest Articles