Replicat lag not coming down

DBA112

Dear OGG experts,

Source DB version : 11.2.0.4 (OGG version - 18.1)

Target DB version : 19.8 (OGG version - 19.1)

The replicat lag is never coming down. It is in "RUNNING" status all the time and progressing, but never fully catching up the volume.

Can you please suggest what's causing the LAG and how this may be resolved ?

replicat parameter file below:

========================

replicat RSTOT211

DiscardFile ./dirrpt/RSTOT211.dsc, Purge, megabytes 1024

-- TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1536)

useridalias WSTP0

include ./dirmac/ST_wST_ot2_maclib.mac

--assumetargetdefs

-- DYNAMICRESOLUTION

-- REPERROR (01403, DISCARD)

-- 00001 unique constraint voilated

-- 01403 no data found

-- REPERROR (-1, DISCARD)

-- REPERROR (0001, DISCARD)

-- REPERROR (-1403, DISCARD)

-- REPERROR (01403, DISCARD)

--- These 3 parameters added to improve replicat performance

DBOPTIONS INTEGRATEDPARAMS(EAGER_SIZE 100000)

-- DBOPTIONS INTEGRATEDPARAMS(batcSTql_mode sequential)

DBOPTIONS INTEGRATEDPARAMS (max_sga_size 2048, parallelism 4)

-- Use BATCHESPERQUEUE in warehouse type of loads

-- BATCSTQL BATCHESPERQUEUE 1000

BATCSTQL BATCHESPERQUEUE 1000,BATCHTRANSOPS 100000, OPSPERBATCH 100000,OPSPERQUEUE 5000

Map ODS.O_IE_SHIPS, Target SEA_CDC.O_IE_SHIPS, #mac_cols24();

ORASCN

Hello DBA112,

Please provide the below,

OS> ./ggsci

GGSCI> dblogin userid <username>, password *****

GGSCI> info replicat <rep_name> detail

GGSCI> send replicat <rep_name>, status

Report file of the replicat process.

Regards,

Veera

DBA112

info replicat RSTOT211 detail

REPLICAT  RSTOT211 Last Started 2020-12-09 19:17  Status RUNNING

INTEGRATED

Checkpoint Lag    65:41:14 (updated 00:00:00 ago)

Process ID      34800052

Log Read Checkpoint File /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000005898

           2020-12-07 15:14:49.017624 RBA 43207873

INTEGRATED Replicat

DBLOGIN Provided, inbound server name is OGG$RSTOT211 in ATTACHED state

Current Log BSN value: (no data)

Low Watermark CSN value: 7834368579355

(All source transactions prior to this scn have been applied)

High Watermark CSN value: 7834368579355

(Some source transactions between this scn and the low watermark may have been applied)

 Extract Source             Begin       End

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000005898 2020-12-07 04:56 2020-12-07 15:14

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000005856 2020-12-07 04:55 2020-12-07 04:56

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000005856 2020-12-07 04:51 2020-12-07 04:55

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000005856 2020-12-07 04:49 2020-12-07 04:51

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000005856 2020-12-07 04:47 2020-12-07 04:49

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000005856 2020-12-07 04:39 2020-12-07 04:47

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000005855 2020-12-06 23:37 2020-12-07 04:39

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000005844 2020-12-03 04:05 2020-12-06 23:37

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000005582 2020-12-03 01:00 2020-12-03 04:05

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000005573 2020-12-01 10:05 2020-12-03 01:00

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000005436 2020-12-01 09:59 2020-12-01 10:05

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000005436 2020-11-21 13:08 2020-12-01 09:59

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000004769 2020-11-19 16:42 2020-11-21 13:08

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000004582 2020-11-19 16:37 2020-11-19 16:42

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000004582 2020-11-09 19:45 2020-11-19 16:37

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000003838 2020-11-06 22:03 2020-11-09 19:45

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000003659 2020-11-06 21:50 2020-11-06 22:03

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000003659 2020-11-06 20:39 2020-11-06 21:50

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000003656 * Initialized *  2020-11-06 20:39

 /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st000003656 * Initialized *  First Record

 ./dirdat/remote/stot2001/st000003656  * Initialized *  First Record

 ./dirdat/remote/stot2001/st000000000  * Initialized *  First Record

Current directory  /app/oracle/GGS/19.1/19.3

Report file     /app/oracle/GGS/19.1/19.3/dirrpt/RSTOT211.rpt

Parameter file    /app/oracle/GGS/19.1/19.3/dirprm/rstot211.prm

Checkpoint file   /app/oracle/GGS/19.1/19.3/dirchk/RstOT211.cpr

Process file

Error log      /app/oracle/GGS/19.1/19.3/ggserr.log

send replicat RstOT211, status

Sending STATUS request to REPLICAT RSTOT211 ...

 Current status: Processing data

 Sequence #: 5,898

 RBA: 48,375,145

 0 records in current transaction.

*******Nothing got written in the replicat report file ************

Pjacob.Ogg-Oracle

Hi,

Please share the replicat report file

ggsci> send < rep name>, report -- now get the last report file for this replicat

Also get the Healthcheck report file

Regards

Jacob Praveen

ORASCN

Hello,

Thanks for providing the requested info.

Please share the below,

ls -lrt  /app/oracle/GGS/19.1/19.3/dirdat/remote/stot2001/st00000*

set lines 200 pages 1000

col event format a35

col module format a30

col username format a15

col value format a30

col apply_name format a25

select sid, serial#, apply#, apply_name, state from gv$gg_apply_server where apply_name='OGG$RSTOT211';

select * from dba_apply_parameters where parameter='MAX_SGA_SIZE' and apply_name='OGG$RSTOT211';

select sid, serial#, apply_name, state from gv$gg_apply_receiver where apply_name='OGG$RSTOT211';

select count(server_id) "Effective Parallelism" from gv$gg_apply_server where apply_name='OGG$RSTOT211' and total_messaages_applied > 0;

Regards,

Veera