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: 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:
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 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.
