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
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;
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 184.108.40.206.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.
Get Free Blogging Tips & Technology updates in your Email !!!
Latest posts by Anil Verma (see all)
- BlueHost Affiliate Program – Earn $65 Per Referral - August 2, 2015
- WP Engine Affiliate Program – Earn $200 Per Referral - August 1, 2015
- How to fill out BlueHost Affiliate Tax form for Non-US Residents - June 20, 2015