Db_recovery_file_dest_size full causes database start to fail

When you start the database:

[Email protected]>startuporacle instance started. Total System Global area  417546240 bytesfixed size                  2213936 bytesvariable size             327157712 bytesdatabase Buffers           83886080 Bytesredo buffers                4288512 bytesdatabase mounted. Ora-03113:end-of-file on communication channelprocess id:1935session id:1 Serial number:5

Use the 10046 event Tracking database to open the process:

[Email Protected]>startup mountoracle instance started. Total System Global area  417546240 bytesfixed size                  2213936 bytesvariable size             327157712 bytesdatabase Buffers           83886080 Bytesredo buffers                4288512 bytesdatabase mounted.[ Email protected]>oradebug setmypidstatement processed. [Email protected]>[email protected]>[email protected]>oradebug Event 10046 Trace name context Forever,level 12; Statement processed. [Email protected]>[email protected]>[email protected]>oradebug tracefile_name/opt/oracle/diag/rdbms/ Orcl11g/orcl11g/trace/orcl11g_ora_1727.trc[email protected]>alter Database open;alter database Open*ERROR at line 1 : Ora-03113:end-of-file on communication channelprocess id:1727session id:1 Serial number:5

To view the trace file:

Ora-19815:warning:db_recovery_file_dest_size of 4070572032 bytes is 100.00% used, and have 0 remaining bytes available.** You are following choices to free up space from Recovery area:1. Consider changing RMAN RETENTION POLICY. If you is using Data Guard, then consider changing RMAN ARCHIVELOG deletion policy.2. Back the 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 is used to the delete files, then use RMAN crosscheck and delete EXPIRED commands.******** Ora-19809:limit exceeded for recovery  Filesora-19804:cannot Reclaim 43927552 bytes disk space from 4070572032 limit*** 2014-11-15 21:20:56.648 4132 krsh.carch: Error 19809 Creating Archive log file to '/OPT/ORACLE/FLASH_RECOVERY_AREA/ORCL11G/ARCHIVELOG/2014_11_15/O1_MF_1_52_%U_.ARC ' * * * 2014-11-15 21:20:56.648 2747 krsi.ckrsi_dst_fail:dest:1 err:19809 force:0 blast:1wait #1: nam= ' log file sequential read ' ela= 5 log# =0 block#=0 blocks=0 obj#=-1 tim=1416104456648722wait #1: nam= ' Disk file operations I/O ' ela= fileoperation=5 fileno=0 Filetype=3 obj#=-1 tim=1416104456648774wait #1: nam= ' control file sequential read ' ela= file#=0 block#=1 blocks=1 obj#= -1 tim=1416104456648863wait #1: nam= ' control file sequential read ' ela= file#=0 block#=15 blocks=1 obj#=-1 tim=14161044 56648969WAIT #1: nam= ' control file sequential read ' ela= file#=0 block#=17 blocks=1 obj#=-1 tim=1416104456649013wait #1 : nam= ' control file sequential read ' ela= file#=0 block#=22 blocks=1 obj#=-1 tim=1416104456649075wait #1: nam= ' control File parallel write ' ela= 4671 files=1 block#=21 requests=1 obj#=-1 tim=1416104456653795wait #1: nam= ' control file Paralle L Write ' ela= 956 Files=1 block#=18 Requests=1 obj#=-1 tim=1416104456654862wait #1: nam= ' control file parallel write ' ela= 1817 Files=1 block#=16 Requests=1 Obj#=-1 tim=1416104456656679wait #1: nam= ' control file parallel write ' ela= 1295 Files=1 block#=1 Requests=1 obj#=-1 tim= 1416104456658021WAIT #1: nam= ' control file sequential read ' ela= file#=0 block#=1 blocks=1 obj#=-1 tim=1416104456658082 WAIT #1: nam= ' control file sequential read ' ela= file#=0 block#=32 blocks=1 obj#=-1 Tim=1416104456658162dde:problem Ke Y ' ORA 312 ' was flood controlled (0x1) (no incident) Ora-00312:online log 1 thread 1: '/opt/oracle/oradata/orcl11g/redo01. Log ' Ora-16038:log 1 sequence# cannot be archivedora-19809:limit exceeded for recovery filesora-00312:online log 1st Read 1: '/opt/oracle/oradata/orcl11g/redo01.log ' www.zbdba.com*** 2014-11-15 21:20:56.660user (ospid:2247): Terminating the instance due to error 16038

The original is Flash_recovery_area full, unable to archive. Add Db_recovery_file_dest_size:

[Email protected]>alter system set db_recovery_file_dest_size=8g scope=spfile; System altered. [Email protected]>startuporacle instance started. Total System Global area  417546240 bytesfixed size                  2213936 bytesvariable size             327157712 bytesdatabase Buffers           83886080 Bytesredo buffers                4288512 bytesdatabase mounted. Database opened. [email protected]>show parameter recovername                                 TYPE                   VALUE----------------------------------------------- -----------------------------------------db_recovery_file_dest                string                 /opt/oracle/flash_recovery_are                                                            adb_recovery_file_dest_size           Big integer            8grecovery_parallelism                 integer                0

Now that the database has been opened, the database fails to start, and we can use 10046 event tracking to discover the exact cause.

Db_recovery_file_dest_size full causes database start to fail