Oracle Database’s ADRCI : Reading the Old Alert Log and Listener Log

Oracle’s ADR seems to be great for the new XML logs but let’s not forget about the old style logs as ADRCI can do a few things with them as well.

In the last few articles that I’ve been writing about ADR, I’ve stuck with viewing and rotating the new XML style alert log from Oracle. You can view these articles at Alert Log Scraping with Oracle’s ADRCI Utility and Rotating Oracle’s Alert Log with ADRCI. However, as most of us know, even though Oracle is putting out new XML alert and listener log files the old alert.log and listener.log files still exist. When I first started using ADR, I thought that doing anything with these old log files had to be done the old fashion way through querying the database for locations, writing scripts to manage, and accessing the operating system to view them. This just isn’t the case, as we will see through this article.

Just to level the playing field, for those that have only been working with Oracle 11g, the old alert log and listener log files are straight text and have, now, somewhat convoluted ways of determining where they exist on disk. Here is how we would normally go about looking at these log files.

For the alert log

1. Query the database for the background_dump_dest

SQL> SELECT value FROM v$parameter WHERE name = 'background_dump_dest';
VALUE
------------------------------------------
/opt/app/oracle/diag/rdbms/db11/db11/trace

2. Login to the database server, change directory to the background_dump_dest, perform a directory listing to get the name of the alert log, and then view the alert log.

[oracle@ludwig ~]$ cd /opt/app/oracle/diag/rdbms/db11/db11/trace
[oracle@ludwig trace]$ ls -l *.log
-rw-r----- 1 oracle oinstall 183975 Apr 29 02:00 alert_db11.log
[oracle@ludwig trace]$ view alert_db11.log

For the listener log file

1. Login to the database server and execute the lsnrctl stat command to get the location.

[oracle@ludwig ~]$ lsnrctl stat
LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 29-APR-2010 17:00:50
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ludwig)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 27-APR-2010 22:26:01
Uptime 1 days 18 hr. 34 min. 48 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File /opt/app/oracle/product/11.1.0/network/log/listener.log

2. Change directory to the specified location and then view the listener log.

[oracle@ludwig ~]$ cd /opt/app/oracle/product/11.1.0/network/log
[oracle@ludwig trace]$ view listener.log

These methods were all fine and dandy for the pre-ADR but today, with Oracle 11g and the new ADR utility, we can be a slight bit smarter about how we view, manage, and script for possible extensions from Oracle in the future.

To start with, we must first ask ourselves how can we initially get the location of both the alert log and listener log from ADR; bypassing connecting to the database through SQL*Plus. This is actually quite easy but a bit hidden. ADR has a command that will list trace files and if we took Oracle at their word we would expect to see just the *.trc files listed when we issue the following command.

[oracle@ludwig ~]$ adrci
ADRCI: Release 11.1.0.6.0 - Beta on Thu Apr 29 17:18:05 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/opt/app/oracle"

adrci> show tracefiles

Now if we just watched all the trace files fly past the screen you’d miss the fact that there are other files that are listed. In particular, the alert log and listener log are in this listing. I’m guessing that Oracle, since these two files are part of diagnostic data, had to classify them in some way as to not interfere with the new XML alert and listener log files. Anyway we can easily see these two files if we restrict/filter our show tracefiles as such:

adrci> show tracefiles %.log
diag/rdbms/db11/db11/trace/alert_db11.log
diag/tnslsnr/ludwig/listener/trace/listener.log

This is really quite nice when you think of it as we quickly and easily get to these old-style log’s locations and names with ADR. However, then we ask ourselves how can we actually view these files? This is where the pseudo-trick comes into play. A nice variety of ADRCI commands are listed within the manual but, as we know this is often not the full story. If you just look at the manuals, you will only get a subset of the actual commands. To get a full listing you’d first have to issue the HELP command within ADR and then read the fine print at the end of the listing.

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.

At the end of the listing there are EXTENED commands if you issue the HELP EXTENDED command within ADRCI, such as:

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

This is where we get some help with viewing the old-style alert log and listener log. Quite easily now we can use the SHOW TRACE on each of these logs/tracefiles. Just issue one of the following (assuming my listing).

For alert log:

adrci> show tracefile %.log
diag/rdbms/db11/db11/trace/alert_db11.log
diag/tnslsnr/ludwig/listener/trace/listener.log
adrci> show trace alert_db11.log

/opt/app/oracle/diag/rdbms/db11/db11/trace/alert_db11.log
----------------------------------------------------------
LEVEL PAYLOAD
----- ----------------------------------------------------
Fri Apr 23 18:38:39 2010
kcrrdmx: Successful archiving of previously failed ORL
Archiver process freed from errors. No longer stopped
Fri Apr 23 18:38:41 2010
ARC3: Encountered disk I/O error 19502
ORA-27072: File I/O error
Linux Error: 2: No such file or directory
Additional information: 4
Additional information: 79873
Additional information: 52736

For listener.log:

adrci> show tracefile %.log
diag/rdbms/db11/db11/trace/alert_db11.log
diag/tnslsnr/ludwig/listener/trace/listener.log
adrci> show listener.log
DIA-48415: Syntax error found in string [show listener.log] at column [17]

adrci> show trace listener.log
/opt/app/oracle/diag/tnslsnr/ludwig/listener/trace/listener.log
----------------------------------------------------------
LEVEL PAYLOAD
----- ----------------------------------------------------
WARNING: Subscription for node down event still pending
Sat Apr 17 03:54:47 2010
17-APR-2010 03:54:47 * service_update * db11 * 0
Sat Apr 17 03:55:17 2010
17-APR-2010 03:55:17 * service_update * db11 * 0
Sat Apr 17 04:00:50 2010
17-APR-2010 04:00:50 * service_update * db11 * 0
Sat Apr 17 04:01:47 2010

Please note here that the full path to the alert or listener log was not required when viewing these two files. In addition, the default editor being used, as I’m on Linux, is the VI editor. You can change the editor using the SET EDITOR command, such as ‘SET EDITOR xemacs’, ‘SET EDITOR cat’, or whatever you like. Just test before using as Unix’s more or less commands do behave strange.

A couple of other notables about the SHOW TRACE command that might aid in your scripting efforts include:
SHOW TRACE -term ; will list the whole log file to the terminal without pausing.
SHOW TRACE %.log -term; will list all log files to the terminal without pausing. You can use this in conjunction with the SPOOL command to get one complete listing of all log files.

If you plan on using the new ADRCI utility don’t think you also have to give up working with the old-style log files. While the XML based log files are nice, having both old and new allows us to do some interesting things. I’d suggest firstly to modify all of your old scripts to get the location and names of alert and listener log files from ADR. It is much easier, and will allow you to extend your scripting to the next level and align your scripts with the direction of Oracle diagnostics.

Additional Resources

Oracle?s ADR Command Interpreter (ADRCI) ? Part 3
Oracle’s ADR Command Interpreter (ADRCI) ? Part 2
Oracle?s ADR Command Interpreter ? Part 1
Oracle: ADRCI: ADR Command Interpreter

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles