Troubleshooting Oracle Link Server - Part2

It's my bad that I did not made a post since long lately. I would not make an excuse like I was busy, rather, I would say I am little lazy DBA, but I now onward I will try to post blog more frequently.

Well, if you remember, earlier I have posted some tips about troubleshooting Link Server issue for Oracle, today I will be posting one more tip to the list aka extending Troubleshooting Oracle Link Server. 

Few weeks back one of the developer reached out to me needing an assistance with Oracle Link Server. The issue with him was, whenever he execute T-SQL against Oracle Link Server, SQL Server was throwing an error at him which reads like OLE DB provider "OraOLEDB.Oracle" for linked server "XXXXXX" returned message "Cannot start more transactions on this session." I'd connected to the server using SSMS and have checked permissions, Link Server and Provider configuration - nothing was changed. Then what could have cause this error? well, I will need to RDP to the box then to find an answer.     

But before that, as usual, upon questionnaire session, I came to know that developers doesn't have direct access to box and they all were using Citrix to connect to the SQL Server box hence they would not have any idea on what's going on with the physical box. As a DBA I had a privileges to RDP to the development box (obviously ), as soon as I logged in I'd notice that the box is running out of space. The drive which was running out of space was hosting:

• User Profiles
• TempDB
• & UserDB which they were using for ETL purpose for Oracle

And, as expected, once I'd reclaim some space everything was back to normal. 

Regards

Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Troubleshooting Oracle Link Server Issue

Most of the time, due to different business requirements we do have to work on various RDBMS systems, Oracle and MS SQL Server are the widely used and popular RDBMS. Sometimes we need to import/export data from/to SQL Server and for that we’ve to use Link Server feature of MS SQL Server.

In one of my recent project, a critical application has a job that pulls in the data from an Oracle database, we have a DTS Package and some job scheduled for this task. since couple of days we’ve observed below error message in the job history and while running ad-hoc queries. I am penning down the error message and the solution (rather, temporary workaround) we have found out :

1) The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.

==> In this case, rebooting MS DTS service  helped us.

2) The maximum number of active transactions that the MS DTC log file can accommodate has been exceeded.  You must increase the size of the MS DTC log file if you wish to initiate more concurrent transactions.

==> In this case, increasing Log file size helped us.

3)Server: Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "OraLnkSvr" reported an error. The provider ran out of memory.

4) Server: Msg 7330, Level 16, State 1, Line 1
Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "OraLnkSvr".
OLE DB provider "MSDAORA" for linked server "OraLnkSvr" returned message "Out of memory.".

==> In case 3 & 4, rebooting SQL Server service helped us.

 

Hope this helps.

 

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More