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