Intermediate SQL Color Coded SQL, UNIX and Database Essays


ORACLE 11g XML alert logs. Surprise: They are actually more convenient to work with

I have to admit, I was initially very skeptical about ORACLE 11g Automated Diagnostic Repository or ADR.

Why do I need to use “special” tool to access database alert log ? (and not vi) Who decided to move trace directories to some weird location ?? (why not keep them where they’ve always been?) Why store alert records as XML ??!! (it is meant for humans, you know …)

These things have “inconvenience” and “pain in the a%%.” written all over them … And, of course, the usual ORACLE attitude: “we decided to change it, so suck it up and learn how it works” did not help much …

Old habits die hard, but lately, I’ve started to come around using ADR. It turns out that with a bit of adrci knowledge and a few tweaks in the environment, working with alert logs from a regular UNIX shell becomes not only manageable but actually (gulp!) much more convenient …

Let’s create a few examples and see for ourselves.

Step 1. Set up the environment

To make things flexible …

export ORACLE_SID=db1
export DB_UNIQUE_NAME=db1_host1

Step 2. Select ALERT filters that you need

The adrci SHOW ALERT command has quite a number of conditions that you can use to filter ALERT data (this is essentially a WHERE clause):

adrci> help show alert

  Usage: SHOW ALERT [-p <predicate_string>]  [-term]
                    [ [-tail [num] [-f]] | [-file <alert_file_name>] ]
  Purpose: Show alert messages.

    [-p <predicate_string>]: The predicate string must be double quoted.
    The fields in the predicate are the fields:
        ORIGINATING_TIMESTAMP         timestamp
        NORMALIZED_TIMESTAMP          timestamp
        ORGANIZATION_ID               text(65)
        COMPONENT_ID                  text(65)
        HOST_ID                       text(65)
        HOST_ADDRESS                  text(17)
        MESSAGE_TYPE                  number
        MESSAGE_LEVEL                 number
        MESSAGE_ID                    text(65)
        MESSAGE_GROUP                 text(65)
        CLIENT_ID                     text(65)
        MODULE_ID                     text(65)
        PROCESS_ID                    text(33)
        THREAD_ID                     text(65)
        USER_ID                       text(65)
        INSTANCE_ID                   text(65)
        DETAILED_LOCATION             text(161)
        UPSTREAM_COMP_ID              text(101)
        DOWNSTREAM_COMP_ID            text(101)
        EXECUTION_CONTEXT_ID          text(101)
        ERROR_INSTANCE_ID             number
        ERROR_INSTANCE_SEQUENCE       number
        MESSAGE_TEXT                  text(2049)
        MESSAGE_ARGUMENTS             text(129)
        SUPPLEMENTAL_ATTRIBUTES       text(129)
        SUPPLEMENTAL_DETAILS          text(129)
        PROBLEM_KEY                   text(65)

But, probably the most useful filters are ORIGINATING_TIMESTAMP and MESSAGE_TEXT.

Step 3. Create adrci command that uses the filters

Let’s say we want to see all ORACLE errors from alert log. Adrci command that displays that looks like:

UNIX> adrci exec="set home diag\/rdbms\/$DB_UNIQUE_NAME/$ORACLE_SID\; \
  show alert -p \\\"message_text like '%ORA-%'\\\""

Run this command and notice that not only “grepped for” information is displayed, but also the associated errors and, most importantly the timestamp:

2010-07-02 02:32:17.669000 -04:00
ORA-03114: not connected to ORACLE
ORA-02063: preceding line from AB.WORLD
ORA-03113: end-of-file on communication channel

Isn’t that nice ? 🙂

Step 4. Save adrci filter as a named “shortcut”

So that the next time, you can simply call it up without retyping the entire command again …

I’m going to use a (Korn) shell function here, but of course, there are many available options: i.e. aliases or scripts.

ealert() {
  adrci exec="set home diag\/rdbms\/$DB_UNIQUE_NAME/$ORACLE_SID\; \
     show alert -p \\\"message_text like '%ORA-%'\\\""


# To use it, simply run ealert from the shall prompt
UNIX> ealert

Other useful “greplets”

Let’s create a few more useful alert tools:

Using more than one condition – All ORACLE errors for the last month

lealert() {
  adrci exec="set home diag\/rdbms\/$DB_UNIQUE_NAME/$ORACLE_SID\; \
   show alert -p \\\"message_text like '%ORA-%' \
     and originating_timestamp > systimestamp-30\\\""

Looking for instance startup/shutdown times

db_start_stops() {
  adrci exec="set home diag\/rdbms\/$DB_UNIQUE_NAME/$ORACLE_SID\; \
   show alert -p \\\"message_text like '%Shutting down instance%' \
     or message_text like '%Starting up ORACLE RDBMS%'\\\""

Grepping for custom messages

galert() {
  adrci exec="set home diag\/rdbms\/$DB_UNIQUE_NAME/$ORACLE_SID\; \
   show alert -p \\\"message_text like '%$1%'\\\""


UNIX> galert "dead shared server"

Going further

One of the nice features about ADR is that all trace files are in one location and have the same interface to access them.

That means that by slightly modifying our scripts, we can reuse them to grep information is say, listener log.

export TNSLSNR_HOST=test_host
export TNSLSNR_NAME=listener

glistener() {
  adrci exec="set home diag\/tnslsnr\/$TNSLSNR_HOST/$TNSLSNR_NAME\; \
   show alert -p \\\"message_text like '%$1%'\\\""


Hope this makes it easier for somebody to accept the new ADR reality 🙂 (and if you want more, I attached a bunch of additional greplets in the TOOLS section).


Tagged as: , , , Leave a comment
Comments (3) Trackbacks (0)
  1. Great site! Thanks for posting this here!

    best regards

  2. Awesome and thanks!

  3. Great information ..Provided very good insight .. Thanks!

Leave a comment

No trackbacks yet.