Oracle Faq

With Oracle 10g a new data dictionary view was introduced which gives you alerts regarding the database which can be useful to DBA to avoid any problems before they can actually become a problem.

The view name is dba_outstanding_alerts. It is always a good practice to query this view on daily basis and take necessary steps according to the output

Issue the following query to see the message:

SQL> SELECT object_type, message_type, message_level,
            reason, suggested_action
     FROM dba_outstanding_alerts;
The reason why i have included above query in flash recovery area article is because any warnings and alerts for flash recoevry area will be recorded in this view also.

Flash recovery area is the area used by oracle for the storage of archivelogs,backup set and flash back logs and the exact split or % of the space occupied by component in flash recovery area  can be obtained by following query

SQL>  select * from v$flash_recovery_area_usage;

This view contains space_reclaimable column which determines the space which can reclaimed back since the archive logs are backed up or the backup sets have become obsolete because of the recovery window or redundany policy  .Oracle  will automatically reclaim this space  when the space left in flash recovery area is less then 15% of available space

Also the exact space used ,space reclaimable and location of flash recovery area can be determined from v$receovery_file_dest view.

SQL> select * from v$recovery_file_dest;

When the space used in the Flash Recovery Area goes above 85% the we will get
the following warning in the alert log:

  Wed Jun 15 21:51:19 2005
  Errors in file /opt/oracle/product/1010/admin/db10g/udump/db10g_ora_13900.trc:
  ORA-19815: WARNING: db_recovery_file_dest_size of 1073741824 bytes is 86.07% used,
  and has 149591552 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
     DELETE EXPIRED commands.
  ************************************************************************
When the Flash Recovery Area is full and there is no space available to
   create archivelogs in the Flash Recovery Area. We will get the following error
   in the alert log:

Wed Jun 15 23:01:09 2005
Errors in file /opt/oracle/product/1010/admin/db10g/udump/db10g_ora_13754.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1032704 bytes disk space from 1073741824 limit
ARCH: Error 19809 Creating archive log file to ‘/opt/oracle/product/1010/dbs/ora_flash_area/DB10G/archivelog/2005_06_15/o1_mf_1_116_0_.arc’
ARCH: All standby destinations failed; successful archival assumed
ARCH: Failed to archive thread 1 sequence 116 (19809)
Wed Jun 15 23:01:27 2005
ARC1: Evaluating archive thread 1 sequence 116
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 116 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
Wed Jun 15 23:01:27 2005
ORACLE Instance db10g - Archival Error
ARCH: Connecting to console port…
Wed Jun 15 23:01:27 2005
ORA-16014: log 2 sequence# 116 not archived, no available destinations
ORA-00312: online log 2 thread 1: ‘/opt/oracle/product/1010/oradata/db10g/redo02.log’
ARCH: Connecting to console port…
ARCH:
Wed Jun 15 23:01:27 2005
ORA-16014: log 2 sequence# 116 not archived, no available destinations
ORA-00312: online log 2 thread 1: ‘/opt/oracle/product/1010/oradata/db10g/redo02.log’

From a DBA perspective it is very important to monitor flash recovery area on regualar basis to check space and to prevent archival errors.

   

Share This