Enabling archiving to Flash Recovery Area

Flash recovery area is used primarily for recovery file like RMAN backups. Since archived redo logs also form a part of recovery, sometimes archived redos are also directed to FRA.

To start with, flash recovery area has to be configured. This is done by setting DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameters. DB_RECOVERY_FILE_DEST specifies the destination for archived logs. The destination can be filesystem or ASM. DB_RECOVERY_FILE_DEST_SIZE enforces the limit that archiving can use. This ensures that archiving does not fill up the filesystem.

Here, we will see how to set up FRA on ASM diskgroup though the steps are identical for file system as well.

Set environment and connect to SQL*Plus

$ . oraenv
<Oracle SID>

$ sqlplus /nolog
SQL> connect / as sysdba
SQL> show parameter db_recovery_file_dest

Set the flash recovery area using ALTER SYSTEM command :

SQL> alter system set db_recovery_file_dest_size=20G scope=spfile;
SQL> alter system set db_recovery_file_dest='+FRA' scope=spfile;

Set the archive destination to flash recovery area using below command :

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope = spfile;
SQL> archive log list;
SQL> col DEST_NAME FORMAT a33
SQL> col DESTINATION FORMAT a33
SQL> set line 188
SQL> select dest_id, dest_name, binding, status, target, destination from v$archive_dest;

Shut down the database using IMMEDIATE option

SQL> shutdown immediate;

Startup the database to MOUNT mode

SQL> startup mount;

Enable archiving using below command

      SQL> alter database archivelog; 
      SQL> alter database open;
      
Verification

To ensure that archiving is happening, switch log files

SQL> alter system switch logfile;

Navigate to the destination where archiving is suppose to be written and see if archive logs are present.

SQL> archive log list;
SQL> col DEST_NAME FORMAT a33
SQL> col DESTINATION FORMAT a33
SQL> set line 188

SQL> select dest_id, dest_name, binding, status, target, destination from v$archive_dest;
      
This shouldn't happen

Make sure that archived logs are not written to $ORACLE_HOME/dbs (on Unix) or %ORACLE_HOME%/database (on Windows) directories. This is an indication that there are some configuration issues and require further troubleshooting. Database writes archived redos to these platform speific locations if it is not able to write to the destination that we have configured.

Comments