FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Oracle Forum / ORA - Oracle Database Error Codes - Search via specific error codes. / ORA-10560 Through ORA-19999 / ORA-16038: alter database open" ORA-16038 ORA-19809 ORA-00312 errors / Latest Posts

RE: ORA-16038: alter database open" ORA-16038 ORA-19809 ORA-00312 errors

Admin — Thu, 10 Jun 2010 12:08:59 GMT

Whenever you try to startup the database it fails with error ORA-16038,ORA-19809, ORA-00312.
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 117440552 bytes
Database Buffers 41943040 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-16038: log 3 sequence# 572 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/oradata2/data1/dbase/redo03.log'

Or in mount stage whenever you try to open the database it fails with error ORA-16014, ORA-00312.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 3 sequence# 572 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/oradata2/data1/dbase/redo03.log'

The most common of happening the error is the archive log destination if full. You have flash recovery area configured and rman retention policy is failed to delete any archived or incremental backups and so can't archived new online log.

1)Check alert log for any errors.

2)Check the archive destination. To make sure all is ok and not at 100%.

3)Increase the value of db_recovery_file_dest_size

4)Open the database now.

If you have not enough space in your disk and you have recent backup of your database and archive log is not needed then you can issue
$rman target /
RMAN>DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-2';

If you have not any recent backup then backup database to another location and delete archivelogs.

To do this,
$rman target /
RMAN>backup format '/oradata2/%U' archivelog all delete input database;

If you have backup and only need archivelogs.

In this case backup the archive log to another location and delete archive log from flash recovery area.
You can do this by,

$rman target /
RMAN> backup format '/oradata2/%U' archivelog all delete input;

RE: ORA-16038: alter database open" ORA-16038 ORA-19809 ORA-00312 errors

Admin — Thu, 10 Jun 2010 12:00:12 GMT

oracle 10g database "alter database open" ORA-16038 ORA-19809 ORA-00312 errors

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.

There are a couple of possible options.

1) Increase the parameter db_recovery_file_dest_size
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.

ORA-16038: alter database open" ORA-16038 ORA-19809 ORA-00312 errors

Admin — Fri, 24 Apr 2009 15:57:17 GMT

Hi,

I'm getting the following error message when starting up the database:

OS: SUN Solaris 5.10 DB: 10.2.0.3 USING ASM

SQL> startup
ORACLE instance started.

Total System Global Area 2634022912 bytes
Fixed Size 2130720 bytes
Variable Size 513637600 bytes
Database Buffers 2113929216 bytes
Redo Buffers 4325376 bytes
Database mounted.
ORA-16038: log 2 sequence# 1704 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1:
'+SHARED_DATA_DG02/proddb/onlinelog/group_2.257.681318315'
ORA-00312: online log 2 thread 1:
'+SHARED_DATA_DG03/proddb/onlinelog/group_2.263.681321803'

I couldn't figure out what to do. I even deleted all the archivelogs from RMAN:

RMAN> delete noprompt archivelog all;

released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=64 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=163 devtype=DISK
specification does not match any archive log in the recovery catalog

I then checked the recovery area:

SQL> show parameter recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +SHARED_FRA_DG01
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 0

After checking this, I knew what my problem was. The db_recovery_file_dest_size is set to 2G. When i had created my spfile, i had set it to 200G, but this time, I had started it with the init<sid>.ora file and the initfile had it set to 2G and the archiver had been stuck. I changed it by issuesing the following command:

SQL> alter system set db_recovery_file_dest_size=200G scope=both;

System altered.

Then i shutdown the db and started it up again:

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Hope this helps.