X

Maximum Availability Architecture – Oracle’s industry-leading set of database high availability capabilities

  • July 2, 2014

Oracle GoldenGate Active-Active Part 2

Nick Wagner
Product Manager

My
last post ( https://blogs.oracle.com/MAA/entry/oracle_goldengate_active_active_part )  focused on whether or not an application's database structure was set
up sufficiently to perform conflict detection and resolution in active-active
GoldenGate environments. Assuming that your application structure is ready,
I'll now explain how to actually prevent conflicts from happening in the first
place. While this is ideal, I don't think conflict prevention is something
we could ever guarantee... especially when a fault or hiccup occurs in either
the database or GoldenGate itself.  

Let's break up conflicts into 3
types, based on the DML: 

1. Inserts

2. Deletes

3. Updates 

1. Insert conflicts typically occur
when two rows have the same primary key or when there are duplicate unique keys
within a table. 

· Two rows
with same primary key:
To address
these cases we could have primary keys generated based on a sequence value,
then set up something like alternating sequences. Depending on how many nodes
or servers are in the environment, you could use an algorithm that starts with n and increments by N
(where n is the node or server number and N is the total number
of nodes or servers). For example, in a 2-way scenario,  one  side  would  have  odd 
sequence  values  (start with 1 and increment by 2) and the other would have
even sequence values (start with 2 and increment by 2). 

· Duplicate
unique keys
: Avoiding conflicts in tables that have duplicate unique keys
is a little trickier, and sometimes must be managed from the application
perspective.  For example, let's say for a particular application that we
have a table that contains login information for an account.  We would
want the login name to be a unique value.  However it is possible that two
people working on two different servers could attempt to obtain the same login
name.  These kinds of operations can be eliminated if we restrict new
account creation to a single server, thereby letting the database handle the
uniqueness of a column. 

2. Delete conflicts are usually nothing to worry about. In most cases, this
occurs when two people are attempting to delete the same record, or when someone
tries to update a record that has already been deleted.  These conflicts
can usually just be ignored.  However, I typically recommend that
customers keep track of these types of conflicts in an exception table, just to
make sure that nothing out of the ordinary is occurring. Once you’ve confirmed
that things are running smoothly you can eliminate the exception mapping and
just ignore the conflicts completely. 

3. Update conflicts are definitely the most prevalent.  These conflicts
occur when two people try to update the same logical record on two different
servers.  A typical example is when a customer is on the phone with
support to change something associated with his or her credit card. At the same
time, the customer is also logged into the account and is trying to change his
or her address.  If these activities occur on two different servers and
the lag is high enough, it could cause a conflict. In order to reduce or
eliminate these conflicts there are a few best practices to follow: 

1) Reduce
the Oracle GoldenGate (OGG) lag to the lowest level possible. 
There
are a few knowledge tickets on this. The master note is Main Note - Oracle
GoldenGate - Lag, Performance, Slow and Hung Processes (Doc ID 1304557.1)

2) Logically
partition users based upon geographical regions or usernames. 
For
example, when all users in North America access one server, and users in Europe
access a different server, the chance of two people updating the same logical
record on two different machines is greatly reduced.  Another option is to
split up the users based on their usernames. Even something as simple as setting
up usernames A-M to log into one server and usernames N-Z to log into another
server can help reduce conflicts.   The reason this helps is related
to my next point...

3) Set up Session
Persistence time.
IP or Session Persistence is the ability of a load
balancer or router to keep track of where a connection is sent. In the event
that a connection is lost, disconnected, etc, and a user attempts to reconnect
or log back in, the connection will be sent to the same server where it was
originally connected.  Most sessions have a time value that can be
associated with this persistence. For example, if I set my session persistence to
10 seconds, then any time a session is disconnected or killed, the user will be
sent to the same server as long as he or she logs back in within 10 seconds. 
This is ideal for Oracle GoldenGate environments, where there would be lag
between the different databases. In an ideal situation you would set this session
persistence time value to be twice the average lag or 20 seconds – whichever is higher.  This allows a user
who is filling a shopping cart or booking a reservation to maintain a
consistent view of the data, even in the event of a client or network
failure. 

By using these methods, the number of conflicts that actually occur can be
drastically reduced, leading to a happier end user experience.  But even
with the best intentions and preparation, not every
conflict can be avoided. In my next post I will cover how to resolve such unavoidable
conflicts. 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.