Wednesday, July 16, 2014

Archive log files are not getting stored in DB_RECOVERY_FILE_DEST



Archive log files are configured to use db_Recovery_file_dest which has been set to ASM. But the archive log files are getting stored in $ORACLE_HOME/dbs instead of ASM disk group

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Check Archive logs location
#########################################

set lines 200
col name for a90
col thread# for 9999
col creator for a8
col completion_time for a20

Select name,creator,thread#,sequence#,applied,status,to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') "completion_time",archived,backup_count
from v$archived_log order by sequence#;

NAME                                                                                       CREATOR  THREAD#  SEQUENCE# APPLIED   S completion_time      ARC BACKUP_COUNT
------------------------------------------------------------------------------------------ -------- ------- ---------- --------- - -------------------- --- ------------
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_3_852382837.dbf  ARCH           1          3 NO        A 08-JUL-2014 18:22:57 YES            0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_4_852382837.dbf  ARCH           1          4 NO        A 08-JUL-2014 18:23:36 YES            0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_13_852382837.dbf ARCH           1         13 NO        A 09-JUL-2014 16:50:34 YES            0

=====================================================================================================================

#########################################
# 2) Check Log_Archive Destination
#########################################

archive Log List

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

SQL> sho parameter log_archive_dest_1
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=USE_DB_RECOVERY_FILE_
                                                 DEST, valid_for=(ALL_LOGFILES,
                                                  ALL_ROLES)

=====================================================================================================================

#########################################
# 3) So What's the Issue
#########################################

From Step 1), we can see that archive logs are getting stored in $ORACLE_HOME/dbs location

From Step 2), it clearly shows the db_recovery_file_dest is being used.

Seems like the parameters are configured correctly then why archive logs are not stored in ASM disk group.

=====================================================================================================================

#########################################
# Solution
#########################################

Eventhough the configuration seems to be correct, there is a small mistake in defining log_archive_dest_1 parameter,

If we can see the keyword used in log_archive_dest_1 ( location=USE_DB_RECOVERY_FILE_DEST, )

A Comma (,) is being used at the end of the location keyword which seems to be the root cause of this issue.

#### Modifying the Parameter after removing comma (,)

Alter system set log_archive_dest_1 = 'location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES, ALL_ROLES)' sid='*';

SQL> Alter system set log_archive_dest_1 = 'location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES, ALL_ROLES)' sid='*';
System altered.

SQL> sho parameter log_archive_dest_1
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=USE_DB_RECOVERY_FILE_
                                                 DEST valid_for=(ALL_LOGFILES,
                                                 ALL_ROLES)
SQL> Alter system switch logfile;
System altered.
SQL>

NAME                                                                                       CREATOR  THREAD#  SEQUENCE# APPLIED   S completion_time      ARC BACKUP_COUNT
------------------------------------------------------------------------------------------ -------- ------- ---------- --------- - -------------------- --- ------------
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_13_852382837.dbf ARCH           1         13 NO        A 09-JUL-2014 16:50:34 YES            0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_14_852382837.dbf ARCH           1         14 NO        A 09-JUL-2014 16:50:34 YES            0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_15_852382837.dbf ARCH           1         15 NO        A 09-JUL-2014 16:50:34 YES            0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_16_852382837.dbf ARCH           1         16 NO        A 10-JUL-2014 12:16:19 YES            0
+ARCH/oralin/archivelog/2014_07_10/thread_1_seq_17.264.852568337                           ARCH           1         17 NO        A 10-JUL-2014 16:32:17 YES            0

The new archive logs are getting created in +ARCH disk group.

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

No comments: