May 1, 2012 at 7:57 am
Hi
We are having problems regarding Transactional replication of data from a Oracle Database 11g to a MS SQL Server 2008R2. The Issues is regarding refreshing data from 3 Oracle Materialized views (cannot be changed to normal tables or views).
We are on a daily basis refreshing the 3 Oracle Materialized views from a Oracle TOAD Client version 11, and normally the data will be available in SQL server subscription Database in around 5-10 minutes. Daily it is around 1 million new records that is replicated to MS sql server.
The problem is that in the last 1 month, data on MS sql server most days are very slowly replicated or sometimes not even being replicated at all.
When data is replicated it sometimes takes 2-3 hours. But we are not getting any warnings or errors about slow replication or that we are not able to replicate. We have not seen any other performance issues on the 2 servers.
If we are deleting the replication setup on MS sql server (Tansactional Oracle publication and subscription), and are setting it up again, we are able to make a new snapshot and loading data from Oracle in around 15-20 minuttes. But the next times data needs to be refreshed the issue can occur again.
We have tried setting the replication up on new sql server 2008r2 server, but we are having the same issues there.
Can anyone help us making the Transaction replication work as it did earlier? Or give us a hint about what is making the problems?
Best Regards
Cdamgaard
May 1, 2012 at 9:02 am
If the Oracle materialized views are fully refreshed once a day I would probably go with snapshot replication as opposed to transactional replication.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply