This discussion is archived
8 Replies Latest reply: Sep 5, 2013 3:40 PM by 896971 RSS

Standby Not Applying After Outage

896971 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

Legend

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