Saturday, April 30, 2011


Once  my friend call me and told that he has confusion about the  LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_n . I explain him and decided to post it here .

LOG_ARCHIVE_DEST is applicable only if we are running the database in ARCHIVELOG mode. LOG_ARCHIVE_DEST parameter are used to specified the archiving location. The Location specified by log_archive_dest must be local . We choose to archive only two local location  i.e,  primary and a secondary destination ( using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST ) .

The  LOG_ARCHIVE_DEST_n initialization parameter defines up to ten (where n = 1, 2, ... 10) destinations in oracle 10g and thirty one (n=1,2....31)  destination in oracle 11g , each of which must specify either the  LOCATION or  the SERVICE  attribute to specify where to archive the redo data. All other attributes are optional.We set the attributes for the LOG_ARCHIVE_DEST_n initialization parameter to control different aspects of how redo transport services transfer redo data from a production or primary database destination to another (standby) database destination.For every LOG_ARCHIVE_DEST_n initialization parameter that we define, we must specify a corresponding LOG_ARCHIVE_DEST_STATE_n parameter. The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 10) initialization parameter specifies whether the corresponding destination is currently enabled or disabled.

LOG_ARCHIVE_DEST is incompatible with the LOG_ARCHIVE_DEST_n parameters, and must be defined as the null string ("") or (' ') when any LOG_ARCHIVE_DEST_n parameter has a value other than a null string.

If we are using Oracle Enterprise Edition, LOG_ARCHIVE_DEST parameter is deprecated in favor of the LOG_ARCHIVE_DEST_n parameters. If Oracle Enterprise Edition is not installed or it is installed but we have not specified any LOG_ARCHIVE_DEST_n parameters,then LOG_ARCHIVE_DEST parameter is valid. If we have LOG_ARCHIVE_DEST parameter set and we have to set more than two archive location then follow the below steps :

SQL> alter system reset log_archive_dest='' scope=both;
SQL> shutdown immediate
SQL> startup
SQL> alter system set log_archive_dest_1='location=C:\archive\' ;
SQL> alter system set log_archive_dest_2='location=D:\oracle\archive\' ;

Enjoy      : -)


basha said...

Very useful info. I also have come across the same doubt today. Thanks for clear clarification.

basha said...

Very useful info. I also have come across the same doubt today. Thanks for clear clarification.


you welcome Mr basha ...

Enjoy :-)

omprakash said...

Hi ,
Could you please tell me
why log_archive_dest is used in
both primary and secondary database,

and standby_archive_dest for what it is used.


Thanks for info


thanks for info..

Martin Eggens said...

thanks I also had some doubts about this, now everything is clear.

Amit Pawar said...

I had same doubt about archive_dest parameter,but now clear.

Thank You.

Anonymous said...

I had two log_archive_dest_state_1 and log_archive_dest_state_2 parameters.If I disable these two parameters,what happen?