9i Standby Monitoring

[marist89]

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?

[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;

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;

[marist89]

Actually, I was just looking for v$archived_log.applied, but these scripts look interesting as well....

[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 .

DG works well. Might as well use it.

[marist89]

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)?

[Axr2]

I use OS process. I don't think there is a way to tell the database to do it.

[stmontgo]

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