Replication between SQL Server 2012 and Oracle


Replication has 3 roles: Publication, Distribution and Subscription.
The source is from Oracle, and I want the data from Oracle push to SQL Server and not updatable.
Publication: Oracle
Distribution: SQL Server A
Subscriptions: SQL Server A ( I installedont the same Server/Instance), you can add/change it if you want.
  1. Install the Oracle client software and OLE DB provider on the Microsoft SQL Server Distributor
  2. One Oracle Database Connection name/password
  3. Install a feature of replication of SQL Server
  4. Design a shared folder and set up security (I’ll talk about it later)
  5. You can do most of jobs on SQL SSMS

1) Install the Oracle Client software, download site
    • Choose “64-bit ODAC 12c Release 2( for Windows x64 (282MB)
    • unzipp it and install by default
    • image image image
    • The 3rd chart is important but able to be changed later
    • image
    • Then it will create a file called tnsnames.ora
Be aware the settings of “PROTOCOL”, HOST (, PORT (default is 1521) ,SID or Service Name (Oracle database name)
In order to test the connection is available, go to CMD type this command
if successful, it will show "SQL>" prefix.  If not, please look at this website

2) Install Distribution on SQL Server A

Create a new Distributor

Set up Snapshot folder, I really recommend use Shared folder and UNC path. Because if you use specific driver letter path. Oracle won’t know "C:\xxxx" or "D:\xxxx" on Distribution server

It will prompt the login windows:
Server Instance: Oracle server name/Dbname. Be careful is "/", not "\"

Then chose publisher type:

Then it will show the diagram below

You can add multiple Publishers, but Publisher Oracle can only have one Distribution

i will check “Generate a script ….” for saving record later


Specify the Saved path


Then it will create a Distribution database and it belongs to one of System Databases

Look at the Linked servers, SQL server will create 2 Linked servers
One for connecting to Oracle, one for Replication Distribution used


3) Install Publication

Due to Publication role will be on Oracle side, so we need to create a new Oracle Publication instead of New Publication


Oracle Publishers should be added already once you finished the Oracle Login Windows Authentication. If not, please repeat the step above.

Then the step will be a little bit different from New Publication.
There are only two Publication types: Snapshot and Transaction, let’s add Transactional publication first

(You need a primary key for each tables on Oracle, if some of tables are not, you have to compromise using Snapshot Publication to fit your requirements)

Articles: it will take a while depends on how many data you have. The abstraction of the name is related to the names of Publication , Distribution that like the books.But it actually indicated "Tables"

Then choose your tables you want to replicate, and you can set up the properties or Data Mappings for these tables

Filter Table Rows, you can filter data to feed what the subcriptions need

Snapshot Agent:
Even if you choose Transaction Publications, it still needs to create a snapshot first to initialize the subscriptions. For this case, I set up both of options later due to I didn’t create the subscriptions yet

Agent Security:
This step will be a little tricky. For easy to set up (not recommend), you can choose SQL Agent Service Account to execute both of Snapshot and Log reader agent. (impersonate)
But also you need to add the account into the shared folder permission and folder permission
image image image
Next -> Next –> Next -> Success

As well as no we create a Snapshot publication for non-primary key tables.
The install steps are similar as creating Transactional Publication

4) Install Subscriptions

You can create it on the same SQL Server or on the other SQL server, just try it.
But the important is the folder permission

Choose distributor and Publications

Distribution Agent Location: PUSH or PULL? I won’t spend time to explain it, you can just read it on MSDN

Create a new user database before you did it. Do not choose Distribution System Database
Distribution Agent Security, it impacts some of securities for SQL server. If you use Local SQL Agent Service account to execute the Distribution but the Subscriber is on the other Server. You would use another SQL Server Login account to connect to the Subscriber server.


Initialize Subscriptions:
Immediately, if it’s a brand new subscriber, you can do it. But it doesn’t mean you couldn’t do it for existing tables, but it will cause Table locked
At first synchronization, usually it will need a snapshot initialed first.


5) Troubleshooting
"The initial snapshot for publication xxxx is not yet available"

And the subsciber has not received data yet.

  • Restart the SQL Agent Service
  • Check Job log to see if any error messages
  • Folder permission (most likely this issue), please add Agent Service account as Full control
  • Stop all the last running replication jobs and Re-Initial Subscribers to kick the old snapshot out
  • See the reports to look at Agents of Snapshot and Log reader agent

6) Extended reading

1 comment

rantoblogs said...

I have following your steps, but I have a problem when selecting the articles that I want to replicating, there is no table available to be selected, it complaining "Oracle Publisher contains no objects that can be replicated. The most common reasons for this are missing permissions and not having primary keys. At the Oracle server instance, grant the SELECT permission to account" however all the tables has primary key and I also unable to expand the article for viewing the colums.
Is Replication still available from oracle 12c to sql server 2012 ?

Newer Older