Db_recovery_file_dest_size is full, leading to database startup failure

When starting the database: SYS@orcl11gstartupORACLEinstancestarted.TotalSystemGlobalArea417546240bytesFixedSize2213936bytesVariableSize327157712bytesDatabaseBuffers83886080bytesRedoBuffers4288512bytesDatabasemounted.ORA-03113: end-o

When starting the database: SYS @ orcl11startuporacle instance started. Total System Global Area 417546240 bytesFixed Size 2213936 bytesVariable Size 327157712 bytesDatabase Buffers 83886080 bytesRedo Buffers 4288512 bytesDatabase mounted. ORA-03113: end-o

When starting the database:

SYS@orcl11g>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 event 10046 to track the database opening process:

SYS@orcl11g>startup mountORACLE instance started.Total System Global Area  417546240 bytesFixed Size                  2213936 bytesVariable Size             327157712 bytesDatabase Buffers           83886080 bytesRedo Buffers                4288512 bytesDatabase mounted.SYS@orcl11g>oradebug setmypidStatement processed.SYS@orcl11g>SYS@orcl11g>SYS@orcl11g>oradebug event 10046 trace name context forever,level 12;Statement processed.SYS@orcl11g>SYS@orcl11g>SYS@orcl11g>oradebug tracefile_name/opt/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_1727.trcSYS@orcl11g>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

View the trace file:

ORA-19815: WARNING: db_recovery_file_dest_size of 4070572032 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.************************************************************************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= 15 FileOperation=5 fileno=0 filetype=3 obj#=-1 tim=1416104456648774WAIT #1: nam='control file sequential read' ela= 14 file#=0 block#=1 blocks=1 obj#=-1 tim=1416104456648863WAIT #1: nam='control file sequential read' ela= 11 file#=0 block#=15 blocks=1 obj#=-1 tim=1416104456648969WAIT #1: nam='control file sequential read' ela= 10 file#=0 block#=17 blocks=1 obj#=-1 tim=1416104456649013WAIT #1: nam='control file sequential read' ela= 10 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 parallel 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= 14 file#=0 block#=1 blocks=1 obj#=-1 tim=1416104456658082WAIT #1: nam='control file sequential read' ela= 12 file#=0 block#=32 blocks=1 obj#=-1 tim=1416104456658162DDE: Problem Key '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# 52 cannot be archivedORA-19809: limit exceeded for recovery filesORA-00312: online log 1 thread 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

It turns out that flash_recovery_area is full and cannot be archived. Add db_recovery_file_dest_size:

SYS@orcl11g>alter system set db_recovery_file_dest_size=8G scope=spfile;System altered.SYS@orcl11g>startupORACLE instance started.Total System Global Area  417546240 bytesFixed Size                  2213936 bytesVariable Size             327157712 bytesDatabase Buffers           83886080 bytesRedo Buffers                4288512 bytesDatabase mounted.Database opened.SYS@orcl11g>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

So far, the database has been opened, so the database failed to start. We can use 10046 event tracking to find the specific cause.