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
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
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...