FRA Configuration and Maintenance

Creating the Flash Recovery Area

In 10g FRA means Flash Recovery Area.

In 11g FRA means Fast Recovery Area

disable the below 2 parameters if they are already set:

alter system set log_archive_duplex_dest = '';

alter system set log_archive_dest = '';

To enable FRA in a database we need to set below 2 parameters in the same sequence:

db_recovery_file_dest_size

db_recovery_file_dest

First we need to set the size then the location path.

alter system set db_recovery_file_dest_size = 10G;

alter system set db_recovery_file_dest = '/u01/fra';

if we find

show parameter log_archive_dest = /u01/prod/arch

then issue the below command:

alter system set log_archive_dest_1 = 'location=/u01/prod/arch' scope=both;

Now set the previous parameter value to null:

alter system set log_archive_dest = '';

Writing Regular RMAN Backups to the FRA

rman target /

backup database;

This will take the backup pieces to fra by default.

If we want to store somewhere else we need to use channel and format commands.

Freeing FRA Space in an Emergency

We can find messages in alert log when fra is full.

Also if flashback is enabled and stored in fra we can't open the db when fra is full.

We can do the 3 following things to resolve this issue:

1. increase fra size

show parameter db_recovery;

alter system set db_recovery_file_dest_size = 20G;

We need to make sure at OS level we got space.

Same command we can use to shrink (reduce the size) the fra also.

2. remove restore points

select * from v$restore_point;

select name, storage_size from v$restore_point;

If we find any restore points which are taking space we can drop them.

drop restore point rp1;

3. disable flashback

select name, open_mode, log_mode, flashback_on from v$database;

shutdown immediate;

startup mount;

alter database flashback off;

alter database open;

select name, open_mode, log_mode, flashback_on from v$database;

We can also delete old backups or obsolete backups and archived log files.

Note: be careful while using "noprompt"

rman target = /

rman target / nocatalog

RMAN> delete noprompt archivelog all;

RMAN> delete noprompt backup of database;

RMAN> delete noprompt copy of database;

Now we can open the database.

Once the space is available we can recreate the things dropped.

create restore point rp1;

(This will create a normal restore point. System will delete flashback logs if needed space).

To create guaranteed restore point issue the below command:

create guaranteed restore point rp1;

By using the below command we can take the entire db to above restore point.

flashback database to rp1;

Checking Space Usage in the FRA

select * from v$recovery_file_dest;

This will show one line for entire fra.

To see in detail:

select * from v$flash_recovery_area_usage;

Here we can get detailed report by each type of file stored in FRA.

To know the space used by each type of file we can combine the above 2 views.

select file_type, round(space_used*percent_space_used/100/1024/1024) used, 
round(space_reclaimable*percent_space_reclaimable/100/1024/1024) reclaimable, 
frau.number_of_files
from v$recovery_file_dest rfd, v$flash_recovery_area_usage frau;

Expanding or Shrinking the FRA

If the current size is 10gb we can increase it by below command:

Make sure there must be enough spacee at OS level.

alter system set db_recovery_file_dest_size = 12G;

We can also reduce the size by the same command;

Make sure the used space in FRA must be less than 2g.

alter system set db_recovery_file_dest_size = 2G;

When we reduced the size of FRA and try to take backup by below command throws an error:

RMAN> backup as copy tablespace users;

report obsolete;

(rman retention policy will be applied to this command)

delete obsolete;

delete noprompt obsolete;

Configuring Archived Redo Logs to Go to FRA

As a best practice we should not send archived redo logs to FRA.

We can use log_archive_dest_1 to send archive logs to another area.

Default location for archived redo log files is $ORACLE_HOME/dbs in Linux and database in windows.

To send it to a different location use the below command:

alter system set log_archive_dest_1 = 'location=/u01/prod/arch';

(default scope is both)

To send the archive log files to FRA:

alter system set log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST';

Here the condition is log_archive_dest_state_1 must be enabled. By default its enabled.

alter system set log_archive_dest_state_1 = enable;

To confirm the above changes:

archive log list

Issue the below command and check the file creation inside FRA:

alter system switch logfile;

(If this command fails also it means FRA setting is not done properly)

In the above method we can check the physical presence of the file.

Below we can see the record existence in the db.

select name, completion_time from v$archived_log order by completion_time;

Placing a Control File in the FRA

We can set the control_files parameter accordingly:

control_files = ('/u01/prod/control01.ctl','/u02/fra/prod/controlfile/control02.ctl')

Placing Online Redo Log Files in FRA

During database creation we can set the below parameters:

db_create_online_log_dest_1 = '/u01/fra'

db_create_online_log_dest_2 = '/u01/fra'

make sure db_create_file_dest = ''

To confirm the creation:

select member from v$logfile;

select member from v$logfile where group#=1;

Adding new logfile group and both members in fra:

To see the fra location.

show parameter db_recovery_file_dest;

Below parameters must be null:

show parameter db_create_online_log_dest

show parameter db_create_file_dest

Now add the log file group with proper group number:

alter database add logfile group 4;

As the fra is already set this will create a new log file members in fra.

To confirm the above create command:

select member from v$logfile where group# = 4;

Also we can check physical existence of the file:

cd /u01/fra/prod/onlinelog

ls -l

The above create command will create single member in the log group.

To make sure 2 members we can set the below parameters:

alter system set db_create_online_log_dest_1 = '/u02/fra';

alter system set db_create_online_log_dest_2 = '/u02/fra';

Now run the create command:

alter database add logfile group 4;

column member format a50;
select group#, member from v$logfile order by group#;
select group#, bytes/1024/1024 "mb" from v$log order by group#;

alter database drop logfile group 4;

To confirm the creation:

select member from v$logfile where group# = 4;

We can go to the physical location of fra and confirm 2 files.

We can use the below command for faster redo log group creation:

alter database add logfile;

It will take last sequence number plus 1 as its number.

To confirm the creation:

select group#, member from v$logfile order by group# desc;

Adding new log group and one member only in fra:

For this we need to define two parameters:

1. fra 

2. db_create_file_dest

alter system set db_create_file_dest = '+DG1';

show parameter db_create_file_dest;

show parameter db_recovery_file_dest;

If the above 2 parameters are set already and we run add logfile command, group will be created with 2 members in both.

1. to create a new group

alter database add logfile group 7;

2. check how many members are created and where

select member from v$logfile where group# = 7;

Sending Image Copies to the FRA

This will happen if below 2 conditions are met:

1. the fra

2. RMAN script does not have any format command in the channel configuration

RMAN> backup as copy database;

Image copies will go to fra by default.

Deleting Backup Sets from the FRA

There is no command to delete files from fra.  Oracle does this automatically.
Based on space requirements.

rman target = /

list backupset;

delete backupset 62;

Deleting Archived Redo Logs from the FRA

RMAN> list archivelog all;

delete archivelog from logseq = 12 until logseq = 18;

Reinstating a Damaged Datafile from an Image Copy

1. To check the files in the database:

RMAN> report schema;

2. Check for existence of image copies of the damaged datafile:

rman target=/

list copy of datafile 4;

3. Take the damaged datafile offline:

RMAN>  sql 'alter database datafile 4 offline';

4. Now tell db to use image copy of the file as prod file:

RMAN>  switch datafile 4 to copy;

5. Recover the copy to make it consistent with the current state of the database:

RMAN> recover datafile 4;

6. Bring the recovered datafile online:

RMAN> sql 'alter database datafile 4 online';

This is a temporary adjustment. As soon as we get some time, should restore the production file back and switch to it.

Traditional approach to recovery is:

1. Take the tablespace offline

2. Restore the datafile from rman backup

3. Apply the incremental backups

4. Recover the datafile by applying archived redo logs

5. Bring the tablespace online

Switch method instead of traditional restore and recovery will save lot of time.

Switching Back from an Image Copy

1. Check datafiles

RMAN> report schema;

Here it shows datafile 4 is in fra.

2. Remove the file at OS level from original location. As this is unused and offline, there will be no effect on database.

$ rm /u01/prod/users01.dbf

3. connect to rman

rman target=/

4. create an image copy of the file, in this case file 4.

Place that image copy in the file's original location:

RMAN>  backup as copy datafile 4 format='/u01/prod/users01.dbf';

5. Take the datafile offline:

RMAN> sql 'alter database datafile 4 offline';

6. Switch the datafile to the copy you just placed in the original location:

RMAN> switch datafile 4 to copy;

7. Recover datafile to bring it up-to-date with changes that occurred between step 4 and step 5.

RMAN> recover datafile 4;

8. Bring the datafile online:

RMAN> sql 'alter database datafile 4 online';

9. Check the location of the file once again:

RMAN> report schema;

The file is in original location now.

10. As the best practice we must create a fresh image copy of the file and place it in the fra:

RMAN>  backup as copy datafile 4;

Backing Up the FRA to Tape

RMAN> run{
allocate channel c1 type sbt_tape;
backup recovery area;}

This run block backs up the entire flash recovery area to tape.

Sizing the Flash Recovery Area

This part comes with experience. But we can estimate some values.

Sum of size of all datafiles.

Sum of archive log generation per day.

Incremental backup per week.

Control file auto backup.