This discussion is archived
5 Replies Latest reply: Apr 8, 2012 6:47 PM by CKPT RSS

Archive Gap

788793 Newbie
Currently Being Moderated
Hi All,

Using Oracle DG 10Gr2 ON RHEL5. We have a slow network (2mbps) shared line. We see frequent gaps but it catches up within 1/2 hrs.

Recently after some huge transactions on the production the Gap went high and not catching up. The MRP is waiting on 34459 for 4 hrs. Have checked the file sizes in production and standby. Its same.
ARCH    CONNECTED    N/A                                            0         0 
ARCH    CLOSING      4                                              1     33068 
ARCH    CONNECTED    N/A                                            0         0 
MRP0    WAIT_FOR_GAP N/A                                            1     34459 
RFS     IDLE         N/A                                            1     34467 
RFS     IDLE         N/A                                            1     34463 

Standby>> $ ls 1_3445* -lth
-rw-r----- 1 oracle oinstall 146M Apr  4 16:31 1_34459_697785162.dbf

Production>>$ ls 1_3445* -lth
-rw-r----- 1 oracle oinstall 146M Apr  4 09:46 1_34459_697785162.dbf

SQL> select thread#,sequence#,applied,registrar from v$archived_log where (registrar = 'RFS' and applied = 'NO')and sequence# > 34458 order by sequence#;

   THREAD#  SEQUENCE# APP REGISTR
---------- ---------- --- -------
         1      34459 NO  RFS
         1      34460 NO  RFS
         1      34461 NO  RFS
         1      34462 NO  RFS
         1      34463 NO  RFS
         1      34464 NO  RFS
         1      34465 NO  RFS
         1      34466 NO  RFS
         1      34467 NO  RFS
         1      34468 NO  RFS
         1      34470 NO  RFS

   THREAD#  SEQUENCE# APP REGISTR
---------- ---------- --- -------
         1      34472 NO  RFS
         1      34474 NO  RFS
         1      34475 NO  RFS
         1      34476 NO  RFS
         1      34477 NO  RFS
         1      34478 NO  RFS
         1      34480 NO  RFS

The latest archive in Production is;


Latest Archive Log                                                              
------------------                                                              
             34480    
Do I need to copy the 34459 file to Standby and manually register OR any other solution ??
  • 1. Re: Archive Gap
    NikolayIvankin Expert
    Currently Being Moderated
    Check alertlog at standby for errors.
    Check FAL configuration.
    Query V$ARCHIVE_GAP for full gap list.

    If it waits for only one - then copy it and register, this is the fastest way.
  • 2. Re: Archive Gap
    CKPT Guru
    Currently Being Moderated
    Hi,

    Do one thing, Stop the MRP & start Again MRP,
    Check the information from Alert log file and post here. Need to identify whether this log file is corrupted? Looks owner permissions are fine.

    Or copy again from Primary & register it manually once this only archive applied then start MRP as usual.
  • 3. Re: Archive Gap
    788793 Newbie
    Currently Being Moderated
    Strange the gap has reduced. And it's applying again.

    But what was it that it the MRP was waiting so long.?? How to check that ?

    How to boost up/tune the log apply process ?
  • 4. Re: Archive Gap
    757008 Explorer
    Currently Being Moderated
    Hello,

    Use ls -ltr "filename" as this gives in bytes or use checksum to comapare the size of the files .
    A file size haveing same mb does not mean that there is no corruption, it usually will be in bytes hence check the size in bytes or kb best command would be checksum. Syntax:-> cksum file


    In Your case There might the archive file was corrupted while transfering through the sites.... mrp can hang on such cases. Next time insted of waiting for such a long time you can just cancel the MRP proccess in standby, delete the archive log physically ship it manually using (ftp or any other method) regester manually and start the MRP process { if data broker is confiured, there is no need to ship the log manually once it is deleted in standby site , databroker will ship it once the mrp is enabled}

    In case the archive gap is more you can always go for roll forward method for the standby database.( this will be lot faster than applying logs for a gap of such huge number.

    http://www.idevelopment.info/data/Oracle/DBA_tips/Data_Guard/DG_53.shtml

    This shows you how you can do that.

    Regards.

    Edited by: vinay.deshmukh on Apr 8, 2012 4:23 PM

    Edited by: vinay.deshmukh on Apr 8, 2012 4:26 PM
  • 5. Re: Archive Gap
    CKPT Guru
    Currently Being Moderated
    Now it's applying, fine...

    If you want to know the root cause for delay in applying archives you have to review your primary & standby alert log file, monthly primary..

    Issues may be with network problem/bandwidth?
    Issue may be with any TNS , some times services are not registered properly?

    Also go through view v$dataguard_status,

    SQL> select severity, message,error_code,timestamp from v$dataguard_status where dest_id=2;

    Here compare with timestamp when you have seen isse and also check value of parameter LOG_ARCHIVE_MAX_PROCESSES increase this value so multiple processes will work.

Legend

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