0

I have a test environment with an Oracle 10XE database which is linked to an SQL-Server 2005 via an ODBC data source. For reference here I named the connection MSSQL_LINK. The production environment will have an Oracle 11g and an SQL-Server 2008. The connection once set up works fine.

As a test of error conditions in production we unplugged the network cable. This broke the connection. A subsequent executed statement (e.g. select * from test_table@MSSQL_LINK;) failed with an error ORA-28500, which is a wrapper for the original ODBC error:

[Generic Connectivity Using ODBC][S1000] [9013]General error in nvITrans_BeginT - rc = -1. Please refer to the log file for details.
ORA-02063: preceding 2 lines from MSSQL_LINK

This was somewhat expected. Unfortunately plugging the cable back into the machine does not lead to an automatic reconnect of the link. The problem persists until I drop the link and create it anew with the commands

drop database link MSSQL_LINK;
create database link MSSQL_LINK connect to "user" identified by "passwd" using 'MSSQL_LNK';

Also the same happens if I just disconnect the cable for a short time and reconnect it before I execute any statements over the link. The interim breakup is still detected by the ODBC driver but not fixed on its own.

As mentioned by Ste, creating a link will tell Oracle only about the connection parameters like host, user name, password, but not create a connection by itself. The link can be created without the network cable connected to the Oracle machine. If I create the link without the cable connected, then connect it to and execute a simple select on the linked server, the same Oracle error occurs.

Obviously in production mode this is an unacceptable situation which needs to be fixed, preferably by an automatic procedure.

Three questions do I have about this:

  1. Is there a way to test a database link other than e.g. a select on a test table which resides on the linked server?
  2. Is dropping an re-creating the database link on my oracle machine the best option I have when this problem occurs?
  3. What is the best automatic procedure for recovering from this situation.

1 Answer 1

1

Step by step:

  1. There is no way to test a dblink other than select a dummy over it
  2. A network timeout affects only the running transaction that is currently the dblink. There is no recorded state about the dblink, there is no status like ACTIVE or BROKEN. DBLink creation simply instruct the DB about remote host, port, tns, user and password. Hence drop and recreate the dblink does not affect or change the network or ODBC problem. A pratical case is: select sysdate from dual@dblink. A firewall is in place between both databases. Source DB opens the connection, retrieve data and leave connection opened. After 15mins the firewall drop the connection. At minute 20 another user performs the same query. The database will reopen the connection.
  3. The best automatic way should be to query a table; this only if we are experiecing a simple network issue.

Apart from this I understand that you get the connection dropped and you have to way to re-establish it without drop and recreate the database. Metalink (formerly My Oracle Support) suggest to check if HS=OK is correctly set in the tnsnames.ora. Attention: the HS=OK argument needs to be outside of the CONNECT_DATA portion of the entry. Here an example:

MSSQL =
(DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS =
              (PROTOCOL = tcp)
              (HOST = 192.168.36.18)
              (PORT = 1522)
             )
        )
   (CONNECT_DATA =
        (SID = MSSQL)
   )
   (HS=OK)
)

Moreover I suggest you to check (and eventually post) logs on both sides. You can also increase the level of HS_FDS_TRACE_LEVEL parameter. In addition MOS reports some issue with DG4ODBC and suggest to debug them (and fill a bug if necessary) using trace on the ODBC layer.

6
  • the setting seems OK, HS=OK was set. Can you clarify the second point in your post? What do you mean by "without causing any issue"? Also "when the link is requested again", do you mean reused by a query? And "drop and re-creation does not have any sense", in which way? To me it works, but in my oppinion it should not be the route to go.
    – mruether
    May 29, 2013 at 11:49
  • @mruether tried to explain. Tell me you need more clarification.
    – Ste
    May 29, 2013 at 12:24
  • "Hence drop and recreate the dblink does not affect or change the network or ODBC problem": my practical experience proves this wrong. The only remedy for the persisting error messages is a drop and re-create of the database link. But you are right: Oracle does not open or check the connection when the link is created; only when the first statement is executed, a connection is created. I've updated the post according to this fact.
    – mruether
    May 29, 2013 at 12:39
  • @mruether I agree with your actual experience but the behaviour is strange and probably can be associated to a bug (I see 12+ bugs with ORA-2603 and DG4ODBC). Also, try to trace ODBC, this is the debug way proposed by oracle.
    – Ste
    May 29, 2013 at 13:16
  • OK, I will try to analyse the logfiles, and come back to you after that. Do you have a link which lists the 12+ ODBC errors?
    – mruether
    May 30, 2013 at 13:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.