How to generate Oracle Net tracing for a DBMS_JOB using a database link

Problem Description
-------------------
A job that is scheduled via the DBMS_JOB system fails with a general error
returned from the network layer. The job makes use of distributed transactions
(i.e a Database Lin)..

You wish to determine the underlying cause by activating Oracle Net tracing
on the Data Server, where the job was executed. However, no Oracle Net trace
files are created upon job failure.


Solution Description
--------------------

DBMS_JOBs are executed by the SNP background processes which are automatically
started  when the database is started. No new client process/session is
created to serve the communication over the database link. Instead, the SNP
process acts as a client on behalf of the database link session.

Therefore, you must trace the SNP background process/es by enabling Oracle Net
server-side tracing for the database where the job is executed.


Solution Explanation
--------------------

1. Enable server-side sqlnet tracing where the job is executed as follows:

   # Data Server SQLNET.ORA file:
   SQLNET_LEVEL_SERVER=16
   SQLNET_FILE_SERVER=server
   SQLNET_DIRECTORY_SERVER=/tmp/tns_trace
         

2. Restart the Listener and Database for the Oracle Net tracing parameters to
   take effect.

3. Determine the process id's (PID) for the SNP background process/es as
   follows:
 
  SQL> select spid from v$process where program like '%SNP%';
 
  SPID
  ----------
  4894
  4896

4. Verify the trace files are created.
   Note that the file location and format depends on the Oracle Net trace
   parammeters defined in the SQLNET.ORA file.

   Based on the above configuration, trace files would have the following
   format:

   /tmp/tns_trace/server_<spid>.trc


5. Either execute the job manually using 'dbms_job.run(jobno)' or wait until
   the job is next executed automatically. Then, determine which of the above
   trace files represents your job for further analysis.


References
----------

Note:16658.1 : Tracing SQL*Net/Net8


Additional Search Words
-----------------------

DATABASE; DBLINK; DBMS_JOB; DISTRIBUTED; JOB; LINK; NET; SNP; SQLNET; TRACE;
NET8; NETWORK;