Oracle introduced the flash recovery area concept in 10 Gb to simplify and improve backup. However, the flash recovery area also needs to be carefully planned and configured. Otherwise, problems may occur, from Oracle10gR2, Oracle also provides a new view V $ FLASH_RECOVERY_AREA_USAGE to monitor the consumption of space in the flash back area. This article briefly introduces the alerts and space maintenance mechanisms for the Oracle flash back area.
Each time RMAN creates a file in the flash recovery area, the list of files that can be deleted is updated. When there is space pressure in the flash back area, Oracle will automatically delete discarded files from the flash back area. When there is no more space to release, Oracle will give a space pressure alarm.
When the space usage reaches 100%, the database will be suspended for archiving and other reasons.
The size of the flash back area is specified by the db_recovery_file_dest_size parameter.
The path is specified by db_recovery_file_dest.
SQL> show parameter db_recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /msflsh db_recovery_file_dest_size big integer 65G |
Both parameters are dynamic parameters.
When the space used in the flash back area reaches 85%, Oracle will issue a warning:
*** SERVICE NAME:(SYS$BACKGROUND) 2005-12-03 13:20:16.864 *** SESSION ID:(156.1) 2005-12-03 13:20:16.864 ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 85.00% used, and has 8050696704 remaining bytes available. |
When the space usage reaches 97%, Oracle will issue a Critical alarm:
ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 97.02% used, and has 1602355712 remaining bytes available. |
When the space usage reaches 100%, the database will be suspended if it cannot be archived:
ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 100.00% used, and has 0 remaining bytes available. |
The following error is returned:
ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim 9563136 bytes disk space from 53687091200 limit *** 2005-12-04 13:59:14.011 52278 kcrr.c ARC1: Error 19809 Creating archive log file to '/msflsh/MMSDB/archivelog/2005_12_04/o1_mf_1_17108_%u_.arc' *** 2005-12-04 13:59:14.011 50725 kcrr.c kcrrfail: dest:10 err:19809 force:0 blast:1 *** 2005-12-04 13:59:14.012 52278 kcrr.c ARC1: All standby destinations failed; successful archival assumed *** 2005-12-04 13:59:14.026 16432 kcrr.c ORA-16038: log 1 sequence# 17108 cannot be archived |
Note the word "reclaim". Oracle uses recycling here, which means that there is no space available for recycling to meet the archiving space requirements.
When Oracle is in the reclaim space, you may see the following similar information:
Sat Oct 1 21:20:54 2005 Deleted Oracle managed file +ORADG/danaly/backupset/2006_09_07/ncsnf0_tag20060907t192619_0.274 Deleted Oracle managed file +ORADG/danaly/archivelog/2006_09_08/thread_1_seq_35.276.600588049 Sun Oct 2 05:46:40 2005 Thread 1 advanced to log sequence 80 Current log# 2 seq# 80 mem# 0: +ORADG/danaly/onlinelog/group_2.260.600173851 Current log# 2 seq# 80 mem# 1: +ORADG/danaly/onlinelog/group_2.261.600173853 Sun Oct 2 05:46:41 2005 Deleted Oracle managed file +ORADG/danaly/archivelog/2006_09_08/thread_1_seq_36.277.600600509 Deleted Deleted Oracle managed file +ORADG/danaly/archivelog/2006_09_09/thread_1_seq_38.279.600674413 |