O script abaixo nos reporta informa\u00e7\u00f5es m\u00ednimas sobre a FRA (Fast Recovery Area) do ambiente:<\/p>\n\n\n
\nSQL> SET LIN 300\nCOL NAME FORMAT A30\nCOL SPACE_LIMIT FORMAT 99999\nCOL SPACE_USED FORMAT 99999\nSELECT NAME,SPACE_LIMIT\/1024\/1024 AS SPACE_LIMIT_MB,SPACE_USED\/1024\/1024 AS SPACE_USED_MB,SPACE_RECLAIMABLE,NUMBER_OF_FILES FROM V$RECOVERY_FILE_DEST;SQL> SQL> SQL> SQL>\n \nNAME SPACE_LIMIT_MB SPACE_USED_MB SPACE_RECLAIMABLE NUMBER_OF_FILES\n------------------------------ -------------- ------------- ----------------- ---------------\n\/oracle\/fra 700 171.296875 0 5\n \nSQL>\n<\/pre><\/div>\n\n\nJ\u00e1 o caso abaixo nos demonstra, em termos percentuais, o consumo de cada tipo de arquivo dentro da FRA:<\/p>\n\n\n
\nSQL> SELECT FILE_TYPE,PERCENT_SPACE_USED,PERCENT_SPACE_RECLAIMABLE,NUMBER_OF_FILES FROM V$RECOVERY_AREA_USAGE;\n \nFILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES\n----------------------- ------------------ ------------------------- ---------------\nCONTROL FILE 1.44 0 1\nREDO LOG 21.43 0 3\nARCHIVED LOG 0 0 0\nBACKUP PIECE 1.46 0 1\nIMAGE COPY 0 0 0\nFLASHBACK LOG 0 0 0\nFOREIGN ARCHIVED LOG 0 0 0\nAUXILIARY DATAFILE COPY 0 0 0\n \n8 rows selected.\n \nSQL>\n<\/pre><\/div>\n\n\nPara tentar simular o estouro da FRA, vou diminuir o seu tamanho e ver o comportamento:<\/p>\n\n\n
\n[oracle@oel8 oracle]$ sql\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Sun Feb 28 15:08:07 2021\nVersion 18.3.0.0.0\n \nCopyright (c) 1982, 2018, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.3.0.0.0\n \nSQL> alter system set db_recovery_file_dest_size=180M;\n \nSystem altered.\n \nSQL>\n<\/pre><\/div>\n\n\nNo alertlog do banco j\u00e1 \u00e9 poss\u00edvel ver a mensagem que a FRA est\u00e1 quase em seu esgotamento total:<\/p>\n\n\n
\n2021-02-28 15:08:48.549000 -03:00\nErrors in file \/oracle\/18.0.0\/base\/diag\/rdbms\/rmandb\/RMANDB\/trace\/RMANDB_m002_6256.trc:\nORA-19815: WARNING: db_recovery_file_dest_size of 188743680 bytes is 95.16% used, and has 9125888 remaining bytes available.\n************************************************************************\nYou have following choices to free up space from recovery area:\n1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,\n then consider changing RMAN ARCHIVELOG DELETION POLICY.\n2. Back up files to tertiary device such as tape using RMAN\n BACKUP RECOVERY AREA command.\n3. Add disk space and increase db_recovery_file_dest_size parameter to\n reflect the new space.\n4. Delete unnecessary files using RMAN DELETE command. If an operating\n system command was used to delete files, then use RMAN CROSSCHECK and\n DELETE EXPIRED commands.\n************************************************************************\nALTER SYSTEM SET db_recovery_file_dest_size=180M SCOPE=BOTH;\n<\/pre><\/div>\n\n\nEste limite \u00e9 poss\u00edvel ser consultado tamb\u00e9m atrav\u00e9s da query abaixo:<\/p>\n\n\n
\nSQL> SELECT OBJECT_TYPE, MESSAGE_TYPE, MESSAGE_LEVEL, REASON, SUGGESTED_ACTION FROM DBA_OUTSTANDING_ALERTS;\n \nOBJECT_TYPE MESSAGE_TYPE\n---------------------------------------------------------------- ------------\nMESSAGE_LEVEL\n-------------\nREASON\n--------------------------------------------------------------------------------\nSUGGESTED_ACTION\n--------------------------------------------------------------------------------\nRECOVERY AREA Warning\n 5\ndb_recovery_file_dest_size of 188743680 bytes is 95.16% used and has 9125888 rem\naining bytes available.\nChoices to free up space from recovery area: 1. Consider changing RMAN RETENTION\n \nOBJECT_TYPE MESSAGE_TYPE\n---------------------------------------------------------------- ------------\nMESSAGE_LEVEL\n-------------\nREASON\n--------------------------------------------------------------------------------\nSUGGESTED_ACTION\n--------------------------------------------------------------------------------\n POLICY. If you are using Data Guard, then consider changing RMAN ARCHIVELOG DEL\nETION POLICY. 2. Backup files to tape using RMAN BACKUP RECOVERY AREA command. 3\n. Add disk space and increase db_recovery_file_dest_size parameter. 4. Delete un\nnecessary files using RMAN DELETE command. If OS command was used to delete file\ns, then use RMAN CROSSCHECK and DELETE EXPIRED commands.\n \nOBJECT_TYPE MESSAGE_TYPE\n---------------------------------------------------------------- ------------\nMESSAGE_LEVEL\n-------------\nREASON\n--------------------------------------------------------------------------------\nSUGGESTED_ACTION\n--------------------------------------------------------------------------------\n \n \nSQL>\n<\/pre><\/div>\n\n\nAt\u00e9 o momento, o fen\u00f4meno ainda \u00e9 considerado Warning, mas a partir dos 97%, passa a ser considerado como Critical. Uma boa fonte oficial de como atuar em problemas de espa\u00e7o na FRA \u00e9 a Nota “Space issue in Fast \/ Flash Recovery Area – FRA Full (Doc ID 829755.1)<\/strong>“. <\/p>\n","protected":false},"excerpt":{"rendered":"O script abaixo nos reporta informa\u00e7\u00f5es m\u00ednimas sobre a FRA (Fast Recovery Area) do ambiente: J\u00e1 o caso abaixo nos demonstra, em termos percentuais, o consumo de cada tipo de arquivo dentro da FRA: Para tentar simular o estouro da FRA, vou diminuir o seu tamanho e ver o comportamento: No alertlog do banco j\u00e1 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,3],"tags":[],"class_list":["post-2231","post","type-post","status-publish","format-standard","hentry","category-administration","category-backup-recovery"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/2231","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=2231"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/2231\/revisions"}],"predecessor-version":[{"id":9253,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/2231\/revisions\/9253"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=2231"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=2231"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=2231"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}