9i Standby Monitoring
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: 9i Standby Monitoring

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

    9i Standby Monitoring

    I have setup a simple physical standby database using Oracle 9.2.0.5. My primary database spits out archived redo logs at the rate of about 128M/minute. Because I don't want to affect the performance of the primary db, I am not using lgwr to write to both destinations.

    The logs get generated on the primary. They get transferred to the standby fine. However, the standby takes a while to apply them during the managed recovery process. For example, my primary db has generated logs 100-150 and they are sitting on disk on my primary box. Also, logs 100-150 are sitting on my standby box as they have been transferred correctly. However, my managed recovery on the standby is only on "Media Recovery Log ...1_135.log.

    How do I monitor the lag between what's been transferred and what's been applied? I looked at v$archived_log where dest_id = 1 and dest_id=2, but they were in sync because the logs had already been shipped to the standby successfully. There was nothing in v$archive_gap as the primary doesn't think anything else needs to be transferred. I am not using the Data Guard tools at the moment. Any hints?
    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 2002
    Posts
    807
    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;

    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;
    Last edited by Axr2; 08-27-2004 at 02:22 PM.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Actually, I was just looking for v$archived_log.applied, but these scripts look interesting as well....
    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
    Oct 2002
    Posts
    807
    There's a lot of stuff/scripts on metalink that you'll start to see once you start having problems with the standby. Enjoy .

    DG works well. Might as well use it.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Axr2
    There's a lot of stuff/scripts on metalink that you'll start to see once you start having problems with the standby. Enjoy .

    Thanks, I'll look there...


    DG works well. Might as well use it.
    Understood. Just want to make sure I really understand what's happening when the s$@t hits the fan.

    How do you handle your archived redo logs on the standby? I could delete them via an OS process once they have been applied, but is there a way to tell the database to do it (v$archived_log.deleted)?
    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
    I use OS process. I don't think there is a way to tell the database to do it.

  7. #7
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by marist89

    Thanks, I'll look there...


    Understood. Just want to make sure I really understand what's happening when the s$@t hits the fan.

    How do you handle your archived redo logs on the standby? I could delete them via an OS process once they have been applied, but is there a way to tell the database to do it (v$archived_log.deleted)? [/B]
    I'm late to the party on this one but I just finished some testing on backing up archive logs on the standby. I did it with RMAN.
    I have further testing to complete but I can keep you in the loop if you like. Essentially I connected to the recovery catalog remotely and the target (the stanbdy) locally. As I understand it you do not register the standby in the catalog. Anyhow...once connected I was able to backup and delete the backup logs on the standby.

    My goal is to keep sysdate minus 7 worth or archive logs and have a batch job whack the backup sets once a week
    I'm stmontgo and I approve of this message

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