O script abaixo nos reporta informações mínimas sobre a FRA (Fast Recovery Area) do ambiente:
SQL> SET LIN 300
COL NAME FORMAT A30
COL SPACE_LIMIT FORMAT 99999
COL SPACE_USED FORMAT 99999
SELECT 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>
NAME SPACE_LIMIT_MB SPACE_USED_MB SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------ -------------- ------------- ----------------- ---------------
/oracle/fra 700 171.296875 0 5
SQL>
Já o caso abaixo nos demonstra, em termos percentuais, o consumo de cada tipo de arquivo dentro da FRA:
SQL> SELECT FILE_TYPE,PERCENT_SPACE_USED,PERCENT_SPACE_RECLAIMABLE,NUMBER_OF_FILES FROM V$RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------------- ------------------ ------------------------- ---------------
CONTROL FILE 1.44 0 1
REDO LOG 21.43 0 3
ARCHIVED LOG 0 0 0
BACKUP PIECE 1.46 0 1
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
AUXILIARY DATAFILE COPY 0 0 0
8 rows selected.
SQL>
Para tentar simular o estouro da FRA, vou diminuir o seu tamanho e ver o comportamento:
[oracle@oel8 oracle]$ sql
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Feb 28 15:08:07 2021
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> alter system set db_recovery_file_dest_size=180M;
System altered.
SQL>
No alertlog do banco já é possível ver a mensagem que a FRA está quase em seu esgotamento total:
2021-02-28 15:08:48.549000 -03:00
Errors in file /oracle/18.0.0/base/diag/rdbms/rmandb/RMANDB/trace/RMANDB_m002_6256.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 188743680 bytes is 95.16% used, and has 9125888 remaining bytes available.
************************************************************************
You have following choices to free up space from 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.
************************************************************************
ALTER SYSTEM SET db_recovery_file_dest_size=180M SCOPE=BOTH;
Este limite é possível ser consultado também através da query abaixo:
SQL> SELECT OBJECT_TYPE, MESSAGE_TYPE, MESSAGE_LEVEL, REASON, SUGGESTED_ACTION FROM DBA_OUTSTANDING_ALERTS;
OBJECT_TYPE MESSAGE_TYPE
---------------------------------------------------------------- ------------
MESSAGE_LEVEL
-------------
REASON
--------------------------------------------------------------------------------
SUGGESTED_ACTION
--------------------------------------------------------------------------------
RECOVERY AREA Warning
5
db_recovery_file_dest_size of 188743680 bytes is 95.16% used and has 9125888 rem
aining bytes available.
Choices to free up space from recovery area: 1. Consider changing RMAN RETENTION
OBJECT_TYPE MESSAGE_TYPE
---------------------------------------------------------------- ------------
MESSAGE_LEVEL
-------------
REASON
--------------------------------------------------------------------------------
SUGGESTED_ACTION
--------------------------------------------------------------------------------
POLICY. If you are using Data Guard, then consider changing RMAN ARCHIVELOG DEL
ETION POLICY. 2. Backup files to tape using RMAN BACKUP RECOVERY AREA command. 3
. Add disk space and increase db_recovery_file_dest_size parameter. 4. Delete un
necessary files using RMAN DELETE command. If OS command was used to delete file
s, then use RMAN CROSSCHECK and DELETE EXPIRED commands.
OBJECT_TYPE MESSAGE_TYPE
---------------------------------------------------------------- ------------
MESSAGE_LEVEL
-------------
REASON
--------------------------------------------------------------------------------
SUGGESTED_ACTION
--------------------------------------------------------------------------------
SQL>
Até o momento, o fenômeno ainda é considerado Warning, mas a partir dos 97%, passa a ser considerado como Critical. Uma boa fonte oficial de como atuar em problemas de espaço na FRA é a Nota “Space issue in Fast / Flash Recovery Area – FRA Full (Doc ID 829755.1)“.
Portuguese
English
Spanish