Sooner or later I guess every DBA will be faced with this error-message

ORA-00257: archiver error. Connect internal only, until freed.

and angry phone-calls from users complaining about a hanging database.

The reason for this error is a lack of space for archiving redologs.

When using a FRA and a proper set up backup that deletes obsolete files automatically, you propably only have two options:

Increase the FRA-Size by changing the parameter “db_recovery_file_dest_size” and/or

Relocate the FRA-Directory to another disc by changing the parameter “db_recovery_file_dest”

If space is available, both options are done quick and easy and get you out of your mess instantly. So don’t panic – and beware of deleting e.g. some archive-logs on OS-Level. As laid out in my post “Oracle: How to tell DB about manually freed space in FRA“, the latter won’t get you further and may even worsen your situation.

To increase the FRA-Size do this:

alter system set db_recovery_file_dest_size=100g scope=both;

To change to FRA-Location do this:

alter system set db_recovery_file_dest='/usr4/oracle/fast_recovery_area' scope=both;

As our database grows and grows, here especially in respect of backupsize and daily generated archive-logs, we may sometime get to a point when we have to relocate our FRA to another device with more free space. This could also be the case in a situation when we have run out of FRA-space because of an unexpected sudden massive increase of archived redologs. I just had the latter when some developers had run an upgrade of their application.

Luckily it is no big fuss to change the FRAs destination directory. It’s just a matter of changing the location with

alter system set db_recovery_file_dest='/usr4/oracle/fast_recovery_area' scope=both;

and increasing the FRAs size with

alter system set db_recovery_file_dest_size=100g scope=both;

To test the new settings we can initiate a redolog-archiving:

alter system switch logfile;
alter system checkpoint;

After this we should see a new subdirectory in the new FRA-Location with our DB’s SID as name and some subfolders in there containing our archived redolog.

Beware not to move the existing/old FRA-Files to the new location using OS-Commands, as the DB would not notice this and couldn’t keep track of that files. We can just let the files stay in the old FRA-Location until they age out and get deleted automatically by our DB. The absolute path to each file is recorded in the DB, so that the change of the db_recovery_file_dest is no problem for existing files. But keep in mind, that the overall size of files in the old db_recovery_file_dest adds up to the total size of our FRA. The FRA is a mere virtual concept that has no necessary 1:1 relationship to a physical directory on disc. So if we create a backup outside of our db_recovery_file_dest, this adds up too to our FRA-Size.

Especially developers often have the need to clean out their dev-schema and get a fresh start with a given dump-set. A DBA usually would simply drop and recreate the schema. But for not to cross their DBA, kind developers are longing for a solution to carry out that cleaning autonomously. This could be done (e.g. in SQL-Plus) with a script like this. Please mind to execute this script in the “right” schema, as the dropping starts immediately without further inquiry!