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>
