I have setup a simple physical standby database using Oracle 126.96.36.199. 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?
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
group by ads.dest_id;
protection_mode,standby_logfile_count "SRL_COUNT",standby_logfile_active "SRL_ACTIVE",
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