Managing the space of the fast recovery area (FRA) is as crucial as managing the core of the database. There could be many consequences of not having enough space in the FRA. For example, your database won’t be able to –
- Generate Archive redo logs
- Create and save backups
- Create restore points
- Turn on flashback (if it is turned off)
In any case, you don’t want either of these to happen. Let’s learn how to free up the already occupied space from the Fast Recovery area in Oracle Database.
If you don’t know what is Fast Recovery Area and How to configure it then read my blog on FRA. That will help you!
There are 5 things that you can do to either free up the already used area or increase the space of your fast recovery area.
1. Delete Old files
First thing that you can do is to delete the old unwanted files. Here you need to make sure that all the files that are needed for database recovery should remain intact. Therefore it is not advisable to delete the old files manually. Instead let the RMAN do the work for you. Here is how –
Step1- connect with the target database using RMAN
C> RMAN target /
watch how to connect with RMAN on my YouTube channel.
Step 2- Delete the old files
RMAN> delete obsolete;
The above command will automatically delete all the old and unwanted files that are of no use for database recovery for you. All the files that are still required for the database recovery will remain intact.
2. Remove Restore Points
Restore points consume a lot of space in Fast recovery area. We can reclaim that space by deleting old restore points that are not currently in use. Like this –
Step 1 – Check the details of the restore points
first let’s see how many restore points do we have and how much space they are consuming
-- connect with the database using sys user C> SQLPLUS / as SYSDBA -- format the SELECT statement SQL>col name format a15; --Query the v$restore_point view SQL> SELECT name, storage_size FROM v$restore_point;
The above statement will show you the name of all the restore points created into the connected database along with the space that they are consuming.
Step 2 – Drop the restore point
Let’s say we have a restore point with the name RP1 that is consuming a lot of space and is of no use right now. Let’s see how to drop it –
SQL> DROP restore point RP1;
The above statement will drop the restore point whose name is RP1.
3. Delete Archive logs
Similar to restore points archive redo logs also consume a lot of space. We can reclaim that space just by deleting them. To do that we first need to connect with the database using RMAN.
Step 1 – Connect With The RMAN
To delete the archive logs we first need to connect with the database using RMAN. Like this
C> RMAN target /
Step 2 – Delete the archive logs
Once the connection is established, next you have to delete the archive logs. Like this
RMAN> DELETE archivelog ALL;
The above command will delete all the archive logs and free up the space taken by them in the Fast recovery area.
4. Increase the Space
If you don’t want to perform any of the above-mentioned delete operations then you can opt to increase the space of your fast recovery area.
Let’s say the space of my fast recovery area is 10GB and I want to change it to 20GB.
Step 1 – Connect with the database using SQL
-- connect with the database using SQL C> SQLPLUS / as SYSDBA
Step 2 – Run The Alter System
In order to increase the space of the fast recovery area we need to execute the ALTER SYSTEM DDL like this
SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 20G;
The above DDL will increase the size of the fast recovery area to 20G which indicates 20GB.
5. Disable the Flashback
If none of the above works and you have to take a drastic step then go with this option. Remember, disabling flashback is neither recommended nor advisable.
Step 1 – Connect With The Database Using SQL
first, connect with the database using SQL
C> SQLPLUS / as SYSDBA
Step 2 – ALTER The Database
Once connected, next alter the database using ALTER DATABASE DDL like this
SQL> ALTER DATABASE flashback off;
On execution of this statement, flashback will be turned off and the database will stop generating flashback logs. This should reduce the space requirement of the FRA.
Turning off flashback is not recommended and not advisable at all. Turn off flashback only if it is utterly necessary otherwise don’t.
That is how we manage the space of the fast recovery area in the Oracle Database. Hope you enjoyed if so then share the link of this blog on your social media.
Thanks and Have a Great Day!