Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 19, 2010

Oracle Database's ADRCI: the Extended Commands

By James Koopmann

Oracle's ADRCI extended commands provide valuable scripting and file viewing options, with about two dozen commands that you can use to look at and manage diagnostic information.. ADR is the future of Oracle diagnostics and it's essential that database administrators understand it.

If you read Oracle database documentation for the ADRCI utility, and looked at the command reference, you'd see about two dozen commands that you can use to look at and manage diagnostic information. However, in my opinion this set of commands, when trying to script for various scenarios leave you begging for a bit more. This is where, after issuing the ADRCI HELP command, we should pay close attention to the last line of output that states:

There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list.

To see these extended commands just enter the ADRCI HELP EXTENDED command such as:

adrci> help extended
 HELP [topic]
 Available Topics:

From this set of extended commands, probably my favorite is the SHOW TRACE command. This command allows you to view, through your editor, the contents of a trace file. One of the best uses is to actually view the old-style alert log that many of us have grown up on (alert_.log) as Oracle considers this just another trace file. Just issue the following SHOW TRACE commands:

adrci> show tracefile alert%log
adrci> show trace alert_db11.log

This works just as well with what we would consider real tracefiles as well. Use the following to investigate archiving tracefiles:

adrci> show tracefile %arc%
adrci> show trace db11_arc2_5540.trc

For scripting purposes, a nice parameter to the SHOW TRACE command is the -TERM option; telling SHOW TRACE to just spool the output to the screen where you, within a script, could capture it to a text file for further interrogation:

SHOW TRACE -term ; will list the whole log file to the terminal without pausing
SHOW TRACE %.log -term; will list all log files to the terminal without pausing.
SHOW TRACE %arc% -term; will list all trace files that contain the 'arc' in the name.

Another important set of commands that can aid in your scripting efforts are those that allow you to define variables within ADRCI. Very simply use the following format to define a substitution variable, DEFINE.

So if we wanted to define a variable for the old-style alert log and then use that variable, all we need to do is; noticing the quotes surrounding the variable_value and the tilde before the use of the variable_name:

adrci> define alertLog 'alert_db11.log'
adrci> show trace ~alertLog

To view any variables that might have been defined, use the LIST DEFINE command:

adrci> list define
alertLog alert_db11.log 
1 variables defined

Then to unset or undefined a variable, use the UNDEFINE command:

adrci> undefine alertLog
adrci> list define
0 variables defined

Now one might wonder what good defining a variable and using it within the same session might be. Well, just like normal scripting in Unix or DOS shell, we often save variables in a script file to be used at a later date, much like a .profile in Unix. To do this with ADRCI we could easily create a variable script file and then execute it upon entry into ADRCI. Assume we have the following file (env.adrci) with variables being defined:

set base /opt/app/oracle
define rdbmsHomepath 'diag/rdbms/db11/db11'
define tnslsnrHomepath 'diag/tnslsnr/ludwig/listener'
define alertLog 'alert_db11.log'
define listenerLog 'listener.log'
list define

Then, when starting ADRCI we could issue a SCRIPT argument such as the following:

[oracle@ludwig ~]$ adrci
ADRCI: Release - Beta on Fri May 7 14:25:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/opt/app/oracle"
adrci> run env.adrci
alertLog alert_db11.log 
listenerLog listener.log 
rdbmsHomepath diag/rdbms/db11/db11 
tnslsnrHomepath diag/tnslsnr/ludwig/listener 
4 variables defined

In addition, if you wanted to just run the script, exiting ADRCI after execution you could just execute from the Unix prompt:

[oracle@ludwig ~]$ adrci script=env.adrci
ADRCI: Release - Beta on Fri May 7 14:27:44 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/opt/app/oracle"
alertLog alert_db11.log 
listenerLog listener.log 
rdbmsHomepath diag/rdbms/db11/db11 
tnslsnrHomepath diag/tnslsnr/ludwig/listener 
4 variables defined

Now if we wanted to switch between the two homes (rdbms & tnslsnr) within a script all we would have to issue is the following:

adrci> set homepath ~tnslsnrHomepath

Another really helpful EXTENDED command would be the MERGE ALERT command. This command will take multiple alert logs from multiple ADR homes and merge them together so that you can correlate what is happening across nodes, systems, instances, etc. more easily. The command is easy, just issue a MERGE ALERT without any options and you'll get a basic merge of alert logs. While not too exciting of an output, realizing I just copied my current alert log into another diagnostic directory, this merge does show how beneficial it could be in sniffing out issues.

adrci> merge alert

The above will put you in your default editor with the alert logs merged. At the top of the file you can confirm what has been merged together.

---------------------------- Files to be merged ----------------------------
/opt/app/oracle/diag/rdbms/db11/db11/log.xml (alert_db11)
/opt/app/oracle/diag/rdbms/db12/db12/log.xml (alert_db12)
/opt/app/oracle/diag/tnslsnr/ludwig/listener/log.xml (alert_listener)
------------------------------ The merge results -------------------------------
--------------- ---------------- -----------------------------------------------
18:19:48.011000 alert_listener 18-APR-2010 18:19:48 * service_update * db11 * 0
18:20:21.022000 alert_listener 18-APR-2010 18:20:21 * service_update * db11 * 0
18:26:36.114000 alert_listener 18-APR-2010 18:26:36 * service_update * db11 * 0
18:27:21.126000 alert_listener 18-APR-2010 18:27:21 * service_update * db11 * 0
02:00:00.128000 alert_db11 Clearing Resource Manager plan via parameter
02:00:00.128000 alert_db12 Clearing Resource Manager plan via parameter
02:48:43.326000 alert_db11 Thread 1 advanced to log sequence 11
02:48:43.326000 alert_db12 Thread 1 advanced to log sequence 11
02:48:43.327000 alert_db11 Current log# 2 seq# 11 mem# 0: /opt/app/oracle/oradata/db11/redo02.log
02:48:43.327000 alert_db12 Current log# 2 seq# 11 mem# 0: /opt/app/oracle/oradata/db11/redo02.log
03:00:16.812000 alert_db11 Thread 1 advanced to log sequence 12
03:00:16.812000 alert_db12 Thread 1 advanced to log sequence 12
03:00:16.911000 alert_db11 Current log# 3 seq# 12 mem# 0: /opt/app/oracle/oradata/db11/redo03.log

In this same vain, and as helpful, is the MERGE FILE command that will merge files based on time order of messages as well. Valid files consist of trace files, incident files, and alert logs.

Oracle's EXTENDED commands for ADRCI show some great promise and I've only scratched the surface here as each of these commands have ample options to extract additional information. There are, as you should have noticed, other EXTENDED commands I didn't go into any detail at all. My suggestion is to investigate, play with, and then put to good use as the sooner you become familiar with the ADRCI command set the better off you will be in the long run. ADR is the future of Oracle diagnostics and without an understanding you will surely be lost.

» See All Articles by Columnist James Koopmann

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM