About DATABASE

TECH: SQL*Net V2 on Unix - A Quick Guide to Setting Up Client Side Tracing [ID 16564.1]
		Modified 20-OCT-2005 Type REFERENCE Status PUBLISHED

( For a more detailed discussion of SQL*Net V2 tracing see Note:16658.1 )

Client Tracing
~~~~~~~~~~~~~~
1) Set the environment variable TNS_ADMIN to the directory where the 
   tnsnames.ora and listener.ora files exist.

   The default location is $ORACLE_HOME/network/admin. Set $TNS_ADMIN to this
   if it is not set. This ENSURES you know which files you are using.

2) Start the listener:          lsnrctl
                                > set password <password>
                                > start

   Note any errors. If you do not have a password set then ignore the 
   set password command.

3) If the listener started, start the database.

4) Create a file in $HOME called .sqlnet.ora and add the lines:

        trace_level_client= 16
        trace_file_client=client
        trace_directory_client= /tmp            (or similar)
        trace_unique_client=true

5) Try to connect from SQL*Plus thus:

        sqlplus username/password@alias

   or
        sqlplus username/password

   substituting a suitable alias.

6) If you get an error we may need to see the client trace file
   /tmp/client_<PID>.trc where <PID> is the process ID of the 
   client process (*1).
   This will be quite large so it is best to FAX or EMAIL it.

*1 Note: On earlier versions of SQL*Net the filename may NOT have
         the process ID appended to it.


Listener Tracing:
~~~~~~~~~~~~~~~~~

1) Edit your $TNS_ADMIN/listener.ora file and add the lines:

        TRACE_LEVEL_LISTENER     = 16    
        TRACE_DIRECTORY_LISTENER = /tmp
        TRACE_FILE_LISTENER      = "listener"   

2) Stop and restart the listener:

        lsnrctl stop
        lsnrctl start

   Output should go to /tmp/listener.trc

In this Document
  Goal
  Solution
     Launch the Net Manager utility
     Enabling Oracle Net Client / Server tracing (or both).
      Enabling Oracle SQLNet Listener tracing.
     Cyclic Tracing
     Tracing Kerberos
     Tracing Externel Procedures (Extproc)
     Tnsping Trace
     Enabling Dynamic Listener Tracing
     Only for 11g
  References

---

Applies to:

Oracle Net Services - Version: 9.2.0.1.0 to 10.2.0.2.0
Information in this document applies to any platform.

Goal

This document covers how to use Oracle Net Manager tool to enable Oracle SQLNet Client, Server, Listener, Kerberos and External Procedure tracing.  It also includes how to control the size and amount of trace files produced and steps to start dynamic listener tracing.

Solution

Launch the Net Manager utility

 

Windows : Go to Start button, select the Oracle Home on which we are going to generate the tracing for. Net Manager is located under 'Configuration and Migration Tools' of the Oracle Home menu.

Unix : The tool can also be started remotely from a Microsoft Windows system with the help a terminal emulation software. You need to ensure this software is up and running first.  Ensure that the $DISPLAY, $ORACLE_HOME, and $PATH environment variables are properly set. The Net Manager tool is started by the command "netmgr".

 

 

Enabling Oracle Net Client / Server tracing (or both).

 

A. Click on Profiles in the left panel. The right panel of Net Manager will now show more details.

B. Select General from drop down menu on the right panel of the Net Manager.

C. Select Tracing tab in the right panel. Client Information and Server Information is now seen.

D. Select the required Trace Level. Oracle Support usually requests level SUPPORT (also known as level 16). It is optional to provide a value for the Trace Directory and Trace File. If you do,  ensure that the directory exists and that the owner of the Oracle software is able to write to that directory. The default location for the trace files is the $ORACLE_HOME/network/trace directory. Default name is CLI.TRC

The Unique Trace File Name option for the Client Information section, will make unique trace files for every connection. Without this option selected, all client tracing goes into the one file.  If running Microsoft Windows, always set this parameter or there is the potential that the trace file with the error will be overwritten.  The Server does not have this parameter available.

E. If tracing is to be done on the Server and the connection model is Shared Server, then the Instance will have to be restarted for the Dispatchers to pick up the changes to the SQLNET.ORA file.

The SQLNET.ORA will now look like the following :

 Enabling Oracle SQLNet Listener tracing.

A. In the left panel of the Net Manager, select Listeners view.

B. Select the Listener you want to trace. Once selected the right hand panel will show more details.

C. Select General Parameters from the drop down button, in the right hand panel.

D. Select Logging & Tracing tab.

E. Click the Tracing Enabled radio button and select Trace Level. SUPPORT level is normally requested by Oracle Support.

Select a directory for the trace to be generated.

G. Listener will need to be restarted or reloaded to enable or disable tracing.
 

 

The LISTENER.ORA will now have the following lines.

Cyclic Tracing

Adding parameters TRACE_FILENO_ and TRACE_FILELEN_ to the SQLNET.ORA or LISTENER.ORA files enables Oracle Net trace files to be limited a certain size and a certain number of files. Following example is for Listener traces of 10 MB, with a maximum of 10 files for a maximum of 100 Mb of trace files. Cyclic tracing can be configured for the Client, Server and Listener

TRACE_FILENO_{CLIENT/SERVER/listener_name}: When this parameter is set it will determine the maximum number of trace files that will be generated. Trace data will continue to generate but in a cyclic fashion. The maximum size of each file is set by configuring the appropriate TRACE_FILELEN parameter.

TRACE_FILELEN_{CLIENT/SERVER/listener_name}: When set determines the maximum size of each trace file before either starting a new trace file (if the TRACE_FILENO parameter is set) or will overwrites an existing file (cyclic tracing). Once the last trace file has been written, up to the size specified by TRACE_FILELEN_<CLIENT/SERVER/listener_name>, tracing will continue by overwriting the first trace file in the cycle.

Tracing Kerberos

Kerberos can be traced using the following parameters in the SQLNET.ORA file.
See Note 185897.1 Kerberos Troubleshooting Guide for more details.

 

Tracing Externel Procedures (Extproc)

External procedure can be traced using the following parameter in the SQLNET.ORA file, where extproc is setup.

 

Tnsping Trace

 

Enabling Dynamic Listener Tracing

There might be situations when you cannot reload or restart the listener to enable the traces.Such times, you can fmake use of the dynamic tracing for the listener .

This would create sqlnet support level tracing on the listener under directory /home/oracle with name listener.trc

Only for 11g

11g has new method of tracing Automatic Diagnostic Repository(ADR)  which will create the trace to the ADR directories
Reference
Note 454927.1Using and Disabling the Automatic Diagnostic Repository (ADR) with Oracle Net for 11g

To create the trace files to the desired directory,
For client tracing & server tracing add:


For listener tracing:

Unable to start Net Manager

If Net Manager is unable to start, check that you are able to launch other GUI tools from the same server, where Net Manager is failing. For example the X-Window utility "xclock". If you are not able to launch xclock, you need to address this problem, before attempting to start Oracle GUI tools. If xclock works, check other Oracle GUI tools, like DBCA before reporting the issue to Oracle Support.

References

NOTE:374116.1 - How to Match Oracle Net Client and Server Trace Files
NOTE:454927.1 - Using and Disabling the Automatic Diagnostic Repository (ADR) with Oracle Net for 11g

---

In this Document
  Goal
  Solution
  References

---

Applies to:

Oracle Net Services - Version: 9.2.0.1.0 to 11.2.0.1 - Release: 9.2 to 11.2
Information in this document applies to any platform.
Checked for relevance on 7-MAY-2010.

Goal

When attempting to limit the amount of trace generated when TNS listener tracing is enabled, cyclic tracing can be used. But to further restrict the amount of time TNS listener is enabled, tracing can be enabled and disabled, dynamically via the listener control, lsnrctl. This means tracing can be enabled only when required and allows the listener to run without generating trace until required.But when enabled via this method, Time stamp is missing from the trace file(s) generated. The following steps explain how to workaround the time stamp restriction.

Solution

To workaround the current restriction of TNS listener tracing enabled via listener control, generating traces without time stamp, the following steps can be used. Steps 1 to 3 must be inplaced before attempting to capture any problem with tracing.

 1. Backup the Listener.ora file, add the following parameters and save.

TRACE_LEVEL_<Listener Name> = 16
TRACE_FILE_<Listener Name> = <File Name>
TRACE_DIRECTORY_<Listener Name> = <Directory>
TRACE_TIMESTAMP_<Listener Name> = ON
TRACE_FILELEN_<Listener Name> = <Size in KiloBytes>
TRACE_FILENO_<Listener Name> = <Number>

TRACE_FILELEN and TRACE_FILENO are the cyclic tracing parameters.Ensure values used for these are large enough to capture information,without being overwritten.

2.Reload the listener, with command lsnrctl reload <listener name>

With a default listener name of "listener", the listener name can be excluded from commands.

3. Stop listener tracing, run set trc_level 0 via lsnrctl

If your listener name is not using the default name of "listener", then run command "set current_listener <listener name> in listener control, before any other command is used.

4. When TNS listener tracing is required to be re-enabled. run set trc_level 16 via lsnrctl.

 
5. Turn off tracing as soon as the problem / issue has been captured with command "set trc_level 0" from lsnrctl. (As per step 3). Remove or hash out the parameters added to the listener.ora in Step 1 and run reload command. This will stop tracing from being re-activated.

UNIX: EXAMPLE OF TURNING ON LISTENER TRACE
==========================================

PURPOSE

---

RDBMS Manual, Oracle Note에서 Oracle Net Listener Trace File 생성 방법을
설명하고 있으나 실예가 고객에 이해를 돕는데 유용하여
아래와 같이 그 실제 상황에 가까운 예를 적습니다.

Explanation

---

Example

---

listener trace를 생성하는 절차는 다음과 같습니다.

a. 문제가 발생하는 database server에서:
prompt$ echo $TNS_ADMIN

b. TNS_ADMIN 환경 변수가 설정되어 있으면
prompt$ cd $TNS_ADMIN

설정되어 있지 않으면
prompt$ cd $ORACLE_HOME/network/admin

prompt$ vi listener.ora
...
TRACE_LEVEL_<listener name>=16
TRACE_FILE_<listener name>=<filename>
TRACE_DIRECTORY_<listener name>=<directory>
TRACE_TIMESTAMP_<listener name>=ON
:wq
prompt$ lsnrctl stop <listener name>
prompt$ lsnrctl start <listener name>

Production 환경에서 trace file로 인한 disk full 없도록 trace file을 재사용할 수도 있습니다.

TRACE_LEVEL_<listener name>=16
TRACE_FILE_<listener name>=<filename>
TRACE_DIRECTORY_<listener name>=<directory>
TRACE_TIMESTAMP_<listener name>=ON
TRACE_FILENO_<listener name>=<# of trace files to switch>
TRACE_FILELEN_<listener name>=<length of each trace file in KB>

예)
TRACE_LEVEL_listener1=16
TRACE_FILE_listener1=listener1
TRACE_DIRECTORY_listener1=/u01/app/oracle/rdbms/10.2.0/network/trace
TRACE_TIMESTAMP_listener1=ON
TRACE_FILENO_listener1=3
TRACE_FILELEN_listener1=1024768

3. Reproduction
문제 발생 후 trace를 중단 하시기 바랍니다.

4. Stopping Oracle Net listener Tracing:
prompt$ vi listener.ora
...
TRACE_LEVEL_listener=0
...
:wq
prompt$ lsnrctl stop <listener name>
prompt$ lsnrctl start <listener name>
prompt$

4. Listener가 내려간 동안은 기존의 database session들은
Oracle Server Process의 의하여 계속 처리가 됩니다.
새로운 database connection request는 listener가 내려간 동안
처리되지 않아 그 동안 client는 database에 연결할 수 없습니다.

5. TAR를 통한 진행 중에는 생성된 Oracle Net Server Trace file들을
WinZip 또는 Unix의 compress (.tar.Z), GNU zip (tar.gz)으로
묽어 올려주시기 바랍니다.

6. TAR를 통한 진행 중에는 문제가 발생한 시:분도 같이 알려 주시면
Net engineer가 Trace 중에 해당 시간에 기록된 내용을 중심으로 보게 됩니다.

Reference Documents

OS : Linux Fedora 9

Oracle Release 11.2.0.1.0

오라클 11g(Release 11.2.0.1.0)를 성공적으로 설치하였음에도 불구하고 서버가 설치된 머신 에서는 sqlplus등의 Tool로 정상적으로 접속이 이루어졌지만, 원격으로 접속하는 경우 ORA-12514오류가 발생하면서 접속이 이루어 지지 않아 다음의 내용을 오라클 서버의 listener.ora를 수정함으로 해결 되었다. 수정이 된 이후에는 오라클 리스너를 새로 시작해야 한다.

 

대상파일 : /Oracle/Database/oracle/product/11.2.0/dbhome/network/admin/listener.ora

 

수정 前

+1 LISTENER =  +2   (DESCRIPTION_LIST =  +3     (DESCRIPTION =  +4       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  +5       (ADDRESS = (PROTOCOL = TCP) (HOST = localhost.localdomain)(PORT = 1521))  +6     )  +7   )  +8  +9 ADR_BASE_LISTENER = /Oracle/Database/oracle

 

수정 後(붉은색 부분이 추가된 부분)

+1 SID_LIST_LISTENER =  +2 (SID_LIST =  +3     (SID_DESC =  +4         (SID_NAME = PLSExtProc)  +5         (ORACLE_HOME = /Oracle/Database/oracle/product/11.2.0/dbhome)  +6         (PROGRAM = extproc)  +7     )  +8     (SID_DESC =  +9         (ORACLE_HOME = /Oracle/Database/oracle/product/11.2.0/dbhome) +10         (SID_NAME = mjs) +11     ) +12 ) +13 +14 +15 LISTENER = +16   (DESCRIPTION_LIST = +17     (DESCRIPTION = +18       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) +19       (ADDRESS = (PROTOCOL = TCP) (HOST = localhost.localdomain)(PORT = 1521)) +20     ) +21   ) +22 +23 ADR_BASE_LISTENER = /Oracle/Database/oracle