[Translated from mos] Enable oraclenettrace/tracing of dblink-Method for tracking dblink

[Translated from mos] Enable oraclenettrace/tracing of dblink-Method for tracking dblink

Enable dblink's oracle net trace/tracing -- Method for tracking dblink.

Reference Original:
DBLINK: How to Enable Oracle Net Tracing for Database links (Doc ID 746917.1)

Applicable:
Oracle Net Services-Version 9.2.0.1.0 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.
* ** Checked for relevance on 24-JAN-2013 ***

Solution:

Database link uses the Oracle Net server code. Therefore, when tracing (TRACE) dblink, Oracle Net server tracing must be enabled. Add the following content to the sqlnet. ora file:
TRACE_LEVEL_SERVER = 16
TRACE_DIRECTORY_SERVER = DIRECTORY # eg/u01/oracle/trace
TRACE_TIMESTAMP_SERVER = ON

DIAG_ADR_ENABLED = OFF # This parameter is required for version 11g onwards

The preceding content must be added to the server created by dblink. If the tracing requires dblink at both ends, Oracle Net server tracing must be enabled at both ends.
If dblink uses the VPC connection mode, the trail starts immediately (effective for all connections using the db ). For the Shared Server mode, you need to restart the dispatcher to take effect.
For more information about the Shared Server mode, see Note 1005259.6 Shared Server (MTS) Diagnostics

To locate the Oracle Net server trace file generated by dblink, search for the dblink name or the error code generated when dblink is used.

Sqlplus scott/tiger
SQL * Plus: Release 10.2.0.4.0-Production on Mon Nov 3 12:33:39 2008
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production
With the Partitioning and Data Mining options

SQL> select sysdate from dual @ test;

SYSDATE
---------
03-NOV-08

SQL>

Cd to TRACE_DIRECTORY_SERVER
Cd $ ORACLE_HOME/network/trace

Search for dblink name, SQL used, error code, and so on.

Grep date *. trc
Svr_3229.trc: [03-NOV-2008 12: 33: 48: 476] nsprecv: 64 61 74 65 20 66 72 6F | date. fro |

The trace svr_3229.trc file on the Server is generated when the session uses dblink. The trace file on the server will display two Connection ID values.
The first value is the initial connection to the local database.
The second value is the connection to the remote database.

Grep Connection ID svr_3229.trc
[03-NOV-2008 12: 33: 39: 915] nas_scn: Connection ID: 00c9c89d59c3
[03-NOV-2008 12: 33: 49: 093] nas_ccn: Connection ID: 00c9d89d59f9d

Go to the remote server and search with the second value:
Cd to TRACE_DIRECTORY_SERVER
Cd $ ORACLE_HOME/network/trace

Grep the connection ID

Grep 00c9d89d59f9d *. trc
Svr_3243.trc: [03-NOV-2008 12: 33: 49: 170] nas_scn: Connection ID: 00c9d89d59f9d

Therefore, the trace file successfully matched by this session is svr_3229.trc and svr_3243.trc.