6 Replies Latest reply: Aug 12, 2013 3:50 AM by rontrinidadcabral RSS

    Standby media recovery waiting for inactive thread

    rontrinidadcabral

      Hi,

       

      Please let me know any idea on this scenario. Thanks.

       

      Environment:

      Oracle 11.2.0.2

      primary: 3 node RAC

      standby: 3 node RAC

       

      Problem:

      there is thread 5 (not registered instance or did not show in srvctl) that generates archivelog. but the lag apply stopped because of this when the instance (of thread 5) is shutdown.

       

      question: somehow an instance is registered in the cluster but in srvctl only 3 instance is running. it should have 4 instance but 1 is not running. How can I remove the thread 5 so when someone startup then shutdown instance#4 it will not create archivelog that will stopped the apply of archivelog in standby.

       

      note: this is perf environment server so someone and "other" DBA is accessing this environment which I am not aware what are they doing with the cluster.

       

      looking in alert log file: - it is waiting for thread 5 sequence 510. But the instance is down so log is not shipped to standby database and this resulted to lag in other threads.

       

       

      Sat Aug 03 18:54:47 2013

      Media Recovery Log +FLASH/dgjmspl/archivelog/2013_08_01/thread_1_seq_13718.1544.822333555

      Media Recovery Log +FLASH/dgjmspl/archivelog/2013_08_01/thread_2_seq_17665.22678.822315375

      Media Recovery Log +FLASH/dgjmspl/archivelog/2013_08_01/thread_3_seq_15465.14138.822313997

      Media Recovery Waiting for thread 5 sequence 510

       

         THREAD# LAST_SEQ_RECEIVED LAST_SEQ_APPLIED

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

               1             13745            13717

               2             17728            17664

               3             15527            15464

               5               509              509

       

      what I did is:

      1. primary (asm copy to file system)

      2. scp primary to standby

      3. standby (file system copy to asm)

      4. rman target / -> catalog archivelog <thread 5 sequence 510)

      5. then looking into alert log file; it performed media recovery

       

      Sat Aug 03 23:03:13 2013

      Media Recovery Log +FLASH/dgjmspl/archivelog/2013_08_01/thread_1_seq_13718.1544.822333555

      Media Recovery Log +FLASH/dgjmspl/archivelog/2013_08_01/thread_2_seq_17665.22678.822315375

      Media Recovery Log +FLASH/dgjmspl/archivelog/2013_08_01/thread_3_seq_15465.14138.822313997

      Media Recovery Waiting for thread 5 sequence 510

      Sat Aug 03 23:15:21 2013

      Media Recovery Log +FLASH/dgjmspl/archivelog/2013_08_01/thread_5_seq_510

      Sat Aug 03 23:15:32 2013

      Media Recovery Log +FLASH/dgjmspl/archivelog/2013_08_01/thread_3_seq_15466.10925.822316315

      Sat Aug 03 23:17:18 2013

      Media Recovery Log +FLASH/dgjmspl/archivelog/2013_08_01/thread_2_seq_17666.853.822333143

      Sat Aug 03 23:18:39 2013

      Media Recovery Log +FLASH/dgjmspl/archivelog/2013_08_01/thread_3_seq_15467.834.822333553

      Sat Aug 03 23:20:54 2013

        • 1. Re: Standby media recovery waiting for inactive thread
          Mihael

          Disable inactive thread, drop its redo log groups and recreate standby controlfile.

          • 2. Re: Standby media recovery waiting for inactive thread
            rontrinidadcabral

            in Standby, thread 4 and 5 are both UNUSED, and size is incorrect (not equal to other redo log). I want to recreate it but cannot drop redo log. I follow Doc ID 740675.1.

             

            any idea what are the missing steps? thanks.

             

            ORA-01624: needed for crash recovery of instance UNNAMED_INSTANCE_5 (thread 5)

             

            select group#,thread#,archived,status,bytes from v$log;

             

            primary DB:

                GROUP#    THREAD# ARC STATUS                BYTES
            ---------- ---------- --- ---------------- ----------
                     1          1 YES INACTIVE         1073741824
                     2          1 YES INACTIVE         1073741824
                     3          2 NO  CURRENT          1073741824
                     4          2 YES INACTIVE         1073741824
                     5          3 YES INACTIVE         1073741824
                     6          3 YES INACTIVE         1073741824
                     7          2 YES INACTIVE         1073741824
                     8          1 NO  CURRENT          1073741824
                     9          3 NO  CURRENT          1073741824
                    10          4 YES INACTIVE         1073741824
                    11          4 NO  CURRENT          1073741824

                GROUP#    THREAD# ARC STATUS                BYTES
            ---------- ---------- --- ---------------- ----------
                    12          4 YES INACTIVE         1073741824
                    13          5 YES INACTIVE         1073741824
                    14          5 YES INACTIVE         1073741824
                    15          5 NO  CURRENT          1073741824

             

            standby DB:

             

                GROUP#    THREAD# ARC STATUS                BYTES
            ---------- ---------- --- ---------------- ----------
                     1          1 YES INACTIVE         1073741824
                     2          1 YES INACTIVE         1073741824
                     3          2 NO  CURRENT          1073741824
                     4          2 YES INACTIVE         1073741824
                     5          3 YES INACTIVE         1073741824
                     6          3 YES INACTIVE         1073741824
                     7          2 YES INACTIVE         1073741824
                     8          1 NO  CURRENT          1073741824
                     9          3 NO  CURRENT          1073741824
                    10          4 YES INACTIVE         1073741824
                    11          4 NO  CURRENT          1073741824

                GROUP#    THREAD# ARC STATUS                BYTES
            ---------- ---------- --- ---------------- ----------
                    12          4 YES INACTIVE         1073741824
                    13          5 YES INACTIVE         1073741824
                    14          5 YES INACTIVE         1073741824
                    15          5 NO  CURRENT          1073741824


            • 3. Re: Standby media recovery waiting for inactive thread
              rontrinidadcabral

              correction below is the standby DB v$log;

              standby DB;

               

                  GROUP#    THREAD# ARC STATUS                BYTES
              ---------- ---------- --- ---------------- ----------
                       1          1 YES CLEARING         1073741824
                       2          1 YES CLEARING         1073741824
                       3          2 YES CURRENT          1073741824
                       4          2 YES CLEARING         1073741824
                       5          3 YES CLEARING         1073741824
                       6          3 YES CLEARING         1073741824
                       7          2 YES CLEARING         1073741824
                       8          1 YES CURRENT          1073741824
                       9          3 YES CURRENT          1073741824
                      22          4 YES UNUSED            104857600
                      23          4 YES UNUSED            104857600

                  GROUP#    THREAD# ARC STATUS                BYTES
              ---------- ---------- --- ---------------- ----------
                      24          5 YES UNUSED            104857600
                      25          5 YES UNUSED            104857600

              • 4. Re: Standby media recovery waiting for inactive thread
                Mihael

                In this document go to section Sync Redo Logs on Primary and Standby .

                 

                You need to drop logs on primary and recreate standby controlfile.

                • 5. Re: Standby media recovery waiting for inactive thread
                  rontrinidadcabral

                  Thanks. the link is not working..

                  please repost.

                  • 6. Re: Standby media recovery waiting for inactive thread
                    Mihael

                    This is not a link, this is section from Doc ID 740675.1 that you mentioned.