Oracle GoldenGate Install


Download and Installation
Unidirectional Configuration Example
    1- Configure Manager Process (Source and Target DB)
    2- Configure Extract Process (Source DB)
    3- Configure Replication Parameters (Target DB)
    4- Start Extract and check its Status (Source)
    5- Start Replicate and check its Status (Destination)
Uninstall
Help and Informational Commands
Configure DataPump Parameters
Configure Supplemental Logging for Replicated Tables
Adding Objects: Example adding all tables of a schema
COLS & COLSEXCEPT FILTER (Filter Columns)
Adding Objects: Example Enabling DDL (Source)
Adding new table to an existing extract and replicat
Disable DDL replication
Best Practices and Security
Good Examples


 

 

Download & Installation (Both Sites)

 

This post covers Download & Installation of Oracle GoldenGate 12c (12.2.0.1.1) (software for real-time data integration and replication in heterogeneous IT Systems).

 

Create Oracle Golden Gate staging directory and home directory in BOTH DB Servers

 

D:\>cd oracle

md gg_stage

md gg_home\product\12.2.0\oggcore_1

cd oracle\gg_home\product\12.2.0\oggcore_1

 

Download the File Oracle GoldenGate 12.2.0.2.181009 for Oracle on Microsoft Windows x64 (64-bit), 327.9 MB V980625-01.zip.

The software should be installed on both servers.

 

 

Unzip the Golden Gate media and click on setup.exe file to initiate installation.

 

Machine generated alternative text: Application Tools
              x64 shiphome Name response Diskl Date modified 8/31/2020
              12:02 PM 8/31/2020 12:02 PM 8/31/2020 12:03 PM 0/30/2013
              AM V File folder File folder File folder Application
              Search Diskl Run as administrator

 

 

Choose on which database version you installing golden gate.

Machine generated alternative text: Select
              Installation Option Installation Option Installation
              Details Summary Install Product Oracle GoldenGate
              19.1.0.0.4 - Install Wizard - Step 1 of 5 Select the
              database for this Oracle GoldenGate installatiorL Oracle
              GoldenGate for Oracle Database I gc (E7EDMB) Oracle
              GoldenGate for Oracle Database IBC (E7EDMB) Oracle
              GoldenGate for Oracle Database 12c (E7EDMB) Oracle
              GoldenGate for Oracle Database I Ig (E7EDMB)

 

 

Provide Oracle Golden Gate Home and Oracle Database Home location and port number. I used:

E:\oracle\gg

Machine generated alternative text: Specify
              Installation Details Installation Option Installation
              Details Summarv Install Product racle GoldenGate
              19.1.0.0.4 - Install Wizard - Step 2 of 5 Specify a
              location to install Oracle GoldenGatæ If installing on a
              cluster, t is recommended to install Oracle GoldenGate
              binaries on local storage on each node in the cluster
              Optionally, specify the location of the Orade Database and
              a free port to automatically start the Oracle GoldenGate
              Manager after Browse Software Location art Manager
              Database Location Manager Port E: inraclebg D:
              inracleiproducfll 22 Didbhome_l 7 Bog

 

 

 

When the Installation is successful, click close.

Once Installation done, login to Golden gate from gg home and check the version.

 

Cd E:\oracle\gg

ggsci

 

Machine generated alternative text: C: Nonboard)ggsci
              Oracle GoldenGate Command Interpreter for Oracle Uersion
              19.1.ø.ø.4 Windows x64 (optimized). Oracle 12c on Oct 19
              2M 9 12:52 Operating system character set identif ied as
              windows—1252. Copyright (C) 1995. 2M 9. Oracle and/or its
              affiliates. RI I rights GGSCI ("f iumØ3881) I) stop
              manager reserved. Manager Ree you Sending Request Manager
              process is required by other GGS processes . sure you want
              to stop it STOP request to MANAGER . processed. stopped.

 

 

Type

info all

Machine generated alternative text: GGSCI
              (wfiumø3882) 1) info all Group Lag Chkpt T ime Since Chkpt
              Program MRNA GER Status RUNNING

 

 

 

NOTE = If you get an error on MSVCR100.dll

 

Solution: This DLL is a part of the Microsoft Visual C++ 2010 Redistribution Package (x64). (vcredist_x64.exe)

Download from here and install.

 

 

NOTE = If you get an error on MSVCP140.dll

Machine generated alternative text: The program can't
              start because MSUCP140.dII is missing from your computer.
              Try reinstalling the program to fix this problem.

Solution: This DLL is a part of the Microsoft Visual C++ 2015, 2017 and 2019 Redistribution Package (x64). (vcredist_x64.exe)

Download from here and install.

 

 

On the source and target server add the following entries to /home/oracle/.bash_profile

export LD_LIBRARY_PATH=/home/oracle/goldengate:$ORACLE_HOME/lib

 

Ensure the environment variables are set using:

source /home/oracle/.bash_profile

 

 

 

Create GoldenGate Subdirectories (Both Sites)

On each server create subdirectories for GoldenGate using the CREATE SUBDIRS command. For example:

 

Machine generated alternative text: GGSCI ("f
              iumØ3881) 2) create subdirs Creating subdirectories under
              current directory C: XUsersXonboard Parameter file Report
              f ile Checkpo int f ile Process status files SQL script
              files Database definitions files Extract data files
              Temporary files Credential store files Masterkey wallet
              files Dump f i les E: E: E: E: E: E: E: E: E: E: E: Xorac
              le : Xorac leXggXdirrpt : Xorac irchk: Xorac leXggXdirpcs
              : Xorac : Xorac leXggXdirdef : Xorac leXggXdirdat : Xorac
              le : Xorac le : Xorac leXggXdirwIt : Xorac le : already
              already already already already already already already
              already already already exists. exists. exists. exists.
              exists. exists. exists. exists. exists. exists. exists.

 

As seen above, it will create below subdirectories.

  • dirtmp - For storing temp files
  • dirsql - For storing SQLs
  • dirrpt - For storing report files
  • dirpcs - For storing
  • dirout - For storing output files
  • dirdef - For storing
  • dirdat - For storing trail files
  • dirchk - For storing checkpoint files

 

 

Below are the brief description of each directory:

Sub-directories  Description 
dirbdb  This directory contains the GoldenGate datastore files
dirchk  This directory contains the GoldenGate checkpoint files. Extract and Replicat processes stores current read and write positions in the checkpoint file to support data accuracy and fault tolerance. Extension of the file is either .cpe for Extract checkpoint files or .cpr for Replicat checkpoint files.
dircrd  This directory contains the GoldenGate Credential Store
dirdat  This directory contains the GoldenGate trail files. Created by Extract/Pump processes to store records of extracted data for further processing (example to be processed further by replicat process).File name format starts with a user-defined two-character prefix.
dirdef  This directory contains the GoldenGate definition mapping files. data definitions files are created by the DEFGEN utility and it contain source or target data definitions used in a heterogeneous synchronization environment. Written in external ASCII. File name format is a user-defined name specified in the DEFGEN parameter file.
dirdmp  This directory contains the GoldenGate process dump
dirjar  This directory contains the GoldenGate Java Archive
dirout  This directory contains the GoldenGate output files
dirpcs  This directory contains the GoldenGate process files. It is default location for status files. File name is <group>.<extension> where <group> is the name of the group and <extension> is either pce (Extract), pcr (Replicat), or pcm (Manager). These files are only created while a process is running. The file shows the program name, the process name, the port and process id that is running.
dirprm  This directory contains the GoldenGate process parameter files. It store run-time parameters for GoldenGate process groups or utilities. Written in external ASCII format. File name format is <group name/user-defined name>.prm or mgr.prm.
dirrpt  This directory contains the GoldenGate process report. It is default location for process report files created by Extract, Replicat, and Manager processes to report statistical information relating to a processing run. Written in external ASCII format.
File name format is <group name><sequence number>.rpt where <sequence number> is a sequential number appended to aged files.
dirsql  This directory contains the GoldenGate user defined files. It iis default location for scripts created by the TRIGGEN utility to contain SQL syntax for creating GoldenGate logging triggers and GoldenGate log tables. Written in external format.

File name format is a user-defined name or the defaults of GGSLOG (table-creation script) or the table name (trigger-creation script), with the extension of .sql.
These scripts can be edited if needed.

dirtmp  This directory contains the GoldenGate temporary files. It is default location for storing large transactions when the size exceeds the allocated memory size.
dirwlt  This directory contains the GoldenGate wallet files
dirwww  This directory contains the GoldenGate web pages
dirver  This directory contains the GoldenGate Veridata files

 

 

Check the connectivity from source to target for replication.

Both source and target side add the host information to /etc/hosts file.

 

 

 

Verify that LOG_MODE is set to ARCHIVELOG (On source)

Archivelog must be enable on source side because if we are using classic capture the extract process will capture the changes information through archivelogs only, So it is mandatory for classic capture replication.

 

select LOG_MODE from v$database;

shutdown immediate;

startup mount;

ALTER DATABASE ARCHIVELOG;

alter database open;

select LOG_MODE from v$database;

 

Machine generated alternative text: QL) select
              LOG_MODE from u$database; OG_MODE ORRCHIUELOG QL) shu
              immediate; atabase closed. atabase dismounted. RRCLE
              instance shut down . QL) startup mount; RRCLE instance
              started. bytes bytes bytes bytes bytes otaI System Global
              ixed Size ariable Size atabase Buffers edo Buffers atabase
              mounted. QLD ALTER DATABASE atabase altered. QL) alter
              database atabase altered. Area 54525952øø 1258293832
              W158?2 RRCHIUELOG; open ; QL) select LOG_MODE from
              u$database; OG_MODE RCHIUELOG

 

 

Verify that supplemental logging and forced logging are set properly. (On source)

Enable force logging at the database level. If you are using Oracle 12c Database however, you can alternatively turn force logging at the tablespace level.

 

ALTER DATABASE FORCE LOGGING;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

select SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;

Machine generated alternative text: QLD ALTER
              DATABASE FORCE LOGGING; atabase altered. QLD ALTER
              DATABASE ADD SUPPLEMENTAL LOG DATA; atabase altered. QLD
              select from u$database; UPPLEME FORCE_LOGGING

 

OR

-- 12c only Activate the Loggging for 1 Tablespace

ALTER DATABASE NO FORCE LOGGING;

ALTER TABLESPACE tablespace_name FORCE LOGGING;

ALTER TABLESPACE tablespace_name NOLOGGING;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 

 

Prepare the database to support ddl replication (optional) ( On source)

Turn off recyclebin for the database . . .

alter system set recyclebin=off scope=spfile;

shutdown immediate

startup

 

 

Change parameter GOLDENGATE  (on source and target databases):

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;

show parameter enable_goldengate

 

 

Configure Network

For this basic configuration, an entry is required in the TNSNAMES.ORA file on each server describing the database on the other server.

 

On the source database (fidelio) I added the following entry for the DIEGO database to $ORACLE_HOME/network/admin/tnsnames.ora

DIEGO =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = wfivm03882.us.oracle.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DIEGO)

    )

  )

 

 

On the target database (DIEGO) I added the following entry for the fidelio database to $ORACLE_HOME/network/admin/tnsnames.ora

FIDELIO =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = wfivm03881.us.oracle.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = fidelio)

    )

  )

 

 

 

Create the GoldenGate administrator   (on source and target databases):

A new user should be created to own the GoldenGate database objects.

On each server create the GoldenGate schema owner.

 

create tablespace GOLDENGATE datafile 'E:\oracle\oradata\fidelio\gg_tbs_data01.dbf' size 100m autoextend on maxsize unlimited;

 

create user ggate identified by ggate default tablespace GOLDENGATE quota unlimited on goldengate;

grant execute on utl_file to ggate;

grant connect, resource, unlimited tablespace to ggate;

 

Grant dba to ggate;

OR

grant select any dictionary to ggate;

grant insert any table to ggate;

grant update any table to ggate;

grant delete any table to ggate;

 

EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggate');

 

 

OPTIONAL:

On each server set the GGSCHEMA in the global parameter file.

ggsci

 EDIT PARAMS ./GLOBALS

Machine generated
              alternative text: : _homeX roductX12 . 2 . øXO core A sci
              Oracle GoldenGate Command Interpreter for Oracle Uersion
              19.1.ø.ø.4 Windows x64 (optimized). Oracle 12c on Oct 19
              2M 9 12:52 Operating system character set identif ied as
              windows—1252. reserved. Copyright (C) 1995. GGSCI 2M 9.
              Oracle and/or its affiliates. RI I rights 1) EDIT PRRRMS

 

In this example the parameters file is E:\oracle\gg\GLOBALS

Add the following entry:

GGSCHEMA gguser

Save and close the file

 

 

 

In the Source DB Server, Go to Golden Gate Installed location (in our scenario E:\oracle\gg) and then run the following Golden Gate inbuild scripts for creating all necessary objects  as SYS:

cd E:\oracle\gg

@marker_setup

@ddl_setup

@role_setup.sql

GRANT GGS_GGSUSER_ROLE TO  ggate ;

@ddl_enable.sql   (The default trigger name is GGS_DDL_TRIGGER_BEFORE; it is owned by the SYS user.)

 

 

Brief Explanation of the Scripts:

DDL replication is implemented using the GGS_DDL_TRIGGER_BEFORE database trigger which is configured to fire whenever a DDL statement is executed. This trigger uses procedures the DDL_REPLICATION package to store the DDL statement and other attributes in the DDL tables.

The most significant DDL tables are:

  • GGS_MARKER - all DDL statements executed in the database are stored in this table. CREATE statements are stored in this table.
  • GGS_DDL_HIST - only DDL statements executed by non-Oracle users are stored in this table. CREATE statements are not stored in this table.

When the extract process identifies that a DDL statement has been executed, it generates an entry in the extract trail for the DDL statement. The exact process was not entirely clear at the time of writing. However, the likely sequence of events is:

  • DDL statement is issued within database
  • DDL trigger fires and stores statement in GoldenGate DDL tables
  • DDL operation is stored in redo log (operation 24.1)
  • Extract process captures DDL operation from redo log
  • Extract process looks up corresponding entries in GoldenGate DDL tables
  • Extract process stores DDL statement and attributes in GoldenGate trail.

Once the DDL statement and attributes have been stored in the GoldenGate trail, they can be processed as normal by the data pump and replicat processes.

 

GGSCI DDL Dumps

The contents of the metadata stored in GGS_DDL_HIST table can be dumped using the DUMPDDL SHOW command in GGSCI.

 

 

 

 

 

 

 

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.

 

 

 

 

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

 

 

 

Start Manager

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

 

 

info all

 

 

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

 

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

EXTTRAIL added.

 

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

 

 

 

 

3- 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.*;

 

 

 

 

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

 

 

 

 

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

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.

 

Syntax:

ADD TRANDATA <schema.table_name>  or <schema.*> 


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


Syntax:

ADD TRANDATA <schema.table_name>  or <schema.*> 


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 an existing extract and replicat

 

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


Login to GGSCI

ggsci
dblogin USERID ggate, PASSWORD ggate123


 

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

Login to GGSCI

ggsci
dblogin USERID ggate, PASSWORD ggate123


Syntax:   ADD TRANDATA <schema.table_name>  or <schema.*>

GGSCI > add trandata DBACLASS.SAL

 

 

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

 

 

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

 

 

4. Stop and start the extract and extract pump [ SOURCE ]

stop EXT1

stop  EXT1P

start EXT1

start EXT1P


Verify Status:
info all


Capture Current SCN

select current_scn from v$database;
CURRENT_SCN
-----------
    2298283 <----

 


5. Now do the initial loading, expdp:

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

expdp \'/ as sysdba\' directory=data_pump_dir dumpfile=dbaclass_sal.dmp logfile=dbaclass_sal.log tables=dbaclass.sal flashback_scn=2298283
Or

expdp \'/ as sysdba\' directory=data_pump_dir dumpfile=dbaclass_sal.dmp logfile=dbaclass_sal.log tables=dbaclass.sal


Copy the dump to target host and import.

scp /u01/app/oracle/admin/PSG01DAS/dpdump/dbaclass_sal.dmp oracle@destination:/u01/app/oracle/admin/USG01DAS/dpdump/


impdp \'/ as sysdba\' directory=data_pump_dir dumpfile=dbaclass_sal.dmp logfile=
dbaclass_sal_imp.log remap_schema=source:target transform=segment_attributes:n transform=oid:n


 

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

Add the line MAP DBACLASS.SAL,TARGET DBACLASS.SAL, FILTER ( @GETENV('TRANSACTION', 'CSN') > 2298283), HANDLECOLLISIONS; in param file (if you didn't use SCN, then there is no need of that FILTER)

 

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, FILTER ( @GETENV('TRANSACTION', 'CSN') > 2298283), HANDLECOLLISIONS;

 

 

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

stop repr1

start repr1

GGSCI >  lag replicat REPR1

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


Verify Status:
info all



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

As the lag is cleared, remove the handlecollision parameter and restart the replicat. (if you didn't use SCN, then there is no need of that FILTER)

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

 

 

 

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/

 

GG Commands
http://www.dba.ant.lviv.ua/articles/goldengate


MOS Master Note
Master Note - Oracle GoldenGate (Doc ID 1298817.1)