REPLICAT RUNNING VERY SLOW OR REPLICAT LAGGING
My current status of my Replicat
GGSCI (bhuogg03) 23> info R0000112
REPLICAT R0000112 Last Started 2015-04-10 07:17 Status RUNNING
Checkpoint Lag 48:58:28 (updated 58:56:56 ago)
Log Read Checkpoint File /u01/oracle/gg/prod-bhu/dirdat/ST/tr000629
2015-04-05 19:22:09.005598 RBA 91206293
You can see what is happened from the status of the Replicat using the below command
GGSCI (bhuogg03) 24> stats R0000112
To check what is the current progressing in the GG REPLICAT
GGSCI> STATS R0000112 RESET
GGSCI> STATS R0000112 LATEST
n LATEST option will give the latest activity which are currently happening;
n We can see the progress and how quick it is.
WHAT/HOW TO CHECK
1) We need to identify what kind of query is running; I will check the DB session which are part of GG and it is in active. We can check the table_name from gv$session by identify the sql_id
2) You can login into the OEM or SQLDEVELOPER to identify the query easily.
3) I will check for the index on the table and whether those columns are part of the DML or DR queries
4) We need to look for the plan; if there is a FTS then create an index.
Before creating the index, you need to stop the Replicate, else index cant be created; you can try with the below option
alter session set ddl_lock_timeout=100000;
After the index is created; start the replicat and try to monitor the progress of the replicat. It should move the process quickly; if it is not moving quickly you have start analyzing again.
n Noteè we have to restart the replicate; else index won’t be part of the plan.
n If you have heavy usage on the source side, then you should split the Replicat using the RANGE option;
n You can use the RANGE option only for the Tables;