Kevin Kempf's Blog

June 10, 2011

Rotating Listener Logs without Bouncing the Listener

Filed under: 10g, 11g, Oracle — kkempf @ 9:38 am

It's too big!

The Trouble with Listener Logs

Is that over time, they’re easily forgotten about.  Then they get ginormous, and really unmanageable.  I think there used to be a 2gb limit, after which they’d just kind of stop writing, but I haven’t seen that recently.  Perhaps that was a 32-bit limitation, or a 9i limitation, unsure.  Regardless, I thought I’d share a nifty little shell script for linux which helps manage this issue.

There’s a basic problem with the listener log in Linux: while you can move/rename it, it won’t start a new log file until the listener is bounced.  This is because the listener data is written to the inode, and doesn’t take your change into effect until the listener is bounced.

This fix is based on the premise that you can use lsnrctl to set log_status off, which allows you to manipulate the underlying log file.  You can then set it back to on, and it will continue happily logging connections.

There’s a few assumptions which you may need to tweak for your environment.  First, that you set your environment in the first line for the RDBMS; this essentially is just a call to invoke the $ORACLE_HOME/SID_host.env file.   You can easily hard code this to your needs.  Second, that you have a named listener which has the same name as your SID.  Finally, that you have the zip utility somewhere in your base path.

In case you were wondering, why the %s in the datestamp?  It basically makes the filename it creates reasonably unique, so that in case you accidentally (or intentionally, while testing) run this script multiple times in one day, you don’t lose your logs.

Parameters: The first parameter is simply the database SID, represented as ${1} which is used to invoke the database environment, and also to turn off/on the logging of the listener by the same name.  The second parameter is the full path to the current listener log.

. ~/env/${1}_ORACLE
DATE_STAMP=`date +%m%d%y%s`

lsnrctl <<END
set current_listener ${1}
set log_status off
lsnrctl <<END
set current_listener ${1}
set log_status on

Script in Action

total 41316
-rw-rw-r-- 1 oradevl dba     2663 Jun 10 10:23
-rw-rw---- 1 oradevl dba      888 Jun 10 10:23 dev.log
$ ./ DEV /u01/appdevl/oracle/devdb/11.1.0/log/diag/tnslsnr/myhost/dev/trace/dev.log

LSNRCTL for Linux: Version - Production on 10-JUN-2011 10:24:59

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> Current Listener is DEV
DEV parameter "log_status" set to OFF
The command completed successfully
LSNRCTL for Linux: Version - Production on 10-JUN-2011 10:24:59

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> Current Listener is DEV
DEV parameter "log_status" set to ON
The command completed successfully
LSNRCTL>   adding: u01/appdevl/oracle/devdb/11.1.0/log/diag/tnslsnr/myhost/dev/trace/dev.log.0610111307715899 (deflated 80%)
total 41320
-rw-rw-r-- 1 oradevl dba     2663 Jun 10 10:23
-rw-rw-r-- 1 oradevl dba      579 Jun 10 10:24
-rw-rw---- 1 oradevl dba      931 Jun 10 10:25 dev.log

Throw it in cron, and you’re done!

For those not too good with cron

  • the first entry says at 0315 on the first day of the month, remove anything from the trace directory over a year old
  • the second entry runs the aforementioned script to rotate the listener logs at 00:00 (midnight) on the first of the month for the DEV db and affects the shown log
crontab -l
# cleanup listener logs
15 3 1 * * /usr/bin/find /u01/appdevl/oracle/devdb/11.1.0/log/diag/tnslsnr/myhost/dev/trace  -mtime +365 -exec rm -rf {} \;
# rotate listener logs
0 0 1 * * /scratch/oracle/dba/scripts/ DEV /u01/appdevl/oracle/devdb/11.1.0/log/diag/tnslsnr/myhost/dev/trace/dev.log

I know this should go without saying, but..

Test this script in a non-production environment and understand it before you use it!

June 9, 2011

Where’s the Oracle 11i log?

Filed under: Uncategorized — kkempf @ 3:20 pm

In 11i, I often have to go hunting for logs.  This will be a “work in progress” reference post to see where to look.  I know some of these logs are rather useless, especially those in $APPLCSF/APPLLOG, such as the adapcctl.txt log which tells you that apache was started or stopped, but not when.

Please feel free to contribute via comments!





adcmctl (concurrent manager) log
$APPLCSF/$APPLLOG/adcmctl*.txt (typically $COMN_TOP/admin/log/SID_host)

workflow notification mailer
$APPLCSF/$APPLLOG/FNDCPGSC*.txt (typically $COMN_TOP/admin/log/SID_host)

$APPLCSF/$APPLLOG/rep60*.txt (typically $COMN_TOP/admin/log/SID_host)

11g Database Alert Log

11g Database Listener Log

11i Concurrent Managers Main Log
$COMN_TOP/admin/log/SID_host/SID_date.mgr ($APPL_CSF/APPL_LOG)

Apache logs

Apache Jserv logs

June 3, 2011

11i RDBMS Auditing

Filed under: 11g — kkempf @ 12:50 pm

Everyone loves auditors

Everywhere I’ve worked with Oracle, some 3rd party has come around every year to ensure some minimalistic level of compliance with industry standards in areas such as security, backups, disaster recovery and best practices.  While taken at different levels of importance depending upon industry and company type, one “mandatory” fix which annoyed me was to set my 11g RDBMS audit_trail to OS.   Effectively what this does, is take hundreds of thousands of events per year, and write a text file about each of them to $ORACLE_HOME/rdbms/audit (or whatever you have your audit_file_dest set to).   The logic, this 3rd party said, was that in this way, the DBA could not be the sole owner (or manipulator) of audit-able events.   I did this for a year, and ended up with about a million files in this directory, which then became effectively useless because you can’t hardly search or organize a million files in one Linux directory.   I know you can use grep or some other tool to find what you’re looking for, but it’s still rather unmanageable.  Given that we’re a small shop, where the line between sysadmin and dba is often blurry, I made the command decision to set the parameter back to DB so that if something ever did happen, I could actually find the event in sys.aud$ rather than hunt through a million files for the data.  But this brought up some bigger points, regarding RDBMS auditing and 11i.

  • Some files will always get written to audit_file_dest, regardless of your AUDIT_TRAIL parameter.  These include database startups, shutdowns, and sysdba/sysoper accesses.  See note 308066.1; it was new information to me.
  • When AUDIT_TRAIL is set to DB (or DB Extended), the sys.aud$ table (probably in your SYSAUX tablespace) grows without bounds.
  • There is not existing method for managing these files or this table

Enter Cron and EM

My solution was simple, but easy to manage.   For the files written to audit_file_dest, add a cron job checking every day for files over 31 days, and deleting them.  (Obviously, you need to consider your retention requirements)

  • 15 7 * * * /usr/bin/find /u01/oracle/proddb/11.1.0/rdbms/audit -mtime +31 -exec rm -rf {} \;

To keep the database table sys.aud$ from growing forever, add an EM job which deletes records after a certain age (1 year in my case)

  • delete from sys.aud$ where ntimestamp# < sysdate – 365;

June 1, 2011

Getting Oracle to work is a customization

Filed under: 11i — kkempf @ 9:49 am

Mobile Supply Chain loose ends

There were a few items in mobile supply chain which I followed up on, out of curiosity.  They revolved around the html-based administrative pieces which you can access after an 11i login.  The short of it is, that they simply do not work out of the box in 11i.  So I opened SR’s, just to run them to ground, and found out that to make them work, I needed to make changes to my context file and more important, to $IAS_HOME

You may or may not be familiar with the authorized way of customizing a template in 11i.  Back in the good old days, you just marked up the config file to what you needed, and hoped autoconfig didn’t access it.  Sounds crazy, but in autoconfig’s infancy, in most cases, it didn’t, or possibly you just didn’t use/trust autoconfig because it was just as likely to break your 11i environment as fix it.  Nowadays, autoconfig is usually right and fairly indispensable.  The proper and supported method for customizing a config file is via custom templates, and the method is outlined in note 270519.1 “Customizing an AutoConfig Environment”.  Really, if you’re not familiar with it, I strongly recommend reading it.

On to the problems

  1. As a part of installing MSCA, I tried to log into the administrative pages from 11i.  When logging in as the MWA Server Manager responsibility, I then selected Monitor Server.  It took me to the next page, where it complained with the error Unable to obtain MWA_TOP. Please makesure to set the JVM parameter “oracle.apps.mwa”.    This was a known issue under 11i, as spelled out in 415945.1 (or 782162.1 for R12).  Hey this should be no sweat then, right?  I mean, it’s a known (if not prolific) problem, having survived even a migration to R12.   MSCA has been around for years, and 11i is under Extended Support per this document, which specifically says it includes “Updates, fixes, security alerts, data fixes, and critical patch updates”.  I would say this is a fix, right, as autoconfig breaks their product from running, right out of the box?  Nope.
  2. Three values in the context file are wrong, out of the box.  Oracle doesn’t recognize dashes “-” or commas “,” for these values <mwaPortNo oa_var=”s_mwaPortNo” oa_type=”PORT”>10200-10205</mwaPortNo> or <mwaTelnetPortNo oa_var=”s_mwaTelnetPortNo” oa_type=”PORT”>10260,10262,10264</mwaTelnetPortNo> or <mwaDispatcherPort oa_var=”s_mwaDispatcherPort” oa_type=”PORT”>10300-10303</mwaDispatcherPort>.  The values 10200-10205 and 10300-10303 in my case, should actually be 10200;10202;10204 and 10300 respectively.  Where did these dashed/comma values come from?  Beats me.  I guess I could go digging, but the truth is, that’s not my job.  Support didn’t seem to care, at all, that their default values were wrong (which is the only reason I opened the SR), only giving me the fix (change the values).  My concern here wasn’t to nitpick.  It was that I’d do something like update to the latest autoconfig template patch and they’d break again.  Or that perhaps Oracle didn’t actually mean to deliver this product wrong out of the box, and might be interested to learn that somewhere the ports were being inserted wrong.  Regardless, the analyst simply wanted to get the ticket closed.

A few SRs later…

The fix for the first issue boils down to a bad template, or at least a template which needs to be updated.  I thought this odd, since the actual document explaining the process is titled Customizing an AutoConfig Environment.  To summarize, in order to get the web based MWA server management right, you have to make a customization to the template.  This just doesn’t sit right with me, since I’m still under support.  Isn’t this a bug?  Won’t they fix this in the next autoconfig rollup patch?  Well here’s the details of the fix again, in case you need it:

mkdir $FND_TOP/admin/template/custom

cp $FND_TOP/admin/template/ $FND_TOP/admin/template/custom

edit the new file in /custom, add this line:
wrapper.bin.parameters=-Doracle.apps.mwa=<full path to your $MWA_TOP, such as /u01/app/mwa/11.5.0

restart Apache

Create a free website or blog at