Sqlnet tracing during nightly hours…

A TNS error at night…

Sometime ago my data warehouse colleague came to me with a TNS error. At night times he runs his batch jobs in order to update his data warehouse. That night one of his jobs did not run properly and generated an ORA-12592 error. He had to restart this job during daytime.

It turned out it was not the only occurrence of this TNS error. A couple of days later he again came to me with similar TNS errors which were generated at a similar time. I looked in the alert.log and in the listener.log but nothing could be found. Therefore I decided to switch on sqlnet tracing in order to find out what was happening. However sqlnet tracing generates a lot of data. The TNS errors were generated at night. It is not a good idea to switch on sqlnet tracing during daytimes and then come back the next day and switch it off. You will probably get disk full problems!

Therefore I decided to make some scripts. Using crontab or windows scheduler I switch on sqlnet and listener tracing some time before the TNS error normally occurs and switch it off some time after. I would like to share with you the way I did it.

My configuration to trace.

We run an oracle 11.2.0.4 database on an Oracle Linux 6 server. Our client computer is a windows server computer. On this client some data warehouse tools are installed and run from this client. Also oracle 11.2 client software is installed on that client.

How to switch on sqlnet tracing

I set sqlnet tracing on 3 levels: client level, server level and listener (also on server) level. Sqlnet tracing on the client level can be switched on by setting parameters in the sqlnet.ora file on the client computer. On the server level you have to set parameters in the sqlnet.ora on the server. Setting parameters in the listener.ora file switches on listener tracing. These files can be found in the $ORACLE_HOME/network/admin directory.

Setting sqlnet tracing on the server:

On the server I copied the sqlnet.ora file to a file with the name sqlnet.ora.off. I made another copy of sqlnet.ora and gave it the name sqlnet.ora.on. Both files were put in the $ORACLE_HOME/network/admin directory, the same directory as for the original sqlnet.ora. I edited the sqlnet.ora.on file and added the following parameters to this file:

sqlnet.ora.on on the server:

TRACE_LEVEL_SERVER = 16
TRACE_FILE_SERVER = sqlnet_server.trc
TRACE_DIRECTORY_SERVER = /u03/network/trace
TRACE_UNIQUE_SERVER = ON
TRACE_TIMESTAMP_SERVER = ON

LOG_DIRECTORY_SERVER = /u03/network/log
LOG_FILE_SERVER = sqlnet_server.log

DIAG_ADR_ENABLED = OFF
ADR_BASE = /u01/app/oracle

This is not the place to explain the meaning of these parameters. For more information take a look at note id 219968.1 which can be found on the Oracle Support site or read the Oracle documentation for example: Oracle Database Net Services Administrator’s Guide, chapter 16.8: http://docs.oracle.com/cd/E11882_01/network.112/e41945/trouble.htm#r2c1-t57

However I would like to make some remarks:

TRACE_LEVEL_SERVER = 16
You can set the level of tracing with this parameter. I used the highest level. But it could be a good idea to start with a lower level for example 4 or 6. Higher levels produce more data and therefore more gigabytes.

TRACE_DIRECTORY_SERVER = /u03/network/trace
LOG_DIRECTORY_SERVER = /u03/network/log
I decided to use another mountpoint than the default in order to prevent disk full errors. There was more disk space on the /u03 mountpoint.

TRACE_UNIQUE_SERVER = ON
This causes Oracle to generate for every connection unique trace files.

TRACE_TIMESTAMP_SERVER = ON
If you set this parameter then a timestamp in the form of [DD-MON-YY 24HH:MI:SS] will be recorded for each operation traced by the trace file.

DIAG_ADR_ENABLED = OFF
ADR_BASE = /u01/app/oracle
You should set these two parameters if you are using version 11g or higher. If you use version 10g or lower then you should not add these parameters.

In my first version of the sqlnet.ora.on I also set the parameters:
# TRACE_FILELEN_SERVER = ….
# TRACE_FILENO_SERVER = ….
But it turned out that this was not a very good idea: huge amounts of files were generated. So I decided to throw them out.

Setting tracing on the listener:

I also made a copy of the listener.ora and named it listener.ora.off. I made another copy of this file and named it listener.ora.on. Also these files were put in the $ORACLE_HOME/network/admin directory. I edited the listener.ora.on and added the following parameters:

listener.ora.on on the server:

TRACE_LEVEL_LISTENER = 16
TRACE_FILE_LISTENER = listener.trc
TRACE_DIRECTORY_LISTENER = /u03/network/trace
TRACE_UNIQUE_LISTENER = ON
TRACE_TIMESTAMP_LISTENER = ON

LOG_DIRECTORY_LISTENER = /u03/network/log
LOGGING_LISTENER = ON
LOG_FILE_LISTENER = listener.log

DIAG_ADR_ENABLED_LISTENER = OFF
ADR_BASE_LISTENER = /u01/app/oracle

A remark:
If your listener has another name than the default LISTENER for example LSTNR than you should use parameters as TRACE_LEVEL_LSTNR and so on.

Setting sqlnet tracing on the client:

Also on the client computer I made two copies of sqlnet.ora: sqlnet.ora.off and sqlnet.ora.on. I added the following parameters to the sqlnet.ora.on file:

sqlnet.ora.on on the client:

TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = sqlnet_client.trc
TRACE_DIRECTORY_CLIENT = C:\app\herman\product\11.2.0\client_1\network\trace
TRACE_UNIQUE_CLIENT = ON
TRACE_TIMESTAMP_CLIENT = ON

LOG_DIRECTORY_CLIENT = C:\app\herman\product\11.2.0\client_1\network\log
LOG_FILE_CLIENT = sqlnet_client.log

TNSPING.TRACE_DIRECTORY = C:\app\herman\product\11.2.0\client_1\network\trace
TNSPING.TRACE_LEVEL = ADMIN

DIAG_ADR_ENABLED = OFF
ADR_BASE = c:\app\herman

Scripts for switching on sqlnet tracing

Scripts on the server:

On the server I created the following two scripts: sqlnet_trace_on.sh and sqlnet_trace_off.sh

sqlnet_trace_on.sh:

#!/bin/bash
# ******************************************************************************
# Script Name : sqlnet_trace_on.sh
# Purpose : To switch on sqlnet tracing and listener tracing
# Created by : AMIS Services, Nieuwegein, The Netherlands
#
# Remarks : a set of sqlnet.ora.on, sqlnet.ora.off, listener.ora.on and
# listener.ora.off must be available in the
# OH/network/admin-directory
#
#——————————————————————————-
# Revision record
# Date Version Author Modification
# ———- —— —————– ———————————-
# 07-11-2013 1.0 Karin Kriebisch Created, listener tracing
# 06-05-2014 1.1 Herman Buitenhuis sqlnet tracing added
#
#******************************************************************************
#
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export LISTENER_ORA_LOC=$ORACLE_HOME/network/admin
export LISTENER_TRACE_LOC=$ORACLE_HOME/network/log
export LOG=$LISTENER_TRACE_LOC/Listener_Trace_ON.log
#
echo — Initializing Logfile – Switching sqlnet/listener tracing ON — > $LOG
echo `date` >>$LOG
echo ================================================================ >>$LOG
echo >>$LOG
#
echo Copy listener.ora.on to listener.ora >>$LOG
#
cp $LISTENER_ORA_LOC/listener.ora.on $LISTENER_ORA_LOC/listener.ora >>$LOG
#
echo Copy sqlnet.ora.on to sqlnet.ora >>$LOG
#
cp $LISTENER_ORA_LOC/sqlnet.ora.on $LISTENER_ORA_LOC/sqlnet.ora >>$LOG
#
#
echo Restart LISTENER >>$LOG
$ORACLE_HOME/bin/lsnrctl stop >>$LOG
$ORACLE_HOME/bin/lsnrctl start >>$LOG
echo `date` >>$LOG
#
echo Check LISTENER status after 30 seconds >>$LOG
sleep 30
$ORACLE_HOME/bin/lsnrctl status >>$LOG
#
echo `date` >>$LOG
echo === sqlnet and listener Tracing switched ON === >>$LOG

sqlnet_trace_off.sh:

#!/bin/bash
# ******************************************************************************
# Script Name : sqlnet_trace_off.sh
# Purpose : To switch off sqlnet tracing and listener tracing
# Created by : AMIS Services, Nieuwegein, The Netherlands
#
# Remarks : a set of sqlnet.ora.on, sqlnet.ora.off, listener.ora.on and
# listener.ora.off must be available in the
# OH/network/admin-directory
#
#——————————————————————————-
# Revision record
# Date Version Author Modification
# ———- —— —————– ———————————-
# 07-11-2013 1.0 Karin Kriebisch Created, listener tracing
# 06-05-2014 1.1 Herman Buitenhuis sqlnet tracing added
#
#******************************************************************************
#
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export LISTENER_ORA_LOC=$ORACLE_HOME/network/admin
export LISTENER_TRACE_LOC=$ORACLE_HOME/network/log
export LOG=$LISTENER_TRACE_LOC/Listener_Trace_OFF.log
#
echo — Initializing Logfile – Switching sqlnet/listener tracing OFF — > $LOG
echo `date` >>$LOG
echo ================================================================ >>$LOG
echo >>$LOG
#
echo Copy listener.ora.off to listener.ora >>$LOG
#
cp $LISTENER_ORA_LOC/listener.ora.off $LISTENER_ORA_LOC/listener.ora >>$LOG
#
echo Copy sqlnet.ora.off to sqlnet.ora >>$LOG
#
cp $LISTENER_ORA_LOC/sqlnet.ora.off $LISTENER_ORA_LOC/sqlnet.ora >>$LOG
#
#
echo Restart LISTENER >>$LOG
$ORACLE_HOME/bin/lsnrctl stop >>$LOG
$ORACLE_HOME/bin/lsnrctl start >>$LOG
echo `date` >>$LOG
#
echo Check LISTENER status after 30 seconds >>$LOG
sleep 30
$ORACLE_HOME/bin/lsnrctl status >>$LOG
#
echo `date` >>$LOG
echo === Switched sqlnet/listener Tracing OFF === >>$LOG

Scripts on the windows client:

On the windows client I made the following two scripts: sqlnet_trace_on.cmd and sqlnet_trace_off.cmd.

sqlnet_trace_on.cmd:

REM Script Name: sqlnet_trace_on.cmd
REM Purpose : to switch on sqlnet tracing on the windows client
REM Created by : AMIS Services, Nieuwegein, The Netherlands
REM
REM Remarks : sqlnet.ora.on, sqlnet.ora.off must be available in the
REM OH/network/admin-directory
REM
REM Revision record
REM Date Version Author Modification
REM ———- —— —————– ———————————-
REM 06-05-2014 1.0 Herman Buitenhuis Creation, sqlnet tracing
REM

set ORACLE_HOME=C:\app\herman\product\11.2.0\client_1\
set SQLNET_ORA_LOC=%ORACLE_HOME%/network/admin
set SQLNET_TRACE_LOC=%ORACLE_HOME%/network/log
set LOG=%SQLNET_TRACE_LOC%/sqlnet_trace_on.log

echo — Initializing Logfile – Switching sqlnet tracing ON — > %LOG%

@echo off
For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%a-%%b)
For /f “tokens=1-2 delims=/:” %%a in (‘time /t’) do (set mytime=%%a%%b)
echo %mydate%_%mytime% >>%LOG%

@echo on

echo ================================================================ >>%LOG%
echo >>%LOG%
echo Copy sqlnet.ora.on to sqlnet.ora >>%LOG%

cd %SQLNET_ORA_LOC%
copy sqlnet.ora.on sqlnet.ora

@echo off
For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%a-%%b)
For /f “tokens=1-2 delims=/:” %%a in (‘time /t’) do (set mytime=%%a%%b)
echo %mydate%_%mytime% >>%LOG%
@echo on

echo === Switched sqlnet Tracing ON === >>%LOG%

sqlnet_trace_off.cmd:

REM Script Name: sqlnet_trace_off.cmd
REM Purpose : to switch off sqlnet tracing on the windows client
REM Created by : AMIS Services, Nieuwegein, The Netherlands
REM
REM Remarks : sqlnet.ora.on, sqlnet.ora.off must be available in the
REM OH/network/admin-directory
REM
REM Revision record
REM Date Version Author Modification
REM ———- —— —————– ———————————-
REM 06-05-2014 1.0 Herman Buitenhuis Creation, sqlnet tracing
REM

set ORACLE_HOME=C:\app\herman\product\11.2.0\client_1\
set SQLNET_ORA_LOC=%ORACLE_HOME%/network/admin
set SQLNET_TRACE_LOC=%ORACLE_HOME%/network/log
set LOG=%SQLNET_TRACE_LOC%/sqlnet_Trace_OFF.log

echo — Initializing Logfile – Switching sqlnet tracing OFF — > %LOG%

@echo off
For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%a-%%b)
For /f “tokens=1-2 delims=/:” %%a in (‘time /t’) do (set mytime=%%a%%b)
echo %mydate%_%mytime% >>%LOG%

@echo on
echo ================================================================ >>%LOG%
echo >>%LOG%
echo Copy sqlnet.ora.off to sqlnet.ora >>%LOG%

cd %SQLNET_ORA_LOC%
copy sqlnet.ora.off sqlnet.ora

@echo off
For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%a-%%b)
For /f “tokens=1-2 delims=/:” %%a in (‘time /t’) do (set mytime=%%a%%b)
echo %mydate%_%mytime% >>%LOG%
@echo on

echo === Switched sqlnet Tracing OFF === >>%LOG%

Switching on sqlnet tracing manually…

Using the scripts you can switch on and switch off sqlnet tracing.

On the server you switch on sqlnet and listener tracing by the following command:

./sqlnet_trace_on.sh

You can switch off tracing by:

./sqlnet_trace_off.sh

On the client you can run the scripts sqlnet_trace_on.cmd and sqlnet_trace_off.cmd. However there is an important thing to say: Because of windows security, you should run these scripts in a cmd box with “run as administrator”! If you don’t do that you get “Access is denied” errors.

Switching on sqlnet tracing automatically

Using crontab you can automatically switch on and switch off sqlnet tracing on the server. For example if you want to daily switch on sqlnet tracing on 02:00 and switch it off on 03:00 you add (with “crontab –e”) the following lines to the crontab file:

# switch on/off sqlnet/listener tracing
00 02 * * * /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet_trace_on.sh
00 03 * * * /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet_trace_off.sh
#

On the windows client you can use the windows task scheduler to switch on and switch off sqlnet tracing. However because of windows security you can get access denied errors. In order to solve this I had to contact the windows system administrator. He changed the security settings of the %ORACLE_HOME%/network/admin directory. And then it worked without any problems.

I switch on tracing on the client before I did the restart of the listener. So I scheduled the script sqlnet_trace_on.cmd on 01:55 and sqlnet_trace_off.cmd on 02:55.

Using the above script and method I was able to do my sqlnet and listener tracing at night. And also sleep very well! 🙂

I would like to thank my colleague Karin Kriebisch. She made the first initial version of the script.