Backup Recovery   «Prev  Next»

Lesson 3Duplex and multiple archive logs
ObjectiveImplement duplex or multiple archive logs.

Duplex and Multiple Archive Logs in Oracle

Oracle enables the (ARCH) background archive process, or its foreground equivalent, to archive online redo log files to multiple destinations. Up to five destinations can be specified with the initialization parameter: LOG_ARCHIVE_DEST_n. This parameter defines a destination and attributes for the archive redo log file group. The parameter number suffix is defined as the handle[1] displayed by the fixed-table queries. The destination can be a local disk-based file, or it can be a user-specified standby database that is either local or remote to the primary database. It has been observed that two archival destinations are not enough, though five or more is excessive and provides no additional benefit. In the following code, you can see an example of usage of the parameter, where the first location of the archived redo log file is within the local hard disk. 
LOG_ARCHIVE_DEST_1 = "LOCATION=D:\ORACLE\ORADATA\STBY\ARCHIVE"

A maximum of five destinations may be specified. The first destination specified is treated as the primary destination. The remaining destinations are secondary. At least one archive destination must be a local disk. One archive destination is MANDATORY, although none may actually be specified. If no destinations are specified, the appropriate initialization parameters are used as defaults. 
LOG_ARCHIVE_DUPLEX_DEST

is similar to 
LOG_ARCHIVE_DEST

This parameter introduced within Oracle8 is deprecated[2] within Oracle8i. LOG_ARCHIVE_DEST_n must be used instead within Oracle8i and later. This parameter specifies a second (duplex) archive destination. If LOG_ARCHIVE_DUPLEX_DEST is set to a NULL string ("") or (`'), it means that there is no duplex archive destination. The default of this parameter is a NULL string. In the following simulation, we will set the parameter value of LOG_ARCHIVE_DUPLEX_DEST and LOG_ARCHIVE_DEST_n using the ALTER SYSTEM command within SQL*Plus. Because these two parameters are dynamic, they can be changed using SQL*Plus.

Setting Oracle Parameter Values

  1. Connect to the database by using PETSTORE as the User Name, GREATPETS as the Password, and MYDB as the Host String. Click the OK button to approve the entries.
  2. Build a SQL string to set the parameter value of LOG_ARCHIVE_DUPLEX_DEST to 
    d:\oradata\mydb\archive\duplex

    by entering 
    ALTER SYSTEM SET LOG_ARCHIVE_DUPLEX_DEST = 'd:\oradata\mydb\archive\duplex'

    at the SQL> prompt. Approve the entries and execute the SQL. Press ENTER.
  3. Build a SQL string to set the parameter value of LOG_ARCHIVE_DEST_n to d:\oradata\mydb\archive by entering 
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 = 'LOCATION= d:\oradata\mydb\archive OPTIONAL'

    at the SQL> prompt. Approve the entries and execute the SQL. OPTIONAL is mentioned within the ALTER SYSTEM statement, because Oracle8i allows the user to specify five locations for maintaining the archived redo log files, the first being mandatory and the rest specified as optional. Approve the entries and execute the SQL string.
  4. SQL*Plus now displays the result of compilation of your query. Click Exit to end the simulation.

  • The Fast Recovery Area
    The Fast Recovery Area (FRA) is not a requirement for using RMAN, but it should be. The FRA was introduced in Oracle Database version 10g. It was first called the Flash Recovery Area but was then renamed to the Fast Recovery Area. The term recovery files refers to all files that might be required for a media recovery operation: full datafile backups, incremental backups, datafile copies, backup control files, and archive logs. The FRA also functions as a repository for mirrored copies of online redo log files, the blockchange tracking file, and for a current control file. If set up, flashback logs for using the flashback database option also live in the FRA. The concept behind the FRA is to simplify the management of your backup and recovery duties by consolidating the requisite files into a single location that Oracle and RMAN can then micromanage, while the DBA moves on to other important duties. The FRA really is part of an overall backup and recovery architecture strategy that is designed to ensure that the database is recoverable, with a minimum of work on the part of the DBA.

The FRA that you set up can be either a directory on a normal disk volume or an Automatic Storage Management (ASM) disk group. The FRA is determined by two initialization parameters: DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE. The first determines the location; the second, the size. These can be set in your init.ora file, if you still use one, or in the SPFILE via an alter system set command. With an FRA configured, you are not required to set any other LOG_ARCHIVE_DEST_n parameter for archive logs; by default, with an FRA, Oracle will default the setting for LOG_ARCHIVE_DEST_10 to use the FRA. There may be some cases where you will want to set other log archived destination directories for example, with standby databases. It should also be noted that with an FRA in use, the parameters
  1. LOG_ARCHIVE_DEST or
  2. LOG_ARCHIVE_DUPLEX_DEST
are mutually exclusive, but you certainly rid yourself of these outdated parameters long ago, right? The FRA manages recovery files internally, first based on database name, then on types of files, and then by the dates when the files are generated. The files themselves are named according to the Oracle Managed Files (OMF) format. Significant internal directory structures exist for file management. However, the point of an FRA is that you do not need to spend much time worrying about the files. The same FRA can be used by multiple databases. This can provide significant advantages, particularly for a Data Guard configuration, but also if you have a large ASM disk group and multiple databases on the same system. It can come in handy, as well, when it comes time to clone production for test purposes.
The following MouseOver illustrates the syntax and provides an example for the LOG_ARCHIVE_DUPLEX_DEST and LOG_ARCHIVE_DEST_n initialization parameters.

The name of the initialization parameter, this is used to set multiple destinations for archived redo log files 
LOG_ARCHIVE_DUPLEX_DEST=<string>;
LOG_ARCHIVE_DEST_n = ("null_string"
|SERVICE= tnsnames-service | LOCATION=directory-spec) 
[MANDATORY | OPTOINAL]
[REOPEN= integer]
LOG_ARCHIVE_DUPLEX_DEST The name of the initialization parameter, this is used to set multiple destinations for archived redo log files.
<string> This is the value set to the parameter. The value in the form of a character string must be a directory name for saving the duplexes of archived redo log files.
LOG_ARCHIVE_DEST_n The name of the parameter, which is used to define multiple locations for archived redo log files, where n, is an integer between 1 and 5.
null_string The value of the parameter can be set to a NULL string.
SERVICE=tnsnames-service SERVICE specifies the destination of a standby database, which will be used to transmit the archivelog. There must be a standby instance associated with the destination.
LOCATION=directory-spec) LOCATION specifies a local file-system.
MANDATORY MANDATORY specifies that archiving to the destination must succeed before the REDO log file can be made available for re-use.
OPTIONAL OPTIONAL specifies that successful archiving to the destination is not required before the REDO log file can be made available for re-use.
REOPEN=integer REOPEN specifies an interval of time (in seconds) that must pass after an error is encountered during archiving to the destination before future archives to the destination can be attempted.

Archival Destinations and the parameter `LOG_ARCHIVE_DEST_n` hold true for Oracle 19c and Oracle 23c

The general principles regarding the use of archival destinations and the parameter `LOG_ARCHIVE_DEST_n` (where `n` is a number from 1 to 31) still hold true for Oracle 19c and Oracle 23c, with some enhancements in flexibility and features for modern environments.
Key Points for Oracle 19c and 23c:
  1. Multiple Archive Destinations:
    • Oracle 19c and 23c support up to 31 archive destinations using the LOG_ARCHIVE_DEST_n initialization parameter, where n can range from 1 to 31.
    • Each destination can still be a local file system or a remote standby database, much like previous versions.
  2. Using Multiple Destinations:
    • While two destinations (one for local archival and one for a remote standby database) may often be sufficient in many configurations, modern data protection strategies, such as Data Guard or Remote Data Guard setups, might benefit from multiple destinations to ensure redundancy and business continuity.
    • Best practice suggests that configuring more than two destinations could be useful depending on the environment’s redundancy and high availability requirements, but beyond a certain number (such as five), it often does not add significant benefits and may complicate management.
  3. LOG_ARCHIVE_DEST_n Enhancements:
    • Oracle has added more flexibility in managing destinations with advanced options such as synchronous/asynchronous redo transport, real-time apply on standby databases, and compression of redo log files for efficient data transmission over WANs.
    • The attributes of each LOG_ARCHIVE_DEST_n parameter can be tuned for things like transport methods, redo log validation, and net_timeout to ensure smooth archival to remote destinations.
  4. Local vs Remote Archival:
    • The destinations can still be local disk-based (for local archiving) or a remote standby database, similar to older versions like Oracle 8i and 9i.
    • In modern Oracle environments, leveraging technologies like Oracle Data Guard with real-time apply has become a common practice to ensure maximum data protection.
  5. Does Two Destinations Still Work?:
    • Generally, two archive destinations (one local and one remote) are enough for typical high availability and disaster recovery setups.
    • Configuring additional destinations could be beneficial if you have multiple standby databases or wish to archive to multiple local or remote locations for additional redundancy. However, as you noted, beyond a certain point (usually around five), additional destinations provide diminishing returns in terms of operational benefit.

In Summary:
  • Oracle 19c and Oracle 23c continue to support multiple archive destinations with the LOG_ARCHIVE_DEST_n parameter.
  • Two destinations (local and remote) are typically sufficient, but more than that may be used in certain configurations, especially in complex environments with advanced data protection needs.
  • The maximum limit has increased to 31 destinations, though more than five destinations are usually unnecessary unless the architecture demands it.

LOG_ARCHIVE_DUPLEX_DEST

LOG_ARCHIVE_DUPLEX_DEST is similar to the initialization parameter LOG_ARCHIVE_DEST. This parameter specifies a second archive destination: the duplex archive destination. This duplex archive destination can be either a must-succeed or a best-effort archive destination, depending on how many archive destinations must succeed (as specified in the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter). The default setting of a null string ("") or (' ') indicates that a duplex archive destination does not exist.

Property Description
Parameter type String
Syntax LOG_ARCHIVE_DUPLEX_DEST = filespec
Default value There is no default value.
Modifiable ALTER SYSTEM
Range of values Either a null string or any valid path or device name, except raw partitions
Basic No

Note: If you are using Oracle Enterprise Edition, this parameter is deprecated in favor of the LOG_ARCHIVE_DEST_n parameters. If Oracle Enterprise Edition is not installed or it is installed but you have not specified any LOG_ARCHIVE_DEST_n parameters, this parameter is valid. The next lesson demonstrates how to detect and repair corrupted blocks.

[1]Handle: A handle is a connection to the database server, which completes a transaction for the user.
[2]Deprecated: When a certain feature or functionality is no longer advisable for use or will no longer be supported on the Oracle database server, that feature is called deprecated.

SEMrush Software 3 SEMrush Banner 3