Replication from Oracle to SQL Server

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

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

  • 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

  • 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)

  • 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

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

  • Any ideas? If I can get some article to refer to for doing this that will be great! Thanks.

  • 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

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply