This discussion is archived
7 Replies Latest reply: Nov 4, 2011 2:01 AM by 892831 RSS

Archive Gap - Physical Standby Database

892831 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Done....Thanks.

Legend

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