Oracle's ADR Command Interpreter - Part 1
August 7, 2008
Oracles 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. Lets take a quick look in part I of a series that will take us from conception of ADR through using ADRs command interpreter.
Before we get too excited, Oracles 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 databases 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 NAME TYPE VALUE --------------------- ----------- ---------------- 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.
/opt/app/oracle ./rdbms `-- 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 be:
There is a view called V$DIAG_INFO that can also help you find these locations if you just cant 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; NAME VALUE ------------------------- ------------------------------------------------------------ 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 youre from the old school and not using Enterprise Manager):
1. Connect to the database with SQL*Plus
2. Query V$DIAG_INFO view to find the Diag Trace directory
3. Log into the database server
4. Change directory to the Diag Trace directory
5. Open 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:
1. Log into the database server
2. Invoke the ADR command interface (adrci)
3. Issue the SHOW ALERT command
Three steps compared to five isnt much of a comparison. However, do notice I didnt 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 Ive been using vi for over 20 years, Im pretty happy. You might want to brush up on vi. If you just cant 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 command.
Oracles 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.