ORA-00257: archiver error. Connect internal only, until freed.

ORA-00257 is one of the commonest error in Oracle DBA life. He/She often has to deal with it.Oracle Database almost got frozen because of it and all transaction get stopped .Let us see how to deal with ORA-00257 error

Error Message

ORA-00257: archiver error. Connect internal only, until freed.

As per The oerr ORA-00257 error means

ORA-00257: 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 the 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.

Where do you see this error message

1) You will see below type of error message pertaining in alert log

Following is some if the information from the alert log:

Errors in file \u01\oracle\product\11.2.0\diag\rdbms\TEST\test\trace\test_arc1_1010.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 21474836480 bytes is 100.00% used, and has 0 remaining bytes available.Wed jan 21 02:44:02 2016
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARC1: Failed to archive thread 1 sequence 1459 (1809)
ARCH: Archival stopped, error occurred. Will continue retrying
Wed jan 21 02:44:02 2016
Errors in file \u01\oracle\product\11.2.0\diag\rdbms\TEST\test\trace\test_arc1_1010.trc
ORA-16038: log 3 sequence# 1459 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '\u01\oracle\oradata\TEST\redo03.LOG'

2) If you try to login with non sysdba user, you will the below error message

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 - Production on
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn apps/apps
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
Warning: You are no longer connected to ORACLE.
SQL

3) If you try to login with sysdba user and check the wait event for the session , you will find session waiting log archive switch event
Why ORA-00257 error occurs

See also What is Skew in Database?

This error happens as the target location for archive log is either full or not available. The Oracle ARCH background process is responsible for taking the redo logs from the online redo log file system and writing them to the flat file is not able to write to the filesystem

How to resolve ORA-00257 errors

1) First of all we should find the archive log destination for the database

sqlplus / as sysdba
SQL> archive log list;

you can also find archive destinations by either USE_DB_RECOVERY_FILE_DEST

SQL> show parameter db_recovery_file_dest;

Also get the size of the db_recovery_file_dest

show parameter db_recovery_file_dest_size;

2) The next step in resolving ORA-00257 is to find out what value is being used for db_recovery_file_dest_size, use:

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

You may find that the SPACE_USED is the same as SPACE_LIMIT, if this is the case, to resolve ORA-00257 should be remedied by moving the archive logs to some other destination.

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
+FLASH  21474836480 21474836480 212428800 200

3) We can have many solutions in these situation

a) Increase the size of db_recovery_file_dest if you have space available in Oracle ASM or filesystem whatever you are using

alter system set db_recovery_file_dest_size=40g;

b) We can delete the archive log which have been already backed up

rman target /
delete archivelog UNTIL TIME = 'SYSDATE-1' backed up 1 times to sbt_tape;rman target /
RMAN>delete archivelog
until time 'SYSDATE-3';
or,
RMAN>delete archivelog all;
or
delete archivelog UNTIL TIME = 'SYSDATE-1.5' backed up 1 times to sbt_tape;

c) If you have not taken the backup,then it is advise to take backup and then delete the archive log files

rman target /run {
allocate channel d1 type disk;
backup archivelog all delete input format '/u11/ora/arch_%d_%u_%s';
release channel d1;
}

d) Sometimes old backup piece ,flashback logs may be occupying space in the db_recovery_file_dest, you check the content of db_recovery_file_dest

SQL>Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as "number" from v$flash_recovery_area_usage;FILE_TYPE USED RECLAIMABLE number
------------ ---------- ----------- ----------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 4.77 0 2
BACKUPPIECE 56.80 0 10
IMAGECOPY 0 0 0
FLASHBACKLOG 11.68 11.49 63

Sometimes old guaranteed restore point might be present, Dropping will release the space.

Flashback Database explained and limitation

how to Flashback when we have dataguard

Top 10 Useful Queries for Flashback Database

e) If we dont need archive log , then simply deleting will also serve the purpose

rman target /
DELETE NOPROMPT ARCHIVELOG ALL;

Other useful command in this case

LIST COPY   OF ARCHIVELOG ALL         COMPLETED AFTER 'SYSDATE-1';
DELETE NOPROMPT BACKUP COMPLETED BEFORE 'SYSDATE-4';
LIST COPY   OF ARCHIVELOG UNTIL TIME = 'SYSDATE-18';
BACKUP ARCHIVELOG COMPLETION TIME BETWEEN  'SYSDATE-28' AND 'SYSDATE-7';

Important Note

Please dont delete archive log file manually from the filesystem, it will not update control file and it will not clear the issue. Then you have do crosscheck all in rman and delete obsolete and expired

f) We can specify alternate path for archiving

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=/other_dest_for_archiving'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='alternate'
db_recovery_file_dest='/u01/app/oradata/flash_recovery_area'
db_recovery_file_dest_size=200G

4) Once space is available in db_recovery_file_dest, Please check the system by doing alter system switch logfile

alter system switch logfile;
system alerted

Also try connecting with non sysdba user to make sure ,issue is resolved

sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 - Production on
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn apps/apps
connected
SQL>

It is advisable to take regular backup of archive log and delete them. We should put a monitoring script to keep a check on the flash recovery area space.

Related Posts

Leave a Comment Cancel Reply