Oracles new ADR with command interface shows promise for DBAs to quickly investigate diagnostic information. This article takes a deeper look at ADRCIs 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, well go ahead and look at a few more commandshoping 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 220.127.116.11.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, (dont 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 doesnt 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, dont we all have SQL*Plus commands that we use? Wouldnt 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, Im 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 isnt 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
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. Lets 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 lets 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 Im starting to like this utility, however there are a couple of issues I dont 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 dont 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 Ive gotten us off to a good start--obviously we cant cover all topics in this single article. Just be sure to come back for additional installments to this series, as I dont think Ill 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.