Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replication from Oracle to SQL Server Expand / Collapse
Author
Message
Posted Thursday, September 23, 2010 10:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
We are trying to replicate from Oracle 11G to SQL Server 2008R2.
OS: windows server 2008R2 64bit.
SQL server version: SQL 2008 R2 - 64bit.

I installed the oracle 11G 64bit client. Then restarted the SQL Server services. I still can't see the oracle provider under LinkedServer---->Providers within Sql server management studio.
So now I am thinking of uninstalling the oracle 64bit client and instead install oracle 32bit client. Before doing that I want to check with you all.
Any ideas will be greatly appreciated. Thanks.
Post #992172
Posted Thursday, September 23, 2010 2:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 18, 2012 1:45 PM
Points: 10, Visits: 161
In our case (Oracle 10G 64-bit to SQL Server 2005 64-bit), to make the linked server work, we have to install Oracle 10G ODAC (Oracle Data Access Componet for Windows), haven't tried 11G yet But it is probably needed as well. Hope this helps.
Post #992350
Posted Thursday, September 23, 2010 7:52 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, October 19, 2013 12:40 PM
Points: 361, Visits: 508
I just finished installing Oracle client 11.2 (both 32bit and 64 bit) on one of my test servers with Win 2k8R2 + SQL 2k8R2 64bit.
Everything installed OK and tested, no need to restart the sql service.

1. Check that you installed the 64 bit version of Oracle client
registry (open regedit):
- "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\" and see if there is a folder "Oracle in OraClient11g_home1"
- "HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\" exists with subfolders

- if it is 32bit you should have entries in Wow6432Node: "HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\Oracle in OraClient11g_home1" and "HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE"
- alternatively you could try the "Import and Export Data (32-bit)" wizard and see if the provider is listed meaning you have installed 32-bit

2. How did you install the components? did you select custom install on the first screen?
- run Oracle Universal Installer and review Installed products to check you haven't missed anything

Let me know if you need more help with this.

Links: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
(click on Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64) )

Kind regards,

Hrvoje Piasevoli


Hrvoje Piasevoli
Post #992428
Posted Friday, September 24, 2010 8:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
I am getting the below error:

TITLE: Distributor Properties
------------------------------
Oracle server instance 'Instance1' cannot be enabled as a Publisher because of the following error:
------------------------------
ADDITIONAL INFORMATION:

Unable to run SQL*PLUS. Make certain that a current version of the Oracle client code is installed at the distributor.
For addition information, see SQL Server Error 21617 in Troubleshooting Oracle Publishers in SQL Server Books Online.
(Microsoft SQL Server, Error: 21617)
Post #992813
Posted Friday, September 24, 2010 9:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, October 19, 2013 12:40 PM
Points: 361, Visits: 508
I really can't be of much help with the replication configuration, but you should be able to solve the first issue:
I still can't see the oracle provider under LinkedServer---->Providers within Sql server management studio.

Is this still an issue? If it is, I'd first solve that one.


Check for permissions on Oracle directories for the sql service account in use:
BOL: "The account under which the SQL Server service on the Distributor runs must be granted read and execute permissions for the directory (and all subdirectories) in which the Oracle client networking software is installed."


Hrvoje Piasevoli


Hrvoje Piasevoli
Post #992879
Posted Friday, September 24, 2010 10:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
I can see the oracle provider under the providers list and also I gave permission to the oracle folders to the sql service account and my account, even then I am still getting the same error. I do'nt know what else to do to resolve this error. Thanks.
Post #992935
Posted Sunday, September 26, 2010 6:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
Any ideas? If I can get some article to refer to for doing this that will be great! Thanks.
Post #993401
Posted Tuesday, September 28, 2010 9:23 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, October 19, 2013 12:40 PM
Points: 361, Visits: 508
Hello again!

Out of curiosity, I've managed to enable Oracle replication and it works OK.

Follow the steps in this article:

http://msdn.microsoft.com/en-us/library/ms151243.aspx

If you want contact me freely.


Best regards,

Hrvoje Piasevoli


Hrvoje Piasevoli
Post #994575
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse