| 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
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
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.*;
Save and close the parameter file
We can check our processes again:
info all
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.
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
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.*;
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
Verify that the Extract process has started successfully:
INFO EXTRACT ext1
INFO EXTRACT ext1 detail
Start Replicat and Check its status (Destination)
start replicat rep1
Sending START request to MANAGER …
REPLICAT REP1 starting
Verify that the Replicat process has started successfully:
START REPLICAT rep1
INFO REPLICAT rep1 detail
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
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
INFO EXTRACT dp1
INFO EXTRACT dp1 detail
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.
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);
COLS:
Instead of COLSEXCEPT, if you are using COLS , then the only change is
COLSEXCEPT (SALCATGOERY,SERVICE_TYPE); – >> COLS(EMP_ID,EMP_NAME,DEPTNO);
RESTRICTIONS:
- Do not exclude key columns, and do not use COLSEXCEPT to exclude columns that contain unsupported data types.
- 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