Database startup failed with ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim x bytes disk space from x limit

Error log itself self explanatory, due to limit exceeded at DB_RECOVERY_FILE_DEST_SIZE database not able to open.

Error logs:

Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_ora_6196.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 50111488 bytes disk space from 2307915776 limit
ARCH: Error 19809 Creating archive log file to 'D:\ORACLE_FILES\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2017_06_27\O1_MF_1_393_%U_.ARC'
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_ora_6196.trc:
ORA-16038: log 3 sequence# 393 cannot be archived 
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: 'D:\DATABASE\TEST\REDO031.LOG'
ORA-00312: online log 3 thread 1: 'D:\DATABASE\TEST\REDO032.ORA'
USER (ospid: 6196): terminating the instance due to error 16038
2017-06-27 12:51:40.592000 +05:30
Instance terminated by USER, pid = 6196

Error log itself self-explanatory, due to limit exceeded at DB_RECOVERY_FILE_DEST_SIZE database not able to open.

Solution:

Increase DB_RECOVERY_FILE_DEST_SIZE parameter size, and startup database. In my case 10G is sufficient.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH

According to Oracle suggestions in ADRCI alert logs, you have following choices to clean up space from recovery area:

  1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard, then consider changing RMAN ARCHIVELOG DELETION POLICY.
  2. Back up files to tertiary device such as tape using RMAN BACKUP RECOVERY AREA command.
  3. Add disk space and increase db_recovery_file_dest_size parameter to reflect the new space.
  4. Delete unnecessary files using RMAN DELETE command. If an operating system command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands.

 

Full error logs:

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
2017-06-27 12:51:31.042000 +05:30
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production.
Using parameter settings in server-side spfile D:\ORACLE_FILES\DB_HOME1\DATABASE\SPFILETEST.ORA
System parameters with non-default values:
processes = 150
sessions = 248
shared_pool_size = 608M
large_pool_size = 32M
java_pool_size = 160M
nls_length_semantics = "BYTE"
control_files = "D:\DATABASE\TEST\CONTROL01.CTL"
control_files = "D:\ORACLE_FILES\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL"
control_files = "D:\DATABASE\TEST\CONTROL03.CTL"
db_block_size = 8192
db_cache_size = 608M
compatible = "11.2.0.0.0"
db_recovery_file_dest = "D:\oracle_files\flash_recovery_area"
db_recovery_file_dest_size= 2201M
dml_locks = 800
undo_tablespace = "UNDOTBS1"
undo_retention = 3600
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=testXDB)"
job_queue_processes = 4
audit_file_dest = "D:\ORACLE_FILES\ADMIN\TEST\ADUMP"
audit_trail = "DB"
db_name = "test"
open_cursors = 2048
optimizer_index_cost_adj = 10
query_rewrite_enabled = "FALSE"
query_rewrite_integrity = "enforced"
pga_aggregate_target = 400M
diagnostic_dest = "D:\ORACLE_FILES"
PMON started with pid=2, OS id=3156
VKTM started with pid=3, OS id=5324 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
GEN0 started with pid=4, OS id=6464
DIAG started with pid=5, OS id=8932
DBRM started with pid=6, OS id=3824
PSP0 started with pid=7, OS id=8204
DIA0 started with pid=8, OS id=6920
MMAN started with pid=9, OS id=6040
DBW0 started with pid=10, OS id=4948
LGWR started with pid=11, OS id=8984
CKPT started with pid=12, OS id=7280
SMON started with pid=13, OS id=7588
RECO started with pid=14, OS id=6064
MMON started with pid=15, OS id=8720
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=16, OS id=8104
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:\oracle_files
ALTER DATABASE MOUNT
2017-06-27 12:51:35.391000 +05:30
Successful mount of redo thread 1, with mount id 2245910739
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=20, OS id=4564
2017-06-27 12:51:36.787000 +05:30
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
ARC1 started with pid=21, OS id=2748
ARC2 started with pid=22, OS id=8640
ARC1: Archival started
ARC3 started with pid=23, OS id=3032
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_arc2_8640.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2307915776 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_arc2_8640.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 50094592 bytes disk space from 2307915776 limit
ARC2: Error 19809 Creating archive log file to 'D:\ORACLE_FILES\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2017_06_27\O1_MF_1_3
94_%U_.ARC'
ARCH: Archival stopped, error occurred. Will continue retrying
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_arc2_8640.trc:
ORA-16038: log 1 sequence# 394 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'D:\DATABASE\TEST\REDO011.LOG'
ORA-00312: online log 1 thread 1: 'D:\DATABASE\TEST\REDO012.ORA'
2017-06-27 12:51:37.810000 +05:30
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_arc0_4564.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2307915776 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_arc0_4564.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 50094592 bytes disk space from 2307915776 limit
ARC0: Error 19809 Creating archive log file to 'D:\ORACLE_FILES\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2017_06_27\O1_MF_1_3
94_%U_.ARC'
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_ora_6196.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2307915776 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_ora_6196.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 50111488 bytes disk space from 2307915776 limit
ARCH: Error 19809 Creating archive log file to 'D:\ORACLE_FILES\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2017_06_27\O1_MF_1_3
93_%U_.ARC'
Errors in file d:\oracle_files\diag\rdbms\test\test\trace\test_ora_6196.trc:
ORA-16038: log 3 sequence# 393 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: 'D:\DATABASE\TEST\REDO031.LOG'
ORA-00312: online log 3 thread 1: 'D:\DATABASE\TEST\REDO032.ORA'
USER (ospid: 6196): terminating the instance due to error 16038
2017-06-27 12:51:40.592000 +05:30
Instance terminated by USER, pid = 6196

Thanks, Stay Tune. 🙂

Leave a Reply