Re: Archive Processes

shettysushil_at_gmail.com wrote:
> Joel, i am not asking about archive destinations its the archive
> processes which i m looking for.The destinations are properly defined
> for prmary as well as standby.But the archive processes are always busy
> communicating with the RFS at standby so the primary site gets hanged.
> we are on 9205 on HP-UX
> parametes are
> log_archive_dest_1='string1'
> log_archive_dest_2='service='' arch optional'
> log_archive_max_processes=4
> log_archive_dest_state_1=enable
> log_archive_dest_state_1=enable
> log_archive_min_succeed_dest=1
>
> Thanks in Advance
> sushil

Such processes are controlled by the values in the log_archive_* init.ora parameters. You have no direct control over the actual ora_arch_<sid> processes.

You do not state the number of redo log groups configured for your instance, a number which, I believe, is too small to allow processing to continue during standby archive log writes as the database is needing to wait until the archivelogs from group 1, as an example, are completing on the standby so they can be reused because group 2 logs have completed writing locally. As such it might benefit you to add redo log groups to provide more local space for redo writes and to provide more of a 'buffer' for your remote archivelog destination. Also, the size of the redo logs may be too small for the transaction load, hence another reason why your instance 'stops' during archivelog writes to the standby. Try tunning this query to determine how often log switches are occurring:

SELECT ROUND(AVG(1440 * (b.first_time - a.first_time)), 0) "Log switch time - minutes"
FROM v$loghist a, v$log b
WHERE b.sequence# = a.sequence# + 1
AND a.sequence# = (SELECT MAX(sequence#) FROM v$loghist) ORDER BY a.sequence#;

You will likely discover your log switches are occurring in the middle of writing the previously completed redo logs to your standby destination. And you'll also find you have too few redo log groups as the sum of the average switch times is less than the time required to write the archivelogs from one group to the standby destination.

Increase the size of the current redo log groups and add one or two additional groups and you should see this problem disappear. Treat the problem, not the symptom, as symptomatic 'fixes' almost always fail. And, your problem lies with how many redo log groups you have, and with how they are sized. Increase their size, increase their number and you should see the problem disappear.

David Fitzjarrell Received on Thu Sep 01 2005 - 07:48:13 CDT