Fixed – ORA-19809: limit exceeded for recovery files

SiteGround Hosting Discount

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.

I use SEMRush for Keyword Research & Boost Organic Traffic. Do You?

Highly recommended SEM & SEO Tool for overview of Traffic, Keywords, Backlinks and more. You can search your competitors URL to analyze keywords & crush your competition.



Leave a Reply

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