Fixed – ORA-19809: limit exceeded for recovery files

If you connect with Oracle Database and it throws an error message "ORA-19809: limit exceeded for recovery files", It means there is no free space available in oracle's archive destination (db_recovery_file_dest).

Error Message:

Following error will show you in 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

Resolution:

You need to follow below mentioned steps to resolve this issue.

Step-1:

Connect oracle database with sys privileges and 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:

Now first format the column size then check the total size and used space with the given command.

SQL> col name format A50
SQL> col space_limit format A10
SQL> col space_used format A10

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-4:

Here you can increase Archive Log Destination Size or Delete Archive Log Files using RMAN.

Option-A:

You can increase archive destination size (db_recovery_file_dest_size) with the use of following command.

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

Option-B:

If you don't have enough space in your hard disk, 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;

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

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

Step-4:

Now Open the database with ALTER DATABASE OPEN command.

SQL> alter database open;
Database altered.
SQL>

Now you can verify your database 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.

Subscribe to get free tech advice, tips and in-depth articles.


Start Your Dream Website Today & Earn Money Online

To Start a dream website, choose a Domain Name & Reliable Hosting Provider, Self-hosted Blogging Platform, WordPress Theme, Email Marketing and SEO Tool for traffic.

SiteGround Hosting Discount - 60% OFF
ConvertKit The Best Email Marketing Software
Free SEMRush SEM and SEO Tool
Get Your Hosting Package with SiteGround, Increase new email subscribers with ConvertKit and Research Keywords with SEMRush to Boost Organic Traffic.





Leave a Reply

Your email address will not be published. Required fields are marked *