Oracle 12.2 – How to rotate the 12.2 listener log (DIAG_ADR_ENABLED_LISTENER = OFF) – Follow Up

By William Sescu

My blog about listener rotation caused some discussion, which is great. 🙂 It sounds like an easy stuff, but in case of listener logging it isn’t.

https://www.dbi-services.com/blog/oracle-12-2-how-to-rotate-the-12-2-listener-log-diag_adr_enabled_listener-off/

Many ways do exist to rotate the listener log, but I was trying to point out some issues, because there are a few, e.g.

1. What happens when the log file reaches 4G?
2. What are the performance implications?
3. What happens if I move the listener.log while the listener is running (Open file descriptor)?
4. And how to rotate the listener log with minimal impact?

The first two points have been discussed already in the previous post, so, I’m not going deeper into those one’s. Let’s take a look at the other ones, and start with the file descriptor issue. In the output below, you can see that the listener has an open file descriptor “3w” which is pointing to “/u01/app/oracle/network/log/listener.log”. It can be quite easily identified by using the lsof utility (list open files).

oracle@dbidg01:/u01/app/oracle/ [DBIT122] ps -ef | grep tnslsnr | grep -v grep
oracle    4686     1  0 07:59 ?        00:00:00 /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit

oracle@dbidg01:/u01/app/oracle/ [DBIT122] lsof -p 4686 | grep listener.log
COMMAND  PID   USER   FD      TYPE             DEVICE SIZE/OFF      NODE NAME
tnslsnr 4686 oracle    3w      REG              251,4    55239 141607653 /u01/app/oracle/network/log/listener.log

So .. what happens if I move the listener log, while the listener is running.

oracle@dbidg01:/u01/app/oracle/network/log/ [DBIT122] mv listener.log listener.log.1
oracle@dbidg01:/u01/app/oracle/network/log/ [DBIT122]

And to have some data, I’m doing some sqlplus sessions here to generate more log entries …

oracle@dbidg01:/u01/app/oracle/network/log/ [DBIT122] sqlplus hr/hr@DBIT122_SITE1
... 

oracle@dbidg01:/u01/app/oracle/network/log/ [DBIT122] sqlplus hr/hr@DBIT122_SITE1
... 

oracle@dbidg01:/u01/app/oracle/network/log/ [DBIT122] ls -l listener.log
ls: cannot access listener.log: No such file or directory

oracle@dbidg01:/u01/app/oracle/network/log/ [DBIT122] lsof -p 4686 | grep listener.log
COMMAND  PID   USER   FD      TYPE             DEVICE SIZE/OFF      NODE NAME
tnslsnr 4686 oracle    3w      REG              251,4    56143 141607653 /u01/app/oracle/network/log/listener.log.1

As you can see. A new listener.log is not automatically created, and the file descriptor “3w” is pointing now to /u01/app/oracle/network/log/listener.log.1.

That’s why it is not a good idea to move the listener log, without stopping the listener logging first. And please don’t try to send a hang up signal to the listener. The listener does not understand the hang up signal (kill -HUP) and you would kill it immediately. 😉

Ok. Let’s get back to the question about how to rotate the listener log with minimal impact?

If you have activated the listener logging, then there is a reason for doing that and you don’t want to lose data out of that log file. In case that losing any listener log data is not acceptable, you have to stop the listener, rotate the log and start the listener. There is no way out of that. Of course it has the disadvantage that new sessions cannot be established during that time.

However, if you want the listener to be up and running, and rotate the listener log with an absolute minimum of log data loss (taking here about milliseconds), then I would use the approach which I have described already in my previous post.

I have written quickly a listener rotate script which demonstrates how it can look like. I know that the script is not baby save, and a lot of extra checks and tweaks can be built in, but you will get an idea how it can look like. The script takes two parameters. The first one is the listener name, and the second one is the number of days about how long the listener logs should be kept on disk. Everything older than that will be removed.

oracle@dbidg01:/home/oracle/ [DBIT122] ./rotate_listener.sh LISTENER 7
INFO: Check if Listener LISTENER is running
INFO: Start Listener Rotating for LISTENER
INFO: Current Listener Logging for LISTENER is: ON
INFO: Tuning Listener Logging for LISTENER OFF
INFO: Current Listener Logging for LISTENER is: OFF
INFO: Rotating Listener Log /u01/app/oracle/network/log/listener.log to /u01/app/oracle/network/log/listener.log.1497363899
INFO: Turning on Listener Logging for LISTENER
INFO: Current Listener Logging for LISTENER is: ON
INFO: Rotated successfully Listener Log for LISTENER
INFO: Starting cleanup of old listener log files
INFO: Will delete the following log files
/u01/app/oracle/network/log/listener.log.1497354123
/u01/app/oracle/network/log/listener.log.1497354122
/u01/app/oracle/network/log/listener.log.1497354121
INFO: Finished cleanup

And here are the contents of the script.

oracle@dbidg01:/home/oracle/ [DBIT122] cat rotate_listener.sh
#!/bin/bash
#
#-- Listener Name => parameter 1
#-- Delete listener log files older than number of days => parameter 2
#

#-- set -x

ListenerName=$1
NumberOfDays=$2
#-- %s  seconds since 1970-01-01 00:00:00 UTC
Date=`date +%s`

#-- Check if variable $1 and $2 are empty
if [ -z ${ListenerName} ]; then
        echo ""
        echo "INFO: Please specify the listener name"
        echo "INFO: e.g. ./rotate_listener.sh LISTENER 7"
        echo ""
        exit
fi

if [ -z ${NumberOfDays} ]; then
        echo ""
        echo "INFO: Please specify the number of days"
        echo "INFO: e.g. ./rotate_listener.sh LISTENER 7"
        echo ""
        exit
fi

echo "INFO: Check if Listener ${ListenerName} is running"

ps -ef | grep "tnslsnr ${ListenerName} -inherit" | grep -v grep >/dev/null 2>&1
if [ $? != 0 ]; then
        echo "INFO: Listener ${ListenerName} is not running ... will exit here"
        exit
fi

#-- Set the listener log file
ListenerLogFile=`lsnrctl status ${ListenerName} | grep "Listener Log File" | awk '{ print $4 }'`


echo "INFO: Start Listener Rotating for ${ListenerName}"

#-- Check listener log status
ListenerLogStatus=`lsnrctl <<-EOF  | grep log_status | awk '{ print $6 }'
set displaymode normal
set current_listener ${ListenerName}
show log_status
EOF`

if [ ${ListenerLogStatus} = "ON" ]; then
echo "INFO: Current Listener Logging for ${ListenerName} is: ${ListenerLogStatus}"
echo "INFO: Tuning Listener Logging for ${ListenerName} OFF"

ListenerLogStatus=`lsnrctl <<-EOF | grep log_status | awk '{ print $6 }'
set displaymode normal
set current_listener ${ListenerName}
set log_status off
EOF`

echo "INFO: Current Listener Logging for ${ListenerName} is: ${ListenerLogStatus}"
 if [ ${ListenerLogStatus} = "OFF" ]; then
   echo "INFO: Rotating Listener Log ${ListenerLogFile} to ${ListenerLogFile}.${Date}"
        mv ${ListenerLogFile} ${ListenerLogFile}.${Date}
    echo "INFO: Turning on Listener Logging for ${ListenerName}"

ListenerLogStatus=`lsnrctl <<-EOF | grep log_status | awk '{ print $6 }'
set displaymode normal
set current_listener ${ListenerName}
set log_status on
EOF`

echo "INFO: Current Listener Logging for ${ListenerName} is: ${ListenerLogStatus}"
echo "INFO: Rotated successfully Listener Log for ${ListenerName}"
 fi
fi

echo "INFO: Starting cleanup of old listener log files"
echo "INFO: Will delete the following log files"
ListenerLogDirectory=`dirname $ListenerLogFile`
find ${ListenerLogDirectory} -name "listener.log.*" -mtime +${2} -print
find ${ListenerLogDirectory} -name "listener.log.*" -mtime +${2} -print | xargs rm -f

echo "INFO: Finished cleanup"

#-- EOF

Conclusion

If you run that rotate listener log script during a time (e.g. in the middle of night), where you expect minimal activity on the DB, you can minimize the chance of losing listener log entries even further.