Archive log Destination

With archivelog mode turned on, you need to make a copy of each log file before Oracle will reuse it. This process of copying the log files is referred to as archiving. Before you can archive a log file, you must specify the location in which the archived copies are to be placed. This location is known as the archive log destination.

• Setting the Archive Log Destination
  You can control the archive log destination using the log_archive_dest initialization parameter. You set this in your initialization parameter file, and the setting takes effect the next time the database is started. The following setting, for example, sets the archive log destination to a directory on the E: drive:
  

  log_archive_dest = 'E:\ORADATA\COIN\ARCHIVE'
  

This parameter may also be modified while the database is open. For that, use the ALTER SYSTEM command, as shown here:

ALTER SYSTEM
SET log_archive_dest = 'E:\ORADATA\COIN\ARCHIVE';

Once the destination is set, whenever a log file is archived (whether manually or automatically), Oracle will copy it to that location.

• Setting Second Destination For added security, you can define a second archive log destination. You might choose to do this if your drives are not mirrored. You define a second archive log destination using the log_archive_duplex_dest initialization parameter. If you define two destinations, Oracle makes an archive copy of each log file in both directories. By default, the duplex destination is optional. If a copy to that destination fails, Oracle will still continue to function. You can control whether or not the duplex destination is mandatory by using the log_archive_min_succeed_dest parameter. Here's an example:
  

  log_archive_dest = 'E:\ORADATA\COIN\ARCHIVE'
  log_archive_duplex_dest = 'H:\ORADATA\COIN\ARCHIVE'
  log_archive_min_succeed_dest = 2
  

  
  Here, two destinations have been defined, and the log_archive_min_succeed_dest parameter has been set to 2. If Oracle cannot archive a log file to both locations, then Oracle will stop until you fix the problem. If a value of 1 had been used, which is the default, Oracle would ignore failures when copying to the duplex destination.

Oracle Database 23c introduces some changes to how archive log destinations are specified, continuing Oracle's evolution of log management.
Key Changes in 23c:

1. Deprecation of LOG_ARCHIVE_DEST_n parameters: Oracle 23c continues the deprecation path for the traditional LOG_ARCHIVE_DEST_n parameters in favor of the more flexible LOG_DESTINATION parameter introduced in earlier versions.
2. Enhanced LOG_DESTINATION parameter: This parameter now provides more comprehensive control over archive log destinations with simplified syntax.
3. Cloud-oriented configurations: 23c adds better support for cloud storage destinations (like Oracle Object Storage, AWS S3, Azure Blob) as primary archive destinations.
4. Unified logging architecture: Archive logs are more tightly integrated with the overall database logging framework.

Example Configuration in 23c:

ALTER SYSTEM SET LOG_DESTINATION='LOCATION=/u01/archivelogs, 
			  CLOUD=oracle://archivelogs/,
			  SYNC=YES';

Migration Consideration: If you are upgrading from earlier versions, Oracle provides compatibility modes, but they recommend migrating to the new `LOG_DESTINATION` parameter for future compatibility.

Oracle8i changed the way in which archive log destinations were specified. Instead of just two locations controlled by the log_archive_dest and log_archive_duplex_dest parameters, Oracle8i allows you to have up to five archive log destinations. These locations are specified using the parameters log_archive_dest_1 through log_archive_dest_5.
The following syntax must be used:

log_archive_dest_n = '{SERVICE=service_name

|LOCATION=directory}

[MANDATORY|OPTIONAL]

[REOPEN=seconds]'

The parameters are as follows:

1. n: A number from 1 through 5.
2. service_name: A Net8 service name.
3. directory: A directory path.
4. MANDATORY: The destination is a required destination.
5. OPTIONAL: The destination is optional. This is the default setting. However, the log_archive_min_succeed_dest setting still applies.
6. seconds: A retry interval. Oracle will retry a failed transfer after the specified number of seconds.

Using the method of specifying archive log destinations is not compatible with the 8.0 method. Oracle8i still supports the old method, but you must use either the old or the new. You can't mix them.
The landscape of archive log destinations has expanded significantly since Oracle8i. While the fundamental concept remains the same, ensuring the safety and recoverability of your database through archived redo logs, Oracle 23c offers much more flexibility and granularity. Here's how you might describe archive log destinations in Oracle 23c, reflecting the advancements:
Oracle Database 23c allows for a more dynamic and scalable approach to managing archive log destinations. You can configure up to 31 archive log destinations using the parameters `LOG_ARCHIVE_DEST_1` through `LOG_ARCHIVE_DEST_31`. The syntax for defining each destination has been enhanced to accommodate modern storage options and availability requirements:

LOG_ARCHIVE_DEST_n = 'LOCATION=directory
			 | SERVICE=(service_name [, ...])
			 [VALID_FOR=(ALL_LOGFILES | ONLINE_LOGFILES | STANDBY_LOGFILES)]
			 [DB_UNIQUE_NAME=database_unique_name]
			 [MANDATORY | OPTIONAL]
			 [REOPEN=seconds]
			 [MAX_FAILURE=integer]
			 [DELIVERY_LAG=seconds]
			 [SYNC | ASYNC]
			 [NOREGISTER | REGISTER]'

Here's a breakdown of the parameters, highlighting the key differences and additions compared to Oracle8i:

• n: A number from 1 through 31, significantly increasing the number of supported destinations.
• LOCATION=directory: Specifies a local file system directory for archive logs, similar to Oracle8i.
• SERVICE=(service_name [, ...]): Defines one or more Oracle Net Services names for transmitting archive logs to remote destinations, typically standby databases. The ability to specify multiple service names provides redundancy for remote archiving.
• VALID_FOR=(ALL_LOGFILES | ONLINE_LOGFILES | STANDBY_LOGFILES): This powerful parameter controls which types of log files are archived to this specific destination.
  • ALL_LOGFILES: (Default) Archives all online and standby redo logs.
  • ONLINE_LOGFILES: Archives only online redo logs generated by the primary database.
  • STANDBY_LOGFILES: Archives only standby redo logs generated by a logical standby database. This allows for more targeted archiving strategies.
• DB_UNIQUE_NAME=database_unique_name: Specifies the unique name of the database to which the archive logs being sent belong. This is crucial in Data Guard environments for proper identification and management of archive logs from different databases.
• MANDATORY | OPTIONAL: Similar to Oracle8i, MANDATORY designates a required destination. If archiving to a mandatory destination fails, database operation may be affected. OPTIONAL (default) indicates a non-essential destination. The LOG_ARCHIVE_MIN_SUCCEED_DEST initialization parameter still governs the minimum number of successfully archived destinations required for the primary database to proceed.
• REOPEN=seconds: Specifies the interval in seconds that Oracle will attempt to re-establish a failed connection or retry writing to a failed destination.
• MAX_FAILURE=integer: Introduces a limit on the number of consecutive failures allowed for a destination before it is automatically disabled. This helps prevent repeated failed attempts from impacting performance.
• DELIVERY_LAG=seconds: For remote destinations, this parameter specifies the maximum acceptable delay in seconds for archive log delivery. Oracle can monitor this and potentially issue alerts if the lag exceeds the defined threshold.
• SYNC | ASYNC: For remote destinations, this determines the synchronization mode for archive log transfer.
  • SYNC: The primary database waits for confirmation that the archive log has been successfully received at the standby destination before proceeding. This ensures zero data loss but can impact primary database performance.
  • ASYNC: The primary database does not wait for confirmation, potentially improving performance but with a slight risk of data loss in case of a primary database failure before the logs are received.
• NOREGISTER | REGISTER: This parameter controls whether the archived logs at the destination are automatically registered in the recovery catalog (if used). REGISTER is the default.

As you can see, Oracle 23c provides a much richer set of options for configuring archive log destinations, enabling more sophisticated high availability and disaster recovery strategies through features like granular control over which logs go where, failure management, and synchronization options for remote destinations.
You will learn how to name archive log files in the next lesson.