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:
Post a Comment