We are looking for a solution where our application is able to either write to two databases on separate servers in separate data centers or have Oracle manage this synchronization of data for redundancy. We need the application to be up 24x7 so we were looking at Data Guard originally.
Current architecture in test is:
Application 1 (A1) and Database 1 (DB2) are on the same server. Application 2 (A2) and Database 2 (DB2) are on another server. If the A1/DB2 Server fails, corosync switches everything to A2/DB2 and Oracle bidirectional streams are set up between the databases to sync the data. However, the streams are not reliable and seem to break and we have issues with sequence numbers, and streams is to be de-supported (GoldenGate is supposed to be the replacement?)
We are looking at re-architecting this solution but we would like something that either writes to both databases (Advanced replication or GoldenGate?). We were considering Data Guard (11g) but I am not sure how the application would know to write to the standby if there was a failure on the primary or if we do a switchover. So if we have 4 servers now (A1, A2, DB Primary, DB Standby) and A1 went down and switches to A2, could I name the SIDs the same so that A2 writes to the either primary DB depending on which server it is on? I hope my question makes sense, the complexity and options of the HA solutions is confusing. I'd prefer something that does not require manual intervention on the db side as well.
We need the application to be up and running always and not lose any data. It is a small fast logging application but if it is down, users can't log into their applications.
Thanks for any feedback on the best solution (Data Guard, Advanced Replication, RAC, GoldenGate, bidirectional streams?)