Oracle® Data Guard Broker 10g Release 2 (10.2) Part Number B14230-02 |
|
|
View PDF |
This chapter describes managing the states and properties that are specific to the database. This chapter contains the following sections:
The broker manages database objects. A database object corresponds to a primary or standby database. The broker uses each object's profile to manage and monitor the state of a single database.
The broker distinguishes between physical and logical standby databases. These databases are configured with profiles having states and properties that are appropriate for their standby types.
When a configuration is enabled, its databases can be in one of several states. Table 4-1 describes all of the possible primary and standby database states.
Table 4-1 Database States and Descriptions
Database Role | State Name | Description |
---|---|---|
Primary |
The primary database is open for read/write access and redo transport services are archiving online redo log files to the standby databases. If this is a RAC database, all started instances are open in read/write mode and have redo transport services running. This is the default state for a primary database when it is enabled for the first time. |
|
Primary |
The primary database is open for read/write access, but redo transport services are not transmitting redo data to the standby databases. If this is a RAC database, all started instances are open in read/write mode and redo transport services are not running on any instances. |
|
Physical standby |
The physical standby database is mounted and Redo Apply is started. The standby database is not open for read-only queries. If the standby database is a RAC database, the broker starts log apply services on exactly one standby instance, called the apply instance. If this instance fails, the broker automatically chooses another started instance. This new instance then becomes the apply instance. This is the default state for a physical standby database when it is enabled for the first time. |
|
Physical standby |
The physical standby database is mounted, but Redo Apply is stopped. The standby database is not open for read-only queries. If this is a RAC database, there is no instance running log apply services until you change the database state to |
|
Physical standby |
The physical standby database is open for read-only queries, and Redo Apply is stopped. If this is a RAC database, one or more instances will be open in read-only mode. Redo Apply is not running on any instance. |
|
Logical standby |
The logical standby database is open for read-only queries and SQL Apply is started. The logical standby database guard is on. If this is a RAC database, SQL Apply is running on one instance, the apply instance. If this instance fails, the broker automatically chooses another started instance. This new instance becomes the apply instance. This is the default state for a logical standby database when it is enabled for the first time. |
|
Logical standby |
The logical standby database is open for read-only queries, and SQL Apply is not running. The logical standby database guard is on. If this is a RAC database, there is no instance running SQL Apply until you change the database state to |
|
All |
When you set the state of a standby database to When you set the state of a primary database to If this is a RAC database, all started instances are shutdown. |
Figure 4-1 graphically shows the possible transitions between the states that were described in Table 4-1. The figure shows the database state transitions that can occur on a primary database, a physical standby database, and a logical standby database. This figure has three groupings of circles representing each state (one group for each database type) with arrows showing the transitions that are allowed between the states on each database.
Figure 4-1 Database State Transition Diagrams
You can use the DGMGRL EDIT DATABASE
command to explicitly change the state of a database. For example, the EDIT DATABASE
command in the following example changes the state of the North_Sales
database to LOG-TRANSPORT-OFF
.
DGMGRL> EDIT DATABASE 'North_Sales' SET STATE='LOG-TRANSPORT-OFF'; Succeeded.
See Also:
Chapter 8 for complete information about theEDIT DATABASE
command. See Chapter 6 for examples of performing state transitions using Enterprise Manager.The following sections describe in more detail the possible state transitions for primary and standby databases.
Primary database state transitions
For the primary database, when transitioning from any state to the ONLINE
state, the broker sets up redo transport services to all broker-managed standby databases using the log transport-related properties of the standby databases. See Section 4.4 for the list of all log transport-related properties. Redo transport services setup is done by setting the LOG_ARCHIVE_DEST_
n and LOG_ARCHIVE_DEST_STATE_
n initialization parameters on the primary database, and the LOG_ARCHIVE_CONFIG
initialization parameter on all databases (primary or standby). If necessary, the broker also sets up the data protection mode of the database to match the protection mode recorded in the broker configuration file, and opens the database for read and write access. Finally, the broker switches a log for each thread to initiate redo transport services.
When transitioning from any state to the LOG-TRANSPORT-OFF
state, the broker turns off redo transport services to all broker-managed standby databases by resetting the LOG_ARCHIVE_DEST_STATE_
n initialization parameter. Transmission of redo data to all broker-managed standby databases is stopped. Log files continue to be archived at the primary database.
When transitioning to the OFFLINE
state, the broker shuts down the primary database. The primary database is not available, and the broker is no longer managing the configuration. To transition out of the OFFLINE
state, you need to start up the database in the mounted mode. The broker will restore the primary database to the ONLINE
state.
Note:
Before setting the state toOFFLINE
, you should carefully consider whether or not the interruption in access to data and computing resources is necessary.If the primary database is a RAC database, the broker configures redo transport services on all primary instances with the exact same settings.
See Also:
Section 4.4 for more details on managing redo transport servicesPhysical standby database state transitions
For a physical standby database, when transitioning from any state to the ONLINE
state, the broker starts Redo Apply with options specified by the log apply-related properties. See Section 4.5 for the property list. If the standby database is a RAC database, the broker starts Redo Apply on one standby instance, called the apply instance.
When transitioning to the LOG-APPLY-OFF
state, the broker stops Redo Apply if the database is in the ONLINE
state, or closes the database if the database is in the READ-ONLY
state.
When transitioning to the READ-ONLY
state, the broker stops Redo Apply if it is running, and opens the database for read-only access. If the standby database is a RAC database, all currently active instances will be open READ-ONLY
.
Note:
Before you transition a physical standby database from aREAD-ONLY
state to any other state, it is recommended that you first close all open sessions of the standby database. If some of the sessions are not closed, the broker will automatically shut down all pending user sessions during the state transition.When transitioning to the OFFLINE
state, the broker shuts down the standby database. The standby database is not available, the broker stops redo transport services to this database, and the broker stops managing this database. To transition out of the OFFLINE
state, you need to start up the database in the mounted mode. The broker restores the standby database to the state it was in before the OFFLINE state.
See Also:
Section 4.5 for more details on managing Redo ApplyLogical standby database state transitions
For a logical standby database, when transitioning from any state to the ONLINE
state, the broker opens the database if it is not yet opened, enables the database guard to prevent modifications to tables in the logical standby database, and starts SQL Apply with options specified by the log apply-related properties. If the logical standby database is a RAC database, the broker starts SQL Apply on one standby instance, the apply instance.
When transitioning to the LOG-APPLY-OFF
state, the broker stops SQL Apply.
When transitioning to the OFFLINE
state, the broker shuts down the logical standby database. The logical standby database is not available, the broker stops redo transport services to this database, and the broker stops managing this database. To transition out of the OFFLINE
state, you need to start up the database in the mounted mode. The broker restores the standby database to the state it was in before the OFFLINE
state.
See Also:
Section 4.5 for information about managing SQL Apply
Oracle Data Guard Concepts and Administration for information about the database guard
There are two types of properties: monitorable and configurable. Both monitorable and configurable properties can be further defined into those properties that have database scope and those having instance scope.
Monitorable property values can be viewed only when the associated object is enabled.
Monitorable properties allow you to view information related to database objects, but you cannot change the values of these properties.
Configurable property values can be viewed and dynamically updated.
Configurable properties affect the operation or configuration of the broker. You can change the value of these properties using DGMGRL or Enterprise Manager. You can edit properties if the configuration and its databases are enabled, disabled, online, or offline. However, if the database is disabled, the new property value will not take effect until you enable the configuration or database, as appropriate.
See Also:
Chapter 9 for a detailed list of all database propertiesTo see these properties, you might use the DGMGRL SHOW
command or Edit Properties page in Enterprise Manager. Example 4-1 uses the SHOW DATABASE VERBOSE
command to display information about the North_Sales
database.
Example 4-1 Using the SHOW DATABASE VERBOSE Command to Display Properties
DGMGRL> SHOW DATABASE VERBOSE 'North_Sales' ; Database Name: North_Sales Role: PRIMARY Enabled: YES Intended State: ONLINE Instance(s): sales1 Properties: InitialConnectIdentifier = 'North_Sales.foo.com' LogXptMode = 'ARCH' Dependency = '' DelayMins = '0' Binding = 'OPTIONAL' MaxFailure = '0' MaxConnections = '0' ReopenSecs = '30' NetTimeout = '180' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '5' LogArchiveMinSucceedDest = '1' DbFileNameConvert = 'dbs/s2t, dbs/t' LogFileNameConvert = 'dbs/s2t, dbs/t' FastStartFailoverTarget = '' StatusReport = '(monitor)' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' HostName = 'north.foo.com' SidName = 'sales1' LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=north.foo.com)(PORT=1514))' StandbyArchiveLocation = '/archfs/arch/' AlternateLocation = '' LogArchiveTrace = '255' LogArchiveFormat = 'r_%d_%t_%s_%r.arc' LatestLog = '(monitor)' TopWaitEvents = '(monitor)' Current status for "North_Sales": SUCCESS
See Also:
Chapter 8 for complete information about the DGMGRL command-line interfaceMonitorable properties allow you to view information related to the database, but you cannot change the values of these properties. These properties can be very helpful when you are trying to diagnose problems in the broker configuration. For example, you can view the InconsistentLogXptProps
property to determine where there is a discrepancy in redo transport services properties between the broker configuration file and the actual value currently used by the database.
You can list all monitorable properties using the DGMGRL SHOW DATABASE VERBOSE
command. Use the SHOW DATABASE
command to obtain more details about a particular property. For example, the following shows the InconsistentLogXptProps
property:
DGMGRL> SHOW DATABASE 'North_Sales' 'InconsistentLogXptProps'; INCONSISTENT LOG TRANSPORT PROPERTIES INSTANCE_NAME STANDBY_NAME PROPERTY_NAME MEMORY_VALUE BROKER_VALUE sales1 DR_Sales DelayMins 30 10
Enterprise Manager displays the information obtained from these properties on the Edit Properties page.
Configurable properties affect the operation or configuration of the database. When you use DGMGRL or Enterprise Manager to create a primary database object and import existing standby databases into a new broker configuration, the property values are initially imported from the database settings.
You can update many property values when the database is either disabled or enabled. When a new database is added into the configuration, the broker connects to the database and imports initial values for the database properties from the current database settings. For example:
DGMGRL> SHOW DATABASE 'North_Sales' 'ArchiveLagTarget'; ArchiveLagTarget = '0' DGMGRL> EDIT DATABASE 'North_Sales' SET PROPERTY 'ArchiveLagTarget'=1200; Property "ArchiveLagTarget" updated DGMGRL> SHOW DATABASE 'North_Sales' 'ArchiveLagTarget'; ArchiveLagTarget = '1200'
When the configuration is enabled, the broker keeps the database property values in the broker configuration file consistent with the values being used in the database. For those that are related to initialization parameter properties, the broker maintains the consistency among the value in the broker configuration file, the current database value, and the initialization parameter value in the server parameter file, as follows:
For dynamic parameters, the broker keeps the value of the database parameter consistent in the system global area (SGA) for the instance, in the broker configuration file, and in the server parameter file.
For static parameters and properties, the database parameter value in the system global area (SGA) for the instances may temporarily differ from what is in the broker configuration file and in the server parameter file. Typically, the database value becomes the same as the server parameter file value and the broker configuration file value the next time the database instance is stopped and restarted.
Even when the configuration is disabled, you can update database property values through the broker. The broker retains the property settings (without validating the values) and updates the database initialization parameters in the server parameter file and the settings in memory the next time you enable the broker configuration.
Note:
Even though you can change a property value when the configuration is disabled, the change does not take effect on the database unless the configuration is enabled. Also note that some property values can only be changed in the disabled state.To manage redo transport services, you specify the following set of configurable properties on each standby database:
AlternateLocation
Binding
Dependency
LogShipping
LogXptMode
MaxFailure
NetTimeout
ReopenSecs
StandbyArchiveLocation
You can use these properties to specify how the broker configures redo transport services for the standby database. The actual redo transport setup, such as setting the LOG_ARCHIVE_DEST_
n initialization parameter, is carried out by the broker on the primary database (except for the StandbyArchiveLocation
property). If changing the property requires that you change the LOG_ARCHIVE_DEST_
n initialization parameter attributes, the broker forces a log switch on each thread so that the new setting is adopted immediately by the primary database.
You may also preset these properties on the primary database in preparation for it to be switched over to a standby database.
Section 4.6 describes how the broker handles data protection modes. As a part of the overall configuration protection mode, you must ensure that redo transport services are also properly set up for the data protection mode that you choose.
You use the LogXptMode
property to set the SYNC
, ASYNC
, or ARCH
mode for redo transport services. See Table 4-2 for additional information about protection modes and redo transport services.
The values for the LogXptMode
property are described in the following list:
Configures redo transport services for this standby database using the LGWR
, SYNC
, and AFFIRM
attributes of the LOG_ARCHIVE_DEST_
n initialization parameter. This mode, along with standby redo log files, is required for the maximum protection or maximum availability protection modes. This redo transport service enables the highest grade of data protection to the primary database, but also incurs the highest performance impact.
Configures redo transport services for this standby database using the LGWR
, ASYNC
, and NOAFFIRM
attributes of the LOG_ARCHIVE_DEST_
n initialization parameter. This mode, along with standby redo log files, enables a moderate grade of protection to the primary database, and lower performance impact.
Configures redo transport services for this standby database using the ARCH
attribute of the LOG_ARCHIVE_DEST_
n initialization parameter. Standby redo log files are not required. This mode enables the lowest grade of protection to the primary database, and the lowest performance impact.
Note:
When you change the protection mode, Enterprise Manager automatically sets up standby redo log files on one or more standby databases in your configuration, and on the primary database in preparation for switchover. If you use DGMGRL, you must create standby redo log files using the procedure described in Oracle Data Guard Concepts and Administration.Turn redo transport services on and off by setting the state of the primary database. Setting the primary database state to ONLINE
turns on redo transport services to the standby databases, and setting the primary database state to LOG-TRANSPORT-OFF
turns off redo transport services to all the standby database.
Note:
Oracle does not recommend turning off redo transport services to all standby databases. This increases the risk of data loss if the primary database fails.Turn redo transport services on and off to an individual standby database using the LogShipping
property on the standby database. The LogShipping
property accepts values ON
and OFF
. If you set the LogShipping
property to OFF
for a standby database, redo transport services to this standby database are turned off, while redo transport services to other databases are not affected. You can set LogShipping
to ON
to turn back on redo transport services to the standby database.
The relationship between setting the primary database state and setting the LogShipping
property is as follows:
If the primary database state is set to LOG-TRANSPORT-OFF
, redo transport services to all the standby databases are turned off regardless of the LogShipping
property values of the individual standby databases.
If the primary database state is set to ONLINE
, redo transport services to each standby database are determined by the LogShipping
property of that database.
Example 4-2 and Example 4-3 show how to turn off redo transport services in two different scenarios.
Example 4-2 Turn Off Redo Transport Services to All Standby Databases
DGMGRL> EDIT DATABASE 'North_Sales' SET STATE="LOG-TRANSPORT-OFF"; Succeeded. DGMGRL> SHOW DATABASE 'North_Sales'; Database Name: North_Sales Role: PRIMARY Enabled: YES Intended State: LOG-TRANSPORT-OFF Instance(s): sales1 Current status for "North_Sales": SUCCESS
You can set up locations on the standby database to store the archived redo log files to be used by log apply services on the standby database. This is done by setting the StandbyArchiveLocation
and AlternateLocation
properties on the standby database.
StandbyArchiveLocation
specifies a standby location where the archived redo log files will be stored. The broker ensures that the archived redo log files are stored in the same specified location whether or not the standby database has standby redo log files. The broker only manages the location on the standby database to store archived redo log files received from the primary database. For archived redo log files generated locally when the database is either the primary database or a logical standby database, you need to set up local destinations directly through the LOG_ARCHIVE_DEST_
n initialization parameter. The broker allows the value of StandbyArchiveLocation
to be the same as the location you set up for locally generated logs, in which case the broker sets up the VALID_FOR
attribute of the destination appropriately so that it can be used for both the archived redo log files received from the primary database and archived redo log files generated locally.
Note:
On a logical standby database, Oracle recommends that theLOCATION
attribute of the LOG_ARCHIVE_DEST_
n initialization parameter for the local destination be different from the value of either the StandbyArchiveLocation
or AlternateLocation
property.The broker does not manage the local destinations for locally-generated archived redo log files, but the broker does ensure that the location specified by StandbyArchiveLocation
is not used to store locally-generated archived redo log files. (This is done by setting the VALID_FOR
attribute of the destination to be (STANDBY_ROLE,STANDBY_LOGFILE)
.)
You can also set up an alternate location to store archived redo log files on the standby using the AlternateLocation
property on the standby database. This is useful for avoiding disk capacity problems or disk errors when archiving the online redo log files on the standby database. AlternateLocation
specifies a standby location where the archived redo log files will be stored if the location specified by the StandbyArchiveLocation
fails. The broker sets up the alternate location properly using the ALTERNATE
attribute of the LOG_ARCHIVE_DEST_
n initialization parameter.
Note:
If the flash recovery area is set up on the standby database and you have configured it to store archived redo log files from the primary database, the broker no longer manages the standby locations. In this case, the broker disallows the setting of theStandbyArchiveLocation
and AlternateLocation
properties.You can use the Dependency
property to set up a standby database whose incoming archived redo log files depends on another standby database or on the primary database. This is useful when two standby databases are on the same system (for example, one physical and one logical standby database). This is also useful if the standby database is on the same system as the primary database, in which case one standby database can share the log files with the other database on the same system, and there is no need to archive the log files separately to this standby database.
For example, if you want standby Database B to depend on another Database A (either a standby database or the primary database), you can set the Dependency
property of standby Database B to be the database object name (same as its DB_UNIQUE_NAME
initialization parameter) of Database A. The broker sets up redo transport services properly through the DEPENDENCY
attribute of the LOG_ARCHIVE_DEST_
n initialization parameter.
See Also:
Oracle Data Guard Concepts and Administration for more details on redo transport dependency.You can use properties Binding
, MaxFailure
, MaxConnections, NetTimeout
, and ReopenSecs
to tune the performance of redo transport services and to set up redo transport services failure policies. These properties correspond to attributes on the LOG_ARCHIVE_DEST_
n initialization parameter.
When creating a new standby database, the broker sets these properties with the default values. When importing an existing standby database, the broker imports existing settings corresponding to these properties if the broker finds a match between the service string of a current destination and the initial connect identifier. Note that the connect identifier is supplied when you import the standby database.
For most cases, the default values or the imported values should be sufficient for normal operations. If for some reason you need to change these property values, you can use DGMGRL commands to set up these properties. Enterprise Manager does not provide an interface for modifying these properties.
See Also:
See Chapter 9 for complete information about these propertiesThe broker automatically manages the Oracle Net connections used by redo transport services to the standby databases. Redo transport services managed by the broker do not rely on any Oracle Net Services naming method.
The broker constructs the connect descriptor to the standby apply instance and uses it as the value of the SERVICE
attribute of the LOG_ARCHIVE_DEST_
n
initialization parameter. The broker uses the following information to construct the connect descriptor:
The LOCAL_LISTENER
initialization parameter of the standby apply instance
The DB_UNIQUE_NAME
initialization parameter of the standby database
The INSTANCE_NAME
parameter of the standby apply instance from V$INSTANCE
On the standby database, the broker registers a special service name db_unique_name
_XPT
with the standby listeners for redo transport services. On the primary database, the broker extracts the listener address from the LOCAL_LISTENER
initialization parameter, and uses the service name db_unique_name
_XPT
and the value of INSTANCE_NAME
to construct the connect descriptor so that redo transport services transmit archived redo log files to the standby apply instance.
Note:
If you are using a nondefault port number (for example, something other than port 1521) you need to ensure that theLOCAL_LISTENER
initialization parameter for each standby instance resolves to an address that is reachable by all members of the configuration. Also, the DB_UNIQUE_NAME
initialization parameter of the standby database matches the name that you provide when you add the standby database into the broker configuration.If your database is a RAC database, the broker sets up redo transport services in the following manner:
If the primary database is a RAC database, the broker ensures that redo transport services are identical on each of the primary database instances. Each instance has the same remote destinations, and for each remote destination, all instances are set up the same in terms of redo transport service, performance related settings, and so on. If an instance has different settings, the broker raises a health check warning on that particular instance.
If the standby database is a RAC database, only the apply instance receives log files from the primary database. If the primary database is also a RAC database, all instances of the primary database transmit log files to the same apply instance on the standby database. If the apply service is moved to a different standby instance either by the user or by the broker, the broker resets redo transport services on the primary database to transmit log files to the new apply instance. See Section 4.5 for more details.
Settings relative to redo transport services are saved in the broker configuration file as properties. When you update a log transport-related property on a standby database, the corresponding change is also made automatically by the broker to the LOG_ARCHIVE_DEST_
n initialization parameter on all of the primary database instances. If a new instance comes up on the primary database, the broker sets up redo transport services for the new instance using the log transport-related properties of all the standby databases currently being managed by the broker. After the new instance is opened for activity, all archived redo log files generated on this instance will begin to transmit to the standby databases.
See also:
Oracle Data Guard Concepts and Administration for additional information about theLOG_ARCHIVE_DEST_
n initialization parameterYou can manage Redo Apply and SQL Apply on physical and logical standby databases through the following log apply-related configurable properties:
Properties common to Redo Apply and SQL Apply
ApplyInstanceTimeout
DelayMins
PreferredApplyInstance
Properties specific to Redo Apply
ApplyParallel
Properties specific to SQL Apply
LsbyASkipTxnCfgPr
LsbyDSkipTxnCfgPr
LsbyASkipCfgPr
LsbyDSkipCfgPr
LsbyASkipErrorCfgPr
LsbyDSkipErrorCfgPr
LsbyMaxEventsRecorded
LsbyTxnConsistency
LsbyRecordSkipErrors
LsbyRecordSkipDdl
LsbyRecordAppliedDdl
LsbyMaxSga
LsbyMaxServers
You can set up log apply services so that the application of redo to the standby database is delayed. This allows the standby database to lag behind the primary database, and if a user error (for example, dropping a table) occurs during this window of time, the standby database will still contain the correct data that can be transmitted back to the primary database to repair the data.By default, a delay is not configured for a standby database. The broker applies redo data on the standby database as soon as it is received from the primary database. If the standby database has standby redo logs configured, the broker will enable real-time apply. When Redo Apply and SQL Apply apply redo in real time, the redo data is recovered directly from the standby redo log files as they are being filled. This means that the standby database does not have to wait for the log files to be archived before applying redo data from the archived redo log files. This allows the standby database to more easily stay transactionally consistent with the primary database.Use the DelayMins
property to specify the number of minutes that log apply services must wait before applying redo data to the standby database. Note that only log apply services on the standby database are delayed. Redo transport services on the primary database are not delayed, thus the primary database data is still well protected by the standby database.
Caution:
Because the broker automatically enables real-time apply on standby databases that have standby redo logs configured, Oracle recommends that you configure all databases to use Flashback Database.For Redo Apply, you can configure whether multiple parallel processes are used to apply redo data received from the primary database by using the ApplyParallel
property. Parallelism is enabled by default, which means Redo Apply automatically chooses the optimal number of parallel processes based on the number of CPUs in the system. (This is equivalent to setting the ApplyParallel
property to AUTO
.) You can disable parallelism by setting the ApplyParallel
property to NO
.
Note:
TheApplyParallel
property is not displayed on the Edit Properties page of Enterprise Manager.See Also:
Section 9.2.3, "ApplyParallel"You can control how much SGA memory is available for SQL Apply. This can be set using the LsbyMaxSga
property.
To control the number of parallel query servers used by SQL Apply, you can use the LsbyMaxServers
property.
Users can control the trade off between SQL Apply performance and transaction consistency level. For a higher transaction consistency level, SQL Apply will have a higher performance impact. To control the transaction consistency level, use the LsbyTxnConsistency
property.
Changing any property pertaining to SQL Apply will result in restarting SQL Apply if the current database state is ONLINE
(for example, SQL Apply is currently running). If the current database state is LOG-APPLY-OFF
, the property changes will take effect the next time the database state is changed to ONLINE
.
One of the benefits of a logical standby database is to allow control of what to apply and what not to apply. This is done by setting up SQL Apply filters. The granularity of the filtering ranges from a specific transaction to database objects to a particular class of SQL statement on a particular schema.
To add a SQL Apply filter in Data Guard broker, use the LsbyASkip*
properties (for example, LsbyASkipTxnCfgPr
or LsbyASkipCfgPr
). To delete a previously added SQL Apply filter, use the LsbyDSkip*
properties (for example, LsbyDSkipTxnCfgPr
or LsbyDSkipCfgPr
).
See Also:
Chapter 9 for information on these propertiesChanging these properties results in restarting SQL Apply if the current database state is ONLINE
. If the current database state is LOG-APPLY-OFF
, the property changes take effect the next time the database state is changed to ONLINE
.
You can fine-tune SQL Apply to handle apply errors on a specified set of SQL statements on particular schemas. When such SQL Apply errors are encountered, Data Guard can either skip the error to continue SQL Apply or call a specified stored procedure at the time when the error is encountered.
To add this error handling capability, use the LsbyASkipErrorCfgPr
property. To delete a previously added error handling specification, use the LsbyDSkipErrorCfgPr
property.
Changing these properties results in restarting SQL Apply if the current database state is ONLINE
. If the current database state is LOG-APPLY-OFF
, the property changes take effect the next time the database state is changed to ONLINE
.
The DBA_LOGSTDBY_EVENTS
table records important events that affect SQL Apply. Because every logical standby database might have a different interest in the set of events to be recorded in this table, Data Guard provides a means to control the event recording. From the Data Guard broker, you can use the LsbyRecord*
properties (for example, LsbyRecordSkipDdl
or LsbyRecordSkipErrors
) to control recording of a particular set of events. The value of these properties are either TRUE
or FALSE
, indicating the turning on or off of the event recording.
If the current database state is ONLINE
, changing these properties stops and restarts SQL Apply. If the current database state is LOG-APPLY-OFF
, the property changes take effect the next time the database state is changed to ONLINE
.
If a standby database is a RAC database, only one instance of the RAC database can have log apply services running at any time. This instance is called the apply instance. If the apply instance fails, the broker automatically moves log apply services to a different instance; this is called apply instance failover. In order for the apply instance to continuously apply archived redo log files received from the primary database, the broker also manages redo transport services such that the archived redo log files generated on the primary database are always transmitted to the apply instance.
If you have no preference which instance is to be the apply instance in a RAC standby database, the broker randomly picks an apply instance. If you want to select a particular instance as the apply instance, there are two methods to do this.
The first method is to pick an apply instance before there is an apply instance running in the RAC standby database. To do so, set the value of the PreferredApplyInstance
property to the name of the instance (SID) you prefer to be the apply instance. The broker starts log apply services on the instance specified by the PreferredApplyInstance
property when no apply instance is yet selected in the RAC standby database. This could be the case before you enable the standby database for the first time, or if the apply instance just failed and the broker is about to do an apply instance failover, or if the RAC database is currently the primary and you want to specify its apply instance in preparation for a switchover. Once the apply instance is selected and, as long as the apply instance is still running, the broker disregards the value of the PreferredApplyInstance
property even if you change it.
The second method is to change the apply instance when the apply instance is already selected and is running. To change the apply instance, issue the DGMGRL SET STATE
command to set the standby database state to ONLINE
, with a specific apply instance argument. The SET STATE
command will update the PreferredApplyInstance
property to the new apply instance value, and then move log apply services to the new instance. For example, use DGMGRL SHOW
command to show the available instances for the standby database, then issue the EDIT DATABASE
command to move log apply services to the new instance:
DGMGRL> SHOW DATABASE 'DR_Sales' ; Database Name: DR_Sales Role: PHYSICAL STANDBY Enabled: YES Intended State: ONLINE Instance(s): dr_sales1 (apply instance) dr_sales2 Current status for "DR_Sales": SUCCESS DGMGRL> EDIT DATABASE 'DR_Sales' SET STATE='ONLINE' WITH APPLY INSTANCE="dr_sales2'; Succeeded. DGMGRL> SHOW DATABASE 'DR_Sales' 'PreferredApplyInstance'; PreferredApplyInstance = 'dr_sales2'
DGMGRL> SHOW DATABASE 'DR_Sales' ; Database Name: DR_Sales Role: PHYSICAL STANDBY Enabled: YES Intended State: ONLINE Instance(s): dr_sales1 dr_sales2 (apply instance) Current status for "DR_Sales": SUCCESS
Ensure that the new apply instance is running when the command is issued. Otherwise, the apply instance remains the same.
Once the apply instance is selected, the broker keeps apply instance information in the broker configuration file so that even if the standby database is shut down and restarted, the broker still selects the same instance to start log apply services. The apply instance remains unchanged until changed by the user or it fails for any reason and the broker decides to do an apply instance failover.
When the apply instance fails, not only do log apply services stop applying log files to the standby database, but redo transport services stop transmitting redo data to the standby database because the apply instance is not available to receive and store archived redo log files locally to the standby database. To tolerate a failure of the apply instance, the broker leverages the availability of the RAC standby database by automatically failing over log apply services to a different standby instance. The apply instance failover capability provided by the broker enhances data protection.
To set up apply instance failover, set the ApplyInstanceTimeout
property to specify the time period that the broker will wait after detecting an apply instance failure and before initiating an apply instance failover. To select an appropriate timeout value, you need to consider:
If there is another mechanism in the cluster that will try to recover the failed apply instance.
How long the primary database can tolerate not transmitting redo data to the standby database.
The overhead associated with moving the log apply services to a different instance. The overhead may include retransmitting, from the primary database, all log files accumulated on the failed apply instance that have not been applied if those log files are not saved in a shared file system that can be accessed from other standby instances.
The broker default value of the ApplyInstanceTimeout
property is 0 seconds, indicating that apply instance failover should occur immediately upon detection of the failure of the current apply instance.
After the broker initiates an apply instance failover, the broker selects a new apply instance according to the following rule: if the PreferredApplyInstance
property indicates an instance that is currently running, select it as the new apply instance; else pick a random instance that is currently running to be the new apply instance. The broker also resets redo transport services so that the primary database starts transmitting redo data to the new apply instance.
The broker can simplify the process of setting up your configuration for any of the different grades of data protection: maximum protection, maximum availability, or maximum performance.
This section contains the following topics to help you configure the proper protection for your configuration:
Section 4.6.1, "Setting the Protection Mode for Your Configuration"
Section 4.6.2, "How the Protection Modes Influence Broker Operations"
To set the protection mode, perform the following steps:
Step 1 Determine which data protection mode you want to use.
Each data protection mode provides a different balance of data protection, data availability, and database performance. To select the data protection mode that meets the needs of your business, carefully consider your data protection requirements and the performance expectations of your users.
Maximum Protection This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database shuts down if a fault prevents it from writing its redo stream to at least one remote standby redo log. This protection mode is not used if you enable fast-start failover.
Maximum Availability This protection mode provides the highest level of data protection that is possible without compromising the availability of the primary database. Like maximum protection mode, a transaction will not commit until the redo needed to recover that transaction is written to the local online redo log and to at least one remote standby redo log. Unlike maximum protection mode, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. Instead, the primary database operates in maximum performance mode until the fault is corrected, and all gaps in redo log files are resolved. When all gaps are resolved, the primary database automatically resumes operating in maximum availability mode.
This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.
This protection mode is required if you enable fast-start failover.
Maximum Performance This protection mode is the default mode, and provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover that transaction is written to the local online redo log. The primary database's redo data stream is also written to at least one standby database, but the redo stream is written asynchronously with respect to the commitment of the transactions that create the redo data. This protection mode is not used if you enable fast-start failover.
When network links with sufficient bandwidth are used, this mode provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance.
The maximum protection and maximum availability modes require that a standby redo log is configured on at least one standby database in the configuration. All three protection modes require that specific log transport attributes be specified on the LOG_ARCHIVE_DEST_
n initialization parameter to send redo data to at least one standby database.
See Also:
Oracle Data Guard Concepts and Administration for complete information about the data protection modesStep 2 Set up standby redo log files.
If you are configuring the maximum protection or maximum availability level of protection, you must add standby redo log files on all standby databases. Also, Oracle recommends you add standby redo log files on the primary database in preparation for a future switchover or failover. Standby redo log files are required on the primary database if you want the broker to automatically reinstate the old (failed) primary database after a fast-start failover. (See Section 5.5.7 for more information about reinstatement.)
Enterprise Manager automatically prompts you to select one or more standby databases in the configuration and sets up standby redo log (SRL) files on them and on the primary database in preparation for a future switchover.
See Also:
If you are using the DGMGRL command-line interface, follow the instructions in Oracle Data Guard Concepts and Administration to configure standby redo log files. Also, see Section 6.5.3 for additional information about changing the database protection mode.Step 3 Set the LogXptMode property, if necessary.
If the data protection mode requires that you change the redo transport service used by any of the standby databases, change the setting of the LogXptMode
database property appropriately on each standby database. See Section 4.4 for more information about setting the redo transport service. Table 4-2 shows the protection modes and the corresponding redo transport service.
Enterprise Manager automatically specifies the correct redo transport service on the primary database in preparation for a future switchover. See Section 6.5.3 for additional information about changing the database protection mode.
Table 4-2 Data Guard Protection Modes and Requirements
Protection Mode | Redo Transport | Standby Redo Log Files Needed? | Used with Fast-Start Failover? |
---|---|---|---|
|
|
Yes |
No |
|
|
Yes |
Yes |
|
|
Yes for |
No |
Step 4 Set the protection mode.
Set the protection mode using DGMGRL commands or Enterprise Manager.
With DGMGRL:
If you plan to set the protection mode to either the MAXPROTECTION
or MAXAVAILABILITY
, ensure that standby redo log files are configured on the standby database. Do this also for the primary database or another standby database in the configuration to ensure that it can support the chosen protection mode after a switchover.
Use the EDIT DATABASE (property)
command and specify the standby database whose redo transport service should be changed to correspond to the protection mode you plan to set. For example, if you plan to set the overall Data Guard configuration to the MAXAVAILABILITY
mode, you must use the EDIT DATABASE
command to set the SYNC
mode for redo transport services. For example:
DGMGRL> EDIT DATABASE 'DR_Sales' SET PROPERTY 'LogXptMode'='SYNC';
Do this also for the primary database or another standby database in the configuration to ensure that it can support the chosen protection mode after a switchover.
Use the EDIT CONFIGURATION SET PROTECTION MODE AS
protection-mode
command to set the overall configuration protection mode. For example:
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
See Section 7.5 for a DGMGRL scenario showing how to set the protection mode.
With Enterprise Manager:
On the Data Guard overview page, click the link to the right of the Protection Mode label.
Select Maximum Protection, Maximum Availability, or Maximum Performance and click Continue.
If prompted, log in to the database with SYSDBA
privileges and click Login.
Select one or more standby databases to support the protection mode that you selected. If standby redo log files are needed, verify the names of the log files. Click OK.
On the Confirmation page, click Yes.
See Section 6.5.3 for a scenario showing how to set the protection mode.
After you upgrade the protection mode using either DGMGRL or Enterprise Manager, the primary database will be restarted automatically. The primary database need not be restarted following a downgrade of the protection mode.
This section describes how operations such as switchover, failover, disabling, or enabling the Data Guard configuration can have an effect on the configuration's protection mode and redo transport services. This section contains the following sections:
When you upgrade the current Data Guard protection mode (for example, to upgrade from the maximum performance mode to the maximum availability mode), the broker shuts down and restarts the primary database. When you downgrade the current Data Guard protection mode, the database does not need to be restarted. Follow these recommendations when upgrading or downgrading the Data Guard protection mode:
When upgrading the protection mode, upgrade the redo transport service before you upgrade the overall protection mode. (Enterprise Manager does this for you. See Section 6.5.3 for information.) At the time when you change the protection mode or reset the redo transport service of a standby database, the broker verifies that there is at least one standby database in the configuration that can support the requested grade of protection. If not, then the broker does not change the protection mode and returns an error.
When downgrading the protection mode, downgrade the protection mode first and then change the redo transport service (if necessary). The broker will not allow changing the redo transport service if doing so invalidates the current overall protection mode.
If you upgrade the protection mode from the maximum performance mode, the broker ensures that there is at least one standby database using standby redo log files, and whose redo transport service is set to SYNC
. Additionally, for upgrades to MAXPROTECTION
, the broker ensures there are no gaps in log files on the standby database. If there are no standby databases in the configuration that meet these requirements, the request to upgrade the protection mode is rejected with an error.
The protection mode cannot be changed if fast-start failover is enabled.
WARNING:
The primary database will be shut down and restarted if you upgrade the protection mode.
A switchover does not change the overall Data Guard protection mode. The protection mode remains the same as it was before the switchover.
This requires that there be a standby database that is properly configured to support the current protection mode once the switchover completes. This can be either another standby database in the configuration or the current primary database that will become a standby database after the switchover completes.
Before you invoke a switchover, if necessary, you can add standby redo log files and set the redo transport service on the current primary database, or on another standby database in the configuration, to the SYNC
, ASYNC
, or ARCH
mode that is required to support the Data Guard protection mode. Then, when the switchover begins:
The broker verifies the presence of standby redo log files and the redo transport service setting on each standby database and on the current primary database.
The broker verifies there are no gaps if the protection mode is set to MAXPROTECTION
.
If the verification is successful, the switchover continues; otherwise, the switchover fails, and the database roles and the broker configuration files remain unchanged.
WARNING:
The broker will shut down and restart the primary database if the target of the switchover is a physical standby database.
See Also:
Section 5.3 for more information about switchoversAfter you perform a manual failover, the Data Guard protection mode is downgraded to maximum performance mode if the protection mode was at maximum protection. You can upgrade the protection mode later, if necessary. If the protection mode was at maximum availability, it remains at maximum availability. The redo transport services of the standby databases remain unchanged.
If fast-start failover occurs, the broker preserves the protection mode at the same maximum availability level in which it was operating before the failover.
See Also:
Section 5.4 and Section 5.5 for more information about manual failover and fast-start failover, respectivelyWhen you disable broker management of a standby database, the broker checks to see if the overall protection mode can still be satisfied by any of the remaining standby databases. If not, the broker rejects the disable operation. Otherwise, the broker allows the disable operation to proceed as long as fast-start failover is not enabled. If it is enabled, the broker allows the disable operation to proceed only if the standby database is not the target standby database for fast-start failovers.
Caution:
If you disable broker management of a standby database in the broker configuration, that standby database cannot be used by the broker as a failover target in the event of loss of the primary database.
After a standby database is successfully disabled, you can change the redo transport service for that database and the broker will record the change in the broker configuration file. The change will not affect the overall protection mode because it is guaranteed that at least one of the enabled standby databases already satisfies the overall protection mode requirement.
As long as fast-start failover is not enabled, you can disable the entire configuration regardless of the protection mode. This is because you may want to use the broker only to set up a Data Guard configuration, and then disable it from the broker's control and use other interfaces (for example, using SQL*Plus and SQL statements) for management. You cannot disable the configuration if fast-start failover is enabled. See Section 5.5.2.2, "Restrictions When Fast-Start Failover is Enabled" for more information.
If the entire configuration is disabled, you can change any broker settings, including the redo transport services of the standby databases and the protection mode of the configuration. The broker saves the changes in the broker configuration file, but the changes will not be made to the database itself.
When enabling broker management of the entire configuration, the broker first checks to see if the protection mode will be satisfied by the redo transport services of the standby databases that will be enabled. If not, the enable operation fails and the configuration remains disabled. Otherwise, the enable operation successfully enables the configuration, and the broker enables the database using the settings saved in the broker configuration file.
When removing a standby database from the broker configuration, the broker checks to see if the protection mode will still be satisfied. The broker quits the operation if:
Removing the database compromises the protection mode
Fast-start failover is enabled and you try to remove the standby database that is the target of the fast-start failover
If you want to remove the entire configuration, the broker always allows the operation.
Some operations that take place in a broker configuration, especially operations related to redo transport services, can affect the overall protection mode. These operations include:
Setting the standby database to the offline state
Stopping redo transport services on the primary database
Stopping redo transport services to individual standby databases
Before any of these operations can proceed, the broker checks to see if the protection mode will be supported by the redo transport service settings on the standby databases after the operation completes. If not, the broker quits the operation and returns an error.
Database status reveals the health of the database. In general, the broker checks the health of a database by verifying if the actual database state and settings match with those described in the broker configuration file. This is done by checking if any component of the Data Guard configuration is functioning incorrectly (for example, if redo transport services have an error), and by checking if other required database settings are correctly set (for example, if the server parameter files are available and if the ARCHIVELOG
mode is turned on). The following is a detailed list of what is being checked by the broker on a primary database and a standby database.
On a primary database, the health check determines whether the following conditions are met:
Database is in the state specified by the user, as recorded in the broker configuration file.
Database is in the correct data protection mode.
Database is using a server parameter file.
Database is in the ARCHIVELOG
mode.
Database guard is turned off.
Supplemental logging is turned on when there is a logical standby database in the configuration.
Redo transport services do not have any errors.
Database settings match those specified by the broker configurable properties.
Redo transport settings match those specified by the redo transport-related properties of the standby databases.
On a standby database, the health check determines whether the following conditions are met:
Database is in the state specified by the user, as recorded in the broker configuration file.
Database is using a server parameter file.
Database settings match those specified by the broker configurable properties.
Database guard is turned on when the database is a logical standby database.
Primary and target standby databases are synchronized if fast-start failover is enabled.
The following monitorable properties can be used to query the database status:
StatusReport
LogXptStatus
InconsistentProperties
InconsistentLogXptProps
Note:
These properties are directly accessed through the DGMGRL command-line interface. Enterprise Manager rearranges the values of these properties for presentation in the GUI.The StatusReport
property provides a list of all health check problems the broker detected during a health check. This is usually the first property you use to check the database status. In the following example, you see three items reported by the StatusReport
property.
DGMGRL> SHOW DATABASE 'North_Sales' 'StatusReport'; STATUS REPORT INSTANCE_NAME SEVERITY ERROR_TEXT sales1 ERROR ORA-16737: the redo transport service for standby "North_Sales" has an error sales2 ERROR ORA-16737: the redo transport service for standby "North_Sales" has an error sales2 WARNING ORA-16715: log transport related property MaxFailure of standby "North_Sales" is inconsistent
To further check the details about the database status, you can use the LogXptStatus
, InconsistentProperties
, and InconsistentLogXptProps
properties. LogXptStatus
lists all log transport errors detected on all instances of the primary database. InconsistentProperties
lists all properties that have inconsistent values between the broker configuration file and the database settings. InconsistentLogXptProps
lists all redo transport-related properties of standby databases that have inconsistent values between the broker configuration file and the redo transport settings. For example, the output of StatusReport
(in the previous example) shows two problems: some redo transport services errors and an inconsistent redo transport-related property.
Issue the following SHOW DATABASE
commands to obtain further details about the problems.
DGMGRL> SHOW DATABASE 'North_Sales' 'LogXptStatus'; REDO TRANSPORT STATUS PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS sales1 DR_Sales ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor sales2 DR_Sales ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor DGMGRL> SHOW DATABASE 'North_Sales' 'InconsistentLogXptProps'; INCONSISTENT REDO TRANSPORT PROPERTIES INSTANCE_NAME STANDBY_NAME PROPERTY_NAME MEMORY_VALUE BROKER_VALUE sales2 DR_Sales MaxFailure 9 0
See Also:
Chapter 9 for detailed information about database properties