Intermittent error in distributed server link, SQL Server 2005 to Oracle

Posted on 2009-05-19
Last Modified: 2012-05-07

We have an Oracle server linked to an MS SQL 2005 server (running on Windows 2003 Server), and are running distributed queries from the SQL side, e.g.:

      select * from (oracle_linked_servername)..table.column

Periodically the connection seems to "break", and the following will be displayed in SSMS:

     OLE DB provider "OraOLEDB.Oracle" for linked server "IPOSpire" returned message "Table does not exist.".
     Msg 7311, Level 16, State 2, Line 1
     Cannot obtain the schema rowset "DBSCHEMA_INDEXES" for OLE DB provider "OraOLEDB.Oracle" for linked server "IPOSpire". The provider supports the interface, but returns a failure code when it is used.

I've tried to Google the issue, and apparently this error is often due to a security issue - however, I haven't seen anyone else report this as an _intermittent_ error. Why would the connection sometimes work and sometimes fail? Many thanks, and 1,000 points to whoever can solve it!
Question by:CESD123
  • 3
  • 2
LVL 40

Assisted Solution

mrjoltcola earned 350 total points
ID: 24425474
Can you monitor the Oracle side and verify the sessions prior to, during and after the session "break"

Does this happen when there is a lot of distributed activity?

Did you check the Oracle alert log to see if there are any messages?

Is the schema user for the link under any profile limits?

Accepted Solution

CESD123 earned 0 total points
ID: 24425736
Hello mrjoltcola,

Thanks for your response. I've already asked our Oracle administrator to check the logs on his end, and will also forward your comments to see if that gives him more ideas on what to check for.
LVL 40

Expert Comment

ID: 24425807
What immediately came to mind is a possible resource problem on the Oracle side, such as max processes or cursors being reached in a case where a lot of SQL Server activity is happening. Since you didn't say if this was an idle or busy database, it is worth a short. It should show in the alert log, if so, and will give your DBA clues as to what to set (increase PROCESSES, etc.).

He may also turn on session auditing for the db link user to see what exactly is going on, using fine grained auditing for select statements against the remote table.

Another idea, he can view AWR / ADDM snapshots via Enterprise Manager to see if any instance metrics are spiking during the time of the error.
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

LVL 48

Assisted Solution

schwertner earned 150 total points
ID: 24425830
Check for firewalls, timeouts, antiviruses that in most cases are installed by default and disturbed the connection.

Curiosly sometimes the Networkcards and switches are out, or recently the network administrators has changed IP adresses of important nodes: DNS, default gateway.

Have you recently changed the names of the servers or the IP?
Have they static IP or are DHCP?

Author Comment

ID: 24426669
Hello, and thanks again.

I'm not able to answer most of these questions, but have passed them on to the appropriate administrators. Regarding this:

     Does this happen when there is a lot of distributed activity?

No, to the best of my knowledge there is very little distributed activity.

Author Comment

ID: 24532742

Thanks to everyone for their comments. We believe this was caused by a version mismatch between the Oracle host on the db server, and the Oracle client on server attempting to connect. After updating the client's version to match, the problem seems to have stopped.

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question