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
- Register EXTRACT in DB
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>