• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

ORA-00257:Archiver Error, Connect Internal Only Until Freed

by admin

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.

Filed Under: oracle

Some more articles you might also be interested in …

  1. Operators in PL/SQL
  2. CentOS / RHEL : Installing and Configuring ASMLib
  3. Unable to create spfile for Oracle ASM instance
  4. Oracle 11G RMAN – Understanding UNDO backup optimisation
  5. How to move a datafile from file system to ASM
  6. Dynamic Oracle Net Server Tracing
  7. Basics of PL/SQL LOOPs
  8. How to set udev rule for setting the disk permission on ASM disks when using multipath on CentOS/RHEL 6
  9. How to rename a Pluggable Database, along with the respective directories, in Oracle 12c
  10. How to Shrink the datafile of Undo Tablespace in Oracle Database

You May Also Like

Primary Sidebar

Recent Posts

  • nixos-rebuild Command Examples in Linux
  • nixos-option: Command Examples in Linux
  • nixos-container : Command Examples in Linux
  • nitrogen Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright