7 Replies Latest reply: Nov 4, 2011 4:01 AM by 892831 RSS

    Archive Gap - Physical Standby Database

    892831
      Hello All,

      I'm seeing some archive log gap in physical standby database.

      SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

      THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
      ---------- ------------- --------------
      1 7353 7355


      But when I query the above gaps in primary I'm not able to see any thing

      SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 7353 AND 7355;

      NAME
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


      Can any one please help, How to get those files..since i need to copy those missing files from primary to standby and recover the standby database to sync up with primary.

      Oracle 11gR2 in RHEL5 ( Primary : 2 node RAC , Stdby : Standalone - Physical Standby)
        • 1. Re: Archive Gap - Physical Standby Database
          CKPT
          post some queries output

          from primary:-

          SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
          SQL> select name from v$archived_log where thread#1 and sequence# > 7352 and sequence# < 7356;
          SQL> show parameter state_2

          do you have backup of these three archives in primary? check from RMAN

          from standby:-
          SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
          SQL> select * from v$archive_gap;

          post errors from alert logfile.
          • 2. Re: Archive Gap - Physical Standby Database
            892831
            Hello CKPT ..Thanks a lot for the response, Pelase find below output.

            from primary:-

            SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

            THREAD# MAX(SEQUENCE#)
            ---------- --------------
            1 8041
            2 4496


            SQL> select name from v$archived_log where thread#=1 and sequence# > 7352 and sequence# < 7356;

            NAME
            ----------------------------------------------------------------------

            BWFCCSTD
            BWFCCSTD

            BWFCCSTD


            6 rows selected.


            SQL> show parameter state_2;

            NAME TYPE VALUE
            ------------------------------------ --------------------------------- ------------------------------
            log_archive_dest_state_2 string ENABLE

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

            do you have backup of these three archives in primary? check from RMAN -- Yes

            from standby:-
            SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

            THREAD# MAX(SEQUENCE#)
            ---------- --------------
            1 7352
            2 3994

            SQL> select * from v$archive_gap;

            THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
            ---------- ------------- --------------
            1 7353 7355

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

            post errors from alert logfile:
            PRIMARY_LOG:
            ===========
            Archived Log entry 13265 added for thread 1 sequence 8038 ID 0x4905a9f1 dest 1:
            Mon Oct 31 22:57:29 2011
            ALTER SYSTEM SET service_names='SYS$SYS.KUPC$C_1_20111031225729.BWFCCPRD' SCOPE=MEMORY SID='BWFCCPRD1';
            ALTER SYSTEM SET service_names='SYS$SYS.KUPC$C_1_20111031225729.BWFCCPRD','SYS$SYS.KUPC$S_1_20111031225729.BWFCCPRD' SCOPE=MEMORY SID='BWFCCPRD1';
            Mon Oct 31 22:57:31 2011
            DM00 started with pid=99, OS id=26875, job BWFCCUBSEXP.SYS_EXPORT_FULL_01
            Mon Oct 31 22:57:33 2011
            DW00 started with pid=102, OS id=26883, wid=1, job BWFCCUBSEXP.SYS_EXPORT_FULL_01
            Mon Oct 31 23:08:30 2011
            Thread 1 advanced to log sequence 8040 (LGWR switch)
            Current log# 10 seq# 8040 mem# 0: +ASM_DATA01/bwfccprd/onlinelog/group_10.349.765971215
            Mon Oct 31 23:08:42 2011
            Archived Log entry 13269 added for thread 1 sequence 8039 ID 0x4905a9f1 dest 1:
            Mon Oct 31 23:33:40 2011
            ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20111031225729.BWFCCPRD' SCOPE=MEMORY SID='BWFCCPRD1';
            ALTER SYSTEM SET service_names='BWFCCPRD' SCOPE=MEMORY SID='BWFCCPRD1';
            Tue Nov 01 00:59:59 2011
            Thread 1 advanced to log sequence 8041 (LGWR switch)
            Current log# 11 seq# 8041 mem# 0: +ASM_FRA01/bwfccprd/onlinelog/group_11.10359.765971235
            Tue Nov 01 01:00:02 2011
            Archived Log entry 13274 added for thread 1 sequence 8040 ID 0x4905a9f1 dest 1:
            Tue Nov 01 04:00:00 2011
            Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
            Tue Nov 01 04:00:00 2011
            Starting background process VKRM
            Tue Nov 01 04:00:00 2011
            VKRM started with pid=36, OS id=18963
            Tue Nov 01 07:00:03 2011
            Closing Resource Manager plan via scheduler window
            Clearing Resource Manager plan via parameter
            Tue Nov 01 07:27:31 2011
            Thread 1 advanced to log sequence 8042 (LGWR switch)
            Current log# 10 seq# 8042 mem# 0: +ASM_DATA01/bwfccprd/onlinelog/group_10.349.765971215
            Tue Nov 01 07:27:32 2011
            Archived Log entry 13282 added for thread 1 sequence 8041 ID 0x4905a9f1 dest 1:
            Tue Nov 01 10:45:12 2011
            Global Enqueue Services Deadlock detected. More info in file
            /u01/app/oracle/diag/rdbms/bwfccprd/BWFCCPRD1/trace/BWFCCPRD1_lmd0_19561.trc.
            Tue Nov 01 12:58:23 2011
            Global Enqueue Services Deadlock detected. More info in file
            /u01/app/oracle/diag/rdbms/bwfccprd/BWFCCPRD1/trace/BWFCCPRD1_lmd0_19561.trc.


            STDBY_LOG:
            ==========
            Tue Nov 01 13:44:18 2011
            ARCH: Archival stopped, error occurred. Will continue retrying
            ORACLE Instance BWFCCSTD - Archival Error
            ORA-16014: log 7 sequence# 4486 not archived, no available destinations
            ORA-00312: online log 7 thread 2: '+ASM_DATA01/bwfccstd/onlinelog/group_7.387.765985859'
            Tue Nov 01 13:44:18 2011
            Fetching gap sequence in thread 1, gap sequence 7353-7355
            Tue Nov 01 13:48:17 2011
            Fetching gap sequence in thread 1, gap sequence 7353-7355
            • 3. Re: Archive Gap - Physical Standby Database
              CKPT
              it looks these three sequences not exist in primary database, Do you have any RMAN archive backup which contains archives?

              check from standby:-
              SQL> select thread#,sequence#,name from v$archived_log where thread#=1 and sequence# > 7352 and sequence# < 7356;

              If any of the archives not exist in primary or standby or if you do not have any backup of archives, You have to perform incremental SCN = ROLLFORWARD method to syncronize standby database.
              ORACLE Instance BWFCCSTD - Archival Error
              your stnadby database FRA is full, increase size or delete older archives which are already applied.



              select     name
              ,     floor(space_limit / 1024 / 1024) "Size MB"
              ,     ceil(space_used / 1024 / 1024) "Used MB"
              from     v$recovery_file_dest
              order by name
              /

              Edited by: CKPT on Nov 1, 2011 5:42 PM
              • 4. Re: Archive Gap - Physical Standby Database
                892831
                I could see the three logs available trough RMAN

                BS Key Size Device Type Elapsed Time Completion Time
                ------- ---------- ----------- ------------ ---------------
                198 513.37M DISK 00:01:16 26-OCT-11
                BP Key: 198 Status: AVAILABLE Compressed: YES Tag: TAG20111026T110506
                Piece Name: /oracle/dpdump/RMAN/BWFCCPRD/arc_BWFCCPRD_765545369_212_1

                List of Archived Logs in backup set 198
                Thrd Seq Low SCN Low Time Next SCN Next Time
                ---- ------- ---------- --------- ---------- ---------
                1 7353 41863348061 24-OCT-11 41863435625 24-OCT-11
                1 7354 41863435625 24-OCT-11 41863436282 24-OCT-11
                1 7355 41863436282 24-OCT-11 41863436361 24-OCT-11

                =====

                SQL> select thread#,sequence#,name from v$archived_log where thread#=1 and sequence# > 7352 and sequence# < 7356;

                no rows selected

                ====

                SQL> select name
                , floor(space_limit / 1024 / 1024) "Size MB"
                , ceil(space_used / 1024 / 1024) "Used MB"
                from v$recovery_file_dest
                order by name 2 3 4 5 ;

                NAME Size MB Used MB
                -------------------- ---------- ----------
                +ASM_FRA01                70000         35

                SQL>

                ===

                How to porceed, kindly help me ...
                • 5. Re: Archive Gap - Physical Standby Database
                  Shivananda Rao
                  Try connecting your standby database and the catalog database through RMAN and try to recover the standby database.

                  RMAN> connect target sys/<pwd>@standby

                  RMAN> connect catalog rman_user/<rman_pwd>@catalog

                  RMAN> recover database;

                  Edited by: srp on Nov 1, 2011 7:44 PM
                  • 6. Re: Archive Gap - Physical Standby Database
                    CKPT
                    Restore those archives, and start MRP it should fix the problem.
                    Make sure that backup piece physically exist.
                    • 7. Re: Archive Gap - Physical Standby Database
                      892831
                      Done....Thanks.