Solved

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

Posted on 2009-05-19
6
1,163 Views
Last Modified: 2012-05-07
Hello,

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!
0
Comment
Question by:CESD123
  • 3
  • 2
6 Comments
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 350 total points
Comment Utility
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?
0
 

Accepted Solution

by:
CESD123 earned 0 total points
Comment Utility
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.
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
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.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 150 total points
Comment Utility
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?
0
 

Author Comment

by:CESD123
Comment Utility
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.
0
 

Author Comment

by:CESD123
Comment Utility
Hello,

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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now