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.
»
See All Articles by Columnist James Koopmann
» 
Comment and Contribute