11gR2GoldenGateOracle

UPGRADE CHECKPOINTTABLE – Goldengate

We have a goldengate setup wherein 3 different GoldenGate clients connects and replicat to one target database.Below are the versions being currently used 11.1.1.1, 11.2.1.0.0 and 11.2.1.0.3.
The version 11.2.1.0.3 was recently added, and below are the steps performed

GGSCI (myhost) 2> obey ./dirprm/add_rep.oby
GGSCI (myhost) 3>
GGSCI (myhost) 3> DBLOGIN USERID [email protected] PASSWORD 'xxxxxxxx'
ERROR: Unable to connect to database using user [email protected] Please check privileges.
ORA-12170: TNS:Connect timeout occurred.
GGSCI (myhost) 4>
GGSCI (myhost) 4> ADD REPLICAT rep, extTrail /app/trail/rep/rp, checkpointTable ggate.OGG_CHECKPOINT
REPLICAT added.

dblogin failed but then the replicat got added. Now what happens if we try to delete it

GGSCI (myhost) 5> delete replicat rep
ERROR: Could not delete DB checkpoint for REPLICAT rep (Database login required to delete database checkpoint).
GGSCI (host) 6> info all
Program     Status      Group   Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     ext     00:00:00      00:00:05
REPLICAT    STOPPED     rep     00:00:00      00:00:38

So, now what can be done to delete it. Its simple,

In GG_HOME/dirchk
TEST:/u01/app/oracle/product/ggate/dirchk->ls -lrt
total 20
-rw-rw-r-- 1 ggate dba 4096 Nov 17 01:51 EXT.cpb
-rw-rw-r-- 1 ggate dba 2048 Nov 17 03:39 REP.cpr
-rw-rw-r-- 1 ggate dba   52 Nov 17 03:59 EXT.cps
-rw-rw-r-- 1 ggate dba 8192 Nov 17 03:59 EXT.cpe
Remove "REP.cpr" file.
TEST:/u01/app/oracle/product/ggate/dirchk->rm REP.cpr
TEST:/u01/app/oracle/product/ggate->ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (host) 1> info all
Program     Status      Group  Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT     00:00:01      00:00:07

Once the port issue was resolved, added the replicat “rep” again successfully.

GGSCI (myhost) 2>  DBLOGIN USERID [email protected] PASSWORD "xxxxxxxx"
Successfully logged into database.
GGSCI (myhost) 3> ADD REPLICAT REP, extTrail /app/trail/rep/rp, checkpointTable ggate.OGG_CHECKPOINT
REPLICAT added.
GGSCI (myhost) 4> info all
Program     Status      Group   Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT     00:00:01      00:00:02
REPLICAT    STOPPED     REP     00:00:00      00:00:20
GGSCI (myhost) 2> start REP
Sending START request to MANAGER ...
REPLICAT REP starting
GGSCI (myhost) 3> info all
Program     Status      Group  Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT     00:00:00      00:00:06
REPLICAT    STOPPED     REP     00:00:00      00:04:15

Why is the status ‘STOPPED’? ggserr.log shows

2012-11-17 04:04:46  ERROR   OGG-00446  Oracle GoldenGate Delivery for Oracle, rep.prm:  Supplemental Checkpoint table does not exist.  Create a supplemental checkpoint table with the UPGRADE CHECKPOINTTABLE command in GGSCI if you have upgraded from release 11.2.1.0.0 or earlier.
2012-11-17 04:04:46  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rep.prm:  PROCESS ABENDING.

The target db is on 11.2.0.2.0 version. Checking the checkpoint table

[email protected] > desc ggate.ogg_checkpoint
 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)
04:00:55 [email protected] > /
GROUP_NA  GROUP_KEY      SEQNO        RBA AUDIT_TS                      CREATE_TS                   LAST_UPDATE_TS
-------- ---------- ---------- ---------- ----------------------------- --------------------------- ---------------------------
CURRENT_DIR
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RABC     2253238980          0  230112172 2012-11-17 00:18:32.000000    16-NOV-12-13:56:33          17-NOV-12-04:08:45
/u01/app/oracle/product/ggate
RDEF     451369050        174   13320072 2012-11-17 04:08:48.000000    08-OCT-12-22:02:16          17-NOV-12-04:08:51
/app/ggate
RGBAA    3979228817         85   30933007 2012-11-17 03:03:33.000000    26-OCT-12-10:06:50          17-NOV-12-04:04:09
/app/ggate
RDONE    3150503361        365  276978037 2012-11-17 04:05:33.000000    14-OCT-12-10:34:54          17-NOV-12-04:05:40
/app/ggate

Though we have added and started REP, we don’t see any row for it in checkpoint table. Lets try to run upgrade checkpointtable command

GGSCI (myhost) 2> DBLOGIN USERID [email protected] PASSWORD "xxxxxxx"
Successfully logged into database.
GGSCI (myhost) 3> UPGRADE CHECKPOINTTABLE ggate.OGG_CHECKPOINT
Successfully upgraded checkpoint table ggate.OGG_CHECKPOINT.
GGSCI (myhost) 4> start rep
Sending START request to MANAGER ...
REPLICAT REP starting
GGSCI (myhost) 5> info all
Program     Status      Group  Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT     00:00:02      00:00:26
REPLICAT    RUNNING     REP     02:35:52      00:00:16

From db

[email protected] > desc ggate.ogg_checkpoint
 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)
04:12:24 [email protected] > select * from ggate.ogg_checkpoint;
GROUP_NA  GROUP_KEY      SEQNO        RBA AUDIT_TS                      CREATE_TS                   LAST_UPDATE_TS
-------- ---------- ---------- ---------- ----------------------------- --------------------------- ---------------------------
CURRENT_DIR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOG_CSN
---------------------------------------------------------------------------------------------------------------------------------
LOG_XID
---------------------------------------------------------------------------------------------------------------------------------
LOG_CMPLT_CSN
---------------------------------------------------------------------------------------------------------------------------------
LOG_CMPLT_XIDS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   VERSION
----------
RABC     2253238980          3  119752892 2012-11-20 02:12:30.000000    16-NOV-12-13:56:33          20-NOV-12-04:12:35
/u01/app/oracle/product/ggate
RDEF   451369050        183  229511885 2012-11-20 04:12:22.000000    08-OCT-12-22:02:16          20-NOV-12-04:12:24
/app/ggate
RGBAA   3979228817         97   80060759 2012-11-20 10:07:02.000000    26-OCT-12-10:06:50          20-NOV-12-04:10:09
/app/ggate
REP   2249640216         12  211547890 2012-11-20 04:12:32.000000    17-NOV-12-04:00:48          20-NOV-12-04:12:34
/u01/app/oracle/product/ggate
12957879573832
780.26.260408
12957879573832
780.26.260408
         1
RDONE    3150503361        404  290793846 2012-11-20 04:12:19.000000    14-OCT-12-10:34:54          20-NOV-12-04:12:23
/app/ggate

3 thoughts on “UPGRADE CHECKPOINTTABLE – Goldengate

Leave a Reply