Home » RDBMS Server » Backup & Recovery » ORA-19809: limit exceeded for recovery files (oracle 10gr R2,windows server 2003 64 bit)
ORA-19809: limit exceeded for recovery files [message #548409] Thu, 22 March 2012 01:12 Go to next message
x-oracle
Messages: 326
Registered: April 2011
Location: gujarat
Senior Member
hello i have one question

while opening my database i got this error i already increase my db_recovery_file_dest_size but still i got this error so can you guide me what can i do

E:\oracle\product\10.2.0\db_1\BIN>SET ORACLE_SID=LIVEDB

E:\oracle\product\10.2.0\db_1\BIN>SQLPLUS

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 22 11:14:47 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter user-name: SYS AS SYSDBA
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT OPEN_MODE FROM V$DATABASE;

OPEN_MODE
----------
MOUNTED



SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=2048576000 SCOPE=BOTH;

System altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16038: log 1 sequence# 277 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1:
'E:\ORACLE\PRODUCT\10.2.0\DB_1\LIVEDB\REDO01.LOG'


SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\oracle\product\10.2.0\flash
                                                 _recovery_area
db_recovery_file_dest_size           big integer 2048576000
SQL>
Re: ORA-19809: limit exceeded for recovery files [message #548412 is a reply to message #548409] Thu, 22 March 2012 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 58607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have too much files in the FRA.
Check in v$flash_recovery_area_usage.
Backup your archived logs (with delete inputs).
The other option is to enlarge the FRA.

Regards
Michel
Re: ORA-19809: limit exceeded for recovery files [message #548421 is a reply to message #548412] Thu, 22 March 2012 02:04 Go to previous messageGo to next message
x-oracle
Messages: 326
Registered: April 2011
Location: gujarat
Senior Member
thanks for your reply michel i did this but still this same error so how much space i can be increase can you guide me

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\oracle\product\10.2.0\flash
                                                 _recovery_area
db_recovery_file_dest_size           big integer 4G
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 1 sequence# 277 not archived, no available destinations
ORA-00312: online log 1 thread 1:
'E:\ORACLE\PRODUCT\10.2.0\DB_1\LIVEDB\REDO01.LOG'


SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG               206.24                       .01             153
BACKUPPIECE              151.15                         0               2
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0

6 rows selected.


and i also did this

RMAN>  crosscheck archivelog all;

validation succeeded for archived log
archive log filename=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\LIVEDB\ARCHIVE
LOG\2012_03_21\O1_MF_1_270_7PML7TDO_.ARC recid=236 stamp=778528597
validation succeeded for archived log
archive log filename=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\LIVEDB\ARCHIVE
LOG\2012_03_21\O1_MF_1_271_7PML86NK_.ARC recid=237 stamp=778528609
validation succeeded for archived log
archive log filename=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\LIVEDB\ARCHIVE
LOG\2012_03_21\O1_MF_1_272_7PML8RPM_.ARC recid=238 stamp=778528628
validation succeeded for archived log
archive log filename=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\LIVEDB\ARCHIVE
LOG\2012_03_21\O1_MF_1_273_7PML97TM_.ARC recid=239 stamp=778528643
validation succeeded for archived log
archive log filename=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\LIVEDB\ARCHIVE
LOG\2012_03_21\O1_MF_1_274_7PML9JKV_.ARC recid=240 stamp=778528652
validation succeeded for archived log
archive log filename=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\LIVEDB\ARCHIVE
LOG\2012_03_21\O1_MF_1_275_7PML9WOC_.ARC recid=241 stamp=778528664
validation succeeded for archived log
archive log filename=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\LIVEDB\ARCHIVE
LOG\2012_03_21\O1_MF_1_276_7PMLB8ST_.ARC recid=242 stamp=778528676
Crosschecked 153 objects




RMAN> DELETE EXPIRED ARCHIVELOG ALL;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
specification does not match any archive log in the recovery catalog

RMAN>
Re: ORA-19809: limit exceeded for recovery files [message #548429 is a reply to message #548421] Thu, 22 March 2012 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 58607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i did this but still this same error so

ORA-19809 = ORA-16014? In which world?

ORA-16014: log %s sequence# %s not archived, no available destinations
 *Cause:  An attempt was made to archive the named log, but the archive was
          unsuccessful. The archival failed because there were no archive log
          destinations specified, or all destinations are in an error state.
 *Action: Verify that archive log destinations are specified or
          take the necessary step to correct any errors that may have
          occurred.


Regards
Michel
Re: ORA-19809: limit exceeded for recovery files [message #548434 is a reply to message #548429] Thu, 22 March 2012 04:33 Go to previous messageGo to next message
x-oracle
Messages: 326
Registered: April 2011
Location: gujarat
Senior Member
my archive log destination is

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\oracle\product\10.2.0\flash
                                                 _recovery_area


michel
Re: ORA-19809: limit exceeded for recovery files [message #548439 is a reply to message #548434] Thu, 22 March 2012 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 58607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must also specify you want to use the FRA as archived logs destination:
log_archive_dest_10='location=USE_DB_RECOVERY_FILE_DEST'

Regards
Michel


Re: ORA-19809: limit exceeded for recovery files [message #548447 is a reply to message #548439] Thu, 22 March 2012 05:22 Go to previous messageGo to next message
x-oracle
Messages: 326
Registered: April 2011
Location: gujarat
Senior Member
i also did this

SQL> alter system set log_archive_dest_10='location=USE_DB_RECOVERY_FILE_DEST';

System altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16038: log 1 sequence# 277 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1:
'E:\ORACLE\PRODUCT\10.2.0\DB_1\LIVEDB\REDO01.LOG'


SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=2048576000 SCOPE=BOTH;

System altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16038: log 1 sequence# 277 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1:
'E:\ORACLE\PRODUCT\10.2.0\DB_1\LIVEDB\REDO01.LOG'


SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> starup
SP2-0042: unknown command "starup" - rest of line ignored.
SQL> startup
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  2065408 bytes
Variable Size             301992960 bytes
Database Buffers          939524096 bytes
Redo Buffers               14708736 bytes
Database mounted.
ORA-16038: log 1 sequence# 277 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1:
'E:\ORACLE\PRODUCT\10.2.0\DB_1\LIVEDB\REDO01.LOG'

Re: ORA-19809: limit exceeded for recovery files [message #548462 is a reply to message #548447] Thu, 22 March 2012 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Back to the first post and answer.

Regards
Michel
Re: ORA-19809: limit exceeded for recovery files [message #585000 is a reply to message #548409] Tue, 21 May 2013 04:12 Go to previous message
sudheergodgeri
Messages: 7
Registered: December 2010
Location: Mumbai, India
Junior Member

Plz. try this as well:
1) Ensure the db_recovery_file_dest area points to a disk/partition/mount point that is Xclusively in use for FRA (flash recovery area items) only.
Keeping a large size of db_recovery_file_dest BUT sharing the mount point with other files
which may trigger the ORA-19809 is very very risky.

2) Since your db is not opening - I suggest you open in mount mode - disable archiving temporarily and up the db - change recovery area parameters and then up it again.
BUT make sure you got at least 50% more free space than max . expected size of the
flash recovery area.

Previous Topic: Oracle Crash and Data Corrupted
Next Topic: ORA-01139: RESETLOGS option only valid after an incomplete database recovery
Goto Forum:
  


Current Time: Tue Jul 29 03:04:35 CDT 2014

Total time taken to generate the page: 0.08462 seconds