GoldenGate: 12c Conflict Detection and Resolution (CDR)

One of my favorite 12c enhancement for GoldenGate is its new conflict detection resolution (CDR) feature for two way replication.  In previous version, the replicat parameter files had to contain SQLEXEC commands that would query the target table before applying any DML.  This added an additional call to the database and slowed performance.

CDR provides new parameters that simplify the detection and resolution of conflicting data.  Here are some of the optional keywords that can be combined with the new RESOLVECONFLICT parameter.

UPDATEROWEXISTS
UPDATEROWMISSING
INSERTROWEXISTS
INSERTROWMISSING
DELETEROWEXISTS
DELETEROWMISSING

These can be configured to regard one data source the master and always overwrite the other.  They can utilize a timestamp column that determines the "winner" based on the most recent data.  For data such as inventory, the "delta" can be used to make changes to both sites and adjust the value rather than simply replicating it.

GoldenGate requires the "before" images to be captured for all relevant columns in the source database.  Here is how trandata can be configured for all columns in the SCOTT schema in the PDBORCL pluggable database.

GGSCI (oel1.localdomain) 103> ADD SCHEMATRANDATA pdborcl.scott ALLCOLS

2014-01-20 15:21:04  INFO    OGG-01788  SCHEMATRANDATA has been added on schema scott.

2014-01-20 15:21:05  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema scott.

2014-01-20 15:21:05  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema scott.

In addition, the extract parameter files need the GETBEFORECOLS option as a part of the TABLE parameter.  In the example below, the extract will capture all of the columns in the SCOTT.ITEMS table for each update and delete.  The before image of each of these records will be loaded into the trail file.

EXTRACT e1aa
USERIDALIAS ggsadm domain d1
LOGALLSUPCOLS
EXTTRAIL ./dirdat/aa

TABLE pdborcl.scott.emp;
TABLE pdborcl.scott.dept;

SOURCECATALOG pdborcl
TABLE scott.bonus;
TABLE scott.salgrade;
TABLE scott.items,
  GETBEFORECOLS
    (
    ON UPDATE ALL,
    ON DELETE ALL
    );

The final step is to configure the replicat parameter files with the RESOLVECONFLICT paramter.  In the example below, CDR is configured for the SCOTT.ITEMS table.  If the record exists on the target for an update, the DML_TIMESTAMP column will be compared, and the record with the most recent time will "win" for all of the columns except QTY_ON_HAND.  This column will be updated with the difference between the old and new record value on the source using the UPDATEROWEXISTS and USEDELTA keywords.

For example:  If QTY_ON_HAND is updated on both databases in a bi-directional replication setup, the change needs to be reflected appropriately on each system.  Let's say that the value on both system starts out at 22.  The value on DB1 is decremented to 18, while the value on DB2 is incremented to 30.  When the two updates pass each other and get applied on their respective targets, the records cannot simply be replicated as is.  Simple arithmetic must be applied to both sides in order to account for the change in inventory on both sides.  The "delta" for DB1 is -4.  The "delta" for DB2 is +8.  Therefore, the value on DB1 (18) will be incremented by 8 for a new total of 26. The value on DB2 (30) will be decremented by 4 for a new total of 26.  Now, rather than having a "winner", both sources of data will be equal based on the "delta".

The replicat parameter file below is set up for such a situation.

REPLICAT r2aa
USERIDALIAS ggsadm domain d2
ASSUMETARGETDEFS

MAP pdborcl.scott.emp, TARGET scott.emp;
MAP pdborcl.scott.dept, TARGET scott.dept;

SOURCECATALOG pdborcl
MAP scott.bonus, TARGET scott.bonus;
MAP scott.salgrade, TARGET scott.salgrade;
MAP scott.items, TARGET scott.items,
  COMPARECOLS
    (
    ON UPDATE KEYINCLUDING (item_name, qty_on_hand, sales_price, dml_timestamp),
    ON DELETE KEYINCLUDING (item_name, qty_on_hand, sales_price, dml_timestamp)
    ),
  RESOLVECONFLICT (UPDATEROWEXISTS,
                     (delta_combine, USEDELTA, COLS (qty_on_hand)),
                     (DEFAULT, USEMAX (dml_timestamp))),
  RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (dml_timestamp))),
  RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)),
  RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),
  RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD))
  ;

GoldenGate: 12c Credential Store Secure Login

The Credential Store is a new 12c security feature in GoldenGate that has been implemented as an autologin wallet in Oracle’s Credential Store Framework.  User IDs and passwords are encrypted in the store and, as a result, an encryption key in the connection string is no longer needed.

The default location of the store is in the ./dircrd directory of the GoldenGate software home.  If you want to change the location, you can edit the ./GLOBALS file with the following CREDENTIALSTORELOCATION parameter.

GGSCI> edit params ./GLOBALS


CREDENTIALSTORELOCATION /home/oracle/ggs/dircrd

You must exit and restart ggsci before proceeding or the file will be created in the default location.

GGSCI> exit

$ ./ggsci

GGSCI> add credentialstore

Credential store created in /home/oracle/ggs/dircrd/.

GGSCI> exit

$ ls /home/oracle/ggs/dircrd
cwallet.sso

Once the credential store has been created, users and password can be added to it.  One of the key features of the store is the use of domains which can be used to logically group login aliases.  The same alias can be defined in different domains with different credentials.  This can be handy when developing and testing in different database environments from the same GoldenGate installation.  The default domain is “Oracle GoldenGate”.

In this example, the user c##ggsadmin is added to the store in the “test” domain.  If the “password” keyword is omitted, GoldenGate will prompt for the password and hide it from the output.

GGSCI> alter credentialstore add user c##ggsadmin, alias ggsadm, domain test
Password:

Credential store in /home/oracle/ggs/dircrd/ altered.

If you want to see the information maintained in the store, you can use the INFO CREDENTIONSTORE command.  If you don’t specify the domain, it will default to “Oracle GoldenGate”.  As you can see below, the default domain is still empty.

GGSCI> info credentialstore

Reading from /home/oracle/ggs/dircrd/:

No information found in credential store.

GGSCI> info credentialstore domain test

Reading from /home/oracle/ggs/dircrd/:

Domain: test
  Alias: ggsadm
  Userid: c##ggsadmin

In older version of GoldenGate, you had to supply the username and password in plain text or encrypted for your login credentials.


Using DBLOGIN at the command line.

GGSCI> DBLOGIN USERID c##ggsadmin@orcl, PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC AES128, ENCRYPTKEY securekey1

Successfully logged into database CDB$ROOT.

Using credentials in the parameter files.

GGSCI> edit params e1aa


EXTRACT e1aa
USERID c##ggsadmin@orcl, PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC AES128, ENCRYPTKEY securekey1
LOGALLSUPCOLS
EXTTRAIL ./dirdat/aa

TABLE pdborcl.scott.emp;
TABLE pdborcl.scott.dept;

SOURCECATALOG pdborcl
TABLE scott.bonus;
TABLE scott.salgrade;

In 12c, the credential store secures the information and makes the connection much easier through the use of the alias that was created.


Using DBLOGIN at the command line.

GGSCI> dblogin useridalias ggsadm domain test

Successfully logged into database CDB$ROOT.

Using credentials in the parameter files.

GGSCI> edit params e1aa


EXTRACT e1aa
USERIDALIAS ggsadm domain test
LOGALLSUPCOLS
EXTTRAIL ./dirdat/aa

TABLE pdborcl.scott.emp;
TABLE pdborcl.scott.dept;

SOURCECATALOG pdborcl
TABLE scott.bonus;
TABLE scott.salgrade;