3
Database Resources

Chapter 2 introduced the database resource states and properties. This chapter describes using the database resource object in the following sections:

• Section 3.1, "Database Resources"

• Section 3.2, "Database Resource States"

• Section 3.3, "Database Resource Properties"

3.1 Database Resources

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

To predictably and consistently manage database resources, the DMON process uses a resource guard, that is preconfigured to monitor the database and manage its state and property transitions. When you use Data Guard Manager or the Data Guard command-line interface (CLI) to make a state change or property change, the request is passed through the DMON process to its built-in resource guard that then services the requests according to a predefined set of functions. It is the resource guard that ensures the correct and consistent functioning of the database resource.

3.2 Database Resource States

The state of a database resource is dependent upon the state of the site on which the resource resides, and the state of the site is dependent upon that of the configuration. Thus, a site is the parent for a database resource and the configuration is the parent for a site.

This is important because when a parent is offline, its dependents must also be offline. 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. Similarly, if the configuration is offline, all of the sites and resources in the configuration are also offline because all are logically dependent on the configuration object.

3.2.1 Database Online and Offline States

When enabled, a database resource object can be in an online or an offline state.

• Offline--The database instance is started and not mounted:
  • For a primary database, the database instance is closed and not mounted and log transport services do not archive redo logs to the standby database.

  • For a standby database, the database instance is started, nomount and log apply services do not apply redo logs to the standby database.

    Note: When the primary database is brought to an offline state from an online state, the database is automatically shut down first and then restarted (nomount). When the standby database is brought to an offline state from an online state, the standby database is put directly into a started, nomount mode; the standby database is not shut down and restarted.

• Online--The database instance is started and in its default online state:
  • For a primary database, the default is to start the database instance and open it in read/write mode with log transport services started (archiving redo logs to the standby database). The default online state for the primary database is referred to as the READ-WRITE-XPTON substate in the CLI and it is referred to as the online selection for the primary database in Data Guard Manager.

  • For a standby database, the database instance is started and mounted. Log apply services apply archived redo logs to the standby database. The default online state for the standby database is referred to as the PHYSICAL-APPLY-ON substate in the CLI and it is referred to as the online selection for the standby database in Data Guard Manager.

  See Table 3-1 for all of the online substates for the primary and standby database resources.

3.2.2 Database Substates

Section 2.5 provided some background information about configuration, site, and database states and substates. This section provides more detail about the database substate, which is related to the role (primary or standby) in which the site is currently running.

When a database is in an online state, the database state is further qualified to be in one of several substates. For example, when a primary database is in an online state, it might be started and open in read/write mode, or it might be started and mounted in read/write mode with the log transport services archiving redo logs to the standby site.

Table 3-1 describes all of the primary and standby database resource 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 3-1  Database Substate Names and Descriptions

Substate Name in Data Guard Manager	Substate Name in CLI	Description	Applies to
Standby paused	PHYSICAL-APPLY-READY	The standby database is mounted, but log apply services are not running. The standby database is not open for read-only queries.	Standby database
Standby online	PHYSICAL-APPLY-ON	The standby database is mounted and log apply services are started. This is the default state for the standby database.	Standby database
Standby paused, read-only mode	READ-ONLY	The standby database is open for read-only queries, but log apply services are not started.	Standby database
Primary paused	READ-WRITE	The primary database is available for updates, but log transport services are not shipping logs to the standby database.	Primary database
Primary online	READ-WRITE-XPTON	The primary database is available for updates and log transport services are archiving redo logs to the standby site. This is the default state for the primary database.	Primary database

Table 3-2 summarizes all of the possible variations of the database online substates and the implication of each one on the primary and standby databases, and on log transport services and log apply services.

Table 3-2  Effect of Database Resource States and Substates on Underlying Components

Component	Offline	Paused	Online	Physical Apply Ready	Physical Apply On	Read- Only	Read- Write	Read-Write XPTON
Primary Database	Started, nomount	Started and opened in read/write mode	Started and opened in read/write mode	Not applicable	Not applicable	Not applicable	Started and opened in read/ write mode	Started and opened in read/write mode
Log transport services	Stopped	Stopped	Started	Not applicable	Not applicable	Not applicable	Stopped	Started
Standby database	Started, nomount	Started and mounted	Started and mounted	Started and mounted	Started and mounted	Open for read-only queries	Not applicable	Not applicable
Log apply services	Stopped	Stopped	Started	Stopped	Started	Stopped	Not applicable	Not applicable

3.2.3 Database State Transitions

Figure 3-1 graphically shows the database states that were described in Table 3-1, and indicates that you can transition from one state to any other state.

Figure 3-1 Database State Transition Diagrams

Text description of the illustration transitn.gif

Figure 3-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
  

  Note: 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.

  Standby database online substates:

  PHYSICAL-APPLY-READY

  PHYSICAL-APPLY-ON

  READ-ONLY

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

  Note: Before you can transition a 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.

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 Chapter 6 for complete information about the ALTER RESOURCE command. See also Chapter 4 for information about performing state transitions using Data Guard Manager.

3.3 Database Resource Properties

There are two types of properties--configurable and monitorable:

• Configurable property values can be viewed and dynamically updated.

• Monitorable property values can only be viewed.

3.3.1 Configurable (Changeable) Database Resource Properties

Configurable properties affect the operation or configuration of the resource objects. You can change the value of these properties using the CLI or Data Guard Manager.

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

Table 3-3 describes the database resource configurable properties.

Table 3-3  Configurable Properties for Database Resources

Name	Value	Type	Critical or Noncritical	Set On	Description
ArchiveDestDependency
	Site name	String	Critical	Standby	Specifies the dependency attribute for the LOG_ARCHIVE_DEST_n parameter. The site name (can be the primary or a standby site name) on which this site depends for receiving archived redo logs. If this is not set on the standby database, then log transport services will not be enabled. You can set this critical property to null. For example, consider one primary site (Boston) and one standby site (San Jose). San Jose depends on Boston for its archived redo logs. Then, you update the property ArchiveDestDependency of the San Jose database resource to value Boston.
ArchiveLagTarget
	Seconds	Integer	Noncritical	Primary	Updates the ARCHIVE_LAG_TARGET=seconds initialization parameter setting. This property limits the amount of data that can be lost and effectively increases the availability of the standby database by forcing a log switch after the amount of time you specify (in seconds) elapses. That way, the standby database will not miss redo records generated from a time range longer than a value of ARCHIVE_LAG_TARGET.
LogArchiveDestOptions
	Attributes for the log_archive_dest_n initialization parameter, except for the location, service, and dependency attributes.	String	Critical	Standby	Specifies the log_archive_dest_n parameter attributes (for example, "optional, reopen=30"). You cannot specify the service and location attributes, because they are maintained by the broker. Use the ArchiveDestDependency property to configure the dependency attribute. If this is not set on the standby database, then log transport services will not be enabled. You can set this critical property to null.
LogArchiveTrace
	Any valid integer value Foot 1	Integer	Noncritical	Primary and standby	Updates the LOG_ARCHIVE_TRACE=integer initialization parameter. Set this parameter to an integer value to see the progression of the archiving of redo logs to the standby site. Oracle writes an audit trail of the archived logs received from the primary database into a trace file.
FalServer
	Oracle Net service name	String	Noncritical	Standby	Updates the FAL_SERVER=Oracle_net_service_name initialization parameter setting. Set this on the standby site to assign the FAL (Fetch Archive Log) server for the standby database. Note: If you never change the FALSERVER property and the current FAL_SERVER parameter for the standby database is empty, then the database resource guard automatically sets the value to the net service name for the primary site.
FalClient
	Oracle Net service name	String	Noncritical	Standby	Updates the FAL_CLIENT=oracle_net_service_name initialization parameter setting, which is the Oracle Net service name used by the primary database to refer to the standby database. The FalClient property assigns the FAL (Fetch Archive Log) client name used by the FAL server to refer to the FAL client. Note: If you never change the FalClient property and the current FAL_CLIENT parameter for the standby database is empty, then the database resource guard automatically sets the value to the net service name for the standby site.
StandbyArchiveDest
	File specification	String	Critical	Standby	Updates the file specification for the STANDBY_ARCHIVE_DEST=filespec initialization parameter. Specifies the location of archived redo logs arriving from a primary database. This property is relevant only for a standby database in managed recovery mode. You can set this critical property to null.
StandbyFileManagement
	AUTO or MANUAL	String	Noncritical	Standby	Updates the STANDBY_FILE_MANAGEMENT=[AUTO | MANUAL] initialization parameter setting. Set this property on standby sites to indicate whether or not the filenames on the standby database are the same as those on the primary database. Set to AUTO only if the COMPATIBILITY parameter is set to 9.0.n.

1 See the Oracle9i Database Reference for a list of valid values to the LOG_ARCHIVE_TRACE initialization parameter.

Note the following about the configurable properties in Table 3-3:

• Most of the configurable properties map to parameter values that affect log transport services.

• 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.

• You can alter the database resource properties as appropriate for your specific Data Guard environment. However, the value for the changed parameter is not changed in the initialization parameter file.

  Note: If the database is shut down and restarted outside of Data Guard Manager, the database will revert to the original values that were set in the initialization file and the properties maintained by Data Guard will be inconsistent with the actual database values.

• Configurable properties can be either noncritical or critical, as described in Section 3.3.1.1 and Section 3.3.1.2.

3.3.1.1 Noncritical Database Properties

Noncritical database properties do not need to be set for log transport services and log apply services to function properly. If a noncritical property is not set, then the Data Guard monitor uses the value that is in the parameter file.

3.3.1.2 Critical Database Properties

Critical database properties must be set for log transport services and log apply services to function properly. Only standby resources require that you set the critical database properties. If critical properties are not set for a standby database resource, then log transport services on the primary site will not be enabled and the health check for the configuration will fail and return an error status to the Data Guard monitor.

The LogArchiveDestOptions, ArchiveDestDependency, and StandbyArchiveDest critical database properties map directly to the following database initialization parameters: LOG_ARCHIVE_DEST_n, the DEPENDENCY attribute of LOG_ARCHIVE_DEST_n, and STANDBY_ARCHIVE_DEST, respectively.

Depending on how you create the broker configuration, the critical database properties may be set automatically or manually, as follows:

• If you create the configuration with Data Guard Manager, it automatically sets the LogArchiveDestOptions, ArchiveDestDependency, and StandbyArchiveDest properties to the following default values:
  • LogArchiveDestOptions = ' '

  • ArchiveDestDependency = ' '

  • StandbyArchiveDest takes one of the following defaults:
    • If you add an existing standby database, StandbyArchiveDest = ' '

    • If you create a new standby database, StandbyArchiveDest uses the value that you entered in the Create Configuration wizard.

• If you create the configuration with the CLI, you must set the critical properties before the configuration can be successfully enabled. For example, the following SQL statements set the critical properties for the reportingdb standby database resource:

  DGMGRL> ALTER RESOURCE reportingdb SET PROPERTY LogArchiveDestOptions = '';
  DGMGRL> ALTER RESOURCE reportingdb SET PROPERTY ArchiveDestDependency = '';
  DGMGRL> ALTER RESOURCE reportingdb SET PROPERTY StandbyArchiveDest = 
  '/archfs/arch/';
  
  
  

You do not need to set critical properties for the primary database resource.

3.3.2 Monitorable (Read-Only) Properties

Monitorable properties allow you to view information related to resources, but you cannot change the value of these properties.

The following sections describe the database resource monitorable properties:

• Section 3.3.2.1, "SendQEntries (Send Queue Entries)"

• Section 3.3.2.2, "LogXptStatus (Log Transport Status)"

• Section 3.3.2.3, "SbyLogQueue (Standby Log Queue)"

  
  

  Note: Information for monitorable properties can be seen only when the database resource is enabled and in an online state.

3.3.2.1 SendQEntries (Send Queue Entries)

The SendQEntries property returns a table that shows all of the log files on the primary site that have not yet been successfully shipped to one or more standby sites. The table contains the following columns:

• SITE_NAME

  The value can be empty or it can contain the name of the site. If empty, the STATUS column will contain a value of CURRENT or NOT_ARCHIVED.

• STATUS

  The STATUS column is set to one of the following values:

  • CURRENT--A log file to which online redo logs are being written currently.

  • NOT_ARCHIVED--A completed log file that has been archived locally, but has not been shipped to the standby sites.

  • ARCHIVED--A completed log file that has been archived locally and shipped to the standby sites.

  The table contains exactly one row with the value of STATUS=CURRENT. There can be multiple rows with the value STATUS=ARCHIVED or STATUS=NOT_ARCHIVED.

• LOG_SEQ

  The log sequence number. Multiple rows may have the same LOG_SEQ value (for different SITE_NAME values).

• TIME_GENERATED

  The time when the log was generated.

• TIME_COMPLETED

  The time when the log was completed.

For example, the following shows output from a SHOW RESOURCE VERBOSE command:

DGMGRL> SHOW RESOURCE VERBOSE 'db' 'SendQEntries';
PRIMARY_SEND_QUEUE
 SITE_NAME     STATUS       LOG_SEQ              TIME_GENERATED       TIME_COMPLETED
   Standby   ARCHIVED             9         11/21/2000 10:57:49  11/21/2000 10:59:42
   Standby   ARCHIVED            10         11/21/2000 10:59:42  11/21/2000 10:59:43
   Standby   ARCHIVED            11         11/21/2000 10:59:43  11/21/2000 10:59:49
   Standby   ARCHIVED            12         11/21/2000 10:59:49  11/21/2000 10:59:54
             CURRENT             13         11/21/2000 10:59:54

Note: In Oracle9i Data Guard Manager, information from the SendQEntries property is displayed on the Log Files property page for the primary database resource.

3.3.2.2 LogXptStatus (Log Transport Status)

The LogXptStatus property contains the error status of log transport services for each of the currently enabled standby sites, unless the following conditions are true:

• The standby database for the site is in an offline state.

• The LOG_ARCHIVE_DEST_n parameter includes the OPTIONAL attribute.

The format of the error status is as follows:

"standby1_sitename=error_status, standby2_sitename=error_status,..."

The error status can be an empty string.

In the following example, the string for Standby1 is empty because there is no error for the Standby1 destination. The standby2 destination returned the ORA-01034 message:

DGMGRL> SHOW RESOURCE VERBOSE 'Sales_db' 'LogXptStatus';
  LogXptStatus = 'Standby1=',standby2=ORA-01034: ORACLE not available"

Note: In Oracle9i Data Guard Manager, information from the LogXptStatus property is displayed on the Properties page for the primary database resource.

3.3.2.3 SbyLogQueue (Standby Log Queue)

The SbyLogQueue property returns a table that indicates all of the logs that were received by the standby site, but have not yet been applied. The table contains the following columns:

• LOG_SEQ

  The log sequence number.

• TIME_GENERATED

  The time when the log was generated.

• TIME_COMPLETED

  The time when the log was completed.

For example:

DGMGRL> SHOW RESOURCE VERBOSE 'reportingdb' 'SbyLogQueue';
STANDBY_RECEIVE_QUEUE
         LOG_SEQ         TIME_GENERATED          TIME_COMPLETED
               6    11/21/2000 10:57:16     11/21/2000 10:57:41
               7    11/21/2000 10:57:41     11/21/2000 10:57:43
               8    11/21/2000 10:57:43     11/21/2000 10:57:49

Note: If you are using Data Guard Manager to monitor properties, you can view information from the SbyLogQueue property on the Log Files property page for the standby database resource.