Oracle’s new ADR with command interface shows promise for DBAs to quickly investigate diagnostic information. This article takes a deeper look at ADRCI’s commands.
In Part I of this series we took a very quick look at the ADRCI utility and discussed how to show some of the alert log messages. This looked very promising and as such, we’ll go ahead and look at a few more commands—hoping to gain a little understanding of how we might use this utility and then dive a bit deeper into the different diagnostic information that is accessible.
The best place to start when looking at any utility is how to invoke it, how to get help, and how to exit properly. To invoke ADRCI you simply supply the adrci command line:
[oracle@ludwig ~]$ adrci ADRCI: Release 11.1.0.6.0 - Beta on Wed Sep 24 07:18:56 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. ADR base = "/opt/app/oracle" adrci> help
As you can see, this give you the adrci command line. Not too exciting, but issue the help command and you will see the following topics. Issue a help [topic] and you get a detailed help description for any of the topics. Also note that there are Oracle extended commands that are supposed to be used by Oracle, Oracle support I assume, (don’t quote me on that), and we might just might run a few in up and coming articles.
adrci> help
HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL
There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list
Here is the help generated for the run topic. Looks a lot like SQL*Plus doesn’t it? I am wondering at this point: why is this separate from SQL*Plus? I mean there are SET commands, a RUN command, echo, exit, help, spool, etc.. Seems like a logical integration and one that could have really been beneficial.
I mean, don’t we all have SQL*Plus commands that we use? Wouldn’t it be easier to just extend those current SQL*Plus scripts with calls to ADRCI without having to invoke a different command line utility? Sure, it would have.
adrci> help run
Usage: <RUN | @ | @@> <script_name>
Purpose: Run the specified ADRCI scripts.
RUN: Run the ADRCI commands in the specified script file.
@: Synonym for RUN.
@@: Similar to @, except when used within a script, the path of the calling script is used to locate the called script.
Arguments:
<script_file>: The file containing the ADRCI commands to execute.
If the file name is specified without a file extension, the default extension ".adi"is used.
Examples:
run my_script
@my_script
Ok, here are the extended topics to be used by Oracle. Any of them look interesting to you? Quite a few look interesting to me, especially that SWEEP topic. I can only imagine.
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
adrci>
Oh, to exit the adrci command line utility, just issue the exit command. That was easy.
But wait, I’m thinking we are still a bit confused about how the whole ADR repository is set up. When working with ADR you must continually remember that ADR maintains a directory for all diagnostic data related to instances or Oracle products. So, if we had multiple instances or products and we issued a simple show alert command, we would get the following:
adrci> show alert -tail 50 DIA-48449: Tail alert can only apply to single ADR home
This is not what we want to have happen, especially when we start writing scripts. This isn’t so bad when we try and run the same show alert command semi-interactively or at least with options that do not signal the report for a single instance or product. So, we can effectively do:
adrci> show alert Choose the alert log from the following homes to view: 1: diag/rdbms/db11fs/db11FS 2: diag/tnslsnr/ludwig/listener Q: to quit Please select option:
Notice how ADR shows an instance (db11FS) and a product (listener). Each of which have logs that we might want to extract information from. In the interactive mode we can just choose one or two or quit and we are very happy.
However, to actually issue the show alert –tail command, we need to tell ADR which ADR home we want to use, prior to the command. To do this, just issue the following:
adrci> set home diag/rdbms/db11fs/db11FS adrci> show alert -tail 2008-09-24 06:58:08.591000 -06:00 QMNC started with pid=22, OS id=2718 2008-09-24 06:58:20.404000 -06:00 db_recovery_file_dest_size of 1024 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. 2008-09-24 06:59:07.217000 -06:00 Completed: ALTER DATABASE OPEN 2008-09-24 07:02:54.990000 -06:00 Starting background process CJQ0 CJQ0 started with pid=25, OS id=2822 2008-09-24 07:03:10.296000 -06:00 Errors in file /opt/app/oracle/diag/rdbms/db11fs/db11FS/trace/db11FS_j009_2842.trc: ORA-12012: error on auto execute of job 11689 ORA-01403: no data found
Or:
adrci> set home diag/rdbms/db11fs/db11FS adrci> show alert -tail 2008-08-27 20:37:29.502000 -06:00 27-AUG-2008 20:37:29 * service_update * db11FS * 0 2008-08-27 20:37:56.511000 -06:00 27-AUG-2008 20:37:56 * service_update * db11FS * 0 2008-08-27 20:38:29.524000 -06:00 27-AUG-2008 20:38:29 * service_update * db11FS * 0
Problem solved depending on what ADR home you want to look at. Let’s look at how this might look in an ADR script:
SET ECHO OFF SET TERMOUT OFF SPOOL alerttail.lst SET HOME diag/rdbms/db11fs/db11FS SHOW ALERT -TAIL 5 SPOOL OFF SET ECHO ON SET TERMOUT ON
Now let’s run the script and look at the spooled file:
adrci> set echo off adrci> @tailalert.adrci adrci> host "more alerttail.lst" 2008-09-24 06:58:08.591000 -06:00 QMNC started with pid=22, OS id=2718 2008-09-24 06:58:20.404000 -06:00 db_recovery_file_dest_size of 1024 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. 2008-09-24 06:59:07.217000 -06:00 Completed: ALTER DATABASE OPEN 2008-09-24 07:02:54.990000 -06:00 Starting background process CJQ0 CJQ0 started with pid=25, OS id=2822 2008-09-24 07:03:10.296000 -06:00 Errors in file /opt/app/oracle/diag/rdbms/db11fs/db11FS/trace/db11FS_j009_2842.trc: ORA-12012: error on auto execute of job 11689 ORA-01403: no data found
I think I’m starting to like this utility, however there are a couple of issues I don’t like. For instance, currently, I like the way SQL*Plus interfaces with the host and has the edit command. To edit a script in ADR I have to issue the following with quotes. I don’t really like this but it might help in the long run to actually delimit what a true and complete host command is.
adrci> host "vi tailalert.adrci"
Obviously, there is a lot more to cover. Hopefully I’ve gotten us off to a good start–obviously we can’t cover all topics in this single article. Just be sure to come back for additional installments to this series, as I don’t think I’ll be done until I try all of these topics and start writing some scripts. We are beginning to have to interact with the host where ADR resides and that always means setting up your environment properly and extending into scripting. Next time we will explore the interaction of host and ADR a bit more.
