Problem solve Get help with specific problems with your technologies, process and projects.

TNS connection problem between two linked databases

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@ " takes 45 seconds to show an ORA-12203 error.

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.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.