Oracle Goldengate: Unidirectional Configuration Example

Source Database Target Database
Hostname wfivm03881 Wfivm03882
SID FIDELIO DIEGO
DB Version 12.2.0 12.2.0
GG version 19.1.0. 19.1.0.

GG Process Names

Source Target
Extract ext1
Data Pump dp1
Replicat rep1

Prepare Test Environment
Create Test Schema and Some Tables

In order to test the GoldenGate configuration I will create a replication from schema sender to schema receiver (the receiving schema can be on the same database or on another)

.a) Source database:

create user sender identified by sender default tablespace users temporary tablespace temp;
grant connect,resource,unlimited tablespace to sender;

b) Destination database:

create user receiver identified by receiver default tablespace users temporary tablespace temp;
grant connect,resource,unlimited tablespace to receiver;

We’re going to create the simplest replication without the GG data pump (we can add it later).
Our goal is to create dml and ddl replication from the sender schema on the source database to receiver schema on the destination.
Replication also works if you’re using only one database. This is replication between schemas.

Configure Manager Process (Source and Target DB)

The Goldengate Manager is the very first processes that needs to be setup in the replication configuration. This process must be running on the source and the target system and is required to be able to configure and start the other Goldengate processes. It also manages the disk space by purging the old trail files periodically. Only one manager process is required for every Goldengate installation.

Create and start manager on the source and target DB

ggsci
EDIT PARAMS MGR

The above command created the file /home/oracle/goldengate/dirprm/mgr.prm

Add the following parameters to the parameter file:

PORT 7809
DYNAMICPORTLIST 7810-7820

Machine generated alternative text:
GGSCI 
GGSCI 
PORT 
1) 
2) 
EDIT 
PRRRMS 
MGR 
mgr.prm - 
Notepad 
Format Vieuu Help 
7889 
DYNAMICPORTLIST 7818-7828

Save and close the parameter file

There are other parameters that you can set like:

PARAMETER DESCRIPTION
PORT The default manager port is 7809.
AUTORESTART ER Restarts the crashed processes
RETRIES Number of restarts attempts
PURGEOLDEXTRACTS Purges the old trail files to manage the disk space
LAGCRITICALMINUTES Warning logged in the error log when lag threshold exceeded
LAGINFOMINUTES Information logged in the error log when lag threshold exceeded
LAGREPORTMINUTES Time interval after which manager process checks the processes for lag

Example:

PORT 7809
DYNAMICPORTLIST 7810-7820
AUTOSTART ER *
PURGEOLDEXTRACTS /golden/dirdat/*,
USECHECKPOINTS,
MINKEEPFILES 4,
minkeepdays 2
laginfominutes 10
lagcriticalminutes 90
FREQUENCYMINUTES 15
— delay starting other process after rebooting servers by 30min
–bootdelayminutes 20
–autostart ER *
— auto start pump processes to startup if network failed
autorestart EXTRACT p*, retries 4, waitminutes 10
autorestart EXTRACT e*, retries 4, waitminutes 10

Another Example:

PORT 7810
AUTORESTART ER *, RETRIES 3, WAITMINUTES 3, RESETMINUTES 10
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS
LAGCRITICALMINUTES 5
LAGREPORTMINUTES 60
LAGINFOMINUTES 0

Stop and Start Manager

Machine generated alternative text:
GGSCI ("f iumØ3882) 4) start manager 
Manager started.

Configure Extract Process (Source DB)

The Goldenagte Extract process captures committed transactions from the Oracle Redo Logs or the Archived Logs. The Extract process writes the captured source data into a file known an EXTTRAIL file. The extract process uses the checkpoints to mark the read and write positions to track the location up to which the data has been extracted at any given time. This information is used to determine the starting point in case of a failure.

In this example the extract process will be called “ext1”. Also the “lt” is the starting 2 characters of the name of the Exttrail file to be created. Only a maximum of two characters can be specified.

add extract ext1, tranlog, begin now
EXTRACT added.

C:\Users\dpafumi\AppData\Local\Temp\msohtmlclip1\02\clip_image003.png

add exttrail E:\oracle\gg\dirdat\lt, extract ext1
EXTTRAIL added.

C:\Users\dpafumi\AppData\Local\Temp\msohtmlclip1\02\clip_image004.png

edit params ext1

The above command created the file E:\oracle\gg\dirprm\ext1.prm

Add the following lines to the new parameter file for our extract:

–extract group–
extract ext1
–connection to database–
userid ggate, password ggate
–hostname and port for trail–
rmthost wfivm03882, mgrport 7809
–path and name for trail–
rmttrail E:\oracle\gg\dirdat\lt
–DDL support
ddl include mapped objname sender.*
–DML
table sender.*;

Machine generated alternative text:
GGSCI 
("f iumØ3881) 8) edit params extI 
Format Vieuu Help 
- -extract group-- 
extract ext 1 
-connection to database-- 
userid ggate, password ggate 
-hostname and port for trail-- 
rmthost lnJfi'„rn83882, mgrport 7889 
-path and name for trail-- 
rmttrail E: Norac1eXggXdirdatX1t 
-DDL support 
ddl include mapped objname sender. * 
table sender. *

Save and close the parameter file

We can check our processes again:

info all

Machine generated alternative text:
GSCI (wfiumø3881) 
9) 
Ifo all 
Group 
EXTI 
Lag at Chkpt 
øø:øø:øø 
Time Since 
øø:ø2 
Chkpt 
Fogram 
RNRGER 
XTRRCT 
Status 
RUNNING 
s TOPPED

For the DDL Parameter section:

If multiple DDL filtering options are specified then all criteria must be true for the DDL to be included.

The syntax for the DDL parameter is as follows:

DDL [
{ INCLUDE | EXCLUDE }
[, MAPPED | UNMAPPED | OTHER | ALL]
[, OPTYPE ]
[, OBJTYPE ”]
[, OBJNAME ]
[, INSTR ”]
[, INSTRCOMMENTS ”]
[, STAYMETADATA]
[, EVENTACTIONS {}
]

If an EXCLUDE clause is specified, then a corresponding INCLUDE clause must exist.
For example the following is valid as it contains both clauses:
DDL INCLUDE ALL, EXCLUDE OBJNAME “US03.*”

The following is valid as it includes an INCLUDE clause:
DDL INCLUDE OBJNAME “US03.*”

However the following is invalid as it only contains an EXCLUDE clause:
DDL EXCLUDE OBJNAME “US03.*”

EXCLUDE clauses have priority over INCLUDE clauses where both reference the same objects.

The parameters used in the extract prm file are briefly explained below.

PARAMETER DESCRIPTION
EXTRACT Extract process name
USERID Database username for GoldenGate
EXTTRAIL Name of the extract file to be created
TRANLOGOPTIONS Specific directives while mining transactions
DBLOGREADER New ASM API to extract data
BUFSIZE Max bytes of memory allocated to read redo data – Increases Capture Speed
DBLOGREADERBUFSIZE Increases Maximum read size for ASM
TABLE Specify the SCHEMA and TABLE to extract data for

Another Example:

EXTRACT ESRC01
USERID OGG_USER PASSWORD OGG_USER
EXTTRAIL ./dirdat/st
TRANLOGOPTIONS EXCLUDEUSER OGG_USER
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS BUFSIZE 4096000
TRANLOGOPTIONS DBLOGREADERBUFSIZE 4096000
TABLE APP_TEST.*;

Another Example:

EXTRACT EXT1
SETENV (ORACLE_SID=”SRCDB”)
SETENV (ORACLE_HOME = “/oracle/app/oracle/product/12.1.0/dbhome_1”)
SETENV (NLS_LANG = “AMERICAN_AMERICA.AL32UTF8”)
userid ggate, PASSWORD ggate123
FETCHOPTIONS  FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION
DBOPTIONS ALLOWUNUSEDCOLUMN
DISCARDFILE /ggate/app/oracle/product/12.2.1.0.3/TST/EXT1.dsc, APPEND, MEGABYTES 100
EXTTRAIL /ggate/app/oracle/product/12.2.1.0.3/TST/E5
DDL INCLUDE MAPPED OBJNAME APPLIANCE.CLASSTAB1
TABLE APPLIANCE.CLASSTAB1;

When some activity is going on, you can check the status of extract with:

GGSCI () 18> stats EXT1

Some other commands:

Status of Extract

In “ggsci” type the below command to check the status of the Golden gate extract process.

ggsci> info ESRC01
ggsci> view report ESRC01
ggsci> stats extract ESRC01

This command shows the display the status, row counts and details of data processed by the extract process.

Delete Extract

In case the Extract process needs to be deleted, you can run the “DELETE EXTRACT ..” to remove the extract process. However before you can delete the processes make sure that you connect to the database to remove the process entry from the database.

ggsci>DBLOGIN USERID OGG_USER PASSWORD OGG_USER
ggsci> DELETE EXTRACT ESRC01

Configure Replication Parameters (Target DB)

The Goldengate Replicat process also known as the apply process, is a mandatory process in the Goldengate setup. This data delivery process is the last component to be setup on the the target database. It reads the data from a file known an EXTTRAIL file which contains data changes made on the source. In addition to applying the data on the target, the Goldengate replicat also posts its exact location of the trail file while processing records. This is used for recovery from crashes, allowing it to start from the exact point where it left off, without any data loss.

The check point table should be created in the target database.

On the target server login as the ggate user and add the check point table:

ggsci
DBLOGIN USERID ggate , PASSWORD ggate

Successfully logged into database.

ADD CHECKPOINTTABLE ggate.checkpoint

Successfully created checkpoint table gguser.checkpointtable.

Machine generated alternative text:
GGSCI (wfiumø3882) 1) DBLOGIN USERID ggate . 
PASSWORD ggate 
2ø2ø-ø9-ø1 16:11 WARNING OGG-ø2551 ORRCLE_HOME is not set to Oracle software directory. 
Successfully logged into database. 
GGSCI (wfiumø3882 as ggateediego) 2) ADD CHECKPOINTTRBLE -checkpoint 
Successfully created checkpoint table ggate . checkpoint.

The name of the check point table must be added to the GLOBALS file on the target server.

On the target server edit the GLOBALS file

EDIT PARAMS ./GLOBALS

And put following lines to the global parameter file:

GGSCHEMA ggate

CHECKPOINTTABLE ggate.checkpoint

Machine generated alternative text:
GGSCI 
("f iumØ3882) 2) edit params 
Format Vieuu Help 
. 'GLOBAL 
GLOBAL 
PGSCHEPIA ggate 
CHECKPOINT TABLE ggate . checkpoint

Save and close the GLOBALS parameter file.

Create replicat group:

add replicat rep1, exttrail E:\oracle\gg\dirdat\lt,checkpointtable ggate.checkpoint

REPLICAT added.

NOTE:

If you try to create the Replicat group, the ADD REPLICAT command might produces an error:

ERROR: No checkpoint table specified for ADD REPLICAT.

The solution is simply to exit that GGSCI session and then start IT AGAIN issuing ADD REPLICAT.

The ADD REPLICATE command fails if issued from the session where the GLOBALS file using the GGSCI command “EDIT PARAMS ./GLOBALS” was created.

This is because the name of the checkpoint table is read from GLOBALS by GGSCI. The session in which the GLOBALS was created cannot read the file.

Reference – MOS 965256.1

On the target server create the parameter file for replication process rep1:

EDIT PARAMS rep1

The above command created the file E:\oracle\gg\dirprm\rep1.prm

Add the following parameters to the new file:

–Replicat group —

replicat rep1

–source and target definitions

ASSUMETARGETDEFS

–target database login —

userid ggate, password ggate

–file for dicarded transaction —

discardfile E:\oracle\gg\discard\rep1_discard.txt, append, megabytes 10

–ddl support

DDL

–Specify table mapping —

map sender.*, target receiver.*;

Machine generated alternative text:
GGSCI 
(wfiumø3882 as ggateediego) 4) EDIT PRRRMS repl 
repl.prm - Notepad 
Format Vieuu Help 
append, 
megabytes 
-Replicat group - 
replicat repl 
-source and target definitions 
ASSUME-TARGETDEFS 
-target database login - 
userid ggate, password ggate 
-file for dicarded transaction 
discardfile E: Norac1eXggXdiscardXrep1 discard. txt, 
-ddl support 
-Specify table mapping - 
map sender. 
* target receiver. * ; 
18

On the Discard Option, you can include the PURGE keyword. If PURGE is not specified them the replication process will fail the second time it is started. Example:

discardfile E:\oracle\gg\discard\rep1_discard.txt, PURGE

Alternatively use the APPEND keyword to append output to the existing file.

Below is the example of the GoldenGate Replicat parameter file. This file in usually placed in the $GG_HOME/dirprm directory. The GG_HOME is the home directory where GoldenGate binaries are installed and dirprm was created in the pre-requisites phase by issuing “CREATE SUBDIRS”.

REPLICAT RWHS01

ASSUMETARGETDEFS

USERID OGG_USER PASSWORD hsdgeh#die4fsG

DISCARDFILE ./dirrpt/rprt.dsc, PURGE

DDL INCLUDE ALL

MAP APPSRC01.*, TARGET APPTAR01.*

The parameters above used in the Oracle Replicat parameter file, have been explained below.

PARAMETER DESCRIPTION
REPLICAT Name of the Replicat Process
ASSUMETARGETDEFS Used to identify that the column structure of the SOURCE and TARGET tables in the MAP statement are identical
USERID Login details for the GoldenGate Database user
DISCARDFILE This file contains data records that caused an error while they are written to the TARGET databas
DDL INCLUDE MAPPED This parameter is used to ensure that the scope of the DDL transaction is of mapped scope only
MAP & TARGET Used to map SOURCE and TARGET schema. In this example the source schema name is APPSRC01 and the target schema is APPTAR01

Another Example:

REPLICAT rep1
USERID gg01, PASSWORD gg01
ASSUMETARGETDEFS
DISCARDFILE /home/oracle/goldengate/discards, PURGE
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
MAP US03.*, TARGET US03.*;

The DDLERROR parameter prevents the replicat process from abending if there is a mismatch between the source and target environments. This should not really happen under normal circumstances; in a test environment this parameter may be required to synchronize the objects between the databases.

Although the above configurations only replicate DML for the US03 schema, the DDL INCLUDE ALL command replicates DDL for all schemas. So if a new table is created under the US01 schema, this should be replicated to the same schema in the target database.

Another Example:

replicat r_shl01t

— add replicat r_shl01t, exttrail ./dirdat/****/th

discardfile ./dirrpt/discard/r_shl01t.dsc, append megabytes 50

SETENV (ORACLE_SID=’****’)

SETENV (NLS_LANG = “AMERICAN_AMERICA.AR8MSWIN1256”)

–SETENV (ORACLE_HOME=C:\oracle\rdbms11g\product\11.2.0\dbhome_1)

userid ggadmin, password ggadmin123

AssumeTargetDefs

— for triggers starting from 11.2.0.2 oracle automatically disable trigger fire

dboptions suppresstriggers

— for cascading const

dboptions deferrefconst

DDL include all

–DDLSUBST ‘SYS_C0040303’ WITH ‘SYS_C0040298’

–ddlerror 1031 ignore

grouptransops 1000

include ./dirprm/include_reporting.inc

–include ./dirprm/HB_Rep.inc

APPLYNOOPUPDATES

map ggadmin.ggsync, target ggadmin.ggsync;

Map <Schema_name>.*, Target <Schema_name>.*;

Map <Schema_name>.*, Target <Schema_name>.*;

Map <Schema_name>.*, Target <Schema_name>.*;

Map <Schema_name>.*, Target <Schema_name>.*;

Map <Schema_name>.*, Target <Schema_name>.*;

Map <Schema_name>.*, Target <Schema_name>.*;

Map test_user.*, Target test_user.*;

Start Extract and Check its Status (Source)

start extract ext1

Info all

Machine generated alternative text:
GSCI (wfiumø3881) ID 
tart extract 
ending START request to MANAGER . 
XTRRCT EXTI starting 
GSCI (wfiumø3881) 11) info all 
ext: 
Lag at Chkpt 
øø:øø:øø 
Time Since 
Chkpt 
Fogram 
RNRGER 
XTRRCT 
Status 
RUNNING 
RUNNING 
Group 
EXTI

Verify that the Extract process has started successfully:

INFO EXTRACT ext1

Machine generated alternative text:
GSCI (wfiumø3881) 
XTRRCT 
EXTI 
heckpo int Lag 
rocess ID 
og Read Checkpo int 
3) 
EXTRACT ext: 
Last Started 2ø2ø-ø9- 
øø:øø:øø (updated øø: 
256ø 
Oracle Redo Logs 
2ø2ø-ø9-ø1 
øl 
øø: 
16: 
ø9 
21 
Status 
ago ) 
RUNNING 
Segno 
23. RBR 3ø6ø4288 
SCN ø.325ø652 (325ø652)

INFO EXTRACT ext1 detail

Machine generated alternative text:
GSCI (wfiumø3881) 
XTRRCT 
EXTI 
heckpo int Lag 
rocess ID 
og Read Checkpo int 
2) 
EXTRACT exti deta: 
Last Started 2ø2ø-ø9-ø1 
øø:øø:øø (updated øø:øø: 
256ø 
Oracle Redo Logs 
16: 
21 
Status 
ago ) 
RUNNING 
2ø2ø-ø9-ø1 16: 
SCN ø.325ø2ø3 
Target Extract Trails: 
Trail Name 
E: Xorac le XggXdirdatXIt 
Extract Source 
E: DELI ONREDOø2 . 
Sø:26 Segno 
(325ø2ø3) 
Begin 
23. RBR 3ø453?6ø 
Max MB 
søø 
Trail T ype 
RMTTRRIL 
Segno 
RBR 
4986 
LOG 
55 
End 
2ø2ø- 
2ø2ø- 
2ø2ø- 
2ø2ø- 
ø9- 
ø9- 
ø9- 
ø9- 
16: 
15: 
15: 
15: 
55 
55 
55 
2ø2ø-ø9-ø1 15: 
Initialized 
Initialized 
Initialized 
øl 
øl 
øl 
øl 
Not Available 
Not Available 
Not Available 
urrent directory 
eport file 
arameter file 
heckpo int file 
rocess file 
reor log 
E: 
E: 
E: 
E: 
E: 
Xorac le Xgg 
Xorac le NEXT I . 
Xorac le XggXdirprmXEXTI . 
Xorac le . 
Xorac leXggXggserr . log 
rpt 
pem 
c pe

Start Replicat and Check its status (Destination)

start replicat rep1

Sending START request to MANAGER …
REPLICAT REP1 starting

Machine generated alternative text:
GGSCI ("f iumØ3882 as ggateediego) 
Sending START request to MANAGER . 
REPLICAT REPI starting 
GGSCI ("f iumØ3882 as ggateediego) 
start 
re 
licat 
re 
Since 
Chkpt 
6) info all 
Lag at Chkpt 
øø:øø:øø 
Program 
MRNA GER 
REPLICAT 
Status 
RUNNING 
s TOPPED 
Group 
T ime

Verify that the Replicat process has started successfully:

START REPLICAT rep1

Machine generated alternative text:
GGSCI (wfiumø3882) 
REPLICAT 
Checkpo int Lag 
Process ID 
Log Read Checkpo int 
15) 
INFO REPLICAT re 1 
Last Started 2ø2ø-ø9-ø1 16:36 
Status 
øø:øø:øø (updated øø:øø:øø ago) 
4324 
File E: Norac1exggXdirdatX1tØØØØØØØØØ 
2ø2ø-ø9-ø1 RBR 4986 
RUNNING

INFO REPLICAT rep1 detail

Machine generated alternative text:
GGSCI (wfiumø3882) 
REPLICAT 
Checkpo int Lag 
Process ID 
16) 
INFO REPLICAT re 1 
Last Started 2ø2ø-ø9- 
øø:øø:øø (updated øø: 
4324 
detail 
øl 16:36 
Status 
øø:ø6 ago) 
RUNNING 
43 
Log Read Checkpo int File E : XoracIeXggXdirdatXItØØØØØØØØØ 
2ø2ø-ø9-ø1 RBR 4986 
Current Log BSN value: (requires database login) 
Last Committed Transaction CSN value: (requires database login) 
Extract Source 
E : Xo rac le XggXd irdat It 
E : Xo rac le XggXd irdat It 
E : Xo rac le XggXd irdat It 
Begin 
Initialized 
Initialized 
Initialized 
End 
2ø2ø-ø9-ø1 16: 
First Record 
First Record 
rpt 
pem 
Current directory 
Report f ile 
Parameter file 
Checkpo int f ile 
Checkpo int table 
Process file 
Error log 
E: Xorac leXgg 
E: Xorac le XREPI . 
E: Xorac le XggXdirprmXREPI . 
E: Xorac le XggXdirchkXREPI . 
ggate . checkpo int 
E: Xorac leXggXggserr . log

Our replication has been successfully created.

Checking

Now we can check our replication. We will create some tables in the sender schema on the source, insert some rows, and check how it will replicate to destination side.

Source database:

Sqlplus sender/sender

create table sender.test_tab_1 (id number,rnd_str varchar2(12));

insert into sender.test_tab_1 values (1,’test_1′);

ALTER TABLE sender.test_tab_1 ADD CONSTRAINT tab_1_i1 PRIMARY KEY (id);

commit;

Destination database:

Sqlplus receiver/receiver

SQL> select * from receiver.test_tab_1;

Uninstall GoldenGate

If you need to uninstall GoldenGate you can do it from inside the GG_HOME going to the directory deinstall and running the script deinstall.sh or deinstall.bat

Help and Informational Commands

GGSCI> HELP
GGSCI> HELP ALL
GGSCI> HELP ADD EXTRACT
GGSCI> HELP ADD EXITRAIL

GGSCI> set editor emacs
GGSCI> set editor vi

GGSCI> HISTORY

— View the GoldenGate application log (view GGS Event )
— Oracle GoldenGate error log (ggserr.log file)
GGSCI> VIEW GGSEVT

— View a brief informational summary of all processes
GGSCI> — INFO ALL [TASKS | ALLPROCESSES]
GGSCI> INFO ALL
GGSCI> info all tasks
GGSCI> info all allprocesses

GGSCI> status extract initext
GGSCI> INFO EXTRACT INITEXT
GGSCI> INFO EXTRACT INITEXT,DETAIL
GGSCI> info extract extora, showch,detail,tasks,allprocesses
GGSCI> info extract initext,showch,detail,tasks,allprocesses

GGSCI> VIEW REPORT INITEXT

GGSCI> send extract extora, report
GGSCI> view report extora

GGSCI> stats <group>
GGSCI> stats <group>, TABLE *, TOTALSONLY *

GGSCI> send extract extora, rollover

Configure Data Pump Parameters (Source DB)

The Oracle GoldenGate Pump process is responsible for reading the data from the local EXTTRAIL trail file (the file with data captured by extract process) and writing the data to the target system. The Pump process is an optional component of the replication mechanism and its main benefit is its usefulness in ensuring robustness in the replication configuration when there is a system or network failure. The following are the steps required to configure the Oracle GoldenGate Pump Process.

In this example the Data Pump process will be called dp1

On the source server create the parameter file for Data Pump process dp1:

ggsci
EDIT PARAMS dp1

The above command created the file D:\oracle\gg_home\product\12.2.0\oggcore_1\dirprm\dp1.prm

Add the following parameters to the new file:

EXTRACT dp1

USERID gguser, PASSWORD gguser

RMTHOST wfivm03882, MGRPORT 7809

RMTTRAIL D:\oracle\gg_home\product\12.2.0\oggcore_1\dirdat\rt

TABLE us01.*;

Save and close the parameter file

Machine generated alternative text:
GGSCI 
(wfiumø3881) 1) EDIT PRRRMS dpi 
Format Vieuu Help 
EXTRACT dpi 
USERID gguser, PASSWRD gguser 
RPffHOST 
RPORT 7889 
RPITTRAIL D: Norac1eXgg_hcrneXproductX12.2. noggcore 1 Ndirdatxrt 
TABLE us81.*.

Add the Data Pump Process

On the source server add the Data Pump process (dp1)

ADD EXTRACT dp1 EXTTRAILSOURCE D:\oracle\gg_home\product\12.2.0\oggcore_1\dirdat\ex

EXTRACT added.

Add the Data Pump Trail

On the source server add the Data Pump trail (/home/oracle/gg/dirdat/rt). This trail is created on the target server. However, the name is required in order to set up the Data Pump process on the source server.

ADD RMTTRAIL D:\oracle\gg_home\product\12.2.0\oggcore_1\dirdat\rt, EXTRACT dp1

RMTTRAIL added.

Start Data Pump Process (Source DB)

On the source server, start the Data Pump (dp1):

START EXTRACT dp1
Sending START request to MANAGER …
EXTRACT DP1 starting

Machine generated alternative text:
GGSCI (wfiumø3881) 2) START EXTRACT dpi 
Sending START request to MANAGER . 
EXTRACT DPI starting

INFO EXTRACT dp1

Machine generated alternative text:
GGSCI (wfiumø3881) 3) INFO EXTRACT dpi 
EXTRACT 
Checkpo int Lag 
Log Read Checkpo int 
Last Started 2ø2ø-ø8-31 13:41 
Status RBENDED 
øø:øø:øø (updated ago) 
File D: XoracIeXgg_homeXproductX12 .2 .ØXoggcore_IXdirdatXexØØØØØØØØØ 
First Record RBR ø

INFO EXTRACT dp1 detail

Machine generated alternative text:
GGSCI (wfiumø3881) 4) INFO EXTRACT dpi detail 
EXTRACT 
Checkpo int Lag 
Last Started 2ø2ø-ø8-31 13:41 
Status RBENDED 
øø:øø:øø (updated ago) 
Log Read Checkpo int File D : XoracIeXgg_homeXproductX12 .2 .ØXoggcore_IXdirdatXexØØØØØØØØØ 
First Record RBR ø 
Max MB Trail Type 
Søø RMTTRRIL 
Target Extract Trails: 
Trail Name 
D : Xorac le Xgg_home XproductX12 . 2 
Extract Source 
D : Xorac le Xgg_home XproductX12 . 2 
D : Xorac le Xgg_home XproductX12 . 2 
Segno 
. øXoggcore _I 
Begin 
End 
RBR 
First 
First 
Record 
Record 
. øXoggcore _I Nd irdat Xøøøøøøøøø 
. øXoggcore _I Nd irdat Xøøøøøøøøø 
Initialized 
Initialized 
Current directory 
Report f ile 
Parameter file 
Checkpo int f ile 
Process file 
Error log 
D: 
D: 
D: 
D: 
D: 
. øxoggcore_l 
. øxoggcore_l XDPI . rpt 
. øxoggcore_l XdirprmXDPI . pem 
. øxoggcore_l XdirchkXDPI . c pe 
. øXoggcore_1 Xggserr . log 
Xorac le Xgg_home XproductX12 . 2 
Xorac le Xgg_home XproductX12 . 2 
Xorac le Xgg_home XproductX12 . 2 
Xorac le Xgg_home XproductX12 . 2 
Xorac le Xgg_home XproductX12 . 2

The PSRC01.prm parameter file is used to create the PUMP process and is stored in the $GG_HOME/dirprm directory.The GG_HOME is the home directory where the GoldenGate binaries are installed and dirprm was created in the pre-requisites phase by issuing “CREATE SUBDIRS”.

cat dirprm/PSRC01.prm

EXTRACT PSRC01

RMTHOST ggdb02, MGRPORT 7800, TIMEOUT 30

RMTTRAIL /u01/app/ggs/dirdat/ps

PASSTHRU

TABLE app_test.*;

Below is the description of the parameters used in the parameter file.

PARAMETER DESCRIPTION
RMTHOST Target host name
MGRPORT Port number of Manager running on target side
COMPRESS Used to compress the RMTTRAIL file
RMTTRAIL Location and prefix of file to be created on the target system
PASSTHRU No connection to the database is used
TABLE Data is extracted for only the schema.table name specified

Configure Supplemental Logging for Replicated Tables (Source DB)

On the source server configure supplemental logging for all tables that will be replicated. In this example there is only one table (T1)

Supplemental logging can be configured by any user that has privileges to modify the underlying database table.

ggsci
DBLOGIN USERID us01, PASSWORD us01

Successfully logged into database.

ADD TRANDATA US01.t1
Logging of supplemental redo data enabled for table US01.T1.

Machine generated alternative text:
GGSCI (wfiumø3881) 1) DBLOGIN USERID usøl. PASSWORD usøl 
2ø2ø-ø8-31 11 WARNING OGG-ø2551 ORRCLE_HOME is not 
Successfully logged into database. 
GGSCI (wfiumø3881 as usøl@fidelio) 2) ADD TRRNDRTR usøl . ti 
set 
Orac le 
software 
directory. 
Logging of supplemental redo data enabled for table USM .11. 
TRRNDRTR for scheduling columns has been added on table ' USM .11' . 
TRRNDRTR for instantiation CSN has been added on table ' USM .11' . 
GGSCI (wfiumø3881 as usøl@fidelio) 3)

Adding Objects: Example adding all tables of a schema (Source)

DBLOGIN USERID ggate, PASSWORD ggate

ADD TRANDATA COMP_USR.*

Prepare extract file [ SOURCE ]

edit params EX_C1

EXTRACT EX_C1

SETENV (ORACLE_SID=”SRCDB”)

SETENV (ORACLE_HOME=”/oracle/app/oracle/product/12.1.0.2/dbhome_1″)

userid ggate_user, password ggate_user

FETCHOPTIONS  FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION

DBOPTIONS ALLOWUNUSEDCOLUMN

CACHEMGR CACHESIZE 500M

DISCARDFILE /ggate_user/DISCARD/CRM_B2B1.dsc, APPEND, MEGABYTES 100

EXTTRAIL /ggate_user/TRAILFILE/S2

WARNLONGTRANS 6H, CHECKINTERVAL 1H

TABLE COMP_USR.*;

Prepare extract pump parameter file [ SOURCE ]

edit params EX_C1P

EXTRACT EX_C1P

userid ggate_user, password ggate_user

SETENV (ORACLE_HOME=”/oracle/app/oracle/product/12.1.0.2/dbhome_1″)

RMTHOST 172.41.32.42, MGRPORT 7809

RMTTRAIL /ggate/install/datacapture/T2

DBOPTIONS ALLOWUNUSEDCOLUMN

TABLE COMP_USR.*;

Add extract process [SOURCE]

add extract EX_C1,tranlog,begin now

Add the extract trail [SOURCE]

add exttrail /ggate_user/TRAILFILE/S2,extract EX_C1

Add the extract pump process [SOURCE]

add extract EX_C1P, exttrailsource /ggate_user/TRAILFILE/S2

Add remote trail [SOURCE]

add rmttrail /ggate/install/datacapture/T2,extract EX_C1P

Prepare replicat file [ TARGET ]

Here we have to add HANDLECOLLISION parameter to resolve conflicts for the transactions during initial loading. We will remove this parameter once the lag is cleared after initial loading.

As per Oracle DOC:

Use the HANDLECOLLISIONS and NOHANDLECOLLISIONS parameters to control whether or not Replicat tries to resolve duplicate-record and missing-record errors when applying SQL on the target. These errors, called collisions, occur during an initial load, when data from source tables is being loaded to target tables while Oracle GoldenGate is replicating transactional changes that are being made to those tables. When Oracle GoldenGate applies the replicated changes after the load is finished, HANDLECOLLISIONS provides Replicat with error-handling logic for these collisions.

edit params REP_S1

REPLICAT REP_S1

SETENV (ORACLE_HOME = “/u01/app/oracle/product/12.1.0/db_1”)

SETENV (ORACLE_SID = “TRGDB”)

USERID ggate_user, PASSWORD ggate_user

DBOPTIONS SUPPRESSTRIGGERS,DEFERREFCONST

ASSUMETARGETDEFS

HANDLECOLLISIONS

DISCARDFILE /ggate/install/datacapture/discardcrm.dsc,append

MAP COMP_USR.*,TARGET COMP_USR.*;

add the replicat process [ TARGET ]

add replicat REP_S1, exttrail /ggate/install/datacapture/T2

Start the extract and pump process [ SOURCE ]

GGSCI > START  EX_C1

GGSCI > START EX_C1P

GGSCI > info all

Initial loading:

Now export this schema using expdp in the source database and import in the target database.

— SOURCE DB

expdp dumpfile=initload.dmp logfile=initload.log directory=DIRT schemas=COMP_USR

Username: / as sysdba

— TRANSFER THE DUMP TO TARGET SERVER:

scp oracle@**********:/archive/dump/initload.dmp .

— -IMPORT IN TARGET DATABASE:

impdp dumpfile=initload.dmp logfile=imp_initialload.log directory=FULL_REFRESH

Username: / as sysdba

Start the replicat: [ SOURCE ]

start REP_S1

Sending START request to MANAGER …

REPLICAT REP_S1 starting

Use LAG command to monitor the lag at replicat.

GGSCI >  lag replicat REP_S1

2017-07-24 15:33:12  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to REPLICAT REPR1 …

Last record lag 6 seconds.

GGSCI >  !

2017-07-24 15:33:12  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to REPLICAT REP_S1 …

Last record lag 6 seconds.

At EOF, no more records to process. ——– >>>>>>>>> This indicates lag is zero.

Remove handlecollison parameter and restart replicat[ TARGET ]

REPLICAT REP_S1

SETENV (ORACLE_HOME = “/u01/app/oracle/product/12.1.0/db_1”)

SETENV (ORACLE_SID = “TRGDB”)

USERID ggate_user, PASSWORD ggate_user

DBOPTIONS SUPPRESSTRIGGERS,DEFERREFCONST

ASSUMETARGETDEFS

–HANDLECOLLISIONS

DISCARDFILE /ggate/install/datacapture/discardcrm.dsc,append

MAP COMP_USR.*,TARGET COMP_USR.*;

ggsci>stop REP_S1

ggsci>start REP_S1

COLS & COLSEXCEPT FILTER (Filter Columns)

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 parameters are valid for extract and extract pump only. If we use COLS & COLSEXCEPT in extract, then keyword PASSTHRU need to be added in the pump parameter file. But if we use these in the pump, then no need of using PASSTRHU.

COLS – This is used to include/select set of columns of a table from replication.

COLSEXCEPT – This is used to exclude a set of columns of a table from replication.

Example

Table EMPTAG in SOURCE Table EMPTAG in TARGET
Emp_ID number Emp_ID number
EMP_Name varchar2(128) EMP_Name varchar2(128)
Depto number Depto number
SalCategory number
Service_type varchar2(23)

Here we will extract only EMP_ID, EMP_NAME, DEPTNO and exclude SALCATGOERY, SERVICE_TYPE.

COLS and COLEXCPET parameter can be defined either in EXTRACT or EXTRACT PUMP PARAMETER

COLSEXCEPT:

If we are defining COLSEXCEPT parameter in extract parameter, then extract prm file will look as below.

Extract Parameter File:

EXTRACT EXT1

userid ggate_user, password ggate_user

FETCHOPTIONS  FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION

DBOPTIONS ALLOWUNUSEDCOLUMN

DISCARDFILE /u01/ggate/datacapture/EXT4.dsc, APPEND, MEGABYTES 100

EXTTRAIL /u01/ggate/datacapture/T0

WARNLONGTRANS 6H, CHECKINTERVAL 1H

TABLE APPLIANCE.EMPTAG,COLSEXCEPT (SALCATGOERY,SERVICE_TYPE);

Extract pump parameter file:

As we used COLSEXCEPT filter in the extract, We need to use PASSTHRU parameter in pump parameter file.

EXTRACT EXT1P

userid ggatebss, password ggatebss#123

PASSTHRU

RMTHOST 172.18.83.472, PORT 7893

RMTTRAIL /ggatebss/oradata/TI2/y0

TABLE APPLIANCE.EMPTAG;

But, If we are defining colsexcept parameter in pump parameter, then pump prm file will look as below.i.e no changes to extract file.

Extract parameter file:

EXTRACT EXT1

userid ggate_user, password ggate_user

FETCHOPTIONS  FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION

DBOPTIONS ALLOWUNUSEDCOLUMN

DISCARDFILE /u01/ggate/datacapture/EXT4.dsc, APPEND, MEGABYTES 100

EXTTRAIL /u01/ggate/datacapture/T0

WARNLONGTRANS 6H, CHECKINTERVAL 1H

Extract pump parameter file:

As we used COLSEXCEPT filter in the pump itself, No need to add PASSTHRU parameter.

EXTRACT EXT1P

userid ggatebss, password ggatebss#123

RMTHOST 172.18.83.472, PORT 7893

RMTTRAIL /ggatebss/oradata/TI2/y0

TABLE APPLIANCE.EMPTAG,COLSEXCEPT (SALCATGOERY,SERVICE_TYPE);

Machine generated alternative text:
EXTRACT FILE 
EXTRACT EXTI 
userid ggate user, password 'gate user 
FETCHOPTIONS FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION 
DBOPT ONS ALLOWUNUSEDCOLUMN 
DISCARDFILE APPEND, MEGABYTES 100 
EXTTRA IL 
WARN CNGTRANS 6H, CHECKINTERVAL IH 
TABLE APPLIANCE.EMPTAG,COLSEXCEPT (SALCATGOERY„SERVICE TYPE); 
PUMP FILE: 
EXTRACT EXTI P 
userid ggateb55, password ggatebss#123 
PASSTHRU 
RMTHOST 1721883.472, PORT 7893 
R MTT IL 
TABLE APPLIANCE.EMPTAG; 
REPLICAT FILE: 
REPLICAT REPRI 
USERID "ate, PASSWORD 'gate123 
DBOPT ONS supp DEFERREFCONST 
ASSUMETARGETDEFS 
DISCARDFILE 1000 
MAP APPLIANCE.EMPTAG,TARGET APPLIANCE_EMPTAG; 
EXTRACT FILE 
EXTRACT EXTI 
useridggate user, password 'gate user 
FETCHOPTIONS FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION 
DBOPTICNSA LOWUNUSEDCOLUMN 
DISCARDFILE /u01/ggate/datacapture/EXT4_dsc, APPEND, MEGABYTES 100 
EXTTRAIL /uOI/ggate/datacapture/TO 
WARNLONGTRANS EH, CHECKINTERVAL IH 
TABLE APPLIANCE.EMPTAG; 
PUMP FILE: 
EXTRACT EXTIP 
userid ggatebss, password ggatebss#123 
RMTHOST 1721883472, PORT 7893 
R MTTPAL / 
TABLE APPLIANCE.EMPTAG, COLSEXCEPT (SALCATGOERY„SERVICE TYPE); 
REPLICAT FILE: 
REPLICAT REPRI 
USER'D *ate, PASSWORD 'gate123 
D BOPTICNS supp RESSTRIGGERS,DEFERREFCONST 
ASSUMETARGETDEFS 
DISCARDFILE /datacapturefiRG/reprt 1000 
MAP APPLIANCE.EMPTAG,TARGET APPLIANCE.EMPTAG;

COLS:

Instead of COLSEXCEPT, if you are using COLS , then the only change is

COLSEXCEPT (SALCATGOERY,SERVICE_TYPE); – >> COLS(EMP_ID,EMP_NAME,DEPTNO);

Machine generated alternative text:
EXTRACT FILE 
EXTRACT EXTI 
userid ggate user, password 'gate user 
FETCHOPTIONS F TCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION 
DBOPT ONS ALLCWUNUSEDCOLUMN 
DISCARDFILE APPEND, MEGABYTES 100 
EXTTRA IL 
WARN CNGTRANS 6H, CHECKINTERVAL IH 
TABLE APP IANCE.EMPTAG,COLSEXCEPT (SALCATGOERY„SERVICE TYPE); 
PUMP FILE: 
EXTRACT EXTI P 
userid ggateb55, password 'gatebss#123 
PASSTHRU 
RMTHOST 1721883.472, PORT78g3 
R M TTRA IL 
TABLE APPLIANCE.EMPTAG 
REPLICAT FILE: 
REPLICAT REPRI 
USERID "ate, PASSWORD 'gate123 
DBOPTIONS supp RESSTRIGGERS, DEFERREFCONST 
ASSUMETARGETDEFS 
DISCARDFILE 1000 
MAP 
EXTRACT FILE 
EXTRACT EXTI 
userid ggate user, password 'gate user 
FETCHOPTIONS F TCHPKUPDATECOLS, USESNAPSHOT, 
USEL_ATESTVERSION 
DBOPT ONS ALLOWUNUSEDCOLUMN 
DISCARDFILE /u01/ggate/datacapture/EXT4.dsc, APPEND, 
MEGABYTES 100 
EXTTRAIL /uOI/ggate/datacapture/TO 
WARN ONGTRANS 6B, CHECKINTERVAL IH 
TABLE APP IANCE.EMPTAG, COLS(EMP ID,EMP NAME,DEPTNO) 
PUMP FILE: 
EXTRACT EXTI P 
userid ggatebss, password 'gatebss#123 
PASSTHRU 
RMTHOST 1721883.472, PORT 7893 
R M TTVIL /gga 2/vO 
TABLE APPLIANCE.EMPTAG; 
REPLICAT FILE: 
REPLICAT REPRI 
USERID "ate, PASSWORD 'gate123 
DBOPTIONS supp RESSTRIGGERS, DEFERREFCONST 
ASSUMETARGETDEFS 
DISCARDFILE 1000 
MAP APPLIANCEEMPTAG,TARGET APPLIANCE_EMPTAG;

RESTRICTIONS:

  1. Do not exclude key columns, and do not use COLSEXCEPT to exclude columns that contain unsupported data types.
  2. COLS should include the column used in KEYCOL in replicat.

Adding Objects: Example Enabling DDL (Source)

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 extract is running [ SOURCE ]

conn / as sysdba

@role_setup

2. Grant the role to GATE_USER.[SOURCE]

grant GGS_user_role to gate_user;

3.Run the marker script:[SOURCE]

conn / as sysdba

@marker_setup.sql

4. Run ddl_setup.sql script [ SOURCE ]

conn / as sysdba

@ddl_setup.sql

5. Update the extract parameter file to include DDL

We need to add the command DDL INCLUDE MAPPED OBJNAME <SCHEMA_NAME>.<TABLE_NAME>

It will look as below:

edit params ext1

EXTRACT EXT1

SETENV (ORACLE_SID=”SRCDB”)

SETENV (ORACLE_HOME = “/oracle/app/oracle/product/12.1.0/dbhome_1”)

SETENV (NLS_LANG = “AMERICAN_AMERICA.AL32UTF8”)

userid ggate, PASSWORD ggate123

FETCHOPTIONS  FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION

DBOPTIONS ALLOWUNUSEDCOLUMN

DISCARDFILE /ggate/app/oracle/product/12.2.1.0.3/TST/EXT1.dsc, APPEND, MEGABYTES 100

EXTTRAIL /ggate/app/oracle/product/12.2.1.0.3/TST/E5

DDL INCLUDE MAPPED OBJNAME APPLIANCE.CLASSTAB1

TABLE APPLIANCE.CLASSTAB1;

No changes required to extract pump and replicat parameter files.

6. Restart the extract [ SOURCE]

stop ext1

start ext1

Now DDL replication is enabled. Do some DDL changes on source.

Then check its status and also check it on the Destination

— Check the status of extract:

GGSCI () 18> stats EXT1

Adding new table to a existing extract and replicat

Below are the steps for adding a new table to the existing extract and replicat. In this example, we will add DBACLASS.SAL table to existing replication.

1.stop extract and pump process [ SOURCE]

stop EXT1

stop EXT1P

GGSCI > info all

2. STOP replicat:[ TARGET ]

Confirm that lag is zero i.e replicat consumed all the transactions.

lag replicat REPR1

As LAG command reports that no more records to process(i.e lag is zero), we can stop the replicat also.

stop REPR1

3. Enable supplemental logging for the tables, that need to be added [ SOURCE].

dblogin USERID ggate, PASSWORD ggate123

Successfully logged into database.

GGSCI > add trandata DBACLASS.SAL

4. Include the table in extract param file:[ SOURCE ]

Add the line TABLE DBACLASS.SAL; in parameter file.

edit params EXT1

EXTRACT EXT1

SETENV (ORACLE_SID=”SRCDB”)

SETENV (ORACLE_HOME = “/oracle/app/oracle/product/11.2.0/dbhome_1”)

SETENV (NLS_LANG = “AMERICAN_AMERICA.AL32UTF8”)

userid ggate, PASSWORD ggate123

FETCHOPTIONS  FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION

DBOPTIONS ALLOWUNUSEDCOLUMN

DISCARDFILE /ggate/app/oracle/product/11.2.1.0.3/TST/EXT1.dsc, APPEND, MEGABYTES 100

EXTTRAIL /ggate/app/oracle/product/11.2.1.0.3/TST/E5

WARNLONGTRANS 6H, CHECKINTERVAL 1H

TABLE DBACLASS.EMP;

TABLE DBACLASS.DEPT;

TABLE DBACLASS.SAL;

5. Include the table in extract pump file: [ SOURCE]

Add the line TABLE DBACLASS.SAL; in pump parameter file.

edit params EXT1P

EXTRACT EXT1P

SETENV (ORACLE_HOME = “/oracle/app/oracle/product/11.2.0/dbhome_1”)

SETENV (ORACLE_SID = “SRCDB”)

SETENV (NLS_LANG = “AMERICAN_AMERICA.AL32UTF8”)

USERID ggate, PASSWORD ggate123

RMTHOST 172.20.21.56, MGRPORT 7809

RMTTRAIL /ggate/oradata/datacapture/TRG/P1

TABLE DBACLASS.EMP;

TABLE DBACLASS.DEPT;

TABLE DBACLASS.SAL;

6. start the extract and extract pump [ SOURCE ]

start EXT1

start EXT1P

7. Now do the initial loading:

Here for initial loading, take export dump of the table that we are adding and import in target database

expdp dumpfile=initload.dmp logfile=initload.log directory=DIRT tables=DBACLASS.SAL

Copy the dump to target host and import.

scp oracle@172.38.342.29:/archive/dump/initload.dmp .

impdp dumpfile=initload.dmp logfile=imp_initialload.log directory=FULL_REFRESH

8. Update the replicat parameter file with handlecollisions parameter: [ TARGET]

Add the line MAP DBACLASS.SAL,TARGET DBACLASS.SAL, HANDLECOLLISIONS; in param file

REPLICAT REPR1

setenv (ORACLE_HOME=”/u01/app/oracle/product/12.1.0.2/dbhome_1″)

setenv (ORACLE_SID=”TRGDB”)

USERID ggate, PASSWORD ggate123

DBOPTIONS SUPPRESSTRIGGERS,DEFERREFCONST

ASSUMETARGETDEFS

DISCARDFILE /ggate/oradata/datacapture/TRG/reprt.dsc,append,megabytes 1000

MAP  DBACLASS.EMP,TARGET DBACLASS.EMP;

MAP  DBACLASS.DEPT,TARGET DBACLASS.DEPT ;

MAP  DBACLASS.SAL,TARGET DBACLASS.SAL, HANDLECOLLISIONS;

9 . Start the replicat and wait for the lag to be cleared.[TARGET]

start repr1

GGSCI >  lag replicat REPR1

Wait till this lag is cleared( lag command will report as At EOF, no more records to process.)

10. Remove the HANDLECOLLISIONS from the replicat parameter file and restart the replicat[TARGET]

As the lag is cleared, remove the handlecollision parameter and restart the replicat.

stop REPR1

ggsci> edit replicat REPR1

REPLICAT REPR1

setenv (ORACLE_HOME=”/u01/app/oracle/product/12.1.0.2/dbhome_1″)

setenv (ORACLE_SID=”TRGDB”)

USERID ggate, PASSWORD ggate123

DBOPTIONS SUPPRESSTRIGGERS,DEFERREFCONST

ASSUMETARGETDEFS

DISCARDFILE /ggate/oradata/datacapture/TRG/reprt.dsc,append,megabytes 1000

MAP  DBACLASS.EMP,TARGET DBACLASS.EMP;

MAP  DBACLASS.DEPT,TARGET DBACLASS.DEPT ;

MAP  DBACLASS.SAL,TARGET DBACLASS.SAL;

start repr1

Disable DDL replication

If you wish to disable DDL replication in goldengate, then you need to run few scripts from GG_HOME as SYS

@ddl_remove.sql

@marker_remove.sql

@ddl_disable.sql

Oracle GoldenGate: Best Practices and Security

For replication to be robust, well tuned and secure, some of the out-of-the box settings need to modified. Here is a list of these items, focusing on the Goldengate Best Practices and Security, in areas such as the Database, Parameters, processes, etc.

Oracle Database

• Ensure that all the tables have Primary keys.

• Do not grant the DBA privilege to the GG user, give only the required privileges.

• Supplemental logging should be enabled using TRANDATA

Oracle Golden Gate Parameter files

• Use USECHECKPOINTS with the PURGEOLDEXTRACTS clause in the mgr.prm file.

PURGEOLDEXTRACTS , USECHECKPOINTS, MINKEEPHOURS 24 MINKEEPFILES 30 • Do not use HANDLECOLLISIONS unless it is absolutely required and temporary. Read more on effects of using handle collisions

• If HANDLECOLLISIONS is being used it should only be set for specific tables ONLY and the remainder tables set to NOHANDLECOLLISIONS.

Oracle Golden Gate Performance

• Increase read buffer size of dblogreader to 4M

TRANLOGOPTIONS DBLOGREADER

TRANLOGOPTIONS BUFSIZE 4096000

TRANLOGOPTIONS DBLOGREADERBUFSIZE 4096000 • Use datapump compression

Oracle Golden Gate High Availability

• Add autostart for mgr process

• Add auto restart specifying at least 3 minimum restart attempts

AUTORESTART EXTRACT *, RETRIES , WAITMINUTES , RESETMINUTES • Use checkpoint table not checkpoint file for checkpoint recovery.

Oracle GoldenGate Security

• In PCI environments personal sensitive data should be excluded by the extract process, to avoid any of this data in the trail files.

• Used password encryption

• Use datapump encryption

• Use trail file encryption

Troubleshooting/Monitoring

• Write lag messages and warnings to the ggserror.log file for all the Oracle Goldengate processes.

LAGREPORTMINUTES

LAGCRITICALMINUTES

• Use WARNLONGTRANS 2h, CHECKINTERVAL 10m (example) on extract side to write warnings to the alert log for long running transactions

• Write record count of transactions every 1 hour or so to report log.

REPORTCOUNT EVERY HOURS, RATE • Rollover the report for the individual processes daily.

REPORTROLLOVER AT on

Good Examples

Oracle GoldenGate Installation, Part 1

https://blog.pythian.com/oracle-goldengate-installation-part-1/

Setting Up Table Replication In Oracle Goldengate

http://oracle-help.com/goldengate/setting-table-replication-oracle-goldengate/

Multi-Master Oracle Replication Using GoldenGate

http://houseofbrick.com/multi-master-oracle-replication-using-goldengate/

Golden Gate: Setup Bi-Directional Replication in Multi-tenant Environment (PDBs)

https://eclipsys.ca/goldengate-setup-bi-directional-replication-in-multitenant-environment-pdbs/

Installation And Configuration Of GoldenGate 19c Microservices On RAC

https://gavinsoorma.com.au/2020/07/installation-and-configuration-of-goldengate-19c-microservices-on-rac/