Oracle's ADR Command Interpreter (ADRCI) - Part 3

October 2, 2008

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