Re: Databse Link Works only in One Direction Except for Sys Schema

On 23 Mar 2005 19:03:47 -0800, bstover_at_norcalmutual.com wrote:

>Hi All,
>
>I'm not an affluent oracle dba. I've been hammering on this for a week,
>and I stll can't solve it. I'm hoping I can find some help here.
>
>We have a VPN to from a local segement to a remote segment in PA. It
>is wide open temporarily for testing the problem. There are 2 UNIX
>boxes running Oracle on either end. HPUX 11. BoxA and BoxB. I can
>ping, ssh, tracert, from BoxA to BoxB and visa versa. There are no
>restrictions. I can do names resolution between the machines.
>
>I can connect remotely ( (sqlplus username/passwd_at_dbname) from a
>command line, and use sql tools to connect to a remote database and
>perform queries. This works with both SQL Plus and SQL Navigator.
>This works from BoxA to BoxB and visa versa.
>
>I can perform queries using a database link for views belonging to the
>sys schema and other views in the sys schema (select count(*) from
>user_objects_at_otherdbname). This works in both directions.
>
>I can connectct by using the dbname or global naming convention:
>dbname or dbname.world.
>
>
>HOWEVER, when I attempt to perform queries against regular tables, it
>works fine from BoxA to BoxB, but when attempted from BoxB to BoxA, the
>session hangs. We can see that the session is connected to BoxA, but
>no data is returned through the connection. I've tested the link, and
>it it's fine.
>
>TNSNames entries match for both machines. Oracle 9.2 to 9.2.
>
>I've checked bandwidth -- hardly any utilization. T3 on our end. T1
>on their end.
>
>Any ideas? Any help you could offer would be appreciated.

Step 1 perform an ordinary ping in both directions. Notice the response times
Step 2 run traceroute to the other system from both ends. Notice the response times and/or any timeouts
Step 3 Run tnsping from both ends. Notice the response times.

Step 4 Enable sql net trace on level 16 on both systems, and reproduce the hanging situation. Kill the client session, and look in your sqlnet trace file how far it got.
If you actually do see data packets being transmitted, run trcasst on the sqlnet trace file, and notice how big the packages are.

In this stadium also find out what the MTU of the network card and/or your system is, and compare it to the SDU of sqlnet. If you didn't set it: the default is 2048 bytes.

Note: It is highly unlikely it is Oracle, because there are no reasons why sqlnet would hang.

--
Sybrand Bakker, Senior Oracle DBA