Oracle DB: Oracle Archive Log Full – ORA-00257

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

Smart Panda - DatabaseIf you are loading large volumes of data and you have archive logging turned on sometimes you will run into the ORA-00257 error.  This error occurs when the upper limit of the archive log dedicated space is hit.  You need to clean up some/all of the logs in order to set the database free to work again.

A relatively straightforward way to do this is to use the rman tool:

. oraenv (set your sid/oracle_home)
rman target /
crosscheck archivelog all;  (you should see all the logs)
delete archivelog all; (removes all the logs)
crosscheck archivelog all; (you should see no logs)

Now you should be able to access the database again without the ORA-00257 error.  Sometimes it maybe necessary to turn the archive logging off during these large loads, see the post on turning off archive logging.

Smart Panda - IdeaNote: When in ARCHIVELOG mode, the database will make copies of all online redo logs after they are filled. These copies are called archived redo logs. The archived redo logs are created via Oracle’s Archive Process (ARCH). This process copies the archived redo log files to one or more archive log destination directories. In order for this process to be used additional setup is required primarily the redo log destination directories. In non-production environments there can be some major down sides to running in ARCHIVELOG mode.  The most common error you will encounter with Archive Logging turned on is the ORA-00257 error.  It should also be noted that absolutely nothing will process within the system until the archive logs are freed.  This can be a serious issue to an environment when doing maintenance, upgrades, or large data loads.  In those scenarios it is recommended to make backups of the environment and turn archive logging off for the duration of the high traffic work, then again backup and turn archive logging back on if needed.