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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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 ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

636 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