Tuesday, October 18, 2011

flash_recovery_area full error- cant start database

Trace file /opt/app/oracle/diag/rdbms/cert/cert/trace/cert_ora_8290.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: xpsi7
Release: 2.6.32-131.12.1.el6.x86_64
Version: #1 SMP Tue Aug 23 11:13:45 CDT 2011
Machine: x86_64
Instance name: cert
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 8290, image: oracle@xpsi7 (TNS V1-V3)

*** 2011-10-18 22:11:30.934
*** SESSION ID:(96.3) 2011-10-18 22:11:30.934
*** CLIENT ID:() 2011-10-18 22:11:30.934
*** SERVICE NAME:() 2011-10-18 22:11:30.934
*** MODULE NAME:(sqlplus@xpsi7 (TNS V1-V3)) 2011-10-18 22:11:30.934
*** ACTION NAME:() 2011-10-18 22:11:30.934

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 43637760 bytes disk space from 4070572032 limit
*** 2011-10-18 22:11:30.936 4132 krsh.c
ARCH: Error 19809 Creating archive log file to '/opt/app/oracle/flash_recovery_area/CERT/archivelog/2011_10_18/o1_mf_1_114_%u_.arc'
*** 2011-10-18 22:11:30.936 2747 krsi.c
krsi_dst_fail: dest:1 err:19809 force:0 blast:1
DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)
ORA-00312: online log 3 thread 1: '/opt/app/oracle/oradata/cert/redo03.log'
ORA-16038: log 3 sequence# 114 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/opt/app/oracle/oradata/cert/redo03.log'

*** 2011-10-18 22:11:31.000
USER (ospid: 8290): terminating the instance due to error 16038


Fixed by
sqlplus / as sysdba
startup nomount

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID='*';

select * from v$recovery_file_dest;




select * from v$flash_recovery_area_usage;

select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name;

No comments:

Post a Comment

Followers

About Me

Torrance, CA, United States