Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Using Oracle GoldenGate (OGG) 11gR2 for Conflict Detection and Resolution (CDR) based on balance and timestamp in a bidirectional active-active configuration

Using Oracle GoldenGate (OGG) 11gR2 for Conflict Detection and Resolution (CDR) based on balance and timestamp in a bidirectional active-active configuration

In the article you will have a look at an example for CDR implementation based on a balance and timestamp column in a bidirectional active-active OGG setup. I will build an active-active bidirectional OGG replication between two sites (RACD, RACDB) each having identical tables (test5.account, test5.seat_assignment). I will emphasize on the requirements for CDR implementation and will outline CDR concepts and illustrate a step-by step CDR implementation, testing and troubleshooting. I will cover two cases

  • Use delta method for account balance CDR – An initial balance of 1000 will be simultaneously credited 200 on site B and debited 100 on site A. The result will be 1100 on both site A and site B.
  • Use USEMIN timestamp method for seat booking CDR – a seat ‘2A’ will be booked 1st by John Smith on site A and at about the same time will be booked by Pier Cardin on site B. The result will be the first user on both site A and site B.

Starting with OGG 11gR2 there are build in options in the MAP replicat parameter, such as COMPARECOLS, RESOLVECONFLICT, and in the TABLE extract parameter, such as GETBEFORECOLS, allowing easy, automatic and OGG driven CDR compared to the methods involving SQL or PLSQL code invoked from SQLEXEC used for CDR in versions of OGG prior to 11gR2.

In active-active bidirectional configuration we have

  • OGG configured for replication from site A to site B and from site B to site A
  • Application that can access both site A and site B

In the article Site A is RACD ad site b is RACDB.

Due to the asynchronous nature of OGG conflicts can occur if both sites update the same record at or near the same time. For CDR there are different methods in use such as

  • Latest time stamp – a timestamp column is added to the table and in case of two contending operations against the same record issued each on a different site, the record corresponding to an operation with the latest timestamp win the contest and persist in the database
  • Earliest timestamp – a timestamp column is added to the table and in case of two contending operations against the same record issued each on a different site, the record corresponding to an operation with the earliest timestamp win the contest and persist in the database.
  • Balance – in case of two contending operations against the same record issued each on a different site, the record that persist in the database is a summation from the difference(before-after) from the source + current column value from a record on the target for the column where balance is used. That is, adds the difference between the before and after values in the trail record from site A to the current value of the column in the target database on site B.
  • Site priority
  • Etc…
  • Any combination of the above methods.

OGG 11gR2 introduced facilities for automatic handling of

  • Latest timestamp – using USEMAX option in RESOLVECONFLICT
  • Earliest timestamp – using USEMIN option in RESOLVECONFLICT
  • Balance – using BALANCE option in RESOLVECONFLICT

In OGG versions prior to 11gR2 a custom code using SQLEXEC was required in order to implement the CDR functionality.

For CDR to operate the before image of the changed record is required in addition to the before image of the table key. Force Oracle to log in the tranlog the before image of a changed non key column by issuing ADD TRANDATA <tablename>, COLS(<changed columns>)

The extract capture for CDR should include the following

  1. Force extract to capture the before image using GETBEFORECOLS in the TABLE parameter.
  2. Use NOCOMPRESSDELETES and NOCOMPRESSUPDATES in the extract parameter file so that to have extract write a full record in a trail instead of the changed columns only.

For CDR replicat will be configured with mapping for:

  • A base table part of the replication configuration
  • An exception table corresponding to each replicated table to store the records details only in case of a conflict resolution or an error ( Similar to the way OGG REPERROR maps errors using exception map statement into an exception table)

The CDR is handled first and the REPERROR is handled second. Thus, the exception table is populated in case of a CDR.

A replicat configured for CDR performs the following tasks in addition to the usual tasks of applying the records from the trail.

  1. Compare the before values of the record from the trail using the MAP option COMPARECOLS with the before values on the target for each update or delete or both update and delete
  2. Use the before images from the trail to calculate a value on the target in case of conflict if DELTA is used
  3. Use the after images from the trail to calculate a value on the target in case of conflict if DELTA, USEMIN or USEMAX is used
  4. In case of conflict populates the table specified in the exception map for future reference (optional if you use an exception mapping)

The configuration is as follows.

Site A Site B
Database RACD RACDB
Tables in the replication configuration are identical create table test5.seat_assignment (
id             number(10) primary key,

passenger_name         varchar2(50),

latest_timestamp        timestamp,

flight_no        number(10),

seat_no            varchar2(19),

flight_time        date);

create table test5.account (

account_id            number(10) primary key,

account_name        varchar2(50),

account_tel        varchar2(12),

account_address        varchar2(200),

balance            number(10));

create table test5.seat_assignment (
id             number(10) primary key,

passenger_name         varchar2(50),

latest_timestamp        timestamp,

flight_no        number(10),

seat_no            varchar2(19),

flight_time        date);

create table test5.account (

account_id            number(10) primary key,

account_name        varchar2(50),

account_tel        varchar2(12),

account_address        varchar2(200),

balance            number(10));

Exception tables create table test5.seat_assignment_ex (
id_pk                number(10) primary key,

res_date            date,

optype                varchar2(100),

dberrnum            varchar2(100),

dberrmsge            varchar2(400),

tablename            varchar2(20),

id_curr                number(10) ,

passenger_name_curr         varchar2(50),

latest_timestamp_curr        timestamp,

flight_no_curr            number(10),

seat_no_curr            varchar2(19),

flight_time_curr        date,

id_before             number(10) ,

passenger_name_before         varchar2(50),

latest_timestamp_before        timestamp,

flight_no_before        number(10),

seat_no_before            varchar2(19),

flight_time_before        date,

id_after            number(10) ,

passenger_name_after         varchar2(50),

latest_timestamp_after        timestamp,

flight_no_after            number(10),

seat_no_after            varchar2(19),

flight_time_after        date

);

create table test5.account_ex (

id_pk                number(10) primary key,

res_date            date,

optype                varchar2(100),

dberrnum            varchar2(100),

dberrmsge            varchar2(400),

tablename            varchar2(20),

account_id_curr            number(10) ,

account_name_curr        varchar2(50),

account_tel_curr        varchar2(12),

account_address_curr        varchar2(200),

balance_curr            number(10),

account_id_before        number(10) ,

account_name_before        varchar2(50),

account_tel_before        varchar2(12),

account_address_before        varchar2(200),

balance_before            number(10),

account_id            number(10) ,

account_name            varchar2(50),

account_tel            varchar2(12),

account_address            varchar2(200),

balance                number(10)

);

create table test5.seat_assignment_ex (
id_pk                number(10) primary key,

res_date            date,

optype                varchar2(100),

dberrnum            varchar2(100),

dberrmsge            varchar2(400),

tablename            varchar2(20),

id_curr                number(10) ,

passenger_name_curr         varchar2(50),

latest_timestamp_curr        timestamp,

flight_no_curr            number(10),

seat_no_curr            varchar2(19),

flight_time_curr        date,

id_before             number(10) ,

passenger_name_before         varchar2(50),

latest_timestamp_before        timestamp,

flight_no_before        number(10),

seat_no_before            varchar2(19),

flight_time_before        date,

id_after            number(10) ,

passenger_name_after         varchar2(50),

latest_timestamp_after        timestamp,

flight_no_after            number(10),

seat_no_after            varchar2(19),

flight_time_after        date

);

create table test5.account_ex (

id_pk                number(10) primary key,

res_date            date,

optype                varchar2(100),

dberrnum            varchar2(100),

dberrmsge            varchar2(400),

tablename            varchar2(20),

account_id_curr            number(10) ,

account_name_curr        varchar2(50),

account_tel_curr        varchar2(12),

account_address_curr        varchar2(200),

balance_curr            number(10),

account_id_before        number(10) ,

account_name_before        varchar2(50),

account_tel_before        varchar2(12),

account_address_before        varchar2(200),

balance_before            number(10),

account_id            number(10) ,

account_name            varchar2(50),

account_tel            varchar2(12),

account_address            varchar2(200),

balance                number(10)

);

Table accessed by an application test5.seat_assignment
test5.account
test5.seat_assignment
test5.account
Extracts Extbi1 Extbi2
Replicats Repbi2 Repbi1
Trail files ./dirdat/3y ./dirdat/3z
Oracle sequence object to generate key values in the exception table map. See the replicat parameter file. CREATE SEQUENCE test4.exception
START WITH 1

INCREMENT BY 2

CACHE 30000 ;

CREATE SEQUENCE test4.exception
START WITH 2

INCREMENT BY 2

CACHE 30000 ;

The dataflow is as follows

Source database extract trail replicat Target database
From A to B RACD Extbi1 ./dirdat/3z Repbi1 RACDB
From B to A RACDB Extbi2 ./dirdat/3y Repbi2 RACD

The column test5.seat_assignment.latest_timestamp is instrumental for the USEMIN mode CDR. If there are no conflicts the data from the trail is applied as usual. In case of a conflict the after image from the trail file for this column is compared to the current value on the target. The earliest timestamp wins and the change record with the earliest after image of test5.seat_assignment.latest_timestam is written to the target. Note that as DEFAULT is used the resolution applies to all columns.

The column test5.account.balance is instrumental for the DELTA mode CDR. If there are no conflicts the data from the trail is applied as usual. In case of a conflict, the difference between the before and after values of test5.account.balance column from the trail record is added to the current value of the test5.account.balance column on the target database. The remaining columns are overwritten.

Implementing bidirectional active-active OGG replication with CDR includes the following steps performed in order.

  1. Prepare the Oracle to log into the redo log before and after images for all columns, not only keys and changed data, for tables in the replication (test5.seat_assignment,test5.account)
  1. Configuring replication from RACD to RACDB
  1. Configuring replication from RACDB to RACD
  2. Testing USEMIN and BALANCE CDR

Prepare Oracle to log data for all table columns

For each site perform the following

dblogin userid ogg_extract@[RACD-RACDB], password ogg_extrac

add trandata test5.seat_assignment, cols(passenger_name, latest_timestamp, flight_no, seat_no, flight_time)

add trandata test5.account, cols(account_name,account_tel,account_address,balance)

Use info trandata to verify the output. Take a note that the default used in DDLOPTIONS ADDTRANDATA and without columns specifications do not add all columns.

GGSCI (raclinux1.gj.com) 15> dblogin userid ogg_extract@racd, password ogg_extract

Successfully logged into database.

GGSCI (raclinux1.gj.com) 16> info trandata TEST5.ACCOUNT

Logging of supplemental redo log data is enabled for table TEST5.ACCOUNT.

Columns supplementally logged for table TEST5.ACCOUNT: ACCOUNT_ID, ACCOUNT_NAME, ACCOUNT_TEL, ACCOUNT_ADDRESS, BALANCE.

GGSCI (raclinux1.gj.com) 17> info trandata test5.seat_assignment

Logging of supplemental redo log data is enabled for table TEST5.SEAT_ASSIGNMENT.

Columns supplementally logged for table TEST5.SEAT_ASSIGNMENT: ID, PASSENGER_NAME, LATEST_TIMESTAMP, FLIGHT_NO, SEAT_NO, FLIGHT_TIME.

GGSCI (raclinux1.gj.com) 18>

Failure to add logging to all columns will result in

2012-11-07 15:41:30 ERROR OGG-01920 Missing COMPARECOLS column ACCOUNT_NAME in before image, while mapping to target table TEST5.ACCOUNT. Add the column to GETBEFORECOLS.

Configure replication from RACD to RACDB

Create an extract, trail and replicat

GGSCI (raclinux1.gj.com) 126> add extract extbi1, tranlog, begin now, threads 2

EXTRACT added.

GGSCI (raclinux1.gj.com) 127>

GGSCI (raclinux1.gj.com) 130> add rmttrail ./dirdat/3z, extract extbi1, megabytes 30

RMTTRAIL added.

GGSCI (raclinux1.gj.com) 131>

GGSCI (raclinux1.gj.com) 133> add replicat repbi1, exttrail ./dirdat/3z

REPLICAT added.

Pay attention to the parameter files

GGSCI (raclinux1.gj.com) 3> view params extbi1

extract extbi1

SETENV (ORACLE_SID = “RACDB1″)

tranlogoptions asmuser sys@ASM, asmpassword sys1

userid ogg_extract1@racd, password ogg_extract1

report at 10:00

reportcount every 10 minutes, rate

reportrollover on friday

nocompressupdates — Include the full record

nocompressdeletes — Include the full record

tranlogoptions excludeuser ogg_replicat — Exclude the replicat user

rmthost raclinux1, mgrport 7809

rmttrail ./dirdat/3z

table test5.seat_booking;

table test5.seat_assignment, getbeforecols(on update all, on delete all);

table test5.account, getbeforecols(on update all, on delete all);

GGSCI (raclinux1.gj.com) 4>

GGSCI (raclinux1.gj.com) 4> view params repbi1

replicat repbi1

–maxtransops 1

–TRANSACTIONTIMEOUT 5 S

reperror(default,exception) — For exeption mapping

reperror(default2,discard) — For test5.seat_booking as is withot exeption map

SETENV (ORACLE_SID = “RACDB1″)

userid ogg_replicat@racdb, password ogg_replicat

assumetargetdefs

report at 10:00

reportcount every 10 minutes, rate

reportrollover on friday

discardfile ./dirrpt/repbi1.dsc, purge

map test5.seat_booking, target test5.seat_booking;

map test5.seat_assignment, target test5.seat_assignment,

comparecols( on update all, on delete all),

RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMIN (latest_timestamp))),

RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMIN (latest_timestamp))),

RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)),

RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),

RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));

map test5.seat_assignment, target test5.seat_assignment_ex,

exceptionsonly,

insertallrecords,

sqlexec(id seq1, query ” select test4.exception.nextval from dual “, NOPARAMS),

sqlexec(id query_val1, query ” select ID, PASSENGER_NAME, LATEST_TIMESTAMP, FLIGHT_NO, SEAT_NO, FL

IGHT_TIME from test5.seat_assignment where id = :var_id “, PARAMS(var_id=id)),

colmap (

usedefaults,

id_pk = seq1.test4.exception.nextval,

res_date = @DATENOW(),

– captures and maps the DML operation type.

optype = @GETENV(“LASTERR”, “OPTYPE”),

– captures and maps the database error number that was returned.

dberrnum = @GETENV(“LASTERR”, “DBERRNUM”),

– captures and maps the database error that was returned.

dberrmsge = @GETENV(“LASTERR”, “DBERRMSG”),

– captures and maps the name of the target table

tablename = @GETENV(“GGHEADER”, “TABLENAME”),

id_curr = @GETVAL(query_val1.id),

passenger_name_curr = @GETVAL(query_val1.passenger_name),

latest_timestamp_curr = @GETVAL(query_val1.latest_timestamp),

flight_no_curr = @GETVAL(query_val1.flight_no),

seat_no_curr = @GETVAL(query_val1.seat_no),

flight_time_curr = @GETVAL(query_val1.flight_time),

id_before = before.id,

passenger_name_before = before.passenger_name,

latest_timestamp_before = before.latest_timestamp,

flight_no_before = before.flight_no,

seat_no_before = before.seat_no,

flight_time_before= before.flight_time,

id_after = id,

passenger_name_after = passenger_name,

latest_timestamp_after = latest_timestamp,

flight_no_after = flight_no,

seat_no_after = seat_no,

flight_time_after = flight_time);

map test5.account, target test5.account,

comparecols( on update all, on delete all),

RESOLVECONFLICT (UPDATEROWEXISTS, (delta_calc, USEDELTA, cols(balance)),(DEFAULT,OVERWRITE)),

RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)),

RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),

RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));

map test5.account, target test5.account_ex,

exceptionsonly,

insertallrecords,

sqlexec(id seq2, query ” select test4.exception.nextval from dual “, NOPARAMS),

sqlexec(id current_val2, query ” select ACCOUNT_ID, ACCOUNT_NAME, ACCOUNT_TEL, ACCOUNT_ADDRESS, BA

LANCE from test5.account where account_id = :var_id “, PARAMS(var_id=account_id)),

colmap (

usedefaults,

id_pk = seq2.test4.exception.nextval,

res_date = @DATENOW(),

– captures and maps the DML operation type.

optype = @GETENV(“LASTERR”, “OPTYPE”),

– captures and maps the database error number that was returned.

dberrnum = @GETENV(“LASTERR”, “DBERRNUM”),

– captures and maps the database error that was returned.

dberrmsge = @GETENV(“LASTERR”, “DBERRMSG”),

– captures and maps the name of the target table

tablename = @GETENV(“GGHEADER”, “TABLENAME”),

account_id_curr = current_val2.account_id,

account_name_curr = current_val2.account_name,

account_tel_curr = current_val2.account_tel,

account_address_curr = current_val2.account_address,

balance_curr = current_val2.balance,

account_id_before = before.account_id,

account_name_before = before.account_name,

account_tel_before = before.account_tel,

account_address_before = before.account_address,

balance_before = before.balance

–account_id = account_id

–account_name = account_name

–account_tel = account_tel

–account_address = account_address

–balance = balance

);

GGSCI (raclinux1.gj.com) 5>

Configure replication from RACDB to RACD

Create an extract, trail and replicat

GGSCI (raclinux1.gj.com) 16>

GGSCI (raclinux1.gj.com) 18> add extract extbi2, tranlog, begin now, threads 2

EXTRACT added.

GGSCI (raclinux1.gj.com) 19> add rmttrail ./dirdat/3y extract extbi2,megabytes 30

RMTTRAIL added.

GGSCI (raclinux1.gj.com) 20>

GGSCI (raclinux1.gj.com) 21> add replicat repbi2, exttrail ./dirdat/3y

REPLICAT added.

GGSCI (raclinux1.gj.com) 22>

Pay attention to the parameter files

GGSCI (raclinux1.gj.com) 5> view params extbi2

extract extbi2

SETENV (ORACLE_SID = “RACDB1″)

tranlogoptions asmuser sys@ASM, asmpassword sys1

userid ogg_extract1@racdb, password ogg_extract1

report at 10:00

reportcount every 10 minutes, rate

reportrollover on friday

nocompressupdates — Include the full record

nocompressdeletes — Include the full record

tranlogoptions excludeuser ogg_replicat — Exclude the replicat user

rmthost raclinux1, mgrport 7809

rmttrail ./dirdat/3y

table test5.seat_booking;

table test5.seat_assignment, getbeforecols(on update all, on delete all);

table test5.account, getbeforecols(on update all, on delete all);

GGSCI (raclinux1.gj.com) 6>

GGSCI (raclinux1.gj.com) 6> view params repbi2

replicat repbi2

–maxtransops 1

–TRANSACTIONTIMEOUT 5 S

reperror(default,exception) — For exeption mapping

reperror(default2,discard) — For test5.seat_booking as is withot exeption map

SETENV (ORACLE_SID = “RACDB1″)

userid ogg_replicat@racd, password ogg_replicat

assumetargetdefs

report at 10:00

reportcount every 10 minutes, rate

reportrollover on friday

discardfile ./dirrpt/repdown.dsc, purge

map test5.seat_booking, target test5.seat_booking;

map test5.seat_assignment, target test5.seat_assignment,

comparecols( on update all, on delete all),

RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMIN (latest_timestamp))),

RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMIN (latest_timestamp))),

RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)),

RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),

RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));

map test5.seat_assignment, target test5.seat_assignment_ex,

exceptionsonly,

insertallrecords,

sqlexec(id seq1, query ” select test4.exception.nextval from dual “, NOPARAMS),

sqlexec(id query_val1, query ” select ID, PASSENGER_NAME, LATEST_TIMESTAMP, FLIGHT_NO, SEAT_NO, FL

IGHT_TIME from test5.seat_assignment where id = :var_id “, PARAMS(var_id=id)),

colmap (

usedefaults,

id_pk = seq1.test4.exception.nextval,

res_date = @DATENOW(),

– captures and maps the DML operation type.

optype = @GETENV(“LASTERR”, “OPTYPE”),

– captures and maps the database error number that was returned.

dberrnum = @GETENV(“LASTERR”, “DBERRNUM”),

– captures and maps the database error that was returned.

dberrmsge = @GETENV(“LASTERR”, “DBERRMSG”),

– captures and maps the name of the target table

tablename = @GETENV(“GGHEADER”, “TABLENAME”),

id_curr = @GETVAL(query_val1.id),

passenger_name_curr = @GETVAL(query_val1.passenger_name),

latest_timestamp_curr = @GETVAL(query_val1.latest_timestamp),

flight_no_curr = @GETVAL(query_val1.flight_no),

seat_no_curr = @GETVAL(query_val1.seat_no),

flight_time_curr = @GETVAL(query_val1.flight_time),

id_before = before.id,

passenger_name_before = before.passenger_name,

latest_timestamp_before = before.latest_timestamp,

flight_no_before = before.flight_no,

seat_no_before = before.seat_no,

flight_time_before= before.flight_time,

id_after = id,

passenger_name_after = passenger_name,

latest_timestamp_after = latest_timestamp,

flight_no_after = flight_no,

seat_no_after = seat_no,

flight_time_after = flight_time);

map test5.account, target test5.account,

comparecols( on update all, on delete all),

RESOLVECONFLICT (UPDATEROWEXISTS, (delta_calc, USEDELTA, cols(balance)),(DEFAULT,OVERWRITE)),

RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)),

RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),

RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));

map test5.account, target test5.account_ex,

exceptionsonly,

insertallrecords,

sqlexec(id seq2, query ” select test4.exception.nextval from dual “, NOPARAMS),

sqlexec(id current_val2, query ” select ACCOUNT_ID, ACCOUNT_NAME, ACCOUNT_TEL, ACCOUNT_ADDRESS, BA

LANCE from test5.account where account_id = :var_id “, PARAMS(var_id=account_id)),

colmap (

usedefaults,

id_pk = seq2.test4.exception.nextval,

res_date = @DATENOW(),

– captures and maps the DML operation type.

optype = @GETENV(“LASTERR”, “OPTYPE”),

– captures and maps the database error number that was returned.

dberrnum = @GETENV(“LASTERR”, “DBERRNUM”),

– captures and maps the database error that was returned.

dberrmsge = @GETENV(“LASTERR”, “DBERRMSG”),

– captures and maps the name of the target table

tablename = @GETENV(“GGHEADER”, “TABLENAME”),

account_id_curr = current_val2.account_id,

account_name_curr = current_val2.account_name,

account_tel_curr = current_val2.account_tel,

account_address_curr = current_val2.account_address,

balance_curr = current_val2.balance,

account_id_before = before.account_id,

account_name_before = before.account_name,

account_tel_before = before.account_tel,

account_address_before = before.account_address,

balance_before = before.balance

–account_id = account_id

–account_name = account_name

–account_tel = account_tel

–account_address = account_address

–balance = balance

);

GGSCI (raclinux1.gj.com) 7>

Testing CDR

Make sure that extract and the replicates are running.

Testing BALANCE

The test will include series of SQL performed in the following order on RACD and RACDB.


RACD RACDB
1 16:27:04 SQL> insert into test5.account values(1,’Smith’,’555-555-5555′,’1234 Some street name’,1000);

commit;

1 row created.

Elapsed: 00:00:00.01

16:27:10 SQL>

Commit complete.

Elapsed: 00:00:00.02

16:27:12 SQL> select * from account;

ACCOUNT_ID ACCOUNT_NAME ACCOUNT_TEL ACCOUNT_AD BALANCE

———- ——————– ———— ———- ———-

1 Smith 555-555-5555 1234 Some 1000

street nam

e

Elapsed: 00:00:00.00

16:27:21 SQL> 2 16:27:08 SQL> select * from account;

ACCOUNT_ID ACCOUNT_NAME ACCOUNT_TEL ACCOUNT_AD BALANCE

———- ——————– ———— ———- ———-

1 Smith 555-555-5555 1234 Some 1000

street nam

e

Elapsed: 00:00:00.00

16:27:27 SQL>

16:28:20 SQL> insert into test5.account values(2,’Jones’,’666-666-6666′,’5678 Some street name’,2000);

commit;

1 row created.

Elapsed: 00:00:00.00

16:28:20 SQL>

Commit complete.

Elapsed: 00:00:00.01

16:28:20 SQL>

16:28:21 SQL> select * from account;

ACCOUNT_ID ACCOUNT_NAME ACCOUNT_TEL ACCOUNT_AD BALANCE

———- ——————– ———— ———- ———-

1 Smith 555-555-5555 1234 Some 1000

street nam

e

2 Jones 666-666-6666 5678 Some 2000

street nam

e

Elapsed: 00:00:00.00

16:28:27 SQL>316:28:58 SQL> select * from account;

ACCOUNT_ID ACCOUNT_NAME ACCOUNT_TEL ACCOUNT_AD BALANCE

———- ——————– ———— ———- ———-

1 Smith 555-555-5555 1234 Some 1000

street nam

e

2 Jones 666-666-6666 5678 Some 2000

street nam

e

Elapsed: 00:00:00.01

16:29:00 SQL> 416:29:00 SQL> update test5.account set balance=1200 where account_id=1;

1 row updated.

Elapsed: 00:00:00.00

16:29:36 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

16:30:04 SQL> select * from account;

ACCOUNT_ID ACCOUNT_NAME ACCOUNT_TEL ACCOUNT_AD BALANCE

———- ——————– ———— ———- ———-

1 Smith 555-555-5555 1234 Some 1100

street nam

e

2 Jones 666-666-6666 5678 Some 2000

street nam

e

Elapsed: 00:00:00.00

16:30:10 SQL>


16:28:27 SQL> update test5.account set balance=900 where account_id=1;

1 row updated.

Elapsed: 00:00:00.00

16:29:51 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

16:30:03 SQL> select * from account;

ACCOUNT_ID ACCOUNT_NAME ACCOUNT_TEL ACCOUNT_AD BALANCE

———- ——————– ———— ———- ———-

1 Smith 555-555-5555 1234 Some 1100

street nam

e

2 Jones 666-666-6666 5678 Some 2000

street nam

e

Elapsed: 00:00:00.01

16:30:14 SQL>

16:30:48 SQL>

Starting from an initial balance of 1000 and decreasing the balance by 100 to 900 on RACDB and increasing by 200 to 1200 on RACD results in balance of 1100. This is correct as 1100=1000+200-100.

You can use GGSCI stats to monitor what have happened. See the Appendix for a detail output but a snip will look like

Replicating from TEST5.ACCOUNT to TEST5.ACCOUNT:

*** Total statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

The exception table gets populated as well. On RACD I have

16:56:43 SQL> select * from account_ex;

ID_PK RES_DATE OPTYPE DBERRNUM DBERRMSGE TABLENAME ACCOUNT_ID_CURR ACCOUNT_NAME_CURR ACCOUNT_TEL_ ACCOUNT_AD BALANCE_CURR ACCOUNT_ID_BEFORE ACCOUNT_NAME_BEFORE ACCOUNT_TEL_ ACCOUNT_AD BALANCE_BEFORE ACCOUNT_ID ACCOUNT_NAME ACCOUNT_TEL ACCOUNT_AD BALANCE

———- ——— ———— ———— ———— ——————– ————— ——————– ———— ———- ———— —————– ——————– ———— ———- ————– ———- ——————– ———— ———- ———-

180007 07-NOV-12 SQL COMPUPDA 1403 TEST5.ACCOUNT 1 Smith 555-555-5555 1234 Some 1100 1 Smith 555-555-5555 1234 Some 1000 1 900

TE street nam street nam

e e

Elapsed: 00:00:00.01

16:57:08 SQL>

On RACDB I have

16:57:59 SQL> select * from account_ex;

ID_PK RES_DATE OPTYPE DBERRNUM DBERRMSGE TABLENAME ACCOUNT_ID_CURR ACCOUNT_NAME_CURR ACCOUNT_TEL_ ACCOUNT_AD BALANCE_CURR ACCOUNT_ID_BEFORE ACCOUNT_NAME_BEFORE ACCOUNT_TEL_ ACCOUNT_AD BALANCE_BEFORE ACCOUNT_ID ACCOUNT_NAME ACCOUNT_TEL ACCOUNT_AD BALANCE

———- ——— ———— ———— ———— ——————– ————— ——————– ———— ———- ———— —————– ——————– ———— ———- ————– ———- ——————– ———— ———- ———-

180001 07-NOV-12 SQL COMPUPDA 1403 TEST5.ACCOUNT 1 Smith 555-555-5555 1234 Some 1100 1 Smith 555-555-5555 1234 Some 1000 1 1200

TE street nam street nam

e e

Elapsed: 00:00:00.01

16:58:11 SQL>

Testing USEMIN

The test will include series of SQL performed in the following order on RACD and RACDB.

RACD RACDB
1 16:37:25 SQL>

16:39:05 SQL> insert into test5.seat_assignment values(1,”,current_timestamp,120,’1A’,sysdate);

insert into test5.seat_assignment values(2,”,current_timestamp,120,’1B’,sysdate);

insert into test5.seat_assignment values(3,”,current_timestamp,120,’1C’,sysdate);

insert into test5.seat_assignment values(4,”,current_timestamp,120,’1D’,sysdate);

insert into test5.seat_assignment values(5,”,current_timestamp,120,’1E’,sysdate);

insert into test5.seat_assignment values(6,”,current_timestamp,120,’1F’,sysdate);

commit;

1 row created.

Elapsed: 00:00:00.02

16:39:13 SQL>

1 row created.

Elapsed: 00:00:00.01

16:39:13 SQL>

1 row created.

Elapsed: 00:00:00.00

16:39:13 SQL>

1 row created.

Elapsed: 00:00:00.01

16:39:13 SQL>

1 row created.

Elapsed: 00:00:00.00

16:39:13 SQL>

1 row created.

Elapsed: 00:00:00.01

16:39:13 SQL>

Commit complete.

Elapsed: 00:00:00.00

16:39:13 SQL>

16:39:19 SQL> select count(*) from seat_assignment;

COUNT(*)

———-

6

Elapsed: 00:00:00.01

16:39:34 SQL> 2 16:40:11 SQL> select count(*) from seat_assignment;

COUNT(*)

———-

6

Elapsed: 00:00:00.04

16:40:13 SQL> insert into test5.seat_assignment values(7,”,current_timestamp,120,’2A’,sysdate);

insert into test5.seat_assignment values(8,”,current_timestamp,120,’2B’,sysdate);

insert into test5.seat_assignment values(9,”,current_timestamp,120,’2C’,sysdate);

insert into test5.seat_assignment values(10,”,current_timestamp,120,’2D’,sysdate);

insert into test5.seat_assignment values(11,”,current_timestamp,120,’2E’,sysdate);

insert into test5.seat_assignment values(12,”,current_timestamp,120,’2F’,sysdate);

commit;

1 row created.

Elapsed: 00:00:00.00

16:40:25 SQL>

1 row created.

Elapsed: 00:00:00.00

16:40:25 SQL>

1 row created.

Elapsed: 00:00:00.02

16:40:25 SQL>

1 row created.

Elapsed: 00:00:00.00

16:40:25 SQL>

1 row created.

Elapsed: 00:00:00.01

16:40:25 SQL>

1 row created.

Elapsed: 00:00:00.02

16:40:25 SQL>

Commit complete.

Elapsed: 00:00:00.00

16:40:26 SQL>

16:40:27 SQL> select count(*) from seat_assignment

16:40:32 2 ;

COUNT(*)

———-

12

Elapsed: 00:00:00.01

16:40:34 SQL>
316:39:34 SQL> select count(*) from seat_assignment;

COUNT(*)

———-

12

Elapsed: 00:00:00.00

16:41:14 SQL> 416:51:57 SQL> select * from seat_assignment where seat_no=’2A';

ID PASSENGER_NAME LATEST_TIMESTAMP FLIGHT_NO SEAT_NO FLIGHT_TI

———- ————————————————– ————————————————————————— ———- ——————- ———

7 07-NOV-12 04.40.25.481803 PM 120 2A 07-NOV-12

Elapsed: 00:00:00.00

16:51:59 SQL> update test5.seat_assignment set PASSENGER_NAME=’John Smith’, LATEST_TIMESTAMP=current_timestamp where seat_no=’2A’ and FLIGHT_NO=120;

1 row updated.

Elapsed: 00:00:00.00

16:52:31 SQL> commit;

Commit complete.

Elapsed: 00:00:00.03

16:53:00 SQL> select * from seat_assignment where seat_no=’2A';

ID PASSENGER_NAME LATEST_TIMESTAMP FLIGHT_NO SEAT_NO FLIGHT_TI

———- ————————————————– ————————————————————————— ———- ——————- ———

7 John Smith 07-NOV-12 04.52.31.618956 PM 120 2A 07-NOV-12

Elapsed: 00:00:00.00

16:53:12 SQL>
16:52:09 SQL> select * from seat_assignment where seat_no=’2A';

ID PASSENGER_NAME LATEST_TIMESTAMP FLIGHT_NO SEAT_NO FLIGHT_TI

———- ————————————————– ————————————————————————— ———- ——————- ———

7 07-NOV-12 04.40.25.481803 PM 120 2A 07-NOV-12

Elapsed: 00:00:00.01

16:52:12 SQL> update test5.seat_assignment set PASSENGER_NAME=’Pier Cardin’, LATEST_TIMESTAMP=current_timestamp where seat_no=’2A’ and FLIGHT_NO=120;

1 row updated.

Elapsed: 00:00:00.01

16:52:46 SQL> commit;

Commit complete.

Elapsed: 00:00:00.05

16:53:03 SQL> select * from seat_assignment where seat_no=’2A';

ID PASSENGER_NAME LATEST_TIMESTAMP FLIGHT_NO SEAT_NO FLIGHT_TI

———- ————————————————– ————————————————————————— ———- ——————- ———

7 John Smith 07-NOV-12 04.52.31.618956 PM 120 2A 07-NOV-12

Elapsed: 00:00:00.01

16:53:16 SQL>

Here 1st committed change is for ‘John Smith’ that wins against the change for ‘Pier Cardin’. Therefore, the 1st record persists across both sites and ‘John Smith’ change is visible on both sites.

You can use GGSCI stats to monitor what have happened. See the Appendix for a detail output but a snip will look like

Replicating from TEST5.SEAT_ASSIGNMENT to TEST5.SEAT_ASSIGNMENT:

*** Total statistics since 2012-11-07 16:27:17 ***

Total inserts 6.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 7.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

Replicating from TEST5.SEAT_ASSIGNMENT to TEST5.SEAT_ASSIGNMENT_EX:

*** Total statistics since 2012-11-07 16:27:17 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

The exception table gets populated as well. On RACD I have

16:56:25 SQL> select * from seat_assignment_ex;

ID_PK RES_DATE OPTYPE DBERRNUM DBERRMSGE TABLENAME ID_CURR PASSENGER_NAME_CURR LATEST_TIMESTAMP_CURR FLIGHT_NO_CURR SEAT_NO_CURR FLIGHT_TI ID_BEFORE PASSENGER_NAME_BEFOR LATEST_TIMESTAMP_BEF FLIGHT_NO_BEFORE SEAT_NO_BEFORE FLIGHT_TI ID_AFTER PASSENGER_NAME_AFTER LATEST_TIMESTAMP_AFTER FLIGHT_NO_AFTER SEAT_NO_AFTER FLIGHT_TI

———- ——— ———— ———— ———— ——————– ———- ——————– ————————————————————————— ————– ——————- ——— ———- ——————– ——————– —————- ——————- ——— ———- ————————————————– ————————————————————————— ————— ——————- ———

180009 07-NOV-12 SQL COMPUPDA 1403 OCI Error OR TEST5.SEAT_ASSIGNMEN 7 John Smith 07-NOV-12 04.52.31.618956 PM 120 2A 07-NOV-12 7 07-NOV-12 04.40.25.4 120 2A 07-NOV-12 7 Pier Cardin 07-NOV-12 04.52.46.481506 PM

TE A-01403: no 81803 PM

data found,

SQL <UPDATE

“TEST5″.”SEA

T_ASSIGNMENT

” SET “PASSE

NGER_NAME” =

:a1,”LATEST

_TIMESTAMP”

= :a2 WHERE

ID_PK RES_DATE OPTYPE DBERRNUM DBERRMSGE TABLENAME ID_CURR PASSENGER_NAME_CURR LATEST_TIMESTAMP_CURR FLIGHT_NO_CURR SEAT_NO_CURR FLIGHT_TI ID_BEFORE PASSENGER_NAME_BEFOR LATEST_TIMESTAMP_BEF FLIGHT_NO_BEFORE SEAT_NO_BEFORE FLIGHT_TI ID_AFTER PASSENGER_NAME_AFTER LATEST_TIMESTAMP_AFTER FLIGHT_NO_AFTER SEAT_NO_AFTER FLIGHT_TI

———- ——— ———— ———— ———— ——————– ———- ——————– ————————————————————————— ————– ——————- ——— ———- ——————– ——————– —————- ——————- ——— ———- ————————————————– ————————————————————————— ————— ——————- ———

“ID” = :b0 A

ND “LATEST_T

IMESTAMP” >

:b1>

Elapsed: 00:00:00.01

16:56:43 SQL>

On RACDB I have

16:57:41 SQL> select * from seat_assignment where seat_no=’2A';

ID PASSENGER_NAME LATEST_TIMESTAMP FLIGHT_NO SEAT_NO FLIGHT_TI

———- ————————————————– ————————————————————————— ———- ——————- ———

7 John Smith 07-NOV-12 04.52.31.618956 PM 120 2A 07-NOV-12

Elapsed: 00:00:00.00

16:57:48 SQL> select * from seat_assignment_ex;

ID_PK RES_DATE OPTYPE DBERRNUM DBERRMSGE TABLENAME ID_CURR PASSENGER_NAME_CURR LATEST_TIMESTAMP_CURR FLIGHT_NO_CURR SEAT_NO_CURR FLIGHT_TI ID_BEFORE PASSENGER_NAME_BEFOR LATEST_TIMESTAMP_BEF FLIGHT_NO_BEFORE SEAT_NO_BEFORE FLIGHT_TI ID_AFTER PASSENGER_NAME_AFTER LATEST_TIMESTAMP_AFTER FLIGHT_NO_AFTER SEAT_NO_AFTER FLIGHT_TI

———- ——— ———— ———— ———— ——————– ———- ——————– ————————————————————————— ————– ——————- ——— ———- ——————– ——————– —————- ——————- ——— ———- ————————————————– ————————————————————————— ————— ——————- ———

180003 07-NOV-12 SQL COMPUPDA 1403 TEST5.SEAT_ASSIGNMEN 7 John Smith 07-NOV-12 04.52.31.618956 PM 120 2A 07-NOV-12 7 07-NOV-12 04.40.25.4 120 2A 07-NOV-12 7 John Smith 07-NOV-12 04.52.31.618956 PM

TE 81803 PM

Elapsed: 00:00:00.00

16:57:59 SQL> select * from account_ex;

ID_PK RES_DATE OPTYPE DBERRNUM DBERRMSGE TABLENAME ACCOUNT_ID_CURR ACCOUNT_NAME_CURR ACCOUNT_TEL_ ACCOUNT_AD BALANCE_CURR ACCOUNT_ID_BEFORE ACCOUNT_NAME_BEFORE ACCOUNT_TEL_ ACCOUNT_AD BALANCE_BEFORE ACCOUNT_ID ACCOUNT_NAME ACCOUNT_TEL ACCOUNT_AD BALANCE

———- ——— ———— ———— ———— ——————– ————— ——————– ———— ———- ———— —————– ——————– ———— ———- ————– ———- ——————– ———— ———- ———-

180001 07-NOV-12 SQL COMPUPDA 1403 TEST5.ACCOUNT 1 Smith 555-555-5555 1234 Some 1100 1 Smith 555-555-5555 1234 Some 1000 1 1200

TE street nam street nam

e e

Elapsed: 00:00:00.01

16:58:11 SQL>

OGG replicat report files also provide CDR related information that looks like.

From Table TEST5.SEAT_ASSIGNMENT to TEST5.SEAT_ASSIGNMENT:

# inserts: 29

# updates: 2

# deletes: 23

# discards: 0

# CDR conflicts : 1

# CDR resolutions succeeded : 1

# CDR UPDATEROWEXISTS conflicts : 1

From Table TEST5.SEAT_ASSIGNMENT to TEST5.SEAT_ASSIGNMENT_EX:

# inserts: 0

# updates: 1

# deletes: 0

# discards: 0

Stored procedure seq1:

attempts: 1

successful: 1

Stored procedure query_val1:

attempts: 1

successful: 1

From Table TEST5.ACCOUNT to TEST5.ACCOUNT:

# inserts: 1

# updates: 3

# deletes: 0

# discards: 0

# CDR conflicts : 2

# CDR resolutions succeeded : 2

# CDR UPDATEROWEXISTS conflicts : 2

From Table TEST5.ACCOUNT to TEST5.ACCOUNT_EX:

# inserts: 0

# updates: 2

# deletes: 0

# discards: 0

Stored procedure seq2:

attempts: 2

successful: 2

Stored procedure current_val2:

attempts: 2

successful: 2

Summary

In the article you had a look at a way to setup a bidirectional active-active OGG replication implementing CDR using a timestamp USEMIN and DELTA methods.

Appendix

After DELTA test

GGSCI (raclinux1.gj.com) 52> stats extbi1 reportcdr

Sending STATS request to EXTRACT EXTBI1 …

Start of Statistics at 2012-11-07 16:31:30.

Output to ./dirdat/3z:

Extracting from TEST5.ACCOUNT to TEST5.ACCOUNT:

*** Total statistics since 2012-11-07 16:27:16 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Daily statistics since 2012-11-07 16:27:16 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Hourly statistics since 2012-11-07 16:27:16 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Latest statistics since 2012-11-07 16:27:16 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

End of Statistics.

GGSCI (raclinux1.gj.com) 53>

GGSCI (raclinux1.gj.com) 75> stats repbi1 reportcdr

Sending STATS request to REPLICAT REPBI1 …

Start of Statistics at 2012-11-07 16:32:36.

Replicating from TEST5.ACCOUNT to TEST5.ACCOUNT:

*** Total statistics since 2012-11-07 16:27:17 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

*** Daily statistics since 2012-11-07 16:27:17 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

*** Hourly statistics since 2012-11-07 16:27:17 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

*** Latest statistics since 2012-11-07 16:27:17 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

Replicating from TEST5.ACCOUNT to TEST5.ACCOUNT_EX:

*** Total statistics since 2012-11-07 16:27:17 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Daily statistics since 2012-11-07 16:27:17 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Hourly statistics since 2012-11-07 16:27:17 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Latest statistics since 2012-11-07 16:27:17 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

GGSCI (raclinux1.gj.com) 76>

GGSCI (raclinux1.gj.com) 76> stats extbi2 reportcdr

Sending STATS request to EXTRACT EXTBI2 …

Start of Statistics at 2012-11-07 16:33:27.

Output to ./dirdat/3y:

Extracting from TEST5.ACCOUNT to TEST5.ACCOUNT:

*** Total statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Daily statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Hourly statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Latest statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

End of Statistics.

GGSCI (raclinux1.gj.com) 77>

GGSCI (raclinux1.gj.com) 53> stats repbi2 reportcdr

Sending STATS request to REPLICAT REPBI2 …

Start of Statistics at 2012-11-07 16:34:05.

Replicating from TEST5.ACCOUNT to TEST5.ACCOUNT:

*** Total statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

*** Daily statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

*** Hourly statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

*** Latest statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

Replicating from TEST5.ACCOUNT to TEST5.ACCOUNT_EX:

*** Total statistics since 2012-11-07 16:28:24 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Daily statistics since 2012-11-07 16:28:24 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Hourly statistics since 2012-11-07 16:28:24 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Latest statistics since 2012-11-07 16:28:24 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

GGSCI (raclinux1.gj.com) 54>

After USEMIN test

GGSCI (raclinux1.gj.com) 57> stats extbi1 reportcdr

Sending STATS request to EXTRACT EXTBI1 …

Start of Statistics at 2012-11-07 16:59:53.

Output to ./dirdat/3z:

Extracting from TEST5.ACCOUNT to TEST5.ACCOUNT:

*** Total statistics since 2012-11-07 16:27:16 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Daily statistics since 2012-11-07 16:27:16 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Hourly statistics since 2012-11-07 16:27:16 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Latest statistics since 2012-11-07 16:27:16 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

Extracting from TEST5.SEAT_ASSIGNMENT to TEST5.SEAT_ASSIGNMENT:

*** Total statistics since 2012-11-07 16:27:16 ***

Total inserts 6.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 8.00

*** Daily statistics since 2012-11-07 16:27:16 ***

Total inserts 6.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 8.00

*** Hourly statistics since 2012-11-07 16:27:16 ***

Total inserts 6.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 8.00

*** Latest statistics since 2012-11-07 16:27:16 ***

Total inserts 6.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 8.00

End of Statistics.

GGSCI (raclinux1.gj.com) 58>

GGSCI (raclinux1.gj.com) 80> stats repbi1 reportcdr

Sending STATS request to REPLICAT REPBI1 …

Start of Statistics at 2012-11-07 17:00:36.

Replicating from TEST5.ACCOUNT to TEST5.ACCOUNT:

*** Total statistics since 2012-11-07 16:27:17 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

*** Daily statistics since 2012-11-07 16:27:17 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

*** Hourly statistics since 2012-11-07 17:00:00 ***

No database operations have been performed.

*** Latest statistics since 2012-11-07 16:27:17 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

Replicating from TEST5.ACCOUNT to TEST5.ACCOUNT_EX:

*** Total statistics since 2012-11-07 16:27:17 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Daily statistics since 2012-11-07 16:27:17 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Hourly statistics since 2012-11-07 17:00:00 ***

No database operations have been performed.

*** Latest statistics since 2012-11-07 16:27:17 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

Replicating from TEST5.SEAT_ASSIGNMENT to TEST5.SEAT_ASSIGNMENT:

*** Total statistics since 2012-11-07 16:27:17 ***

Total inserts 6.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 7.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

*** Daily statistics since 2012-11-07 16:27:17 ***

Total inserts 6.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 7.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

*** Hourly statistics since 2012-11-07 17:00:00 ***

No database operations have been performed.

*** Latest statistics since 2012-11-07 16:27:17 ***

Total inserts 6.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 7.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

Replicating from TEST5.SEAT_ASSIGNMENT to TEST5.SEAT_ASSIGNMENT_EX:

*** Total statistics since 2012-11-07 16:27:17 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Daily statistics since 2012-11-07 16:27:17 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Hourly statistics since 2012-11-07 17:00:00 ***

No database operations have been performed.

*** Latest statistics since 2012-11-07 16:27:17 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

GGSCI (raclinux1.gj.com) 81>

GGSCI (raclinux1.gj.com) 81> stats extbi2 reportcdr

Sending STATS request to EXTRACT EXTBI2 …

Start of Statistics at 2012-11-07 17:01:26.

Output to ./dirdat/3y:

Extracting from TEST5.ACCOUNT to TEST5.ACCOUNT:

*** Total statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Daily statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Hourly statistics since 2012-11-07 17:00:00 ***

No database operations have been performed.

*** Latest statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

Extracting from TEST5.SEAT_ASSIGNMENT to TEST5.SEAT_ASSIGNMENT:

*** Total statistics since 2012-11-07 16:28:24 ***

Total inserts 6.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 8.00

*** Daily statistics since 2012-11-07 16:28:24 ***

Total inserts 6.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 8.00

*** Hourly statistics since 2012-11-07 17:00:00 ***

No database operations have been performed.

*** Latest statistics since 2012-11-07 16:28:24 ***

Total inserts 6.00

Total updates 1.00

Total befores 1.00

Total deletes 0.00

Total discards 0.00

Total operations 8.00

End of Statistics.

GGSCI (raclinux1.gj.com) 82>

GGSCI (raclinux1.gj.com) 58> stats repbi2 reportcdr

Sending STATS request to REPLICAT REPBI2 …

Start of Statistics at 2012-11-07 17:02:03.

Replicating from TEST5.ACCOUNT to TEST5.ACCOUNT:

*** Total statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

*** Daily statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

*** Hourly statistics since 2012-11-07 17:00:00 ***

No database operations have been performed.

*** Latest statistics since 2012-11-07 16:28:24 ***

Total inserts 1.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

Replicating from TEST5.ACCOUNT to TEST5.ACCOUNT_EX:

*** Total statistics since 2012-11-07 16:28:24 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Daily statistics since 2012-11-07 16:28:24 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Hourly statistics since 2012-11-07 17:00:00 ***

No database operations have been performed.

*** Latest statistics since 2012-11-07 16:28:24 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

Replicating from TEST5.SEAT_ASSIGNMENT to TEST5.SEAT_ASSIGNMENT:

*** Total statistics since 2012-11-07 16:28:24 ***

Total inserts 6.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 7.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

*** Daily statistics since 2012-11-07 16:28:24 ***

Total inserts 6.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 7.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

*** Hourly statistics since 2012-11-07 17:00:00 ***

No database operations have been performed.

*** Latest statistics since 2012-11-07 16:28:24 ***

Total inserts 6.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 7.00

Total CDR conflicts 1.00

CDR resolutions succeeded 1.00

CDR UPDATEROWEXISTS conflicts 1.00

Replicating from TEST5.SEAT_ASSIGNMENT to TEST5.SEAT_ASSIGNMENT_EX:

*** Total statistics since 2012-11-07 16:28:24 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Daily statistics since 2012-11-07 16:28:24 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Hourly statistics since 2012-11-07 17:00:00 ***

No database operations have been performed.

*** Latest statistics since 2012-11-07 16:28:24 ***

Total inserts 0.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

End of Statistics.

GGSCI (raclinux1.gj.com) 59>

Output from the exception tables.

16:56:25 SQL> select * from seat_assignment_ex;

ID_PK RES_DATE OPTYPE DBERRNUM DBERRMSGE TABLENAME ID_CURR PASSENGER_NAME_CURR LATEST_TIMESTAMP_CURR FLIGHT_NO_CURR SEAT_NO_CURR FLIGHT_TI ID_BEFORE PASSENGER_NAME_BEFOR LATEST_TIMESTAMP_BEF FLIGHT_NO_BEFORE SEAT_NO_BEFORE FLIGHT_TI ID_AFTER PASSENGER_NAME_AFTER LATEST_TIMESTAMP_AFTER FLIGHT_NO_AFTER SEAT_NO_AFTER FLIGHT_TI

———- ——— ———— ———— ———— ——————– ———- ——————– ————————————————————————— ————– ——————- ——— ———- ——————– ——————– —————- ——————- ——— ———- ————————————————– ————————————————————————— ————— ——————- ———

180009 07-NOV-12 SQL COMPUPDA 1403 OCI Error OR TEST5.SEAT_ASSIGNMEN 7 John Smith 07-NOV-12 04.52.31.618956 PM 120 2A 07-NOV-12 7 07-NOV-12 04.40.25.4 120 2A 07-NOV-12 7 Pier Cardin 07-NOV-12 04.52.46.481506 PM

TE A-01403: no 81803 PM

data found,

SQL <UPDATE

“TEST5″.”SEA

T_ASSIGNMENT

” SET “PASSE

NGER_NAME” =

:a1,”LATEST

_TIMESTAMP”

= :a2 WHERE

ID_PK RES_DATE OPTYPE DBERRNUM DBERRMSGE TABLENAME ID_CURR PASSENGER_NAME_CURR LATEST_TIMESTAMP_CURR FLIGHT_NO_CURR SEAT_NO_CURR FLIGHT_TI ID_BEFORE PASSENGER_NAME_BEFOR LATEST_TIMESTAMP_BEF FLIGHT_NO_BEFORE SEAT_NO_BEFORE FLIGHT_TI ID_AFTER PASSENGER_NAME_AFTER LATEST_TIMESTAMP_AFTER FLIGHT_NO_AFTER SEAT_NO_AFTER FLIGHT_TI

———- ——— ———— ———— ———— ——————– ———- ——————– ————————————————————————— ————– ——————- ——— ———- ——————– ——————– —————- ——————- ——— ———- ————————————————– ————————————————————————— ————— ——————- ———

“ID” = :b0 A

ND “LATEST_T

IMESTAMP” >

:b1>

Elapsed: 00:00:00.01

16:56:43 SQL> select * from account_ex;

ID_PK RES_DATE OPTYPE DBERRNUM DBERRMSGE TABLENAME ACCOUNT_ID_CURR ACCOUNT_NAME_CURR ACCOUNT_TEL_ ACCOUNT_AD BALANCE_CURR ACCOUNT_ID_BEFORE ACCOUNT_NAME_BEFORE ACCOUNT_TEL_ ACCOUNT_AD BALANCE_BEFORE ACCOUNT_ID ACCOUNT_NAME ACCOUNT_TEL ACCOUNT_AD BALANCE

———- ——— ———— ———— ———— ——————– ————— ——————– ———— ———- ———— —————– ——————– ———— ———- ————– ———- ——————– ———— ———- ———-

180007 07-NOV-12 SQL COMPUPDA 1403 TEST5.ACCOUNT 1 Smith 555-555-5555 1234 Some 1100 1 Smith 555-555-5555 1234 Some 1000 1 900

TE street nam street nam

e e

Elapsed: 00:00:00.01

16:57:08 SQL> spool off

16:57:41 SQL> select * from seat_assignment where seat_no=’2A';

ID PASSENGER_NAME LATEST_TIMESTAMP FLIGHT_NO SEAT_NO FLIGHT_TI

———- ————————————————– ————————————————————————— ———- ——————- ———

7 John Smith 07-NOV-12 04.52.31.618956 PM 120 2A 07-NOV-12

Elapsed: 00:00:00.00

16:57:48 SQL> select * from seat_assignment_ex;

ID_PK RES_DATE OPTYPE DBERRNUM DBERRMSGE TABLENAME ID_CURR PASSENGER_NAME_CURR LATEST_TIMESTAMP_CURR FLIGHT_NO_CURR SEAT_NO_CURR FLIGHT_TI ID_BEFORE PASSENGER_NAME_BEFOR LATEST_TIMESTAMP_BEF FLIGHT_NO_BEFORE SEAT_NO_BEFORE FLIGHT_TI ID_AFTER PASSENGER_NAME_AFTER LATEST_TIMESTAMP_AFTER FLIGHT_NO_AFTER SEAT_NO_AFTER FLIGHT_TI

———- ——— ———— ———— ———— ——————– ———- ——————– ————————————————————————— ————– ——————- ——— ———- ——————– ——————– —————- ——————- ——— ———- ————————————————– ————————————————————————— ————— ——————- ———

180003 07-NOV-12 SQL COMPUPDA 1403 TEST5.SEAT_ASSIGNMEN 7 John Smith 07-NOV-12 04.52.31.618956 PM 120 2A 07-NOV-12 7 07-NOV-12 04.40.25.4 120 2A 07-NOV-12 7 John Smith 07-NOV-12 04.52.31.618956 PM

TE 81803 PM

Elapsed: 00:00:00.00

16:57:59 SQL> select * from account_ex;

ID_PK RES_DATE OPTYPE DBERRNUM DBERRMSGE TABLENAME ACCOUNT_ID_CURR ACCOUNT_NAME_CURR ACCOUNT_TEL_ ACCOUNT_AD BALANCE_CURR ACCOUNT_ID_BEFORE ACCOUNT_NAME_BEFORE ACCOUNT_TEL_ ACCOUNT_AD BALANCE_BEFORE ACCOUNT_ID ACCOUNT_NAME ACCOUNT_TEL ACCOUNT_AD BALANCE

———- ——— ———— ———— ———— ——————– ————— ——————– ———— ———- ———— —————– ——————– ———— ———- ————– ———- ——————– ———— ———- ———-

180001 07-NOV-12 SQL COMPUPDA 1403 TEST5.ACCOUNT 1 Smith 555-555-5555 1234 Some 1100 1 Smith 555-555-5555 1234 Some 1000 1 1200

TE street nam street nam

e e

Elapsed: 00:00:00.01

16:58:11 SQL> spool off

Reference

  1. OGG Oracle Installation and Setup Guide
  1. OGG Reference Guide

Best Practices for Conflict Detection and Resolution in Active-Active Database Configurations Using Oracle GoldenGate

November 7, 2012 - Posted by | oracle

4 Comments »

  1. [...] online from here or download Using Oracle GoldenGate (OGG) 11gR2 for Conflict Detection and Resolution (CDR) based [...]

    Pingback by Using Oracle GoldenGate (OGG) 11gR2 for Conflict Detection and Resolution (CDR) based on balance and timestamp in a bidirectional active-active configuration – Download « Guenadi N Jilevski's Oracle BLOG | November 8, 2012 | Reply

  2. It’s a pity you don’t have a donate button! I’d most certainly donate to this fantastic blog! I suppose for now i’ll settle
    for bookmarking and adding your RSS feed to my Google
    account. I look forward to brand new updates and will talk about
    this site with my Facebook group. Talk soon!

    Comment by Analisa | March 12, 2013 | Reply

  3. Hey there! This is kind of off topic but I need some help from an established blog.
    Is it very difficult to set up your own blog? I’m not very techincal but I can figure things out pretty quick. I’m thinking about creating my own but I’m not sure where to begin. Do you have any points or suggestions? Thanks

    Comment by Offshore bulletproof Xrumer Hosting | June 20, 2013 | Reply

  4. I am extremely impressed with your writing skills as well as with the layout on your blog.
    Is this a paid theme or did you modify it yourself? Anyway keep up the nice quality writing, it is rare to
    see a great blog like this one these days.

    Comment by haga clic aquí | July 7, 2013 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 776 other followers

%d bloggers like this: