Today I faced issues while starting my Oracle database. While startup/opening the database I was getting the error:
ORA-03113: end-of-file on communication channel
Process ID: 10263
Session ID: 91 Serial number: 3
I checked my “Alert log” which was at
$ORACLE_BASE/diag/rdbms/xe/XE/trace/alert_XE.log
In there was this line
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available
So the issue was my recovery size was full!
Oracle gives you the below options for this problem
************************************************************************
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.
************************************************************************
First I need to delete the files to recover the space:
oracle@linux-xbvi:~> rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Sat Oct 25 21:19:05 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: XE (DBID=2702090012)
RMAN> delete archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
List of Archived Log Copies for database with db_unique_name XE
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
36 1 48 A 25-AUG-13
Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2014_01_19/o1_mf_1_48_9fonmn1c_.arc
37 1 49 A 19-JAN-14
Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2014_01_19/o1_mf_1_49_9fonn0p1_.arc
38 1 50 A 19-JAN-14
...
Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/XE/archivelog/2014_01_19/o1_mf_1_48_9fonmn1c_.arc RECID=36 STAMP=837218637
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/XE/archivelog/2014_01_19/o1_mf_1_49_9fonn0p1_.arc RECID=37 STAMP=837218649
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/XE/archivelog/2014_01_19/o1_mf_1_50_9fonn8xb_.arc RECID=38 STAMP=837218657
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/XE/archivelog/2014_01_19/o1_mf_1_51_9fonnh9l_.arc RECID=39 STAMP=837218663
...
archived log file name=/u01/app/oracle/fast_recovery_area/XE/archivelog/2014_10_25/o1_mf_1_266_b4qkjlxx_.arc RECID=254 STAMP=861916652
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/XE/archivelog/2014_10_25/o1_mf_1_267_b4qkjnjl_.arc RECID=255 STAMP=861916654
Deleted 220 objects
After that change RMAN retention policy to avoid this problem in future:
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;
new RMAN configuration parameters are successfully stored