Oracle-Listener log解读-白红宇

Oracle-Listener log解读

阅读量：4208 次

发布时间：2019-05-26

本文共 14134 字，大约阅读时间需要 47 分钟。

在ORACLE数据库中，如果不对监听日志文件（listener.log）进行截断,那么监听日志文件（listener.log）会变得越来越大.

Listener log location

For oracle 9i/10g

在下面的目录下：

$ORACLE_HOME/network/log/listener_$ORACLE_SID.log

For oracle 11g/12c

在下面的目录下：

$ORACLE_BASE/diag/tnslsnr/主机名称/listener/trace/listener.log

这里写图片描述

或者通过 lsnrctl status 也可以查看位置

这里写图片描述

这里展示的是 xml格式的日志，跟.log并无区别。

或者11g可以通过 adrci 命令

oracle@entel2:[/oracle]$adrciADRCI: Release 11.2.0.4.0 - Production on Wed Nov 30 20:56:28 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.ADR base = "/oracle"adrci> help  --help可以看帮助命令。输入help show alert，可以看到show alert的详细用法 HELP [topic]   Available Topics:        CREATE REPORT        ECHO        EXIT        HELP        HOST        IPS        PURGE        RUN        SET BASE        SET BROWSER        SET CONTROL        SET ECHO        SET EDITOR        SET HOMES | HOME | HOMEPATH        SET TERMOUT        SHOW ALERT        SHOW BASE        SHOW CONTROL        SHOW HM_RUN        SHOW HOMES | HOME | HOMEPATH        SHOW INCDIR        SHOW INCIDENT        SHOW PROBLEM        SHOW REPORT        SHOW TRACEFILE        SPOOL There are other commands intended to be used directly by Oracle, type "HELP EXTENDED" to see the listadrci> show alert  --显示alert信息Choose the alert log from the following homes to view:1: diag/clients/user_oracle/host_880756540_802: diag/tnslsnr/procsdb2/listener_cc3: diag/tnslsnr/entel2/sid_list_listener4: diag/tnslsnr/entel2/listener_rb5: diag/tnslsnr/entel2/listener6: diag/tnslsnr/entel2/listener_cc7: diag/tnslsnr/procsdb1/listener_rb8: diag/rdbms/ccdg/ccdg9: diag/rdbms/rb/rb10: diag/rdbms/cc/ccQ: to quitPlease select option: 5 --输入数字，查看对应日志Output the results to file: /tmp/alert_13187_1397_listener_3.ado2016-06-27 09:15:45.164000 -04:00Create Relation ADR_CONTROLCreate Relation ADR_INVALIDATIONCreate Relation INC_METER_IMPT_DEF2016-06-27 09:15:46.444000 -04:00Create Relation INC_METER_PK_IMPTSSystem parameter file is /oracle/product/112/network/admin/listener.oraLog messages written to /oracle/diag/tnslsnr/entel2/listener/alert/log.xmlTrace information written to /oracle/diag/tnslsnr/entel2/listener/trace/ora_16175_140656975550208.trcTrace level is currently 0Started with pid=16175Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.45.7.198)(PORT=1521)))Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.123.1)(PORT=1521)))Listener completed notification to CRS on start..................

Listener log日志文件清理

需要对监听日志文件（listener.log）进行定期清理。

1：监听日志文件（listener.log）变得越来越大，占用额外的存储空间

2：监听日志文件（listener.log）变得太大会带来一些问题，查找起来也相当麻烦

3：监听日志文件（listener.log）变得太大，给写入、查看带来的一些性能问题、麻烦

定期对监听日志文件（listener.log）进行清理，另外一种说法叫截断日志文件。

列举一个错误的做法

oracle@entel2:[/oracle]$mv listener.log listener.log.20161201oracle@entel2:[/oracle]$cp /dev/null listener.logoracle@entel2:[/oracle]$more listener.log

如上所示，这样截断监听日志（listener.log）后，监听服务进程（tnslsnr）并不会将新的监听信息写入listener.log，而是继续写入listener.log.20161201

正确的做法

1：首先停止监听服务进程（tnslsnr）记录日志。

oracle@entel2:[/oracle]$lsnrctl  set log_status off

2：将监听日志文件（listener.log）复制一份，以listener.log.yyyymmdd格式命名

oracle@entel2:[/oracle]$cp listener.log listener.log.20161201

3：将监听日志文件（listener.log）清空。清空文件的方法有很多

oracle@entel2:[/oracle]$echo “” > listener.log或者oracle@entel2:[/oracle]$cp /dev/null listener.log或者oracle@entel2:[/oracle]$echo /dev/null > listener.log或者oracle@entel2:[/oracle]$>listener.log

4：开启监听服务进程（tnslsnr）记录日志

oracle@entel2:[/oracle]$lsnrctl set log_status on

当然也可以移走监听日志文件（listener.log），数据库实例会自动创建一个listener.log文件。

oracle@entel2:[/oracle]$ lsnrctl set log_status offoracle@entel2:[/oracle]$mv listener.log listener.yyyymmddoracle@entel2:[/oracle]$lsnrctl set log_status on

清理shell脚本

当然这些操作应该通过shell脚本来处理，然后结合crontab作业定期清理、截断监听日志文件。

简单一点的（核心部分）

rq=` date +"%d" `cp $ORACLE_HOME/network/log/listener.log $ORACLE_BACKUP/network/log/listener_$rq.logsu - oracle -c "lsnrctl set log_status off"cp /dev/null $ORACLE_HOME/network/log/listener.logsu - oracle -c "lsnrctl set log_status on"

这样的脚本还没有解决一个问题，就是截断的监听日志文件保留多久的问题。比如我只想保留这些截断的监听日志一个月时间，我希望作业自动维护。不需要我去手工操作。有这样一个脚本cls_oracle.sh可以完全做到这个，当然它还会归档、清理其它日志文件，例如告警文件（alert_sid.log）等等。功能非常强大。

#!/bin/bash## Script used to cleanup any Oracle environment.## Cleans:      audit_log_dest#              background_dump_dest#              core_dump_dest#              user_dump_dest## Rotates:     Alert Logs#              Listener Logs## Scheduling:  00 00 * * * /home/oracle/_cron/cls_oracle/cls_oracle.sh -d 31 > /home/oracle/_cron/cls_oracle/cls_oracle.log 2>&1## Created By:  Tommy Wang  2012-09-10## History: #RM="rm -f"RMDIR="rm -rf"LS="ls -l"MV="mv"TOUCH="touch"TESTTOUCH="echo touch"TESTMV="echo mv"TESTRM=$LSTESTRMDIR=$LSSUCCESS=0FAILURE=1TEST=0HOSTNAME=`hostname`ORAENV="oraenv"TODAY=`date +%Y%m%d`ORIGPATH=/usr/local/bin:$PATHORIGLD=$LD_LIBRARY_PATHexport PATH=$ORIGPATH# Usage function.f_usage(){  echo "Usage: `basename $0` -d DAYS [-a DAYS] [-b DAYS] [-c DAYS] [-n DAYS] [-r DAYS] [-u DAYS] [-t] [-h]"  echo "       -d = Mandatory default number of days to keep log files that are not explicitly passed as parameters."  echo "       -a = Optional number of days to keep audit logs."  echo "       -b = Optional number of days to keep background dumps."  echo "       -c = Optional number of days to keep core dumps."  echo "       -n = Optional number of days to keep network log files."  echo "       -r = Optional number of days to keep clusterware log files."  echo "       -u = Optional number of days to keep user dumps."  echo "       -h = Optional help mode."  echo "       -t = Optional test mode. Does not delete any files."}if [ $# -lt 1 ]; then  f_usage  exit $FAILUREfi# Function used to check the validity of days.f_checkdays(){  if [ $1 -lt 1 ]; then    echo "ERROR: Number of days is invalid."    exit $FAILURE  fi  if [ $? -ne 0 ]; then    echo "ERROR: Number of days is invalid."    exit $FAILURE  fi} # Function used to cut log files.f_cutlog(){  # Set name of log file.  LOG_FILE=$1  CUT_FILE=${LOG_FILE}.${TODAY}  FILESIZE=`ls -l $LOG_FILE | awk '{print $5}'`  # Cut the log file if it has not been cut today.  if [ -f $CUT_FILE ]; then    echo "Log Already Cut Today: $CUT_FILE"  elif [ ! -f $LOG_FILE ]; then    echo "Log File Does Not Exist: $LOG_FILE"  elif [ $FILESIZE -eq 0 ]; then    echo "Log File Has Zero Size: $LOG_FILE"  else    # Cut file.    echo "Cutting Log File: $LOG_FILE"    $MV $LOG_FILE $CUT_FILE    $TOUCH $LOG_FILE  fi}# Function used to delete log files.f_deletelog(){  # Set name of log file.  CLEAN_LOG=$1  # Set time limit and confirm it is valid.  CLEAN_DAYS=$2  f_checkdays $CLEAN_DAYS  # Delete old log files if they exist.  find $CLEAN_LOG.[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] -type f -mtime +$CLEAN_DAYS -exec $RM {} \; 2>/dev/null}# Function used to get database parameter values.f_getparameter(){  if [ -z "$1" ]; then    return  fi  PARAMETER=$1  sqlplus -s /nolog <
   
    /dev/null ]; then      echo "  Audit Dump Dest: $ADUMPDEST"      ADUMPDIRS="$ADUMPDIRS $ADUMPDEST"    fi    # Get the background_dump_dest.    BDUMPDEST=`f_getparameter background_dump_dest`    echo "  Background Dump Dest: $BDUMPDEST"    if [ ! -z "$BDUMPDEST" ] && [ -d "$BDUMPDEST" ]; then      BDUMPDIRS="$BDUMPDIRS $BDUMPDEST"    fi    # Get the core_dump_dest.    CDUMPDEST=`f_getparameter core_dump_dest`    echo "  Core Dump Dest: $CDUMPDEST"    if [ ! -z "$CDUMPDEST" ] && [ -d "$CDUMPDEST" ]; then      CDUMPDIRS="$CDUMPDIRS $CDUMPDEST"    fi    # Get the user_dump_dest.    UDUMPDEST=`f_getparameter user_dump_dest`    echo "  User Dump Dest: $UDUMPDEST"    if [ ! -z "$UDUMPDEST" ] && [ -d "$UDUMPDEST" ]; then      UDUMPDIRS="$UDUMPDIRS $UDUMPDEST"    fi  fidone# Do cleanup for each Oracle Home.for ORAHOME in `f_getuniq "$OH"`do  # Get the standard audit directory if present.  if [ -d $ORAHOME/rdbms/audit ]; then     ADUMPDIRS="$ADUMPDIRS $ORAHOME/rdbms/audit"  fi  # Get the Cluster Ready Services Daemon (crsd) log directory if present.  if [ -d $ORAHOME/log/$HOSTNAME/crsd ]; then    CRSLOGDIRS="$CRSLOGDIRS $ORAHOME/log/$HOSTNAME/crsd"  fi  # Get the  Oracle Cluster Registry (OCR) log directory if present.  if [ -d $ORAHOME/log/$HOSTNAME/client ]; then    OCRLOGDIRS="$OCRLOGDIRS $ORAHOME/log/$HOSTNAME/client"  fi  # Get the Cluster Synchronization Services (CSS) log directory if present.  if [ -d $ORAHOME/log/$HOSTNAME/cssd ]; then    CSSLOGDIRS="$CSSLOGDIRS $ORAHOME/log/$HOSTNAME/cssd"  fi  # Get the Event Manager (EVM) log directory if present.  if [ -d $ORAHOME/log/$HOSTNAME/evmd ]; then    EVMLOGDIRS="$EVMLOGDIRS $ORAHOME/log/$HOSTNAME/evmd"  fi  # Get the RACG log directory if present.  if [ -d $ORAHOME/log/$HOSTNAME/racg ]; then    RACGLOGDIRS="$RACGLOGDIRS $ORAHOME/log/$HOSTNAME/racg"  fidone# Clean the audit_dump_dest directories.if [ ! -z "$ADUMPDIRS" ]; then  for DIR in `f_getuniq "$ADUMPDIRS"`; do    if [ -d $DIR ]; then      echo "Cleaning Audit Dump Directory: $DIR"      find $DIR -type f -name "*.aud" -mtime +$ADAYS -exec $RM {} \; 2>/dev/null    fi  donefi# Clean the background_dump_dest directories.if [ ! -z "$BDUMPDIRS" ]; then  for DIR in `f_getuniq "$BDUMPDIRS"`; do    if [ -d $DIR ]; then      echo "Cleaning Background Dump Destination Directory: $DIR"      # Clean up old trace files.      find $DIR -type f -name "*.tr[c,m]" -mtime +$BDAYS -exec $RM {} \; 2>/dev/null      find $DIR -type d -name "cdmp*" -mtime +$BDAYS -exec $RMDIR {} \; 2>/dev/null    fi    if [ -d $DIR ]; then      # Cut the alert log and clean old ones.      for f in `find $DIR -type f -name "alert\_*.log" ! -name "alert_[0-9A-Z]*.[0-9]*.log" 2>/dev/null`; do        echo "Alert Log: $f"        f_cutlog $f        f_deletelog $f $BDAYS      done    fi  donefi# Clean the core_dump_dest directories.if [ ! -z "$CDUMPDIRS" ]; then  for DIR in `f_getuniq "$CDUMPDIRS"`; do    if [ -d $DIR ]; then      echo "Cleaning Core Dump Destination: $DIR"      find $DIR -type d -name "core*" -mtime +$CDAYS -exec $RMDIR {} \; 2>/dev/null    fi  donefi# Clean the user_dump_dest directories.if [ ! -z "$UDUMPDIRS" ]; then  for DIR in `f_getuniq "$UDUMPDIRS"`; do    if [ -d $DIR ]; then      echo "Cleaning User Dump Destination: $DIR"      find $DIR -type f -name "*.trc" -mtime +$UDAYS -exec $RM {} \; 2>/dev/null    fi  donefi# Cluster Ready Services Daemon (crsd) Log Filesfor DIR in `f_getuniq "$CRSLOGDIRS $OCRLOGDIRS $CSSLOGDIRS $EVMLOGDIRS $RACGLOGDIRS"`; do  if [ -d $DIR ]; then    echo "Cleaning Clusterware Directory: $DIR"    find $DIR -type f -name "*.log" -mtime +$RDAYS -exec $RM {} \; 2>/dev/null  fidone# Clean Listener Log Files.# Get the list of running listeners. It is assumed that if the listener is not running, the log file does not need to be cut.ps -e -o args | grep tnslsnr | grep -v grep | while read LSNR; do  # Derive the lsnrctl path from the tnslsnr process path.  TNSLSNR=`echo $LSNR | awk '{print $1}'`  ORACLE_PATH=`dirname $TNSLSNR`  ORACLE_HOME=`dirname $ORACLE_PATH`  PATH=$ORACLE_PATH:$ORIGPATH  LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORIGLD  LSNRCTL=$ORACLE_PATH/lsnrctl  echo "Listener Control Command: $LSNRCTL"  # Derive the listener name from the running process.  LSNRNAME=`echo $LSNR | awk '{print $2}' | tr "[:upper:]" "[:lower:]"`  echo "Listener Name: $LSNRNAME"  # Get the listener version.  LSNRVER=`$LSNRCTL version | grep "LSNRCTL" | grep "Version" | awk '{print $5}' | awk -F. '{print $1}'`  echo "Listener Version: $LSNRVER"  # Get the TNS_ADMIN variable.  echo "Initial TNS_ADMIN: $TNS_ADMIN"  unset TNS_ADMIN  TNS_ADMIN=`$LSNRCTL status $LSNRNAME | grep "Listener Parameter File" | awk '{print $4}'`  if [ ! -z $TNS_ADMIN ]; then    export TNS_ADMIN=`dirname $TNS_ADMIN`  else    export TNS_ADMIN=$ORACLE_HOME/network/admin  fi  echo "Network Admin Directory: $TNS_ADMIN"  # If the listener is 11g, get the diagnostic dest, etc...  if [ $LSNRVER -ge 11 ]; then    # Get the listener log file directory.     LSNRDIAG=`$LSNRCTL<
    
     /dev/null    fi    # Disable logging.    $LSNRCTL <

在crontab中设置一个作业，每天晚上凌晨零点运行这个脚本，日志文件保留31天。

00 00 * * * /home/oracle/_cron/cls_oracle/cls_oracle.sh -d 31 > /home/oracle/_cron/cls_oracle/cls_oracle.sh.log 2>&1

你可能感兴趣的文章