Archive for August, 2009

TOD – Keep Listening, Stop Logging

Every so often I get a call from a client because their database just up and stopped.  Generally that means only one of two things, either the LOG_ARCHIVE_DEST is full and the database(s) can no longer write out their deltas or the filesystem which contains the $ORACLE_HOME for said database is full.   In either case  Oracle will quiesce each database hosted from that home and no changes except startup and shutdown may be made to said database.

When the latter happens,   the first place I look is for log files, but not just any log files.  The alert log can grow rapidly, but it doesnt compare to the listener.log (or listener_<hostname>.log).  Since this logfile is a record of every SQL*Net connection to the database, on busy systems it can and does fill up rapidly.  Most systems no longer have a 2GB limit, thus the log can reach huge sizes and fill a filesystem before you know it.

What can be done?  You can rotate the log in some fashion or clear it (>listener.log will clear a file without removing it).  If deleted or moved it will recreate itself on the next write.  But what if you don’t care about logging all that information?  If it isn’t needed for auditing or tracing, you can shut it off by adding the following line to your listener.ora file:

LOGGING_LISTENER = OFF
or
LOGGING_LISTENER_<hostname>=OFF (if RAC or named listeners are used)

and then reload the listener, no need for a restart here.  At once logging will stop and all the free space on your filesystem will celebrate the fact that it will not be soon gobbled up by a rapidly growing listener.log.  If you determine that it is needed, you can remove the line (the default is to log) or set the value to ON and reload the listener.

2 Comments