ORA-00257 Error Message

Archiver Error Tips

Today we are going to discuss an error that can arise, due in part, to improper memory storage. Because Oracle is indeed a database software, the very nature of that alone requires a degree of management when it comes to maintaining storage space for the archive. The ORA-00257 is a common Oracle error that typically concerns the amount of storage space set aside for archived log data.

With Oracle 10g and later, a system was implemented called the Flashback Recovery Area. This is a storage area, which can be configured using an Automatic Storage Management disk group or on a local disk, that is set aside specifically for the preservation of backup elements such as archived redo logs. The Flashback tools are beneficial in that they allow a user to view past instances without having to recover to a fixed time, which can prove destructive for data since employed.

In the case of an ORA-00257, which typically is accompanied by a message along the lines of an “archiver error, connect internal only until freed”, an attempt to archive a redo log file elicited the error. This can be problematic; if the error is not resolved, eventually Oracle will stop executing transactions. It could be that the initialization parameter ARCHIVE_LOG_DEST was not set correctly, in which case the fix is easy. However, it is far more likely cause of this error comes from not allotting enough storage space to store the redo log file.

As a first step, if you are unsure of where to begin, go ahead and check the archiver trace file to see if you can gather more information on the problem. Now to begin resolving the error, first make sure that you have enabled automatic archiving, which you can try to check through “SQL>archive log list;”. You can see your archive destination by prompting the following: “SQL> show parameter db_recovery_file_dest;”. You can then type “SQL> SELECT * FROM V$RECOVERY_FILE_DEST;” which will tell you the value that you are using for db_recovery_file_dest_size (recovery destination size).

At this juncture, you should be able to see the parameters SPACE_USED and SPACE_LIMIT. If the former is equal to the latter, or close, it should now be clear that this is where the problem has emanated from. You should proceed with moving the archive logs to another destination device. You can then archive your log files by typing “SQL> alter system archive log all;”. Be sure during this step to use the proper archive log path so as to avoid another Oracle error, ORA-16020. Finally, change the logs for verification by using “SQL> alter system switch logfile;”.

The easiest way to prevent this type of error from popping up is by keeping note of storage space used. You can speak with an Oracle consultant for more information on what you should expect to use storage-wise for your Oracle database so that incremental checks can be made when the storage device would likely begin to reach its capacity. Just as well, keeping spare storage devices on hand can help aid in alleviating the frustration your Oracle executions coming to a halt due to a lack of memory space.