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

If you work on any Oracle database you might get in situation like this:

C:\Users\DamirV>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sri Pro 23 20:25:11 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

20:25:14 SQL> conn damirv/qw
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.


Warning: You are no longer connected to ORACLE.
20:25:20 SQL>

Or worse situation when you get from users (help desk):
"DBA-database is frozen! Heeeeeeeeelp!!"

Alert log

As always, first step in troubleshooting Oracle is to check an alert.log. If you do not know where is it(!?) then perform next two steps:

SQL> select value from v$parameter where name = 'background_dump_dest';

VALUE
--------------------------------------------------------------------------------
c:\oracle\product\diag\rdbms\xe\xe\trace

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\DamirV>dir c:\oracle\product\diag\rdbms\xe\xe\trace\al*.log
 Volume in drive C has no label.
 Volume Serial Number is 40A5-D38E

 Directory of c:\oracle\product\diag\rdbms\xe\xe\trace

23.12.2009  20:53           312.008 alert_xe.log
               1 File(s)        312.008 bytes
               0 Dir(s)  36.568.383.488 bytes free
C:\Users\DamirV>

In this case at the end of alert log file there are significant number of messages which will explain what goes wrong.

Errors in file c:\oracle\product\diag\rdbms\xe\xe\trace\xe_arc1_3176.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 47379968 bytes disk space from 2147483648 limit
ARC1: Error 19809 Creating archive log file to 'C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2009_12_23\O1_MF_1_145_%U_.ARC'
ARCH: Archival stopped, error occurred. Will continue retrying
Errors in file c:\oracle\product\diag\rdbms\xe\xe\trace\xe_arc1_3176.trc:
ORA-16038: log 1 sequence# 145 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'C:\ORACLE\PRODUCT\ORADATA\XE\REDO01.LOG'
Thread 1 cannot allocate new log, sequence 147

To be certain in solving any situation, always look in trace files until you are sure what they are signalizing. Here is the content, in this case, "xe_arc1_3176.trc" file:

*** 2009-12-23 20:23:57.253
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
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

*** 2009-12-23 20:23:57.530
   DELETE EXPIRED commands.
************************************************************************

Resolution

All looks that your db_recovery_file_dest_size is full with archive logs. To check that and be certain, perform:

SQL> show parameter db_recovery_file_dest_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 2G

SQL> set lines 100
SQL> col name format a60
SQL> select     name
  2  ,  floor(space_limit / 1024 / 1024) "Size MB"
  3  ,  ceil(space_used  / 1024 / 1024) "Used MB"
  4  from       v$recovery_file_dest
  5  order by name
  6  /

NAME                                                            Size MB    Used MB
------------------------------------------------------------ ---------- ----------
C:\oracle\product\flash_recovery_area                              2048       2007

SQL>

Because Oracle has no space to create new archived log file it freeze all operations and could wait in this state until eternity if you do not help him!

So you have two (three) solutions to overcome this.
1) Enlarge db_recovery_file_dest_size (from 2 to 3 GB in our case)

alter system set db_recovery_file_dest_size=3G scope=both;

2) Backup and delete archive logs

rman target / nocatalog

run {
allocate channel t1 type disk;
backup archivelog all delete input format '/arch_%d_%u_%s';
release channel t1;
}

3) Just delete archive logs (what I'll show as easiest way but I prefer previous two options on any production installations). I'll do that with RMAN as best way for that:

C:\Users\DamirV>rman target / nocatalog

Recovery Manager: Release 11.1.0.7.0 - Production on Sri Pro 23 20:52:08 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: XE (DBID=2578577487)
using target database control file instead of recovery catalog

RMAN> delete archivelog all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=116 device type=DISK
List of Archived Log Copies for database with db_unique_name XE
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------
1       1    99      A 25.11.09
        Name: C:\ORACLE\...\2009_11_25\O1_MF_1_99_5JV7D7M1_.ARC

2       1    100     A 25.11.09
        Name: C:\ORACLE\...\2009_11_26\O1_MF_1_100_5JXFJXH5_.ARC

3       1    101     A 26.11.09
        Name: C:\ORACLE\...\2009_11_27\O1_MF_1_101_5JZ0C9TO_.ARC

...
...

43      1    141     A 23.12.09
        Name: C:\ORACLE\...\2009_12_23\O1_MF_1_141_5M4V7S2J_.ARC

44      1    142     A 23.12.09
        Name: C:\ORACLE\...\2009_12_23\O1_MF_1_142_5M4V7ZNR_.ARC

45      1    143     A 23.12.09
        Name: C:\ORACLE\...\2009_12_23\O1_MF_1_143_5M4V876L_.ARC

46      1    144     A 23.12.09
        Name: C:\ORACLE\...\2009_12_23\O1_MF_1_144_5M4V8JJH_.ARC


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=C:\ORACLE\...\2009_11_25\O1_MF_1_99_5JV7D7M1_.ARC RECID=1 STAMP=703894295
deleted archived log
archived log file name=C:\ORACLE\...\2009_11_26\O1_MF_1_100_5JXFJXH5_.ARC RECID=2 STAMP=703966128
deleted archived log
archived log file name=C:\ORACLE\...\2009_11_27\O1_MF_1_101_5JZ0C9TO_.ARC RECID=3 STAMP=704018171
deleted archived log

...
...

archived log file name=C:\ORACLE\...\2009_12_23\O1_MF_1_141_5M4V7S2J_.ARC RECID=43 STAMP=706393099
deleted archived log
archived log file name=C:\ORACLE\...\2009_12_23\O1_MF_1_142_5M4V7ZNR_.ARC RECID=44 STAMP=706393105
deleted archived log
archived log file name=C:\ORACLE\...\2009_12_23\O1_MF_1_143_5M4V876L_.ARC RECID=45 STAMP=706393113
deleted archived log
archived log file name=C:\ORACLE\...\2009_12_23\O1_MF_1_144_5M4V8JJH_.ARC RECID=46 STAMP=706393122
Deleted 46 objects

RMAN>

Now, let us look again in alert log to see that situation has changed (as we expect):

Wed Dec 23 20:52:57 2009
db_recovery_file_dest_size of 2048 MB is 2.21% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
kcrrdmx: Successful archiving of previously failed ORL
Archiver process freed from errors. No longer stopped
Wed Dec 23 20:53:00 2009
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Dec 23 20:53:00 2009
Thread 1 advanced to log sequence 148 (LGWR switch)
  Current log# 1 seq# 148 mem# 0: C:\ORACLE\PRODUCT\ORADATA\XE\REDO01.LOG

As you can see db_recovery_file_dest_size of 2048 MB is 2.21% used shows that we have emptied all!

Trust but verify!

Before you send an e-mail (or telephone call) that all is OK, verify the case and try o connect as anon sysdba user:

C:\Users\DamirV>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sri Pro 23 20:55:25 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn damirv/pwd
Connected.
SQL>

Conclusion

Remember that in situations all you have to do is be calm. Oracle will not crashed or anything like this so you have time until the end of the world-so do not hurry too much. 3 minutes more or less will not worse the situation. So react smoothly!

If you want to prevent situations like this there are two general suggestions:

1. Either increase your file system (or db_recovery_file_dest_size size like in this case) to accommodate more archived logs. And check free space in both part.
2. Take more frequent backups of archived logs and delete them after backing them up.
3. Monitor alert.log all the time.

Cheers!