SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication from Oracle to SQL Server


Replication from Oracle to SQL Server

Author
Message
Mh-397891
Mh-397891
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1516 Visits: 1514
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.
bigblue-283639
bigblue-283639
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 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.
hrvoje.piasevoli
hrvoje.piasevoli
Mr or Mrs. 500
Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)

Group: General Forum Members
Points: 547 Visits: 510
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
Mh-397891
Mh-397891
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1516 Visits: 1514
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)
hrvoje.piasevoli
hrvoje.piasevoli
Mr or Mrs. 500
Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)

Group: General Forum Members
Points: 547 Visits: 510
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
Mh-397891
Mh-397891
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1516 Visits: 1514
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.
Mh-397891
Mh-397891
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1516 Visits: 1514
Any ideas? If I can get some article to refer to for doing this that will be great! Thanks.
hrvoje.piasevoli
hrvoje.piasevoli
Mr or Mrs. 500
Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)

Group: General Forum Members
Points: 547 Visits: 510
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
phil.rodas
phil.rodas
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 2
SharePlex from Dell Software simply streams data from Oracle to SQL server. We can do many to one, and certain tables or schemas. Reduce the human intervention. http://software.dell.com/products/shareplex/shareplex-for-sql-server.aspx

Simplify Oracle data integration with SharePlex: http://youtu.be/IKhVs46luOU?a via @YouTube

White Papers:
• SharePlex for SQL Server subpage

Phillip Rodas
Territory Account Manager
Dell | Data Protection - Dell Software
949-754-8421 O
714-801-7445 C
949-754-1834 F
SharePlex Community
SharePlex™
If you’ve tried Oracle GoldenGate, Data Guard, CDC or Streams
only to discover they were slow, complex, too expensive, or
soon to be de-supported, it’s time to check out a better solution.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search