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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. Oracle Database : Startup basics (How to start Oracle Database)
  2. How To Cancel A SQL Query In Oracle Database 18c
  3. How to relocate or move oracle database files using RMAN
  4. How to Check the Environment Variables for an Oracle Process
  5. How to Drop/Truncate Multiple Partitions in Oracle 12C
  6. How To Setup UDEV Rules For RAC OCR And Voting Devices on Partitions
  7. Oracle 11G RMAN – Understanding UNDO backup optimisation
  8. How to recreate Control file in Oracle Database
  9. What are Oracle Data Guard Protection Modes (redo transport rules) and how to configure them
  10. Step By Step Guide To Create Physical Standby On Normal File System For ASM Primary using RMAN

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ICMP redirects on CentOS/RHEL
  • What are Oracle Key Vault Roles
  • What Is Oracle Key Vault
  • Auditing with Oracle Database Vault Reports
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary