Deprecated - This has not been a problem in years.

If an "archiver log stuck" condition is encountered, this is the work-around. Since archive logs are generated by one instance, and passed to the next to update the fallback database instance, this instruction must be carried through for both nodes.

Discussion

The first and best response to an archive logger stuck condition is to increase the available archive log space.

Condition was encountered most often in our environment when big database changes were occurring--BOX and EOX processing. Every change to the database generates a change log so the standby database can be updated to the same state. If the rate of change is too high, we can overrun the space allocation estimated for an average or typical operational state.

Nota bene.

  • Feedback from the DB and systems personnel indicate that 400G is the physical limit.
  • I.e. specifying 1000G while it may be effective, is not actually realistic.
  • The same command should be used to set the archive log space back to its original size--BOX and EOX processing are exceptional, not daily operational states.

Method

Review current settings first. Increasing the archive log space may buy a bit of time--depending on what is generating the unexpected database traffic.

[oracle@k1 ~]$ . oraenv
ORACLE_SID = [LIMSJR] ? LIMSJR
[oracle@k1 ~]$ sqlplus your-name_dba
> alter system set db_recovery_file_dest_size=200G scope=memory;
> exit
[oracle@k1 ~]$ . oraenv
ORACLE_SID = [LIMSJR] ? LIMSJR1
[oracle@k1 ~]$ sqlplus your-name_dba
> alter system set db_recovery_file_dest_size=200G scope=memory;
> exit

Review current space settings

col name     format a32
col size_mb  format 999,999,999
col used_mb  format 999,999,999
col pct_used format 999

select
   name,
   ceil( space_limit / 1024 / 1024) size_mb,
   ceil( space_used / 1024 / 1024) used_mb,
   decode( nvl( space_used, 0),0, 0,
   ceil ( ( space_used / space_limit) * 100) ) pct_used
from
    v$recovery_file_dest
order by
   name desc;

*********************************

set lines 100
col name format a60

select
   name,
   floor(space_limit / 1024 / 1024) "Size MB",
   ceil(space_used / 1024 / 1024)   "Used MB",
from
   v$recovery_file_dest
order by
   name;

Reference

Burleson Consulting: http://www.dba-oracle.com/p_db_recovery_file_dest_size.htm

  • No labels