Oracle GoldenGate Tutorial 5 – configuring online change synchronization

Create the GoldenGate Checkpoint table

GoldenGate maintains its own Checkpoints which is a known position in the trail file from where the Replicat process will start processing after any kind of error or shutdown. This ensures data integrity and a record of these checkpoints is either maintained in files stored on disk or table in the database which is the preferred option.

We can also create a single Checkpoint table which can used by all Replicat groups from the single or many GoldenGate instances.

In one of the earlier tutorials we had created the GLOBALS file. We now need to edit that GLOBALS file and add an entry for CHECKPOINTTABLE which will include the checkpoint table name which will be available to all Replicat processes via the EDIT PARAMS command.
GGSCI (CyclopDB) 1> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE ggadmin.chkpt


GGSCI (CyclopDB) 2> dblogin userid ggadmin,password india123
Successfully logged into database.

GGSCI (CyclopDB) 3> ADD CHECKPOINTTABLE GGADMIN.CHKPTAB

Successfully created checkpoint table GGADMIN.CHKPTAB.

GGSCI (CyclopDB) 4> exit
[oracle@CyclopDB gg_home]$ sqlplus ggadmin/india123

SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 17 23:16:02 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc chkptab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP_NAME                                NOT NULL VARCHAR2(8)
 GROUP_KEY                                 NOT NULL NUMBER(19)
 SEQNO                                              NUMBER(10)
 RBA                                       NOT NULL NUMBER(19)
 AUDIT_TS                                           VARCHAR2(29)
 CREATE_TS                                 NOT NULL DATE
 LAST_UPDATE_TS                            NOT NULL DATE
 CURRENT_DIR                               NOT NULL VARCHAR2(255)
 LOG_CSN                                            VARCHAR2(129)
 LOG_XID                                            VARCHAR2(129)
 LOG_CMPLT_CSN                                      VARCHAR2(129)
 LOG_CMPLT_XIDS                                     VARCHAR2(2000)
 VERSION                                            NUMBER(3)

SQL>
ON SOURCE
==========

GGSCI (PrimeDG) 17> edit params ext7
EXTRACT ext7
SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
SETENV (ORACLE_SID=PrimeDG)
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID ggadmin, PASSWORD india123
RMTHOST 192.168.1.253, MGRPORT 7809
RMTTRAIL./dirdat/rt
TABLE scott.emp;
~

GGSCI (PrimeDG) 18> dblogin userid ggadmin,password india123
Successfully logged into database.

GGSCI (PrimeDG) 19> ADD EXTRACT ext7, TRANLOG, BEGIN NOW
EXTRACT added.


GGSCI (PrimeDG) 20> ADD RMTTRAIL ./dirdat/rt, EXTRACT ext7
RMTTRAIL added.







ON TARGET SYSTEM

Create the online Replicat group
GGSCI (CyclopDB) 6> ADD REPLICAT rep7, EXTTRAIL ./dirdat/rt
REPLICAT added

Create a parameter file for the online Replicat group, rep7

GGSCI (CyclopDB) 4> edit params rep7

REPLICAT rep7
ASSUMETARGETDEFS
USERID ggadmin, PASSWORD india123
MAP scott.emp, TARGET scott.emp;

ON SOURCE

Start the Extract process

GGSCI (PrimeDG) 21> START EXTRACT ext7

Sending START request to MANAGER ...
EXTRACT EXT7 starting



GGSCI (redhat346.localdomain) 17> STATUS EXTRACT ext7
EXTRACT EXT1: RUNNING

GGSCI (PrimeDG) 22> STATUS EXTRACT ext7
EXTRACT EXT7: ABENDED



GGSCI (CyclopDB) 9> START REPLICAT rep7
REPLICAT REP7 is already running.




GGSCI (CyclopDB) 10> INFO REPLICAT rep7

REPLICAT   REP7      Last Started 2016-04-17 23:39   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint  File ./dirdat/rt000000
                     First Record  RBA 0


LET US NOW TEST …

ON SOURCE

SQL> conn scott/tiger
Connected.

SQL> UPDATE emp SET Job='MANAGER',Sal=7000,Deptno=10 WHERE Ename= 'ALLEN';

1 row updated.



SQL> SELECT SAL FROM emp WHERE ename=  'ALLEN';

       SAL
----------
      7000
SQL> commit;

Commit complete

ON TARGET

SQL>  SELECT SAL FROM emp WHERE ename=  'ALLEN';

       SAL
----------
      1600


GGSCI (CyclopDB) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     REP5        00:00:00      00:02:45
REPLICAT    ABENDED     REP7        00:09:37      00:00:04


GGSCI (CyclopDB) 4>


GGSCI (CyclopDB) 4> exit
[oracle@CyclopDB gg_home]$ cat ggserr.log

MP OCI Error ORA-01403: no data found, SQL <UPDATE "SCOTT"."EMP" SET "JOB" = :a1,"SAL" = :a2,"DEPTNO" = :a3 WHERE "EMPNO" = :b0>.
2016-04-18 00:02:40  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rep7.prm:  Repositioning to rba 1023 in seqno 0.
2016-04-18 00:02:40  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, rep7.prm:  Error mapping from SCOTT.EMP to SCOTT.EMP.
2016-04-18 00:02:40  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rep7.prm:  PROCESS ABENDING.
[oracle@CyclopDB gg_home]$