From alert log, we find the following errors
Sat Jan 16 03:08:36 2010 ARCH: Archival stopped, error occurred. Will continue retrying Sat Jan 16 03:08:36 2010 ORACLE Instance OMSRPS - Archival Error Sat Jan 16 03:08:36 2010 ORA-16014: log 6 sequence# 11181 not archived, no available destinations ORA-00312: online log 6 thread 1: '/SIDS1/oradata/OMSRPS/stdby_redo02.log' ORA-00312: online log 6 thread 1: '/SIDS2/oradata/OMSRPS/stdby_redo02.log' Sat Jan 16 03:08:36 2010 Errors in file /SIDS/app/oracle/admin/OMSRPS/bdump/omsrps_arc4_9885.trc: ORA-16014: log 6 sequence# 11181 not archived, no available destinations ORA-00312: online log 6 thread 1: '/SIDS1/oradata/OMSRPS/stdby_redo02.log' ORA-00312: online log 6 thread 1: '/SIDS2/oradata/OMSRPS/stdby_redo02.log'If we check the content of trace file /SIDS/app/oracle/admin/OMSRPS/bdump/omsrps_arc4_9885.trc we see,
ORA-19815: WARNING: db_recovery_file_dest_size of 99857989632 bytes is 70.10% used, and has 29852624896 remaining bytes available. *** 2010-01-16 03:23:54.463 ************************************************************************ 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 DELETE EXPIRED commands. ************************************************************************ *** 2010-01-16 03:23:54.464 62692 kcrr.c ARC4: Error 19809 Creating archive log file to '/backup/flash_recovery_area/OMSRPS/archivelog/2010_01_16/o1_mf_1_11181_%u_.arc' *** 2010-01-16 03:23:54.464 60970 kcrr.c kcrrfail: dest:1 err:19809 force:0 blast:1 ARCH: Connecting to console port... ARCH: Connecting to console port... *** 2010-01-16 03:23:54.513 21373 kcrr.c ORA-16038: log 6 sequence# 11181 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 6 thread 1: '/SIDS1/oradata/OMSRPS/stdby_redo02.log' ORA-00312: online log 6 thread 1: '/SIDS2/oradata/OMSRPS/stdby_redo02.log'Cause of the Problem
Flash recovery area is used for archival destination. ORA-16014 ORA-00312 ORA-16038 ORA-19809: limit exceeded for recovery files will be raised because there is no space to create additional archive log in the flash recovery area. Note that the error inside trace file "ORA-19815: WARNING: db_recovery_file_dest_size of 99857989632 bytes is 70.10% used, and has 29852624896 remaining bytes available." can be misleading. Though it says 70.10% used but actually it is 100% used. Ensure by issuing following query in the database,
SQL> set lines 120 SQL> col name format a50 SQL> select name ,floor(space_limit / 1024 / 1024) "Size MB" ,ceil(space_used / 1024 / 1024) "Used MB" from v$recovery_file_dest order by name / NAME Size MB Used MB ------------------------------------------- --------- ------------ /backup/flash_recovery_area 95232 95152We see in the flash recovery area space limit and space used are almost same and no more space for archival. Hence we get error.
Consequences of the Error
Due to ORA-16014, ORA-00312, ORA-16038 and ORA-19809 there might have following effects to database.
1) Database archive process stops and database hangs.
2) Users are not able to connect to database.
3) If you try to open database you are not able to open database bypass these errors.
4) In the Alert log/Trace file Flash Recovery Area reports ORA-19809: limit exceeded for recovery files.
Solution of the Problem
There may have several solutions to solve above problems.
Solution 00: Ensure that you have enough space in the underlying directory
This step is common and application for all solution methods.
In the underlying mount point of your archival destination ensure that you have enough space. That is ensure that your hard disk is not full. If you don't have sufficient space in your disk space free up the space by deleting unnecessary files.
On Linux/Solaris you can check disk space of all mount points by $df -h
On HP-UX issue $df -k
Solution 01: Increase the size of Flash recovery area
The size of flash recovery area is determine by the parameter db_recovery_file_dest_size. You can check the current size of flash recovery area in Sql*plus by issuing,
SYS@OMSRPS> show parameter db_recovery_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /backup/flash_recovery_area db_recovery_file_dest_size big integer 93Gor by issuing query,
SQL> select value from v$parameter where name='db_recovery_file_dest_size'; VALUE ----------------------------------------------------------------------------- 99857989632 In order to increase the size of the parameter issue following query. SYS@OMSRPS> alter system set db_recovery_file_dest_size = 95G scope=both; System altered.Here, I have increased to 95G. Ensure by issuing,
SYS@OMSRPS> show parameter db_recovery_file NAME TYPE VALUE ------------------------------------ ----------- -------------------------- db_recovery_file_dest string /backup/flash_recovery_area db_recovery_file_dest_size big integer 95GNow check your alert log file and you see Archiver process freed from errors.
Sat Jan 16 03:37:31 2010 ALTER SYSTEM SET db_recovery_file_dest_size='95G' SCOPE=BOTH; Sat Jan 16 03:38:25 2010 Archiver process freed from errors. No longer stopped Sat Jan 16 03:38:25 2010 Primary database is in MAXIMUM PERFORMANCE mode RFS[9]: No standby redo logfiles of size 512000 blocks available Sat Jan 16 03:38:27 2010 Media Recovery Waiting for thread 1 sequence 11186 (in transit)
Solution 02: Change the database archival location outside FRA
You can solve the problem if you stop using flash recovery area for your archival destination. You can disable using flash recovery area by issuing following query,
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';
System altered.
After disabling flash recovery area you will see your archival location is changed to database default archival location. If you have enough space in your default archive location (you can check it by issuing "archive log list" after connecting as sysdba) you should no longer get above errors.
Solution 03: Free up space in FRA
If you use RMAN to database backup and store backups to FRA then check space distribution in the FRA by following query,
SQL>select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as "number" from v$flash_recovery_area_usage;
After issuing query if you see almost all the space are used by Archivelogs and backup pieces then there is no space to reclaim. So you need to free up some spaces with by of the following methods.
a) Take backup of archivelogs to some other locations and delete the archivelogs from flash recovery area.
You can delete older archivelogs say before 7 days ago by following RMAN command.
RMAN>Delete archivelog all completed before 'SYSDATE-7';
b) If flashback logs are enable then make sure you have enough space for all the flashback logs. If you don't need flashback logs you can disable it by command in mount stage.
SQL>Alter database FLASHBACK OFF;
c) Note that if you have guaranteed restore point then flashback logs will not be reclaimed by flash recovery area. Even due to some bugs Flashback logs are not reclaimed by flash recovery area when using guaranteed restore point (after dropping guaranteed restore point) or when changing db_flashback_retention_target to a lower value. If there is such guaranteed restore point and it is no longer needed you can delete by,
SQL> Drop restore point restore_point_name;
d) From your archival location using any operating system utility like rm or del or delete you can remove the backups/archive log files. After you remove the files the OS knows that you have deleted files but still you need to inform the database to be aware of the resulting free space.
To inform database about free space connect as RMAN and then issue following commands.
RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK ARCHIVELOG ALL;
RMAN>Delete expired backup;
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;
Solution 4: Scenarios based on Database Backup Strategy
Follow the scenarios as demonstrated in topic Database Startup fails with error ORA-16038,ORA-19809, ORA-00312. In the post it is demonstrated what we will do if we don't (in Solution C:) have any backup and also the secnarios if we (Solution D:) have recent backup and only need archivelogs.
Related Documents
Set up Flash Recovery Area for RMAN
Database Startup fails with error ORA-16038,ORA-19809, ORA-00312
