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
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.
