oracle 10g database ORA-16038 ORA-19809 ORA-00312 error

Just copy paste :

oracle 10g database ORA-16038 ORA-19809 ORA-00312 error
-----------------------------------------------------------------------------


[oradb@luping ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on Mon May 31 19:50:20 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> startup mount
ORACLE instance started.

Total System Global Area 620756992 bytes
Fixed Size 780104 bytes
Variable Size 316938424 bytes
Database Buffers 301989888 bytes
Redo Buffers 1048576 bytes
Database mounted.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16038: log 1 sequence# 230 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/oradata/orcl/redo01.log'

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4294967296 SCOPE=BOTH;

System altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.


Reference:

+ Recovery Manager Maintenance Tasks <http://download-west.oracle.com/docs/cd/B13789_01/
server.101/b10735/maint.htm#1006546> - Oracle® Database Backup and Recovery Basics 10g Release 1 (10.1) Part Number B10735-01

+ Metalink: ORA -19809 - limit exceeded for revovery files? (http://metalink.oracle.com/metalink/plsql/ml2_documents.showFOR?
p_id=621248.995&p_showHeader=1&p_showHelp=1)

Cause
~~~~~

We register all the information about what we place in the flash recovery
area in the rman repository/controlfile. If we determine that there is not
sufficient space in the recovery file destination, as set by dest_size then
we will fail.

Just deleting the old backups/archive logs from disk is not sufficient as
it's the rman repository/controlfile that holds the space used information.

Fix
~~~

There are a couple of possible options.

1) Increase the parameter db_recovery_file_dest_size
the recovery file destination might be full, use the following statements:
SQL> select value from v$parameter where name like 'db_recovery_file_dest_size';
SQL> alter system set db_recovery_file_dest_size= < higher than the output value from
the first statement> ;
SQL> show parameter log_archive_dest_
This parameter will also show the archive log destination.
2) Stop using the db_recovery_file_dest by unsetting the parameter. (This
assumes you never really wanted to use this option)
3) Remove the Entries from the rman repository/Controlfile

The removal is desribed in the RMAN documentation but this is a quick and
dirty way if you don't have an rman repository - but could endanger your
ability to recover - so be careful.

a) delete unwanted archive log files from disk (rm, del commands)
b) connect to rman ( RMAN> connect target / )
c) RMAN> crosscheck archivelog all; - marks the controlfile that the archives
have been deleted
d) RMAN> delete expired archivelog all; - deletes the log entries identified
above.

You should then find archiving resumes OK.