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.
|
Comments
Post a Comment