Main file types are archivelog, flashback log, backups, as well as mirrors for your control files and redo log files.
All files in the FRA are Oracle-managed files. Using a Flash Recovery Area simplifies the administration of your database by automatically retaining them for as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed, because the space is needed for another backup and recovery-related purpose.
The FRA size is set with only one parameter for all file types together, but we can do some calculations on the size needed for individual file types.
Flash Recovery Area needs
Before you start configuring your FRA sizing, you need to define your own needs for the recovery windows and retention time.
- How long do you need to keep your backups?
- How do you want to use flashback database?
- How much archivelog do you want to keep on disk?
Checking the current usage
You can check the configuration by looking at two parameters.
SQL> SHOW parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big INTEGER 2300G
The current FRA usage can be checked with the views v$recovery_area_usage (for each file type) and v$recovery_file_dest (for overall size and usage).
set lines 120
break on report
compute sum of percent_space_used on report
compute sum of percent_space_reclaimable on report
select file_type
, percent_space_used
, percent_space_reclaimable
, number_of_files
, con_id
from v$recovery_area_usage
order by 1
/
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
ARCHIVED LOG 61.57 61.57 243 0
AUXILIARY DATAFILE COPY 0 0 0 0
BACKUP PIECE 0 0 0 0
CONTROL FILE 0 0 0 0
FLASHBACK LOG 18.08 .34 53 0
FOREIGN ARCHIVED LOG .12 0 5 0
IMAGE COPY 0 0 0 0
REDO LOG 0 0 0 0
------------------ -------------------------
sum 79.77 61.91
8 rows selected.
col name format a7
clear breaks
clear computes
select name
, round(space_limit / 1024 / 1024) size_mb
, round(space_used / 1024 / 1024) used_mb
, decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used
from v$recovery_file_dest
order by name
/
NAME SIZE_MB USED_MB PCT_USED
------- ---------- ---------- ----------
+RECO 1201138 958099 80
The v$recovery_area_usage view gives information about reclaimable files. Files that are reclaimable will be removed by the database when the space is needed for other purposes. This is done when the usage of the FRA is about 80% of the defined size (db_recovery_file_dest_size).
For monitoring your FRA, you need to check your unreclaimable space. Just checking the percentage of the FRA in use is not very helpful, because it will often be around 80%.
This query combines the two views to calculate the PERCENT_SPACE_NOT_RECLAIMABLE. If it is around (or above) 80% you will need to act, because that is a situation where your actual FRA usage will also rise above 80%. It is an indication that Oracle cannot remove files, because all files need to be kept for recovery purposes. The most common problem with an undersized FRA is that the database will hang when it cannot create an archivelog file at time of a logswitch.
col name format a7
clear breaks
clear computes
select name
, round(space_limit / 1024 / 1024) space_limit_mb
, round(space_used / 1024 / 1024) space_used_mb
, percent_space_used
, percent_space_reclaimable
, percent_space_not_reclaimable
from v$recovery_file_dest
, ( select sum(percent_space_reclaimable) percent_space_reclaimable
, sum(percent_space_used) percent_space_used
, sum(percent_space_used - percent_space_reclaimable) percent_space_not_reclaimable
from v$recovery_area_usage)
order by name
/
NAME SPACE_LIMIT_MB SPACE_USED_MB PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE PERCENT_SPACE_NOT_RECLAIMABLE
------- -------------- ------------- ------------------ ------------------------- -----------------------------
+RECO 1201138 958099 79.77 61.91 17.86
Configuration FRA size
Configuration of the FRA size is done with the parameter db_recovery_file_dest_size.
SQL> SHOW parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big INTEGER 2300G
SQL> ALTER system SET db_recovery_file_dest_size=3500G scope=BOTH ;
System altered.
SQL>
Configuration in a RAC cluster
For a RAC cluster, you should configure a shared recovery area for all instances, with the same location and size for all instances.
It is good to know that the contents of the v$recovery_area_usage are also about the files from all instances. Queries on this view will give the same results on all instances. There is no gv$recovery_area_usage view.
SQL> SHOW parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big INTEGER 2300G
SELECT inst_id , VALUE / (1024 * 1024 * 1024) GB
FROM gv$parameter
WHERE name = 'db_recovery_file_dest_size'
/
INST_ID GB
---------- ----------
1 2300
2 2300
SQL>
SQL> ALTER system SET db_recovery_file_dest_size=3500G sid='*' scope=BOTH ;
System altered.
SQL> SELECT inst_id , VALUE / (1024 * 1024 * 1024) GB
FROM gv$parameter
WHERE name = 'db_recovery_file_dest_size'
/
INST_ID GB
---------- ----------
1 3500
2 3500
SQL>
So, in this case, you would use a maximum of 3500 GB on the +RECO disk group for the database. You do not need 3500 GB for each instance.
Configuring archivelog deletion
You can define your archivelog deletion policy in RMAN. If there is no archived redo log deletion policy in RMAN, the files can be deleted when backed up at least once to disk or SBT. Or the logs are obsolete according to the backup retention policy.
If you do create an archivelog deletion policy, they can be deleted after you meet the requirements in the policy.
Examples are:
- CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO SBT;
- CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
- CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
The deletion policy itself does not delete archivelog files. It is a protection; files are not deleted if it conflicts with the policy.
Two common ways to delete archivelogs are:
- Delete as part of your backup script
- Do not create any cleanup script and let the database handle it
A common RMAN script for archivelog files is:
backup device type sbt archivelog all not backed up 1 times ;
delete noprompt archivelog until time = 'sysdate-1' backed up 1 times to sbt ;
The delete statement will check the delete policy, so files won't be deleted if that conflicts with the policy. However, you will receive an error during the backup:
archived log file name=+RECO/MYDB/ARCHIVELOG/2020_06_26/thread_1_seq_2.4585.1044140407 thread=1 sequence=2
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
If you don't run a delete in backup script, the files can just be deleted by the database, based on your configuration. Remember, we are using OMF for the FRA, so these are Oracle managed files.
For example, if you have a data guard standby database and no backup running on the standby, you will find that the database will delete archivelogs that are applied. This is about the files that are "reclaimable"in the v$recovery_area_usage view. So, when the database runs out of space, it will remove some of the reclaimable files, as you can see in the alert log:
Mon Jul 06 04:55:55 2020
Deleted Oracle managed file +RECO/MYDB/ARCHIVELOG/2020_06_30/thread_1_seq_55913.1742.1044422411
Deleted Oracle managed file +RECO/MYDB/ARCHIVELOG/2020_06_30/thread_2_seq_63726.1586.1044422409
Mon Jul 06 04:58:12 2020
Do not create a shell script that will just delete archivelog files. If your database doesn't know you removed them, you can still get an error if you run out of space in your FRA. Always use RMAN to delete archivelog files.
Calculating archivelog size
How much archivelog is created? You can check the size of your redo log files in v$log and multiply it with the average number of log switches from v$loghist. But that’s not perfect, as you can do a log switch before the redo log file is 100% full.
It’s better to look at the v$archived_log view. This will have more information about all the created archivelogs:
- If you use the gv$archived_log version, you will see all instances of a RAC database.
- If you check with gv$archive_dest.destination = 'USE_DB_RECOVERY_FILE_DEST' you will only get files in the FRA (so we will skip remote destinations used by Data Guard)
- If you check the status, you can see if the file is still available in the FRA
select trunc(l.first_time) arch_date
, l.inst_id
, l.status
, n.destination
, round(sum( blocks * block_size ) / ( 1024 * 1024 * 1024 ) ) total_file_size_gb
, count(*) file_count
from gv$archived_log l
, gv$archive_dest n
where l.inst_id = n.inst_id
and l.dest_id = n.dest_id
and n.destination = 'USE_DB_RECOVERY_FILE_DEST'
group by trunc(l.first_time)
, l.inst_id
, l.status
, n.destination
order by trunc(l.first_time)
, l.inst_id
, l.status
, n.destination
/
ARCH_DATE INST_ID S DESTINATION TOTAL_FILE_SIZE_GB FILE_COUNT
--------- ---------- - ------------------------- ------------------ ----------
08-JUN-20 1 D USE_DB_RECOVERY_FILE_DEST 108 46
08-JUN-20 2 D USE_DB_RECOVERY_FILE_DEST 108 46
09-JUN-20 1 D USE_DB_RECOVERY_FILE_DEST 299 116
09-JUN-20 2 D USE_DB_RECOVERY_FILE_DEST 299 116
10-JUN-20 1 D USE_DB_RECOVERY_FILE_DEST 441 167
10-JUN-20 2 D USE_DB_RECOVERY_FILE_DEST 441 167
11-JUN-20 1 D USE_DB_RECOVERY_FILE_DEST 406 146
11-JUN-20 2 D USE_DB_RECOVERY_FILE_DEST 406 146
[....]
08-JUL-20 1 A USE_DB_RECOVERY_FILE_DEST 225 76
08-JUL-20 2 A USE_DB_RECOVERY_FILE_DEST 225 76
64 rows selected.
The status can have several values:
- A - Available
- D - Deleted
- U - Unavailable
- X - Expired
So, based on this column we can calculate how much archivelog is created and how much is still available in the FRA.
select trunc(l.first_time) arch_date
, round(sum(decode(l.status,'D',blocks * block_size,0)/(1024 * 1024 * 1024))) deleted_gb
, round(sum(decode(l.status,'A',blocks * block_size,0)/(1024 * 1024 * 1024))) available_gb
, round(sum(decode(l.status,'U',blocks * block_size,0)/(1024 * 1024 * 1024))) unavailable_gb
, round(sum(decode(l.status,'X',blocks * block_size,0)/(1024 * 1024 * 1024))) expired_gb
, round(sum(blocks * block_size) /(1024 * 1024 * 1024)) total_size_gb
from gv$archived_log l
, gv$archive_dest n
where l.inst_id = n.inst_id
and l.dest_id = n.dest_id
and n.destination = 'USE_DB_RECOVERY_FILE_DEST'
group by trunc(l.first_time)
order by trunc(l.first_time)
/
ARCH_DATE DELETED_GB AVAILABLE_GB UNAVAILABLE_GB EXPIRED_GB TOTAL_SIZE_GB
--------- ---------- ------------ -------------- ---------- -------------
08-JUN-20 217 0 0 0 217
09-JUN-20 599 0 0 0 599
10-JUN-20 883 0 0 0 883
11-JUN-20 812 0 0 0 812
12-JUN-20 625 0 0 0 625
13-JUN-20 739 0 0 0 739
14-JUN-20 618 0 0 0 618
15-JUN-20 761 0 0 0 761
16-JUN-20 789 0 0 0 789
17-JUN-20 852 0 0 0 852
18-JUN-20 867 0 0 0 867
19-JUN-20 811 0 0 0 811
20-JUN-20 697 0 0 0 697
21-JUN-20 686 0 0 0 686
22-JUN-20 740 0 0 0 740
23-JUN-20 781 0 0 0 781
24-JUN-20 891 0 0 0 891
25-JUN-20 898 0 0 0 898
26-JUN-20 806 0 0 0 806
27-JUN-20 752 0 0 0 752
28-JUN-20 750 0 0 0 750
29-JUN-20 776 0 0 0 776
30-JUN-20 910 0 0 0 910
01-JUL-20 1048 0 0 0 1048
02-JUL-20 1021 0 0 0 1021
03-JUL-20 865 0 0 0 865
04-JUL-20 333 0 0 0 333
05-JUL-20 317 37 0 0 355
06-JUL-20 0 437 0 0 437
07-JUL-20 0 521 0 0 521
08-JUL-20 0 450 0 0 450
31 rows selected.
So, now we know how much archivelog is created daily in the FRA.
We only need to decide how long we want to keep the archivelogs. This depends on several factors:
- How often do you make a backup? Making a backup can make the space used by the archivelog reclaimable
- Even after you made a backup, you might want to keep the archivelog on disk for some time, because recovering your database is faster when you don't need to restore all the files first
- In a data guard environment, you might want to keep files longer, because you can't remove them before they are shipped to the standby databases
Now we can calculate how much FRA space we need for archivelog.
Configuring flashback log
Before you can use flashback, you should turn the option on and configure the retention target.
SQL> SELECT flashback_on FROM v$database ;
FLASHBACK_ON
------------------
NO
SQL> ALTER DATABASE flashback ON ;
DATABASE altered.
SQL> SELECT flashback_on FROM v$database ;
FLASHBACK_ON
------------------
YES
SQL>
SQL> ALTER system SET db_flashback_retention_target=1440 scope=BOTH ;
System altered.
SQL>
SQL> SHOW parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target INTEGER 1440
SQL>
The flashback sizing can be viewed in v$flashback_database_log (or gv$flashback_database_log for a RAC database).
SQL>
SELECT inst_id
, to_char(oldest_flashback_time,'dd-mm-yyyy-hh24:mi') oldest_flashback_time
, retention_target
, round((sysdate - oldest_flashback_time ) * (60 * 24)) actual_retention_possible
, round(flashback_size / (1024 * 1024 * 1024)) flashback_size_gb
, round(estimated_flashback_size / (1024 * 1024 * 1024)) estimated_flashback_size_gb
FROM gv$flashback_database_log
/
INST_ID OLDEST_FLASHBACK RETENTION_TARGET ACTUAL_RETENTION_POSSIBLE FLASHBACK_SIZE_GB ESTIMATED_FLASHBACK_SIZE_GB
---------- ---------------- ---------------- ------------------------- ----------------- ---------------------------
2 07-07-2020-12:44 1440 1512 301 208
1 07-07-2020-12:44 1440 1512 301 208
The db_flashback_retention_target is exactly what the name implies: a retention target. If the FRA is large, you can have more; because these are OMF files, flashback files are only removed if the database needs the storage for other things. If the FRA is too small, you may not have enough to reach the target.
In this case, the target is 1440 minutes (1 day), but we have storage in use for a possible flashback of 1512 minutes. We have 301 GB in use, but only need about 208 to reach the target. So, some of the space is reclaimable:
SQL> SELECT * FROM v$recovery_area_usage WHERE FILE_TYPE = 'FLASHBACK LOG' ;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
FLASHBACK LOG 6.85 1.55 76 0
SQL>
If you want to be sure you can flashback your database, you need to make a guaranteed restore point (aka a snapshot). The database will then keep the needed flashback files until you drop the restore point, even if you exceed the db_flashback_retention_target.
SQL> CREATE restore point my_rp guarantee flashback DATABASE;
Restore point created.
SQL> SELECT name , storage_size FROM v$restore_point
NAME STORAGE_SIZE
---------- ------------
MY_RP 4294967296
SQL> DROP restore point my_rp ;
Restore point dropped.
SQL>
Configuring backups
Definition of your retention policy should be done in RMAN. Your policy can be a recovery window or the number of backups you want to save.
The actual usage of storage in the FRA can of course be checked again in the v$recovery_area_usage view, looking at the file types IMAGE COPY, AUXILIARY DATAFILE COPY and BACKUP PIECE.
In RMAN, you can define:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
If you keep the defaults, the backup files will be stored in the FRA with retention policy redundancy 1. Meaning that if I make two backups, 1 will be reclaimable.
RMAN> show DEFAULT DEVICE TYPE;
RMAN configuration parameters for database with db_unique_name RCAT are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
RMAN> show RETENTION POLICY;
RMAN configuration parameters for database with db_unique_name RCAT are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
RMAN> backup tablespace cattbs;
RMAN> backup tablespace cattbs;
And then check the FRA again:
SQL> SELECT * FROM v$recovery_area_usage WHERE file_type = ‘BACKUP PIECE’;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
BACKUP PIECE .8 .06 9 0
Alter a "Delete obsolete" in RMAN. The first backup is removed, and the reclaimable space is back to 0.
RMAN's crosscheck command will resolve the inconsistency between the FRA usage in the database and on disk.
Be aware that the most common problem with an undersized FRA is a database hang, because the database cannot create an archivelog file at time of a logswitch. The most common solution is to create a backup of the archivelogs, which will also delete these archivelogs.
This doesn't make much sense if both the archivelogs and the backups are stored in the FRA. You can't create a backup in the FRA, when the FRA runs out of space.
So, always investigate if you can locate backups outside the FRA. See also this Oracle support note: How to disable use of Flash Recovery Area for Archivelogs and Backups (Doc ID 297397.1).
Compression
If you are licensed to use the advanced compression option, you can compress RMAN backups while they are created.
Automatic compression of archivelog files is still not implemented by Oracle; see Oracle support Doc ID 2449903.1.
You should not write your own scripts to compress archivelogs in the FRA. If you want to save space using your own scripts, you should create an archive location outside the FRA.
Conclusion
A full FRA can cause a hanging database, so a system down. When monitoring the FRA, focus on the space that is unreclaimable and not just on the actual usage. Make sure you know your needs for your recovery window and flashback target.
Store the RMAN backups outside the FRA, if possible. If you want to make sure you can use a flashback database, you should create a guaranteed restore point. And don't forget to drop it, when it’s no longer needed.
Always let the database or your RMAN backup script handle files in the FRA, because they are all Oracle Managed Files
.
Article taken from -https://www.qualogy.com/techblog/oracle/managing-the-oracle-database-flash-recovery-area
