2
Scenario description -
We have two Oracle DB's A & B. Both DB's have a table test1 (customerid varchar2(50, val number). The idea is that for every customer id, the counter (stored in val) increases.
Whenever we send a file to the customer, we tag/identify it with a counter value. This is for verification on the customer side that they have received all files by checking for a break in the counter.
We have many customers in the table, each with a different counter value (depending on the number of files sent to each customer).
Our current solution -
Today, we use only one DB at a time to get these counter values from the table and periodically export the table contents.
When this active DB (suppose A) fails/crashes, we import the latest export file to the inactive database ( B ) then start using this table for getting the coutners for the customers.
Drawback of this process -
1. We loose changes of the counter values for the time between the last export and the time the DB crashes/fails. This results in duplicates of counter values to the customers.
2. We loose time in importing the data intot he standby database.
Can anyone provide a solution satisfying the following requirements -
1. The counter values are synched b/w the two databases at any given time.
2. The application should be able to increment the counter values from both the databases at any given time WITHOUT getting any duplciate counter values for a given customerid.
3. Suppose if A goes down/fails, the application should not have any problem in retreiving/incrementing the counter values in the active database B. When the failed database A comes up, the updates start flowing from B to A to bring upto "speed" / latest values and the also proogate any of the changes being made in B.
4. The ides is basically to be able to use either one of the databases or both at any given time WITHOUT any outages.
I have thought of using triggers, but the issue comes up with point 2. Also need to figure out if any of the transactions get caught in the DBA_2PC_PENDING table. If so, then how do we clear those rows out of it so that when the connection is established, these transactions get cleared ?
Also out of the box question -
Is there any other way to achive the above withoutusing database at all ? Meaning to be able to keep some counters in synch across servers and also being able to update it at all sites ?
