Oracle9i Data Guard Concepts and Administration Release 2 (9.2) Part Number A96653-01 |
|
This chapter explains how to set up and use log transport services to control automated archiving of redo logs from the primary database to one or more standby sites. It includes the following topics:
The log transport services component of the Data Guard environment is responsible for automatic archiving of primary database online redo logs. Once archived, these logs are known as archived redo logs. Log transport services provide for the management of archived redo log permissions, destinations, transmission, reception, and transmission failure resolution. In a Data Guard environment, the log transport services component coordinates its activities with log apply services and role management services for switchover and failover operations.
Log transport services and log apply services automate the process of copying online redo logs to a remote node and applying them so that data from a primary database can be made available on a standby database. Log transport services provide the capability to archive the primary databases's redo log files to a maximum of 10 archiving locations called destinations.
Log transport services provide flexibility in defining destinations and archiving completion requirements. These services also provide I/O failure handling and transmission restart capability.
Figure 5-1 shows a simple Data Guard configuration with redo logs being archived from a primary database to a local destination and a remote standby database destination using log transport services.
Text description of the illustration logtrans.gif
The archiver process (ARCn ) or log writer process (LGWR) can be configured to archive online redo logs to a maximum of 10 destinations. These destinations are specified using the LOG_ARCHIVE_DEST_
n
(where n
is a number from 1 to 10) database initialization parameter. One destination must be a local directory, but others can be at remote destinations. A remote destination is identified by an Oracle Net network service name, which is defined in the appropriate network services configuration files.
Archiving redo logs to a remote destination requires uninterrupted connectivity through Oracle Net. If the destination is a remote physical standby database, it must be mounted or open in read-only mode to receive the archived redo logs.
Logical standby databases must be open in read/write mode. If a network connection to a standby database is broken, it must be reestablished to continue log archiving.
Log transport services use the following processes on the primary site:
The log writer process (LGWR) collects transaction redo and updates the online redo logs. The log writer process can also create local archived redo logs and transmit online redo to standby databases.
The archiver process (ARCn), or a SQL session performing an archiving operation, creates a copy of the online redo logs, either locally or remotely, for standby databases.
The fetch archive log (FAL) client is the Oracle Net name of the standby site that is requesting a gap sequence. The initialization parameter for the FAL client is set on the standby site. The FAL client pulls archived redo log files from the primary site and initiates and requests the transfer of archived redo log files automatically when it detects an archive gap on the standby database.
The fetch archive log (FAL) process provides a client/server mechanism for resolving gaps detected in the range of archived redo logs generated at the primary database and received at the standby database. The FAL client requests the transfer of archived redo log files automatically when it detects a gap in the redo logs received by the standby database. The FAL server typically runs on the primary database and services the FAL requests coming from the FAL client. The FAL client and server are configured using the FAL_CLIENT
and FAL_SERVER
initialization parameters that are set on the standby location. (The parameters can also be set on the primary database in preparation for a future switchover operation.)
Log transport services capabilities allow you to determine how archiving online redo logs will be handled in a Data Guard configuration. These capabilities can be classified into the following categories:
You control whether the primary database can archive redo logs to a standby site, and whether the standby database can receive them from a primary site. Permission is set using the REMOTE_ARCHIVE_ENABLE
database initialization parameter.
Using log transport services, redo logs can be archived to any of the following destinations:
A local file system location.
A remote location accessed using an Oracle Net service name. There are several types of remote destinations, as shown in the following table:
See Also:
|
You can choose the method and form (online redo logs or archived redo logs) by which redo logs are transmitted from the primary database to each archive destination by specifying:
You can specify either the log writer process or the archiver process to transmit the redo logs.
When using the log writer process, you can specify synchronous or asynchronous network transmission of redo logs to remote destinations. The log writer process sends redo logs in the form of online redo logs.
When you are using the archiver process, only synchronous transmission of redo logs to remote destinations is available. The archiver process sends redo logs in the form of archived redo logs.
Using transmission options, you can configure log transport services protection modes that give you the flexibility to balance data protection levels and application performance.
You can specify whether log archiving network I/O operations are to be performed synchronously or asynchronously. This applies to both the log writer and archiver processes.
See Also:
Section 5.7 for more information on log transport services data protection modes |
Reception gives you control over determining where redo logs will be placed at remote destinations. The redo data can be stored on the standby site using either standby redo logs or archived redo logs.
Oracle9i introduces the concept of standby redo logs. Standby redo logs form a separate pool of log file groups. Although standby redo log file groups are only used when a database is operating in a physical standby role, you can specify them on the primary database in anticipation of a switchover operation.
See Also:
Section 5.8.4 for more information about reception options |
Failure resolution gives you control over what actions will occur on a primary database if log archiving from the primary database to the standby database fails. These actions include:
See Also:
Section 5.5.2.5 and Section 5.8 for more information about failure resolution options |
Log transport services options are entered and changed using the following interfaces:
You configure the primary database to perform remote archiving by setting destinations and associated states. You do this using LOG_ARCHIVE_DEST_
n
(where n is an integer from 1 to 10) initialization parameter and corresponding LOG_ARCHIVE_DEST_STATE_
n
(where n is an integer from 1 to 10) initialization parameter. You can also use these initialization parameters to set up cascading standby databases, as described in Appendix E.
LOG_ARCHIVE_DEST_
n
(where n is an integer from 1 to 10) initialization parameter allows you to specify up to 10 archive destinations, including 1 required local destination and up to 9 additional local or remote destinations. This parameter also allows you to set a number of archiving options for each destination. These options are set using the attributes described in Table 5-1.
Attribute | Description | Capability | More Information |
---|---|---|---|
|
Ensures that archived redo log contents are written to disk successfully and are available immediately for database recovery. The |
Transmission |
See Section 5.8.3 and see AFFIRM and NOAFFIRM in Chapter 12 |
|
Specifies an alternate location that can be used as a destination for archival operations if archiving to the original destination fails |
Failure resolution |
See Section 5.5.2.5 and see ALTERNATE and NOALTERNATE in Chapter 12 |
|
The archiver process (ARCn) will archive online redo logs to local and remote destinations |
Transmission |
See Section 5.8.1 and see ARCH and LGWR in Chapter 12 |
|
Network I/O operations for log transport services are to be done asynchronously. Specifies the size of the SGA network buffer to be used. |
Transmission |
See Section 5.8.2 and see SYNC and ASYNC in Chapter 12 |
|
Specifies a time interval between archiving a redo log at a remote site and applying that archived redo log to the standby database |
Reception |
See Section 5.5.2.8 and see DELAY and NODELAY in Chapter 12 |
|
Archival operations to a destination are dependent upon the success or failure of archival operations to another local destination |
Transmission |
See Section 5.5.2.6 and see DEPENDENCY and NODEPENDENCY in Chapter 12 |
|
The log writer process will archive current online redo logs to local and remote destinations |
Transmission |
See Section 5.8.1 and see ARCH and LGWR in Chapter 12 |
|
Identifies a local disk directory location where archived redo logs will be stored |
Destination |
See Section 5.3.1.3 and see LOCATION and SERVICE in Chapter 12 |
|
Archiving to this location must succeed before the online redo log can be overwritten |
Failure resolution |
See Section 5.5.2.3 and see MANDATORY and OPTIONAL in Chapter 12 |
|
Sets a limit on the number of consecutive times log transport services will retry archival operations to any location after a communication failure |
Failure resolution |
See Section 5.5.2.5 and see MAX_FAILURE and NOMAX_FAILURE in Chapter 12 |
|
Specifies the number of seconds the log writer process will wait for status from the network server before assuming there is a network timeout error |
Failure resolution |
|
|
Successful archiving to this location is not necessary |
Failure resolution |
See Section 5.5.2.3 and see MANDATORY and OPTIONAL in Chapter 12 |
|
The maximum number of 512-byte blocks that can be consumed on the specified destination |
Transmission |
See Section 5.5.2.9 and see QUOTA_SIZE and NOQUOTA_SIZE in Chapter 12 |
|
Identifies the size of all of the archived redo logs currently residing on the specified destination |
Transmission |
|
|
Specifies whether or not the archiving location is to be recorded in the standby database control file |
Reception |
|
|
Specifies a fully qualified filename format template for archived redo logs that is different from the default filename format template defined in the primary and standby database initialization parameter files. The default filename format template is a combination of the database initialization parameters |
Reception |
See Section 5.5.2.7 and see REGISTER=location_format in Chapter 12 |
|
Specifies the minimum number of seconds before the archiver process (ARCn, foreground, or log writer process) should try again to access a previously failed destination |
Failure resolution |
See Section 5.5.2.5 and see REOPEN and NOREOPEN in Chapter 12 |
|
Specifies the net service name of a standby database where redo log files are to be archived |
Destination |
See Section 5.5.2.2 and see LOCATION and SERVICE in Chapter 12 |
|
Indicates that network I/O operations for log transport services are to be done synchronously and in parallel or not for all |
Transmission |
See Section 5.8.2 and see SYNC and ASYNC in Chapter 12 |
|
Defines a directory specification and format template for archived redo logs at the standby destination. You can specify this attribute in either the primary or standby initialization parameter file, but the attribute applies only to the database role that is archiving. |
Reception |
The LOG_ARCHIVE_DEST_STATE_
n
(where n
is an integer from 1 to 10) initialization parameter specifies the state of the corresponding destination indicated by the LOG_ARCHIVE_DEST_
n
initialization parameter (where n
is the same integer). For example, the LOG_ARCHIVE_DEST_STATE_3
parameter specifies the state of the LOG_ARCHIVE_DEST_3
destination.
LOG_ARCHIVE_DEST_STATE_
n
parameter attributes are described in Table 5-2.
Setting up log transport services requires modification of the database initialization file. When you set up log transport services parameters, you can specify attributes as:
Example 5-1 shows how to specify a single attribute on one line.
LOG_ARCHIVE_DEST_1='LOCATION=/disk1/d1arch'
Example 5-2 shows how to set multiple attributes on a single line.
LOG_ARCHIVE_DEST_1='LOCATION=/disk1/d1arch OPTIONAL'
Example 5-3 shows how to set multiple attributes incrementally on separate lines. SERVICE
or LOCATION
attributes must be specified on the first line. You can specify attributes incrementally even when the initialization parameter file is used to create a server parameter file (SPFILE).
LOG_ARCHIVE_DEST_1='LOCATION=/disk1/d1arch' LOG_ARCHIVE_DEST_1='OPTIONAL' LOG_ARCHIVE_DEST_1='REOPEN=5'
Example 5-4 shows how to specify attributes for multiple destinations. Incremental parameters such as the LOG_ARCHIVE_DEST_
n
initialization parameter must immediately follow each other in the initialization parameter file.
LOG_ARCHIVE_DEST_1='LOCATION=/disk1/d1arch OPTIONAL' LOG_ARCHIVE_DEST_2='SERVICE=stby REOPEN=60'
Attributes specified on multiple lines for a single destination must be entered sequentially. Example 5-5 shows an entry for the LOG_ARCHIVE_DEST_1
that is not allowed.
LOG_ARCHIVE_DEST_1='LOCATION=/disk1/d1arch' LOG_ARCHIVE_DEST_2='SERVICE=stby REOPEN=60' LOG_ARCHIVE_DEST_1='OPTIONAL'
A destination specification can be redefined after another destination has been specified if the new specification includes LOCATION
or SERVICE
attributes. Example 5-6 shows how to replace the initial specification of LOG_ARCHIVE_DEST_1.
LOG_ARCHIVE_DEST_1='LOCATION=/disk1/d1arch' LOG_ARCHIVE_DEST_2='SERVICE=stby REOPEN=60' LOG_ARCHIVE_DEST_1='LOCATION=/disk3/d3arch MANDATORY'
A string containing a null value for parameter attributes will clear a previously entered destination specification. Example 5-7 shows how to clear the definition of LOG_ARCHIVE_DEST_1
.
LOG_ARCHIVE_DEST_1='LOCATION=/disk1/d1arch' LOG_ARCHIVE_DEST_2='SERVICE=stby REOPEN=60' LOG_ARCHIVE_DEST_1=''
To specify service names that use embedded characters, such as equal signs (=) and spaces, use double quotation marks (") around the service name. Example 5-8 shows the specification of a service name that includes a space.
LOG_ARCHIVE_DEST_6='SERVICE="stdby arch" MANDATORY'
SQL statements can be used to set up most initialization parameters for log transport services and to monitor the environment. You can use SQL statements for the following tasks:
You can use SQL to query fixed views such as V$ARCHIVE_DEST
to see current LOG_ARCHIVE_DEST_
n
initialization parameter settings. For example, to view current destination settings on the primary database, enter the following statement:
SQL> SELECT DESTINATION FROM V$ARCHIVE_DEST;
At runtime, the LOG_ARCHIVE_DEST_
n
initialization parameter can be changed using ALTER SYSTEM
and ALTER SESSION
statements. You can specify the attributes in one or more strings in one statement or incrementally in separate statements.
Example 5-9 shows how to modify archive destination parameters at the system and session level.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_6='SERVICE=stby REOPEN=8'; SQL> ALTER SESSION SET LOG_ARCHIVE_DEST_6='SERVICE=stby2 REOPEN=10';
Unlike setting parameters in the database initialization file at startup, at runtime you can incrementally change the characteristics of a destination after modifying the settings of another destination. Example 5-10 shows how to make incremental changes to LOG_ARCHIVE_DEST_6
after LOG_ARCHIVE_DEST_7
has been defined.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_6='SERVICE=stby1 REOPEN=8'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_7='SERVICE=stby2 NOREOPEN'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_6='MANDATORY LGWR DELAY';
You can enter a null value for a destination to clear a previous definition. Example 5-11 shows how to clear the definition of LOG_ARCHIVE_DEST_6
.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_6='SERVICE=stby1 REOPEN=8'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_7='SERVICE=stby2 NOREOPEN'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_6='';
To specify service names that use embedded equal signs (=) and spaces, use double quotation marks (") around the service name. Example 5-12 shows the specification of a service name that includes a space.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_6='SERVICE="stdby arch" MANDATORY';
Table 5-3 lists the attributes that can be set for the LOG_ARCHIVE_DEST_
n
initialization parameter and indicates whether the attribute can be changed using an ALTER SYSTEM
or ALTER SESSION
statement.
You can set specific options for log transport services using SQL statements. The following example sets the data protection mode to maximize availability.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
See Also:
Oracle9i SQL Reference for more information about SQL statements |
Log transport services manage the automatic archiving of primary database online redo logs to the standby site. Table 5-4 summarizes the basic tasks for configuring log transport services on the primary database and the standby database.
Step | Task | See ... |
---|---|---|
1 |
Set the primary database to run in ARCHIVELOG mode. |
Section 5.5.1.1 and the Oracle9i Database Administrator's Guide |
2 |
Determine the appropriate number and size of primary database online redo logs. |
|
3 |
Set the |
|
4 |
Monitor checkpointing and system performance by examining the |
Section 5.5.1.4 and the Oracle9i Database Administrator's Guide |
5 |
Set permission for the archiving of online redo logs to remote destinations using the |
|
6 |
Set the initialization parameters for the primary database. |
Section 5.5.2.2 through Section 5.5.2.9, Section 5.6.3.1, and Chapter 12 for details about these attributes |
7 |
Duplicate and transfer the primary database initialization parameter file to the standby site. Depending on your configuration, you may need to set filename conversion parameters. |
|
8 |
Choose a data protection mode. |
|
9 |
Configure the data protection mode for log transport services. |
|
9a |
Specify a redo log writing process. |
|
9b |
Specify a network transmission mode. |
|
9c |
Specify a method of writing archived logs to disk. |
|
9d |
Create standby redo logs, if necessary. |
|
9e |
Set a failure resolution policy. |
This section discusses what settings to make on the primary database to use the various options of log transport services in a Data Guard environment. The following topics are included in this section:
Considerations when configuring log transport services on the primary database include:
To use log transport services, you must set the primary database to run in ARCHIVELOG mode. When you run a database in ARCHIVELOG mode, you enable the archiving of the online redo logs. The database control file indicates that a group of filled online redo logs cannot be reused by the log writer process until the group is archived. A filled group is immediately available for archiving after a redo log switch occurs.
See Also:
Oracle9i Database Administrator's Guide for details about setting ARCHIVELOG mode |
You can set the ARCHIVELOG mode at database creation or by using the SQL ALTER DATABASE
statement.
Note: Oracle Corporation does not recommend running in NOARCHIVELOG mode, because it severely limits the possibilities for recovery of lost data. |
Both the size of the online redo logs and the frequency with which they switch affect the generation of archived redo logs at the primary site. In general, the most important factor when considering the size of an online redo log should be the amount of application data that needs to be applied to a standby database during a database failover operation. The larger the online redo log, the more data that needs to be applied to a standby database to make it consistent with the primary database.
Another important consideration should be the size of the archival media. Online redo logs should be sized so that a filled online redo log group can be archived to a single unit of offline storage media (such as a tape or disk), with the least amount of space on the medium left unused. For example, suppose only one filled online redo log group can fit on a tape, and 49% of the tape's storage capacity remains unused. In this case, it is better to decrease the size of the online redo log files slightly, so that two log groups can be archived per tape.
The best way to determine the appropriate number of online redo log files for a database instance is to test different configurations. The goal is to create the fewest groups possible without hampering the log writer process's ability to write redo log information.
In some cases, a database instance may require only two groups. In other situations, a database instance may require additional groups to guarantee that a recycled group is always available to the log writer process. During testing, the easiest way to determine if the current configuration is satisfactory is to examine the contents of the log writer process's trace file and the database's alert log. If messages indicate that the log writer process frequently must wait for a group because a checkpoint has not completed or a group has not been archived, add more groups.
Table 5-5 provides some guidelines to help in determining the number and sizes of primary database online redo logs.
The C
ONTROL_FILE_RECORD_KEEP_TIME
initialization parameter specifies the minimum number of days that must pass before a reusable record in the control file can be reused. Setting this parameter prevents the ARCHIVELOG mechanism from overwriting a reusable record in the control file. (It applies only to records in the control file that are serially reusable.) Using this parameter helps to ensure that data is made available on the standby database. The range of values for this parameter is 0 to 365 days. The default value is 7 days.
See Also:
Oracle9i Database Reference for more details about the |
The Oracle database server will attempt a checkpoint at each log switch. Therefore, if the online redo log size is too small, frequent log switches will lead to frequent checkpointing and negatively affect system performance. Oracle Corporation recommends a checkpoint and log switch interval between 5 and 15 minutes during normal operations and at 5-minute intervals during peak load periods, such as during batch processing. To get started, examine the V$SYSSTAT
view during a peak load period and then calculate 5 to 10 minutes of redo.
If you have remote hardware mirroring, you may want a minimum number of log groups, to ensure that no-data-loss failover can occur in case some logs are not archived. Furthermore, if some redo logs are not archived, they are not available to the standby database. This is another reason to avoid overly large online redo logs.
The sizing of the log files, along with the setting of the data protection modes, should factor in how much data loss your site can tolerate.
You should avoid situations where you cannot overwrite a redo log because you must wait for a checkpoint or archiving of that log. In these cases, there is usually a combination of problems with redo log configuration and database tuning. Oracle Corporation recommends that you solve the tuning problem first but, if that fails to correct the problem, add more log groups or increase log sizes.
Oracle Corporation generally recommends:
See Also:
Oracle9i Database Administrator's Guide for more details about configuring online redo logs and online redo log groups |
The log transport services component of the Data Guard environment is configured primarily through the setting of database initialization parameters and options. You set parameters by editing the database initialization parameter file or using SQL statements.
See Also:
Oracle9i Database Reference for details about creating and editing database initialization parameter files |
Permission for the archiving of online redo logs to remote destinations is specified using the REMOTE_ARCHIVE_ENABLE
initialization parameter. This parameter provides true
, false
, send
, and receive
options. In most cases, you should set this parameter to TRUE
on both the primary and standby databases in a Data Guard environment. To independently enable and disable the sending and receiving of remote archived redo logs, use the send
and receive
values.
The following list describes how you might use each option to control archiving of online redo logs:
REMOTE_ARCHIVE_ENABLE=true
on the primary database and standby databases to allow the primary database to send redo logs to the standby database and to allow the standby database to receive redo logs for archiving from the primary database.REMOTE_ARCHIVE_ENABLE=false
on the primary database and standby databases to disable both the sending and receiving of redo logs.REMOTE_ARCHIVE_ENABLE=send
on the primary database to enable the primary database to send redo logs to the standby database.REMOTE_ARCHIVE_ENABLE=receive
on the standby database to enable the standby database to receive redo logs from the primary database.Specifying the send
and receive
values together is the same as specifying true
. Every instance of the database must contain the same REMOTE_ARCHIVE_ENABLE
value.
In addition to setting up the primary database to run in ARCHIVELOG mode, you must configure the primary database to archive redo logs by setting destinations and associated states. You do this using the LOG_ARCHIVE_DEST_
n
initialization parameter and corresponding LOG_ARCHIVE_DEST_STATE_
n
parameter as discussed in Section 5.3.1.
This section describes the creation of a standby database named standby1
on a remote node named stbyhost
based on the following assumptions:
The prmyinit.ora
file is the initialization parameter file for the primary database.stbyhost
).stbyhost
.stbyhost
to use the TCP/IP protocol, and to statically register the standby1
database service using its SID
, so that the standby1
standby database can be managed by the Data Guard broker.standby1
that can be resolved to a connect descriptor that contains the same protocol, host address, port, and SID
that were used to statically register the standby1
standby database with the listener on stbyhost
.stbyhost
.
See Also:
Oracle9i Net Services Administrator's Guide for details about using Oracle Net Manager networking components |
To set up log transport services to archive redo logs to the standby database, make the following modifications to the primary database initialization parameter file. These modifications will take effect after the instance is restarted:
prmyinit.ora
file:
LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1 MANDATORY REOPEN=60'
prmyinit.ora
file:
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
To avoid having to restart the instance, you can issue the following SQL statements to ensure that the initialization parameters you have set in this step take effect immediately:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby1 MANDATORY REOPEN=60'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
You can specify a policy for the reuse of online redo logs using the OPTIONAL
or MANDATORY
attributes of the LOG_ARCHIVE_DEST_
n
initialization parameter. The archiving of log files to an OPTIONAL
destination can fail, and the online redo log will be overwritten. If the archiving of redo log files to a MANDATORY
destination fails, online redo log files will not be overwritten.
By default, one destination is mandatory even if all destinations are designated to be optional.
Example 5-13 shows how to set a mandatory local archiving destination and enable that destination.
LOG_ARCHIVE_DEST_3 = 'LOCATION=/arc_dest MANDATORY' LOG_ARCHIVE_DEST_STATE_3 = ENABLE
The LOG_ARCHIVE_MIN_SUCCEED_DEST=
n
parameter (where n
is an integer from 1 to 10) specifies the number of destinations that must receive redo logs successfully before the log writer process can reuse the online redo logs. All MANDATORY
destinations and non-standby OPTIONAL
destinations contribute to satisfying the LOG_ARCHIVE_MIN_SUCCEED_DEST=
n
count. Example 5-14 shows how to specify that redo logs must be successfully archived to two destinations before the online redo logs can be reused.
LOG_ARCHIVE_MIN_SUCCEED_DEST=2
You use attributes of the LOG_ARCHIVE_DEST_
n
initialization parameter to specify what actions are to be taken when archiving to a destination fails. You can use LOG_ARCHIVE_DEST_
n
attributes to:
Use the REOPEN
attribute of the LOG_ARCHIVE_DEST_
n
parameter to determine whether and when the archiver process or the log writer process attempts to archive redo logs again to a failed destination following an error.
The REOPEN=seconds
attribute specifies the minimum number of seconds that must elapse following an error before the archiving process will try again to access a failed destination. The default value is 300 seconds. The value set for the REOPEN
attribute applies to all errors, not just connection failures. You can turn off the option by specifying NOREOPEN
, which will prevent the destination from being retried after a failure occurs.
You can use the REOPEN
attribute in conjunction with the MAX_FAILURE
attribute to limit the number of consecutive attempts that will be made to reestablish communication with a failed destination. Once the specified number of consecutive attempts has been exceeded, the destination is treated as if the NOREOPEN
attribute had been specified.
If you specify REOPEN
for an OPTIONAL
destination, it is still possible for the Oracle database server to overwrite online redo logs even if there is an error. If you specify REOPEN
for a MANDATORY
destination, log transport services stalls the primary database when it cannot successfully archive redo logs. When this situation occurs, consider the following options:
When you use the REOPEN
attribute, note that:
REOPEN
attribute, the archiving process checks whether the time of the recorded error plus the REOPEN
interval is less than the current time. If it is, the archival operation to that destination is retried.
You can control the number of times a destination will be retried after a log archiving failure by specifying a value for the MAX_FAILURE=
count attribute of the LOG_ARCHIVE_DEST_
n
initialization parameter.
The MAX_FAILURE
attribute specifies the maximum number of contiguous archiving failures that will be allowed for the particular destination. This attribute is useful for archive destinations that you want to retry after a failure, but do not want to retry indefinitely. The REOPEN
attribute is required when you use the MAX_FAILURE
attribute. Example 5-15 shows how to set a retry time of 5 seconds and limit retries to 3 times.
LOG_ARCHIVE_DEST_1='LOCATION=/arc_dest REOPEN=5 MAX_FAILURE=3'
Using the ALTERNATE
attribute of the LOG_ARCHIVE_DEST_
n
parameter, you can specify alternate archive destinations. An alternate archive destination can be used when the archiving of an online redo log to a standby site fails. If archiving fails and the NO
REOPEN attribute has been specified, or the MAX_FAILURE
attribute threshold has been exceeded, log transport services will attempt to archive redo logs to the alternate destination on the next archiving operation.
Use the NOALTERNATE
attribute to prevent the original archive destination from automatically changing to an alternate archive destination when the original archive destination fails.
Example 5-16 shows how to set the initialization parameter file so that a single, mandatory, local destination will automatically fail over to a different destination if any error occurs.
LOG_ARCHIVE_DEST_1='LOCATION=/disk1 MANDATORY ALTERNATE=LOG_ARCHIVE_DEST_2' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_2='LOCATION=/disk2 MANDATORY' LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_DEST_STATE_2=ALTERNATE
If the LOG_ARCHIVE_DEST_1
destination fails, the archiving process will automatically switch to the LOG_ARCHIVE_DEST_2
destination at the next log switch on the primary database.
Archiving redo logs to a remote database can be defined as being dependent upon the success or failure of an archival operation for another destination. The dependent destination is known as the child destination. The destination on which the child depends is known as the parent destination.
Use the DEPENDENCY
attribute of the LOG_ARCHIVE_DEST_
n
initialization parameter to define a child destination. This attribute indicates that this destination depends upon the successful completion of archival operations for the parent destination.
Specifying a destination dependency can be useful in the following situations:
In these situations, although a physical archival operation is not required, the standby database needs to know the location of the archived redo logs on the primary site. This allows the standby database to access the archived redo logs on the primary site when they become available for managed recovery. You must specify an archiving destination as being dependent on the success or failure of another (parent) destination. This is known as a destination dependency.
The REGISTER
attribute indicates that the fully qualified filename of the archived redo log at the remote destination is to be recorded with the destination database at the remote destination. The fully qualified filename is derived from the values entered in the STANDBY_ARCHIVE_DEST
and LOG_ARCHIVE_FORMAT
initialization parameters. This is the implicit default setting.
For a physical standby destination, the archived redo log filename is recorded in the destination database control file, which is then used by the managed recovery operation. Also, for a physical standby destination database, this archived redo log registry serves as the manifest for the standby recovery operation.
For a logical standby database, the archived redo log filename is recorded in a tablespace maintained by the logical standby database control file, which is then used by log apply services.
You can also use the optional TEMPLATE=
filename_template
attribute to specify a filename format that is different from the format defined by the STANDBY_ARCHIVE_DEST
and LOG_ARCHIVE_FORMAT
initialization parameters at the standby destination. Thus, you can use the TEMPLATE
attribute to override the values entered in the STANDBY_ARCHIVE_DEST
and LOG_ARCHIVE_FORMAT
initialization parameters. This is the filename that will be recorded at the remote destination.
See Also:
REGISTER and NOREGISTER, REGISTER=location_format, and TEMPLATE and NOTEMPLATE in Chapter 12 |
By default, a physical standby database automatically applies archived redo logs when they arrive from the primary database. A logical standby database automatically applies SQL statements once they have been transformed from the archived redo logs. But in some cases, you may want to create a time lag between the archiving of a redo log at the primary site and the applying of the redo log at the standby site. A time lag can protect against the application of corrupted or erroneous data from the primary site to the standby site.
Use the DELAY=
minutes
attribute of the LOG_ARCHIVE_DEST_
n
initialization parameter to specify a time lag for applying redo logs at the standby site. The default setting for this attribute is NODELAY
. If the DELAY
attribute is set with no value specified, then the value for this attribute is 30 minutes. Example 5-17 shows how to set up a destination with a time delay of 4 hours.
The DELAY
interval is relative to when the archived redo log file is complete at the destination; it does not delay the transport of the redo log to the standby database.
LOG_ARCHIVE_DEST_3='SERVICE=stby1 DELAY=240 REOPEN=300' LOG_ARCHIVE_DEST_STATE_3=ENABLE
See Also:
|
You can specify the amount of physical storage on a disk device to be available for an archiving destination using the QUOTA_SIZE
attribute of the LOG_ARCHIVE_DEST_
n
initialization parameter. The QUOTA_SIZE
attribute indicates the maximum number of 512-byte blocks of physical storage on a disk device that may be consumed by a destination. The value is specified in 512-byte blocks even if the physical device uses a different block size. The optional suffix values K, M, and G represent thousand, million, and billion, respectively (the value "1K" means 1,000 512-byte blocks).
A destination can be designated as being able to occupy all or some portion of the physical disk represented by the destination. For example, in a Real Application Clusters environment, a physical archived redo log disk device may be shared by two or more separate nodes (through a clustered file system, such as is available with Sun Clusters). As there is no cross-instance initialization parameter knowledge, none of the Real Application Clusters nodes is aware that the archived redo log physical disk device is shared with other instances. This leads to substantial problems when the destination disk device becomes full; the error is not detected until every instance tries to archive to the already full device. This seriously affects database availability.
The QUOTA_SIZE
value does not have to be the actual number of blocks on the disk device; the value represents the portion of the disk device that can be consumed by the archived redo log destination.
This section describes how to set up log transport services on the standby database in preparation for switchover. The following topics are presented:
Most initialization parameters at the primary and standby databases should be identical, although some initialization parameters such as CONTROL_FILES
and DB_FILE_NAME_CONVERT
must differ. Differences in initialization parameters can cause performance degradation at a standby database and, in some cases, halt database operations completely. Change parameter values only when it is required for the functionality of the standby database or for filename conversions.
The initialization parameters in the following list play a key role in the configuration of the standby database.
COMPATIBLE
CONTROL_FILE_RECORD_KEEP_TIME
CONTROL_FILES
DB_FILE_NAME_CONVERT
DB_FILES
DB_NAME
FAL_CLIENT
(physical standby databases only)FAL_SERVER
(physical standby databases only)LOCK_NAME_SPACE
LOG_ARCHIVE_DEST_
n
(where n
is a value from 1 to 10)LOG_ARCHIVE_DEST_STATE_
n
(where n
is a value from 1 to 10)LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_MAX_PROCESSES
LOG_ARCHIVE_MIN_SUCCEED_DEST
(logical standby databases only)LOG_ARCHIVE_START
LOG_ARCHIVE_TRACE
LOG_FILE_NAME_CONVERT
LOG_PARALLELISM
(logical standby databases only)PARALLEL_MAX_SERVERS
(logical standby databases only)REMOTE_ARCHIVE_ENABLE
STANDBY_ARCHIVE_DEST
STANDBY_FILE_MANAGEMENT
For more information about these and other database initialization parameters specific to the Data Guard environment, see Chapter 11.
Once you have configured the primary database initialization parameter file, you can duplicate the file for use by the standby database. The procedure for creating the standby initialization parameter file is as follows:
Oracle Corporation suggests that you maintain two database initialization parameter files at both the primary and standby databases. This will allow you to easily change the databases from the primary role to the standby role or from the standby role to the primary role.
Example 5-18 and Example 5-19 show sections of initialization parameter files that you could maintain on the primary database. These examples show only parameters specific to log transport services. For complete examples of database initialization files, see Chapter 10.
Example 5-18 shows log transport services initialization parameters for a typical primary database. These log transport services parameter settings are used when the primary database is operating in the primary role.
Note:
|
DB_NAME=primary1 CONTROL_FILES=primary.ctl COMPATIBLE=9.0.1.0.0 LOG_ARCHIVE_START=TRUE LOG_ARCHIVE_DEST="" LOG_ARCHIVE_DUPLEX_DEST="" LOG_ARCHIVE_DEST_1='LOCATION=/oracle/arc/ MANDATORY REOPEN=30' LOG_ARCHIVE_DEST_2='SERVICE=standby MANDATORY REOPEN=15' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_FORMAT=log%t_%s.arc REMOTE_ARCHIVE_ENABLE=TRUE . . .
Example 5-19 shows log transport services parameters for an initialization parameter file to be maintained on the primary database and used if the primary database's role is changed to the standby role.
DB_NAME=primary1 CONTROL_FILES=primary.ctl COMPATIBLE=9.0.1.0.0 LOG_ARCHIVE_START=TRUE # The following parameter is required only if the primary and standby databases # are located on the same system. LOCK_NAME_SPACE=primary1 FAL_SERVER=standby1 FAL_CLIENT=primary1 DB_FILE_NAME_CONVERT=('/standby','/primary') LOG_FILE_NAME_CONVERT=('/standby','/primary') STANDBY_ARCHIVE_DEST=/oracle/arc/ LOG_ARCHIVE_DEST_1='LOCATION=/oracle/arc/' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=DEFER LOG_ARCHIVE_TRACE=127 LOG_ARCHIVE_FORMAT=log%t_%s.arc STANDBY_FILE_MANAGEMENT=AUTO REMOTE_ARCHIVE_ENABLE=TRUE . . .
Example 5-20 and Example 5-21 show sections of initialization parameter files that you could maintain on the standby database. These examples show only parameters specific to log transport services. For complete examples of database initialization files, see Chapter 10.
Example 5-20 shows log transport services parameters for an initialization parameter file to be maintained on the standby database when it is operating in the standby role.
DB_NAME=primary1 CONTROL_FILES=standby.ctl COMPATIBLE=9.0.1.0.0 LOG_ARCHIVE_START=TRUE # The following parameter is required only if the primary and standby databases # are located on the same system. LOCK_NAME_SPACE=standby1 FAL_SERVER=primary1 FAL_CLIENT=standby1 DB_FILE_NAME_CONVERT=("/primary","/standby") LOG_FILE_NAME_CONVERT=("/primary","/standby") STANDBY_ARCHIVE_DEST=/oracle/stby/arc LOG_ARCHIVE_DEST_1='LOCATION=/oracle/stby/arc/' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_TRACE=127 LOG_ARCHIVE_FORMAT=log%t_%s.arc STANDBY_FILE_MANAGEMENT=AUTO REMOTE_ARCHIVE_ENABLE=TRUE . . .
Example 5-21 shows transport services parameters for an initialization parameter file to be maintained on the standby database and used when it is to operate in the primary role.
DB_NAME=primary1 CONTROL_FILES=standby.ctl COMPATIBLE=9.0.1.0.0 LOG_ARCHIVE_START=true LOG_ARCHIVE_DEST="" LOG_ARCHIVE_DUPLEX_DEST="" LOG_ARCHIVE_DEST_1='LOCATION=/oracle/stby/arc/ MANDATORY REOPEN=30' LOG_ARCHIVE_DEST_2='SERVICE=primary1 MANDATORY REOPEN=15' LOG_ARCHIVE_DEST_STATE_1=DEFER LOG_ARCHIVE_DEST_STATE_2=DEFER LOG_ARCHIVE_FORMAT=log%t_%s.arc REMOTE_ARCHIVE_ENABLE=TRUE . . .
Data Guard provides three high-level modes of data protection that you can configure to balance cost, availability, performance, and transaction protection. Using the following SQL SET STANDBY DATABASE
statement on the primary database, you can configure the Data Guard environment to maximize data protection, availability, or performance:
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE};
The default data protection mode is to maximize performance.
See Also:
Chapter 13 for additional information about SQL statements that are pertinent to Data Guard environments |
To determine the appropriate data protection mode to use, you need to weigh your business requirements for data availability against user demands for response time and performance. Table 5-6 summarizes the data protection modes, the LOG_ARCHIVE_DEST_
n
settings for log transport services, and the implications of each one for primary database data divergence and standby database switchover and failover.
The following sections describe all of these aspects in more detail to help you determine the correct protection mode for your Data Guard environment.
Maximum protection mode offers the highest level of data availability for the primary database. When used with force logging, this protection mode guarantees all data that has been committed on the primary database will be available for recovery on the standby site in the event of a failure. Also, if the last participating standby database becomes unavailable, processing automatically halts on the primary database as well. This ensures that no transactions are lost when the primary database loses contact with all of its standby databases.
When operating in maximum protection mode, the log writer process (LGWR) transmits redo records from the primary database to the standby database, and a transaction is not committed on the primary database until it has been confirmed that the transaction data is available on at least one standby database. While this can potentially decrease primary database performance, it provides the highest degree of data protection at the standby site. The impact on performance can be minimized by configuring a network with sufficient throughput for peak transaction load and with low row trip latency. Stock exchanges, currency exchanges, and financial institutions are examples of businesses that require maximum protection.
Issue the following SQL statement on the primary database to define this level of protection for the overall Data Guard configuration:
ALTER DATABASE SET STANDBY TO MAXIMIZE PROTECTION;
The standby sites you want to participate as members of the maximum protection configuration must use standby online redo logs.
You must define at least one standby site destination with these attributes of the LOG_ARCHIVE_DEST_
n
parameter: MANDATORY
, LGWR
, SYNC
, and AFFIRM
. Also, all destinations that will be part of a maximum protection configuration must be enabled (LOG_ARCHIVE_DEST_STATE_
n
=ENABLE
) and reachable on the network when you start the primary database. For example:
LOG_ARCHIVE_DEST_1='LOCATION=/disk1/d1arch OPTIONAL' LOG_ARCHIVE_DEST_2='SERVICE=stby MANDATORY LGWR SYNC AFFIRM' LOG_ARCHIVE_DEST_STATE_1 = ENABLE LOG_ARCHIVE_DEST_STATE_2 = ENABLE
You can define other destinations differently in a maximum protection configuration. Destinations defined with other attributes will not be considered part of the requirements for this mode, but will continue to receive redo logs.
You can defer currently enabled sites using the LOG_ARCHIVE_DEST_STATE_
n
=DEFER
parameter, and then reenable the sites later. This capability has the advantage of allowing you to physically move a site and easily make it a part of the maximum protection configuration by reenabling it using the LOG_ARCHIVE_DEST_STATE_
n
=ENABLE
parameter.
See Also:
Section 5.8 for more information about configuring log transport services |
In a Real Application Clusters configuration, any node that loses connectivity with a standby destination will cause all other members of the cluster to stop sending data to that destination (this maintains the data integrity of the data that has been transmitted to that destination and can be recovered).
When the failed standby destination comes back up, Data Guard runs the site in resynchronization mode until the primary and standby databases are identical (no gaps remain). Then, the standby destination can participate in the maximum protection configuration.
If the lost destination is the last participating standby site, then the instance on the node that loses connectivity will be shut down. Other nodes in a Real Application Clusters configuration that still have connectivity to the last standby site will recover the lost instance and continue sending to their standby site. Only when every node in a Real Application Clusters configuration loses connectivity to the last standby site will the configuration, including the primary database, be shut down.
When a failover operation occurs to a site that is participating in the maximum protection configuration, all data that was ever committed on the primary database will be recovered on the standby site.
Maximum availability mode offers the next highest level of data availability for the primary database. If a standby database becomes unavailable, processing does not halt on the primary database unless a primary database failure occurs before recovery from a network outage. Then, no data is lost up to the last transaction that was transmitted to the site. (Transactions that continued on the primary site after the network went down could be lost.) The standby database may temporarily diverge from the primary database, but upon failover to the standby database, the databases can be synchronized, and no data will be lost.
Maximum availability mode makes a best effort to write the redo records to at least one physical standby database that is configured to use the SYNC
log transport mode. The maximum availability mode does not shut down the primary database instance. Instead, the protection mode is lowered temporarily to maximum performance mode until the fault has been corrected and the standby database has caught up with the primary database.
As with the maximum protection mode, the log writer process (LGWR) transmits redo logs from the primary database to the standby database. The transaction is not complete on the primary database until it has been confirmed that the transaction data is either available on the standby database, or that the data could not be received by the standby database.
This protection mode presents a potential response time degradation and a potential bottleneck for throughput. These can be minimized by configuring a network with sufficient throughput for peak transaction load, and with low row trip latency.
An example of a business that can use this data protection mode is a manufacturing plant; the risks of having no standby database for a period of time and data divergence are acceptable as long as no data is lost if failover is necessary.
Use the following SQL statement on the primary database to define this level of protection for the overall Data Guard configuration:
ALTER DATABASE SET STANDBY TO MAXIMIZE AVAILABILITY;
Although the use of standby online redo log files is optional for this mode, Oracle Corporation recommends that the physical standby sites that you want to participate as members of the maximum availability configuration be configured to use standby online redo logs. You can configure logical standby sites to participate in a maximum availability configuration even though they do not use standby online redo logs.
You set up log transport services to maximize availability exactly the same way as described for the maximum protection mode. You must define at least one site destination with these attributes on the LOG_ARCHIVE_DEST_
n
parameter: MANDATORY
, LGWR
, SYNC
, and AFFIRM
. All destinations that will be part of a maximum availability configuration must be enabled (LOG_ARCHIVE_DEST_STATE_
n
=ENABLE
) and reachable on the network when you start the primary database. For example:
LOG_ARCHIVE_DEST_1='LOCATION=/disk1/d1arch OPTIONAL' LOG_ARCHIVE_DEST_2='SERVICE=stby MANDATORY LGWR SYNC AFFIRM' LOG_ARCHIVE_DEST_STATE_1 = ENABLE LOG_ARCHIVE_DEST_STATE_2 = ENABLE
You can define other destinations differently in a maximum availability configuration. Destinations defined with other attributes will not be considered part of the requirements for this mode.
You can defer currently enabled sites using the LOG_ARCHIVE_DEST_STATE_
n
=DEFER
parameter, and then reenable the sites later. This provides the ability to physically move a site (while it is deferred) but still easily make it a part of the maximum availability configuration by reenabling it using the LOG_ARCHIVE_DEST_STATE_
n
=ENABLE
parameter.
See Also:
Section 5.8 for more information about configuring log transport services |
In a Real Application Clusters configuration, any node that loses connectivity with a standby destination will cause all other members of the Real Application Clusters configuration to stop sending data to that destination. When the failed standby destination comes back up, Data Guard runs the site in resynchronization mode until the primary and standby databases are identical (no gaps remain). Then, the standby destination can again participate in the maximum availability configuration. Losing the last standby destination does not cause the primary database instance to shut down.
When a failover operation occurs to a site that is participating in the maximum availability configuration, all data that was ever committed on the primary database and was successfully sent to the standby database will be recovered on the standby site.
Maximum performance mode is the default protection mode. It offers slightly less primary data protection than maximum availability mode. During normal operations, processing on the primary database continues without regard to data availability on any standby database. If any standby destination becomes unavailable at any time, processing does not halt on the primary database and there is little or no effect on performance.
You can use the log writer process (LGWR) or the archiver process (ARCn) to transmit archived redo logs to the standby sites, and you can specify a synchronous or asynchronous network transmission mode. Use the maximum performance mode when availability and performance on the primary database are more important than the risk of losing a small amount of data. This protection mode can be used if your business has some other form of backup or if the loss of data would have only a small effect on your business.
You use the following SQL statement to define this level of protection for the overall Data Guard configuration:
ALTER DATABASE SET STANDBY TO MAXIMIZE PERFORMANCE;
Standby online redo logs are not required for this protection mode; therefore, both physical and logical standby sites can participate in a maximum performance configuration. Physical standby databases can use the standby redo logs if the log transport services are set up using the LGWR
attribute.
You do not need to define standby destinations to implement the maximum performance mode. However, Oracle Corporation recommends that you configure at least one standby site; availability cannot be achieved without the presence of at least one standby site in the configuration.
If you define any destinations, you can define them with any combination of log transport services attributes: LGWR SYNC
, LGWR ASYNC
, or ARCH
. Destinations that will be part of the Data Guard configuration operating under this protection mode do not have to be enabled or reachable at the time you start the primary database instance.
You can defer currently enabled sites using the LOG_ARCHIVE_DEST_STATE_
n
=DEFER
parameter, and then reenable the sites later. This provides the ability to physically move a site (while it is deferred) but still easily make it a part of the maximum performance configuration by reenabling it using the LOG_ARCHIVE_DEST_STATE_
n
=ENABLE
parameter.
See Also:
Section 5.8 for more information about configuring log transport services |
In a Real Application Clusters environment, any node that loses connectivity with a standby destination will stop transmitting archived redo logs to that destination, but all other nodes in the Real Application Clusters configuration will continue sending data to that destination. When the failed standby destination comes back up, Data Guard runs the site in resynchronization mode until the primary and standby databases are identical (no gaps remain). Then, the standby destination can again participate in the maximum performance configuration. Losing the last standby destination does not cause the primary database instance to shut down.
When a failover operation occurs to any standby site, data that was received from the primary database will be recovered on the standby database up to the last transactionally consistent point in time. In a single-instance configuration, this means all data received will be recovered. In a failover situation, it is possible to lose some transactions from one or more logs that have not yet been transmitted.
Log transport services supports three data protection modes, as explained in Section 5.7. You configure data protection modes by:
Table 5-7 lists the protection modes and the settings required to implement each mode. You can set other variations of archive destination attributes and log transport services options; however, the following table identifies the most common settings.
Timely protection of application data requires use of the log writer process (LGWR) to propagate primary database modifications to one or more standby databases. This is achieved using the LGWR
attribute of the LOG_ARCHIVE_DEST_
n
initialization parameter.
Attribute | Example | Default |
---|---|---|
{ |
|
|
Choosing the LGWR
attribute indicates that the log writer process (LGWR) will concurrently create the archived redo logs as the online redo log is populated. Depending on the configuration, this may require the log writer process to also transmit redo logs to remote archival destinations.
Choosing the ARCH
attribute indicates that one of the archiver processes (ARCn) will create archived redo logs on the primary database and also transmit redo logs for archiving at specified destinations. This is the default setting.
When you set the LOG_ARCHIVE_DEST_
n
parameter to specify LGWR
instead of ARCH
, the behavior is as follows. Instead of waiting for the online redo log to switch at the primary database and then let the archiver process write the entire archived redo log at the standby destination all at once, the log writer process creates a new redo log at the standby site that reflects the log sequence number (and size) of the current online redo log of the primary database. Then, as redo is generated at the primary database, it is also propagated to the standby database redo log file. Based on whether you specify the SYNC
or ASYNC
network transmission mode, the propagation will either be immediate or asynchronous.
The LGWR
and ARCH
attributes are mutually exclusive. Therefore, you cannot specify both attributes for the same destination. However, you can specify either the LGWR
or the ARC
n
attribute for individual destinations. This allows you to specify that the log writer process writes to redo logs and archives for some destinations while the archiver process archives redo logs to other destinations.
Note: The log writer process will transmit log buffers to the archival destination only if the primary database is in ARCHIVELOG mode. |
When using the log writer process to archive redo logs, you can specify synchronous (SYNC
) or asynchronous (ASYNC
) network transmission of redo logs to archiving destinations using the SYNC
or ASYNC=
blocks
attributes. If you do not specify either the SYNC
or ASYNC
attribute, the default is the SYNC
network transmission mode.
If you specify the SYNC attribute, all network I/O operations are performed synchronously, in conjunction with each write operation to the online redo log. Control is not returned to the executing application or user until the redo information is received by the standby site. This attribute has the potential to affect primary database performance adversely, but provides the highest degree of data protection at the destination site. Synchronous transmission is required for no-data-loss environments.
When you specify the LGWR
and SYNC
attributes to transmit redo logs to multiple standby destinations, you can specify either the SYNC=PARALLEL
or SYNC=NOPARALLEL
option for each destination:
SYNC=NO
PARALLEL
, the LGWR process initiates an I/O operation to the first destination and waits until the operation completes before initiating an I/O operation to the next destination, and so on.SYNC=P
ARALLEL
, the LGWR process initiates an I/O operation to each destination at the same time. However, the LGWR process waits for each of the I/O operations to complete before continuing, which is the same as performing multiple, synchronous I/O operations simultaneously.
If you specify the SYNC
attribute without specifying an option, the default option depends on whether the location is local or remote, and on whether you chose the ARCH or LGWR process for the destination. Because the PARALLEL
qualifier is not supported for the ARCH process, the default will always be NOPARALLEL
. Table 5-8 shows the default PARALLEL
and NOPARALLEL
options.
If you specify the ASYNC=
blocks
attribute, all network I/O operations are performed asynchronously, and control is returned to the executing application or user immediately. You can specify a block count to determine the size of the SGA network buffer to be used. Block counts from 0 to 20,480 are allowed. The attribute allows the optional suffix value K to represent 1,000 (the value "1K" indicates 1,000 512-byte blocks). In general, for slower network connections, use larger block counts.
When you use the ASYNC
attribute, there are several events that cause the network I/O to be initiated:
Attribute | Example | Default |
---|---|---|
{ |
|
|
Table 5-9 identifies the attributes of the LOG_ARCHIVE_DEST_
n
initialization parameter that are used to specify the transmission mode.
The [NO]AFFIRM
attribute of the LOG_ARCHIVE_DEST_
n
initialization parameter is used to specify whether log archiving disk write I/O operations on the standby database are to be performed synchronously or asynchronously. By default, disk write operations are performed asynchronously.
It is necessary for the primary database to receive acknowledgment of the availability of the modifications on the standby database in a maximum protection or maximum availability environment. This is achieved using both the SYNC
and AFFIRM
attributes of the LOG_ARCHIVE_DEST_
n
initialization parameter. The SYNC
attribute indicates that synchronous network transmission is necessary, and the AFFIRM
attribute indicates that synchronous archived redo log disk write I/O operations are necessary. Together, these attributes ensure that primary database modifications are available on the standby database.
This attribute applies to local and remote archive destination disk I/O operations and standby redo log disk write I/O operations.
Attribute | Example | Default |
---|---|---|
|
|
|
Note: The |
Online redo logs transferred from the primary database are received by the remote file server process (RFS) on the physical standby site and can be stored as either standby redo logs or archived redo logs as shown in Figure 5-2.
Text description of the illustration redologs.gif
Oracle9i Data Guard introduces the concept of standby redo logs. Standby redo logs are required for physical standby databases running in the overall maximum protection mode. Standby redo logs form a separate pool of log file groups and provide the following advantages over archived online redo logs:
You should also configure standby redo logs on the primary database. Even though the standby redo logs are not used when the database is running in the primary role, configuring the standby redo logs on the primary database is recommended in preparation for an eventual switchover operation.
Standby redo logs are created using the ADD STANDBY LOGFILE
clause of the ALTER DATABASE
statement. Although standby redo logs are only used when the database is running in the standby role, Oracle Corporation recommends that you create standby redo logs so that the primary database can switch roles easily and quickly without additional DBA intervention.
To verify that standby redo logs have been created, query the V$STANDBY_LOG
view (displays standby redo log status as ACTIVE
or INACTIVE
) or the V$LOGFILE
view. The following example queries the V$LOGFILE
view:
SQL> SELECT * FROM V$LOGFILE WHERE TYPE = 'STANDBY';
Additional standby log group members can be added later to provide another level of reliability against disk failure on the standby site.
Once you have created the standby redo logs, they are automatically selected as the repository of redo data received from the primary site, if all of the following conditions apply:
For example, if the primary database uses two online redo log groups whose log size is 100K and 200K, respectively, then the standby database should have standby redo log groups with those same sizes. However, it may be necessary to create additional standby log groups on the standby database, so that the archival operation has time to complete before the standby redo log is used. If the primary database is operating in maximum protection mode, and a standby redo log cannot be allocated, the primary database instance may be shut down immediately. (In some cases, the primary database may wait for the standby redo log to become available.) Therefore, be sure to allocate an adequate number of standby redo logs.
When you use Real Application Clusters, the various standby redo logs are shared among the various primary database instances. Standby redo log groups are not dedicated to a particular primary database thread.
Standby redo logs must be archived before the data can be applied to the standby database. The standby archival operation occurs automatically, even if the standby database is not in ARCHIVELOG mode. However, the archiver process must be started on the standby database. Note that the use of the archiver process (ARCn) is a requirement for selection of a standby redo log.
Because of this additional archival operation, using standby redo logs may cause the standby database to lag further behind the primary database than when archiving directly from the primary database to standby destinations. However, the use of standby redo logs ultimately improves redo data availability on the standby database.
The best way to determine the appropriate number of standby redo log groups for a database instance is to test different configurations. The minimum configuration should have one more standby redo log group than the primary database. The optimum configuration has a few more groups than the primary database.
In some cases, a standby database instance may require only two groups. In other situations, a database may require additional groups to guarantee that a recycled group is always available to receive redo information from the primary database. During testing, the easiest way to determine if the current standby log configuration is satisfactory is to examine the contents of the RFS process trace file and the database alert log. If messages indicate that the RFS process frequently has to wait for a group because archiving has not completed, add more standby groups.
Consider the parameters that can limit the number of standby redo log groups before setting up or altering the configuration of the standby redo log groups. The following parameters limit the number of standby redo log groups that you can add to a database:
MAXLOGFILES
parameter of the CREATE DATABASE
statement for the primary database determines the maximum number of groups of standby redo logs per standby database. The only way to override this limit is to re-create the primary database or control file.LOG_FILES
parameter can temporarily decrease the maximum number of groups of standby redo logs for the duration of the current instance.MAXLOGMEMBERS
parameter of the CREATE DATABASE
statement used for the primary database determines the maximum number of members per group. The only way to override this limit is to re-create the primary database or control file.
Plan the standby redo log configuration of a database and create all required groups and members of groups after you have instantiated the standby database. However, there are cases where you might want to create additional groups or members. For example, adding groups to a standby redo log configuration can correct redo log group availability problems. To create new standby redo log groups and members, you must have the ALTER DATABASE
system privilege. A database can have as many groups as the value of MAXLOGFILES
.
To create a new group of standby redo logs, use the ALTER DATABASE
statement with the ADD STANDBY LOGFILE
clause.
The following statement adds a new group of standby redo logs to the database:
SQL> ALTER DATABASE ADD STANDBY LOGFILE 2> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500K;
You can also specify a number that identifies the group using the GROUP
option:
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 2> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500K;
Using group numbers can make administering standby redo log groups easier. However, the group number must be between 1 and the value of the MAXLOGFILES
parameter. Do not skip redo log file group numbers (that is, do not number groups 10, 20, 30, and so on), or you will consume additional space in the standby database control file.
In some cases, it might not be necessary to create a complete group of standby redo logs. A group could already exist, but not be complete because one or more members were dropped (for example, because of disk failure). In this case, you can add new members to an existing group.
Use fully qualified filenames of new log members to indicate where the file should be created. Otherwise, files will be created in either the default or current directory of the database server, depending upon your operating system.
To add new standby redo log group members, use the ALTER DATABASE
statement with the ADD STANDBY LOGFILE MEMBER
parameter. The following statement adds a new member to redo log group number 2:
SQL> ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/oracle/dbs/log2b.rdo' 2> TO GROUP 2;
See Also:
Oracle9i SQL Reference for a complete description of the |
When archived redo logs are used, use the STANDBY_ARCHIVE_DEST
initialization parameter on the standby database to specify the directory in which to store the archived redo logs. Log transport services use this value in conjunction with the LOG_ARCHIVE_FORMAT
parameter to generate the archived redo log filenames on the standby site.
Log transport services store the fully qualified filenames in the standby control file. Log apply services use this information to perform recovery operations on the standby database. You can access this information through the V$ARCHIVED_LOG
view on the standby database:
SQL> SELECT NAME FROM V$ARCHIVED_LOG; NAME -------------------------------------------------------------------------------- /arc_dest/log_1_771.arc /arc_dest/log_1_772.arc /arc_dest/log_1_773.arc /arc_dest/log_1_774.arc /arc_dest/log_1_775.arc
When standby redo logs are used, the LOG_ARCHIVE_DEST_
n
initialization parameter (where n
is a value from 1 to 10) on the standby database specifies the directory in which to store standby redo logs.
A failure resolution policy determines what actions will occur on a primary database when the last standby destination fails to archive redo logs.
To set the highest level of data protection, place the primary database in maximum protection mode using the SET STANDBY DATABASE
clause of the ALTER DATABASE
statement as shown in the following example:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
If connectivity between the primary and standby database should now be lost, the primary database will shut down. When using this level of data protection, standby databases must have two or more standby redo log groups. Also, one or more primary database archived redo log destinations must have LGWR
and SYNC
attributes specified. The functionality of the AFFIRM
attribute is implicitly set.
You can revert to a mode that allows some data divergence by placing the primary database in MAXIMIZE AVAILABILITY
mode using the following statement:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
See Also:
Oracle9i SQL Reference for complete |
How you set the destination disk write I/O attributes NOA
FFIRM
and A
FFIRM
, and the S
YNC
and A
SYNC
network transmission attributes, affects performance on the primary database and data availability on the standby database. (The SYNC
and ASYNC
attributes affect network I/O performance only when the log writer process is used.) Table 5-10 shows a comparison of primary database performance to data protection on the standby database when various combinations of archive process, network I/O, and disk I/O attribute settings are used.
The process of archiving redo logs involves reading a buffer from the redo log and writing it to the archive log location. When the destination is remote, the buffer is written to the archive log location over the network using Oracle Net services.
The default archive log buffer size is 1 megabyte. The default transfer buffer size for Oracle Net is 2 kilobytes. Therefore, the archive log buffer is divided into units of approximately 2 kilobytes for transmission. These units could get further divided depending on the maximum transmission unit (MTU) of the underlying network interface.
The Oracle Net parameter that controls the transport size is session data unit (SDU). This parameter can be adjusted to reduce the number of network packets that are transmitted. This parameter allows a range of 512 bytes to 32 kilobytes.
For optimal performance, set the Oracle Net SDU
parameter to 32 kilobytes for the associated SERVICE
destination parameter.
The following example shows a database initialization parameter file segment that defines a remote destination netserv
:
LOG_ARCHIVE_DEST_3='SERVICE=netserv' SERVICE_NAMES=srvc
The following example shows the definition of that service name in the tnsnames.ora
file:
netserv=(DESCRIPTION=(SDU=32768)(ADDRESS=(PROTOCOL=tcp)(HOST=host) (PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=srvc)(ORACLE_HOME=/oracle)))
The following example shows the definition in the listener.ora
file:
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp) (HOST=host)(PORT=1521)))) SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SDU=32768)(SID_NAME=sid) (GLOBALDBNAME=srvc)(ORACLE_HOME=/oracle)))
If you archive to a remote site using high-latency/high-bandwidth connections, you can improve performance by increasing the TCP send and receive window sizes. Use caution, however, because this may adversely affect networked applications that do not exhibit the same characteristics as archiving. This method consumes a large amount of system resources.
You can also use cascading standby databases to off-load network processing from the primary database to a standby database. See Appendix E for more information.
This section describes manual methods of monitoring redo log archival activity for the primary database.
The Oracle9i Data Guard Manager graphical user interface automates many of the tasks involved in monitoring a Data Guard environment. See Oracle9i Data Guard Broker and the Data Guard Manager online help for more information.
Enter the following query on the primary database to determine the current redo log sequence numbers:
SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG; THREAD# SEQUENCE# ARC STATUS -------- --------- --- ------ 1 947 YES ACTIVE 1 948 NO CURRENT
Enter the following query at the primary database to determine the most recently archived redo log file:
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG; MAX(SEQUENCE#) -------------- 947
Enter the following query at the primary database to determine the most recently archived redo log file to each of the archive destinations:
SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# 2> FROM V$ARCHIVE_DEST_STATUS 3> WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE'; DESTINATION STATUS ARCHIVED_THREAD# ARCHIVED_SEQ# ------------------ ------ ---------------- ------------- /private1/prmy/lad VALID 1 947 standby1 VALID 1 947
The most recently archived redo log file should be the same for each archive destination listed. If it is not, a status other than VALID
may identify an error encountered during the archival operation to that destination.
You can issue a query at the primary database to find out if a log has not been sent to a particular site. Each archive destination has an ID number associated with it. You can query the DEST_ID
column of the V$ARCHIVE_DEST
fixed view on the primary database to identify archive destination IDs.
Assume the current local archive destination is 1, and one of the remote standby archive destination IDs is 2. To identify which logs have not been received by this standby destination, issue the following query:
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM 2> (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) 3> LOCAL WHERE 4> LOCAL.SEQUENCE# NOT IN 5> (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND 6> THREAD# = LOCAL.THREAD#); THREAD# SEQUENCE# --------- --------- 1 12 1 13 1 14
See Also:
Appendix A, "Troubleshooting the Standby Database" to learn more about monitoring the archiving status of the primary database |
To see the progression of the archiving of redo logs to the standby site, set the LOG_ARCHIVE_TRACE
parameter in the primary and standby initialization parameter files. See Section 6.4.8 for complete details and examples.
|
Copyright © 1999, 2002 Oracle Corporation. All Rights Reserved. |
|