GOLDENGATE

Shell script to monitor goldengate process

OBJECTIVE: Write a script is to monitor goldengate processes like extract and replicat, And in case extract or replicat is down, it will send alert to the respective email ids. SOLUTION: Below is the shell script.(gg_alert.sh) cat gg_alert.sh #!/bin/bash EMAIL_LIST=”support@dbaclass.com” OIFS=$IFS IFS=” ” NIFS=$IFS function status { OUTPUT=`$GG_HOME/ggsci << EOF info all exit EOF` } […]

COLS & COLSEXCEPT FILTER in goldengate

                    COLS & COLSEXCEPT filter parameters are used to exclude or include(select) few columns of a table from goldengate extraction. i.e if a table on source has 5 column, and requirement is to replicat only 3 columns, then COLS and COLSEXCEPT filter can be used These […]

Schema replication using oracle goldengate

In this tutorial, we will explain how to do schema replication in goldengate. Schema replication means, replicating all the tables of a schema.   1. add supplemental for all tables of a schema [ SOURCE ] add trandata <schema_name>.* GGSCI > dblogin userid ggate_user, password ggate_user Successfully logged into database. GGSCI > ADD TRANDATA COMP_USR.* […]

Enable DDL replication in goldengate

In this below tutorial, we will enable DDL replication for a table, which is already part of  goldengate replication and currently only DML transactions are getting replicated. TABLE_NAME = APPLIANCE.CLASSTAB1 To enable DDL replication, We need to run few SQL scripts(provided under $GG_HOME)  on the source database. and update the extract parameter file. 1. Run role_setup.sql where […]

Setting up Table replication in oracle goldengate

In this below tutorial, we will setup one-way goldengate replication for below two tables from database SRCDB to TRGDB. DBACLASS.EMP; DBACLASS.DEPT; SEE – Oracle goldengate Fundamentals NOTE – Here we are replicating only DML transactions PREREQUISITE: Make sure the goldengate installation is completed and manager process is running on both source and target hosts. REFER: Installation […]

Obey command in goldengate

Like we execute .sql file against SQL, we can write multiple goldengate commands inside a .oby file and execute it against GGSCI prompt using OBEY command. EXAMPLE: In the below example, we will add supplemental login(add trandata) for multiple tables using obey command. 1. Create a text file and put goldengate commands. vi addtran.oby dblogin USERID […]

OGG-01028 partial record at sequence extract abended

         If extract process in goldengate abend with OGG-01028  partial record at sequence  error, then you below solution.   PROBLEM:( snippet from the extract log) 2016-12-25 14:05:41 INFO OGG-01738 BOUNDED RECOVERY: CHECKPOINT: for object pool 2: p57354_Redo_Thread_2: start=SeqNo: 8786, RBA: 3756633616, SCN: 2718.265350089 (11673986460617), Timestamp: 2 016-12-25 14:05:40.000000, Thread: 2, end=SeqNo: 8786, RBA: […]

Defgen utility in Oracle goldengate

     If source and target table structures are different, like source has then we need to use definition file in replicat file. Example: Here we will generate definition file of a table DBACLASS.EMPLOYEE from source database and use it in target db.   1. Create defgen parameter file ggsci>edit params defgen defsfile /u01/ggate/dirsql/dbaclass_def.sql userid ggatebss, […]

ERROR: No checkpoint table specified for ADD REPLICAT

           While adding the replicate, you may get the below error. GGSCI (db75-2) 2> add replicat LOYTSTR, exttrail /ggatebss/oradata/RC12C/R4 ERROR: No checkpoint table specified for ADD REPLICAT. Solution:   Checkpoint table need to be created in the replication side. 1. Login to ggsci with dblogin GGSCI (db75-2) 4> dblogin USERID TEST_GGATE, […]

OGG-00665 OCI Error describe for query

Problem:    In goldengate version 12, while starting the extract,it may abend with below error.  2016-04-11 12:31:10  ERROR   OGG-00665  OCI Error describe for query (status = 942-ORA-00942: table or view does not exist), SQL<SELECT MOD(t.property, POWER(2, 64)) as property,   opq.flags   FROM sys.tab$ t, sys.opqtype$ opq   WHERE t.obj# = 4214597 and t.obj#=opq.obj# (+) >.   It […]