October 4, 2013 at 1:36 am
Hi
I am not sure how to start this. I was given a task to start replication from Sql server 2008 R2 to Oracle 11g
I have tried google, I couldn't understand clearly where to start.
What permissions required on oracle and what is to be done at Oracle end to support replication.
The same from sql server side?
Can anyone give details step by step?
thanks in advance.
October 4, 2013 at 2:29 am
Hi,
I had to set this up a few weeks ago (SQL2008R2 to Oracle 11g Subscriber).
Some tips:
- You will need to install the oracle client tools on the distributor and configure the tnsnames.ora file (adding the oracle servers inside) - (http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html) - This is an old link for the ones I used. A friend of mine mentioned that you might need to configure some DLL's in order for it to work with 11g as they currently point to 10, but this was all done already so I didn't need to do this.
- Add a linked server between the distributor and the Oracle Subscriber (the oracle subscriber needs a username and password - this is used by the linked server)
- Configure your distributor as normal
- configure publication but with the following caveats
- @sync_method should be "character" in sp_addpublication
- @enabled_for_het_sub should be "True" in sp_addpublication
- @pre_creation_cmd should be "none" in sp_addarticle (I recommend ensuring that the schema/tables are already created on the Oracle end)
- @schema_option should be "0x0000000000000000" in sp_addarticle
- @Destination_table and @destination_owner should be configured in sp_addarticle
- Configure your subscriber but with the following caveat
- @sync_type should be "none" in sp_addsubscription
- @subscriber should be the name you defined in the tnsnames.ora file in sp_addsubscription
One other thing you might need to do is tick the "Allow InProcess" option for the OraOLEDB.Oracle provider on the distributor.
It took a bit of playing around when I configured this but the above are the pain points I personally encountered.
Good luck 🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply