FRA space full oracle

Make it simple

we often as a dba run into trouble ,when flash recovery area of one of critical database gets full due to bulk DML as a cause of business transactions.

This causes the database to hang and cause impact to the applications.Though ,it is not in our hands as we cant control it until the architecture .

Hence a good monitoring system should be implemented to overcome these situations.

The parameter set for FRA is db_recovery_file_dest_size

If the parameter is not set sufficient to balance the archivelog generations ,then database get issues.

We get “ORA-12154 ” and “ORA-00257: archiver error”

SQL>show parameter db_recovery_file_dest_size;

db_recovery_file_dest_size 100G

check the reason for the FRA space full and free space using below useful views

SQL>select * from v$flash_recovery_area_usage;

SQL>select * from v$recovery_file_dest;

To resolve this error,we can either increase the FRA or delete the archivelogs from disk which are older.

Solution 1:

SQL>alter system set db_recovery_file_dest_size=200G;

Solution 2:

Connect to RMAN ,then we can delete the archivelogs which are older.If backups are taken every 12 hours in database we can consider deleting archivelogs generated before 1 day.

You can delete obsolete,expired backups which are no longer needed for recovery to clean up space.You can write a shell script to monitor the space for FRA and make them clear automatically which we will see in the future posts.

In case you have a standby database ,then archivelog deletion policy can be enabled in RMAN configuration.

~]rman target /

run { 
configure archivelog deletion policy to applied on all standby;
allocate channel "c1" device type disk; 
allocate channel "c2" device type disk; 
crosscheck backup; 
crosscheck archivelog all; 
delete noprompt expired backup; 
crosscheck archivelog all; 
delete noprompt expired archivelog all; 
delete noprompt obsolete recovery window of 30 days; 
backup archivelog all delete input; 
crosscheck archivelog all;
delete noprompt archivelog until time 'sysdate-1' backed up 1 times to device type disk; 
}

You can also run periodic archive backups and delete them.Put backup configuration script in crontab to run every hour

something like this

~]crontab -e

0 */1 * * * /home/oracle/archivebackup.sh >/dev/null 2>&1

The maximum that you can do to clear FRA are above solutions. You can notify your application user to batch jobs during off peak hours to avoid bulk transactions concurrently.

Published by kishan

You will know about me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: