v$archived_log in standby database
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: v$archived_log in standby database

  1. #1
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    v$archived_log in standby database

    I have a simple 2 node standby setup. On my primary, I query v$archived_log for my secondary destination and it correctly shows the logs that have been archived and applied on the standby. However, the applied flag is not set for some logs, but the alert.log on the standby says it was applied.

    Code:
      1  select sequence#, dest_id, archived, applied, completion_time
      2  from v$archived_log
      3  where dest_id = 2
      4  and sequence# > 36925
      5* order by sequence#
    system@xxx.us> /
    
     SEQUENCE#    DEST_ID ARC APP COMPLETION_TIME
    ---------- ---------- --- --- -------------------
         36926          2 YES YES 09/12/2004 20:17:46
         36927          2 YES YES 09/12/2004 20:18:09
         36928          2 YES YES 09/12/2004 20:18:36
         36929          2 YES YES 09/12/2004 20:18:59
         36930          2 YES NO  09/12/2004 20:25:49
         36931          2 YES YES 09/12/2004 20:25:51
         36932          2 YES YES 09/12/2004 20:56:02
         36933          2 YES YES 09/12/2004 21:26:29
         36934          2 YES YES 09/12/2004 21:56:55
         36935          2 YES YES 09/12/2004 22:27:21
         36936          2 YES YES 09/12/2004 22:57:47
         36937          2 YES YES 09/12/2004 23:28:12
    
    12 rows selected.
    
    Elapsed: 00:00:00.21
    
    
    Media Recovery Waiting for thread 1 seq# 36922
    Media Recovery Log /u07/oraarch/xxx/1_36922.dbf
    Media Recovery Waiting for thread 1 seq# 36923
    Media Recovery Log /u07/oraarch/xxx/1_36923.dbf
    Media Recovery Waiting for thread 1 seq# 36924
    Media Recovery Log /u07/oraarch/xxx/1_36924.dbf
    Media Recovery Waiting for thread 1 seq# 36925
    Media Recovery Log /u07/oraarch/xxx/1_36925.dbf
    Media Recovery Log /u07/oraarch/xxx/1_36926.dbf
    Media Recovery Log /u07/oraarch/xxx/1_36927.dbf
    Media Recovery Log /u07/oraarch/xxx/1_36928.dbf
    Media Recovery Log /u07/oraarch/xxx/1_36929.dbf
    Media Recovery Log /u07/oraarch/xxx/1_36930.dbf
    Media Recovery Log /u07/oraarch/xxx/1_36931.dbf
    Media Recovery Waiting for thread 1 seq# 36932
    Media Recovery Log /u07/oraarch/xxx/1_36932.dbf
    Media Recovery Waiting for thread 1 seq# 36933
    Media Recovery Log /u07/oraarch/xxx/1_36933.dbf
    Media Recovery Waiting for thread 1 seq# 36934
    Media Recovery Log /u07/oraarch/xxx/1_36934.dbf
    Media Recovery Waiting for thread 1 seq# 36935
    Media Recovery Log /u07/oraarch/xxx/1_36935.dbf
    Media Recovery Waiting for thread 1 seq# 36936
    Media Recovery Log /u07/oraarch/xxx/1_36936.dbf
    Media Recovery Waiting for thread 1 seq# 36937
    Media Recovery Log /u07/oraarch/xxx/1_36937.dbf
    Media Recovery Waiting for thread 1 seq# 36938
    
    
    $ r ls
    ls -l /u07/oraarch/xxx/1_36930.dbf
    -rw-r-----   1 oracle   dba      32668160 Sep 12 20:25 /u07/oraarch/xxx1/1_36930.dbf
    Can anybody enlighten me or point me to some documentation that explains this?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  2. #2
    Join Date
    Oct 2001
    Posts
    45
    When the sequence no, in your case is 36930, was not able to send to standby due to whatever reason, it will be sent later on by an ARCH process as per FAL request. In this case, V$ARCHIVED_LOG.APPLIED column of the primary node won't be updated thus it still shows '
    NO' status but actually it already applied in standby db.
    Last edited by xiaomao; 09-13-2004 at 05:56 AM.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    How can I track that this was satisfied by FAL request?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    I've seen this if I interrupt the automatic recovery process , manually apply logs and then resume sustained recovery. Not sure if that applies in your case.

    Steve
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by stmontgo
    I've seen this if I interrupt the automatic recovery process , manually apply logs and then resume sustained recovery. Not sure if that applies in your case.

    Steve
    Both the primary and standby were up and down during this period, but I never manually recovered anything.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Oct 2002
    Posts
    807
    Are you using data guard?

  7. #7
    Join Date
    Oct 2002
    Posts
    807
    Originally posted by marist89
    How can I track that this was satisfied by FAL request?
    Not sure I understand the above. In your first post you said -

    On my primary, I query v$archived_log for my secondary destination and it correctly shows the logs that have been archived and applied on the standby.
    That pretty much means it was satisfied by the FAL right?

    In any case, here are some queries. See if they help your cause.

    COLUMN destination FORMAT A35 WRAP
    column process format a7
    column archiver format a8
    column ID format 99
    select dest_id "ID",destination,status,target,
    archiver,schedule,process,mountid
    from v$archive_dest;
    select dest_id,process,transmit_mode,async_blocks,
    net_timeout,delay_mins,reopen_secs,register,binding
    from v$archive_dest;
    column error format a55 tru
    select dest_id,status,error from v$archive_dest;
    column message format a80
    select message, timestamp
    from v$dataguard_status
    where severity in ('Error','Fatal')
    order by timestamp;
    select ads.dest_id,max(sequence#) "Current Sequence",max(log_sequence) "Last Archived",
    max(applied_seq#) "Last Sequence Applied"
    from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
    where ad.dest_id=al.dest_id
    and al.dest_id=ads.dest_id
    group by ads.dest_id;
    column SRL_Count format 99
    column SRL_Active format 99
    select dest_id,database_mode,recovery_mode,
    protection_mode,standby_logfile_count "SRL_COUNT",standby_logfile_active "SRL_ACTIVE",
    archived_seq#, applied_seq#
    from v$archive_dest_status;
    select process,status,client_process,sequence#,block#,active_agents,known_agents
    from v$managed_standby;
    select group#,sequence#,bytes from v$standby_log;
    select group#,thread#,sequence#,bytes,archived,status from v$log;
    Last edited by Axr2; 09-13-2004 at 05:44 PM.

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Axr2
    Are you using data guard?
    no
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Axr2
    Not sure I understand the above. In your first post you said -



    I'm sure it was applied, but not sure how it was applied. The alert.log of the standby shows it was applied, but v$archived_log doesn't.

    I guess my big question is how can I verify all my logs have been applied without looking at the alert.log on the standby.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Oct 2002
    Posts
    807
    Originally posted by marist89

    I guess my big question is how can I verify all my logs have been applied without looking at the alert.log on the standby. [/B]
    See first sql in

    http://www.dbasupport.com/forums/sho...threadid=44415

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width