Today I faced very funny issue related to FRA – Flash Recovery Area.

Linux guys informed us to clear old files as one of the mount point on database server had reached to its threshold free space.

After investigating we found that one of database ORACLE_HOME was 192GB is size. Definitely something was wrong with it.

I found that archivelogs are getting created under $ORACLE_HOME/dbs location though we were using FRA under ASM diskgroup.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination db_recovery_file_dest
Oldest online log sequence 421
Next log sequence to archive 425
Current log sequence 425

SQL> sho parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +T_ARCHIVING
db_recovery_file_dest_size big integer 80G

SQL> sho parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=DB_RECOVERY_FILE_DEST

I was thinking everything is proper but still y archives are getting generated on filesystem instead of FRA 😦

Definitely somewhere there was mistake but where… I followed simple approach to go through Oracle docs related to FRA.

Here I found following :

If a fast recovery area is configured, then you can add the fast recovery area as an archiving destination by setting any LOG_ARCHIVE_DEST_n parameter to LOCATION=USE_DB_RECOVERY_FILE_DEST.

So here is the culprit, parameter log_archive_dest_1 was set to “LOCATION=DB_RECOVERY_FILE_DEST” instead of “LOCATION=USE_DB_RECOVERY_FILE_DEST” 😀

Making necessary changes in said parameter resolved issue.

SQL> sho parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_DEST

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 423
Next log sequence to archive 427
Current log sequence 427

ASMCMD [+T_ARCHIVING/DATAMIG/ARCHIVELOG/2014_08_12] > ls
thread_1_seq_426.319.855411089
thread_1_seq_427.3475.855411181

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath