C:\Users\DamirV>sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production on Sri Pro 23 20:25:11 2009 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 20:25:14 SQL> conn damirv/qw ERROR: ORA-00257: archiver error. Connect internal only, until freed. Warning: You are no longer connected to ORACLE. 20:25:20 SQL>Or worse situation when you get from users (help desk):
"DBA-database is frozen! Heeeeeeeeelp!!"
Alert log
As always, first step in troubleshooting Oracle is to check an alert.log. If you do not know where is it(!?) then perform next two steps:SQL> select value from v$parameter where name = 'background_dump_dest'; VALUE -------------------------------------------------------------------------------- c:\oracle\product\diag\rdbms\xe\xe\trace SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options C:\Users\DamirV>dir c:\oracle\product\diag\rdbms\xe\xe\trace\al*.log Volume in drive C has no label. Volume Serial Number is 40A5-D38E Directory of c:\oracle\product\diag\rdbms\xe\xe\trace 23.12.2009 20:53 312.008 alert_xe.log 1 File(s) 312.008 bytes 0 Dir(s) 36.568.383.488 bytes free C:\Users\DamirV>In this case at the end of alert log file there are significant number of messages which will explain what goes wrong.
Errors in file c:\oracle\product\diag\rdbms\xe\xe\trace\xe_arc1_3176.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 47379968 bytes disk space from 2147483648 limit
ARC1: Error 19809 Creating archive log file to 'C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2009_12_23\O1_MF_1_145_%U_.ARC'
ARCH: Archival stopped, error occurred. Will continue retrying
Errors in file c:\oracle\product\diag\rdbms\xe\xe\trace\xe_arc1_3176.trc:
ORA-16038: log 1 sequence# 145 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'C:\ORACLE\PRODUCT\ORADATA\XE\REDO01.LOG'
Thread 1 cannot allocate new log, sequence 147
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 47379968 bytes disk space from 2147483648 limit
ARC1: Error 19809 Creating archive log file to 'C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2009_12_23\O1_MF_1_145_%U_.ARC'
ARCH: Archival stopped, error occurred. Will continue retrying
Errors in file c:\oracle\product\diag\rdbms\xe\xe\trace\xe_arc1_3176.trc:
ORA-16038: log 1 sequence# 145 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'C:\ORACLE\PRODUCT\ORADATA\XE\REDO01.LOG'
Thread 1 cannot allocate new log, sequence 147
To be certain in solving any situation, always look in trace files until you are sure what they are signalizing. Here is the content, in this case, "xe_arc1_3176.trc" file:
*** 2009-12-23 20:23:57.253
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 flash 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
*** 2009-12-23 20:23:57.530
DELETE EXPIRED commands.
************************************************************************
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 flash 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
*** 2009-12-23 20:23:57.530
DELETE EXPIRED commands.
************************************************************************
Resolution
All looks that your db_recovery_file_dest_size is full with archive logs. To check that and be certain, perform:SQL> show parameter db_recovery_file_dest_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size big integer 2G SQL> set lines 100 SQL> col name format a60 SQL> select name 2 , floor(space_limit / 1024 / 1024) "Size MB" 3 , ceil(space_used / 1024 / 1024) "Used MB" 4 from v$recovery_file_dest 5 order by name 6 / NAME Size MB Used MB ------------------------------------------------------------ ---------- ---------- C:\oracle\product\flash_recovery_area 2048 2007 SQL>Because Oracle has no space to create new archived log file it freeze all operations and could wait in this state until eternity if you do not help him!
So you have two (three) solutions to overcome this.
1) Enlarge db_recovery_file_dest_size (from 2 to 3 GB in our case)
alter system set db_recovery_file_dest_size=3G scope=both;2) Backup and delete archive logs
rman target / nocatalog
run {
allocate channel t1 type disk;
backup archivelog all delete input format '/arch_%d_%u_%s';
release channel t1;
}
3) Just delete archive logs (what I'll show as easiest way but I prefer previous two options on any production installations). I'll do that with RMAN as best way for that:
C:\Users\DamirV>rman target / nocatalog Recovery Manager: Release 11.1.0.7.0 - Production on Sri Pro 23 20:52:08 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: XE (DBID=2578577487) using target database control file instead of recovery catalog RMAN> delete archivelog all; allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=116 device type=DISK List of Archived Log Copies for database with db_unique_name XE ===================================================================== Key Thrd Seq S Low Time ------- ---- ------- - -------- 1 1 99 A 25.11.09 Name: C:\ORACLE\...\2009_11_25\O1_MF_1_99_5JV7D7M1_.ARC 2 1 100 A 25.11.09 Name: C:\ORACLE\...\2009_11_26\O1_MF_1_100_5JXFJXH5_.ARC 3 1 101 A 26.11.09 Name: C:\ORACLE\...\2009_11_27\O1_MF_1_101_5JZ0C9TO_.ARC ... ... 43 1 141 A 23.12.09 Name: C:\ORACLE\...\2009_12_23\O1_MF_1_141_5M4V7S2J_.ARC 44 1 142 A 23.12.09 Name: C:\ORACLE\...\2009_12_23\O1_MF_1_142_5M4V7ZNR_.ARC 45 1 143 A 23.12.09 Name: C:\ORACLE\...\2009_12_23\O1_MF_1_143_5M4V876L_.ARC 46 1 144 A 23.12.09 Name: C:\ORACLE\...\2009_12_23\O1_MF_1_144_5M4V8JJH_.ARC Do you really want to delete the above objects (enter YES or NO)? yes deleted archived log archived log file name=C:\ORACLE\...\2009_11_25\O1_MF_1_99_5JV7D7M1_.ARC RECID=1 STAMP=703894295 deleted archived log archived log file name=C:\ORACLE\...\2009_11_26\O1_MF_1_100_5JXFJXH5_.ARC RECID=2 STAMP=703966128 deleted archived log archived log file name=C:\ORACLE\...\2009_11_27\O1_MF_1_101_5JZ0C9TO_.ARC RECID=3 STAMP=704018171 deleted archived log ... ... archived log file name=C:\ORACLE\...\2009_12_23\O1_MF_1_141_5M4V7S2J_.ARC RECID=43 STAMP=706393099 deleted archived log archived log file name=C:\ORACLE\...\2009_12_23\O1_MF_1_142_5M4V7ZNR_.ARC RECID=44 STAMP=706393105 deleted archived log archived log file name=C:\ORACLE\...\2009_12_23\O1_MF_1_143_5M4V876L_.ARC RECID=45 STAMP=706393113 deleted archived log archived log file name=C:\ORACLE\...\2009_12_23\O1_MF_1_144_5M4V8JJH_.ARC RECID=46 STAMP=706393122 Deleted 46 objects RMAN>
Now, let us look again in alert log to see that situation has changed (as we expect):
Wed Dec 23 20:52:57 2009
db_recovery_file_dest_size of 2048 MB is 2.21% 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.
kcrrdmx: Successful archiving of previously failed ORL
Archiver process freed from errors. No longer stopped
Wed Dec 23 20:53:00 2009
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Dec 23 20:53:00 2009
Thread 1 advanced to log sequence 148 (LGWR switch)
Current log# 1 seq# 148 mem# 0: C:\ORACLE\PRODUCT\ORADATA\XE\REDO01.LOG
As you can see db_recovery_file_dest_size of 2048 MB is 2.21% used shows that we have emptied all!db_recovery_file_dest_size of 2048 MB is 2.21% 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.
kcrrdmx: Successful archiving of previously failed ORL
Archiver process freed from errors. No longer stopped
Wed Dec 23 20:53:00 2009
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Dec 23 20:53:00 2009
Thread 1 advanced to log sequence 148 (LGWR switch)
Current log# 1 seq# 148 mem# 0: C:\ORACLE\PRODUCT\ORADATA\XE\REDO01.LOG
Trust but verify!
Before you send an e-mail (or telephone call) that all is OK, verify the case and try o connect as anon sysdba user:C:\Users\DamirV>sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production on Sri Pro 23 20:55:25 2009 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn damirv/pwd Connected. SQL>
Conclusion
Remember that in situations all you have to do is be calm. Oracle will not crashed or anything like this so you have time until the end of the world-so do not hurry too much. 3 minutes more or less will not worse the situation. So react smoothly!If you want to prevent situations like this there are two general suggestions:
- Either increase your file system (or db_recovery_file_dest_size size like in this case) to accommodate more archived logs. And check free space in both part.
- Take more frequent backups of archived logs and delete them after backing them up.
- Monitor alert.log all the time.
Cheers!
