Fast Recovery Area is used for various components such as duplicates of control files and redo logs, backups, archive logs and flashback logs. If a database is in ARCHIVELOG mode and there is no space left in FRA, the database aborts any connections. Only SYS (SYSDBA) user can connect.<\/p>\n\n\n\n
The space used can be checked with the following statement:<\/p>\n\n\n\n
<code>select<br>name,<br>floor(space_limit \/ 1024 \/ 1024) "Size MB",<br>ceil(space_used \/ 1024 \/ 1024) "Used MB"<br>from v$recovery_file_dest;<\/code><\/code><\/pre>\n\n\n\nOR<\/p>\n\n\n\n
<code>SELECT\nROUND((A.SPACE_LIMIT \/ 1024 \/ 1024 \/ 1024), 2) AS FLASH_IN_GB,\nROUND((A.SPACE_USED \/ 1024 \/ 1024 \/ 1024), 2) AS FLASH_USED_IN_GB,\nROUND((A.SPACE_RECLAIMABLE \/ 1024 \/ 1024 \/ 1024), 2) AS FLASH_RECLAIMABLE_GB,\nSUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED\nFROM\nV$RECOVERY_FILE_DEST A,\nV$FLASH_RECOVERY_AREA_USAGE B\nGROUP BY\nSPACE_LIMIT,\nSPACE_USED ,\nSPACE_RECLAIMABLE ;<\/code><\/code><\/pre>\n\n\n\nSpace used can be further analyzed with the following query:<\/p>\n\n\n\n
SELECT * FROM V$RECOVERY_AREA_USAGE;<\/code><\/pre>\n\n\n\nMind that there is reclaimable space and this will be overwritten automatically when needed, They are mostly used from flashback logs.<\/p>\n\n\n\n
If the FRA is full then perhaps some archive logs can be deleted to free some space. Another quick option to resolve the connection errors is to resize FRA to a bigger size, if there is space available on the disk hosting it.<\/p>\n\n\n\n
<code>ALTER SYSTEM SET db_recovery_file_dest_size= xxxx scope=memory;<\/code><\/code><\/pre>\n\n\n\nIf there is no disk available then the quickest way may be to change the location of the FRA temporarily:<\/p>\n\n\n\n
ALTER SYSTEM SET db_recovery_file_dest= '\/u01\/xxxx' scope=memory;<\/code><\/pre>\n\n\n\nYou can use RMAN to delete archive logs from FRA. For example the following RMAN command deletes all archives that are older than 24 hours:<\/p>\n\n\n\n
<code> delete archivelog from time 'SYSDATE-1';<\/code><\/code><\/pre>\n\n\n\n<\/p>\n","protected":false},"excerpt":{"rendered":"
Fast Recovery Area is used for various components such as duplicates of control files and redo logs, backups, archive logs and flashback logs. If a database is in ARCHIVELOG mode and there is no space left in FRA, the database aborts any connections. Only SYS (SYSDBA) user can connect. The space used can be checked […]<\/p>\n","protected":false},"author":1,"featured_media":106,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[52,51,53],"class_list":["post-152","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-archive-logs","tag-fra","tag-rman"],"blocksy_meta":[],"_links":{"self":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/152","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/comments?post=152"}],"version-history":[{"count":7,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/152\/revisions"}],"predecessor-version":[{"id":301,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/152\/revisions\/301"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media\/106"}],"wp:attachment":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media?parent=152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=152"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}