Wednesday, April 29, 2020

ORA-19815: WARNING: db_recovery_file_dest_size of 128849010 bytes is 99.03% used, and has 14136528 remaining bytes available.

You will receive ORA-19815 is reaching maximum size of flash backup recovery area (FRA) and note that this limitation will be managed by db_recovery_file_dest_size parameter

If you got this error as one time then you might be running adhoc load or some batch job. Also you will get below message in alert.log file. Please follow one of the choice to fix the issue.

ORA-19815: WARNING: db_recovery_file_dest_size of 128849010 bytes is 99.03% used, and has 14136528 remaining bytes available.
2020-02-21T11:31:19.892420-07:00

************************************************************************
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.

In case if you archive logs are going to FRA then you should run RMAN backups and delete the unnecessary files to avoid Database hung or CRASH situation.

RMAN> backup archivelog all delete input;

If the Flash recovery area is 100% then you might receive below error when starting up the database

Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 9789
Session ID: 321 Serial number: 3


Pls make sure you follow below steps to add more space to flash recovery area (FRA)

SQL> startup mount;
SQL> alter system set db_recovery_file_dest_size=<size>G scope=both;
Replace <size> with a number larger than the current and ensure is sufficient space in the destination
SQL> alter database open;


To see what FRA has run below command

SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
-------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                          0                         0               0          0
REDO LOG                              0                         0               0          0
ARCHIVED LOG                          0                         0               0          0
BACKUP PIECE                          0                         0               0          0
IMAGE COPY                            0                         0               0          0
FLASHBACK LOG                     99.76                         0             192          0
FOREIGN ARCHIVED LOG                  0                         0               0          0
AUXILIARY DATAFILE C                  0                         0               0          0

In my case I am using for FLASHBACK LOG, these logs are different from redo logs or undo. They are a separate, optional recording of database activity.

These logs are automatically deleted when FRA is our of space and logs is not needed for recvoery within the window specified in DB_FLASHBACK_RETENTION_TARGET parameter

To determine the disk quota and current disk usage in the flash recovery area, query the view
V$RECOVERY_FILE_DEST.

Thanks & Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

No comments:

Post a Comment