How to Manage Space of The FAST RECOVERY AREA

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.

• Generate Archive redo logs
• Create and save backups
• Create restore points
• Turn on flashback (if it is turned off)

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.

Step1- connect with the target database using RMAN

[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Feb 1 13:52:54 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB1 (DBID=1093429351)

RMAN> 

Step 2- Delete the old files

RMAN> 

RMAN> delete obsolete;

using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=93 device type=DISK
no obsolete backups found

RMAN> 

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. 

Step 1 – Check the details of the restore points

-- connect with the database using sys user
[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 1 13:56:57 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> col name format a15;
SQL> 
SQL> select name,storage_size from v$restore_point;

no rows selected

SQL> 

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

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

[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Feb 1 14:02:38 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB1 (DBID=1093429351)

RMAN> 

Step 2 – Delete the archive logs

[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Feb 1 14:02:38 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB1 (DBID=1093429351)

RMAN> 

RMAN> 

RMAN> delete archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=93 device type=DISK
List of Archived Log Copies for database with db_unique_name CDB1
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
1       1    126     A 01-FEB-23
        Name: /u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_126_1119711914.dbf


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/arch1_126_1119711914.dbf RECID=1 STAMP=1127656818
Deleted 1 objects


RMAN> 

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

Step 1 – Connect with the database using SQL

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 1 14:05:27 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

Step 2 – Run The Alter System

SQL> show parameter db_recovery_file_dest_size;

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest_size           big integer
0
SQL> 
SQL> show user;
USER is "SYS"
SQL> 
SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 20G;

System altered.

SQL> 

SQL> show parameter db_recovery_file_dest_size;

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest_size           big integer
20G
SQL> 

The above DDL will increase the size of the fast recovery area to 20G which indicates 20GB.

 

5. Disable the Flashback

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 1 14:09:12 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
SQL> ALTER DATABASE flashback off;

Database altered.

SQL> 

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.

[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 1 14:09:12 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
SQL> ALTER DATABASE flashback off;

Database altered.

SQL>