Difference between LOG_ARCHIVE_DEST_1 LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST

advertisements

_____________________________________________________________________________________________________________________

Oracle database can run either in archivelog mode or non archivelog mode. All these parameters (LOG_ARCHIVE_DEST_1 LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST) are valid for archivelog mode only.

How to check whether the database is running on Archivelog Mode?

Connect to sqlplus with / as sysdba

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

LOG_ARCHIVE_DEST parameter determines the location for the archive log files. The directory location must be a local directory which is accessible by the Oracle server. LOG_ARCHIVE_DEST is incompatible with LOG_ARCHIVE_DEST_n parameters. Any of LOG_ARCHIVE_DEST_n parameter has non empty string value the LOG_ARCHIVE_DEST value must be null.

LOG_ARCHIVE_DUPLEX_DEST parameter is similar to LOG_ARCHIVE_DEST. In case if you wanted to write the archive logs to one more location other than the LOG_ARCHIVE_DEST, then you can use this option. As the name indicates it is secondary (DUPLEX) archive destination. The default value for this parameter is null string. Similar to LOG_ARCHIVE_DEST you have to specify a valid directory path name as the parameter value to start writing the archive logs to this destination.

LOG_ARCHIVE_DEST_n parameter is used to specify more archive destinations. The value for n can be 1, 2, . . . ,10 destinations in Oracle 11gR1 and in Oracle 11gR2 it can be from 1-31. Each of which (1-10) must specify either the file directory LOCATION or the SERVICE attributes. In Oracle 11gR2 LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31 destination must contain a SERVICE attribute. If you do not want to enter any attribute you can give a null string as the parameter value. If you are going to use LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31, then you should set the COMPATIBLE initialization parameter to 11.2.0.0 or later. LOG_ARCHIVE_DEST_n parameter should use in conjunction with LOG_ARCHIVE_DEST_STATE_n parameter. If you wanted to enable LOG_ARCHIVE_DEST_1 parameter you should set the LOG_ARCHIVE_DEST_STATE_1 = ENABLE. Most of the attributes are modifiable by ALTER SESSION and ALTER SYSTEM command but not for all.

Syntax to Change the parameter values.

alter system set log_archive_dest='/u01/app/oracle/fast_recovery_area';

alter system set log_archive_dest_1='location=/u01/app/oracle/arch1/ ' ;

alter system set log_archive_dest_1='location=/u02/app/oracle/arch2 ' ;

Note:- One interesting thing is if your database is running in archive log mode and specify db_recovery_file_dest parameter to a valid destination or string, you don’t need to mention any of these archive destinations.

List of Attributes for LOG_ARCHIVE_DEST_n are:

1. AFFIRM and NOAFFIRM
2. ALTERNATE (not supported for LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31)
3. COMPRESSION
4. DB_UNIQUE_NAME
5. DELAY
6. LOCATION and SERVICE (LOCATION is not supported for 11gR2 LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31)
7. MANDATORY (not supported for 11gR2 LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31)
8. MAX_CONNECTIONS
9. MAX_FAILURE
10. NET_TIMEOUT
11. NOREGISTER
12. REOPEN
13. SYNC and ASYNC (SYNC is not supported for 11gR2 LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31)
14. TEMPLATE
15. VALID_FOR

_____________________________________________________________________________________________________________________

Website Stats