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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.