• 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. How to Migrate ASM Disk Groups to another Storage Online [non ASMLIB Devices]
  2. How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace
  3. How to upgrade RMAN catalog SCHEMA from 11g to 12.1.0.2 without upgrading the catalog database
  4. Oracle Interview Questions : Recovery catalog for RMAN backup
  5. Script To Find Redolog Switch History And Find Archivelog Size For Each Instances In Oracle RAC
  6. How Realms Work in Oracle Database Vault
  7. Beginners Guide to Flash Recovery Area in Oracle Database
  8. Real-time SQL Monitoring for Developers – Oracle Database 19c New Feature
  9. Oracle ASM 11gR2 instance is unable to start due to missing ASM spfile
  10. Oracle Database : script to create a “CREATE SYNONYM Script”

You May Also Like

Primary Sidebar

Recent Posts

  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Basics of client connectivity in Oracle Data Guard configuration
  • ORA-354 ORA-353 and ORA-312: Possible corruption in Online Redo Log File Members in a Redo Log Group
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary