Source
|
Target
|
|
Database Version
|
11.2.0.4
|
11.2.0.4
|
OS Version
|
OEL 6 – 64 Bit
|
OEL 6 – 64 Bit
|
OGG HOME
|
/u01/GG/training/source
|
/u01/GG/training
|
GoldenGate user
|
ggs_admin
|
ggs_admin
|
OGG Core
|
11.2.1.0.0OGGBP_PLATFORMS_140304.2209_FBO
|
|
OGG Version
|
11.2.1.0.20
|
Database Prerequisites (On Both Source and Target)
Enable GoldenGate Replicaiton at database level (Applicable for 11.2.0.4 and
above Database Versions)
SQL> show parameter
enable_goldengate_replication
NAME TYPE VALUE
------------------------------------
----------- --------
enable_goldengate_replication boolean TRUE
|
Keep your database in Archivelog Mode
SQL>
select LOG_MODE from v$database;
LOG_MODE
------------
ARCHIVELOG
|
Enable Supplemental Logging for Primary Key, Unique Index,
Foreign Keys and All Data.
Note: supplemental_log_data_min
can be IMPLICIT or YES
SQL> SELECT
supplemental_log_data_min,
supplemental_log_data_pk,
supplemental_log_data_ui,
supplemental_log_data_fk,
supplemental_log_data_all
FROM v$database;
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
NO NO
NO NO NO
SQL> ALTER DATABASE ADD
SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SQL> ALTER DATABASE ADD
SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
SQL> ALTER DATABASE ADD
SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
SQL> ALTER DATABASE ADD
SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
SQL> SELECT
supplemental_log_data_min,
supplemental_log_data_pk,
supplemental_log_data_ui,
supplemental_log_data_fk,
supplemental_log_data_all
FROM v$database;
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
YES YES YES YES YES
|
Source:
GGSCI
(ogg1.sukumar.com 1> edit param ./GLOBALS
GGSCHEMA
GGS_ADMIN
CHECKPOINTTABLE
GGS_ADMIN.CHKPTAB
|
Target:
GGSCI
(ogg2.sukumar.com 1> edit param ./GLOBALS
GGSCHEMA
GGS_ADMIN
CHECKPOINTTABLE
GGS_ADMIN.CHKPTAB
|
Create checkpoint table on both Source and Target
Source:
GGSCI (ogg1.sukumar.com 1> add checkpointtable ggs_admin.CHKPTAB
Successfully created checkpoint table ggs_admin.chkptab.
|
Target:
GGSCI (ogg2.sukumar.com 1> add checkpointtable ggs_admin.CHKPTAB
Successfully created checkpoint table ggs_admin.chkptab.
|
Create Parameter Files on Source:
Extract
GGSCI
(ogg01.sukumar.com) 5> edit param ext1
EXTRACT ext1
SETENV
(NLS_LANG=AMERICAN_AMERICA.UTF8)
SETENV
ORACLE_SID=SUKUMAR1
USERID
ggs_admin, PASSWORD ggs_admin
EXTTRAIL
/u01/GG/training/source/dirdat/e1
TRANLOGOPTIONS
DBLOGREADER
TRANLOGOPTIONS
EXCLUDEUSER GGS_ADMIN
TABLE
ABC.RESERVATION,
GETBEFORECOLS(
ON UPDATE
KEYINCLUDING(TIME),
ON DELETE
KEYINCLUDING(TIME));
|
Pump
GGSCI (ogg01.sukumar.com) 6> edit param dpump
EXTRACT dpump
USERID ggs_admin, PASSWORD ggs_admin
RMTHOST ogg02.sukumar.com, MGRPORT 4444
RMTTRAIL /u01/GG/training/dirdat/p1
TABLE ABC.*;
|
Replicat
GGSCI (ogg01.sukumar.com) 8> edit param rep2
REPLICAT rep2
SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8)
SETENV ORACLE_SID=SUKUMAR1
USERID ggs_admin PASSWORD ggs_admin
DISCARDFILE
/u01/GG/training/source/dirrpt/reptr.dsc, APPEND, MEGABYTES 512
ALLOWNOOPUPDATES
ASSUMETARGETDEFS
MAP ABC.RESERVATION, TARGET ABC.RESERVATION,
GETBEFORECOLS
(
ON
UPDATE KEYINCLUDING (TIME),
ON
DELETE KEYINCLUDING (TIME)
),
&
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMIN
(TIME))), &
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMIN
(TIME))), &
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT,
OVERWRITE)), &
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT,
OVERWRITE)), &
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT,
DISCARD));
|
Add extract, pump and Replicat Process to GoldenGate
GGSCI (ogg01.sukumar.com) 9> add extract
ext1 tranlog, begin now
GGSCI (ogg01.sukumar.com) 10> add exttrail
/u01/GG/training/source/dirdat/e1, extract ext1
GGSCI (ogg01.sukumar.com) 11> add extract dpump,
exttrailsource /u01/GG/training/source/dirdat/e1
GGSCI (ogg01.sukumar.com) 12>add rmttrail
/u01/GG/training/dirdat/p1, extract dpump
GGSCI (ogg01.sukumar.com) 13> add replicat rep2
exttrail /u01/GG/training/source/dirdat/p2, CHECKPOINTTABLE GGS_ADMIN.CHKPTAB
|
Check the Process status
GGSCI (ogg01.sukumar.com) 14> info all
Program
Status Group Lag at Chkpt Time Since Chkpt
MANAGER
RUNNING
EXTRACT STOPPED DPUMP 00:00:00 00:00:20
EXTRACT STOPPED
EXT1 00:00:00 00:00:15
REPLICAT STOPPED
REP2 00:00:00 00:00:11
|
Start all the process and check the status (All should be
in running state)
GGSCI (ogg01.sukumar.com) 14> start *
GGSCI (ogg01.sukumar.com)
15> info all
Program
Status Group Lag at Chkpt Time Since Chkpt
MANAGER
RUNNING
EXTRACT RUNNING DPUMP 00:00:00 00:00:03
EXTRACT RUNNING EXT1 00:00:00 00:00:00
REPLICAT RUNNING REP2 00:00:00 00:00:02
|
Add TRANDATA on to the table
GGSCI (ogg01.sukumar.com) 5> dblogin userid
ggs_admin, password ggs_admin
Successfully logged into database.
GGSCI (ogg01.sukumar.com) 6> add schematrandata
abc
2014-08-27 08:15:31
INFO OGG-01788 SCHEMATRANDATA has been added on schema
abc.
|
Create Parameter Files on Target:
Extract
GGSCI (ogg02.sukumar.com) 6> edit param ext2
EXTRACT ext2
SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8)
SETENV ORACLE_SID=SUKUMAR2
USERID ggs_admin, PASSWORD ggs_admin
EXTTRAIL /u01/GG/training/source/dirdat/e2
TRANLOGOPTIONS DBLOGREADER
TABLE ABC.RESERVATION,
GETBEFORECOLS(
ON UPDATE KEYINCLUDING(TIME),
ON DELETE KEYINCLUDING(TIME));
|
Pump
GGSCI (ogg02.sukumar.com) 7> edit param dpump
EXTRACT dpump
USERID ggs_admin, PASSWORD ggs_admin
RMTHOST ogg01.sukumar.com, MGRPORT 8877
RMTTRAIL /u01/GG/training/source/dirdat/p2
TABLE ABC.*;
|
Replicat
GGSCI (ogg02.sukumar.com) 5> edit param rep1
REPLICAT rep1
SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8)
SETENV ORACLE_SID=SUKUMAR2
USERID ggs_admin PASSWORD ggs_admin
DISCARDFILE /u01/GG/training/dirrpt/reptr.dsc,
APPEND, MEGABYTES 512
ALLOWNOOPUPDATES
ASSUMETARGETDEFS
MAP ABC.RESERVATION, TARGET ABC.RESERVATION,
GETBEFORECOLS
(
ON UPDATE KEYINCLUDING (TIME),
ON DELETE KEYINCLUDING (TIME)
),
&
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT,
USEMIN (TIME))), &
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT,
USEMIN (TIME))), &
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT,
OVERWRITE)), &
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT,
OVERWRITE)), &
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT,
DISCARD));
|
Add extract, pump and Replicat Process to GoldenGate
GGSCI (ogg02.sukumar.com) 6> add extract
ext2 tranlog, begin now
GGSCI (ogg02.sukumar.com) 7> add exttrail
/u01/GG/training/dirdat/e2 extract
ext2
GGSCI (ogg02.sukumar.com) 8> add extract
dpump, exttrailsource
/u01/GG/training/source/dirdat/e2
GGSCI (ogg02.sukumar.com) 9> add rmttrail
/u01/GG/training/source/dirdat/p2
extract dpump
GGSCI (ogg02.sukumar.com) 10> add replicat
rep1, exttrail /u01/GG/training/dirdat/p1, CHECKPOINTTABLE GGS_ADMIN.CHKPTAB
|
Check the Process status
GGSCI (ogg02.sukumar.com) 11> info all
Program
Status Group Lag at Chkpt Time Since Chkpt
MANAGER
RUNNING
EXTRACT
STOPPED DPUMP 00:00:00 00:00:11
EXTRACT
STOPPED EXT2 00:00:00 00:00:09
REPLICAT
STOPPED REP1 00:00:00 00:00:06
|
Start all the process and check the status(All should be
in running state)
GGSCI (ogg02.sukumar.com) 11> start *
GGSCI (ogg02.sukumar.com)
11> info all
Program
Status Group Lag at Chkpt Time Since Chkpt
MANAGER
RUNNING
EXTRACT
RUNNING DPUMP 00:00:00 00:00:04
EXTRACT
RUNNING EXT2 00:00:00 00:00:02
REPLICAT
RUNNING REP1 00:00:00 00:00:00
|
Add TRANDATA to the table
GGSCI (ogg02.sukumar.com) 5> dblogin userid
ggs_admin, password ggs_admin
Successfully logged into database.
GGSCI (ogg02.sukumar.com) 6> add
schematrandata abc
2014-08-27 08:15:31 INFO
OGG-01788 SCHEMATRANDATA has
been added on schema abc.
|
Soruce Table
SQL> desc ABC.RESERVATION
Name Null? Type
----------------- -------- ------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
TIME TIMESTAMP(6)
|
Target Table
SQL> desc ABC.RESERVATION
Name Null? Type
----------------- -------- ------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
TIME TIMESTAMP(6)
|
Let’s test with Inserts from both the Nodes.
SQL> insert
into reservation values (1,'ogg1',sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL>
insert into reservation values (2,'ogg2', sysdate);
1 row
created.
SQL>
commit;
Commit
complete.
SQL> select *
from reservation;
ID NAME TIME
----------
-------------------- ------------------------------
2 ogg2 27-AUG-14 09.38.09.000000 AM
1 ogg1 27-AUG-14 09.34.02.000000 AM
SQL>
select * from reservation;
ID NAME TIME
----------
-------------------- ------------------------------
1 ogg1 27-AUG-14 09.34.02.000000 AM
2 ogg2 27-AUG-14 09.38.09.000000 AM
|
Now test with the Update statements,
As we have used the Conflict Resolution as USEMIN reference
to TIME column, which ever record have the minimum time will be updated to the
table.
As simple as , First come First Serve of COMMIT.
SQL> update reservation set NAME='UPDATED ON
OGG1',TIME=sysdate where id=1;
1 row updated.
SQL> select * from reservation where id=1;
ID
NAME TIME
---------- --------------------
------------------------------
1
UPDATED ON OGG1 27-AUG-14 09.38.14.000000
AM
SQL> update reservation set NAME='UPDATED ON
OGG2', TIME=sysdate where id=1;
1 row updated.
SQL> select * from reservation where id=1;
ID
NAME TIME
---------- --------------------
------------------------------
1
UPDATED ON OGG2 27-AUG-14
09.42.21.000000 AM
|
We dint commit the Transaction yet on any of the database,
hence it is showing the record with two different times.
but I am commiting the record on ogg2 first then ogg1
SQL>
update reservation set NAME='UPDATED ON OGG2', TIME=sysdate where id=1;
1 row
updated.
SQL> Commit;
Commit complete.
SQL> update reservation
set NAME='UPDATED ON OGG1',TIME=sysdate where id=1;
1 row updated.
SQL> Commit;
Commit complete.
|
In General Latest update should be effective in the
database, As we are using USEMIN to the Time column, First Commit will always
be updated when the transaction at the other end is open/ not committed.
SQL>
select * from reservation;
ID NAME TIME
----------
-------------------- ------------------------------
2 ogg2 27-AUG-14 09.38.09.000000 AM
1 UPDATED ON OGG2 27-AUG-14 09.42.21.000000 AM
SQL>
select * from reservation;
ID NAME TIME
----------
-------------------- ------------------------------
1 UPDATED ON OGG2 27-AUG-14 09.42.21.000000 AM
2 ogg2 27-AUG-14 09.38.09.000000 AM
|
Now Let’s work with Delete Statements.
Deleting the record from ogg1, keep the transaction open.
Then, delete the record from ogg2.
Commit the transaction from ogg1, deletes the record on
ogg2 also. If you commit on ogg2 now, in general it should throw no data found
error. As we have used DELETEROWMISSING default ignore, transaction will commit
successfully even though there is no record in the database.
SQL>
delete from reservation where id=1;
1
row deleted.
SQL>
delete from reservation where id=1;
1
row deleted.
SQL>
commit;
Commit
complete.
SQL>
commit;
Commit
complete.
|