This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Mar 12, 2013 12:08 AM by MahirM.Quluzade RSS

In Standby, applied=No and deleted=Yes

nww Newbie
Currently Being Moderated
Hi All,

I have a question about the table v$archived_log in standby.

From the table v$archived_log in Standby, I found some records that were not applied but deleted. However, there are entries of the same sequence# which showing applied and deleted. How an archived log got deleted before being applied? What are they?
RECID      STAMP  SEQUENCE#    THREAD# CREATOR REGISTR APP DEL FIRST_TIME      NEXT_TIME       COMPLETION_TIME
206261  809266472     267281          1 LGWR    RFS     NO  YES 130305 12:14:19 130305 12:14:23 130305 12:14:32
206263  809266481     267281          1 ARCH    RFS     YES YES 130305 12:14:19 130305 12:14:23 130305 12:14:41
206258  809266444     267279          1 LGWR    RFS     NO  YES 130305 12:13:54 130305 12:13:58 130305 12:14:04
206259  809266452     267279          1 ARCH    RFS     YES YES 130305 12:13:54 130305 12:13:58 130305 12:14:12
206254  809266421     267276          1 LGWR    RFS     NO  YES 130305 12:13:26 130305 12:13:30 130305 12:13:41
206255  809266429     267276          1 ARCH    RFS     YES YES 130305 12:13:26 130305 12:13:30 130305 12:13:49
206238  809266260     267261          1 LGWR    RFS     NO  YES 130305 12:10:49 130305 12:10:53 130305 12:11:00
206239  809266274     267261          1 ARCH    RFS     YES YES 130305 12:10:49 130305 12:10:53 130305 12:11:14
202822  807796491     263851          1 LGWR    RFS     NO  YES 130219 11:54:09 130219 11:54:29 130219 11:54:51
202825  807796499     263851          1 ARCH    RFS     YES YES 130219 11:54:09 130219 11:54:29 130219 11:54:59
202492  807790205     263517          1 LGWR    RFS     NO  YES 130219 10:09:53 130219 10:09:58 130219 10:10:05
202493  807790215     263517          1 ARCH    RFS     YES YES 130219 10:09:53 130219 10:09:58 130219 10:10:15
202466  807790084     263492          1 LGWR    RFS     NO  YES 130219 10:07:45 130219 10:07:50 130219 10:08:04
202468  807790096     263492          1 ARCH    RFS     YES YES 130219 10:07:45 130219 10:07:50 130219 10:08:16
Regards,
nww
  • 1. Re: In Standby, applied=No and deleted=Yes
    Shivananda Rao Guru
    Currently Being Moderated
    Hello,
    RECID      STAMP  SEQUENCE#    THREAD# CREATOR REGISTR APP DEL FIRST_TIME      NEXT_TIME       COMPLETION_TIME
    206261  809266472     267281          1 LGWR    RFS     NO  YES 130305 12:14:19 130305 12:14:23 130305 12:14:32
    206263  809266481     267281          1 ARCH    RFS     YES YES 130305 12:14:19 130305 12:14:23 130305 12:14:41
    Sequence# with status being shown as applied "YES" would be associated with destination ID 2 (pointing to the standby database) and APPLIED="NO" would be the sequence associated with Destination ID 1 on the primary database.

    These archives would have been deleted after taking backup on primary and which is why the status is being show as "YES" under the deleted column but there is nothing unusual in what you have posted. You can add the "dest_id" column in the query you ran to get more clear picture.


    Regards,
    Shivananda
  • 2. Re: In Standby, applied=No and deleted=Yes
    nww Newbie
    Currently Being Moderated
    Hi,

    Thanks for your reply.
    I did a select from both primary and standby db and got the following records. Now, I have 2 questions.
    (1) Why are there 2 records for DEST_ID=2 from the primary?
    (2) What is the applied=NO and deleted=YES record from the standby?
    [PRIMARY]
    NAME        RECID  STAMP     DEST_ID SEQUENCE# THREAD# CREATOR REGISTR APP DEL FIRST_TIME      NEXT_TIME       COMPLETION_TIME
    ----------- ------ --------- ------- --------- ------- ------- ------- --- --- --------------- --------------- ---------------
                621660 809266466       1    267281       1 ARCH    ARCH    NO  YES 130305 12:14:19 130305 12:14:23 130305 12:14:26
    ORACLEDB_SB 621662 809266472       2    267281       1 LGWR    LGWR    NO  NO  130305 12:14:19 130305 12:14:23 130305 12:14:32
    ORACLEDB_SB 621664 809266481       2    267281       1 ARCH    ARCH    NO  NO  130305 12:14:19 130305 12:14:23 130305 12:14:41
    
    [STANDBY]
    NAME        RECID  STAMP     DEST_ID SEQUENCE# THREAD# CREATOR REGISTR APP DEL FIRST_TIME      NEXT_TIME       COMPLETION_TIME
    ----------- ------ --------- ------- --------- ------- ------- ------- --- --- --------------- --------------- ---------------
                206261 809266472       2    267281       1 LGWR    RFS     NO  YES 130305 12:14:19 130305 12:14:23 130305 12:14:32
                206263 809266481       2    267281       1 ARCH    RFS     YES YES 130305 12:14:19 130305 12:14:23 130305 12:14:41
    Take a look to another archived log. Database backup was executed in primary. This makes more sense to me.
    [PRIMARY]
    NAME                 RECID  STAMP     DEST_ID SEQUENCE# THREAD# CREATOR REGISTR APP DEL FIRST_TIME      NEXT_TIME       COMPLETION_TIME
    -------------------- ------ --------- ------- --------- ------- ------- ------- --- --- --------------- --------------- ---------------
                         623279 809350827       1    268090       1 ARCH    ARCH    NO  YES 130306 11:30:22 130306 11:40:24 130306 11:40:27
                         623280 809350828       2    268090       1 LGWR    LGWR    YES NO  130306 11:30:22 130306 11:40:24 130306 11:40:28
    
    [STANDBY]
    NAME                 RECID  STAMP     DEST_ID SEQUENCE# THREAD# CREATOR REGISTR APP DEL FIRST_TIME      NEXT_TIME       COMPLETION_TIME
    -------------------- ------ --------- ------- --------- ------- ------- ------- --- --- --------------- --------------- ---------------
    log/2013_03_06/o1_mf 207071 809350828       2    268090       1 LGWR    RFS     YES NO  130306 11:30:22 130306 11:40:24 130306 11:40:28
    _1_268090_8mfg2m4h_.
    arc
    Look at an even older archived log that was applied and deleted in standby. Name field was updated.
    [PRIMARY]
    NAME        RECID  STAMP     DEST_ID SEQUENCE# THREAD# CREATOR REGISTR APP DEL FIRST_TIME      NEXT_TIME       COMPLETION_TIME
    ----------- ------ --------- ------- --------- ------- ------- ------- --- --- --------------- --------------- ---------------
                623084 809325880       1    267992       1 ARCH    ARCH    NO  YES 130306 04:44:13 130306 04:44:37 130306 04:44:40
    ORACLEDB_SB 623083 809325878       2    267992       1 LGWR    LGWR    YES NO  130306 04:44:13 130306 04:44:37 130306 04:44:38
    
    [STANDBY]
    NAME        RECID  STAMP     DEST_ID SEQUENCE# THREAD# CREATOR REGISTR APP DEL FIRST_TIME      NEXT_TIME       COMPLETION_TIME
    ----------- ------ --------- ------- --------- ------- ------- ------- --- --- --------------- --------------- ---------------
                206973 809325878       2    267992       1 LGWR    RFS     YES YES 130306 04:44:13 130306 04:44:37 130306 04:44:38
    Edited by: nww on Mar 6, 2013 2:20 PM

    Edited by: nww on Mar 6, 2013 2:28 PM
  • 3. Re: In Standby, applied=No and deleted=Yes
    Shivananda Rao Guru
    Currently Being Moderated
    [STANDBY]
    NAME        RECID  STAMP     DEST_ID SEQUENCE# THREAD# CREATOR REGISTR APP DEL FIRST_TIME      NEXT_TIME       COMPLETION_TIME
    ----------- ------ --------- ------- --------- ------- ------- ------- --- --- --------------- --------------- ---------------
                206973 809325878       2    267992       1 LGWR    RFS     YES YES 130306 04:44:13 130306 04:44:37 130306 04:44:38
    Is DEST_ID "1" not being used on the standby database ?
    From the standby database, please post the outcome of these:
    SQL>show parameter dest_1
    SQL>show parameter dest_2
    Regards,
    Shivananda
  • 4. Re: In Standby, applied=No and deleted=Yes
    nww Newbie
    Currently Being Moderated
    Output from Standby.
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_create_online_log_dest_1          string      +ORACLEDB_DG
    log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                     DEST  VALID_FOR=(ALL_LOGFILES,
                                                     ALL_ROLES) DB_UNIQUE_NAME=ORAC
                                                     LEDB_SB
    log_archive_dest_10                  string
    SQL> show parameter dest_2
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_create_online_log_dest_2          string
    log_archive_dest_2                   string      SERVICE=ORACLEDB LGWR ASYNC VA
                                                     LID_FOR=(ONLINE_LOGFILES,PRIMA
                                                     RY_ROLE) DB_UNIQUE_NAME=ORACLE
                                                     DB
  • 5. Re: In Standby, applied=No and deleted=Yes
    Sunny kichloo Expert
    Currently Being Moderated
    Also share the output of

    log_archive_dest_state_1 and log_archive_dest_state_2
  • 6. Re: In Standby, applied=No and deleted=Yes
    nww Newbie
    Currently Being Moderated
    Here you are.
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_state_1             string      ENABLE
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_state_2             string      ENABLE
  • 7. Re: In Standby, applied=No and deleted=Yes
    Acooper Explorer
    Currently Being Moderated
    Post the RMAN settings for

    Archivelog Deletion Policy

    For both Standby and Primary
  • 8. Re: In Standby, applied=No and deleted=Yes
    nww Newbie
    Currently Being Moderated
    I found that both primary and standby are having the same deletion policy.
    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
  • 9. Re: In Standby, applied=No and deleted=Yes
    nww Newbie
    Currently Being Moderated
    I have changed the deletion policy to NONE in primary and keep the DELETE APPLIED ON STANDBY in standby. Let's see if I will still get those wired records similar to sequence#=267281, for example.
  • 10. Re: In Standby, applied=No and deleted=Yes
    Acooper Explorer
    Currently Being Moderated
    Bad idea.

    If you have an issue shipping redo from the Primary to the Standby you could delete Archive before the Standby ever sees it.

    You should change it back ASAP.
  • 11. Re: In Standby, applied=No and deleted=Yes
    nww Newbie
    Currently Being Moderated
    It's because backups of archived redo log files are taken on the primary database. Below is extracted from Oracle online document.
    When backups of archived redo log files are taken on the standby database:
    
    Issue the following command on the primary database: 
    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
    
    
    Issue the following command on the standby database: 
    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
    
    
    When backups of archived redo log files are taken on the primary database:
    
    Issue the following command on the standby database: 
    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
    
    
    Issue the following command on the primary database: 
    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
  • 12. Re: In Standby, applied=No and deleted=Yes
    nww Newbie
    Currently Being Moderated
    After restarting the standby, those "not applied but deleted" records were removed from the v$archived_log table!!!
    Anyone have any idea?
  • 13. Re: In Standby, applied=No and deleted=Yes
    CKPT Guru
    Currently Being Moderated
    I see this question was from long time, Can you please provide me output of below query from both primary and standby?
    set linesize 200
    select ds.dest_id id
    , ad.status
    , ds.database_mode db_mode
    , ad.archiver type
    , ds.recovery_mode
    , ds.protection_mode
    , ds.standby_logfile_count "SRLs"
    , ds.standby_logfile_active active
    , ds.archived_seq#
    from v$archive_dest_status ds
    , v$archive_dest ad
    where ds.dest_id = ad.dest_id
    and ad.status != 'INACTIVE'
    order by
    ds.dest_id;
    also from both primary and standby

    SQL> select dest_id,thread#,sequence#,creator,registrar,applied from v$archived_log;
  • 14. Re: In Standby, applied=No and deleted=Yes
    nww Newbie
    Currently Being Moderated
    Sorry for my late reply.

    Primary
            ID STATUS    DB_MODE         TYPE       RECOVERY_MODE           PROTECTION_MODE            SRLs     ACTIVE ARCHIVED_SEQ#
    ---------- --------- --------------- ---------- ----------------------- -------------------- ---------- ---------- -------------
             1 VALID     OPEN            ARCH       IDLE                    MAXIMUM PERFORMANCE           0          0        269209
             2 VALID     MOUNTED-STANDBY LGWR       MANAGED                 MAXIMUM PERFORMANCE           0          0        269205
    Standby
            ID STATUS    DB_MODE         TYPE       RECOVERY_MODE           PROTECTION_MODE            SRLs     ACTIVE ARCHIVED_SEQ#
    ---------- --------- --------------- ---------- ----------------------- -------------------- ---------- ---------- -------------
             1 VALID     MOUNTED-STANDBY ARCH       MANAGED                 MAXIMUM PERFORMANCE           0          0             0
             2 VALID     MOUNTED-STANDBY LGWR       MANAGED                 MAXIMUM PERFORMANCE           0          0             0
            11 VALID     MOUNTED-STANDBY ARCH       MANAGED                 MAXIMUM PERFORMANCE           0          0        269208
1 2 Previous Next

Legend

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