I would like to share my thoughts with the Oracle DBAs and would welcome any guidance that would help me further in achieving the challenging task ahead. My current environment is 9i release 2 on AIX 5.3. I have an OLTP database with heavy trans rate, average 300 transactions per sec. The application type is online banking, responsible for buying and selling stocks online to thousands of users via the Internet. Concurrent users are exceeding 10,000. Application architecture includes Oracle 10g AS clusters accessing a single Unix database. The scope of discussion is, however, relative to the database only. DR is being prepared with extensive investment; the DR site is about two to three kilometers away with dedicated leased line; network latency/bandwidth will not be an issue between the prime and DR site. Dataguard high protection option is sought because no loss of data is tolerable.
Now comes the difficult question: With all of the investment that will be done, it does not make sense not to leverage this cost, hence we are looking into options of using the DR infrastructure as an active-active scenario. The 10g AS cluster at DR can reference the database, however what is required is a way to use both databases (primary and DR) as active-active. Dataguard does not provide that (it does have logical standby for reporting but that is not the point, we need full active-active). Streams I guess support asynchronous replication. That leaves the option of the Advanced Replication feature, which supports synchronous two-way replication.
Has anyone deployed Advanced Replication for such a critical app? The data is not segmented like East Coast, West Coast, or each region has its own data like in a department store. Its a shares accounting apps where traders trade on the Internet and traders will come to both databases (primary and DR if Advanced Replication enabled). Then how would the locking be handled, trans concurrency? Will a single commit now become two commits then? Will there be adverse performance degradation? Will there be changes in apps design? The database structure will be the same, so if a record is being modfied at one end, that record should be locked also at other site? How would it handle since in 9i Dataguard, you have an interconnect that handles these locks?
I have heard of a recent option on 10g rel 2, ASM Database Mirroring, that combines RAC (which so far has many nodes but one physically located DB) with DB mirroring? Is that true, and if yes is that a better solution than Advanced Replication? Will apps changes be required? Can you guide me to ref docs for such an option? Is the option stablised?
You could argue that what we require is a DR by itself plus the ability to use DR as active to load balance and leverage the cost. So simply put, for our high OLTP banking apps, we need to have two DBs at different locations (close) being active at the same time. What could be the problems? Can we do this without changing a code? Does Oracle tech offer this and how? What about ASM in 10g with 10g RAC, in that scenario is the ASM mirrored DB actually used?
Interesting scenario, and not one I have come across before. Your brief description suggests that your database/application are critical for operation and in the event of any disaster you need to be able to rely on your DR site 100%. If this is your case, then switching your environment to an "active-active" scenario may compromise what you are trying to achieve with your DR project.
I have some experience with multimaster replication and it did prove difficult to manage, even in simple situations. I would guess in your situation it would be even more difficult to achieve. It is not the road I would pursue. The investment in DR is just so that your business can rely on your second site.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Harish Harbham
SOA and Integration expert Harish Harbham explains how to transfer data from Oracle to MS SQL Server. Continue Reading
Oracle expert Harish Harbham explains how Oracle Streams can be used for replication. Continue Reading
Expert Harish Harbham answers the question, "How do you convert SQL Server 2005 database into 0racle 10g?" Continue Reading