Friday, October 4, 2024

Conditional DDL replication in Goldengate

Thanks for visiting this page.I will share my experience in Goldengate DDL replication for a specific condition.

Current state: Goldengate 19c is already setup between source(OLTP) and target (DW) oracle database with the same oracle 19c version.In source side there is an application schema called cisadm and we are replicating some specifc tables under that schema into target siide.Only DML replication is enabled and in target side there some mapping happens for every table to capture time of DML,DML type(Insert/Update/Delete) etc.DDL replication is not enabled.

Future state:Need to setup/configure existing goldengate as application is upgrading their schema.During application schema upgrade lots of DDL/DML will be executed which involves creation of new tables/indexes, modification of table structures, creation of packages/procedures and some DML related to application metadata.

Requirement: Application owner wants to replicate DDL changes caused by the schema upgrade but that should be limited to mapped tables and only interested for ALTER TABLE  statement.To satisfy this requirement we will use Goldengate DDL filtering option which will capture only DDL related to table structure change.

Observation: If we compare table structure definition between replicated tables of application then we can see column name,length and datatype are same but naming convention of primary key constraints are different.Please see below SQL script for sample tables highlighting the difference.

OLTP

DW

ALTER TABLE CISADM.F1_SYNC_REQ ADD (

  PRIMARY KEY

  (F1_SYNC_REQ_ID)

  USING INDEX

    TABLESPACE CISTS_01

    PCTFREE    10

    INITRANS   2

    MAXTRANS   255

    STORAGE    (

                INITIAL          64K

                NEXT             1M

                MINEXTENTS       1

                MAXEXTENTS       UNLIMITED

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               )

  ENABLE VALIDATE);

ALTER TABLE CCB_STAGE.F1_SYNC_REQ ADD (

  CONSTRAINT PK_F1_SYNC_REQ

  PRIMARY KEY

  (F1_SYNC_REQ_ID)

  USING INDEX CCB_STAGE.PK_F1_SYNC_REQ

  ENABLE VALIDATE);

ALTER TABLE CISADM.CI_ACCT ADD (

  PRIMARY KEY

  (ACCT_ID)

  USING INDEX CISADM.XM148P0

  ENABLE VALIDATE);

ALTER TABLE CCB_STAGE.CI_ACCT ADD (

  CONSTRAINT PK_CI_ACCT

  PRIMARY KEY

  (ACCT_ID, ETL_RCRD_LD_DT)

  USING INDEX CCB_STAGE.PK_CI_ACCT

  ENABLE VALIDATE);

 

So while enabling DDL filtering we can safely exclude DDL related to primary key constraint.We have seen during application schema upgrade primary key constrains are dropped and recreated.So those statements will also propagate and we dont want that to happen

So while enabling DDL filtering we can safely exclude DDL related to primary key constraint 

Approach: We will make sure there is no lag between source and target like below.Then we shall upgrade  application schema to target version and let extract process capture all the necessary changes including DDL.

Source side

GGSCI 1> dblogin useridalias etladmin

Successfully logged into database.

 

GGSCI > lag E_CCB1

 

Sending GETLAG request to EXTRACT E_CCB1 ...

Last record lag 2 seconds.

At EOF, no more records to process

 

 

GGSCI 3> lag E_CCB2

 

Sending GETLAG request to EXTRACT E_CCB2 ...

Last record lag 3 seconds.

At EOF, no more records to process

 

 


If there is no lag then save current state of extract using below command and stop pump process.

 

 

GGSCI8> info E_CCB1 detail

 

EXTRACT    E_CCB1    Last Started 2024-09-30 02:58   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:05 ago)

Process ID           27179

Log Read Checkpoint  Oracle Integrated Redo Logs

                     2024-09-30 05:44:50

                     SCN 9136.615720641 (39239436936897)

 

  Target Extract Trails:

 

  Trail Name                                       Seqno        RBA     Max MB Trail Type

 

  /ogg/cist3/extract_ccb1/oc                           0      30567       1000 EXTTRAIL

 

 

Integrated Extract outbound server first scn: 9136.615618721 (39239436834977)

 

Integrated Extract outbound server filtering start scn: 9136.615618721 (39239436834977)

 

Target side

 

GGSCI  1> dblogin useridalias etladmin

Successfully logged into database.

 

GGSCI 3> lag R_EIM1

 

Sending GETLAG request to REPLICAT R_EIM1 ...

Last record lag 607 seconds.

At EOF, no more records to process

 

 

GGSCI> lag R_EIM2

 

Sending GETLAG request to REPLICAT R_EIM2 ...

Last record lag 330,124 seconds.

At EOF, no more records to process

 

If there is no lag then stop replicate process

 

GGSCI>Stop R_EIM1

GGSCI >Stop R_EIM2

 

 

 Ø  Before  upgrading schema make following changes in source side parameter file E_CCB1.prm and E_CCB2.prm

 export OGG_HOME=<goldengate home path>

cd $OGG_HOME/dirprm

Add following highlighted line

useridalias etladmin

TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 2048,PARALLELISM 1)

exttrail /ogg/cist3/extract_ccb1/oc

DDL INCLUDE MAPPED,OBJTYPE 'TABLE',EXCLUDE INSTR 'PRIMARY'

 

Above highlighted statement make sure extract only capture mapped table in scope and exclude DDL statement with the string matching keyword ‘PRIMARY’

 

Ø  Start application schema upgrade

Ø  To check interim extract statistics use below command which will dump stats in report file

Send extract E_CCB1,report

Send extract E_CCB2,report

Ø  Once upgrade is verified successfully start pump process at source CC&B side Source

Start P_CCB1

Start P_CCB2

 

Ø  Before starting replicate process in target side remove sourcedef parameter otherwise it will throw below error

ERROR   OGG-00513  Oracle GoldenGate Delivery for Oracle, R_EIM1.prm:  Table with SOURCEDEF cannot have DDL operations (table CCB_STAGE.CI_RV_L). Either remove SOURCEDEF or filter out table from DDL operations.

As per oracle documentation sourcedef parameter is no longer required as extract find table definition from trail file

https://docs.oracle.com/en/middleware/goldengate/core/23/reference/sourcedefs.html

 

Ø  add below DDL exception parameter in R_EIM1.prm and R_EIM2.prm file under dirprm directory

DDLERROR 904 IGNORE

To handle below error

ERROR   OGG-00519  Oracle GoldenGate Delivery for Oracle, R_EIM1.prm:  Fatal error executing DDL replication: error [Error code [904], ORA-00904: "DESCR_TMPLT": invalid identifier

                              ], no error handler present.

 

The reason for throwing this error is Goldengate assumes current schema as sys which we can see in ggserr.log file like below

2024-09-26 00:50:25  INFO    OGG-01407  Setting current schema for DDL operation to SYS.

DDLERROR 1430 IGNORE

 Target

Start  R_EIM1

Start R_EIM2

info all

You can see sync status from info all command and for me it took an hour just to sync everything.


Happy learning...


 

When SQL plan baseline is not used

 Thank you very much for your interest. Problem Statement: Lets assume you are reported about an application query running slow in one envir...