db_recovery_file_dest_size 满导致数据库启动失败
SYS@orcl11g>startup ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2213936 bytes Variable Size 327157712 bytes Database Buffers 83886080 bytes Redo Buffers 4288512 bytes Database mounted. ORA-03113: end-of-file on communication channel Process ID: 1935 Session ID: 1 Serial number: 5
利用10046 事件跟踪数据库打开过程:
SYS@orcl11g>startup mount ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2213936 bytes Variable Size 327157712 bytes Database Buffers 83886080 bytes Redo Buffers 4288512 bytes Database mounted. SYS@orcl11g>oradebug setmypid Statement 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.trc SYS@orcl11g>alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 1727 Session ID: 1 Serial number: 5查看trace文件:
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 files ORA-19804: cannot reclaim 43927552 bytes disk space from 4070572032 limit *** 2014-11-15 21:20:56.648 4132 krsh.c ARCH: 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.c krsi_dst_fail: dest:1 err:19809 force:0 blast:1 WAIT #1: nam='log file sequential read' ela= 5 log#=0 block#=0 blocks=0 obj#=-1 tim=1416104456648722 WAIT #1: nam='Disk file operations I/O' ela= 15 FileOperation=5 fileno=0 filetype=3 obj#=-1 tim=1416104456648774 WAIT #1: nam='control file sequential read' ela= 14 file#=0 block#=1 blocks=1 obj#=-1 tim=1416104456648863 WAIT #1: nam='control file sequential read' ela= 11 file#=0 block#=15 blocks=1 obj#=-1 tim=1416104456648969 WAIT #1: nam='control file sequential read' ela= 10 file#=0 block#=17 blocks=1 obj#=-1 tim=1416104456649013 WAIT #1: nam='control file sequential read' ela= 10 file#=0 block#=22 blocks=1 obj#=-1 tim=1416104456649075 WAIT #1: nam='control file parallel write' ela= 4671 files=1 block#=21 requests=1 obj#=-1 tim=1416104456653795 WAIT #1: nam='control file parallel write' ela= 956 files=1 block#=18 requests=1 obj#=-1 tim=1416104456654862 WAIT #1: nam='control file parallel write' ela= 1817 files=1 block#=16 requests=1 obj#=-1 tim=1416104456656679 WAIT #1: nam='control file parallel write' ela= 1295 files=1 block#=1 requests=1 obj#=-1 tim=1416104456658021 WAIT #1: nam='control file sequential read' ela= 14 file#=0 block#=1 blocks=1 obj#=-1 tim=1416104456658082 WAIT #1: nam='control file sequential read' ela= 12 file#=0 block#=32 blocks=1 obj#=-1 tim=1416104456658162 DDE: 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 archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/orcl11g/redo01.log' www.zbdba.com *** 2014-11-15 21:20:56.660 USER (ospid: 2247): terminating the instance due to error 16038原来是flash_recovery_area满了,无法进行归档。增加db_recovery_file_dest_size:
SYS@orcl11g>alter system set db_recovery_file_dest_size=8G scope=spfile; System altered. SYS@orcl11g>startup ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2213936 bytes Variable Size 327157712 bytes Database Buffers 83886080 bytes Redo Buffers 4288512 bytes Database mounted. Database opened. SYS@orcl11g>show parameter recover NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ db_recovery_file_dest string /opt/oracle/flash_recovery_are a db_recovery_file_dest_size big integer 8G recovery_parallelism integer 0
至此已经打开数据库,所以数据库启动失败,我们利用10046事件跟踪就能发现具体的原因。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。