The Flash recovery area(FRA) is an important location in the Oracle database. It stores archivelog, flashback logs, backups etc. Here in this post, we will see how to check fra size in Oracle, flash recovery area usage query, and how to increase fra size in Oracle
how to check fra size in oracle
The above query will show the total amount allocated in FRA and the total used space
select * from v$recovery_file_dest;
flash recovery area usage query
set linesize 200 trim pool on
col name form a72
select * from v$flash_recovery_area_usage;
The above will show the division of the size of archive logs, flashback logs, and backup in the total used space. The above query will be very useful when clearing the FRA
how to increase fra size in oracle
alter system set DB_RECOVERY_FILE_DEST_SIZE=<new size> scope=both;
This is an online activity.
How to check fra location in Oracle
sqlplus / as sysdba
show parameter DB_RECOVERY_FILE_DEST
How to change FRA location in Oracle
sqlplus / as sysdba show parameter DB_RECOVERY_FILE_DEST alter system set db_recovery_file_dest='<new location>' scope=both;
Now the database will start generating the files in the new location. If the flashback is set to ON, flashback logs will keep generating at the old location. We can enable and disable flashback to generate the flashback logs on the new location
How to clear the FRA
Oracle automatically Manage the space in FRA if the following configurations are done Properly
Configure RMAN retention policy: This tells about the backup retention policy, how long to keep the backup
Configure archived log deletion policy (RMAN): This tells about the archivelog retention. Generally, we set it to delete the archive after one backup is done for archive logs. If it is set to NONE, oracle implements the default settings
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY BACKED UP 1 TIMES TO DISK;
flashback retention: This tells about the flashback log retention
You may still get issues after doing these settings, Then following steps can be checked and done
If the usage report shows, archivelog filling space, then check
- Check If the archive logs backup is happening
- what is the setting of the Configure archived log deletion policy?
You can use the below commands to backup and delete depending on the condition
RMAN>BACKUP archivelog all;
RMAN>CROSSCHECK ARCHIVELOG ALL;
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;
RMAN>Delete archivelog all completed before 'SYSDATE-7';
If the usage report shows, flashback logs filling space, then check
- Check if you have any guaranteed restore point
- Check if the flashback is enabled and how much the flashback retention size
You can drop the guaranteed restore point if not required
Drop restore point <restore point name> ;
You can disable the flashback if not required and then enable it. It will delete all flashback logs
Alter database FLASHBACK OFF;
Alter database FLASHBACK ON;
You can reduce the flashback retention size to decrease the space consumed by flashback logs
show parameter DB_FLASHBACK_RETENTION_TARGET
alter system set DB_FLASHBACK_RETENTION_TARGET=60;
I hope you like this short and quick post on how to check fra size in Oracle
Related Articles
oracle create tablespace: This article on how to create tablespace in oracle, various characteristics associated with it and different create tablespace statements
ORA-01652: ORA-01652 error usually because when the tablespace does not have free space in Permanent and Temporary tablespace in oracle database. Check out how to resolve it
shrink datafile in Oracle: Check out how to shrink the datafile and reclaim space on the filesystem. How to resolve ORA-03297
Oracle database administration tutorial: This lists all the Oracle DBA-related stuff. Very helpful for administration
how to change default tablespace in oracle: Default tablespace is the tablespace where the objects are created when no tablespace name is specified by users. Find out how to check default tablespace
How to check temp tablespace in Oracle: This article is about temp tablespace in Oracle, resize the tempfile, drop the temp file, find the temp usage by Session
alter tablespace add datafile: Check out this post on How to add a datafile to tablespace in Oracle, add tempfile to the temporary tablespace, how to add datafile in ASM
https://docs.oracle.com/cd/B19306_01/backup.102/b14192/setup005.htm