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.

Full article...