Initial load from Oracle to PostgreSQL Using GoldenGate

Hosts and databases used in this environment.

Category	Source	Target
Host	srvdb01.localdomain	srvdb02.localdomain
IP Address	192.168.56.81	192.168.56.82
Database	Oracle	PostgreSQL
Database Name	orcl	hr
Database Version	19.3.0.0.0	12.0
Database Home	/u01/app/oracle/product/19.0.0/dbhome_1	/usr/local/pg12
Goldengate Version	19.1.0.0.4	21.3.0.0.0
Goldengate Home	/u01/app/oracle/product/19.1.0/ogg_1	/ogg_1
Goldengate Schema	oggadmin	pggadmin
Table for Replication	Scott.dept	Public.dept

We will setup table replication between Oracle to PostgreSQL databases.

Oracle (Source)	PostgreSQL (Target)
1.Unzip the Oracle GoldenGate for Oracle, Prepare the response file with below parameter values. [oracle@srvdb01 ~]$ unzip -qa /tmp/V983658-01.zip -d /tmp [oracle@srvdb01 ~]$ cd /tmp/fbo_ggs_Linux_x64_shiphome/Disk1/response/ [oracle@srvdb01 response]$ cp oggcore.rsp ogginstall.rsp [oracle@srvdb01 response]$ cat ogginstall.rsp |grep -v "#" INSTALL_OPTION=ORA19c SOFTWARE_LOCATION=/u01/app/oracle/product/19.1.0/ogg_1 START_MANAGER=true MANAGER_PORT=7809 DATABASE_LOCATION=/u01/app/oracle/product/19.0.0/dbhome_1 INVENTORY_LOCATION=/u01/app/oraInventory UNIX_GROUP_NAME=oinstall	1. Unzip and untar Oracle GoldenGate for Postgresql under GoldenGate home directory.  [postgres@srvdb02 ~]$ cd /ogg_1 [postgres@srvdb02 ogg_1]$ unzip -qa /tmp/213000_ggs_Linux_x64_PostgreSQL_64bit.zip [postgres@srvdb02 ogg_1]$ tar -xf ggs_Linux_x64_PostgreSQL_64bit.tar  After installation, we need to add the GoldenGate Home directory ($OGG_HOME) and the $OGG_HOME/lib directory to your LD_LIBRARY_PATH. export PG_HOME=/usr/local/pg12 export OGG_HOME=/ogg_1 export LD_LIBRARY_PATH=$PG_HOME/lib:$OGG_HOME/lib:$LD_LIBRARY_PAT export PATH=/usr/local/pg12/bin:$PATH alias ggsci='/ogg_1/ggsci'
2. Run the installer in silent mode with ogginstall.rsp response file. [oracle@srvdb01 Disk1]$ cd /tmp/fbo_ggs_Linux_x64_shiphome/Disk1 [oracle@srvdb01 Disk1]$ ./runInstaller -silent -responsefile /tmp/fbo_ggs_Linux_x64_shiphome/Disk1/response/ogginstall.rsp Starting Oracle Universal Installer...  Checking Temp space: must be greater than 120 MB.   Actual 6937 MB    Passed Checking swap space: must be greater than 150 MB.   Actual 8191 MB    Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2024-01-09_12-03-00PM. [oracle@srvdb01 Disk1]$ You can find the log of this install session at:  /u01/app/oraInventory/logs/installActions2024-01-09_12-03-00PM.log The installation of Oracle GoldenGate Core was successful. Please check '/u01/app/oraInventory/logs/silentInstall2024-01-09_12-03-00PM.log' for more details. Successfully Setup Software.	2. Start GGSCI and run the command to create the necessary sub-directories. [postgres@srvdb02 ~]$ ggsci Oracle GoldenGate Command Interpreter for PostgreSQL Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047 Oracle Linux 7, x64, 64bit (optimized), PostgreSQL  on Aug  4 2021 20:27:55 Operating system character set identified as UTF-8. Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved. GGSCI (srvdb02.localdomain) 1> create subdirs Creating subdirectories under current directory /ogg_1 Parameter file                 /ogg_1/dirprm: created. Report file                    /ogg_1/dirrpt: created. Checkpoint file                /ogg_1/dirchk: created. Process status files           /ogg_1/dirpcs: created. SQL script files               /ogg_1/dirsql: created. Database definitions files     /ogg_1/dirdef: created. Extract data files             /ogg_1/dirdat: created. Temporary files                /ogg_1/dirtmp: created. Credential store files         /ogg_1/dircrd: created. Master encryption key wallet files /ogg_1/dirwlt: created. Dump files                     /ogg_1/dirdmp: created.
3. Enable archivelog mode, force logging, minimal supplemental logging and golden replication parameter as true. [oracle@srvdb01]$ sqlplus / as sysdba  SQL> startup mount; ORACLE instance started. Total System Global Area  541063272 bytes Fixed Size                  8898664 bytes Variable Size             314572800 bytes Database Buffers          209715200 bytes Redo Buffers                7876608 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> alter database force logging; Database altered. SQL> alter database add supplemental log data; Database altered. SQL> alter system set enable_goldengate_replication=true scope=both; System altered.	3. Create the ODBC.ini file under GoldenGate home. See the following sample odbc.ini file, GoldenGate uses an ODBC connection to connect to the Postgres database. [postgres@srvdb02 ~]$ vi /ogg_1/odbc.ini cat odbc.ini [ODBC Data Sources] pgdsn=DataDirect 7.1 PostgreSQL Wire Protocol [ODBC] IANAAppCodePage=4 InstallDir=/ogg_1 [pgdsn] Driver=/ogg_1/lib/GGpsql25.so Description=DataDirect 7.1 PostgreSQL Wire Protocol Database=hr HostName=192.168.56.82 PortNumber=5432 LogonID=pggadmin Password=pggadmin
4. Creating dedicated tablespace and provide required grants for the GoldenGate schema.   SQL> create tablespace oggtbsp datafile '/u01/app/oracle/oradata/ORCL/oggtbsp.dbf' size 100m autoextend on; Tablespace created.   SQL> create user oggadmin identified by oggadmin default tablespace oggtbsp temporary tablespace temp; grant connect,resource to oggadmin; grant create table,create session,create view to oggadmin; grant select any dictionary,select any table,select any transaction to oggadmin; grant create any table to oggadmin; grant flashback any table to oggadmin; grant execute on dbms_flashback to oggadmin; grant execute on utl_file to oggadmin; grant unlimited tablespace to oggadmin; grant select on system.logmnr_session$ to oggadmin; grant alter system to oggadmin; grant dba to oggadmin; exec dbms_goldengate_auth.grant_admin_privilege(grantee=>'oggadmin',privilege_type=>'CAPTURE',grant_select_privileges=>TRUE,do_grants=>TRUE); User created. SQL> Grant succeeded. SQL> PL/SQL procedure successfully completed.	4.Create a database user, having replication user privileges, which is dedicated to Oracle GoldenGate. [postgres@srvdb02 ~]$ psql psql (12.0) Type "help" for help. postgres=# CREATE USER pggadmin WITH PASSWORD 'pggadmin' LOGIN; CREATE ROLE postgres=# GRANT CONNECT ON DATABASE hr TO pggadmin; GRANT postgres=# ALTER USER pggadmin WITH SUPERUSER; ALTER ROLE postgres=# ALTER USER pggadmin with REPLICATION; ALTER ROLE
5. On source, table structure and datatype look as below, [oracle@srvdb01]$ sqlplus / as sysdba  SQL> conn scott Enter password: Connected. SQL> desc dept;  Name                                      Null?    Type  ----------------------------------------- -------- ---------------  DEPTNO                                    NOT NULL NUMBER(2)  DNAME                                         VARCHAR2(14)  LOC                                                VARCHAR2(13)   SQL> select * from dept;     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON	5. create a similar table structure in the public schema. [postgres@srvdb02 ~]$ psql psql (12.0) Type "help" for help. postgres=# \c hr You are now connected to database "hr" as user "postgres". hr=# CREATE TABLE DEPT (DEPTNO INTEGER PRIMARY KEY,DNAME VARCHAR(14),LOC VARCHAR(13)); CREATE TABLE hr=# \dt         List of relations  Schema | Name | Type  |  Owner --------+------+-------+----------  public | dept | table | postgres (1 row)
6. Connect to the source database from ggsci and enable trandata for the table to be captured.  [oracle@srvdb01 ~]$ ggsci 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 (srvdb01.localdomain) 1> dblogin userid oggadmin,password oggadmin Successfully logged into database. GGSCI (srvdb01.localdomain as oggadmin@orcl) 2> add trandata scott.dept  2024-01-12 02:02:00  INFO    OGG-15131  Logging of supplemental redo log data is already enabled for table SCOTT.DEPT. 2024-01-12 02:02:00  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table SCOTT.DEPT. 2024-01-12 02:02:01  INFO    OGG-10471  ***** Oracle Goldengate support information on table SCOTT.DEPT ***** Oracle Goldengate support native capture on table SCOTT.DEPT. Oracle Goldengate marked following column as key columns on table SCOTT.DEPT: DEPTNO.	6. Client authentication is controlled by a configuration file pg_hba.conf, add lines as mentioned below and reload the configuration.  [postgres@srvdb02 ~]$ vi /pgdata/data/pg_hba.conf host    all             pggadmin        192.168.56.82/32        trust host    replication     pggadmin        192.168.56.82/32        trust [postgres@srvdb02 ~]$ psql psql (12.0) Type "help" for help.   postgres=# select pg_reload_conf();  pg_reload_conf ----------------  t (1 row)
7. On the source, issue the following command to create the initial-load Extract. [oracle@srvdb01 ~]$ ggsci 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 (srvdb01.localdomain) 1> ADD EXTRACT initext,SOURCEISTABLE EXTRACT added.	7. On the target system, issue the following command to create the initial-load Replicat task.  [postgres@srvdb02 ~]$ ggsci Oracle GoldenGate Command Interpreter for PostgreSQL Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047 Oracle Linux 7, x64, 64bit (optimized), PostgreSQL  on Aug  4 2021 20:27:55 Operating system character set identified as UTF-8. Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved. GGSCI (srvdb02.localdomain) 1> ADD REPLICAT initrep,SPECIALRUN Replicat added.
8. On the source system, capture the current scn and substitute as highlighted, issue the following command to create an initial-load extract parameter file and save the configuration.  SQL> select current_scn from v$database; CURRENT_SCN --------------------     2816486 GGSCI (srvdb01.localdomain) 2> edit params initext EXTRACT initext USERID oggadmin,PASSWORD oggadmin RMTHOST 192.168.56.82,MGRPORT 7809 RMTTASK REPLICAT,GROUP initrep TABLE scott.dept,SQLPREDICATE 'AS OF SCN 2816486';	8. On the target system, issue the following command to create an initial-load Replicat parameter file and save the configuration. GGSCI (srvdb02.localdomain) 2> edit params initrep REPLICAT initrep setenv (ODBCINI="/ogg_1/odbc.ini") targetdb pgdsn USERID pggadmin, PASSWORD pggadmin DISCARDFILE ./dirrpt/initrep.dsc, purge MAP scott.dept,TARGET public.dept;
9. On the source system, start the initial-load Extract.  [oracle@srvdb01 ~]$ ggsci  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 (srvdb01.localdomain) 1> start initext  Sending START request to MANAGER ... EXTRACT INITEXT starting	9. The Manager process starts the replicat and loads the data. [postgres@srvdb02 ~]$ psql psql (12.0) Type "help" for help. postgres=# \c hr You are now connected to database "hr" as user "postgres". hr=# select * from dept;  deptno | dname | loc --------+-------+----- (0 rows) hr=# select * from dept;  deptno |   dname    |   loc --------+------------+----------      10 | ACCOUNTING | NEW YORK      20 | RESEARCH   | DALLAS      30 | SALES      | CHICAGO      40 | OPERATIONS | BOSTON (4 rows)