Monday, April 13, 2015

REPLICAT RUNNING VERY SLOW OR REPLICAT LAGGING

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;

1 comment:

  1. Thanks Bhuvanesh, very helpful.

    Would like to add steps used for similar issue:
    - Found that there is 2 plan for this query good and bad, and current one is bad plan
    - with help of SQLT (sqltrpt.sql), we fixed this immediately using profile

    ReplyDelete