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:
 BEGIN BACKUP
 CD
 DDE
 DEFINE
 DESCRIBE
 END BACKUP
 LIST DEFINE
 MERGE ALERT
 MERGE FILE
 QUERY
 SET COLUMN
 SHOW CATALOG
 SHOW DUMP
 SHOW SECTION
 SHOW TRACE
 SHOW TRACEMAP
 SWEEP
 UNDEFINE
 VIEW

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
 diag/rdbms/db11/db11/trace/alert_db11.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%
 diag/rdbms/db11/db11/trace/db11_arc2_5540.trc
 diag/rdbms/db11/db11/trace/db11_arc1_5538.trc
 diag/rdbms/db11/db11/trace/db11_arc3_4300.trc
 diag/rdbms/db11/db11/trace/db11_arc3_31983.trc
 diag/rdbms/db11/db11/trace/db11_arc1_31979.trc
 diag/rdbms/db11/db11/trace/db11_arc0_31977.trc
 diag/rdbms/db11/db11/trace/db11_arc2_31981.trc
 diag/rdbms/db11/db11/trace/db11_arc3_5542.trc
 diag/rdbms/db11/db11/trace/db11_arc0_5536.trc
 diag/rdbms/db11/db11/trace/db11_arc0_4294.trc
 diag/rdbms/db11/db11/trace/db11_arc1_4296.trc
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:

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
NAME VALUE 
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 11.1.0.6.0 - 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
NAME VALUE 
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 11.1.0.6.0 - Beta on Fri May 7 14:27:44 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/opt/app/oracle"
NAME VALUE 
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 -------------------------------
TIMESTAMP FILENAME PAYLOAD
--------------- ---------------- -----------------------------------------------
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.

Additional Resources

Oracle Database's ADRCI : Reading the Old Alert Log and Listener Log
Purging Oracle Database's Alert Log with ADRCI; usage and warning
Rotating Oracle Database's Alert Log with ADRCI
Alert Log Scraping with Oracle's ADRCI Utility
Oracle ADRCI: ADR Command Interpreter
Oracle ADRCI: ADR Command Interpreter
Oracle FAQ Adrci
OracleBrains ADR Command Interpreter (ADRCI) a new tool in Oracle 11g

Previous

Back to DBAsupport.com