Flashback Recovery Area Full - Unable to connect RMAN

Yesterday, There was an issue with the Flashback Recovery Area. The FRA disk is full and when we try to connect as "sys as sysdba" or "rman target", the database hangs.


If you are looking for a solution to similar scenario then read on...

Check the disk space for total, available and used space using df command in linux.

move to the archivelog folder in the flash recovery area for the database and check for the old logs.

If you have set the retention window to 7 days for example or have a backup taken 2-3 days before then you can remove the old logs to reclaim the disk space.

Now try to connect using the RMAN

rman target nocatalog

execute the crosscheck command to verify the backups and delete expired backups and logs.

Note, By removing the logs at OS level will not update the data dictionary hence the crosscheck command needs to be executed to cross verify the logs and files.

How to create flashback restore point on database

How to create flashback restore point on database?

If the database is up, shutdown the database and restart the database in mount mode.

Check whether the flashback is on, If the flashback is not on then enable the flashback. Note: If you are using RAC then before enabling flashback make sure all other instances are shutdown

If you executed the flashback on command without shutting down the other instances, you will receive the below error

ERROR at line 1:
ORA-38777: database must not be started in any other instance.

Solution is to make sure all other instances are down.

After enabling the flashback, check the status to make sure the flashback is enabled on the database.

And check the DB_FLASHBACK_RETENTION_TARGET. By default it is 1440 which is one day. If you want for “n” no of days then the formula is 1440*n=value. For example if you want to retain for 3 days then 1440*3=4320

Alter the database to set the retention to 3 days.

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;

And then create the restore point as below

CREATE RESTORE POINT before_my_change;