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

Thread: v$archived_log in standby database

  1. #11
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by Axr2

    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;
    I don't think this query is accurate. Right now, by alert.log says:
    Code:
    Media Recovery Log /u07/oraarch/tstore1/1_37032.dbf
    Mon Sep 13 16:51:25 2004
    Media Recovery Waiting for thread 1 seq# 37033
    Media Recovery Log /u07/oraarch/tstore1/1_37033.dbf
    Mon Sep 13 17:21:22 2004
    Media Recovery Waiting for thread 1 seq# 37034
    and this query shows:
    Code:
    system@tstore1.us> l
      1  select ads.dest_id,max(al.sequence#) "Current Sequence",max(ad.log_sequence) "Last Archived",
      2  max(ads.applied_seq#) "Last Sequence Applied"
      3  from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
      4  where ad.dest_id=al.dest_id
      5  and al.dest_id=ads.dest_id
      6* group by ads.dest_id
    system@tstore1.us> /
     
       DEST_ID Current Sequence Last Archived Last Sequence Applied
    ---------- ---------------- ------------- ---------------------
             1            37033         37033                     0
             2            37033         37033                 37032
    The query is telling me 37032 was the last applied and the alert.log is telling me 37033 was applied.
    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. #12
    Join Date
    Oct 2002
    Posts
    807
    Issue
    select max(sequence#) from v$archived_log where applied='YES';
    on the standby then.

    Am still not sure about the descrepancy in sequence #s in the query ouput you listed. I would have to test some more.

    Switch some logfiles manually, then issue the below on the standby.

    select process,status,client_process,sequence#,block#,active_agents,known_agents
    from v$managed_standby;

    It'll give you an exact picture of what's happening on the standby.

  3. #13
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    what about V$ARCHIVE_GAP?

    That might help. Also consider that FAL does work so that view
    may give you false alarms

    for instance

    log 1 shippied and applied to standby
    log 2 starts to ship and network hiccups, log does not get sent
    when log 3 is generated it will detect the gap and send log 3 and log two

    In that instance it's possible that log three gets received first and hence a gap may be detected.
    I'm stmontgo and I approve of this message

  4. #14
    Join Date
    Oct 2001
    Posts
    45
    How can I track that this was satisfied by FAL request?
    1.Do you set FAL parameters in your standby init.ora? ('FAL_SERVER' & 'FAL_CLIENT')
    2.FAL is running on primary server. Check you primary database alert.log, you should be able to see some entries like: 'ARC0: Begin FAL archive','ARC0: Complete FAL archive...
    3.If no FAL parameters set, ARCH also can automatically send missing archived logs to standby db.

    Both the primary and standby were up and down during this period, but I never manually recovered anything.
    Most of time, you donít have to manually recovered/sync standby db as Oracle automatically resolves the gap issues. (oracle 9i)

    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.
    Again, it comes back to your original question. You may refer to oracle article '263994.1' for the answer.

    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.
    For me, I always query the max(sequence#) from V$archived_log in primary db to get the latest logs applied. Sometime I also need to verify it in alert.log if any doubt. As noted in oracle article 150214.1, you canít trust V$archived_log 100%.

    In article 150214.1---
    'Note: the sql statements give additional information (confirmation)of information of alert files. If these sql statements return rows it doesn't necessarily mean ther's an actual gap! Always check the alert files as in 1b.'

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