Managing Archive Log Space

Owned by jr_fackler (Unlicensed)
Jan 19, 2023
2 min read

 

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