Oracle Streams apply process is slow On Goldengate integrated replicat.
Usually If your environment having huge transaction (replication setup) definitely your sys.streams$_apply_progress segment also will increase randomly.
So my advice is instead of facing checkpoint lag on replication side ,Periodically we have to check STREAMS$_APPLY_PROGRESS segment size also Need to perform re-org either manual way or else schedule the job using dbms_scheduler.
In my environment every twenty days we used to do re-org using below steps (scheduled). Again after twenty days table will reach approximately 10 GB of size.
There are two type of re-org options is there.
1. OFFLINE
2. ONLINE
In this blog we are going to see offline method using below steps:
First check the size of stream apply table before going to re-org.
SQL> select sum(bytes/1024/1024) from dba_segments where segment_name=’STREAMS$_APPLY_PROGRESS’;
SUM(BYTES/1024/1024)
——————–
12.063225
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
GGSCI (GG-Target.com) 11> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP2 00:01:18 00:00:45
OFFLINE: Stop the replicat process.
GGSCI (GG-Target.com) 12> stop rep2
Sending STOP request to REPLICAT REP2 …
Request processed.
GGSCI (GG-Target.com) 14> info rep2
REPLICAT REP2 Last Started 2019-08-09 21:26 Status STOPPED
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint File ./dirdat/BB000001929
2019-08-09 22:04:42.658077 RBA 54619
GGSCI (GG-Target.com) 15> sh sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 9 22:06:23 2019
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> SELECT COUNT(*) from STREAMS$_APPLY_PROGRESS;
COUNT(*)
———-
3591816
Create temporary table (Backup Table) using original stream apply table.
SQL> Create table stream_dbwr as select * from sys.streams$_apply_progress p where p.commit_scn >= (select m.commit_scn from streams$_apply_milestone m where m.apply# = p.apply#);
Table created.
Compare the steam table (original) and temporary table count.
SQL> SELECT COUNT(*) from stream_dbwr;
COUNT(*)
———-
3591816
Then truncate the stream table.
SQL> Truncate table sys.streams$_apply_progress;
Table truncated.
SQL> ALTER TABLE STREAMS$_APPLY_PROGRESS INITRANS 8 PCTFREE 10;
Table altered.
Again insert the data from temorary table to main table.
SQL> Insert into sys.streams$_apply_progress select * from stream_dbwr;
3591816 rows created.
Check the count of main stream apply table.
SQL> SELECT COUNT(*) from STREAMS$_APPLY_PROGRESS;
COUNT(*)
———-
3591816
Then enable the row movement for stream table
SQL> alter table sys.streams$_apply_progress enable row movement;
Table altered.
And shrink the space.
SQL> alter table sys.streams$_apply_progress shrink space;
Table altered.
Then disable the row movement.
SQL> alter table sys.streams$_apply_progress disable row movement;
Table altered.
Check the size of stream apply table size.
SQL> select sum(bytes/1024/1024) from dba_segments where segment_name=’STREAMS$_APPLY_PROGRESS’;
SUM(BYTES/1024/1024/1024)
——————–
.0723625
Finally start your replication definitely your replicat process achieve more performance compare to past.
GGSCI (GG-Target.com) 16> start rep2
Sending START request to MANAGER …
REPLICAT REP2 starting
GGSCI (GG-Target.com) 17> info rep2
REPLICAT REP2 Last Started 2019-08-09 22:22 Status RUNNING
INTEGRATED
Checkpoint Lag 00:17:31 (updated 00:00:00 ago)
Process ID 4984
Log Read Checkpoint File ./dirdat/BB000001929
2019-08-09 22:04:42.658077 RBA 54619
