11 Replies Latest reply: Apr 23, 2013 5:10 AM by Mahir M. Quluzade RSS

    Redo Archive Logs Missing

    Thunder2777
      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
          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
            Mahir M. Quluzade
            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
              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
                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
                  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
                    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
                      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
                        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
                          Mahir M. Quluzade
                          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
                            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
                              Mahir M. Quluzade
                              You are welcome!