jump to navigation

Oracle to SQL Server Replication-Design considerations May 21, 2013

Posted by msrviking in Configuration, Data Integration, Design, Heterogeneous, Integration, Replication.
Tags: , , , , , , ,
add a comment

I am thinking I have covered all and best of the points for one to configure a transactional replication between Oracle and SQL Server in all my previous posts. In this post I would like to give my 2 cents on design considerations and limitations of Oracle publishers that I had to keep in mind during this exercise. There is a neat list of other points published in this Technet article.

  1. Configuration of Oracle Publisher with options

    Per the above article I learnt that the Oracle publishing can be done using the Oracle Gateway and Oracle Complete options. Few things that make difference these two options is that Oracle Gateway provides better performance but doesn’t let multi-transactional publications of a single to-be or published table. Whereas in the case of Oracle Complete option the table can be published in several publications, but lesser performance.

    I tried digging in Google to see on how these two options work as described, because I didn’t try on the setup I built to find the difference. I didn’t find any reliable sources that talk about difference in detail but one of the posts by Richard Douglas (Editor in Chief of SQL ServerPedia) gives a short description “Gateway is for high performance replication solutions. Complete is when you want to transform your data or to do row filtering”. I might have to try and see what it means in here.

    I remember seeing row filtering in a transaction replication using the option of Oracle Gateway, and I am yet to find out on how this is different when Oracle Complete is chosen. For now I shall park for future tries on this limitation and share my findings.

  2. It’s quite obvious with this point (supports few objects) because the similarities that Oracle and SQL Server have at object level implementation are few. So,

    What are the objects that are supported?

    1. Tables
    2. Index-organized tables
    3. Indexes
    4. Materialized views (as tables)

    What are those objects that cannot be replicated, but can exist on published tables?

    1. Domain-based indexes
    2. Function-based indexes
    3. Defaults
    4. Check constraints
    5. Foreign keys
    6. Storage options (tablespaces, clusters, etc.)

    What are the objects that cannot be replicated at all?

    1. Nested tables
    3. Packages, package bodies, procedures, and triggers
    4. Queues
    5. Sequences
    6. Synonyms

    Okay with this list we know what can be replicated, what cannot be replicated? I picked the list from the above post, but was hunting to find out why we can’t replicate these objects. One simple reason that keeps coming to my mind is that “some of the above objects in the ‘cannot’ list don’t have equivalents in SQL Server”. Okay, so aren’t there workarounds for these objects? For example Packages have bundle of programmed objects like packaged variables, cursors and few others which have to be converted through a specific process, and this specific process can’t be through Replication. That exercise could be migration of objects from Oracle to SQL Server, and surely can’t be part of Replication topology. Does this apply for simple objects also? I don’t have any technical reason to explain for this too so concluding it with my 2 cents. Meanwhile I shall see if I can find better “reasons”.

    Here is a link from Microsoft on what it all means to convert some of the above listed objects. This was one of the reasons that I helped me to conclude logically that this is part of migration from Oracle to SQL Server and can’t be part of Replication at a conceptual or architectural level.

Since my setup was to test the new Oracle Publisher I didn’t have to keep tab on other design considerations, but I am sure I will have to deal with others too when this gets on to full deployment mode than a POC.

I hope this short post helps you folks out there, and happy reading.

The usual ask – please feel free to share your thoughts or comments.



Oracle to SQL Server Replication – Configure Transactional Replication w/ Pull Subscription May 16, 2013

Posted by msrviking in Configuration, Data Integration, Heterogeneous, Integration, Replication.
Tags: , , , , , ,
add a comment

This post is going to be short because I am thinking I shouldn’t give my bit of explanation on how to configure a Transactional Replication in SQL Server against Oracle Publisher because there is no difference that I could share.

Instead let me share few links that are well explained by few other SQL Server experts on how to configure SQL Server Transactional Replication except that you have to pick already configured Oracle Publisher in the publication list.

So here are those for your reference and as guidance.

The last link is the source for me to work on the design, implementation and troubleshooting of Oracle Publisher – SQL Server Transaction Replication w/ Pull Subscription. In few of my concluding posts of this series I shall talk about the design, deployment and issues I had faced.

Happy reading.


Oracle to SQL Server Replication-Configure Oracle Publisher May 15, 2013

Posted by msrviking in Configuration, Data Integration, Heterogeneous, Integration, Replication.
Tags: , , , , ,
add a comment

In today’s post as part of this series I will share few steps I diligently followed to configure Oracle publication. As first step certain pre-requisites have to be addressed on SQL Server box and the Oracle instance.

1. Installing Oracle client and OLE DB provider on SQL Server system

I am not too much of an Oracle guy, but have fair deal of experience to identify annoying issues that rise from connecting between SQL Server and Oracle. So make sure you have the Oracle client and OLE DB provider installed appropriately on the SQL Server box.

I didn’t play much with changing environment variable paths, tnsnames.ora, so I let the installations happen all by default path, and the end I wanted SQLPLUS let me connect to the Oracle instance from the box.

So did it happen with me? Yes, after a bit of tussle I did manage to have Oracle connected and that was the first relieving moment only because I knew if I cross this first major hurdle others (configuring publisher, replication) would be easy steps. Here is the snapshot of the SQLPLUS that gave me a bit of smile, and the next one was in waiting (Oracle Publication).

There is one more tiny part to be done on SQL Server box, is to set read and execute permissions to the Oracle (client and provider) folders against the service account that runs SQL Server, and for obvious known reasons.

2. Create Replication administrative user, schema

I used the commands from this blog post and the post has decent inline comments on what each command does and what does it expect from you when you run this on Oracle instance.

Remember, that you logon using sysdba privileges (per recommendation), and in my case I managed to get hands on the sys login of the Oracle instance. The script has bunch of commands that needs to be executed on SQL Server instance too and that’s to configure Oracle Publication, Distributor and Replication with Pull Subscription.

I didn’t run all those commands because I wanted to try through the GUI way of configuring the publication. So feel free to pick your path, however make sure you are following each step until configuring the Replication administrative user, creating a user schema, and grant appropriate permissions. If you miss any you might have trouble in making the Oracle side of publication work properly and I am sure you will be clueless for a little while and may revisit the steps you have executed.

This step was not difficult and upon completion I know I was getting into the world of troubleshooting SQL Server to Oracle connectivity.

I shall paste the steps – commands for your quick reference and are exact copy from the above link.

  • Logon to ORACLE database as SYSDBA and follow the below steps
    • Create Tablespace to Store SQL Server Replication Metadata Objects

<Sample Script>

Create Tablespace <SQL_REPLUSER_TBL>

datafile ‘<Path>\<SQL_REPLUSER_TBL>.dbf’
size 512M autoextend on
next 512M maxsize

</Sample Script>

  • Create SQLReplUser on the ORACLE Database and make the tablespace created in step 1 above as the user default tablespace

<Sample Script>


— Copyright (c) 2003 Microsoft Corporation

— File:

— oracleadmin.sql

— Purpose:

— PL/SQL script to create a database user with the required permissions

— to administer SQL Server publishing for an Oracle database.

— &&ReplLogin == Replication user login

— &&ReplPassword == Replication user password

— &&DefaultTablespace == Tablespace that will serve as the default tablespace for the

— replication user.

— The replication user will be authorized to allocate UNLIMITED space

— on the default tablespace, which must already exist.

— Notes:

— This script must be run from an Oracle login having the authorization to

— create a new user and grant unlimited tablespace on any existing tablespace. The

— login must also be able to grant to the newly created login the following authorizations:

— create public synonym

— drop public synonym

— create sequence

— create procedure

— create session

— create table

— create view

— Additionally, the following properties are also required for transactional publications.

— create any trigger

— All of the privileges may be granted through a role, with the exception

— of create table, create view, and create any trigger. These must be

— granted explicitly to the replication user login. In the script, all grants are

— granted explicitly to the replication user.

— In addition to these general grants, a table owner must explicitly grant select

— authorization to the replication user on a table before the table can be published.


‘User to create for replication: ‘;

‘Replication user passsword: ‘

‘Default tablespace: ‘;

— Create the replication user account

BY &&ReplPassword
TABLESPACE &&DefaultTablespace
ON &&DefaultTablespace;

— It is recommended that only the required grants be granted to this user.

— The following 5 privileges are granted explicitly, but could be granted through a role.






— The following privileges must be granted explicitly to the replication user.



— The replication user login needs to be able to create a tracking trigger on any table that is

— to be published in a transactional publication. The CREATE ANY privilege is used to

— obtain the authorization to create these triggers. To replicate a table, the table

— owner must additionally explicitly grant select authorization on the table to the

— replication user.

— NOTE: CREATE ANY TRIGGER is not required for snapshot publications.



</Sample Script>

  • Grant Select privilege on table you would like to publisher to <SQLReplUser> created in the step (b) above

<Sample Script>

–Execute the below statement from SQL*PLus

<Owner>.<Table> to <SQLReplUser>;

</Sample Script>

3. Configuring Oracle Publication

This is the one I did multiple times, and when I say multiple times it’s for these reasons

  • Connecting to Oracle instance is very demanding process and anytime it could bounce back as a surprise failure although the instructions are followed to configure the Oracle client or OLE DB providers
  • The failed connectivity errors raised by Oracle through OLE DB provider will be gobbled by SQL Server. What does that mean? If there is logon failure, Oracle instance /service id resolution failure, or user schema access issues – all these have one type of error although there are bunch defined on MSDN.

So watch out on what type of error you could be getting and what reason. It’s almost like reading the errors through your inner sense, huh. That was a tough ask so I requested a team mate to build a small .NET code using the OLE DB provider and anything I do on the wizard I imitated on the code and I was able to find the cause of errors and made things work quicker. Thanks to the team mate for pitching in on right time to fix some “hidden errors”.

Here are the snapshots that would you see when configuring Oracle Publication.

  1. Configure Distributor

    After few screens which are default for configuring a distributor with any type of replication you will have to end with this step at the end. I opted to have SQL Server box which was supposedly to be subscriber as distributor.

  2. Configure Oracle Publication

    From here you would see a configured Oracle Publisher with the name that is mentioned as part of Server Instance. This is one of the most important step which confirms that the Oracle Publication is configured on SQL Server. I was relieved after configuring the publisher, and this didn’t come that easy at the first attempt.

    I hope all these help for configuring the Oracle Publisher, and the next step would be a Transactional Replication with a Pull Subscription. I shall share configuring Transactional Replication details against the Oracle publication in the upcoming posts, however there is nothing new once the publisher is done and is similar with that of SQL Server publication.

    I am also intending to share some important tips on design considerations, troubleshooting and performance of Oracle Publication.

    Until then, happy reading.


Oracle to SQL Server Replication-A bit of insight May 13, 2013

Posted by msrviking in Configuration, Data Integration, Heterogeneous, Integration, Replication.
Tags: , , , , , ,
add a comment

In last couple of posts in the series, I had shared an approach and also appropriate pre-requisites for setting up an Oracle Publisher based Transactional Replication setup. Today in this post, I shall talk about how different it is to replicate data from SQL Server and Oracle.

Just before I started working on this assignment I recollected several of my past experiences where I had configured SQL Server Replication setups in all the topologies, and based on that I safely presumed that it won’t be that different with Oracle Publisher too. Well, I was partly right, SQL Server replication setup was, and is easy because the wizard helps one to configure publisher, distributor and replication quickly. I would think and also voice that its same for what I had worked on in this assignment except that I had to establish successful connection with Oracle and then let SQL Server do its job of configuring the publisher, distributor and Transactional Replication (pull-subscription).

The difficult part was having the appropriate Oracle client and OLE DB drivers installed, and please refer to my post which has links to the downloads from Oracle Technology Network (OTN).

I would like to share few internals on Oracle Publication from SQL Server, and without this the significance of configuration such type of publication wouldn’t be much.

1. Configuring Oracle Publisher is a new feature starting from SQL Server 2005, and works with Oracle 9i onwards.

Before SQL Server 2005, I remember well we had used SQL Server 2000 DTS packages to pull data to synchronize data between SQL Server and Oracle, and this way got replaced with Oracle Publication using Replication, and this is nice. The use of DTS package wasn’t straightforward because we needed few staging tables to capture the data because of DML change in Oracle published (source tables) and these were to be propagated into SQL Server tables through an UPSERT method. Although this worked well, but had the whole bunch of problems associated initially, before the system settled with that type of data changes that need to be captured. And the with this new feature in SQL Server 2005 I think we have similar setup happening, but everything is handled by SQL Server, and that’s why I said its pretty neat and nice.

2. Data from Oracle could be synched up with SQL Server either through Snapshot Replication or Transactional Replication.

Please note the difference in here between these two type of Replications when configured with Oracle Publisher. At a configuration and working level its almost similar with SQL Server Publisher yet I thought I will put up couple of notable points for anyone’s quick reference.

– A snapshot replication takes snapshot of published tables and applies them as a whole on the subscription end. Simple, eh, and this is run by a Snapshot Agent job which gathers the table data in the form of schema scripts, and publishes for subscriptions to consume. No “change tracking triggers” are created on the publisher tables.

– A transactional replication tracks change in each row of the published tables, through “triggers” created on the tables. The triggers that are created on published tables captures the DML changes and pushes the data into change tracking table and the Log Reader agent reads, and moves the changed data for publishing to subscribers through an intermediary Distribution database.

So transactional replication creates database triggers on the published tables and data is tracked into change tracking tables on the Oracle end. The changed data then is read into distribution database through Log Reader Agent running on SQL Server. This link has details on how the whole process happens, but let me share a snapshot of the tables that are created on the Oracle database.

Here are few snapshots of the system tables, change tracking tables and the triggers created when Oracle Publisher is configured.


Replication tables created on Oracle schema



Change Tracking Tables to track changes of data on published tables



Trigger that is created on the publisher table


With this I am thinking you should be well briefed on what happens when an Oracle Publisher is configured with Transactional Replication. Of course, I didn’t talk much on the Snapshot Replication with any pics, because I couldn’t setup one during this assignment.

In the next post I will walk you through configuration of Oracle Publisher, Distributor.

Until then happy reading, and please feel free to comment.


Oracle to SQL Server Replication-Pre-Requisites May 10, 2013

Posted by msrviking in Configuration, Heterogeneous, Integration, Replication.
Tags: , , , , ,

In the first post on this topic I had shared my tiny plan or approach on how to get the Oracle to SQL Server replication done. Today I shall talk a bit about on why I listed the steps, and also share the links of forums, blog posts I had used to work on each step.

1. Environment setup

This was the first step where I had to decide what is that I could have running on that box at an OS level, the hardware configuration I would need to run an instance of Oracle and SQL Server. So here are the specs I chose for bringing up this demo-able replication setup quickly.

  • A virtual machine, this is the fastest and best way to have POCs setup for understanding the internals and share it with teams.
  • A virtual machine with enough memory, CPU power and hard disk space. What would be the configuration for this? I knew I wasn’t showing anything on performance when there is data being replicated between systems, but a simple walk-through to understand the pain of having replication done from Oracle to SQL Server.Here are the specs,
    • Memory /RAM > 4GB
    • CPU > Dual core processor
    • Hard Disk space > 150GB
  • The VM with a an appropriate OS. While I was deciding on these specs I thought it doesn’t make sense to configure replication between Oracle and SQL Server running on the same box, and that too on Windows. So there was a little change of course of what should run where. That made me to pick Windows Server 2008 R2 to run SQL Server 2012 with SP1, and Oracle on Linux box. Now the challenge to find a box running with Linux and Oracle on top of it. I got some help because I had one of the boxes with Oracle on Linux, running for another customer so I had to just use it after few requests with the team.

Here is the link that tells you about software and hardware requirement to run SQL Server 2012 SP1 Developer Edition (this edition has Replication Features). We know about this link, nevertheless I thought sharing won’t cost me anything and could help anyone of us.

2. Installation of Oracle

As mentioned earlier I wanted to have the setup with Oracle on Linux and “Oracle edition that supports replication setup”. Just take a note of the last sentence in quotes, by saying that I wanted Oracle Standard /Enterprise Edition to support Oracle Gateway option which is part of these editions, and this option is not available in Oracle Express Edition. Oracle Gateway needs to be installed separately without any license in the same box of Oracle instance or a different box. I didn’t want to get into too many installations for a quick POC hence chose an Oracle Enterprise edition instance which was already up and running on Linux, 64 bits environment. Made my job precise and easy, eh!

All this said I shall still share the links on the Oracle Editions, and the license information /feature availability per Edition.

Oracle Standard Edition (SE), and Enterprise Edition (EE) features availability


Oracle Express Edition (EE)


Another point that I realized although didn’t or probably won’t be of great importance is that you have only an Oracle 32-bit Express Edition on Windows, and no 64-bit is available. I checked, and verified that there is no such installation. Of course I didn’t have to bother much because I dropped the idea of running an Oracle XE on Windows, rather a EE, 64 bit on Linux. Nevertheless, in case you are hunting for one for whatever reason I am thinking this should help you to stop the mad hunt. Here is the link where it certifies that no 64-bit.


3. Installation of SQL Server

This was simplest because this was done a zillion times earlier, except that I had to be choosy in features list when I was installing the SQL Server instance on Windows Server 2008 R2 64 bit. The feature I had to make sure that was available was Replication as part of installation, and that too was done with ease. The pre-requisites were taken care when I chose the OS, Hardware for this piece of work, but make sure  you have latest service pack. I haven’t seen anything break when I tried with SQL Server 2012 w/o SP, though.

4. Configuration of Oracle publisher and SQL Server Transactional Replication – Subscriber

Under this heading I won’t write about how the configuration was done but the intent is to list down the pre-requisites.

  • What is needed before Oracle publisher is configured?
    • Oracle Database Client (Oracle Database 11g Release 2 Client) on Windows box where SQL Server 2012 instance is running and you could get this from here. Please remember the bit version you would like to download, and trust me this is the most important step to establish a connection from SQL Server on Windows with Oracle on Linux. You mess or miss this step for whatever reason you will realize that it was simple step, and bad mistake.
    • 64-bit Data Access Components to be installed on SQL Server box. You will get that installable from here, and you will want to choose the right one based on your environment. I had to pick this one 64-bit ODAC 11.2 Release 5 ( for Windows x64.
  • What is needed for SQL Server Transaction Replication – Subscriber setup? Primarily everything done in the first step is good enough to establish a connection with Oracle, and then configuration a publication – subscription (pull) is quick.

I shall share finer details under each of the configuration steps and the intent in this post was to tell what you should have installed before you start putting the Oracle Publisher – SQL Server Transactional Replication – Subscriber in place.

At the end this link – Configuring an Oracle Publisher talk about same steps I have put in here, but falls short of pointing to right places of appropriate downloads.

I hope these help, and please feel free to comment.


Oracle to SQL Server Replication – First steps May 6, 2013

Posted by msrviking in Configuration, Heterogeneous, Integration, Replication.
Tags: , , , ,
1 comment so far

There was a request in my shop to setup a Transactional Replication on SQL Server 2012 and the publisher would be Oracle. A known setup in SQL Server – SQL Server, but not straight if Oracle is the publisher. The setup was to be such a way that nothing should be done on Oracle end, and everything should be configured from SQL Server. Sounds good and easy, eh! This was my first thought, but post this exercise I realized that it definitely isn’t that straight forward too and needs a bit of planning, careful installations of dependent features, and at the end has helped me refresh Replication setup knowledge which I didn’t apply for last few years.

In this post I shall share steps to configure transactional replication on SQL Server 2012, and the publisher is Oracle 11g enterprise edition running on Linux.

As mentioned earlier firstly I had to put in my thoughts to see what are the steps are involved, what are the other features or dependent components I need to install before I configure replication. I will try my best to post in few images of installation of any important step, otherwise it would be more of text writing.

Here is the high level activity I put in for sake of clarity and to know where I am. This helps and helped me to judge on how much time would I take to configure the whole setup, whatever in-depth experience I have. A bit of planning and picking the right steps (an approach) did help me avoid some rework.

  1. Environment setup
  2. Installation of Oracle
  3. Installation of SQL Server
  4. Configuration of Oracle Publisher
  5. Configuration of SQL Server transactional replication-subscriber

I shall share few thoughts against some of the above steps, because I realized everything requires few pre-requisites. For e.g. an environment is the box that is needed to run both SQL Server and Oracle, installation of appropriate version and edition of Oracle or SQL Server, drivers and client network /connectivity tools, connecting to Oracle through SQL Server replication and publishing the tables. All sound easy and familiar but things go bad and take few hours more than expected how much so you plan and jot your points.

I shall write details in the next post as part of this series, so stay tuned.