This note details the goldengate 19c setup, leveraging the marketplace image on OCI.
#1 Provision the gg VM from marketplace. Be sure to select the classic version.
#2 Login with opc
#3 Run ggsci <version>
-bash-4.2$ ggsci oracle11 Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.200714 31846205_FBO Linux, x64, 64bit (optimized), Oracle 11g on Sep 5 2020 14:28:05 Operating system character set identified as UTF-8. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. G GSCI (gg19cls) 1> create subdirs Creating subdirectories under current directory /home/opc/oracle11 Parameter file /home/opc/oracle11/dirprm: already exists. Report file /home/opc/oracle11/dirrpt: already exists. Checkpoint file /home/opc/oracle11/dirchk: already exists. Process status files /home/opc/oracle11/dirpcs: already exists. SQL script files /home/opc/oracle11/dirsql: already exists. Database definitions files /home/opc/oracle11/dirdef: already exists. Extract data files /home/opc/oracle11/dirdat: already exists. Temporary files /home/opc/oracle11/dirtmp: already exists. Credential store files /home/opc/oracle11/dircrd: already exists. Masterkey wallet files /home/opc/oracle11/dirwlt: already exists. Dump files /home/opc/oracle11/dirdmp: already exists. GGSCI (gg19cls) ; exit
#4 Create the TNS_ADMIN directory
mkdir -p /u02/deployments/oracle11/network/admin
#5 Create the tnsnames.ora file
Configure the TNS aliases to connect to either or both the source and target database(s).
If the source db is running on ASM, specify the alias to connect to asm
ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.19)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = +ASM)
)
)#6 Create the alias for the tnsnames.ora file
ln -s network/admin/tnsnames.ora tnsnames.ora
#7 Prepare the source db
@poc_profile @poc_gg_source
If TDE is configured on the source db, apply the patch 17030189 or install the file prvtlmpg.plb in the db. this file is present in the gg directory
#8 Prepare the target db
@poc_profile @poc_gg_target
#9 (Optional) Setup an instant client and check the db connection
On the location when the instant client is installed, do:
ln -s /u02/deployments/oracle11/network/admin/tnsnames.ora tnsnames.ora
#10 Set the ggschema.
This step is required to create successfully the heartbeat tables. When gg hosts both the source and target, the schema uses must be the same.
edit params ./GLOBAL ggschema ggadmin exit
#11 Create the credentials for the source and/or target databases
-bash-4.2$ ggsci oracle11
add credentialstore
alter credentialstore add user ggadmin@db11ss password gg alias db11ss domain OracleGoldenGate
alter credentialstore replace user ggadmin@db11ss password gg alias db11ss domain OracleGoldenGate
Check with
dblogin useridalias db11ss domain OracleGoldenGate
do the same for the target
alter credentialstore add user ggadmin@db11st password gg alias db11st domain OracleGoldenGate alter credentialstore replace user ggadmin@db11st password gg alias db11st domain OracleGoldenGate
Check with
dblogin useridalias db11st domain OracleGoldenGate
#12 Add the schema trandata to the source db
-bash-4.2$ ggsci oracle11
dblogin useridalias db11ss domain OracleGoldenGate
add schematrandata scott
scott is the schema being replicated
#13 Add the checkpoint table to the target db
-bash-4.2$ ggsci oracle11
dblogin useridalias db11st domain OracleGoldenGate
add checkpointtable ggadmin.checkpointtable
#14 Add the hearbeat table to both the source and target db
-bash-4.2$ ggsci oracle11
dblogin useridalias db11ss domain OracleGoldenGate
add heartbeattable gg_source
dblogin useridalias db11st domain OracleGoldenGate
add heartbeattable
#15 Check the gg port
edit params mgr
=> this is 10011
#14 Create the group for the initial load extract
EXTRACT e11si1
USERIDALIAS db11ss
RMTHOST localhost MGRPORT 10011
RMTTASK replicat, group r11ti1
TABLE scott.*;
#15 Create the group for the initial replicat
REPLICAT r11ti1
USERIDALIAS db11st
ASSUMETARGETDEFS
MAP scott.*, TARGET scott.*;
#15 Create the initial load extract
$ ggsci oracle11
dblogin useridalias db11ss domain OracleGoldenGate
add extract e11si1, sourceistable
add replicat r11ti1, specialrun
start extract e11si1
#16 Create the replicat for the initial load
$ ggsci oracle11
dblogin useridalias db11st domain OracleGoldenGate
add replicat r11ti1, exttrail ./dirdat/ri, checkpointtable ggadmin.checkpointtable
#17 Start the initial extract replicat
$ ggsci oracle11
start extract e11si1
This extract should run a few seconds, then stopped once all records have been pulled and inserted on the target db.
#18 Create the parameter file for the continuous load extract
EXTRACT e11sc1
USERIDALIAS db11ss
EXTTRAIL ./dirdat/sc
TRANLOGOPTIONS ASMUSER sys@ASMS, ASMPASSWORD welcome1
TABLE scott.*;
#19 Create the parameter file for the continuous replicat
REPLICAT r11tc1
USERIDALIAS db11st
ASSUMETARGETDEFS
MAP scott.dept, TARGET scott.dept;
#####
ENCRYPT PASSWORD sharedsecretdb11ss AES128 ENCRYPTKEY keydb11ss
@?/rdbms/admin/prvtclkm.plb
GRANT EXECUTE ON DBMS_INTERNAL_CLKM TO scott
GRANT DV_STREAMS_ADMIN to ggadmin;
#20 Prepare the db for the integrated capture
dblogin useridalias db11ss domain OracleGoldenGate
register extrACT E11SC1 DATABASE
#20 Create the extract and replicat
$ ggsci oracle11
add extract e11sc1, integrated tranlog, begin now
add rmttrail ./dirdat/sc, extract e11sc1
add replicat r11tc1, exttrail ./dirdat/sc, nodbcheckpoint, begin now
For classic, do not specify ‘integrated’
Check that all groups are present:
info all allsubprocesses
#21 Start the processes
start extract e11sc1
start replicat r11tc1
#22 Change from classic to integrated
dblogin useridalias db11ss domain OracleGoldenGate
register extrACT E11SC1 DATABASE
info all
# How to activate trace for extract
send extract E11SC1 trace /tmp/E11SC1.trc
send extract E11SC1 trace off
Note that if the error OGG-08221 Cannot register or unregister EXTRACT . . . because of the following SQL error: OCI Error ORA (status = 26723-ORA-26723: user “GGADMIN” requires the role “DV_STREAMS_ADMIN/DV_XSTREAM_ADMIN/DV_GOLDENGATE_ADMIN”, best is probably to unregister DV as documented in the MOS 803948.1.