Wednesday 19 May 2010

SQL*Net, Net8, Oracle Net Services - Tracing and Logging at a Glance.

SQL*Net, Net8, Oracle Net Services - Tracing and Logging at a Glance.


PURPOSE
-------

The purpose of Oracle Net tracing and logging is to provide detailed
information to track and diagnose Oracle Net problems such as connectivity
issues, abnormal disconnection and connection delay. Tracing provides varying
degrees of information that describe connection-specific internal operations
during Oracle Net usage. Logging reports summary, status and error messages.

Oracle Net Services is the replacement name for the Oracle Networking product
formerly known as SQL*Net (Oracle7 [v2.x]) and Net8 (Oracle8/8i [v8.0/8.1]).
For consistency, the term Oracle Net is used thoughout this article and refers
to all Oracle Net product versions.


SCOPE & APPLICATION
-------------------

The aim of this document is to overview SQL*Net, Net8, Oracle Net Services
tracing and logging facilities. The intended audience includes novice Oracle
users and DBAs alike. Although only basic information on how to enable and
disable tracing and logging features is described, the document also serves
as a quick reference. The document provides the reader with the minimum
information necessary to generate trace and log files with a view to
forwarding them to Oracle Support Services (OSS) for further diagnosis. The
article does not intend to describe trace/log file contents or explain how to
interpret them.


LOG & TRACE PARAMETER OVERVIEW
------------------------------

The following is an overview of Oracle Net trace and log parameters.

  TRACE_LEVEL_[CLIENT|SERVER|LISTENER]     = [0-16|USER|ADMIN|SUPPORT|OFF]
  TRACE_FILE_[CLIENT|SERVER|LISTENER]      = <FILE NAME>
  TRACE_DIRECTORY_[CLIENT|SERVER|LISTENER] = <DIRECTORY>
  TRACE_UNIQUE_[CLIENT|SERVER|LISTENER]    = [ON|TRUE|OFF|FALSE]
  TRACE_TIMESTAMP_[CLIENT|SERVER|LISTENER] = [ON|TRUE|OFF|FALSE]   #Oracle8i+
  TRACE_FILELEN_[CLIENT|SERVER|LISTENER]   = <SIZE in KB>          #Oracle8i+
  TRACE_FILENO_[CLIENT|SERVER|LISTENER]    = <NUMBER>              #Oracle8i+

  LOG_FILE_[CLIENT|SERVER|LISTENER]        = <FILE NAME>
  LOG_DIRECTORY_[CLIENT|SERVER|LISTENER]   = <DIRECTORY NAME>
  LOGGING_LISTENER                         = [ON|OFF]

  TNSPING.TRACE_LEVEL                      = [0-16|USER|ADMIN|SUPPORT|OFF]
  TNSPING.TRACE_DIRECTORY                  = <DIRECTORY>

  NAMES.TRACE_LEVEL                        = [0-16|USER|ADMIN|SUPPORT|OFF]
  NAMES.TRACE_FILE                         = <FILE NAME> 
  NAMES.TRACE_DIRECTORY                    = <DIRECTORY>
  NAMES.TRACE_UNIQUE                       = [ON|OFF]
  NAMES.LOG_FILE                           = <FILE NAME>
  NAMES.LOG_DIRECTORY                      = <DIRECTORY>
  NAMES.LOG_UNIQUE                         = [ON|OFF]

  NAMESCTL.TRACE_LEVEL                     = [0-16|USER|ADMIN|SUPPORT|OFF]
  NAMESCTL.TRACE_FILE                      = <FILE NAME>
  NAMESCTL.TRACE_DIRECTORY                 = <DIRECTORY>
  NAMESCTL.TRACE_UNIQUE                    = [ON|OFF]

  Note: With the exception of parameters suffixed with LISTENER, all other
        parameter suffixes and prefixes [CLIENT|NAMES|NAMESCTL|SERVER|TNSPING]
        are fixed and cannot be changed. For parameters suffixed with LISTENER,
        the suffix name should be the actual Listener name. For example, if
        the Listener name is PROD_LSNR, an example trace parameter name would
        be TRACE_LEVEL_PROD_LSNR=OFF.


CONFIGURATION FILES
-------------------

Files required to enable Oracle Net tracing and logging features include:

  Oracle Net Listener        LISTENER.ORA                  LISTENER.TRC
  Oracle Net - Client        SQLNET.ORA on client          SQLNET.TRC
  Oracle Net - Server        SQLNET.ORA on server          SQLNET.TRC
  TNSPING Utility            SQLNET.ORA on client/Server   TNSPING.TRC
  Oracle Name Server         NAMES.ORA                     NAMES.TRC
  Oracle NAMESCTL            SQLNET.ORA on server
  Oracle Connection Manager  CMAN.ORA


CONSIDERATIONS WHEN USING LOGGING/TRACING
-----------------------------------------

1. Verify which Oracle Net configuration files are in use.
   By default, Oracle Net configuration files are sought and resolved from
   the following locations:

   TNS_ADMIN environment variable (incl. Windows Registry Key)
   /etc or /var/opt/oracle (Unix)
   $ORACLE_HOME/network/admin (Unix)
   %ORACLE_HOME%/Network/Admin or %ORACLE_HOME%/Net80/Admin (Windows)

   Note: User-specific Oracle Net parameters may also reside in
         $HOME/sqlnet.ora file.
         An Oracle Net server installation is also a client.
   
2. Oracle Net tracing and logging can consume vast quantities of disk space.
   Monitor for sufficient disk space when tracing is enabled.
   On some Unix operating systems, /tmp is used for swap space.
   Although generally writable by all users, this is not an ideal location for
   trace/log file generation.

3. Oracle Net tracing should only be enabled for the duration of the issue at
   hand. Oracle Net tracing should always be disabled after problem resolution.

4. Large trace/log files place an overhead on the processes that generate them.
   In the absence of issues, the disabling of tracing and/or logging will
   improve Oracle Net overall efficiency.
   Alternatively, regularly truncating log files will also improve efficiency.

5. Ensure that the target trace/log directory is writable by the connecting
   user, Oracle software owner and/or user that starts the Net Listener.

 WARNING! PLEASE NOTE:
   When SERVER side SQL*Net tracing is enabled, this will trace each and
   every connection that ends with this server as the target. Therefore, to
   turn OFF server-side tracing, simply turning off the trace parameters
   will NOT stop tracing and this can result in severe production
   outages due to enormous trace files. To stop server tracing then,
   the server processes started with tracing turned on need to be
   stopped themselves. This might entail stopping a few processes to
   restarting the entire database, depending on the number of sessions
   involved.


LOG & TRACE PARAMETERS
----------------------

This section provides a detailed description of each trace and log parameter.

  TRACE LEVELS

    TRACE_LEVEL_[CLIENT|SERVER|LISTENER] = [0-16|USER|ADMIN|SUPPORT|OFF]
    Determines the degree to which Oracle Net tracing is provided.
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Level 0 is disabled - level 16 is the most verbose tracing level.
    Listener tracing requires the Net Listener to be reloaded or restarted
    after adding trace parameters to LISTENER.ORA.
    Oracle Net (client/server) tracing takes immediate effect after tracing
    parameters are added to SQLNET.ORA.
    By default, the trace level is OFF.
  
    OFF     (equivalent to 0) disabled - provides no tracing.
    USER    (equivalent to 4) traces to identify user-induced error conditions. 
    ADMIN   (equivalent to 6) traces to identify installation-specific problems. 
    SUPPORT (equivalent to 16) trace information required by OSS for 
            troubleshooting.

  TRACE FILE NAME

    TRACE_FILE_[CLIENT|SERVER|LISTENER] = <FILE NAME>
    Determines the trace file name.
    Any valid operating system file name.
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Trace file is automatically appended with '.TRC'.
    Default trace file name is SQLNET.TRC, LISTENER.TRC.

  TRACE DIRECTORY

    TRACE_DIRECTORY_[CLIENT|SERVER|LISTENER] = <DIRECTORY>
    Determines the directory in which trace files are written.
    Any valid operating system directory name.
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Directory should be writable by the connecting user and/or Oracle software
    owner.
    Default trace directory is $ORACLE_HOME/network/trace.

  UNIQUE TRACE FILES

    TRACE_UNIQUE_[CLIENT|SERVER|LISTENER] = [ON|TRUE|OFF|FALSE]
    Allows generation of unique trace files per connection.
    Trace file names are automatically appended with '_<PID>.TRC'.
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Unique tracing is ideal for sporadic issues/errors that occur infrequently
    or randomly.
    Default value is OFF

  TRACE TIMING

    TRACE_TIMESTAMP_[CLIENT|SERVER|LISTENER] = [ON|TRUE|OFF|FALSE]
    A timestamp in the form of [DD-MON-YY 24HH:MI;SS] is recorded against each
    operation traced by the trace file.
    Configuration file is SQLNET.ORA, LISTENER.ORA
    Suitable for hanging or slow connection issues.
    Available from Oracle8i onwards.
    Default value is is OFF.

  MAXIMUM TRACE FILE LENGTH

    TRACE_FILELEN_[CLIENT|SERVER|LISTENER] = <SIZE>
    Determines the maximum trace file size in Kilobytes (Kb).
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Available from Oracle8i onwards.
    Default value is UNLIMITED.

  TRACE FILE CYCLING

    TRACE_FILENO_[CLIENT|SERVER|LISTENER] = <NUMBER>
    Determines the maximum number of trace files through which to perform
    cyclic tracing.
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Suitable when disk space is limited or when tracing is required to be
    enabled for long periods.
    Available from Oracle8i onwards.
    Default value is 1 (file).

  LOG FILE NAME

    LOG_FILE_[CLIENT|SERVER|LISTENER] = <FILE NAME>
    Determines the log file name.
    May be any valid operating system file name.
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Log file is automatically appended with '.LOG'.
    Default log file name is SQLNET.LOG, LISTENER.LOG.

  LOG DIRECTORY

    LOG_DIRECTORY_[CLIENT|SERVER|LISTENER] = <DIRECTORY NAME>
    Determines the directory in which log files are written.
    Any valid operating system directory name.
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Directory should be writable by the connecting user or Oracle software
    owner.
    Default directory is $ORACLE_HOME/network/log.
  

  DISABLING LOGGING

    LOGGING_LISTENER = [ON|OFF]
    Disables Listener logging facility.
    Configuration file is LISTENER.ORA.
    Default value is ON.


ORACLE NET TRACE/LOG EXAMPLES
-----------------------------

  CLIENT (SQLNET.ORA)
    trace_level_client = 16
    trace_file_client = cli
    trace_directory_client = /u01/app/oracle/product/9.0.1/network/trace
    trace_unique_client = on
    trace_timestamp_client = on
    trace_filelen_client = 100
    trace_fileno_client = 2
    log_file_client = cli
    log_directory_client = /u01/app/oracle/product/9.0.1/network/log
    tnsping.trace_directory = /u01/app/oracle/product/9.0.1/network/trace
    tnsping.trace_level = admin

  SERVER (SQLNET.ORA)

    trace_level_server = 16
    trace_file_server = svr
    trace_directory_server = /u01/app/oracle/product/9.0.1/network/trace
    trace_unique_server = on
    trace_timestamp_server = on
    trace_filelen_server = 100
    trace_fileno_server = 2
    log_file_server = svr
    log_directory_server = /u01/app/oracle/product/9.0.1/network/log

    namesctl.trace_level = 16
    namesctl.trace_file = namesctl
    namesctl.trace_directory = /u01/app/oracle/product/9.0.1/network/trace
    namesctl.trace_unique = on

  LISTENER (LISTENER.ORA)

    trace_level_listener = 16
    trace_file_listener = listener
    trace_directory_listener = /u01/app/oracle/product/9.0.1/network/trace
    trace_timestamp_listener = on
    trace_filelen_listener = 100
    trace_fileno_listener = 2
    logging_listener = off
    log_directory_listener = /u01/app/oracle/product/9.0.1/network/log
    log_file_listener=listener

  NAMESERVER TRACE (NAMES.ORA)

    names.trace_level = 16
    names.trace_file = names
    names.trace_directory = /u01/app/oracle/product/9.0.1/network/trace
    names.trace_unique = off

  CONNECTION MANAGER TRACE (CMAN.ORA)

    tracing = yes

How to enable sql*net client side tracing?

PERFORMING A CLIENT-SIDE SQL*NET TRACE

(1) Open the SQLNET.ORA file typically found in the following location:

ORACLE_BASE\ORACLE_HOME\Network\Admin (7.3.x, 8.1.x or later)
ORACLE_BASE\ORACLE_HOME\Net80\Admin (8.0.x)

(2) Add the following parameters at the end of the file:

#CLIENT-SIDE SQL*NET TRACE PARAMETERS
#====================================
TRACE_UNIQUE_CLIENT = ON
TRACE_LEVEL_CLIENT = 16
TRACE_DIRECTORY_CLIENT = C:\temp
TRACE_FILE_CLIENT = SQLNetTrace
TRACE_TIMESTAMP_CLIENT = ON
#TRACE_FILELEN_CLIENT = 2048
#TRACE_FILENO_CLIENT = 2

Parameters prefixed with a "#" are interpreted as comments and will not
affect tracing.

If you want to change the location where the trace file will be saved
modify the following two parameters:

(a) TRACE_DIRECTORY_CLIENT

Set this parameter to the folder where you want the trace file to be
saved.

WARNING: Do not end the path with a "\". This is not necessary and may
prevent the trace file from being generated.

(b) TRACE_FILE_CLIENT

Set this parameter to the filename of the trace file that will be
created. The actual trace filename may contain additional information
appended to the end of the filename such as

SQLNetTrace_ _<#>.trc

where is the application's process id while it was running and
<#> distinguishes different client connections established from the
same process id (or session) to the database. This occurs since the
parameter TRACE_UNIQUE_CLIENT is set to ON.

The parameter TRACE_LEVEL_CLIENT=16 ensures that maximum trace
information is logged.

NOTE: If you think the size of the trace file will exceed the free space
on the drive where TRACE_DIRECTORY_CLIENT is saving the file, then
uncomment the last two parameters which will have the following
affect:

(a) TRACE_FILELEN_CLIENT - will create trace files of the size
specified in kilobytes (KB)

(b) TRACE_FILENO_CLIENT - will write to multiple trace files up
to the maximum size specified by
TRACE_FILELEN_CLIENT and then cycle
through the files again overwriting
previously written trace information

The default values specified above for these two paramters will
cycle the trace output between two trace files with a maximum size
of 2MB each.

Once you have made the appropriate changes to the SQLNET.ORA file be
sure to SAVE the file.

NOTE: If the application you are tracing is running from inside a web
server environment, such as Microsoft's Internet Information
Server (IIS), you will need to RESTART THE WEB SERVER at this
point in order for these changes to be seen by the web server.
This is due to the fact that web servers may cache the SQLNET.ORA
file.

(3) Immediately run the application that uses one of the programmatic
interfaces and generate the error.

(4) Immediately go back to the SQLNET.ORA file and turn tracing off by using
any of the following methods:

(a) Modify the following parameter:

TRACE_LEVEL_CLIENT = OFF

(b) Remove all the tracing parameters you added to this file or

(c) Comment all the tracing parameters out by placing the "#"
character before each one.

Save the changes to the SQLNET.ORA file.

(5) Retrieve the ".trc" file(s) from the location "C:\temp\SQLNetTrace\" or
wherever you have saved the file(s) based on what was specified in the
parameters TRACE_DIRECTORY_CLIENT and TRACE_FILE_CLIENT. If you did not
specify a location please check the location of the directory where the
application was run from or search your hard drive for all ".trc" files.
Check the size of the ".trc" file(s) and make sure they do not have a
size of 0 bytes but do have the current time and date. Zip the file up
and upload it to support.

Friday 14 May 2010

EM RMAN BACKUP SCRIPTS

FRIDAY FULL INCREMENTAL 0 BACKUP :

--set encryption on for all tablespaces algorithm 'aes256' identified by '%password';

--backup device type disk tag '%tag' database;

backup incremental level 0 cumulative device type disk tag '%TAG' database;

backup device type disk tag '%TAG' archivelog all not backed up delete all input;;

allocate channel for maintenance device type disk;

crosscheck backup;

delete noprompt expired backup;

delete noprompt obsolete device type disk;

crosscheck archivelog all;

delete noprompt expired archivelog all;

release channel;

DAILY Full INCREMENTAL 1 (dIff) BACKUP;

backup incremental level 1 device type disk tag '%TAG' database;

backup device type disk tag '%TAG' archivelog all not backed up;