14 Replies Latest reply: Jan 4, 2014 6:08 AM by Shivananda Rao RSS

    MRP0 stuck at log 4054

      Hi All,

       

      HAPPY NEW YEAR TO ALL!!! Hope you had peace and joy this 2014

       

      My first post for 2014.

       

      Returning back for work, checked my standby for gaps. And out of 6 Prod databases, I always have this one (1) which often encounters gap and MRP0 not running.

      I really do not know why this only the database that is getting its mMRP0 dead. So I run this command >

      ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

      Then MRP0 was alive and start applying logs (started at 4031), I noticed that it smoothly applying until 4054.

       

      But its been long time like 1 hr that it is still at 4054 What shall I do? Thanks. pK

       

      SQL> select PROCESS, STATUS from v$managed_standby where PROCESS ='MRP0';

       

       

      PROCESS   STATUS

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

      MRP0      APPLYING_LOG

       

       

      SQL> @gap.sql

       

      DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP

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

      PROD1       LOCALHOST              4062        4054 01-JAN/19:33         8

       

       

      SQL> l

        1  SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP

        2     FROM( SELECT NAME DB_NAME FROM V$DATABASE),

        3  (SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME), (INSTR(HOST_NAME,'.')-1))))) HOSTNAME

        4     FROM V$INSTANCE),(SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),

        5  (SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'),

        6* (SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES')

      SQL> /

       

       

      DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP

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

      PROD1       LOCALHOST              4062        4054 01-JAN/19:33         8

       

       

      SQL> SET TIME ON

      09:24:48 SQL> select PROCESS, STATUS from v$managed_standby where PROCESS ='MRP0';

       

       

      PROCESS   STATUS

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

      MRP0      APPLYING_LOG

       

       

      09:24:50 SQL>

        • 1. Re: MRP0 stuck at log 4054
          mseberg

          Hello;

           

          What version of Oracle?

           

          How many ARCH processes do you show?

           

          What is LOG_ARCHIVE_MAX_PROCESSES set to?


          Your LOCALHOST for hostname is very odd. Are you not using DNS?


          Did the logs transport, but not apply only?

           

          I would check note 1221163.1 ( How to resolve MRP stuck issues on a physical standby database? [ID 1221163.1] )

           

          SQL> select PROCESS, STATUS from v$managed_standby;

           

          PROCESS   STATUS

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

          ARCH      CLOSING

          ARCH      CONNECTED

          ARCH      CLOSING

          ARCH      CLOSING

          ARCH      CLOSING

          ARCH      CLOSING

          ARCH      CLOSING

          ARCH      CLOSING

          MRP0      APPLYING_LOG

           

          Best Regards

           

          mseberg

          • 2. Re: MRP0 stuck at log 4054

            Hi Mse,

             

            What version of Oracle? 11.2.0.1

             

            How many ARCH processes do you show? I see 9 rows

             

            SQL> select PROCESS, STATUS from v$managed_standby;

             

            PROCESS   STATUS

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

            ARCH      CLOSING

            ARCH      CONNECTED

            ARCH      CLOSING

            ARCH      CLOSING

            RFS       IDLE

            RFS       IDLE

            RFS       IDLE

            RFS       IDLE

            MRP0      APPLYING_LOG

             

            9 rows selected.

             

            What is LOG_ARCHIVE_MAX_PROCESSES set to?


            SQL> show parameter LOG_ARCHIVE_MAX_PROCESSES

             

            NAME                                 TYPE        VALUE

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

            log_archive_max_processes            integer     4

             

             

            Your LOCALHOST for hostname is very odd. Are you not using DNS? It has been like this for a year now with no MAJOR issues, and all other (3) databases residing on this server also uses "localhost" is applying logs good.


            Did the logs transport, but not apply only?  Yes I can see the archive log 4054, 4055, etc.... at the standby server.

             

            I would check note 1221163.1 ( How to resolve MRP stuck issues on a physical standby database? [ID 1221163.1] )

             

            Okay checking now.....

            • 3. Re: MRP0 stuck at log 4054

              Hi again Mse,

               

              I am confused about this script to check for log gaps:

               

              SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP

                 FROM( SELECT NAME DB_NAME FROM V$DATABASE),

              (SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME), (INSTR(HOST_NAME,'.')-1))))) HOSTNAME

                 FROM V$INSTANCE),(SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),

              (SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'),

              (SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');



              Is this script correct?


              Bacause when I run this on the other database with MRP0 running okay, I got this output:


              SQL> @gap.sql

               

              DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP

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

              SMSDB      LOCALHOST              1076         597 19-SEP/14:15       479

               

              Shocking! 479 gaps?

               

              But if I run > 

               

              SQL> select process,status,sequence# from v$managed_standby;

               

              PROCESS   STATUS        SEQUENCE#

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

              ARCH      CLOSING            1076

              ARCH      CLOSING            1074

              ARCH      CONNECTED             0

              ARCH      CLOSING            1075

              MRP0      WAIT_FOR_LOG       1077

              RFS       IDLE                  0

              RFS       IDLE               1077

               

              7 rows selected.

               

               

              Which database view is questionable now?

               

              Please help...

               

              Thanks,

              pK

              • 4. Re: MRP0 stuck at log 4054

                SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP

                   FROM( SELECT NAME DB_NAME FROM V$DATABASE),

                (SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME), (INSTR(HOST_NAME,'.')-1))))) HOSTNAME

                   FROM V$INSTANCE),(SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),

                (SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'),

                (SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');


                 

                Opps I run the script at STANDBY, it should be at PRIMARY

                • 5. Re: MRP0 stuck at log 4054
                  CKPT

                  Simply you can run below query from standby to check archive log gaps.

                   

                   

                  SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

                  FROM

                  (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

                  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

                  WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

                  • 6. Re: MRP0 stuck at log 4054
                    Shivananda Rao

                    On Primary:

                     

                    Select thread#,max(sequence#) from v$archived_log group by thread#;

                     

                    On standby:

                     

                    select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

                     

                    If your issue is resolved, please consider closing the thread by marking it as answered.

                     

                     

                    Regards,

                    Shivananda

                    • 7. Re: MRP0 stuck at log 4054

                      Thanks Shiv, Chpt,

                       

                      Which server gap-check script should I run  to give most reliable output? is at PRIMARY or STANDBY?

                      • 8. Re: MRP0 stuck at log 4054
                        CKPT

                        I've already mentioned to run on standby, hope you missed reading it?

                        • 9. Re: MRP0 stuck at log 4054

                          Hi All,

                           

                          My question in why is that my standby MPR0 status is always "APPLYING_LOG" now, even if log is still being written at the PRIMARY? When before I always see it as "WAITING FOR LOG"?

                           

                          Thanks

                          • 10. Re: MRP0 stuck at log 4054

                            Hi Chkpt,

                             

                            "I've already mentioned to run on standby, hope you missed reading it?

                            Yeah I did read it

                            But my question is which is more reliable result? the script to run on PRIMARY or the script to run on STANDBY?

                             

                            This one (PRIMARY)

                             

                            SQL> SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP

                              2     FROM( SELECT NAME DB_NAME FROM V$DATABASE),

                              3  (SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME), (INSTR(HOST_NAME,'.')-1))))) HOSTNAME

                              4     FROM V$INSTANCE),(SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),

                              5  (SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'),

                              6  (SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');

                             

                            DB_NAME   HOSTNAME                                                         LOG_ARCHIVED LOG_APPLIED APPLIED_TIME    LOG_GAP

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

                            PROD1     PRD-SVR                                                                  4086        4086 03-JAN/18:39          0

                             

                             

                            or the ones you given me?

                             

                            SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

                              2  FROM

                              3  (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

                              4  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

                            WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

                              5

                                Thread Last Sequence Received Last Sequence Applied Difference

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

                                     1                   4086                  4086          0

                             

                             

                            tHANKS

                            • 11. Re: MRP0 stuck at log 4054
                              CKPT

                              Only standby database.

                              • 12. Re: MRP0 stuck at log 4054
                                Shivananda Rao

                                Hello,

                                 

                                You can run this query as well to find out the sequence that has been received and the latest sequence that has been applied. Make sure you run this on the standby database.

                                 

                                select al.thread#, al.last_rec "Last Recd", la.last_app "Last Applied"

                                from

                                (select thread#, max(sequence#) last_rec

                                   from v$archived_log

                                   where resetlogs_id = (select max(resetlogs_id) from v$archived_log)

                                   group by thread#) al,

                                (select thread#, max(sequence#) last_app

                                   from v$archived_log

                                   where resetlogs_id = (select max(resetlogs_id) from v$archived_log)

                                   and applied='YES' and registrar='RFS'

                                   group by thread#) la

                                where al.thread#=la.thread#

                                and   al.thread# != 0

                                order by al.thread#

                                /

                                 

                                 

                                Regards,

                                Shivananda

                                • 13. Re: MRP0 stuck at log 4054

                                  Hi Shi,

                                   

                                  SQL> select al.thread#, al.last_rec "Last Recd", la.last_app "Last Applied"

                                    2  from

                                    3  (select thread#, max(sequence#) last_rec

                                    4     from v$archived_log

                                    5     where resetlogs_id = (select max(resetlogs_id) from v$archived_log)

                                    6     group by thread#) al,

                                    7  (select thread#, max(sequence#) last_app

                                    8     from v$archived_log

                                    9     where resetlogs_id = (select max(resetlogs_id) from v$archived_log)

                                  10     and applied='YES' and registrar='RFS'

                                  11     group by thread#) la

                                  12  where al.thread#=la.thread#

                                  13  and   al.thread# != 0

                                  14  order by al.thread#

                                  15  /

                                   

                                   

                                     THREAD#  Last Recd Last Applied

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

                                           1       4102         4102

                                  • 14. Re: MRP0 stuck at log 4054
                                    Shivananda Rao

                                    In that case, I do not see any gaps between the primary and the standby database.

                                     

                                     

                                    Regards,

                                    Shivananda