I have a TNS connection problem. I've got two Oracle databases, one running Oracle 7.3 on a server running Windows NT 4, Service Pak 6 (we'll call this Server-1), and the other running Oracle 9i Enterprise Edition on a server running Windows 2003 (we'll call this Server-2). I have a database link in database 1 that connects to database 2. Database 1 has a table Tbl_1, whose exact structure is in database 2 as Tbl_5. I have a trigger on Insert for Tbl_1 that inserts data in Tbl_5 and in EXCEPTION 'when others'; it inserts the same data into a tmp_Tbl1 table in database 1.
When Server-2, which contains database 2, is up, the inserts in Tbl_1 in database 1 are quick and the trigger inserts into Tbl_5 also happen quickly. The problem is when Server-2 is down. It takes about 45 seconds to insert a record in Tbl_1. I tried setting tcp.nodelay = yes in sqlnet.ora, but this didn't help. What is the correct method? Even a simple "DESC TBl_5@
I would first like to caution you that database links between Oracle 9i and Oracle 7 are not supported. You may run into connectivity problems that cannot be fixed due to the difference in versions. I would highly recommend upgrading your Oracle 7 database to Oracle 10g.
To solve your specific problem, I would recommend setting your SQLNET.INBOUND_CONNECT_TIMEOUT parameter in your SQLNET.ORA file. This should be set on the database server.