Tuesday, April 3, 2012

DBUA hangs with ORA-19815 , ORA-19809 ,and ORA-19804

I was upgrading from Oracle 11.1.0.6.0 to Oracle 11.2.0.1.0 using dbua when I noticed that around 84% at "Upgrading Oracle Java Packages" dbua was hung.



On looking at alter log showed the following message:

bash-3.2$ tail -f ./app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log

Tue Apr 03 22:21:38 2012
Errors in file /u02/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc1_30212224.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 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 /u02/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc1_30212224.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 36977152 bytes disk space from 2147483648 limit
ARC1: Error 19809 Creating archive log file to '/oraarc/TESTDB/archivelog/2012_04_03/o1_mf_1_60_%u_.arc'

Fix:

DB_RECOVERY_FILE_DEST_SIZE specifies (in bytes) the hard limit on the total space to be used by target database recovery files created in the fast recovery area.

After setting the ORACLE_HOME to 11gR2 and ORACLE_SID=testdb I found that the current size of "db_recovery_file_dest_size" was 2G. So I increased it to 10G.


bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 3 22:26:52 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_recovery_file_dest_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 2G
SQL>


SQL> ALTER SYSTEM SET db_recovery_file_dest_size='10G';

System altered.

SQL>

I noticed in the alter log the "Archiver process freed from errors. No longer stopped" message.


bash-3.2$ tail -f ./app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log

ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=MEMORY;
Tue Apr 03 22:29:06 2012
db_recovery_file_dest_size of 10240 MB is 19.99% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Tue Apr 03 22:29:37 2012
Archived Log entry 64 added for thread 1 sequence 60 ID 0xffffffff98badd37 dest 1:
Archiver process freed from errors. No longer stopped
Tue Apr 03 22:29:37 2012
Thread 1 advanced to log sequence 63 (LGWR switch)
  Current log# 3 seq# 63 mem# 0: /oradb/testdb/redo03.log
Archived Log entry 65 added for thread 1 sequence 61 ID 0xffffffff98badd37 dest 1:
Tue Apr 03 22:29:38 2012
Archived Log entry 66 added for thread 1 sequence 62 ID 0xffffffff98badd37 dest 1:
Tue Apr 03 22:29:57 2012
SERVER COMPONENT id=ORDIM: status=VALID, version=11.2.0.1.0, timestamp=2012-04-03 22:29:57
Tue Apr 03 22:29:57 2012
Thread 1 advanced to log sequence 64 (LGWR switch)
  Current log# 1 seq# 64 mem# 0: /oradb/testdb/redo01.log
Tue Apr 03 22:29:57 2012
Archived Log entry 67 added for thread 1 sequence 63 ID 0xf

After that dbua proceeded file, and completed with the upgrade from Oracle 11.1.0.6.0 to Oracle 11.2.0.1.0