Fixed – ORA-19809: limit exceeded for recovery files

When you try to connect with Oracle Database and it throws an error message ORA-19809: limit exceeded for recovery files. It means archive destination (db_recovery_file_dest) has been full.

Following error will appear in the alert log file if you try to shutdown a database or switch a log file:

ORACLE Instance flash – Archival Error
ORA-16038: log 1 sequence# 45 cannot be archived
ORA-19809: limit exceeded for recovery files

ORA-19809 limit exceeded

To resolve this issue follow below steps:


Step-1: Issue Shutdown Abort command

SQL> conn sys/sys as sysdba

Connected.

SQL> shutdown abort;

ORACLE instance shut down.

Step-2: Mount the Oracle Database


SQL> startup mount;

ORACLE instance started.

Total System Global Area 1071333376 bytes

Fixed Size                  1388352 bytes

Variable Size             620757184 bytes

Database Buffers          444596224 bytes

Redo Buffers                4591616 bytes

Database mounted.

Step-3: Format Column Size

SQL> col name format A50

SQL> col space_limit format A10

SQL> col space_used format A10

Step-4: Check Total Size and Used Space

SQL> select  name,  (space_limit/1024/1024) ||'MB' as Space_Limit,
(space_used/1024/1024)||'MB' as Space_Used from  v$recovery_file_dest;

NAME                                               SPACE_LIMI SPACE_USED
-------------------------------------------------- ---------- ----------
C:\oraclexe\app\oracle\fast_recovery_area          10240MB    10230MB

Step-5: Increase Archive Log Destination Size or Delete Archive Log Files

If the space is full then you have two options:

Option-A) You can increase archive destination size (db_recovery_file_dest_size)

SQL> alter system set db_recovery_file_dest_size=4096m scope=both;

Or

Option-B) You can copy all the archive log files manually at some other location and delete all those archive log files using RMAN.

C:\>rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Nov 20 15:16:56 2013

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

connected to target database: XE (DBID=2712423074)

RMAN> DELETE ARCHIVELOG ALL;

It will prompt you for Yes/No option, Type Yes and press Enter to delete all archive log files from your Hard Disk.

Note: Backup all the archive log files before issuing DELETE ARCHIVELOG ALL command.

Step-6: Open the database

SQL> alter database open;

Database altered.

SQL>

Now check your database functioning again by shutdown and startup command.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 1071333376 bytes

Fixed Size                  1388352 bytes

Variable Size             620757184 bytes

Database Buffers          444596224 bytes

Redo Buffers                4591616 bytes

Database mounted.Database opened.
SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)