This discussion is archived
11 Replies Latest reply: Apr 23, 2013 3:10 AM by MahirM.Quluzade RSS

Redo Archive Logs Missing

Thunder2777 Newbie
Currently Being Moderated
Hi Gurus

While Configuring Data Guard for ORacle 10g (10.2.0.4) 64 bits on Windows 2007 Server 64 bits.

I got few questions

1. What is the Default mode of Standby Database?

2. Should we Always Start Physical Standby Database to Recover Missing Redo Archive Log?

SQL> startup mount;

ORACLE instance started.

Total System Global Area 591396864 bytes
Fixed Size 2067496 bytes
Variable Size 163578840 bytes
Database Buffers 419430400 bytes
Redo Buffers 6320128 bytes
Database mounted.

SQL> alter databse recover managed standby database disconnect from session;
               Database altered.


3. When there are missing Redo Log Archives e.g.

----On Standby Database--------

SQL> SELECT RESETLOGS_ID,SEQUENCE#,STATUS,ARCHIVED FROM V$ARCHIVED_LOG
2 ORDER BY RESETLOGS_ID,SEQUENCE#;

RESETLOGS_ID SEQUENCE# S ARC
------------ ---------- - ---
812980008 15 A YES
812980008 16 A YES
812980008 17 A YES
812980008 18 A YES
812980008 19 A YES
812980008 20 A YES
812980008 21 A YES
812980008 22 A YES
812980008 23 A YES
812980008 24 A YES
812980008 25 A YES

RESETLOGS_ID SEQUENCE# S ARC
------------ ---------- - ---
812980008 26 A YES
812980008 27 A YES
812980008 28 A YES
812980008 29 A YES
812980008 30 A YES
812980008 31 A YES
812980008 32 A YES
812980008 33 A YES
812980008 34 A YES
812980008 35 A YES
812980008 36 A YES

RESETLOGS_ID SEQUENCE# S ARC
------------ ---------- - ---
812980008 37 A YES
812980008 38 A YES
812980008 39 A YES
812980008 40 A YES
812980008 41 A YES
812980008 42 A YES
812980008 43 A YES

29 rows selected.

---------------On Primary Database---------------------

SQL> SELECT RESETLOGS_ID,SEQUENCE#,STATUS,ARCHIVED FROM V$ARCHIVED_LOG
2 ORDER BY RESETLOGS_ID,SEQUENCE# ;

RESETLOGS_ID SEQUENCE# S ARC
------------ ---------- - ---
*812980008 8 A YES*
*812980008 9 A YES*
*812980008 10 A YES*
*812980008 11 A YES*
*812980008 12 A YES*
*812980008 13 A YES*
*812980008 14 A YES*
812980008 15 A YES
812980008 15 A YES
812980008 16 A YES
812980008 16 A YES

RESETLOGS_ID SEQUENCE# S ARC
------------ ---------- - ---
812980008 17 A YES
812980008 17 A YES
812980008 18 A YES
812980008 18 A YES
812980008 19 A YES
812980008 19 A YES
812980008 20 A YES
812980008 20 A YES
812980008 21 A YES
812980008 21 A YES
812980008 22 A YES

RESETLOGS_ID SEQUENCE# S ARC
------------ ---------- - ---
812980008 22 A YES
812980008 23 A YES
812980008 23 A YES
812980008 24 A YES
812980008 24 A YES
812980008 25 A YES
812980008 25 A YES
812980008 26 A YES
812980008 26 A YES
812980008 27 A YES
812980008 27 A YES

RESETLOGS_ID SEQUENCE# S ARC
------------ ---------- - ---
812980008 28 A YES
812980008 28 A YES
812980008 29 A YES
812980008 29 A YES
812980008 30 A YES
812980008 30 A YES
812980008 31 A YES
812980008 31 A YES
812980008 32 A YES
812980008 32 A YES
812980008 33 A YES

RESETLOGS_ID SEQUENCE# S ARC
------------ ---------- - ---
812980008 33 A YES
812980008 34 A YES
812980008 34 A YES
812980008 35 A YES
812980008 35 A YES
812980008 36 A YES
812980008 36 A YES
812980008 37 A YES
812980008 37 A YES
812980008 38 A YES
812980008 38 A YES

RESETLOGS_ID SEQUENCE# S ARC
------------ ---------- - ---
812980008 39 A YES
812980008 39 A YES
812980008 40 A YES
812980008 40 A YES
812980008 41 A YES
812980008 41 A YES
812980008 42 A YES
812980008 42 A YES
812980008 43 A YES
812980008 43 A YES

65 rows selected.

Log 8, 9, 10, 11, 12, 13, 14, 15 are missing.

How to Apply / Recover These Logs on Standby Database?

Regards
Thunder2777
  • 1. Re: Redo Archive Logs Missing
    Sunny kichloo Expert
    Currently Being Moderated
    What is the Default mode of Standby Database?

    By default it is Maximum Performance.

    http://docs.oracle.com/cd/B28359_01/server.111/b28294/protection.htm

    Also Missing archive logs you can use register command if gap is less

    http://jarneil.wordpress.com/2008/05/16/registering-archive-logfiles-on-a-standby/
  • 2. Re: Redo Archive Logs Missing
    MahirM.Quluzade Guru
    Currently Being Moderated
    Hi
    Thunder2777 wrote:
    Hi Gurus

    While Configuring Data Guard for ORacle 10g (10.2.0.4) 64 bits on Windows 2007 Server 64 bits.

    I got few questions

    1. What is the Default mode of Standby Database?
    What is default mode? I think you want ask in what mode standby database is apply redo logs.
    Standby database can apply received redo only MOUNT mode, (your version is 10g, after 11g can apply open mode with READ ONLY WITH APPLY)

    2. Should we Always Start Physical Standby Database to Recover Missing Redo Archive Log?
    If Standby database opened mount mode, then database can receive redo.
    If you are start Redo Apply then MPR can request from primary for missing redo logs.
    SQL> startup mount;

    ORACLE instance started.

    Total System Global Area 591396864 bytes
    Fixed Size 2067496 bytes
    Variable Size 163578840 bytes
    Database Buffers 419430400 bytes
    Redo Buffers 6320128 bytes
    Database mounted.

    SQL> alter databse recover managed standby database disconnect from session;
                   Database altered.
    It is started recovery, in other words Redo Apply (MRP0 process)

    >
    3. When there are missing Redo Log Archives e.g.

    ----On Standby Database--------
    >
    SQL> SELECT RESETLOGS_ID,SEQUENCE#,STATUS,ARCHIVED FROM V$ARCHIVED_LOG
    2 ORDER BY RESETLOGS_ID,SEQUENCE#;

    RESETLOGS_ID SEQUENCE# S ARC
    ------------ ---------- - ---
    812980008 15 A YES
    812980008 16 A YES
    812980008 17 A YES
    812980008 18 A YES
    812980008 19 A YES
    812980008 20 A YES
    812980008 21 A YES
    812980008 22 A YES
    812980008 23 A YES
    812980008 24 A YES
    812980008 25 A YES

    RESETLOGS_ID SEQUENCE# S ARC
    ------------ ---------- - ---
    812980008 26 A YES
    812980008 27 A YES
    812980008 28 A YES
    812980008 29 A YES
    812980008 30 A YES
    812980008 31 A YES
    812980008 32 A YES
    812980008 33 A YES
    812980008 34 A YES
    812980008 35 A YES
    812980008 36 A YES

    RESETLOGS_ID SEQUENCE# S ARC
    ------------ ---------- - ---
    812980008 37 A YES
    812980008 38 A YES
    812980008 39 A YES
    812980008 40 A YES
    812980008 41 A YES
    812980008 42 A YES
    812980008 43 A YES

    29 rows selected.

    ---------------On Primary Database---------------------

    SQL> SELECT RESETLOGS_ID,SEQUENCE#,STATUS,ARCHIVED FROM V$ARCHIVED_LOG
    2 ORDER BY RESETLOGS_ID,SEQUENCE# ;

    RESETLOGS_ID SEQUENCE# S ARC
    ------------ ---------- - ---
    *812980008 8 A YES*
    *812980008 9 A YES*
    *812980008 10 A YES*
    *812980008 11 A YES*
    *812980008 12 A YES*
    *812980008 13 A YES*
    *812980008 14 A YES*
    812980008 15 A YES
    812980008 15 A YES
    812980008 16 A YES
    812980008 16 A YES

    RESETLOGS_ID SEQUENCE# S ARC
    ------------ ---------- - ---
    812980008 17 A YES
    812980008 17 A YES
    812980008 18 A YES
    812980008 18 A YES
    812980008 19 A YES
    812980008 19 A YES
    812980008 20 A YES
    812980008 20 A YES
    812980008 21 A YES
    812980008 21 A YES
    812980008 22 A YES

    RESETLOGS_ID SEQUENCE# S ARC
    ------------ ---------- - ---
    812980008 22 A YES
    812980008 23 A YES
    812980008 23 A YES
    812980008 24 A YES
    812980008 24 A YES
    812980008 25 A YES
    812980008 25 A YES
    812980008 26 A YES
    812980008 26 A YES
    812980008 27 A YES
    812980008 27 A YES

    RESETLOGS_ID SEQUENCE# S ARC
    ------------ ---------- - ---
    812980008 28 A YES
    812980008 28 A YES
    812980008 29 A YES
    812980008 29 A YES
    812980008 30 A YES
    812980008 30 A YES
    812980008 31 A YES
    812980008 31 A YES
    812980008 32 A YES
    812980008 32 A YES
    812980008 33 A YES

    RESETLOGS_ID SEQUENCE# S ARC
    ------------ ---------- - ---
    812980008 33 A YES
    812980008 34 A YES
    812980008 34 A YES
    812980008 35 A YES
    812980008 35 A YES
    812980008 36 A YES
    812980008 36 A YES
    812980008 37 A YES
    812980008 37 A YES
    812980008 38 A YES
    812980008 38 A YES

    RESETLOGS_ID SEQUENCE# S ARC
    ------------ ---------- - ---
    812980008 39 A YES
    812980008 39 A YES
    812980008 40 A YES
    812980008 40 A YES
    812980008 41 A YES
    812980008 41 A YES
    812980008 42 A YES
    812980008 42 A YES
    812980008 43 A YES
    812980008 43 A YES

    65 rows selected.

    Log 8, 9, 10, 11, 12, 13, 14, 15 are missing.
    It is no missing, you are created standby database, after sequence 15.
    As you know , if a sequence redo is not applied, then after is sequenced redo log is cannot apply to standby database.
    It means GAP.

    There have 43 archived redo log, and your last sequenced archive log received by standby database
    and applied.

    You can check with following scripts, too
    select max(Sequence#) from v$archived_log; -- on primary 
    select max(Sequence#) from v$archived_log where applied = 'YES';  - on standby  
    Regards
    Mahir M. Quluzade
  • 3. Re: Redo Archive Logs Missing
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Were the logs 8 to 14 created in the Primary before the Backup for the Standby was executed ? If so, the Standby doesn't need them.
    The Standby needs all archivelogs from the point when the Primary Database Backup (that was used to create the Standby) begun.

    If the Primary backup was taken when ArchiveLogs 8 to 14 were still being / not yet generated on the Primary, the Standby would have needed them when you started the RECOVER MANAGED STANDBY DATABASE. Check your Standby alert.log for messages indicating which ArchiveLog it has been applying and/or is waiting for.



    Hemant K Chitale
  • 4. Re: Redo Archive Logs Missing
    Thunder2777 Newbie
    Currently Being Moderated
    THANKS Sunny kichloo for Prompt Reply.

    There was a little Miss Understanding regarding Physical Standby Database mode.

    Consider this, whenever I connected to my Standby DB, it shows connected to
    IDLE Instance. (STLDB2 is Standby DB Name)

    C:\Users\Administrator>SET ORACLE_SID=STLDB2

    C:\Users\Administrator>SQLPLUS / AS SYSDBA

    SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 23 10:46:52 2013

    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

    Connected to an idle instance.

    SQL>

    After that I start it as follow

    SQL> startup mount;

    ORACLE instance started.

    Total System Global Area 591396864 bytes
    Fixed Size 2067496 bytes
    Variable Size 163578840 bytes
    Database Buffers 419430400 bytes
    Redo Buffers 6320128 bytes
    Database mounted.

    SQL> alter databse recover managed standby database disconnect from session;
                   Database altered.

    My question is One has to Repeat the above procedure every time
    in order to Startup a Standby DB and Apply Redo Logs?

    Kind Regards
    Thunder2777
  • 5. Re: Redo Archive Logs Missing
    Thunder2777 Newbie
    Currently Being Moderated
    Thanks Mahir for Prompt Response

    1. I have executed the queries, Result are as follows

    Primary DB
    SQL> select max(Sequence#) from v$archived_log;

    MAX(SEQUENCE#)
    --------------
    43

    Standby DB
    SQL> select max(Sequence#) from v$archived_log where applied = 'YES';

    MAX(SEQUENCE#)
    --------------
    43

    2. When I executed the following query

    ------------------------Standby DB------------------------------------------------------------
    SQL> SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_HISTORY;

    FIRST_TIM FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
    --------- ------------- ------------ ----------
    17-APR-13 602121 632338 1
    17-APR-13 632338 659745 2
    17-APR-13 659745 679908 3
    17-APR-13 679908 700230 4
    17-APR-13 700230 720537 5
    17-APR-13 720537 740841 6
    17-APR-13 740841 761377 7
    17-APR-13 761377 785868 8
    17-APR-13 785868 807639 9
    17-APR-13 807639 828022 10
    17-APR-13 828022 849401 11
    17-APR-13 849401 870229 12
    17-APR-13 870229 891680 13
    17-APR-13 891680 913309 14
    18-APR-13 913309 937799 15
    19-APR-13 937799 944409 16
    19-APR-13 944409 944412 17
    19-APR-13 944412 945139 18
    19-APR-13 945139 965810 19
    22-APR-13 965810 965887 20
    22-APR-13 965887 965917 21
    22-APR-13 965917 970169 22
    22-APR-13 970169 970365 23
    22-APR-13 970365 970368 24
    22-APR-13 970368 970409 25
    22-APR-13 970409 970461 26
    22-APR-13 970461 972305 27
    22-APR-13 972305 972545 28
    22-APR-13 972545 972548 29
    22-APR-13 972548 972590 30
    22-APR-13 972590 977651 31
    22-APR-13 977651 980325 32
    22-APR-13 980325 980555 33
    22-APR-13 980555 980558 34
    22-APR-13 980558 980612 35
    22-APR-13 980612 981366 36
    23-APR-13 981366 981596 37
    23-APR-13 981596 981599 38
    23-APR-13 981599 981638 39
    23-APR-13 981638 982112 40
    23-APR-13 982112 982280 41
    23-APR-13 982280 982310 42
    23-APR-13 982310 982333 43

    43 rows selected.

    ------------------------Primary DB------------------------------------------------------------
    SQL> SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_HISTORY;

    FIRST_TIM FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
    --------- ------------- ------------ ----------
    17-APR-13 602121 632338 1
    17-APR-13 632338 659745 2
    17-APR-13 659745 679908 3
    17-APR-13 679908 700230 4
    17-APR-13 700230 720537 5
    17-APR-13 720537 740841 6
    17-APR-13 740841 761377 7
    17-APR-13 761377 785868 8
    17-APR-13 785868 807639 9
    17-APR-13 807639 828022 10
    17-APR-13 828022 849401 11
    17-APR-13 849401 870229 12
    17-APR-13 870229 891680 13
    17-APR-13 891680 913309 14
    18-APR-13 913309 937799 15
    19-APR-13 937799 944409 16
    19-APR-13 944409 944412 17
    19-APR-13 944412 945139 18
    19-APR-13 945139 965810 19
    22-APR-13 965810 965887 20
    22-APR-13 965887 965917 21
    22-APR-13 965917 970169 22
    22-APR-13 970169 970365 23
    22-APR-13 970365 970368 24
    22-APR-13 970368 970409 25
    22-APR-13 970409 970461 26
    22-APR-13 970461 972305 27
    22-APR-13 972305 972545 28
    22-APR-13 972545 972548 29
    22-APR-13 972548 972590 30
    22-APR-13 972590 977651 31
    22-APR-13 977651 980325 32
    22-APR-13 980325 980555 33
    22-APR-13 980555 980558 34
    22-APR-13 980558 980612 35
    22-APR-13 980612 981366 36
    23-APR-13 981366 981596 37
    23-APR-13 981596 981599 38
    23-APR-13 981599 981638 39
    23-APR-13 981638 982112 40
    23-APR-13 982112 982280 41
    23-APR-13 982280 982310 42
    23-APR-13 982310 982333 43

    43 rows selected.

    Both Result are OK.

    Which means My Data Guard Configuration is OK &
    Primary DB & Standby DB both are doing Well.
    Am I Right????

    Regards
    Thunder2777
  • 6. Re: Redo Archive Logs Missing
    Thunder2777 Newbie
    Currently Being Moderated
    THANKS Hemant K Chitale for Prompt Response.

    When I Executed following query

    -----------------Standby DB---------------------------------

    SQL> SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_HISTORY;

    FIRST_TIM FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
    --------- ------------- ------------ ----------
    17-APR-13 602121 632338 1
    17-APR-13 632338 659745 2
    17-APR-13 659745 679908 3
    17-APR-13 679908 700230 4
    17-APR-13 700230 720537 5
    17-APR-13 720537 740841 6
    17-APR-13 740841 761377 7
    17-APR-13 761377 785868 8
    17-APR-13 785868 807639 9
    17-APR-13 807639 828022 10
    17-APR-13 828022 849401 11
    17-APR-13 849401 870229 12
    17-APR-13 870229 891680 13
    17-APR-13 891680 913309 14
    18-APR-13 913309 937799 15
    19-APR-13 937799 944409 16
    19-APR-13 944409 944412 17
    19-APR-13 944412 945139 18
    19-APR-13 945139 965810 19
    22-APR-13 965810 965887 20
    22-APR-13 965887 965917 21
    22-APR-13 965917 970169 22
    22-APR-13 970169 970365 23
    22-APR-13 970365 970368 24
    22-APR-13 970368 970409 25
    22-APR-13 970409 970461 26
    22-APR-13 970461 972305 27
    22-APR-13 972305 972545 28
    22-APR-13 972545 972548 29
    22-APR-13 972548 972590 30
    22-APR-13 972590 977651 31
    22-APR-13 977651 980325 32
    22-APR-13 980325 980555 33
    22-APR-13 980555 980558 34
    22-APR-13 980558 980612 35
    22-APR-13 980612 981366 36
    23-APR-13 981366 981596 37
    23-APR-13 981596 981599 38
    23-APR-13 981599 981638 39
    23-APR-13 981638 982112 40
    23-APR-13 982112 982280 41
    23-APR-13 982280 982310 42
    23-APR-13 982310 982333 43

    43 rows selected.


    -----------------Primary DB---------------------------------
    SQL> SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_HISTORY;

    FIRST_TIM FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
    --------- ------------- ------------ ----------
    17-APR-13 602121 632338 1
    17-APR-13 632338 659745 2
    17-APR-13 659745 679908 3
    17-APR-13 679908 700230 4
    17-APR-13 700230 720537 5
    17-APR-13 720537 740841 6
    17-APR-13 740841 761377 7
    17-APR-13 761377 785868 8
    17-APR-13 785868 807639 9
    17-APR-13 807639 828022 10
    17-APR-13 828022 849401 11
    17-APR-13 849401 870229 12
    17-APR-13 870229 891680 13
    17-APR-13 891680 913309 14
    18-APR-13 913309 937799 15
    19-APR-13 937799 944409 16
    19-APR-13 944409 944412 17
    19-APR-13 944412 945139 18
    19-APR-13 945139 965810 19
    22-APR-13 965810 965887 20
    22-APR-13 965887 965917 21
    22-APR-13 965917 970169 22
    22-APR-13 970169 970365 23
    22-APR-13 970365 970368 24
    22-APR-13 970368 970409 25
    22-APR-13 970409 970461 26
    22-APR-13 970461 972305 27
    22-APR-13 972305 972545 28
    22-APR-13 972545 972548 29
    22-APR-13 972548 972590 30
    22-APR-13 972590 977651 31
    22-APR-13 977651 980325 32
    22-APR-13 980325 980555 33
    22-APR-13 980555 980558 34
    22-APR-13 980558 980612 35
    22-APR-13 980612 981366 36
    23-APR-13 981366 981596 37
    23-APR-13 981596 981599 38
    23-APR-13 981599 981638 39
    23-APR-13 981638 982112 40
    23-APR-13 982112 982280 41
    23-APR-13 982280 982310 42
    23-APR-13 982310 982333 43

    43 rows selected.

    Both are Equal.

    It means No Issue at All.
    My Data Guard Configuration is Working Right.
    My Primary DB & Standby DB are also doing fine.

    Am I Right?

    Regards
    Thunder2777
  • 7. Re: Redo Archive Logs Missing
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Yes they are in sync.

    You can
    a. View the alert.log of the Standby
    b. Query V$DATAGUARD_STATUS
    c. Query V$DATAGUARD_STATS WHERE NAME = 'apply lag'


    for messages and status information.


    Hemant K Chitale
  • 8. Re: Redo Archive Logs Missing
    Sunny kichloo Expert
    Currently Being Moderated
    Normally People keep standby in mount stage or sometimes open it for read-only purpose.

    Suppose if you have Active Dataguard features enabled then you can keep standby in SYNC with primary in read-only mode.
  • 9. Re: Redo Archive Logs Missing
    MahirM.Quluzade Guru
    Currently Being Moderated
    Yes, you data guard running well.
    You can found erorrs, if have in v$dataguard_status view.

    Thanks
    Mahir M. Quluzade

    p.s. Please close your thread, if your questions is answered.
  • 10. Re: Redo Archive Logs Missing
    Thunder2777 Newbie
    Currently Being Moderated
    Thanks to All of You Guys especially

    Hemant K Chitale,  Sunny kichloo, Mahir M. Quluzade

    Kind Regards
    Thunder2777

    Edited by: Thunder2777 on Apr 23, 2013 3:09 AM
  • 11. Re: Redo Archive Logs Missing
    MahirM.Quluzade Guru
    Currently Being Moderated
    You are welcome!

Legend

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