Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-05-19
6
Medium Priority
?
1,222 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 1400 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?
0
 

Accepted Solution

by:
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.
0
 
LVL 40

Expert Comment

by:mrjoltcola
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 600 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?
0
 

Author Comment

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

Author Comment

by:CESD123
ID: 24532742
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

877 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