12 Replies Latest reply on May 30, 2014 12:04 PM by sweetritz

    Archive logs gap between Primary and Standby issue.

    sweetritz

      Hi DBA gods,

       

      We had a log shipping defer last evening from Primary DB (Oracle 10.2.0.5) to standby DB due to network utilisation issue. And today morning again we enabled the log shipping.

      But while checking for sync we I found that there was archive log gap from the below query in the Standby.

       

      SELECT high.thread#, "LowGap#", "HighGap#"    FROM  (

             SELECT thread#, MIN(sequence#)-1 "HighGap#"

           FROM

              (

                     SELECT a.thread#, a.sequence#

                    FROM

                      (

                      SELECT *

                       FROM v$archived_log

                  ) a,            (

                     SELECT thread#, MAX(next_change#)gap1

                     FROM v$log_history

                      GROUP BY thread#

                 ) b

                 WHERE a.thread# = b.thread#

                   AND a.next_change# > gap1

             )

               GROUP BY thread#

          ) high,

           (

               SELECT thread#, MIN(sequence#) "LowGap#"

               FROM

               (

                   SELECT thread#, sequence#

                   FROM v$log_history, v$datafile

                  WHERE checkpoint_change# <= next_change#

                  AND checkpoint_change# >= first_change#         )

               GROUP BY thread#

           ) low

           WHERE low.thread# = high.thread#;

       

      OUTPUT:

        THREAD#    LowGap#   HighGap#

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

               1                23635      23640

       

       

      I am newbie DBA and struggling to get the solution. Googled but could not find any exact solution as I cant risk my Production DBs. Any help from this Professional Oracle forum would be great.

      How I get the above missing sequence in the standby and get it synced with the primary?

       

      Regards,

      Ritu

        • 1. Re: Archive logs gap between Primary and Standby issue.
          FreddieEssex

          If you look in the alert log of your standby there should be a message something like "Waiting for logfile sequence xyz".

           

          The log shipping should have happened automatically.  Can you not just copy the missing archivelogs across from your primary to your standby?

          • 2. Re: Archive logs gap between Primary and Standby issue.
            sweetritz

            STANDBY DB alert log

            ================

             

             

            Media Recovery Waiting for thread 1 sequence 23636

            Wed May 28 22:35:00 IST 2014

            RFS[1]: Possible network disconnect with primary database

            Thu May 29 07:46:20 IST 2014

            Redo Shipping Client Connected as PUBLIC

            -- Connected User is Valid

            RFS[2]: Assigned to RFS process 8148

            RFS[2]: Identified database type as 'physical standby'

            Thu May 29 07:46:24 IST 2014

            Fetching gap sequence in thread 1, gap sequence 23636-23691

            Thu May 29 07:46:27 IST 2014

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23643_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23644_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23645_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23646_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23647_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23648_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23649_817473049.arc'

            Thu May 29 07:46:38 IST 2014

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23650_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23651_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23652_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23653_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23654_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23655_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23656_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23657_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23658_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23659_817473049.arc'

            Thu May 29 07:46:50 IST 2014

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23660_817473049.arc'

            Thu May 29 07:46:54 IST 2014

            Fetching gap sequence in thread 1, gap sequence 23636-23642

            Thu May 29 07:46:55 IST 2014

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23661_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23662_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23663_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23664_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23665_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23666_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23667_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23668_817473049.arc'

            Thu May 29 07:47:06 IST 2014

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23669_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23670_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23671_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23672_817473049.arc'

            Thu May 29 07:47:17 IST 2014

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23673_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23674_817473049.arc'

            RFS[2]: Archived Log: '/archive01/zeus/local/zeus_1_23675_817473049.arc'

            Thu May 29 07:47:24 IST 2014

            FAL[client]: Failed to request gap sequence

            GAP - thread 1 sequence 23636-23642

            DBID 1234456789 branch 817473049

            FAL[client]: All defined FAL servers have been attempted.

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

            Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization

            parameter is defined to a value that is sufficiently large

            enough to maintain adequate log switch information to resolve

            archivelog gaps.

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

            • 3. Re: Archive logs gap between Primary and Standby issue.
              FreddieEssex

              GAP - thread 1 sequence 23636-23642


              Have you tried copying the above logs from your primary to your standby?

               

              Do they exist on your primary?

               

               


              • 4. Re: Archive logs gap between Primary and Standby issue.
                sweetritz

                no it doesnt exist on primary too.

                • 5. Re: Archive logs gap between Primary and Standby issue.
                  FreddieEssex

                  Restore your archivelogs from backups and then copy over to the standby.

                   

                  If you don't have backups of your archivelogs you will need to either do an incremental restore on your standby or rebuild your standby.

                  1 person found this helpful
                  • 6. Re: Archive logs gap between Primary and Standby issue.
                    sweetritz

                    @FreddieEssex thanks for the guidance....but how to do incremental restore on my standby db?:( pls help

                    • 7. Re: Archive logs gap between Primary and Standby issue.
                      FreddieEssex

                      Here is the link - Using RMAN Incremental Backups to Roll Forward a Physical Standby Database

                       

                      http://docs.oracle.com/cd/E11882_01/server.112/e41134/rman.htm#CIHIAADC

                       

                      Probably worth investigating how/why archivelogs were deleted without being backed up or applied  first.

                      1 person found this helpful
                      • 8. Re: Archive logs gap between Primary and Standby issue.
                        sweetritz

                        thanks for the help. where can I find the missing archive logs backed up or not? in backup folder will it show the missing sequence in the backup peiece if no then how will I know where they are backed up.?

                         

                        Thanks in advance.

                        • 9. Re: Archive logs gap between Primary and Standby issue.
                          FreddieEssex

                          If you are using RMAN to backup your database and archivelogs then use the following to check if they were backed up:

                           

                          list backup of archivelog sequence between 23636 and 23642;
                          

                           

                          If they are backed up then restore them and then copy them over:

                           

                          restore archivelog from sequence 23636 until sequence 23642 thread 1;
                          
                          1 person found this helpful
                          • 10. Re: Archive logs gap between Primary and Standby issue.
                            sweetritz

                            @FreddieEssex thanks for the info ...here is the output of the command you mentioned:

                             

                            RMAN> list backup of archivelog sequence between 23636 and 23642;

                             

                             

                            using target database control file instead of recovery catalog

                             

                             

                            List of Backup Sets

                            ===================

                             

                             

                            BS Key  Size       Device Type Elapsed Time Completion Time

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

                            5698    19.36M     DISK        00:00:10     28-MAY-14

                                    BP Key: 32704   Status: AVAILABLE  Compressed: YES  Tag: TAG20140528T223134

                                    Piece Name: /rman_backup/zeus/d_zeus_s_4101_p_1_t_848788338.bkp

                             

                             

                              List of Archived Logs in backup set 3987

                              Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

                              1    23641   12018906974138 28-MAY-14 12018906981678 28-MAY-14

                              1    23642   12018906981678 28-MAY-14 12018907028520 28-MAY-14

                             

                            So it list the sequence 23641 and 23642 alone. Does it mean other sequence backup doesnt exist?

                            • 11. Re: Archive logs gap between Primary and Standby issue.
                              FreddieEssex

                              Yes...that looks to be the case.

                               

                              You can also use the following to check:

                               

                              list backup of archivelog sequence = 23636;
                              

                               

                              and it should return a message like "specification does not match backup"

                               

                              Looks like an incremental backup will be your best bet.

                              • 12. Re: Archive logs gap between Primary and Standby issue.
                                sweetritz

                                @FreddieEssex yea. that worked. I googled the method you mentioned in the above comment and did the operation mentioned below:

                                 

                                In the standby DB:

                                 

                                 

                                $ . oraenv

                                ORACLE_SID = [oracle] ? zeusSB

                                The Oracle base for ORACLE_HOME=/app/oracle/product/10.2.0.5/db_1 is /app/oracle/product

                                $ sqlplus / as sysdba

                                 

                                 

                                SQL*Plus: Release 10.2.0.5.0 - Production on Fri May 30 09:08:47 2014

                                 

                                 

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

                                 

                                 

                                Connected to:

                                Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

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

                                SQL>  col current_scn format 99999999999999999999

                                SQL> select current_scn from v$database;

                                 

                                 

                                          CURRENT_SCN

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

                                       12018913549581

                                 

                                 

                                SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

                                 

                                 

                                In the Primary DB:

                                $ rman

                                 

                                 

                                Recovery Manager: Release 10.2.0.5.0 - Production on Fri May 30 09:11:11 2014

                                 

                                 

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

                                 

                                 

                                RMAN> connect target /

                                 

                                 

                                connected to target database: zeus (DBID=9856426998)

                                 

                                 

                                RMAN> connect catalog rcat_user/rcat@rdb; (Here I am not sure of the catalog information, need to confirm with you)

                                 

                                 

                                RMAN> BACKUP INCREMENTAL FROM SCN 12018913549581 DATABASE FORMAT '/rmanbp/zeus/forSBDB_%U' tag 'forSBDB';

                                RMAN> exit

                                 

                                 

                                In standby DB:

                                $ rman target / nocatalog

                                 

                                 

                                RMAN> catalog start with '/rmanbp/zeus/forSBDB’;

                                Do you really want to catalog the above files (enter YES or NO)? yes

                                RMAN> shutdown immediate;

                                RMAN> startup mount;

                                RMAN> RECOVER DATABASE NOREDO;

                                RMAN> exit

                                SQL> shutdown imediate;

                                SQL> startup nomount;

                                SQL> alter database mount standby database;

                                SQL> alter database recover managed standby database disconnect from session;

                                 

                                 

                                SQL> shutdown immediate;

                                 

                                 

                                resend the primary controlfile

                                 

                                 

                                In primary DB:

                                >rman

                                RMAN> connect target /

                                RMAN> connect catalog rcat_user/rcat@rdb;

                                RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/forSBDBCTRL.bck';

                                RMAN> exit

                                 

                                 

                                Copy the backed up control file of primary DB to star location:

                                 

                                 

                                scp /tmp/forSBDBCTRL.bck DRsite:/tmp

                                 

                                 

                                In stand by DB:

                                 

                                 

                                $ rman target /

                                connected to target database (not started)

                                RMAN> startup nomount

                                RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/forSBDBCTRL.bck';

                                RMAN> shutdown immediate;

                                RMAN> startup mount;

                                RMAN>exit

                                 

                                 

                                $ sqlplus / as sysdba

                                 

                                 

                                SQL> shutdown immediate;

                                 

                                 

                                SQL> startup nomount

                                 

                                 

                                SQL> alter database mount standby database;

                                 

                                 

                                SQL> alter database recover managed standby database disconnect from session;

                                 

                                 

                                Thank you so much for the help

                                Regards,

                                 

                                Ritu