Wednesday Jul 02, 2014

Oracle GoldenGate Active-Active Part 2

My last post ( )  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. 

Thursday Jun 12, 2014

Oracle Data Protection: How Do You Measure Up? - Part 1

This is the first installment in a blog series, which examines the results of a recent database protection survey conducted by Database Trends and Applications (DBTA) Magazine.

All Oracle IT professionals know that a sound, well-tested backup and recovery strategy plays a foundational role in protecting their Oracle database investments, which in many cases, represent the lifeblood of business operations. But just how common are the data protection strategies used and the challenges faced across various enterprises? In January 2014, Database Trends and Applications Magazine (DBTA), in partnership with Oracle, released the results of its “Oracle Database Management and Data Protection Survey”. Two hundred Oracle IT professionals were interviewed on various aspects of their database backup and recovery strategies, in order to identify the top organizational and operational challenges for protecting Oracle assets.
Here are some of the key findings from the survey:

  • The majority of respondents manage backups for tens to hundreds of databases, representing total data volume of 5 to 50TB (14% manage 50 to 200 TB and some up to 5 PB or more).
  • About half of the respondents (48%) use HA technologies such as RAC, Data Guard, or storage mirroring, however these technologies are deployed on only 25% of their databases (or less).
  • This indicates that backups are still the predominant method for database protection among enterprises. Weekly full and daily incremental backups to disk were the most popular strategy, used by 27% of respondents, followed by daily full backups, which are used by 17%. Interestingly, over half of the respondents reported that 10% or less of their databases undergo regular backup testing.

 A few key backup and recovery challenges resonated across many of the respondents:

  • Poor performance and impact on productivity (see Figure 1)
    • 38% of respondents indicated that backups are too slow, resulting in prolonged backup windows.
    • In a similar vein, 23% complained that backups degrade the performance of production systems.
  • Lack of continuous protection (see Figure 2)
    • 35% revealed that less than 5% of Oracle data is protected in real-time.
  •  Management complexity
    • 25% stated that recovery operations are too complex. (see Figure 1)
    •  31% reported that backups need constant management. (see Figure 1)
    • 45% changed their backup tools as a result of growing data volumes, while 29% changed tools due to the complexity of the tools themselves.

Figure 1: Current Challenges with Database Backup and Recovery

Figure 2: Percentage of Organization’s Data Backed Up in Real-Time or Near Real-Time

In future blogs, we will discuss each of these challenges in more detail and bring insight into how the backup technology industry has attempted to resolve them.


Musings on Oracle's Maximum Availability Architecture (MAA), by members of Oracle Development team. Note that we may not have the bandwidth to answer generic questions on MAA.


« February 2015