8 Replies Latest reply: Sep 5, 2013 6:17 PM by mseberg RSS

    Standby Not Applying After Outage

    896971

      We have a 11r2 primary and standby running on RHEL 5. The primary lost network connectivity for a time and VMWare locked up. The VM admin restarted the primary VM (it was a hard-boot). This all happened while I was gone and when I came into work, the primary database appeared to be working. I decided to dig deeper and check out the standby.

       

      On the standby...

       

      SQL> select * from v$archive_gap;
      
      Thread#: 1
      low_sequence: 123120
      high_sequence: 123120
      
      

       

       

      DGMGRL> show configuration
      
      Configuration - prodDB
           Protection Mode: MaxPerformance
           Databases:
                prodDB - Primary database
                prodDB2 - Physical standby database
      Fast-Start Failover: DISABLED
      Configuration Status:
      SUCCESS
      
      

       

      SQL> select count(*) from v$archived_log where applied='NO';
      
      Count(*): 107
      
      

       

      SQL> select process, client_process, thread#, sequence#, block#, status from v$managed_standby where process='MRP0' or client_process='LGWR';
      
      Process: MRP0
      Client_Process: N/A
      Thread#: 1
      Sequence#: 123120
      Block#: 0
      Status: WAIT_FOR_LOG
      
      Process: RFS
      Client_Process: LGWR
      Thread#: 1
      Sequence#: 128315
      Block#: 4686
      Status: IDLE
      
      

       

      DGMGRL> show database prodDB2;
      
      Role: PHYSICAL STANDBY
      Intended State: APPLY-ON
      Transport Lag: 2 days 3 minutes 53 seconds
      Apply Lag: 2 days 3 minutes 53 seconds
      Real Time Query: OFF
      Instance(s): prodDB
      
      

       

      It appears all is well (just very far behind) but when I run the last code block (show database prodDB2;) again ...

       

      DGMGRL> show database prodDB2;
      
      Role: PHYSICAL STANDBY
      Intended State: APPLY-ON
      Transport Lag: 2 days 1 hour(s) 42 minutes 37 seconds
      Apply Lag: 2 days 1 hour(s) 42 minutes 37 seconds
      Real Time Query: OFF
      Instance(s): prodDB
      
      

       

       

      ...it looks as if the massive backlog is just increasing. How can I confirm that everything that things are going well? I am still learning Data Guard.

       

      Thank you

        • 1. Re: Standby Not Applying After Outage
          896971

          Something I forgot to mention. An hour ago, I ran the following commands:

           

          SQL> alter database recover managed standby database cancel;
          Database altered.
          
          SQL> alter database recover managed standby database using current logfile disconnect;
          Database altered.
          

           

          The Transport/Apply Lag just keeps growing.

          • 2. Re: Standby Not Applying After Outage
            mseberg

            Hello;

             

            Generally mixing Data Guard Broker is not a good idea. Once you start using broker stay with it.

             

            Try running this from the Primary:

             

            http://www.visi.com/~mseberg/data_guard/monitor_data_guard_transport.html

             

            I don't trust  v$archive_gap myself.

             

            Also this:

             

            select count(*) from v$archived_log where applied='NO'; 

             

            Should have or include DEST_ID for the standby when run from the primary side, otherwise you might pickup the primary count.

             

            Best Regards

             

            mseberg

            • 3. Re: Standby Not Applying After Outage
              896971

              Thanks for the reply mseberg! I have a follow-up question if you don't mind. I just ran your query on the primary and got this:

               

              DB_NAME: PRODDB
              HOSTNAME: PRODHOST
              LOG_ARCHIVED: 128315
              LOG_APPLIED: 118464
              APPLIED_TIME: 29-MAY/19:06
              LOG_GAP: 9851
              

               

              This seems to be implying that my logs haven't been applying since May? Or am I misinterpreting this?

              • 4. Re: Standby Not Applying After Outage
                mseberg

                Hello again;

                 

                Yes, it appears you have a larger issue then we thought. I would rebuild at this point.

                 

                If this helps I check my apply everyday. I also would star without Broker at first until I get the feel of Data Guard and then I would add broker back in.

                 

                If you are new to Data guard consider this resource :

                 

                 

                New Data Guard Book

                 

                Best Regards

                 

                 

                mseberg

                • 5. Re: Standby Not Applying After Outage
                  896971

                  How can this be true though? On the standby, it shows that I am only 2 days behind as seen in my first post?

                  • 6. Re: Standby Not Applying After Outage
                    mseberg

                    Hello;

                     

                    It shows 9851 logs behind. If you created the standby more than once then the query could be giving incorrect information. If this is the case then check from the standby. For example

                     

                    PRIMARY SIDE

                     

                    /home/oracle:PRIMARY >sqlplus "/ as sysdba"

                     

                    SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 5 16:14:06 2013

                     

                    Copyright (c) 1982, 2011, Oracle. All rights reserved.

                     

                     

                    Connected to:

                    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                    With the Partitioning, OLAP, Data Mining and Real Application Testing options

                     

                    SQL> select max(sequence#) from v$archived_log where NEXT_TIME > sysdate -1;

                     

                    MAX(SEQUENCE#)

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

                      766

                     

                    SQL>

                     

                    STANDBY SIDE

                     

                    /home/oracle:STANDBY >sqlplus "/ as sysdba"

                     

                    SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 5 16:14:40 2013

                     

                    Copyright (c) 1982, 2011, Oracle. All rights reserved.

                     

                     

                    Connected to:

                    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                    With the Partitioning, OLAP, Data Mining and Real Application Testing options

                     

                    SQL> select max(sequence#) from v$archived_log where NEXT_TIME > sysdate -1;

                     

                    MAX(SEQUENCE#)

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

                      766

                     

                    If you do this is the difference  really 9851?

                     

                    Best Regards

                     

                    mseberg

                    • 7. Re: Standby Not Applying After Outage
                      896971

                      mseberg wrote:

                       

                      If you do this is the difference  really 9851?

                       

                       

                      Thankfully, it appears not to be. The output on both servers is "128317". This seems to imply that they are in sync?

                       

                      But when I run "show database prodDB2" in DGMGRL I again get a response that my transport/apply lag is "2 days 5 hours 46 minutes 16 seconds" behind. I am so confused. :-/ For what it's worth, I did not build either of these servers. I am just inheriting them.

                      • 8. Re: Standby Not Applying After Outage
                        mseberg

                        Hello again;

                         

                        One thing you can try if you are note using ASM and STANDBY_FILE_MANAGEMENT=AUTO on both is to create a tiny new tablespace and then do a log switch. If the new datafile appears then

                        you are getting bogus information from Oracle. Also if you don't have ASM check the physical location of the log. You can have transport working and apply not, or both not working or both working for that matter.


                        The alert logs on both sides are a great source of information. The Standby side will generally say "waiting for log..." compare this to the last log on the Primary side.


                        Best Regards


                        mseberg