Hello Friends's,
This New post comes after a long time ,
So In this post we will disscus about another method of replication by using Goldengate
should be called as DOWNSTREAM ALL on TARGET integrated Method. All process are on Target
so Let's Get started with the process
Step 1 : Create the user if it's not already present .
[root@Machine3 ~]# /usr/sbin/useradd -u 503 -c "Goldengate Owner" -g oinstall -G dba,oper,asmdba,asmoper,asmadmin gguser
[root@Machine3 ~]#
[root@Machine3 ~]# id gguser
uid=503(gguser) gid=501(oinstall) groups=501(oinstall),502(dba),503(oper),504(asmadmin),506(asmdba),507(asmoper)
Step 2 : Sudo to the gguser and check out the installable goldengate binaries .
[gguser@Machine3 Goldengate]$ ls -ltr
total 2006656
-rw-r--r--. 1 gguser oinstall 345971438 Jun 22 15:04 V1011472_21GG.zip
-rw-r--r--. 1 gguser oinstall 556240981 Jun 22 15:06 V983658_19cGG.zip
-rw-r--r--. 1 gguser oinstall 381014026 Jun 22 15:13 V1011471_21_Micro.zip
-rw-r--r--. 1 gguser oinstall 771576660 Jun 22 15:17 V1009716_19c_micro.zip
[gguser@Machine3 Goldengate]$ mkdir GG19c_HOME
[gguser@Machine3 Goldengate]$ mkdir 19cGG
Step 3 : Unzip the binaries to desired location .
[gguser@Machine3 Goldengate]$ unzip --q V983658_19cGG.zip -d /Goldengate/19cGG
Archive: V983658_19cGG.zip
creating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/
creating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/
creating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/.oui
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/attachHome.sh
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/clusterparam.ini
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/detachHome.sh
creating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/images/
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/oraparam.ini
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/oraparam.ini.deinstall
creating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/resource/
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/resource/cons.nls
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/resource/cons_de.nls
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/resource/cons_es.nls
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/resource/cons_fr.nls
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/resource/cons_it.nls
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/resource/cons_ja.nls
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/resource/cons_ko.nls
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/resource/cons_pt_BR.nls
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/resource/cons_zh_CN.nls
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/resource/cons_zh_TW.nls
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/install/runInstaller.sh
creating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/response/
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
inflating: /Goldengate/19cGG/fbo_ggs_Linux_x64_shiphome/Disk1/runInstaller
Step 4 : Once unzip is done move to the location where the binaris are extracted and follow below steps to start the installation .
[gguser@Machine3 19cGG]$ ls -ltr
total 332
-rw-r--r--. 1 gguser oinstall 1413 May 29 2019 OGG-19.1.0.0-README.txt
drwxr-xr-x. 3 gguser oinstall 19 Oct 18 2019 fbo_ggs_Linux_x64_shiphome
-rw-r--r--. 1 gguser oinstall 332523 Oct 21 2019 OGG_WinUnix_Rel_Notes_19.1.0.0.4.pdf
[gguser@Machine3 19cGG]$
[gguser@Machine3 19cGG]$
[gguser@Machine3 19cGG]$ cd fbo_ggs_Linux_x64_shiphome/
[gguser@Machine3 fbo_ggs_Linux_x64_shiphome]$ ls
Disk1
[gguser@Machine3 fbo_ggs_Linux_x64_shiphome]$ cd Disk1/
[gguser@Machine3 fbo_ggs_Linux_x64_shiphome]$ cd Disk1/
[gguser@Machine3 Disk1]$
[gguser@Machine3 Disk1]$ ls -ltr
total 12
drwxr-xr-x. 4 gguser oinstall 4096 Oct 18 2019 install
drwxr-xr-x. 12 gguser oinstall 4096 Oct 18 2019 stage
-rwxr-xr-x. 1 gguser oinstall 918 Oct 18 2019 runInstaller
drwxrwxr-x. 2 gguser oinstall 25 Oct 18 2019 response
[gguser@Machine3 Disk1]$
Step 5 : Start the installer.
[gguser@Machine3 Disk1]$ export DISPLAY=10.10.4.4:0.0
[gguser@Machine3 Disk1]$ ./runInstaller
Starting gguser Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 15220 MB Passed
Checking swap space: must be greater than 150 MB. Actual 7887 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch gguser Universal Installer from /tmp/OraInstall2022-07-01_11-26-30PM. Please wait ...
Step 6 : Once the installation is completed . Start the GGSCI prompt from the bin folder or goldengate home.
[gguser@Machine3 GG19c_HOME]$ ./ggsci
gguser GoldenGate Command Interpreter for gguser
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), gguser 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, gguser and/or its affiliates. All rights reserved.
Step 7 : Now prepare the database for the goldengate . Our all things are done on target database none of the goldengate process will on source server .
[gguser@Machine3 Goldengate]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 2 13:01:29 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, gguser. All rights reserved.
Connected to:
gguser Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OTMP MOUNTED
SQL>
SQL>
SQL> alter pluggable database OTMP open read write;
Pluggable database altered.
SQL> alter pluggable database OTMP save state;
Pluggable database altered.
SQL> alter session set container=OTMP;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
OTMP
Step 8 : Steps on Primary
SQL> alter system set enable_goldengate_replication=true;
System altered.
SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;
SUPPLEME FORCE_LOGGING
-------- ---------------------------------------
YES YES
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FINDB READ WRITE NO
SQL> grant create table to c##ggadmin;
SQL> grant flashback any table to c##ggadmin;
SQL> grant execute on dbms_flashback to c##ggadmin;
SQL> grant execute on utl_file to c##ggadmin;
SQL> grant create any table to c##ggadmin;
SQL> grant insert any table to c##ggadmin;
SQL> grant update any table to c##ggadmin;
SQL> grant delete any table to c##ggadmin;
SQL> grant drop any table to c##ggadmin;
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL> alter system set undo_retention=86400;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 86400
undo_tablespace string UNDOTBS1
SQL>
Step 9 : Primary , Table Creation
SQL> alter session set container=FINDB;
Session altered.
SQL>
create tablespace bigtab datafile '+DATAC1' size 2G autoextend on next 500M maxsize 5G;SQL>
Tablespace created.
SQL> create user bigone identified by bigone quota unlimited on bigtab;
User created.
SQL> alter user bigone default tablespace bigtab;
User altered.
SQL> grant connect,resource to bigone;
Grant succeeded.
SQL> CREATE TABLE bigone.bigtab (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
); 2 3 4 5 6
Table created.
SQL> alter table BIGONE.BIGTAB
add constraint PK_TAB primary key (ID); 2
Table altered.
SQL> create table BIGONE.COUNTRIES (
COUNTRY_ID VARCHAR2(7),
COUNTRY_NAME VARCHAR2(40),
constraint COUNTRY_C_ID_PK primary key (COUNTRY_ID)
); 2 3 4 5
Table created.
SQL> alter table BIGONE.COUNTRIES add constraint countries_name_uq unique (country_name);
Table altered.
Step 10 : User creation and privilege assignation on target .
create user ggadm identified by ggadm;
grant connect, resource to ggadm;
grant select any dictionary, select any table to ggadm;
grant create table to ggadm;
grant flashback any table to ggadm;
grant execute on dbms_flashback to ggadm;
grant execute on utl_file to ggadm;
grant create any table to ggadm;
grant insert any table to ggadm;
grant update any table to ggadm;
grant delete any table to ggadm;
grant drop any table to ggadm;
grant dba TO ggadm;
exec dbms_goldengate_auth.grant_admin_privilege('ggadm');
Step 11 : Target Table and tablespace Creation :
SQL> alter session set container=OTMP;
Session altered.
SQL>create tablespace bigtab datafile '+DATAC1' size 2G autoextend on next 500M maxsize 5G;
Tablespace created.
SQL> create user bigone identified by bigone quota unlimited on bigtab;
User created.
SQL> alter user bigone default tablespace bigtab;
User altered.
SQL> grant connect,resource to bigone;
Grant succeeded.
SQL> CREATE TABLE bigone.bigtab (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
); 2 3 4 5 6
Table created.
SQL> alter table BIGONE.BIGTAB
add constraint PK_TAB primary key (ID); 2
Table altered.
SQL> create table BIGONE.COUNTRIES (
COUNTRY_ID VARCHAR2(7),
COUNTRY_NAME VARCHAR2(40),
constraint COUNTRY_C_ID_PK primary key (COUNTRY_ID)
); 2 3 4 5
Table created.
SQL> alter table BIGONE.COUNTRIES add constraint countries_name_uq unique (country_name);
Table altered.
Listener file sample :
LSNR_OTM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Machine3.database.com)(PORT = 1598))
)
ADR_BASE_LSNR_OTM = /oracle/app/orawork
SID_LIST_LSNR_OTM =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = FINDB)
(ORACLE_HOME = /oracle/app/orawork/product/19.0.0/db_1)
(SID_NAME = FINDB)
)
(SID_DESC =
(GLOBAL_DBNAME = CDBFIN.database.com)
(ORACLE_HOME = /oracle/app/orawork/product/19.0.0/db_1)
(SID_NAME = CDBFIN)
)
(SID_DESC =
(GLOBAL_DBNAME = CDBOTMP1.database.com)
(ORACLE_HOME = /oracle/app/orawork/product/19.0.0/db_1)
(SID_NAME = CDBOTMP1)
)
(SID_DESC =
(GLOBAL_DBNAME = OTMP)
(ORACLE_HOME = /oracle/app/orawork/product/19.0.0/db_1)
(SID_NAME = OTMP)
)
)
TNS file sample :
LSNR_OTM =
(ADDRESS = (PROTOCOL = TCP)(HOST = Machine3.database.com)(PORT = 1598))
CDBOTMP1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Machine3.database.com)(PORT = 1598))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDBOTMP1.database.com)
)
)
FINDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Machine1.database.com)(PORT = 1598))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FINDB.database.com)
(UR = A)
)
)
OTMP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Machine3.database.com)(PORT = 1598))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = OTMP.database.com)
(UR = A)
)
)
asmpr =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Machine1.database.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
(UR = A)
)
)
asmtr =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Machine3.database.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
(UR = A)
)
)
CDBFIN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Machine1.database.com)(PORT = 1598))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDBFIN.database.com)
(UR = A)
)
)
GGSCI PROMPT ON TARGET FOR SOURCE
Step 12 : Create wallet / Credential store and add source database entries to connect the source database.
[gguser@Machine3 GG_HOME19c]$ gg
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (Machine3.database.com) 1> create wallet
Created wallet.
Opened wallet.
GGSCI (Machine3.database.com) 2> Add CredentialStore
Credential store created.
GGSCI (Machine3.database.com) 3> info mgr
Manager is running (IP port TCP:Machine3.database.com.7809, Process ID 12834).
GGSCI (Machine3.database.com) 4>
GGSCI (Machine3.database.com) 4> alter credentialstore add user ggadm@OTMP Password ggadm alias ggadm
Credential store altered.
GGSCI (Machine3.database.com) 5> Alter CredentialStore Add USER "sys@asmpr as sysasm", PASSWORD Password1 Alias gasmp
Credential store altered.
GGSCI (Machine3.database.com) 6> Alter CredentialStore Add USER "sys@asmtr as sysasm", PASSWORD Password1 Alias gasmt
Credential store altered.
GGSCI (Machine3.database.com) 7> alter credentialstore add user c##ggadmin@CDBFIN Password ora123 alias ggadmin
Credential store altered.
GGSCI (Machine3.database.com) 8> info credentialstore
Reading from credential store:
Default domain: OracleGoldenGate
Alias: gasmt
Userid: "sys@asmtr as sysasm"
Alias: ggadm
Userid: ggadm@OTMP
Alias: gasmp
Userid: "sys@asmpr as sysasm"
Alias: ggadmin
Userid: c##ggadmin@CDBFIN
Step 13 : DBlogin source and add trandata for the needed tables .
GGSCI (Machine3.database.com) 9> dblogin useridalias ggadmin
Successfully logged into database CDB$ROOT.
GGSCI (Machine3.database.com as c##ggadmin@CDBFIN/CDB$ROOT) 2> add checkpointtable FINDB.c##ggadmin.chkpoint
Successfully created checkpoint table FINDB.c##ggadmin.chkpoint.
GGSCI (Machine3.database.com as c##ggadmin@CDBFIN/CDB$ROOT) 3> add trandata FINDB.BIGONE.BIGTAB
2022-07-02 19:08:00 INFO OGG-15132 Logging of supplemental redo data enabled for table FINDB.BIGONE.BIGTAB.
2022-07-02 19:08:00 INFO OGG-15133 TRANDATA for scheduling columns has been added on table FINDB.BIGONE.BIGTAB.
2022-07-02 19:08:00 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table FINDB.BIGONE.BIGTAB.
2022-07-02 19:08:01 INFO OGG-10471 ***** Oracle Goldengate support information on table BIGONE.BIGTAB *****
Oracle Goldengate support native capture on table BIGONE.BIGTAB.
Oracle Goldengate marked following column as key columns on table BIGONE.BIGTAB: ID.
GGSCI (Machine3.database.com as c##ggadmin@CDBFIN/CDB$ROOT) 4> add trandata FINDB.BIGONE.COUNTRIES
2022-07-02 19:09:46 INFO OGG-15132 Logging of supplemental redo data enabled for table FINDB.BIGONE.COUNTRIES.
2022-07-02 19:09:46 INFO OGG-15133 TRANDATA for scheduling columns has been added on table FINDB.BIGONE.COUNTRIES.
2022-07-02 19:09:46 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table FINDB.BIGONE.COUNTRIES.
2022-07-02 19:09:46 INFO OGG-10471 ***** Oracle Goldengate support information on table BIGONE.COUNTRIES *****
Oracle Goldengate support native capture on table BIGONE.COUNTRIES.
Oracle Goldengate marked following column as key columns on table BIGONE.COUNTRIES: COUNTRY_ID.
Step 14 : ADD EXTRACT FOR DOWNSTREAM METHOD
[gguser@Machine3 admin]$ gg
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (Machine3.database.com) 1>
GGSCI (Machine3.database.com) 1>
GGSCI (Machine3.database.com) 1> dblogin useridalias ggadmin
Successfully logged into database CDB$ROOT.
GGSCI (Machine3.database.com as c##ggadmin@CDBFIN/CDB$ROOT) 2> edit param EX_PM1
GGSCI (Machine3.database.com as c##ggadmin@CDBFIN/CDB$ROOT) 3> view param EX_PM1
EXTRACT EX_PM1
----------------------------------------------------------------------------------------------------------------
-- State that source and target definitions are identical:
-- Initial Command : add extract ex_pm, integrated TRANLOG, begin now
-- Trail : add exttrail /Goldengate/GG_HOME19c/dirdat/sd, extract ex_pm, MEGABYTES 1024
-----------------------------------------------------------------------------------------------------------------
-- ENVIRONMENT
SETENV (ORACLE_HOME = "/oracle/app/orawork/product/19.0.0/db_1")
SETENV (ORACLE_SID = "CDBFIN")
useridalias ggadmin
EXTTRAIL /Goldengate/GG_HOME19c/dirdat/sd
DISCARDFILE /Goldengate/GG_HOME19c/dirrpt/disc_EF_SRC.dsc, PURGE
--TRANLOGOPTIONS ASMUSERALIAS ggasm
TranlogOptions IntegratedParams (max_sga_size 300)
DDL INCLUDE MAPPED
TABLE FINDB.BIGONE.BIGTAB;
TABLE FINDB.BIGONE.COUNTRIES;
Step 15 : Register extract and extract trail :
GGSCI (Machine3.database.com as c##ggadmin@CDBFIN/CDB$ROOT) 4>
GGSCI (Machine3.database.com as c##ggadmin@CDBFIN/CDB$ROOT) 4> register extract EX_PM1 database container(FINDB)
2022-07-02 21:26:09 INFO OGG-02003 Extract EX_PM1 successfully registered with database at SCN 3098993.
GGSCI (Machine3.database.com as c##ggadmin@CDBFIN/CDB$ROOT) 5> add extract EX_PM1, integrated tranlog, begin now , DESCRIPTION "Tables BIGTAB and COUNTRIES"
EXTRACT (Integrated) added.
GGSCI (Machine3.database.com as c##ggadmin@CDBFIN/CDB$ROOT) 6> ADD EXTTRAIL /Goldengate/GG_HOME19c/dirdat/sd, EXTRACT EX_PM1 , MEGABYTES 100
EXTTRAIL added.
GGSCI (Machine3.database.com as c##ggadmin@CDBFIN/CDB$ROOT) 7>
Step 16 : GGSCI ON TARGET FOR TARGET
[gguser@Machine3 admin]$ gg
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (Machine3.database.com) 1>
GGSCI (Machine3.database.com) 1>
GGSCI (Machine3.database.com) 1> dblogin useridalias ggadm
Successfully logged into database OTMP.
GGSCI (Machine3.database.com as ggadm@CDBOTMP1/OTMP) 2> add checkpointtable OTMP.ggadm.chkpoint
Successfully created checkpoint table OTMP.ggadm.chkpoint.
GGSCI (Machine3.database.com as ggadm@CDBOTMP1/OTMP) 3> edit param GLOBALS
GGSCI (Machine3.database.com as ggadm@CDBOTMP1/OTMP) 4> view param GLOBALS
OTMP.ggadm.chkpoint
GGSCI (Machine3.database.com as ggadm@CDBOTMP1/OTMP) 5>
GGSCI (Machine3.database.com) 6> dblogin useridalias ggadm
Successfully logged into database OTMP.
GGSCI (Machine3.database.com as ggadm@CDBOTMP1/OTMP) 7> edit param REP_TR1
GGSCI (Machine3.database.com as ggadm@CDBOTMP1/OTMP) 3> view param REP_TR1
REPLICAT REP_TR1
----------------------------------------------------------------------------------------------------------------
-- State that source and target definitions are identical:
-- Initial Command : add extract ex_pm, integrated TRANLOG, begin now
-- Trail : Add Replicat REP_TRT1 Integrated exttrail /Goldengate/GG_HOME19c/dirdat/sd, description "Tables BIGTAB and COUNTRIES"
-----------------------------------------------------------------------------------------------------------------
-- ENVIRONMENT
SETENV (ORACLE_HOME = "/oracle/app/orawork/product/19.0.0/db_1")
SETENV (ORACLE_SID = "CDBOTMP1")
ASSUMETARGETDEFS
useridalias ggadm
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
REPERROR (1403,DISCARD)
REPERROR(2291,DISCARD)
HANDLECOLLISIONS
DISCARDFILE /Goldengate/GG_HOME19c/dirrpt/disr.dsc, PURGE
DDL INCLUDE MAPPED
APPLYNOOPUPDATES
DDLERROR DEFAULT IGNORE RETRYOP
MAP FINDB.BIGONE.BIGTAB, target OTMP.BIGONE.BIGTAB;
MAP FINDB.BIGONE.COUNTRIES, target OTMP.BIGONE.COUNTRIES;
Step 17 : Add replicat :
GGSCI (Machine3.database.com as ggadm@CDBOTMP1/OTMP) 4> Add Replicat REP_TR1 Integrated exttrail /Goldengate/GG_HOME19c/dirdat/sd, description "Tables BIGTAB and COUNTRIES"
REPLICAT (Integrated) added.
GGSCI (Machine3.database.com) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EX_PM1 00:00:00 00:13:54
Description "Tables BIGTAB and COUNTRIES"
REPLICAT STOPPED REP_TR1 00:00:00 00:02:42
Description "Tables BIGTAB and COUNTRIES"
GGSCI (Machine3.database.com) 4> start REP_TR1
Sending START request to MANAGER ...
REPLICAT REP_TR1 starting
GGSCI (Machine3.database.com) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EX_PM1 00:14:02 00:00:02
Description "Tables BIGTAB and COUNTRIES"
REPLICAT STOPPED REP_TR1 00:00:00 00:02:51
Description "Tables BIGTAB and COUNTRIES"
Starting the replicat got me error as below in err log file
ggserr.log:
2022-07-02T21:40:37.629+0530 ERROR OGG-02091 Oracle GoldenGate Delivery for Oracle, rep_tr1.prm: Operation not supported because enable_goldengate_replication is not set to true.
2022-07-02T21:40:37.630+0530 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep_tr1.prm: PROCESS ABENDING.
Solution is as below .
Enable GG replication on container level & restart the replicat:
SQL> alter system set enable_goldengate_replication=TRUE scope=BOTH;
System altered.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OTMP READ WRITE NO
Step 18 : Now come on the ggsci prompt and try to start the replicat :
GGSCI (Machine3.database.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EX_PM1 00:00:00 00:00:10
Description "Tables BIGTAB and COUNTRIES"
REPLICAT STOPPED REP_TR1 00:00:00 00:07:31
Description "Tables BIGTAB and COUNTRIES"
GGSCI (Machine3.database.com) 2>
GGSCI (Machine3.database.com) 2> start REP_TR1
Sending START request to MANAGER ...
REPLICAT REP_TR1 starting
GGSCI (Machine3.database.com) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EX_PM1 00:00:00 00:00:08
Description "Tables BIGTAB and COUNTRIES"
REPLICAT RUNNING REP_TR1 00:00:00 00:07:40
Description "Tables BIGTAB and COUNTRIES"
GGSCI (Machine3.database.com) 4>
Step 19 : Now start the load of tables from source database.
SQL> alter session set container=FINDB;
Session altered.
SQL>
SQL>
SQL> DECLARE
l_lookup_id NUMBER(10);
l_create_date DATE;
BEGIN
FOR i IN 1 .. 400000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;
INSERT INTO bigone.bigtab (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
END LOOP;
COMMIT;
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
PL/SQL procedure successfully completed.
Step 20 : Check the progress of replication :
GGSCI (Machine3.database.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EX_PM1 00:00:00 00:00:07
Description "Tables BIGTAB and COUNTRIES"
REPLICAT RUNNING REP_TR1 00:00:00 00:00:05
Description "Tables BIGTAB and COUNTRIES"
GGSCI (Machine3.database.com) 2> info EX_PM1
EXTRACT EX_PM1 Last Started 2022-07-02 21:40 Status RUNNING
Description "Tables BIGTAB and COUNTRIES"
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Process ID 30464
Log Read Checkpoint Oracle Integrated Redo Logs
2022-07-02 22:34:51
SCN 0.3156814 (3156814)
GGSCI (Machine3.database.com) 3> info REP_TR1
REPLICAT REP_TR1 Last Started 2022-07-02 21:45 Status RUNNING
Description "Tables BIGTAB and COUNTRIES"
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Process ID 30794
Log Read Checkpoint File /Goldengate/GG_HOME19c/dirdat/sd000000000
2022-07-02 22:29:03.000000 RBA 67490874
Step 21 : check the status as the transaction replication is in progress or completed
Hope This helps
In case of any questions post in the comments will try and answer.
Regards
Sultan Khan
ConversionConversion EmoticonEmoticon