Hi to all
I would like to hear from you about the advantages and disadvantages on the available Oracle replication options, particularly for Oracle 9i Standard Edition (SE).
Currently I am using Materialized view for 353 tables at every 5 mins interval daily (Fast refresh), with some tables records up to 1 million counts. As of now, database operation wise is still good. However, as the table records size increases in the near future, this may be a performance issue.
Thus, would like to know if there are other options that I can adopt in the near future so as to improve on efficiency.
So far that I know are:
1) Materialized views - May have efficiency issue in the near future as tables size increase daily.
2) Oracle streams - Not available for SE. No downtime available to shutdown the DB in order to upgrade to Enterprise Edition (EE).
3) Multi-Master Replication - replication is not instantaneous and update collisions may occur?
4) Data Guard - High cost.
Thanks to all.
What is the purpose of replicating data? Data Guard is designed to create a standby database for failovers. Multi-master replication involves having multiple databases running simultaneously and replicating changes back and forth. Those are significantly different use cases than what you'd generally use materialized views for.
What OS are you on ?
What is the replication for ? To be used by an application at a remote site, or for DR purposes in case of primary database failure ?
Why can't you upgrade to a supported database ?
Why can't you get downtime ?
Note that you can run a standby database without using dataguard. Just ship the logs using rsync and "recover database using backup controlfile until cancel;"
I'm in the semiconductor industry where chips are being processed every minute...
My Oracle 9i DB (SE, linux OS) willl need to send the replicate data from these 353 tables to another Oracle 11gR2 DB (EE, Win Server 2012). From here, users will then access this 11gR2 DB for their own reporting purpose.
My Oracle 9i DB is on linux OS. Destination DB is Oracle 11gR2 DB on Win Server 2012. These replicated data from those 353 tables will be used by users for their own reporting purpose. Users usually use Excel and MSQuery.
I cannot get approval for downtime because I recently just upgrade the destination DB to Oracle 11gR2, it was on Oracle 188.8.131.52 (Win Server 2003) previously.
at the risk of sounding like a product advertisement: There is a product called dbvisit replicate that works with SE (from 9.2 and up) and is just a little extra cost as an alternative to goldengate replication.
The reason is because many others databases are linked to this "second database", from here users will then do their reporting. So this "second database" is like a central piece.
*Besides, this database architecture was actually designed by some of my predecessor... many many years back I guess..Can't explain why things happen the way they are now to you.. I'm actually the new DBA in the house.. ^^
Edited by: moslee on Feb 7, 2013 11:51 PM
MViews are working now. Why do you want to replace them ? Maybe you can just change job scheduling or run in parallel ?
Your tables are very small, so if you don't believe MViews are suitable I would recommend upgrade to 11g EE and use Streams.
As your destination database is a "hub" with data coming from multiple sources dataguard is not suitable. Multi-Master replication is not used much anymore.
Actually, all the MV are working fine now. We are just taking proactive measures.
The reason for this is because the size of those 353 tables will only be getting bigger and bigger in the near future. If our company decide to increase production by two-three fold in the near future, naturally the size of those tables records count will increase.
But you do have a point there, we will continue to monitor the situation. Thanks for your sharing.
With Standard Edition, using Oracle supplied facilities your only alternatives to Materialized View replication are Streams or Change Data Capture, using synchronous capture. You cannot use the redo based capture for Streams or CDC unless you upgrade your licence to Enterprise Edition. Multi-master replication is also EE only. My own experience of this is that the synchronous capture can have a noticeable impact on the performance of the source DB, whereas MV replication has none. I would look at alternatives only if you find that an increased volume of changes does slow down the refresh too much.