Golden gate missing checkpoint table - GoldenGate

Hello gurus,
I am trying to implement live replication with Golden Gate but I can't create the checkpoint table.
GGSCI () 26> ADD CHECKPOINTTABLE
ERROR: Missing checkpoint table specification.
GGSCI (q) 27> ADD REPLICAT rep1, EXTTRAIL /t01/software/gg2/dirdat/rt
ERROR: No checkpoint table specified for ADD REPLICAT.
GGSCI () 28> ADD CHECKPOINTTABLE
ERROR: Missing checkpoint table specification.
GGSCI (qcdvap1031.gnet.qc.bell.ca) 29> edit ./GLOBALS
ERROR: Invalid command.
GGSCI () 30> edit params ./GLOBALS
GGSCHEMA GGS_OWNER
CHECKPOINTTABLE GGS_OWNER.CHKPTAB
GGSCI (q) 31> ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB
Successfully created checkpoint table GGS_OWNER.CHKPTAB.
GGSCI () 32> ADD REPLICAT rep1, EXTTRAIL /t01/software/gg2/dirdat/rt
ERROR: No checkpoint table specified for ADD REPLICAT.
Not sure what I am missing here, can anyone please help?
Thank you very much, 

ADD REPLICAT <group name>
{, SPECIALRUN |
, EXTFILE <full path name> |
, EXTTRAIL <full path name>}
[, BEGIN {NOW | YYYY-MM-DD HH:MM[:SS[.CCCCCC]]} |
, EXTSEQNO <seqno>, EXTRBA <rba>]
[, CHECKPOINTTABLE <owner.table> | NODBCHECKPOINT]
[, PARAMS <parameter file>]
[, REPORT <report file>]
[, DESC “<description>”]
Either specify the checkpoint table or use NODBCHECKPONT 

Hi Exor,
First you need to create a schema in database and then while adding check point table prefix the schema name.
Do remember to login before creating checkpoint table.
DBLOGIN USERID ggsadm, PASSWORD XXXXX
ADD CHECKPOINTTABLE ggsadm.ggschkpt 

While adding replicate. specify checkpoint table.
ADD REPLICAT rep1, EXTTRAIL /t01/software/gg2/dirdat/rt, CHECKPOINTTABLE GGS_OWNER.CHKPTAB 

Hi,
First you need to create a schema in database and then while adding check point table prefix the schema name.
No need to create a separate schema for the checkpoint table.
Regards, 

specifying the checkpoint table in the add replicat worked but I am asking myself what is the point of specifying the check point table in the global file then? is that read by anyone?
thank you, 

Hi,
Use the CHECKPOINTTABLE parameter in a GLOBALS parameter file to specify the name of a
default checkpoint table that can be used by all Replicat groups in one or more Oracle
GoldenGate instances. All Replicat groups created with the ADD REPLICAT command will
default to this table unless it is overridden by using the CHECKPOINTTABLE option of that
command.
To create the checkpoint table, use the ADD CHECKPOINTTABLE command in GGSCI.
Regards, 

The point of specifying the check point table in the global file is whenever you restart your replicate it will pick up check point table form global file.
But while adding you always have to specify tablename.
hope this make sense 

Hi,
Once created is a done deal. Any restart after that will use whatever is specified in add command. Globalls is to share among Replicat groups in one or more Oracle
GoldenGate instances.
Regards, 

Hi,
Just Exit from ggsci session, login again and try to add replicat.

Related

OGG v12.2.0.1 Alter replicat CHECKPOINTTABLE issue

Hello Evyone, We have a replicat that was created/added before my lifetime with the NODBCHECKPOINT param on Windows Server 2008R2. We are now wanting to incorporate CHECKPOINTTABLE into the replicat configuration. It appears that I am unable to ALTER an existing replicat to utilize CHECKPOINTTABLE. I have created the table on the Target MS SQLDB. If I delete and add the replicat with the CHECKPOINTTABLE param, it works. Is it correct to say the only way to incorporate CHECKPOINT after a replicat is added, is to delete it and re-add? Btw, I have also tried the ALTER in conjunction with/wo NONINTEGRATED; with/wo extseqno and rba, but same result. I appreciate your time, Thank You ! GGSCI (GGSQA) 74> stop abc1Sending STOP request to REPLICAT ABC1 ...Request processed. GGSCI (GGSQA) 80> ALTER REPLICAT ABC1, CHECKPOINTTABLE GGS_CHECKPOINTGGSCI (GGSQA) 80..ERROR: Invalid parameter specified for ALTER REPLICAT.
Yes the only way to have put in a checkpoint table is to delete and add. Note that NONINTEGRATED, CHECKPOINTTABLE owner.table(Oracle) Switches Replicat from integrated mode to non-integrated mode. Is for Oracle only as Integrated is only relevant for that DB. The reference is misleading for this syntaxALTER REPLICAT group_name[threadID], {ADD REPLICAT option [, ...] As you are using MS SQL, you might want to read http://www.oracle.com/technetwork/middleware/goldengate/overview/statement-of-direction-gg-132120.pdfOracle is discontinuing support for MS SQL going forward. Also see thread Oracle GoldenGate 12.2.0.1.0 download for Microsoft SQL Server CheersKee Gan 
Hi , It is better to have a Global Checkpoint table for the replicat process. But you cannot alter the replicat process and add the checkpoint table to it. The only way is to Delete and Re-Add the replicat process. Please check below steps., 1. Create a Global Checkpoint Table at the target side.2. Make an entry of the newly created checkpoint table in the ./GLOBALS parameter file.3. Whenever you do modifications to the ./GLOBALS parameter file, you need to logout and login the ggsci prompt.4. Stop the Replicat process in the target side.5. Get the output of the below command, INFO REPLICAT <REPLICAT_NAME>, DETAIL 6. Note down the Sequence Number and the RBA from the output.7. Take a backup of the existing checkpoint table (if existing, else skip this point). Both from the database as well as the files (cpe files / cpr files) in the dirchk directory.8. dblogin in the ggsci prompt dblogin userid <user_name>, password <password> 9. Delete the checkpoint table which is dedicated to the Replicat process. (if already exists, else skip this point).10. Delete the Replicat process11. Add the Replicat Process without specifying the checkpoint table in it. So by default, it will pick the checkpoint table mentioned in the ./GLOBALSparameter file.12. Alter the Replicat process with the noted sequence number and RBA alter replicat <rep_name>, extseqno <noted_seq_no>, extrba <noted_rba> 13. Start the Replicat process. start replicat <rep_name> 14. Check if the replication is working fine. Regards,Veera

ERROR   OGG-00446  : Checkpoint table ogg_user.checkpointtable does not exist.

Hi,  We are using GoldenGate version 12.2.0.1.1 for replicating data from a Oracle 12C source DB to Oracle 12C target DB trying for unidirectional replication. Today when i was trying to start the REPLICAT process in target GG instance, the process was either getting abended or stopped by throwing the error "ERROR OGG-00446  Oracle GoldenGate Delivery for Oracle, repl.prm: Checkpoint table ogg_user.checkpointtable does not exist.Create this table with the ADD CHECKPOINTTABLE command in GGSCI".  I tried to delete the REPLICAT and CHECKPOINTTABLE and recreate both with different names, but the same error appears with the newly created names. Checkpointtable is surely present in the DB and GoldenGate is somehow unable to reach the table. Please help me resolve the issue
Try to restart the replicate from different session other than the prompt where you have created checkpoint table. Certain time newly added table is not resolved and get the error. Also try below given options.   
Thanks for the suggestion, i have tried doing it but it Still throws the same error
       Try the below given options.              dblogin userid OGG, password OGG                 Delete  CHECKPOINTTABLE GGS_OWNER.CHKPTAB                 ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB                 Restart the mgr and replicat process from other session         Check you have configured correct checkoint table in ./GOBAL parameter file .         Use the parameter NODBCHECKPOINT in replicat which will tells replicate to ignore checkpint table.  Note checkpint table       is optional.  Verify also the table does exist in ogg schema with DESC TABLE_NAME command  Steps for recreating checkpoint table are  To create a new checkpoint table:ggsci> dblogin userid "user id"  PASSWORD "password"ggsci> add checkpointtable "checkpoint table name"ggsci> info "replicat"  (Make a note of the trail file sequence number and RBA )ggsci> delete "replicat"!ggsci> add rep "replicat" , exttrail  "trail file"  extseqno ###, extrba ####, checkpointtable "checkpoint table name"  ( Use the extseqno and extrba from the INFO command)ggsci> start rep "replicat" To use checkpoint table on replicat DB instance:Set ORACLE_SID and ORACLE_HOME environment variablesRecycle manager processggsci>stop mgrggsci> start mgrggsci> start rep "replicat" =========================OR Before doing theses steps, please stop the replicat: 1- Delete the checkpoint 2- recreate the checkpoint: GGSCI>add checkpointtable SYS.GGS_CHECKPOINT make sure to make the change in the GLOBALS to reflect the schema 3-record the checkpoints (from GGSCI>info ,showch) for each object, delete it and re-add it specifying the checkpoint table GGSCI>add replicat XXXX, exttrail , checkpointtable SYS.GGS_CHECKPOINT
What is the userid parameter in the replicat, is it userid ogg_user, password xxxxxxIf not use this, I expectsqlplus ogg_user/password> desc ogg_user.checkpointtable works? CheersKee Gan
Hi , Could you please provide the steps which you have followed to recreate the checkpointtable? Whenever you make the changes to the ./GLOBALS, you need to exit and relogin to the ggsci prompt. Steps to add the checkpoint table 1. Add the checkpoint table in the ./GLOBALS parameter2. Exit from the ggsci prompt3. open a new session and login to ggsci prompt4. issue the below commandadd checkpointtable this should automatically take the checkpointtable 5. add the replicat process. but do not specify any checkpointtable here.6. Start the replicat process Regards,Veera

Alter Replicat checkpoint table

Hi, I want to change the checkpoint table which currently used by replicat process.Please let me know how can I achieve this.Thanks for your time. RegardsSuman
Since you use a checkpoint table then this should be classic replicat and you need to delete and add replicat again. No big deal. run ggsciadd checkpointtable <new table>Stop replicat Info replicat showch(save this somewhere)delete replicat <replicat>Add replicat <eplicat name>, extseqno <the saved info sequence>, extrba <the saved info rba>, checkpointtable <new table> start replicat <replicat> CheersKee Gan
Hi K.Gan,While doing Add replicat step Iam getting an error like below:ERROR: EXTFILE or EXTTRAIL required for ADD REPLICAT.Let me know how can I resolve this.ThanksSuman
Hi , I have resolved this by adding exttrail like below:ADD REPLICAT DELIVER1, INTEGRATED, EXTTRAIL ./dirdat/rt,extseqno 0, extrba 0,checkpointtable ODI_WORK.ODIOGGCKPTReplicat started.Now both extract and replicat running.But data is not copied from source to target. Please let me know if  I am doing anything wrong. ThanksSuman
Hi K.gan, Thanks for your time.This issue resolved and the GG procees works fine now.I have not correctly placed the EXTTRAIL Path. After changing this, it worked fine. ThanksSuman
Your replicat is integrated and the checkpointtable is not used so it does not make a difference. See the manual for add replicat option checkpointtable and it says "Do not use this option with the INTEGRATED option."CheersKee Gan
Hi K.gan, In my checkpoint table I have RBA value in Negative.  Please let me know how can I resolve this. ThanksSuman K
Hi SumanSee my last update, the checkpointtable is never used for Integrated Replicat. GGSCI should produce an error when you tried to do this but unfortunately it does not. So you will get garbage. CheersKee Gan

Unable to replicate schema using goldengate after initial load

Hi ,
I have a schema and i want to replicate it to another database using goldengate. I configured initial-laod-extract on the source and corresponding replicat on the target system. By this i was able to successfully do the initial load of data from source to target. After initial load i have create another separate extract and replicat process for replication of data. But the data is not getting replicated after the initial load.
This is one way replication.
databases --- oracle 11g r2
o/s --- rhel 5
gg - 11.1
I am new to Goldengate so i might be missing something, please help me to resolve this..
Regards, 
what steps did you perform to replicat the data after initial load?
Here a demo that gets the changes and pushes them to the target (w/o a data pump):
Just again a summary about the pre requisites:
Prepare Oracle database SOURCE:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
alter system switch logfile;
SELECT supplemental_log_data_min FROM v$database;
commit;
Within the GG Manager (should be done already):
create subdirs
edit params mgr
=> Port 7809
==> start mgr
Now I define an extract process on my source side and tell it where I want to push the data to (rmttrail):
Extract Prozess:
################
edit param exkgr
EXTRACT exkgr
USERID kgr, PASSWORD kgr
RMTHOST zkupchv119.de.oracle.com, MGRPORT 7809
RMTTRAIL ./dirdat/ek
TABLE kgr.tcustmer;
TABLE kgr.tcustord;
Now add and start the extract:
GGSCI (edvmr1p0) 5> add extract exkgr, tranlog, begin now
EXTRACT added.
GGSCI (edvmr1p0) 6> add exttrail ./dirdat/ek, extract exkgr, megabytes 5
EXTTRAIL added.
GGSCI (edvmr1p0) 7> start exkgr
Sending START request to MANAGER ...
EXTRACT EXKGR starting
GGSCI (edvmr1p0) 8> Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXKGR 00:00:00 00:00:06
so from now on the extract will put the inforation automatically to the target location - when you have a look at the rmthost location you'll see depending on rmttrail a file "ek<6 Znumbers>" is created and the size increases when you add data.
#############
Replication Process
pre requisite is defining a checkpoint table:
SQLPlus:
0. SQL*Plus als user kgr: #chkpt_ora_create.sql
GoldenGate Manager:
1. add Checkpointtable in GLOBALS using edit param ./GLOBALS:
CHECKPOINTTABLE kgr.ggschkpt
2. GGSCI (ZKUPCHV119) 2> DBLOGIN userid kgr, password kgr
Successfully logged into database.
3. GGSCI (ZKUPCHV119) 2> add checkpointtable
No checkpoint table specified, using GLOBALS specification (kgr.ggschkpt)...
Successfully created checkpoint table kgr.ggschkpt.
Now a parameter file for the replicat process:
edit params repkg
REPLICAT repkg
USERID kgr, PASSWORD kgr
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/diskg.dsc, purge
MAP kgr.*, TARGET kgr.*;
Finally start the REPLICAT:
GGSCI (edvmr1p0) 7> add replicat repkg, exttrail ./dirdat/ek
REPLICAT added.
GGSCI (edvmr1p0) 8> start repkg
Sending START request to MANAGER ...
REPLICAT REPKG starting
GGSCI (ZKUPCHV119) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPKG 00:00:00 00:00:00
When I now add something to the source it is replicated to the target.
... Yes, I know a very basic example, but always good as a starter. For more skilled people use data pump etc.
Edited by: kgronau on Jan 28, 2013 3:29 PM
when it still fails, please provide the extract and replicat logs 
Have you tried the tutorials at Oracle Learning Library?
http://apex.oracle.com/pls/apex/f?p=44785:2:0::NO:2,RIR,CIR:P2_GROUP_ID,P2_PRODUCT_ID:1001,2023
Oracle to Oracle
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/goldengate/11g/GGS_Sect_Config_WinUX_ORA_to_WinUX_ORA.pdf

How to update Goldengate checkpoint table

Hi, I have dropped an old replicat on the target system without loggin into db( DBLOGIN ). When I check info all, the replicat process is no longer there. However checkpoint table has an entry for this replicat. How do I get the checkpoint table updated so that it will be clean.  ( I tried to add the replicat again with the same RBA and SeqNo and deleted it again but that didn't help.)Please advice. Thanks.
If that is the only Replicat for that database, just drop and recreate the checkpoint table, and re-add the Replicat. DBLOGIN....DELETE CHECKPOINTTABLE schema.tablenameADD CHECKPOINTTABLE schema.tablenameADD REPLICAT... CHECKPOINTTABLE schema.tablename
This is an old replicat used for fixing an issue and we already have the actual/original replicat running and taking care of replication.So we are trying to remove this replicat and clean up Checkpoint table. Also, we have another replicat database using the same checkpoint table name. What will be the best way to cleanup the Checkpoint table without effecting the replication in progress on production.
I just found this parameter in the Reference Guide, maybe that will work for you.  Test, test, test it first if you can. CLEANUP CHECKPOINTTABLEUse CLEANUP CHECKPOINTTABLE to remove checkpoint records from the checkpoint table when there is no checkpoint file associated with it in the working Oracle GoldenGate directory (from which GGSCI was started). The purpose of this command is to remove checkpoint records that are not needed any more, either because groups were changed or files were moved.Use the DBLOGIN command to establish a database connection before using this command.SyntaxCLEANUP CHECKPOINTTABLE [[container. | catalog.]owner.table] container. | catalog.The Oracle pluggable database or SQL/MX catalog, if applicable. If this option is omitted, the catalog or pluggable database defaults to the one that is associated with the SOURCEDB, USERID, or USERIDALIAS portion of the DBLOGIN command (depending on the database).owner.tableThe owner and name of the checkpoint table to be cleaned up. If an owner and name are not specified, the table that is affected is the one specified with the CHECKPOINTTABLE parameter in the GLOBALS parameter file. ExampleCLEANUP CHECKPOINTTABLE ggs.fin_check
Hi , If your old replicat and the Original Replicat is using the same checkpoint table then it is not required to delete or cleanup the checkpoint table. If you think to not to disturb or take risk on the Original Replicat, follow the below steps., 1. Drop the Old Replicat2. Create the Old Replicat with assigning a unique checkpoint table for it. ADD REPLICAT rep1, EXTTRAIL ./dirdat/rt, CHECKPOINTTABLE GGS_OWNER.<CHECKPOINTTABLE_NAME> By adding the separate checkpointtable for the Old replicat, the original replicat process and the checkpointtable which is declared in the ./GLOBALS will not be disturbed. Regards,Veera

Categories

Resources