Goldengate cdc replication from Oracle to Postgres (DAA)

ADD EXTRACT PROCESS

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 10> dblogin USERID c##oggadmin@node1.muthu.org:1526/oradb, PASSWORD ******* , ENCRYPTKEY DEFAULT

Successfully logged into database CDB$ROOT.

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 11> register extract EXT_E1_2 database container(PDB)

2025-08-12 20:22:02  INFO    OGG-02003  Extract group EXT_E1_2 successfully registered with database at SCN 11849257800002.

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 12> add extract EXT_E1_2 integrated tranlog begin now

Integrated Extract added.

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 13> add exttrail ./dirdat/ex  extract EXT_E1_2 megabytes 500

EXTTRAIL added.

ADD PUMP PROCESS

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 14> add extract PXT_P2 exttrailsource ./dirdat/ex

Extract added.

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 15> add rmttrail ./dirdat/er extract PXT_P2 megabytes 500

RMTTRAIL added.

EXTRACT  PARAMETERS:

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 9> view params EXT_E1_2

EXTRACT EXT_E1_2
SETENV (ORACLE_SID='oradb')
USERID c##oggadmin@node1.muthu.org:1526/oradb, PASSWORD *******, ENCRYPTKEY DEFAULT
EXTTRAIL ./dirdat/ex
SOURCECATALOG PDB
DDL &
INCLUDE MAPPED
DDLOPTIONS REPORT
reportcount every 1 minutes, rate
REPORTROLLOVER AT 01:30 ON Sunday

LOGALLSUPCOLS
UPDATERECORDFORMAT FULL
TABLE PDB.EMP.SALARY;

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 10>

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 10> info EXT_E1_2

Extract    EXT_E1_2  Initialized  2024-08-12 00:34   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:02:45 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2024-08-12 00:34:17
                     SCN 0.0 (0)

PUMP  PARAMETERS;

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 27> view params PXT_P2

EXTRACT PXT_P2
SETENV (ORACLE_SID='oradb')
USERID c##oggadmin@node1.muthu.org:1526/oradb, PASSWORD ******* , ENCRYPTKEY DEFAULT
rmthost 27-45-2-299, mgrport 7809
rmttrail ./dirdat/er
PASSTHRU
SOURCECATALOG PDB
TABLE PDB.EMP.SALARY;

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 16> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT_E1_2    00:00:00      00:00:37
EXTRACT     STOPPED     PXT_P2    00:00:00      00:00:10

START EXTRACT CDC;

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 17> start EXT_E1_2

Sending START request to Manager ...

Extract group EXT_E1_2 starting.

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 18> info EXT_E1_2

Extract    EXT_E1_2  Initialized  2025-08-12 20:22   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:49 ago)
Process ID           1135560
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2025-08-12 20:22:10
                     SCN 0.0 (0)

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 26> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT_E1_2    00:00:03      00:00:08
EXTRACT     STOPPED     PXT_P2    00:00:00      00:08:35

CDC PUMP PROCESS TO START;

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 28>  info PXT_P2,detail
Extract    PXT_P2  Initialized  2025-08-12 20:22   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:10:16 ago)
Log Read Checkpoint  File ./dirdat/ex000000000

First Record  RBA 0
  Target Extract Trails:
  Trail Name                                       Seqno        RBA     Max MB Trail Type
  ./dirdat/er                                          0          0        500 RMTTRAIL

START CDC PUMP PROCESS;

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 29> start PXT_P2

Sending START request to Manager ...

Extract group PXT_P2 starting.

GGSCI (node1.muthu.org) 3> info PXT_P2,detail

Extract    PXT_P2  Last Started 2025-08-12 22:01   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:09 ago)
Process ID           1316914
Log Read Checkpoint  File ./dirdat/ex000000000
                     2025-08-12 21:17:05.000000  RBA 5298768
  Target Extract Trails:
  Trail Name                                       Seqno        RBA     Max MB Trail Type
  ./dirdat/er                                          0    5298846        500 RMTTRAIL

INITIAL LOAD:

INITIAL LOAD EXTRACT:

GGSCI (node1.muthu.org) 1> info all tasks

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
EXTRACT     STOPPED     ILT_03

SCN - Used here is from the REAL TIME EXTRACT PROCESS Registered SCN.

“ Extract group EXT_E1_2 successfully registered with database at SCN 11849257800002.”

GGSCI (node1.muthu.org) 24> view params ILT_03

EXTRACT ILT_03
USERID c##oggadmin@node1.muthu.org:1526/oradb, PASSWORD ******* , ENCRYPTKEY DEFAULT
RMTHOST 27-45-2-299,mgrport 7809, Compress
rmtfile /gg01/app/oracle/product/21.3.0/postgres/dirdat/m11/ta
SOURCECATALOG PDB
TABLE PDB.EMP.COUNTRY SQLPREDICATE 'AS OF SCN 11849257800002';

GGSCI (node1.muthu.org) > delete extract ILT_03

GGSCI (node1.muthu.org) 6> add extract ILT_03,sourceistable

Extract added.

GGSCI (node1.muthu.org) 7> info ILT_03
Extract    ILT_03   Initialized  2025-08-12 23:00   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE

GGSCI (node1.muthu.org) 28> start ILT_03

Sending START request to Manager ...

Extract group ILT_03 starting.

GGSCI (node1.muthu.org) 29> info ILT_03

Extract    ILT_03   Initialized  2025-08-12 23:07   Status RUNNING
Checkpoint Lag       Not Available
Process ID           1437844
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

GGSCI (node1.muthu.org) 34> info ILT_03

Extract    ILT_03   Last Started 2025-08-12 23:08   Status RUNNING
Checkpoint Lag       Not Available
Process ID           1437844
Log Read Checkpoint  Table PDB.EMP.COUNTRY
                     2025-08-12 23:10:09  Record 12715001
Task                 SOURCEISTABLE

INITIAL LOAD REPLICAT

GGSCI (ip-27-45-2-299.us-east) 3> view params ILT_03

replicat ILT_03
SETENV (ODBCINI="/u01/app/oracle/product/21.3.0/postgres/odbc.ini" )
targetdb pgdbs, userid oggadmin PASSWORD *******
DISCARDFILE /gg01/app/oracle/product/21.3.0/postgres/dirprm/dsc_max.dsc, APPEND, MEGABYTES 200
BATCHSQL 
MAP PDB.EMP.COUNTRY,TARGET EMP.COUNTRY;

GGSCI (ip-27-45-2-299.us-east) 7> add replicat ILT_03,exttrail /u01/app/oracle/product/21.3.0/postgres/dirdat/m11/ta , NODBCHECKPOINT

Replicat added.

GGSCI (ip-27-45-2-299.us-east) 9> start ILT_03

Sending START request to Manager ...

Replicat group ILT_03 starting.

GGSCI (ip-27-45-2-299.us-east) 10> info ILT_03

Replicat   ILT_03   Last Started 2025-08-12 00:16   Status RUNNING
Checkpoint Lag       01:08:00 (updated 00:00:00 ago)
Process ID           1797
Log Read Checkpoint  File /u01/app/oracle/product/21.3.0/postgres/dirdat/m11/ta000000
                     2025-08-12 23:08:11.626226  RBA 901433

Once the initial load is completed then start cdc replication process.

GGSCI (ip-27-45-2-299.us-east) 2> add replicat REP_R3,exttrail  ./dirdat/er, NODBCHECKPOINT

Replicat added.

GGSCI (ip-27-45-2-299.us-east) 3> info REP_R3

Replicat   REP_R3  Initialized  2025-08-12 05:51   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  File ./dirdat/er000000000
                     First Record  RBA 0

GGSCI (ip-27-45-2-299.us-east) 7> START REPLICAT REP_R3 AFTERCSN 11849257800002

Sending START request to Manager ...

Replicat group REP_R3 starting.

GGSCI (ip-27-45-2-299.us-east) 16> view params REP_R3

REPLICAT REP_R3
SETENV (ODBCINI="/u01/app/oracle/product/21.3.0/postgres/odbc.ini" )
targetdb pgdbs, userid oggadmin PASSWORD ******* 
DISCARDFILE /gg01/app/oracle/product/21.3.0/postgres/dirrpt/REP_R3.dsc
MAP PDB.EMP.SALARY,TARGET EMP.SALARY;

GGSCI (ip-27-45-2-299.us-east) 15> info REP_R3

Replicat   REP_R3  Last Started 2025-08-12 05:59   Status RUNNING
Checkpoint Lag       04:37:25 (updated 00:00:03 ago)
Process ID           8836
Log Read Checkpoint  File ./dirdat/er000000000
                     2025-08-12 01:25:40.013362  RBA 16962583

SCRIPTS USED TO DROP OBJECTS

Note : Login to respective user then execute this script

SELECT
'DROP TABLE ' || table_schema || '.' || table_name || ' CASCADE;'
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema');

COUNTS;

SELECT
'SELECT COUNT(*) FROM ' || table_schema || '.' || table_name || ';'
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema');

SCRIPT FOR TRIGGERS;

CREATE OR REPLACE FUNCTION set_replica_identity_on_create()
RETURNS event_trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT
            objid::regclass AS full_table_name,
            n.nspname AS schema_name,
            c.relname AS table_name
        FROM pg_event_trigger_ddl_commands() AS e
        JOIN pg_class c ON c.oid = e.objid
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE e.command_tag = 'CREATE TABLE'
          AND n.nspname = 'entitystore'
          AND c.relkind IN ('r', 'p') -- regular and partitioned tables
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(r.schema_name) || '.' || quote_ident(r.table_name) || ' REPLICA IDENTITY FULL';
        RAISE NOTICE 'Set REPLICA IDENTITY FULL for table: %.%', r.schema_name, r.table_name;
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER set_replica_identity_on_create

ON ddl_command_end

WHEN TAG IN ('CREATE TABLE')

EXECUTE FUNCTION set_replica_identity_on_create();

CREATE OR REPLACE FUNCTION set_replica_identity_on_alter()
RETURNS event_trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT
            n.nspname AS schema_name,
            c.relname AS table_name,
            c.relreplident
        FROM pg_event_trigger_ddl_commands() e
        JOIN pg_class c ON c.oid = e.objid
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE e.command_tag = 'ALTER TABLE'
          AND c.relkind IN ('r', 'p')
          AND n.nspname = 'entitystore'
          AND c.relreplident != 'f'  -- Skip if already FULL
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(r.schema_name) || '.' || quote_ident(r.table_name) || ' REPLICA IDENTITY FULL';
        RAISE NOTICE 'Set REPLICA IDENTITY FULL for table: %.%', r.schema_name, r.table_name;
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER set_replica_identity_on_alter
    ON ddl_command_end
    WHEN TAG IN ('ALTER TABLE')
    EXECUTE FUNCTION set_replica_identity_on_alter();

SELECT
    evtname AS trigger_name,
    evtevent AS event,
    evtowner AS owner_id,
    evtfoid::regprocedure AS function_name,
    evtenabled AS enabled_status
FROM pg_event_trigger;