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
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
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
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
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.
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
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;
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
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
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.
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
Checkpoint Lag Not Available
Process ID 1437844
Log Read Checkpoint Not Available
First Record Record 0
Task SOURCEISTABLE
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
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.
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.
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
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
'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 COUNT(*) FROM ' || table_schema || '.' || table_name || ';'
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema');
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();
evtname AS trigger_name,
evtevent AS event,
evtowner AS owner_id,
evtfoid::regprocedure AS function_name,
evtenabled AS enabled_status
FROM pg_event_trigger;
No comments:
Post a Comment