For reporting we have used the Oracle golden gate for replication.
Golden gate is not executing the SQLEXEC for update but insert is working as expected.
Data is transferred from active to reporting using the golden gate for one specific table called MINF. Process flow of operation is as given below.
Active OGG Insert data in MINF_STAB then with INSERTALLRECORDS and SQLEXEC
schema -----------------> Data is inserted into MINF table.
Minf Table
To insert data in MINF@reporting procedure SPG_HANDLE_EXTRACT is handled. For any new row is added in MINF@ACTIVE same would be added in MINF_STAB@REPORTING and SPG_HANDLE_EXTRACT handle the insert in MINF@REPORTING.
For update also MINF_STAB@REPORTING is directy updated using the golden gate and for updating MINF@REPORTING the SPG_HANDLE_EXTRACT is triggered by golden gate. For update SPG_HANDLE_EXTRACT execute other procedures which converts update into insert using MINF_STAB data and insert the same.
Row count in table MINF@ACTIVE and MINF_STAB@REPORTING MINF@REPORTING Same.
Count is being maintained there is not issue. But data content in MINF@reporting is not same as it is expected.
Or we can say data is not getting progressed and we suspect SQLEXEC is not working for update.
For MINF table replication we have one extract EXT_MINF , one Datapump PMP_MINF and two replicat REP_MINF1 ad REP_MINF2
MAP GPP.MINF, TARGET REPGPP.MINF_STAB, FILTER (@RANGE (2 , 2, P_MID)), KEYCOLS ( p_mid );
INSERTALLRECORDS
MAP GPP.MINF, TARGET REPGPP.MID, FILTER (@RANGE (2 , 2, P_MID)), SQLEXEC ( SPNAME REPGPP.SPG_HANDLE_EXTRACT , ID sp1 , PARAMS ( p_in_mid = p_mid , p_in_msg_type = p_msg_type , p_in_version = p_version , p_in_orig_msg_type = p_orig_msg_type , p_in_orig_version = p_orig_version , p_delete = @GETENV ('GGHEADER', 'OPTYPE') ) , EXEC SOURCEROW ), COLMAP ( p_mid = p_mid ), KEYCOLS ( p_mid );
Version details are as given below.
Oracle RDBMS Version: 12.1.0.2 patched to 12.1.0.2.161018,
Oracle Golden Gate: 12.2.0.0.0 not patched.