Alert.log shows No Standby Redo Logfiles Of Size 153600 Blocks Available [ID 405836.1]

In this Document

Applies to:

Oracle Server - Enterprise Edition - Version 10.1.0.2 to 10.2.0.5 [Release 10.1 to 10.2]
Information in this document applies to any platform.
***Checked for relevance on 28-Sep-2012***

Symptoms

After real time apply works within 24hrs
the following error occurs.

ERROR
Rfs[2]: no standby redo logfiles of size 153600 blocks available

Checking the standby logs on standby database, all SRLs are ACTIVE dated from weeks ago- normally we see 1 used for 1 thread and the others will be UNASSIGNED

standby> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ---------- ------------- -------------------- ------------ --------------------
7 2924799764 1 233641 157286400 148177920 NO ACTIVE 8.2227E+12 01-Dec-2006 21:36:20 8.2227E+12 01-Dec-2006 21:54:04
8 2924799764 1 233637 157286400 148179968 NO ACTIVE 8.2227E+12 01-Dec-2006 20:20:21 8.2227E+12 01-Dec-2006 20:39:22
9 2924799764 1 233635 157286400 148292608 NO ACTIVE 8.2227E+12 01-Dec-2006 19:43:15 8.2227E+12 01-Dec-2006 20:00:54
10 2924799764 1 233627 157286400 12578816 NO ACTIVE 8.2227E+12 01-Dec-2006 18:00:25 8.2227E+12 01-Dec-2006 18:01:31
11 2924799764 1 233633 157286400 148156416 NO ACTIVE 8.2227E+12 01-Dec-2006 19:06:06 8.2227E+12 01-Dec-2006 19:22:41
12 2924799764 1 233639 157286400 148199424 NO ACTIVE 8.2227E+12 01-Dec-2006 20:57:35 8.2227E+12 01-Dec-2006 21:16:32
13 2924799764 1 233629 157286400 148305920 NO ACTIVE 8.2227E+12 01-Dec-2006 18:01:41 8.2227E+12 01-Dec-2006 18:20:14
14 2924799764 1 233631 157286400 148451328 NO ACTIVE 8.2227E+12 01-Dec-2006 18:32:17 8.2227E+12 01-Dec-2006 18:48:59
15 2924799764 1 234647 157286400 3922944 YES ACTIVE 8.2228E+12 20-Dec-2006 16:37:31 8.2228E+12 20-Dec-2006 16:37:57

Cause

In Dec1 SRLs created on LG were not archived/stuck and hence remained ACTIVE and could not longer be assigned. At the that time we see that Primary was archiving every minute and only 1 ARCH available to archive to standby. From standby log_archive_max_processes was set to 2, hence only 1 ARCH archiving Locally and most likely unable to cope with the amount of archiving required.

Solution

1. On Standby/Primary set log_archive_max_processes=10

alter system set log_archive_max_processes=10 scope=both;

OR

2. Another way around this if the logs have been applied as they have in this case, the old standby logs
can be dropped and recreated to clear the problem.

alter database drop standby logfile '';
then
alter database add standby logfile group x '';