How to prepare a Goldengate test lab?

Uncategorized

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

High level steps:

  1. Install an Oracle database (19c)
  2. 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
  3. Create the source and destination pluggable databases
  4. Prepare the database for Goldengate
  5. Create the HR demo schema on both pluggable databases
  6. Prepare Goldengate for replication
  7. Create the EXTRACT on the source
  8. Create REPLICAT on the target
  9. Start EXTRACT and PUMP
  10. Start REPLICAT
  11. 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:

[[email protected] ~]$ 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

[[email protected] 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 ...[[email protected] 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 

[[email protected] 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 ...[[email protected] 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:

[[email protected] ~]$ oggsrc
[[email protected] GGSRC]$ ./ggsci

GGSCI (primary) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (primary) 2>


[[email protected] Disk1]$ oggtrg
[[email protected] 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
[[email protected] oradata]$ oggsrc
[[email protected] GGSRC]$ ./ggsci

GGSCI (primary) 1> Add CredentialStore

Credential store created.

GGSCI (primary) 2>



[email protected] GGSRC]$ oggtrg
[[email protected] GGTRG]$ ./ggsci

GGSCI (primary) 1> Add CredentialStore

Credential store created.

GGSCI (primary) 2>
  • Add schematrandata for HR schema
GGSCI (primary) 3> dblogin userid c##[email protected], password ggadmin1
Successfully logged into database PDBSRC.

GGSCI (primary as c##[email protected]/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##[email protected]/PDBSRC) 5>
  • Add credentials into credentialstore on both source and target goldengate
[[email protected] GGTRG]$ oggsrc
[[email protected] GGSRC]$ ./ggsci

GGSCI (primary) 1> alter CredentialStore add user C##[email protected] password ggadmin1 alias GG_ADMIN_PDBSRC

Credential store altered.

GGSCI (primary) 2> alter CredentialStore add user C##[email protected] password ggadmin1 alias GG_ADMIN_PDBTRG

Credential store altered.

GGSCI (primary) 3> alter CredentialStore add user C##[email protected] 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##[email protected]

  Alias: GG_ADMIN_PDBSRC
  Userid: C##[email protected]

  Alias: [email protected]
  Userid: C##GG_ADMIN

GGSCI (primary) 5>
[[email protected] GGSRC]$ oggtrg
[[email protected] GGTRG]$ ./ggsci

GGSCI (primary) 1> alter CredentialStore add user C##[email protected] password ggadmin1 alias GG_ADMIN_PDBSRC

Credential store altered.

GGSCI (primary) 2> alter CredentialStore add user C##[email protected] password ggadmin1 alias GG_ADMIN_PDBTRG

Credential store altered.

GGSCI (primary) 3> alter CredentialStore add user C##[email protected] 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##[email protected]

  Alias: GG_ADMIN_PDBSRC
  Userid: C##GG_ADM[email protected]

  Alias: [email protected]
  Userid: C##GG_ADMIN

Step 7 ) Create the EXTRACT on the source

  • create the parameter file, register it and create the extract group
[[email protected] 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##[email protected]/PDBSRC) 8> dblogin useridalias GG_ADMIN_ROOT
Successfully logged into database PDBSRC.

GGSCI (primary as C##[email protected]/PDBSRC) 10> edit param phr
GGSCI (primary as C##[email protected]) 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##[email protected]/PDBSRC) 11> ADD EXTRACT exthr, INTEGRATED TRANLOG, BEGIN NOW
Integrated Extract added.

GGSCI (primary as c##[email protected]/PDBSRC) 12> ADD EXTTRAIL ./dirdat/eh, EXTRACT exthr, MEGABYTES 5
EXTTRAIL added.

GGSCI (primary as c##[email protected]/PDBSRC) 13> ADD RMTTRAIL ./dirdat/rt, EXTRACT exthr, MEGABYTES 5
RMTTRAIL added.

GGSCI (primary as c##[email protected]/PDBSRC) 14> ADD EXTRACT phr, EXTTRAILSOURCE ./dirdat/eh
Extract added.

GGSCI (primary as c##[email protected]/PDBSRC) 15> ADD RMTTRAIL ./dirdat/rt, EXTRACT phr, MEGABYTES 5
RMTTRAIL added.

GGSCI (primary as c##[email protected]/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
  • Register EXTRACT in DB
GGSCI (primary as c##[email protected]/PDBSRC) 17> dblogin useridalias GG_ADMIN_ROOT
Successfully logged into database CDB$ROOT.

GGSCI (primary as C##[email protected]/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

[[email protected] ~]$ oggtrg
[[email protected] GGTRG]$ ./ggsci

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

GGSCI (primary as C##[email protected]/PDBTRG) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (primary as C##[email protected]/PDBTRG) 3> edit param rhr

GGSCI (primary as C##[email protected]/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##[email protected]/PDBTRG) 8> ADD REPLICAT rhr, INTEGRATED, EXTTRAIL ./dirdat/rt
Integrated Replicat added.


GGSCI (primary as C##[email protected]/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##[email protected]/CDB$ROOT) 22> start exthr

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


GGSCI (primary as C##[email protected]/CDB$ROOT) 23> start phr

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


GGSCI (primary as C##[email protected]/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##[email protected]/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##[email protected]/PDBTRG) 11> start mgr
Manager started.


GGSCI (primary as C##[email protected]/PDBTRG) 12> start RHR

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


GGSCI (primary as C##[email protected]/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##[email protected]/PDBTRG) 14>

Step 11 ) Testing

INSERT

Before the insert:

SOURCE 

[[email protected] GGSRC]$ sqlplus hr/[email protected]

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

no rows selected

SQL>
TARGET

[[email protected] GGTRG]$ sqlplus hr/[email protected]

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

[[email protected] GGSRC]$ sqlplus hr/[email protected]

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

1 row deleted.

SQL> commit;

Commit complete.

SQL>
TARGET 

[[email protected] GGTRG]$ sqlplus hr/[email protected]

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

no rows selected

SQL>

Leave Comment

Your email address will not be published. Required fields are marked *