Check your alert log when you get ORA-03113 at startup

You're a happy DBA wanting to start your Oracle DB Server.
You're running on Windows and you created a bat to startup your listener and DB. I start both the listener and the instance in one script, I use oradim for starting up the instance. That's not the fun part.

You try to connect and you get ORA-03113: end-of-file on communication channel.

Because you think you're smart, you start to troubleshoot. You say this cannot be a communication issue, and cannot have a problem between client and server process.
If someone did not get it, it was me in the middle of the issue.
I shut down the services and go to my friend SQL*Plus, as I trust him the most.
I take a step-by-step approach:

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1670221824 bytes
Fixed Size                  2176328 bytes
Variable Size            1526729400 bytes
Database Buffers          134217728 bytes
Redo Buffers                7098368 bytes
SQL> alter database mount;

Database altered.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 horia
OTPL1110003
11.2.0.1.0        08-AUG-12 MOUNTED      NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8868
Session ID: 2826 Serial number: 1



I have overlooked at this for a couple of 10 minutes, and then it hit me to check my best friend forever, the alert log file.
I quickly find out that I am running out of space in the recovery destination.
I do the cleanup and am able to bring my database up.

Two things to remember here:
1) ORA-03113 can be misleading, it is a "a symptom of an issue happening in behind"
2) Alert log is your most valuable point to do your routine checks. A health system has a clean alert log file. And that is the DBA's role to maintain.


Errors in file d:\app\hberca\diag\rdbms\horia\horia\trace\horia_ora_8868.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4102029312 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.
************************************************************************
Errors in file d:\app\hberca\diag\rdbms\horia\horia\trace\horia_ora_8868.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 39329280 bytes disk space from 4102029312 limit
ARCH: Error 19809 Creating archive log file to 'D:\APP\HBERCA\FLASH_RECOVERY_AREA\HORIA\ARCHIVELOG\2012_08_08\O1_MF_1_92_%U_.ARC'
Errors in file d:\app\hberca\diag\rdbms\horia\horia\trace\horia_ora_8868.trc:
ORA-16038: log 2 sequence# 92 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: 'D:\APP\HBERCA\ORADATA\HORIA\REDO02.LOG'
USER (ospid: 8868): terminating the instance due to error 16038
Wed Aug 08 20:58:38 2012

---------------------------------------------------
---------------------------------------------------

There is yet another trick you can do.
If you are running your db with spfile, it's obvious that you cannot edit it as it is a binary file.
It is known that Oracle uses the last specified value for a parameter.

We build a pfile with this entry:

spfile=D:\app\hberca\product\11.2.0\dbhome_1\database\SPFILEHORIA.ORA
db_recovery_file_dest_size= 5912M

And then startup the database with the pfile we just created

SQL> startup pfile=pfileHORIA.ora
ORACLE instance started.

Total System Global Area 1670221824 bytes
Fixed Size                  2176328 bytes
Variable Size            1526729400 bytes
Database Buffers          134217728 bytes
Redo Buffers                7098368 bytes
Database mounted.
Database opened.
SQL>