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] 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:
- 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?
- Is dropping an re-creating the database link on my oracle machine the best option I have when this problem occurs?
- What is the best automatic procedure for recovering from this situation.