The Problem
Users cannot connect to the database :
0RA-00257:archiver error, connect internal only until freed ORA-16014:log 2 sequence# 231 not archived, no available destinations ORA-00312:online log 2 thread 1:'/[path]/redo02.log'
The Solution
The most probable cause for this error is that the flashback recovery area must have gone full.
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive Destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 231 Next log sequence to archive 231 Current log sequence 233
SQL> select group#,status archived from v$log; GROUP# ARCHIVED ------------ ------------------------ 1 INVALIDATED 2 INACTIVE 3 INACTIVE
The suggested solution to archive all fails:
SQL> archive log all; ORA-16020: less destinations available than specified by LOG_ARCHIVE_MIN_SUCCEEDED_DEST
The only one destination allowed (log_archive_min_succeed_dest= 1) is not able to perform the archiving. Follow the steps below to resolve the issue:
1. Increase the size of the flash back recovery area by increasing DB_RECOVERY_FILE_DEST_SIZE parameter to larger value. This option works if free disk space is available. For example:
SQL> alter system set db_recovery_file_dest_size=3G scope=both;
2. To avoid the situation once the 3Gb is full, set the following parameters so that when the dest1 is full, archiving is automatically performed to the alternate dest2 :
log_archive_dest_1='LOCATION=use_db_recovery_file_dest NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2' log_archive_dest_2='LOCATION=/' log_archive_dest_state_1='enable' log_archive_dest_state_2='alternate' db_recovery_file_dest='/ /flash_recovery_area' db_recovery_file_dest_size=2G
3. If archiving does not resume after freeing up space in archive destination, then the archiver may be stuck. In such case, for each archive destination execute the following to resume automatic archiving:
sql> alter system set LOG_ARCHIVE_DEST_.. = 'location=/[archivelog_path] reopen';
Error Codes
$ oerr ora 00257 00257, 00000, "archiver error. Connect internal only, until freed." // *Cause: The archiver process received an error while trying to archive // a redo log. If the problem is not resolved soon, the database // will stop executing transactions. The most likely cause of this // message is the destination device is out of space to store the // redo log file. // *Action: Check archiver trace file for a detailed description // of the problem. Also verify that the // device specified in the initialization parameter // ARCHIVE_LOG_DEST is set up properly for archiving.