Question: I have hit the
limit for my db_recovery_file_dest_size. When
starting Oracle I get the error "ORA-03113 end-of-file on
communication channel" right after it says that the database was
mounted.
If I look in the alert log it states that there
are errors in the .trc files with error codes "ORA-19809",
"ORA-19804", "ORA-16038", and "ORA-00312". This is proceeded by the
error "ORA-19815" which states that the
db_recovery_file_dest_size is 100% used and has zero bytes
available.
I went to my archived redo log file destination
and deleted old redo logs so that the directory has disk space
available, but that sill throws the error
because Oracle thinks that db_recoveryfile_dest_size is at
100%.
Since I have hit the
db_recoveryfile_dest_size limit, the database will not open. I
can start and mount the database, but cannot connect with SQL plus
to alter the db_recoveryfile_dest_size value. When I try to connect
with SQL*Plus I receive the error "ORA-01033: ORACLE initialization
or shutdown in progress".
How do I increase the limit for
db_recovery_file_dest_size?
Answer: Even though
db_recovery_file_dest_size is 100%, deleting files from the
directory was not the ultimate solution because I also had to update
the RMAN catalog to remove the old redo log entries.
Also note
errors/bugs in v$_flash_recovery_area.
The trick to increasing the limit for
db_recovery_file_dest_size was to:
1 - Start the database, then mount the database
(Do not open the database)
2 - Use RMAN to run a crosscheck on the
archive logs and then update the RMAN catalog to delete the expired
redo logs that I had manually deleted via the OS to free-up space in
the disk filesystem directory. See these notes on using the
RMAN crosscheck command.
The following scripts will check space utilization for
db_recovery_file_dest_size:
col name
format a32
col
size_mb format 999,999,999
col used_mb format 999,999,999
col pct_used format 999
select
name,
ceil( space_limit / 1024 / 1024)
size_mb,
ceil( space_used / 1024 / 1024) used_mb,
decode( nvl( space_used, 0),0, 0,
ceil ( ( space_used / space_limit)
* 100) ) pct_used
from
v$recovery_file_dest
order by
name desc;
*********************************
set lines 100
col name
format a60
select
name,
floor(space_limit / 1024 / 1024)
"Size MB",
ceil(space_used / 1024 / 1024) "Used MB",
from
v$recovery_file_dest
order by
name;
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
