Monday, May 24, 2010

Rotate listener log (TNS-12547, TNS-12560, TNS-00517)

The problem

On many 32 bit OS platforms (Linux and Windows as well) you may face error after starting listener.
TNS-12547:TNS:lost contact
TNS-12560:TNS:protocol adapter error
TNS-00517:lost contact
Intel SVR4 UNIX Error: 32: Broken pipe
The main cause of error like this is 2 GB file limitation which listener.log has succeeded and cannot grow any longer.

The solution

Solution is to rotate listener log. For that i use very easy but useful bash script.
#!/bin/bash

# Works for all Oracle databases pre-11g 
# 11g have changes in a way that introduce universal "diagnostic_dest" parameter
# which is not cover in this solution and will produce an error 'Directory not found'

function quit_with_message {
  echo "Usage: ${0} LSNR";
  echo "       where LSNR is full name of listener name in current ${ORACLE_HOME} home" 
  echo "Information:"  
  echo "       'LSNR' value is case insensitive!"
  echo "  "
  exit 1
  exit;
} 

# Make Sure Listener name is passed
if [ -z "${1}" ]; then 
  quit_with_message;
fi; 

# Set the Environment
DATUM=`date '+%Y%m%d'`; export DATUM
VRIJEME=`date '+%H%M%S'`;export VRIJEME
export NOW=${DATUM}_${VRIJEME};

# We need upper and lower case value of listener name
LSNR=`echo ${1} | tr '[:lower:]' '[:upper:]'`
lsnr=`echo ${1} | tr '[:upper:]' '[:lower:]'`

date ; 

if [ -d ${ORACLE_HOME}/network/log ]; then
  if [ -f ${ORACLE_HOME}/network/log/${lsnr}.log ]; then
    ${ORACLE_HOME}/bin/lsnrctl << EOF!
    set current_listener ${LSNR}
    set log_file temp.log
EOF!
    cat ${ORACLE_HOME}/network/log/${lsnr}.log >> ${ORACLE_HOME}/network/log/${lsnr}.log.$NOW ;    

    rm ${ORACLE_HOME}/network/log/${lsnr}.log ;

    ${ORACLE_HOME}/bin/lsnrctl << EOF!
    set current_listener ${LSNR}
    set log_file ${lsnr}.log
EOF!

# add content which has been logged in the meantime
    cat ${ORACLE_HOME}/network/log/temp.log >> $ORACLE_HOME/network/log/${lsnr}.log.${NOW} ;
    rm ${ORACLE_HOME}/network/log/temp.log ;
  else
    echo "File ${ORACLE_HOME}/network/log/${lsnr}.log not found...";
    echo "  "
    quit_with_message ;
  fi ;
else
  echo "Directory ${ORACLE_HOME}/network/log not found...";
  echo "  "
  quit_with_message ;
fi ;

date
Run this script periodically (through crontab in period that suit your log size) or on demand.

As you can see in header comment, this script is for listeners configuration that rely on (and included) 10 version. To get listener version perform with set environement:
[oracle HACT2@iishacrac2 ~]$ lsnrctl version

LSNRCTL for Linux IA64: Version 10.2.0.4.0 - Production on 24-MAY-2010 15:05:46

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNSLSNR for Linux IA64: Version 10.2.0.4.0 - Production
        TNS for Linux IA64: Version 10.2.0.4.0 - Production
        Unix Domain Socket IPC NT Protocol Adaptor for Linux IA64: Version 10.2.0.4.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux IA64: Version 10.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for Linux IA64: Version 10.2.0.4.0 - Production,,
The command completed successfully
[oracle HACT2@iishacrac2 ~]$

The end

When I said "on demand" usage, I though situation when you need to test results of new listener configuration. This change is for instance, when you involve TAF, LB or secure listener and want to monitor new changes. For such an cases it is really good habit to separate logs for future comparation.

Because "You asked for it" I have added Windows version with same functionality. Hope now is all covered.

Cheers!

4 comments :

  1. Hi!
    Is there something for Windows version? All of you place Linux based DBA administration...
    Gorgy

    ReplyDelete
  2. Hi Damir,

    nice script for rotating listener log.

    I've wrote script for similar purpose when my log file grew to 4G (64-bit OS) and stopped logging.

    Regards,
    Marko

    ReplyDelete
  3. Marko,

    THX, will try to make Windows version as well...
    ;-)

    ReplyDelete
  4. Sorry for my bad english. Thank you so much for your good post. Your post helped me in my college assignment, If you can provide me more details please email me.

    ReplyDelete

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign