Tracing Distributed SQL*Net Connectivity Oracle Tips by Burleson Consulting

Just as the etc/oratab file for SQL*Net version 1.0 is interpreted, the tnsnames.ora file is also interpreted. This means that you can change it at any time without fear of bouncing anything. However, changes to listener.ora require the listener to be reloaded with lsnrctl reload.

When a database is accessed remotely via a database link, SQL*Net uses the temporary tablespace on the destination database, regardless of the processor invoking the task or the original database location. That way, SQL*Net will use the temporary tablespace on the destination database--not the initiating database. In other words, applications on one processor that access another processor with a database link will use the temporary tablespaces on the terminal processor--not the processor that contains the link.

Always remember to change your $ORACLE_HOME/bin/oraenv file to unset ORACLE_SID and set TWO_TASK=sid.

The following three logs appear in SQL*Net:

• listener log--usr/oracle/network/log/listener.log

• sqlnet log--usr/oracle/network/log/sqlnet.log

• trace log--Destination set with the TRACE_DIRECTORY_LISTENER parameter of the etc/listener.ora file

Three levels of tracing are found in SQL*Net:

• lsnrctl trace admin

• lsnrctl trace user

• lsnrctl trace off

More information on SQL*Net tracing tips is available HERE.

This is an excerpt from "High Performance Data Warehousing", copyright 1997.

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.