4
Managing Database Resources

This chapter describes managing the states and properties that are specific to the database resource object in the following sections:

• Section 4.1, "Database Resources"
• Section 4.2, "Database Resource States"
• Section 4.3, "Database Resource Properties"

4.1 Database Resources

A database resource object is at the lowest level in the hierarchy of objects managed by the broker. A database resource object corresponds to a primary or standby database instance. The broker uses this object to manage and monitor the state of a single database.

The broker can distinguish between a physical and a logical standby database, and configures a physical standby site object and database resource object in a broker configuration, or a logical standby site and database resource object. These logical objects are configured with states, properties, and dependency relationships that are appropriate for their standby types.

4.2 Database Resource States

The state of a database resource is dependent upon the state of the site on which the resource resides. For example, if a site is in an offline state, the database that is dependent on the site must also be in an offline state.

When a site is in an online state and enabled, its database resource object can be in either an offline or an online state.

4.2.1 Offline State

When you first create and enable a configuration with the broker, the default state for database resources is online. Before setting the state to offline, you should carefully consider whether or not the interruption in access to data and the computing resources is necessary. The following list describes the actions the broker takes when you set a database resource to the offline state:

When you set the state of a primary or standby (logical or physical) database to offline, the broker automatically shuts down the database first and then restarts it (nomount).

• While the primary database is in the offline state, the database instance is started (nomount) and log transport services are stopped (archive redo logs are not being shipped to any standby databases).
• While the physical or logical standby database is in the offline state, the database instance is started, nomount and log apply services do not apply archived redo logs to the standby database. Furthermore, the standby site is incapable of receiving archived redo logs.

4.2.2 Online State and Substates

When the broker first starts a database resource in the online state, the database resource is started in one of several substates. For example:

• For a primary database, the default online state is to start the database resource and open it in read/write mode with log transport services started (archiving redo logs to the standby database).
• For a physical standby database, the database resource is started and mounted, with log apply services started and applying archived redo logs to the standby database.
• For a logical standby database, the database resource is started and opened in read/write mode, but the replicated tables in the database are open read-only to queries. The database guard is turned on and log apply services are started and applying archived redo logs to the standby database.

Table 4-1 describes all of the primary and standby database resource online states and substates. The first two columns of the table show the substate name if you are using Data Guard Manager and the corresponding name if you are using the CLI.

Table 4-1  Database Substate Names and Descriptions

Substate Name in CLI	Substate Name in Data Guard Manager	Description
LOGICAL-APPLY-READY	Apply Off	The logical standby database is open for read-only queries, but log apply services are not running. The logical standby database guard is on.
LOGICAL-APPLY-ON	Online	The logical standby database is open for read-only queries and log apply services are started. The logical standby database guard is on. This is the default state for a logical standby database.
PHYSICAL-APPLY-READY	Apply Off	The physical standby database is mounted, but log apply services are stopped. The standby database is not open for read-only queries.
PHYSICAL-APPLY-ON	Online	The physical standby database is mounted and log apply services are started. The standby database is not open for read-only queries. This is the default state for a physical standby database.
READ-ONLY	Read-only	The physical standby database is open for read-only queries, but log apply services are stopped. This substate is not applicable to logical standby databases.
READ-WRITE	Transport Off	The primary database is open for read/write access, but log transport services are not shipping logs to the standby databases.
READ-WRITE-XPTON	Online	The primary database is open for read/write access and log transport services are archiving redo logs to the standby databases. This is the default state for a primary database.

4.2.3 Database State Transitions

Figure 4-1 graphically shows the online states and substates that were described in Table 4-1. The double arrows indicate that you can transition from one state to any other state.

Figure 4-1 Database State Transition Diagrams

Text description of the illustration transitn.gif

Figure 4-1 shows that the primary and standby databases can be in an offline state or, when online, can be in one of several substates:

• Primary database online substates:

  READ-WRITE

  READ-WRITE-XPTON

  For a primary database, the transition from any online state to an offline state includes shutting down and restarting the primary database in nomount mode.

  • Logical standby database online substates:

    LOGICAL-APPLY-READY

    LOGICAL-APPLY-ON

    When a logical standby database is taken offline, it is shut down and restarted as is done for the primary database.

    • Physical standby database online substates:

      PHYSICAL-APPLY-READY

      PHYSICAL-APPLY-ON

      READ-ONLY

      When a physical standby database is taken offline, it is shut down and restarted as is done for the primary database.

      Note: Before you can transition a physical standby database from a READ-ONLY state to any other state, you must first close all of the open sessions to the standby database. If some of the sessions are not closed, the state transition will fail.

    The physical standby database cannot have any user sessions connected when changing from the read-only state to any other state. The state change will fail and return the ORA-16727 error corresponding to the ORA-1093 error. You can resolve this error by performing the following steps:

    1. Check the alert log for additional errors.

       If you see the ORA-1093 message in the alert log, it is likely that there are additional user sessions connected to the standby database that is in a read-only state.

    2. Query V$SESSION view (specify the SELECT OSUSER, PROGRAM, TYPE statement) to see all active sessions.
    3. If you see any sessions in the V$SESSION view other than your current broker (Data Guard Manager or command-line interface) session, ask the read-only users to disconnect before you perform the state transition.

    With the CLI, you can use the ALTER RESOURCE command to explicitly change the state of a database resource. For example, the ALTER RESOURCE command in the following example changes the state of the Sales_db resource to read/write.

    DGMGRL> ALTER RESOURCE 'Sales_db' ON SITE 'Boston' SET STATE='read-write';
    Succeeded.
    

    See Also: Chapter 7 for complete information about the ALTER RESOURCE command. See Chapter 5 for examples of performing state transitions using Data Guard Manager.

    4.3 Database Resource Properties

    The broker manages database initialization parameter values as database resource properties and stores them in the Data Guard configuration file.

    There are two types of properties: configurable and monitorable:

    • Monitorable property values can only be viewed.
    • Configurable property values can be viewed and dynamically updated.

    4.3.1 Monitorable (Read-Only) Properties

    Monitorable properties allow you to view information related to database resources, but you cannot change the value of these properties. These properties can be very helpful when you are trying to diagnose problems in the broker configuration. For example, view the InconsistentLogXptProps property to determine where there is a discrepancy for log transport services properties whose values are inconsistent between the Data Guard configuration file and the actual value currently used by the database. You can view all of the monitorable properties using CLI SHOW commands. Data Guard Manager displays the information obtained from these properties on the Properties page.

    The following monitorable properties can be seen only when you are using the CLI and the database resource is enabled:

    InconsistentLogXptProps	(Inconsistent Log Transport Properties)
    InconsistentProperties	(Inconsistent Database Properties)

    The following monitorable properties can be seen only when the database resource is enabled and in an online state in the CLI, except for the LsbySkipTable and the LsbySkipTxnTable properties, which also can be seen in Data Guard Manager:

    LogXptStatus	(Log Transport Status)
    LsbyFailedTxnInfo	(Logical Standby Failed Transaction Information)
    LsbyParameters	(Logical Standby Parameters)
    LsbySkipTable	(Logical Standby Skip Table)
    LsbySkipTxnTable	(Logical Standby Skip Transaction Table)
    SbyLogQueue	(Standby Log Queue)
    SendQEntries	(Send Queue Entries)

    See Also: Chapter 8 for more information about the database resource monitorable properties.

    4.3.2 Configurable (Changeable) Database Resource Properties

    Configurable properties affect the operation or configuration of the database resource objects. When you use the CLI or Data Guard Manager to create a primary site and import existing standby sites into a new broker configuration, the property values are imported from the database settings. Later, you can update many property values when the database is either disabled or enabled.

    The following configurable properties are listed according to whether the property controls initialization parameters, log transport services, or log apply services.

    • Properties that control initialization parameters:

      ArchiveLagTarget

      DbFileNameConvert

      LogArchiveFormat

      LogArchiveMaxProcesses

      LogArchiveMinSucceedDest

      LogArchiveTrace

      LogFileNameConvert

      StandbyArchiveDest

      StandbyFileManagement

    • Properties that control log transport services:

      Alternate

      AsyncBlocks

      Binding

      DelayMins

      Dependency

      LogShipping

      LogXptMode

      MaxFailure

      ReopenSecs

      See Also: Section 4.3.2.4 for information about controlling the log transport services protection mode with the LogXptMode property

    • Properties that control log apply services:

      The following properties control log apply services for physical standby databases:

      ApplyNext

      ApplyNoDelay

      ApplyParallel

      The following properties control SQL apply services for logical standby databases:

      LsbyASkipCfgPr

      LsbyDSkipCfgPr

      LsbyASkipErrorCfgPr

      LsbyDSkipErrorCfgPr

      LsbyASkipTxnCfgPr

      LsbyDSkipTxnCfgPr

      LsbyMaxEventsRecorded

      LsbyMaxServers

      LsbyMaxSga

      LsbyRecordAppliedDdl

      LsbyRecordSkipDdl

      LsbyRecordSkipErrors

      LsbyTxnConsistency

      Note: Data Guard Manager uses the LsbySkipTable property to represent the LsbyASkipCfgPr, LsbyDSkipCfgPr, LsbyASkipErrorCfgPr, and LsbyDSkipErrorCfgPr properties. It uses the LsbySkipTxnTable property to represent the LsbyASkipTxnCfgPr and LsbyDSkipTxnCfgPr properties.

      See Also: Chapter 8 for reference information for all of the database resource properties

    4.3.2.1 Verifying and Updating Properties

    When the configuration is enabled, the broker keeps the database property values in the Data Guard configuration file consistent with the values being used in the database. For initialization parameter-related properties, the broker maintains the consistency between the value in the Data Guard configuration file, the current database value, and the initialization parameter value in the server parameter file (SPFILE), 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 Data Guard configuration file, and in the SPFILE.
    • 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 Data Guard configuration file and in the SPFILE. Typically, the database value becomes the same as the SPFILE value and the Data Guard 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 SPFILE and the in-memory settings the next time you enable the broker configuration.

    Note: Although you can change a property whether the configuration is enabled or disabled, the change does not take effect on the database unless the configuration is enabled.

    4.3.2.2 Default Property Values

    If you do not explicitly set property values, the broker uses a default value for the database properties. For example, if you created a new standby database without specifying a log transport mode, the broker sets the LogXptMode property to ARCH, for physical standby databases and to ASYNC for logical standby databases.

    4.3.2.3 Preparing for Switchover Operations

    All of the properties are present for all of the databases that are a part of a broker configuration--the values for individual databases can be different. Some subset of properties and associated values are used only when the database is a standby database, while others are used when the database is a primary database.

    Note: If you set up the database resource properties for the standby role on the primary database in preparation for it becoming a standby database, the broker cannot verify these properties, thus causing potential problems after the switchover operation completes. For example, if you do not set the StandbyArchiveDest resource property on the primary database prior to the switchover operation, this may cause a problem that only becomes apparent after the switchover operation is complete.

    Therefore, prior to performing a switchover operation, consider setting properties on the standby database to prepare it for a future transition to the primary role. Similarly, you should set properties on the primary database to prepare it for a future transition to the standby role.

    If you set properties related to log transport services, such as the Alternate, AsyncBlocks, Binding, DelayMins, Dependency, LogShipping, LogXptMode, MaxFailure, or ReopenSecs properties on a standby database, the values that you set will persist through role changes during switchover or failover operations. Also:

    • If you set these properties on a standby database, the actual database values are set on the primary database through the LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n initialization parameters.
    • No matter which site becomes the primary site after a switchover or failover operation, these properties of a standby are always used to set log transport of the current primary to this standby.

    4.3.2.4 Configuring Log Transport Services for Data Protection

    Section 2.9 described how the broker handles data protection modes. As a part of the overall configuration protection mode, you must ensure that the log transport services are also properly set up for the data protection mode that you have chosen.

    You use the LogXptMode property to set the SYNC, ASYNC, or ARCH mode for log transport services. Table 4-2 shows the protection modes, the corresponding log transport mode, and the SQL statement that corresponds to the protection mode that you have chosen.

    Table 4-2  Data Guard Protection Modes and Log Transport modes

    Protection Mode	Log Transport Services	Equivalent to . . . .
    MAXPROTECTIONFoot 1	SYNC	ALTER DATABASE SET STANDBY TO MAXIMIZE PROTECTION
    MAXAVAILABILITY	SYNC	ALTER DATABASE SET STANDBY TO MAXIMIZE AVAILABILITY
    MAXPERFORMANCE	ASYNC or ARCH	ALTER DATABASE SET STANDBY TO MAXIMIZE PERFORMANCE

    1 The MAXPROTECTION mode requires physical standby database and standby redo logs. This mode is not supported by logical standby databases.

    
    
    

    The values for the LogXptMode property are described in the following list:

    SYNC

    Configures the log transport services for this standby database using the LGWR, SYNC, and AFFIRM settings. If this is a physical standby database, standby redo logs are required. If this is a logical standby database, standby redo logs are not required because logical standby databases do not use them. This mode is required for the maximum protection or maximum availability data protection modes. This log transport mode provides the highest grade of data protection to the primary database, but also incurs the highest performance impact.

    ASYNC

    Configures the log transport services for this standby database using the LGWR, ASYNC, and NOAFFIRM settings. If this is a physical standby database, standby redo logs are required. If this is a logical standby database, standby redo logs are not required because logical standby databases do not use them. This log transport mode provides a moderate grade of data protection to the primary database, and low performance impact.

    ARCH

    Configures the log transport services for this standby database using the ARCH setting. Standby redo logs are not required. This log transport mode provides the lowest grade of data protection to the primary database, and the lowest performance impact.

    Note: If the data protection mode that you choose requires that one or more physical standby databases use the SYNC or ASYNC log transport mode, you may need to add standby redo logs to one or more of the standby sites. Data Guard Manager provides the Standby Redo Log Assistant that automatically sets up standby redo logs on one or more physical standby databases in your configuration, and on the primary database in preparation for a switchover operation