This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Oct 14, 2013 11:27 AM by Anar Godjaev RSS

Relpication very slow

1006937 Newbie
Currently Being Moderated

hi,

 

11.2.0.2

I have Logical Data Guard which is very slow

select * from v$logstdby

 

383-132011COORDINATOR16116ORA-16116: no work available511796345570
6325032557ANALYZER16116ORA-16116: no work available511796328343
6779132559APPLIER16121ORA-16121: applying transaction with commit SCN 0x0077.297035e2511796327906
6275232561APPLIER16116ORA-16116: no work available
467332563APPLIER16116ORA-16116: no work available
4725432565APPLIER16116ORA-16116: no work available
251532567APPLIER16116ORA-16116: no work available
3117632569APPLIER16116ORA-16116: no work available
3169732571APPLIER16116ORA-16116: no work available
475832573APPLIER16116ORA-16116: no work available
399102432015READER16127ORA-16127: stalled waiting for additional transactions to be applied511796345530
1123102532017BUILDER16127ORA-16127: stalled waiting for additional transactions to be applied511796345530
605102632019PREPARER16127ORA-16127: stalled waiting for additional transactions to be applied511796328342
2865102732021PREPARER16127ORA-16127: stalled waiting for additional transactions to be applied511796328338
631102832023PREPARER16127ORA-16127: stalled waiting for additional transactions to be applied511796328338
5083102932025PREPARER16127ORA-16127: stalled waiting for additional transactions to be applied511796328343

 

applying transaction with commit SCN 0x0077.297035e2 is very very very slow

 

What could be the problem ?

Thanks in advance

 


  • 1. Re: Relpication very slow
    MahirM.Quluzade Guru
    Currently Being Moderated

    Hi,

     

    Can you  check did you have GAP on logical  standby database?

     

    Can you paste following script results to  here?

     

    On Primary:

    select unique thread#, max(Sequence#) over (partition by thread#) as maxseq from  v$archived_log;

     

    On Standby

    select unique thread#, max(Sequence#) over (partition by thread#) as maxseq from  dba_logstdby_log;

    select unique thread#, max(Sequence#) over (partition by thread#) as maxseq from  dba_logstdby_log where applied='YES';

     

    And what is the size of redo log files?

     

    Regards

    Mahir M. Quluzade

  • 2. Re: Relpication very slow
    1006937 Newbie
    Currently Being Moderated

    Thanks Mahir

     

    Primary:

    select unique thread#, max(Sequence#) over (partition by thread#) as maxseq from  v$archived_log;

       THREAD#     MAXSEQ

    ---------- ----------

             1      20617

    1 row selected.

     

     

    Standby:

    select unique thread#, max(Sequence#) over (partition by thread#) as maxseq from  dba_logstdby_log;

    select unique thread#, max(Sequence#) over (partition by thread#) as maxseq from  dba_logstdby_log where applied='YES';

       THREAD#     MAXSEQ

    ---------- ----------

             1      20617

    1 row selected.

    no rows selected.

  • 3. Re: Relpication very slow
    MahirM.Quluzade Guru
    Currently Being Moderated

    Did you sure LOG APPLY (LSP) process running? I think apply process is not running.

     

    select process from v$managed_standby;

     

    Try Stop/Start  Apply process :

     

    SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

     

    SQL>  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

     

    If you get any error, paste here please.

     

    Mahir

  • 4. Re: Relpication very slow
    1006937 Newbie
    Currently Being Moderated

    Thanks Mahir,

     

    stop/start already done ...

    select process from v$managed_standby;

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    ARCH

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

    RFS

  • 5. Re: Relpication very slow
    MahirM.Quluzade Guru
    Currently Being Moderated

    Can you paste here last 50 sentence of Alert log of standby database?

     

    and result of is this changed?

     

    SELECT TYPE,STATUS FROM V$LOGSTDBY;


    And what is state of Coordinator.


    select name,value from v$logstdby_stats where name = 'coordinator state';


  • 6. Re: Relpication very slow
    1006937 Newbie
    Currently Being Moderated

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

    Mon Oct 14 09:06:39 2013

    Thread 1 advanced to log sequence 21271 (LGWR switch)

      Current log# 14 seq# 21271 mem# 0: /u02/oradata/SHDSD/onlinelog/o1_mf_14_1TlFVchpz_.log

      Current log# 14 seq# 21271 mem# 1: /u03/oradata/SHDSD/onlinelog/o1_mf_14_1TlFVn_BV_.log

    Mon Oct 14 09:06:39 2013

    Archived Log entry 21270 added for thread 1 sequence 21270 ID 0x9cd5ffe3 dest 1:

    Mon Oct 14 09:15:43 2013

    Thread 1 advanced to log sequence 21272 (LGWR switch)

      Current log# 15 seq# 21272 mem# 0: /u02/oradata/SHDSD/onlinelog/o1_mf_15_1TlFWKIjx_.log

      Current log# 15 seq# 21272 mem# 1: /u03/oradata/SHDSD/onlinelog/o1_mf_15_1TlFWSDNT_.log

    Mon Oct 14 09:15:49 2013

    Archived Log entry 21271 added for thread 1 sequence 21271 ID 0x9cd5ffe3 dest 1:

    Mon Oct 14 09:30:16 2013

    RFS[225]: Selected log 21 for thread 1 sequence 20618 dbid 860188243 branch 802460371

    Mon Oct 14 09:30:18 2013

    RFS LogMiner: Registered logfile [/u05/oradata/SHDSD/flash_recovery_area/SHDSD/foreign_archivelog/SODSD/2013_10_14/o1_mf_1_20617_1YJvkpxG7_.arc] to LogMiner session id [1]

    Mon Oct 14 09:45:39 2013

    Thread 1 advanced to log sequence 21273 (LGWR switch)

      Current log# 16 seq# 21273 mem# 0: /u02/oradata/SHDSD/onlinelog/o1_mf_16_1TlFX0CO9_.log

      Current log# 16 seq# 21273 mem# 1: /u03/oradata/SHDSD/onlinelog/o1_mf_16_1TlFXEZJT_.log

    Mon Oct 14 09:45:40 2013

    Archived Log entry 21272 added for thread 1 sequence 21272 ID 0x9cd5ffe3 dest 1:

    Mon Oct 14 10:00:16 2013

    RFS[225]: Selected log 22 for thread 1 sequence 20619 dbid 860188243 branch 802460371

    Mon Oct 14 10:00:17 2013

    RFS LogMiner: Registered logfile [/u05/oradata/SHDSD/flash_recovery_area/SHDSD/foreign_archivelog/SODSD/2013_10_14/o1_mf_1_20618_1YJxQ4pcp_.arc] to LogMiner session id [1]

    Mon Oct 14 10:15:38 2013

    Thread 1 advanced to log sequence 21274 (LGWR switch)

      Current log# 14 seq# 21274 mem# 0: /u02/oradata/SHDSD/onlinelog/o1_mf_14_1TlFVchpz_.log

      Current log# 14 seq# 21274 mem# 1: /u03/oradata/SHDSD/onlinelog/o1_mf_14_1TlFVn_BV_.log

    Mon Oct 14 10:15:38 2013

    Archived Log entry 21273 added for thread 1 sequence 21273 ID 0x9cd5ffe3 dest 1:

    Mon Oct 14 10:30:16 2013

    RFS[225]: Selected log 21 for thread 1 sequence 20620 dbid 860188243 branch 802460371

    Mon Oct 14 10:30:16 2013

    RFS LogMiner: Registered logfile [/u05/oradata/SHDSD/flash_recovery_area/SHDSD/foreign_archivelog/SODSD/2013_10_14/o1_mf_1_20619_1YJz5NHpu_.arc] to LogMiner session id [1]

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

    COORDINATORORA-16116: no work available
    ANALYZERORA-16116: no work available
    APPLIERORA-16121: applying transaction with commit SCN 0x0077.29703b93
    APPLIERORA-16116: no work available
    APPLIERORA-16116: no work available
    APPLIERORA-16116: no work available
    APPLIERORA-16116: no work available
    APPLIERORA-16116: no work available
    APPLIERORA-16116: no work available
    APPLIERORA-16116: no work available
    READERORA-16127: stalled waiting for additional transactions to be applied
    BUILDERORA-16127: stalled waiting for additional transactions to be applied
    PREPARERORA-16127: stalled waiting for additional transactions to be applied
    PREPARERORA-16127: stalled waiting for additional transactions to be applied
    PREPARERORA-16127: stalled waiting for additional transactions to be applied
    PREPARERORA-16127: stalled waiting for additional transactions to be applied

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

    select name,value from v$logstdby_stats where name = 'coordinator state';

    coordinator stateAPPLYING
  • 7. Re: Relpication very slow
    Anar Godjaev Expert
    Currently Being Moderated

    HI

     

    ORA-16116: no work available

    The process is idle waiting for additional changes to be made   available.  No action necessary, this informational statement is provided to  record the event for diagnostic purposes.  (Oracle support id Doc ID 172848.1)

     

    ORA-16127: stalled waiting for additional transactions to be applied


    This process is waiting for additional memory before continuing. Additional log information cannot be read into memory until more   transactions have been applied to the database, thus freeing up   additional memory.

    No action necessary, this informational statement is provided to record the event for diagnostic purposes. If this message occurs often and changes are not being applied quickly, increase available SGA or the number of apply processes. (Oracle support id Doc Doc ID 194467.1)

     

    Thank ypu


  • 8. Re: Relpication very slow
    MahirM.Quluzade Guru
    Currently Being Moderated

    coordinator state APPLYING

     

    Apply process started and running:

    You  can check how many logs applied :

     

    select unique thread#, max(Sequence#) over (partition by thread#) as maxseq from  dba_logstdby_log where applied='YES';

     

    I think you must now see Applied sequences is increased. Before your result of this script was no row selected.

    Please wait apply of all  redo logs.

     

    Regards

    Mahir M. Quluzade

  • 9. Re: Relpication very slow
    Anar Godjaev Expert
    Currently Being Moderated

    Hi Mr Mahir and Mr.1006937

     

    The only way to solve this problem is consider the post written above. Because I also faced such problem previously and solved this way.


    Thank you

  • 10. Re: Relpication very slow
    MahirM.Quluzade Guru
    Currently Being Moderated

    Hi Anar!

     

    You means "Increase available SGA or the number of apply processes"  or other solution?

    May be this help for solution of this problem. They  are support notes, which is you are shared.

    There haven't any error or warning in alert log or I didn't saw.

     

    Thanks for sharing.

     

    Regards

    Mahir M. Quluzade

  • 11. Re: Relpication very slow
    1006937 Newbie
    Currently Being Moderated

    Hi AnarGodjaev,

    sorry but what is your solution ?

    Best regards

  • 12. Re: Relpication very slow
    Anar Godjaev Expert
    Currently Being Moderated

    Hi,

     

    ORA-16116: no work available

    The process is idle waiting for additional changes to be made   available.  No action necessary, this informational statement is provided to  record the event for diagnostic purposes.  (Oracle support id Doc ID 172848.1)

     

    ORA-16127: stalled waiting for additional transactions to be applied


    This process is waiting for additional memory before continuing. Additional log information cannot be read into memory until more   transactions have been applied to the database, thus freeing up   additional memory.

    No action necessary, this informational statement is provided to record the event for diagnostic purposes. If this message occurs often and changes are not being applied quickly, increase available SGA or the number of apply processes. (Oracle support id Doc Doc ID 194467.1)

     

    Thank you

  • 13. Re: Relpication very slow
    Anar Godjaev Expert
    Currently Being Moderated

    Hi Mahir !

     

    Thank you response. Yes, increase available SGA or the number of apply processes. "This will solve the problem"

     

    Thank  you

  • 14. Re: Relpication very slow
    1006937 Newbie
    Currently Being Moderated

    Hi,

    I increased appliers to 20, SGA to 700MB

    but still very very slow ...

     

    391-128082COORDINATOR16116ORA-16116: no work available511796345570
    423028449ANALYZER16116ORA-16116: no work available511796311683
    1163128451APPLIER16121ORA-16121: applying transaction with commit SCN 0x0077.296ff3bc511796310972
    611228453APPLIER16116ORA-16116: no work available
    361328455APPLIER16116ORA-16116: no work available
    943428457APPLIER16116ORA-16116: no work available
    313528459APPLIER16116ORA-16116: no work available
    331628461APPLIER16116ORA-16116: no work available
    2869728463APPLIER16116ORA-16116: no work available
    635828465APPLIER16116ORA-16116: no work available
    6807931044APPLIER16116ORA-16116: no work available
    26031031046APPLIER16116ORA-16116: no work available
    24791131048APPLIER16116ORA-16116: no work available
    5111231050APPLIER16116ORA-16116: no work available
    29351331052APPLIER16116ORA-16116: no work available
    1031431054APPLIER16116ORA-16116: no work available
    31191531056APPLIER16116ORA-16116: no work available
    34071631058APPLIER16116ORA-16116: no work available
    4771731060APPLIER16116ORA-16116: no work available
    23651831062APPLIER16116ORA-16116: no work available
    4891931064APPLIER16116ORA-16116: no work available
    48492031066APPLIER16116ORA-16116: no work available
    7697102428084READER16127ORA-16127: stalled waiting for additional transactions to be applied511796345530
    5417102528086BUILDER16127ORA-16127: stalled waiting for additional transactions to be applied511796345530
    6281102628088PREPARER16127ORA-16127: stalled waiting for additional transactions to be applied511796311681
    471102728090PREPARER16127ORA-16127: stalled waiting for additional transactions to be applied511796311683
    4729102828092PREPARER16127ORA-16127: stalled waiting for additional transactions to be applied511796311683
    255102928094PREPARER16127ORA-16127: stalled waiting for additional transactions to be applied511796311683
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points