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.

Leave a Comment

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

shares
Scroll to Top