Database not starting

First thing is to look into the trace dir / logs dir for your database, it’ll look something like:

/u01/app/oracle/diag/rdbms/jdetest/JDETEST1/trace

I find that alert_JDETEST1.log (this is for RAC) is the best place to start, goto the bottom:

ARC0: STARTING ARCH PROCESSES COMPLETE

Errors in file /u01/app/oracle/diag/rdbms/jdetest/JDETEST1/trace/JDETEST1_ora_46405.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 499289948160 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.

************************************************************************

Cool, the database have given me all of these good ideas!

But, I cannot start the database – so I cannot run RMAN. 

Chicken or the egg?

So I can see that I’m using all 465G of recovery area.  I need to extend this to be able to start the database properly so that RMAN will work.  So I update the size allocated (as I still have space available on the device) with the commands below

sqlplus / as sysdba

SQL>show parameter db_recovery_file_dest_size

NAME TYPE VALUE

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

db_recovery_file_dest_size big integer 465G

SQL> alter system set db_recovery_file_dest_size=500G ;

I forget, you might need to startup nomount at the SQL command on one of the RAC nodes (the one that you are on)

Then shutdown the database and start it normally (note that I’m only starting a single instance for the time being – not the RAC instance).

then

>rman / target

RMAN> delete noprompt archivelog all completed before 'sysdate - 1/24';

RMAN> quit

Nice, for a non DBA, I have my database back up and running.