5 Replies Latest reply on Apr 9, 2012 1:47 AM by CKPT

    Archive Gap

      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#;
      ---------- ---------- --- -------
               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
      ---------- ---------- --- -------
               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                                                              
      Do I need to copy the 34459 file to Standby and manually register OR any other solution ??
        • 1. Re: Archive Gap
          Nikolay Ivankin
          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

            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
              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

                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.


                This shows you how you can do that.


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

                Edited by: vinay.deshmukh on Apr 8, 2012 4:26 PM
                1 person found this helpful
                • 5. Re: Archive Gap
                  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.
                  1 person found this helpful