Saturday, June 27, 2009

Truncating, Rotating, Flushing Listener.log file

Listener Log file audits trail information which enables us to analyze network statistics, client connection requests, service registration events or other stuff. So it is obvious that listener log can contain very useful information for troubleshooting network related problems.

This file is usually small and easy to diagnose but in cases of very active databases it could grew very fast to several gigabytes. We have listener.log file which is growing very fast because of very active OLTP database. After few months that file grew up to 4 GB and there stopped updating new logs.

I asked on OTN forums why it stopped on 4 GB's and here is the link to that question:
http://forums.oracle.com/forums/thread.jspa?threadID=921004&tstart=0

In this post I want to show you how to truncate listener log file and free some disk space doing that.

You could stop listener service process, move or remove listener.log file and start service again. But what if your Oracle database is running 24/7 and you cannot easily stop listener service. Then you cannot truncate listener.log file using mv or rm commands because Oracle will continue writing to that file and will not free up disk space.


First solution:




You could also use [ $echo "" > listener.log ] to truncate file.


Second solution - using LSNRCTL utility:





Oracle 11g introduced new diagnostically system - "Automatic Diagnostic Repository (ADR)" and now we have listener log written to xml file (besides listener.log file).
Trying to change log file using LSNRCTL utility will fail with:

TNS-01251: Cannot set trace/log directory under ADR.

LSRNCTL>set current_listener
LSNRCTL>set log_directory fails:

TNS-01251: Cannot set trace/log directory under ADR

For solution find more information on Metalink - note 762787.1.


Third solution - using LOGROTATE command:

We can use logrotate command in cron to rotate listener logs in some time intervals. Logrotate is designed to help administrators with managing large numbers of log files. It allows automatic rotation, compression, removal, and mailing of log files.

Simple logrotate configuration:

Add file:
/etc/logrotate.d/oracle

- write this lines into that file:
/oracle/product/10.2.0/network/log/listener.log {
missingok
weekly
rotate 4
compress
create 644 oracle dba
}


Logs will rotate every weak and old ones will be compressed. We will have 4 latest backups.


Fourth solution - using LSNRCTL utility #2

Oracle TNS listener holds an open handle to listener.log file so I can't just rename file because Oracle won't create new log file for logging purposes. To recreate log file I must restart TNS listener process. Better option is to disable logging to release open handle, rename file and enable logging.





Fifth solution - custom script:

trunc_lsnr.sh




Execute this script as a cron job in time intervals based on your needs.

20 comments:

  1. Hello!

    My name is Jung Yun Han.

    I've just read this post and it's

    absolutely useful to me.

    Thanks~

    ReplyDelete
  2. You're welcome Jung ;)

    Best regards,
    Marko

    ReplyDelete
  3. Can I ask a small question : If we do the Second solution - using LSNRCTL utility, will it impact to any Oracle process are running on the system or not ?
    Sorry, because i'm the newcomer with Oracle database, but really need to solve this problems !

    ReplyDelete
  4. Hi Jack,

    smart thing to do is to test everything before playing in production environment.

    btw, this action shouldn't impact on running Oracle processes...

    but test, test, test ;)

    ReplyDelete
  5. thanks - coming up first when googling "how to rotate listener logs in oracle" - hello from NZ

    ReplyDelete
  6. in windows 11g you can just rename the file and oralce will create a new file. Then delete the old file. :)

    ReplyDelete
  7. I have not tested flushing listener.log in Windows environment.

    Thanks for comment ;)

    Regards,
    Marko

    ReplyDelete
  8. Solution 1 works fine for me on unix systems.

    Thanks !

    ReplyDelete
  9. Hi I found your post really helpful. =)

    ReplyDelete
  10. excellent post, I found it very useful - thanks

    ReplyDelete
  11. Yes thank you for the information had unable to connect to the oracle listener and to this log file hitting this 4gb threshold. We renamed the file and boom able to connect. Now looking for the maintenance to keep it under control.

    I can also confirm that in windows it is possible to rename the log without restarting the listener service (if your in between writes to the log of course)

    ReplyDelete
  12. Hi, I hate so much Oracle, it does not work for me.. set log_file doesn't work

    LSNRCTL> set log_file listener_tmp.log
    TNS-12508: TNS:listener could not resolve the COMMAND given

    ReplyDelete
  13. Hello Peter,

    do you have "ADMIN_RESTRICTIONS_LISTENER = ON" parameter enabled in listener.ora file?

    If yes, disable it and you'll be able to execute admin commands like set_log.

    Regards,
    Marko

    ReplyDelete
  14. Brilliant post! Hats off :)

    ReplyDelete
  15. Here is my low tech solution (for Linux) that retains 500000 lines of the original file:

    tail -n 500000 listener.log >lastbit.log
    cat lastbit.log >listener.log
    rm lastbit.log

    In my case the file went down from 4G to 68Mb.

    ReplyDelete
  16. Hello Steve,

    you're solution also solves this problem.

    Now create script and automate this task - you won't have to worry about listener.log any more ;-)

    Regards,
    Marko

    ReplyDelete
  17. FYI, I compressed the listener.log and it went down from 1 gb to 60 Mb... so it's a nice solution. thanks.

    ReplyDelete
  18. Thanks for Sharing this, very useful , especially all possible solution in one post :)

    ReplyDelete