How to prepare a Goldengate test lab?

At the end of this post we should have a working Goldengate environment where we can replicate a dummy table.

High level steps:

Install an Oracle database (19c)
Install Goldengate 21c (source and destination will be on the same machine in my case) (You can follow this post: ora-base.com/how-to-install-goldengate-21c-in-silent-mode
Create the source and destination pluggable databases
Prepare the database for Goldengate
Create the HR demo schema on both pluggable databases
Prepare Goldengate for replication
Create the EXTRACT on the source
Create REPLICAT on the target
Start EXTRACT and PUMP
Start REPLICAT
Testing

My environment:

Oracle OCI instance:
OS             : Red Hat Enterprise Linux release 8.5 (Ootpa)
ORACLE_HOME    : /u01/app/oracle/product/19.3.0.0/dbhome_1
PDB_SOURCE     : PDBSRC
PDB_TARGET     : PDBTRG
GG_HOME_SRC    : /u01/app/oracle/GGSRC/
GG_HOME_TRG    : /u01/app/oracle/GGTRG/
GG_VERSION     : 21.3.0.0

Step 1 ) Install Oracle database 19c

I have used OCI which automated my oracle database installation (I will explain in a future post how to achieve that)

Make sure that you reference the TARGET HOST in /etc/hosts file. As I have the target and the source o the same host, I do something like this:

[oracle@primary ~]$ cat /etc/hosts
127.0.0.1 targethost
127.0.0.1 sourcehost

Add these lines in .bashrc on both servers:

vi ~/.bashrc

alias oggsrc='export OGG_HOME=/u01/app/oracle/GGSRC; cd /u01/app/oracle/GGSRC'
alias oggtrg='export OGG_HOME=/u01/app/oracle/GGTRG; cd /u01/app/oracle/GGTRG'

TNS_ADMIN=${ORACLE_HOME}/network/admin
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${ORACLE_HOME}/lib

export TNS_ADMIN LD_LIBRARY_PATH

Step 2) Install Goldengate – 2 times in my case

One can follow this post ora-base.com/how-to-install-goldengate-21c-in-silent-mode. This time I have added all the parameters into 1 line:

—— —— —- SOURCE

[oracle@primary Disk1]$ ./runInstaller -silent INSTALL_OPTION=ora21c \
SOFTWARE_LOCATION=/u01/app/oracle/GGSRC \
START_MANAGER=true \
MANAGER_PORT=7809 \
DATABASE_LOCATION=/u01/app/oracle/product/19.3.0.0/dbhome_1 \
INVENTORY_LOCATION=/u01/app/oraInventory \
UNIX_GROUP_NAME=oinstall
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 21423 MB Passed
Checking swap space: must be greater than 150 MB. Actual 4081 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-08-19_02-39-40PM. Please wait ...[oracle@primary Disk1]$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2022-08-19_02-39-40PM.log
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2022-08-19_02-39-40PM.log' for more details.
Successfully Setup Software.

—— —— —- TARGET

[oracle@primary Disk1]$ ./runInstaller -silent INSTALL_OPTION=ora21c \
SOFTWARE_LOCATION=/u01/app/oracle/GGTRG \
START_MANAGER=true \
MANAGER_PORT=7909 \
DATABASE_LOCATION=/u01/app/oracle/product/19.3.0.0/dbhome_1 \
INVENTORY_LOCATION=/u01/app/oraInventory \
UNIX_GROUP_NAME=oinstall

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 20493 MB Passed
Checking swap space: must be greater than 150 MB. Actual 4081 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-08-19_02-44-57PM. Please wait ...[oracle@primary Disk1]$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2022-08-19_02-44-57PM.log
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2022-08-19_02-44-57PM.log' for more details.
Successfully Setup Software.

Please note the port number and the location.

Let’s check the GG installations:

[oracle@primary ~]$ oggsrc
[oracle@primary GGSRC]$ ./ggsci

GGSCI (primary) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (primary) 2>


[oracle@primary Disk1]$ oggtrg
[oracle@primary GGTRG]$ ./ggsci
GGSCI (primary) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (primary) 2>

All good.

Step 3) Create the source and destination pluggable databases

SQL> CREATE PLUGGABLE DATABASE PDBSRC ADMIN USER pdbadmin IDENTIFIED BY pdbadmin#123 FILE_NAME_CONVERT = ('/u02/oradata/DB193H1/pdbseed/', '/u02/oradata/DB193H1/orclsrc/') DEFAULT TABLESPACE users DATAFILE '/u02/oradata/DB193H1/orclsrc/users01.dbf' SIZE 100M AUTOEXTEND ON NEXT 1M;

Pluggable database created.

SQL> CREATE PLUGGABLE DATABASE PDBTRG ADMIN USER pdbadmin IDENTIFIED BY pdbadmin#123 FILE_NAME_CONVERT = ('/u02/oradata/DB193H1/pdbseed/', '/u02/oradata/DB193H1/orcltrg/') DEFAULT TABLESPACE users DATAFILE '/u02/oradata/DB193H1/orcltrg/users01.dbf' SIZE 100M AUTOEXTEND ON NEXT 1M;


Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB			  READ WRITE NO
	 4 PDBSRC			  MOUNTED
	 5 PDBTRG			  MOUNTED
SQL> alter pluggable database PDBSRC open;

Pluggable database altered.

SQL> alter pluggable database PDBTRG open;

Pluggable database altered.

SQL> alter pluggable database PDBSRC save state;

Pluggable database altered.

SQL> alter pluggable database PDBTRG save state;

Pluggable database altered.

SQL>

Make sure you add the necessary TNSNAMES entries:

pdbsrc =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdbsrc)
    )
  )

pdbtrg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdbtrg)
    )
  )

Step 4) Prepare the database for Goldengate

Create the GG user (container level)

CREATE USER c##gg_admin identified by ggadmin1;
ALTER USER c##gg_admin quota unlimited ON users;
ALTER USER c##gg_admin set container_data=all container=current;
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
ALTER DATABASE FORCE LOGGING;
ALTER SESSION SET CONTAINER=cdb$root;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SESSION SET CONTAINER=PDBSRC;
ALTER PLUGGABLE DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SESSION SET CONTAINER=PDBTRG;
ALTER PLUGGABLE DATABASE ADD SUPPLEMENTAL LOG DATA;

Grant necessary privileges to GG user:

sqlplus / as sysdba

GRANT DBA to c##gg_admin container=ALL;
GRANT ALTER any table to c##gg_admin container=ALL;
GRANT CREATE SESSION,CONNECT,RESOURCE,ALTER SYSTEM, select any dictionary,FLASHBACK ANY TABLE to c##gg_admin container=all;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'c##gg_admin', container=>'all',privilege_type=>'CAPTURE',grant_select_privileges => TRUE,do_grants => TRUE,grant_optional_privileges=>'*');

Step 5) Create the HR demo schema on both pluggable databases

I have explained in this blog ora-base.com/how-to-create-the-sample-hr-schema-in-your-pdb how to do this, but I will try to make a short version of it down below:

PDBSRC

SQL> alter session set container=PDBSRC;

Session altered.

SQL> @/u01/app/oracle/product/19.3.0.0/dbhome_1/demo/schema/human_resources/hr_main.sql

specify password for HR as parameter 1:
Enter value for 1: hr

specify default tablespeace for HR as parameter 2:
Enter value for 2: USERS

specify temporary tablespace for HR as parameter 3:
Enter value for 3: temp

specify log path as parameter 4:
Enter value for 4: /tmp

PDBTRG

SQL> alter session set container=PDBTRG;

Session altered.

SQL> @/u01/app/oracle/product/19.3.0.0/dbhome_1/demo/schema/human_resources/hr_main.sql

specify password for HR as parameter 1:
Enter value for 1: hr

specify default tablespeace for HR as parameter 2:
Enter value for 2: USERS

specify temporary tablespace for HR as parameter 3:
Enter value for 3: temp

specify log path as parameter 4:
Enter value for 4: /tmp

Step 6) Prepare Goldengate for replication

create credentialstore on both GG: target and source PDB

[oracle@primary oradata]$ oggsrc
[oracle@primary GGSRC]$ ./ggsci

GGSCI (primary) 1> Add CredentialStore

Credential store created.

GGSCI (primary) 2>



oracle@primary GGSRC]$ oggtrg
[oracle@primary GGTRG]$ ./ggsci

GGSCI (primary) 1> Add CredentialStore

Credential store created.

GGSCI (primary) 2>

Add schematrandata for HR schema

GGSCI (primary) 3> dblogin userid c##gg_admin@pdbsrc, password ggadmin1
Successfully logged into database PDBSRC.

GGSCI (primary as c##gg_admin@DB193H1/PDBSRC) 4> add schematrandata PDBSRC.HR

2022-08-23 09:57:56 INFO OGG-01788 SCHEMATRANDATA has been added on schema "HR".

2022-08-23 09:57:56 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "HR".

2022-08-23 09:57:56 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema "HR".

2022-08-23 09:57:58 INFO OGG-10471 ***** Oracle Goldengate support information on table HR.COUNTRIES *****
Oracle Goldengate support native capture on table HR.COUNTRIES.
Oracle Goldengate marked following column as key columns on table HR.COUNTRIES: COUNTRY_ID.

2022-08-23 09:57:58 INFO OGG-10471 ***** Oracle Goldengate support information on table HR.DEPARTMENTS *****
Oracle Goldengate support native capture on table HR.DEPARTMENTS.
Oracle Goldengate marked following column as key columns on table HR.DEPARTMENTS: DEPARTMENT_ID.

2022-08-23 09:57:58 INFO OGG-10471 ***** Oracle Goldengate support information on table HR.EMPLOYEES *****
Oracle Goldengate support native capture on table HR.EMPLOYEES.
Oracle Goldengate marked following column as key columns on table HR.EMPLOYEES: EMPLOYEE_ID.

2022-08-23 09:57:58 INFO OGG-10471 ***** Oracle Goldengate support information on table HR.JOBS *****
Oracle Goldengate support native capture on table HR.JOBS.
Oracle Goldengate marked following column as key columns on table HR.JOBS: JOB_ID.

2022-08-23 09:57:58 INFO OGG-10471 ***** Oracle Goldengate support information on table HR.JOB_HISTORY *****
Oracle Goldengate support native capture on table HR.JOB_HISTORY.
Oracle Goldengate marked following column as key columns on table HR.JOB_HISTORY: EMPLOYEE_ID, START_DATE.

2022-08-23 09:57:58 INFO OGG-10471 ***** Oracle Goldengate support information on table HR.LOCATIONS *****
Oracle Goldengate support native capture on table HR.LOCATIONS.
Oracle Goldengate marked following column as key columns on table HR.LOCATIONS: LOCATION_ID.

2022-08-23 09:57:58 INFO OGG-10471 ***** Oracle Goldengate support information on table HR.REGIONS *****
Oracle Goldengate support native capture on table HR.REGIONS.
Oracle Goldengate marked following column as key columns on table HR.REGIONS: REGION_ID.

GGSCI (primary as c##gg_admin@DB193H1/PDBSRC) 5>

Add credentials into credentialstore on both source and target goldengate

[oracle@primary GGTRG]$ oggsrc
[oracle@primary GGSRC]$ ./ggsci

GGSCI (primary) 1> alter CredentialStore add user C##GG_ADMIN@PDBSRC password ggadmin1 alias GG_ADMIN_PDBSRC

Credential store altered.

GGSCI (primary) 2> alter CredentialStore add user C##GG_ADMIN@PDBTRG password ggadmin1 alias GG_ADMIN_PDBTRG

Credential store altered.

GGSCI (primary) 3> alter CredentialStore add user C##GG_ADMIN@DB193H1 password ggadmin1 alias GG_ADMIN_ROOT

Credential store altered.

GGSCI (primary) 4> info CredentialStore

Reading from credential store:

Default domain: OracleGoldenGate

  Alias: GG_ADMIN_PDBTRG
  Userid: C##GG_ADMIN@PDBTRG

  Alias: GG_ADMIN_PDBSRC
  Userid: C##GG_ADMIN@PDBSRC

  Alias: GG_ADMIN_ROOT@DB193H1
  Userid: C##GG_ADMIN

GGSCI (primary) 5>

[oracle@primary GGSRC]$ oggtrg
[oracle@primary GGTRG]$ ./ggsci

GGSCI (primary) 1> alter CredentialStore add user C##GG_ADMIN@PDBSRC password ggadmin1 alias GG_ADMIN_PDBSRC

Credential store altered.

GGSCI (primary) 2> alter CredentialStore add user C##GG_ADMIN@PDBTRG password ggadmin1 alias GG_ADMIN_PDBTRG

Credential store altered.

GGSCI (primary) 3> alter CredentialStore add user C##GG_ADMIN@DB193H1 password ggadmin1 alias GG_ADMIN_ROOT

Credential store altered.

GGSCI (primary) 4> info credentialstore

Reading from credential store:

Default domain: OracleGoldenGate

  Alias: GG_ADMIN_PDBTRG
  Userid: C##GG_ADMIN@PDBTRG

  Alias: GG_ADMIN_PDBSRC
  Userid: C##GG_ADMIN@PDBSRC

  Alias: GG_ADMIN_ROOT@DB193H1
  Userid: C##GG_ADMIN

Step 7 ) Create the EXTRACT on the source

create the parameter file, register it and create the extract group

[oracle@primary GGSRC]$ ./ggsci
GGSCI (primary) 1> edit param EXTHR

GGSCI (primary) 17> view param exthr

Extract exthr
UserIdAlias GG_ADMIN_ROOT
TranlogOptions IntegratedParams (max_sga_size 256)
DISCARDFILE ./dirrpt/exthr.dsc, APPEND
ExtTrail ./dirdat/eh
LOGALLSUPCOLS
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
UPDATERECORDFORMAT COMPACT
SOURCECATALOG PDBSRC
Table PDBSRC.HR.EMPLOYEES;
Table PDBSRC.HR.JOBS;

create the pump extract

GGSCI (primary as C##GG_ADMIN@DB193H1/PDBSRC) 8> dblogin useridalias GG_ADMIN_ROOT
Successfully logged into database PDBSRC.

GGSCI (primary as C##GG_ADMIN@DB193H1/PDBSRC) 10> edit param phr
GGSCI (primary as C##GG_ADMIN@ORCL) 16> view param phr

EXTRACT phr
RMTHOST targethost, MGRPORT 7909
PASSTHRU
RMTTRAIL ./dirdat/rt
SOURCECATALOG PDBSRC
Table PDBSRC.HR.EMPLOYEES;
Table PDBSRC.HR.JOBS;

Create EXTRACT group, EXTRAIL, PUMP AND RMTTRAIL

GGSCI (primary as c##gg_admin@DB193H1/PDBSRC) 11> ADD EXTRACT exthr, INTEGRATED TRANLOG, BEGIN NOW
Integrated Extract added.

GGSCI (primary as c##gg_admin@DB193H1/PDBSRC) 12> ADD EXTTRAIL ./dirdat/eh, EXTRACT exthr, MEGABYTES 5
EXTTRAIL added.

GGSCI (primary as c##gg_admin@DB193H1/PDBSRC) 13> ADD RMTTRAIL ./dirdat/rt, EXTRACT exthr, MEGABYTES 5
RMTTRAIL added.

GGSCI (primary as c##gg_admin@DB193H1/PDBSRC) 14> ADD EXTRACT phr, EXTTRAILSOURCE ./dirdat/eh
Extract added.

GGSCI (primary as c##gg_admin@DB193H1/PDBSRC) 15> ADD RMTTRAIL ./dirdat/rt, EXTRACT phr, MEGABYTES 5
RMTTRAIL added.

GGSCI (primary as c##gg_admin@DB193H1/PDBSRC) 16> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXTHR       00:00:00      00:00:58
EXTRACT     STOPPED     PHR         00:00:00      00:00:11

GGSCI (primary as c##gg_admin@DB193H1/PDBSRC) 17> dblogin useridalias GG_ADMIN_ROOT
Successfully logged into database CDB$ROOT.

GGSCI (primary as C##GG_ADMIN@DB193H1/CDB$ROOT) 20> register extract exthr database container (PDBSRC)

2022-08-23 11:28:40  INFO    OGG-02003  Extract group EXTHR successfully registered with database at SCN 2420216.

Step 8 ) Create the REPLICAT on the target

[oracle@primary ~]$ oggtrg
[oracle@primary GGTRG]$ ./ggsci

GGSCI (primary) 1> dblogin useridalias GG_ADMIN_ROOT
Successfully logged into database PDBTRG.

GGSCI (primary as C##GG_ADMIN@DB193H1/PDBTRG) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (primary as C##GG_ADMIN@DB193H1/PDBTRG) 3> edit param rhr

GGSCI (primary as C##GG_ADMIN@DB193H1/PDBTRG) 4> view param rhr

REPLICAT rhr
DISCARDFILE ./dirrpt/rhr.dsc, APPEND
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
ASSUMETARGETDEFS
--- User login
USERIDALIAS GG_ADMIN_PDBTRG
DDL INCLUDE ALL
DDLOPTIONS REPORT
MAP PDBSRC.HR.*, TARGET HR.*;

Create REPLICAT group and Trail Files:

GGSCI (primary) 7> dblogin useridalias GG_ADMIN_PDBTRG
Successfully logged into database PDBTRG.

GGSCI (primary as C##GG_ADMIN@DB193H1/PDBTRG) 8> ADD REPLICAT rhr, INTEGRATED, EXTTRAIL ./dirdat/rt
Integrated Replicat added.


GGSCI (primary as C##GG_ADMIN@DB193H1/PDBTRG) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     RHR         00:00:00      00:00:04

Step 9 ) Start EXTRACT and PUMP

GGSCI (primary as C##GG_ADMIN@DB193H1/CDB$ROOT) 22> start exthr

Sending START request to Manager ...
Extract group EXTHR starting.


GGSCI (primary as C##GG_ADMIN@DB193H1/CDB$ROOT) 23> start phr

Sending START request to Manager ...
Extract group PHR starting.


GGSCI (primary as C##GG_ADMIN@DB193H1/CDB$ROOT) 24> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXTHR       00:00:00      00:00:01
EXTRACT     RUNNING     PHR         00:00:00      00:08:51

Step 10 ) Start REPLICAT

GGSCI (primary as C##GG_ADMIN@DB193H1/PDBTRG) 10> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y

Sending STOP request to Manager ...
Request processed.
Manager stopped.


GGSCI (primary as C##GG_ADMIN@DB193H1/PDBTRG) 11> start mgr
Manager started.


GGSCI (primary as C##GG_ADMIN@DB193H1/PDBTRG) 12> start RHR

Sending START request to Manager ...
Replicat group RHR starting.


GGSCI (primary as C##GG_ADMIN@DB193H1/PDBTRG) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     RHR         00:00:00      00:00:01


GGSCI (primary as C##GG_ADMIN@DB193H1/PDBTRG) 14>

Step 11 ) Testing

INSERT

Before the insert:

SOURCE 

[oracle@primary GGSRC]$ sqlplus hr/hr@pdbsrc

SQL> select * from jobs where job_id='DBA-JOB-ID';

no rows selected

SQL>

TARGET

[oracle@primary GGTRG]$ sqlplus hr/hr@pdbtrg

SQL> select * from jobs where job_id='DBA-JOB-ID';

no rows selected

SQL>

After the insert

SOURCE

SQL> insert into jobs values ('DBA-JOB-ID','dba-job',30000,60000);

1 row created.

SQL> commit;

Commit complete.

SQL>

TARGET

SQL> select * from jobs where job_id='DBA-JOB-ID';

JOB_ID	   JOB_TITLE			       MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
DBA-JOB-ID dba-job				    30000      60000

SQL>

DELETE

SOURCE

[oracle@primary GGSRC]$ sqlplus hr/hr@pdbsrc

SQL> delete from jobs where job_id='DBA-JOB-ID';

1 row deleted.

SQL> commit;

Commit complete.

SQL>

TARGET 

[oracle@primary GGTRG]$ sqlplus hr/hr@pdbtrg

SQL> select * from jobs where job_id='DBA-JOB-ID';

no rows selected

SQL>

Oracle Adventures

How to prepare a Goldengate test lab?