Navigation Bar

Wednesday, September 18, 2024

Data Guard Broker

 The dataguard broker is part of Oracle's Database Enterprise Manager and an integral part of Data Guard, it is the management framework. You can also use SQL *Plus to manage Data Guard but the broker when used with Enterprise Manager becomes a powerful tool offering the following

  • Configuration
  • Monitoring
  • Alerting
  • Performance Analysis
  • Manual switchover's
  • Automatic switchover's

The broker can make changes to a dataguard setup (physical or standby database).  These changes are configuration changes, transport modes, apply setup, role change services and the protection mode. You can also monitor the health of any configuration.

The broker is also responsible for implementing and managing automatic failover capability also know as Fast-Start Failover (FSFO).

The broker has a number of processes running and manages these processes automatically. 

These processes are

Dataguard monitor (DMON) - this is the main process and is responsible for coordinating all broker actions as well maintaining the configuration files. It is enabled or disabled with the DG_BROKER_START parameter.

Broker Resource Manager (RSM) - is responsible for handling any SQL commands used by the broker that need to be executed on one of the databases in the configuration. These SQL commands are made as a result of a change to the configuration made through DGMGRL, or are the configuration commands executed by the broker during the database setup.

Dataguard Net Server (NSVn) - from 1 to n can exist, they are responsible for making contact with the remote databases and sending across any work items to the remote database.

DRCn - these network receiver processes establish the connection from the source database NSVn process NSVn process. This is a similar connection to the Log Writer Network Service (LNS) to a Remote File Server (RFS) connection for redo transport. When the broker needs to do something between databases it uses this NSV to DRC connection. These connections are started as needed.

Configuration Files - the configuration is stored in two binary command files. The files contain the complete configuration setup, the database states how to connect to each one and what parameters to setup when each database starts up.

In a broker configuration it is the Data Guard Monitor (DMON) process on the primary database that is the owner of the configuration, all orders will come from the primary even if we use a DGMGRL CLI from another server, the standby database will receive all configuration changes via the primary.

Dataguard Broker Architecture


The DMON process communicates with the standby database using one of the NSV processes to send work items to the standby. This protects DMON from a hang if the network should go down. 

Whenever the DMON needs to execute some SQL it will enlist the aid of the RSM process on the primary database. If the SQL is for the primary database it will execute it directly. However if the SQL is for a standby database the RSM process asks the NSV process to send it to the standby database. This protects the RSM from a network hang.  

Each NSV process has a DRC partner process on the standby database, which will perform the work of the NSV process. The DRC process on the standby database will return the results or the status back to the NSV on the primary database.

On startup, the DMON will communicate with each standby via the NSV-DRV connection process pair to establish communication and configuration information so that the standby can start the apply services. If there are any problems check the  alert.log to see if there are TNS or other related errors.

When using a RAC environment there is another set of processes called the internode servers (INSV) which maintain a connection between the nodes in the cluster to ensure that the broker on each node knows the state of the cluster. A INSV process will always be started on the primary even if the primary is not a RAC setup.

Lastly you may see more processes called the fast-start-failover process (FSFP), which is used only when the primary database is under the control of data guard automatic failover feature called Fast-Start Failover.

Configuration files

Each database has two copies of the configuration, stored in the below parameter locations

  • DG_BROKER_CONFIG_FILE1
  • DG_BROKER_CONFIG_FILE2

By default they are stored in $ORACLE_HOME/dbs directory with the filename of dr1<DB_UNIQUE_NAME>.dat and dr2<DB_UNIQUE_NAME>.dat. If using RAC these should be kept on a shared location as only one set of copies can exist for the entire RAC.

The primary has the master copies of the configuration files. All changes to these files are done by the primary. Even if you are on the standby server the configuration changes will be passed to the primary via NSV-DRC processes and the DMON process will make the change which is then propogated back to the standby databases. The reason for the mulitple copies of the configuration file is if the primary has a problem then each standby has a copy. When a failover occurs the standby which becomes the primary will become the master of these configuration files.

Broker
You have two choices to interact with dataguard, either Enterprise Manager (EM) or the broker (CLI DGMGRL). You can swap between the two with a few configuration changes. To gain the full functionality of Data Guard through grid control you must use the broker,
Broker CLI DGMGRL comes with Enterprise Manager. You can run the broker on any platform you wish. It does not need to be on the same platform as the primary of the standby database.
You can access DGMGRL command line using the below
SQL> dgmgrl sys/orcledg@orcl
When you start the DGMGRL command line, it does not connect you to a database, only to the data guard configuration. Before you start to use the broker there are a few configurations to be done

  • Configure the broker parameters
  • Configure the broker and the listener
  • Configure RAC and the broker
  • Connecting to the broker
Even if you use Enterprise Manager you should really have an understanding on how the CLI works.

To start the broker you can issue the following command


SQL> alter system set DG_BROKER_START=TRUE SCOPE=BOTH;
To stop the broker you can set the DG_BROKER_START to false.
If you want to change the path of the configuration files you have to follow the following steps
    1. Stop the broker.
    2. Change the DG_BROKER_CONFIG_FILE1 and DG_BROKER_CONFIG_FILE2 to point to the new directory path location.
    3. Copy the configuration files to the new location.
    4. Restart the broker.

The broker uses the Oracle Net services to make connections to the databases, setup redo transport, resolve archive gaps and perform role transitions. 
We need to create a special static entry in the listener.ora file for each database in the broker configuration. This entry makes it possible for the broker to connect to an idle instance using a remote SYSDBA connection and perform the necessary startup.

Example
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
        (GLOBAL_DBNAME = alpha_dgmgrl)
        (ORACLE_HOME = /u01/app/oracle/product/ora19/home)
        (SID_NAME = alpha)
     )
  )

SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
        (GLOBAL_DBNAME = orcl_dgmgrl)
        (ORACLE_HOME = /u01/app/oracle/product/ora19/home)
        (SID_NAME = orcl)
     )
  )
Once the listener is set we can create the broker configuration. To connect to the broker configuration we use dgmgrl command as described above.

Once we connect to the broker on command line using DGMGLR, we create and display the configuration as follows
DGMGRL> connect sys/password
DGMGRL> create configuration orcl as primary database is orcl connect identifier is orcl;

Configuration "orcl" created with primary database "orcl"  

DGMGRL> show configuration;

Configuration - orcl

Protection Mode: MaxPerformance
Databases:
orcl - Primary database
orcl - Physical standby database


Fast-Start Failover: DISABLE

Configuration Status:
SUCCESS

We now add the the standby database to the configuration.
DGMGRL> connect sys/password
DGMGRL> add database alpha as connect identifier is alpha maintained as physical;

Database "alpha" added
When you display the configuration it should show the standby has beend added
DGMGRL> connect sys/password
DGMGRL> show configuration
Configuration - orcl

Protection Mode: MaxPerformance
Databases:
orcl - Primary database
alpha - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED
##primary database 
DGMGRL> show database verbose orcl 
Database - orcl

Role: PRIMARY
Intended State: OFFLINE
Instance(s):
orcl

Properties:
DGConnectIdentifier = 'orcl'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'oellinuxorcl'
SidName = 'orcl'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'

Database Status:
DISABLED
##physical standby database 
DGMGRL> show database verbose alpha 
Database - alpha

Role: PHYSICAL STANDBY
Intended State: OFFLINE
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
ORCL
Properties:
DGConnectIdentifier = 'alpha'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'oellinuxbeta'
SidName = 'alpha'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.102)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ALPHA_DGMGRL)(INSTANCE_NAME=ORCL)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'

Database Status:
DISABLED
One parameter to watch for is the archiving parameter on the primary and standby databases. The broker can modify this parameter and if necessary add the VALID_FOR attribute in preparation for the archival of your standby redo log files.
If you add an additional standby database, you have to verify carefully that the parameters are set correctly for the type of configuration you want. Changes if any should be done before enabling the additional standby database.
Once the configuration of parameters on all the standby databases has been set correctly and verified,  you can enable the configuarion. This will start the redo transport on the primary database and the apply services on the standby databases. 
DGMGRL> connect sys/password
DGMGRL> enable configuration
Enabled.
During the process you can tail the alert log file to see the action that is taking place. Once the configuration is complete and running successfully you can verify it as below. 
# Primary Database server

DGMGRL> connect sys/password
DGMGRL> show configuration;

Configuration - orcl

Protection Mode: MaxPerformance
Databases:
orcl - Primary database
alpha - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
You can also check the redo transport and apply services by viewing the v$managed_standby VIEW.

# Standby database - check the LGWR - RFS connection process and the MRP0 is in the applying state 
SQL> select client_process, process, thread#, sequence#, status from v$managed_standby;
Broker Properties
Broker properties can be edited using the command in the DGMGRL CLI.
EDIT CONFIGURATION SET PROPERTY =
EDIT DATABASE  SET PROPERTY =
EDIT INSTANCE  SET PROPERTY =value>
Some of the broker configuration preoperties and the significance is given below. 

Configuration Properties


BystandersFollowRoleChange
The BystandersFollowRoleChange configuration property establishes whether bystander standby databases are evaluated during failover (value = ALL) or after failover (value = NONE).
 
FastStartFailoverAutoReinstate
The FastStartFailoverAutoReinstate configuration property causes the former primary database to be automatically reinstated if a fast-start failover was initiated because the primary database was either isolated or had crashed.
To prevent automatic reinstatement of the former primary database in these cases, set this configuration property to FALSE.
The broker never automatically reinstates the former primary database if a fast-start failover was initiated because a user configuration condition was detected or was requested by an application calling the DBMS_DG.INITIATE_FS_FAILOVER function.
 
FastStartFailoverLagLimit
The FastStartFailoverLagLimit configuration property establishes an acceptable limit, in seconds, that the standby is allowed to fall behind the primary in terms of redo applied.
If the limit is reached, then a fast-start failover is not allowed. The lowest possible value is 5 seconds.
This property is used when fast-start failover is enabled and the configuration is operating in maximum performance mode.
 
FastStartFailoverPmyShutdown
The FastStartFailoverPmyShutdown configuration property causes the primary database to shut down under certain conditions.
The primary database shuts down if fast-start failover is enabled and V$DATABASE.FS_FAILOVER_STATUS indicates the primary has been STALLED for longer than FastStartFailoverThreshold seconds. In such a situation, it is likely that the primary has been isolated and a fast-start failover has already occurred. A value of TRUE helps to ensure that an isolated primary database cannot satisfy user queries.
Setting this property to FALSE will not prevent the primary database from shutting down if a fast-start failover occurred because a user configuration condition was detected or was requested by an application by calling the DBMS_DG.INITIATE_FS_FAILOVER function.

FastStartFailoverThreshold
The FastStartFailoverThreshold configuration property defines the number of seconds the master observer attempts to reconnect to the primary database before initiating a fast-start failover.
If there is only one observer, then by default it is considered the master. The time interval starts when the observer first loses connection with the primary database. If the observer is unable to regain a connection to the primary database within the specified time, then the observer initiates a fast-start failover to the target standby database. See Task 4 in Enabling Fast-Start Failover for more information about setting this property.
The observer ignores the threshold completely if a configurable fast-start failover condition is detected or an application has requested that fast-start failover be initiated.
 
CommunicationTimeout
The CommunicationTimeout configuration property allows you to decide how many seconds the broker should wait before timing out its network communication between members in the configuration.
A value of zero indicates that a network communication should never be timed out.

Database Properties

FastStartFailoverTarget
The FastStartFailoverTarget configuration property specifies the DB_UNIQUE_NAME of one or more standby databases that can act as target databases in a fast-start failover situation when the database on which the property is set is the primary database.
These possible target databases are referred to as candidate fast-start failover targets. See Task 2 in Enabling Fast-Start Failover for more information about setting this property.
The FastStartFailoverTarget configuration property can only be set to the name of physical or logical standbys. It cannot be set to the name of a snapshot standby database, far sync instance, or Zero Data Loss Recovery Appliance.

ObserverConnectIdentifier
The ObserverConnectIdentifier configurable property specifies a connect identifier that can be used by the observer to connect to this database.
The connect identifier can pertain only to the primary database, or to the target standby database when fast-start failover is enabled.

ApplyInstanceTimeout
The ApplyInstanceTimeout configurable property specifies the number of seconds the broker waits after detecting the current apply instance failed before initiating the apply instance failover.
Basically it defines how long the broker should wait before moving the apply processes to another instance on the standby database. The default is 0 which is immediately.

PreferredApplyInstance
The PreferredApplyInstance configurable property indicates that a particular instance is the preferred choice for serving log apply services.
It is only used when the database is a standby Oracle RAC database. The value could be an empty string (default) which means the broker chooses the apply instance.

ArchiveLagTarget
The ArchiveLagTarget configurable property limits the amount of data that can be lost.
In effect, limiting the amount of data that can be lost 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 the value set for the ARCHIVE_LAG_TARGET initialization parameter.

DbFileNameConvert
The DbFileNameConvert configurable property distinguishes physical standby datafile filenames from primary datafile filenames.

If you add a datafile to the primary database, this property converts the datafile name on the primary database to the datafile on the physical standby database.

This property is used in the following situations:

At physical standby mount time, it is used to rename primary datafile filenames to standby datafile filenames if the datafile file path on the physical standby system is different from the primary database system.

When a new data file is created on the primary database, a corresponding new data file will be created on the physical standby database if the StandbyFileManagement configurable property is set to 'AUTO'. Oracle uses the data-file file-path mapping information from the DbFileNameConvert property to determine the standby file path of the new standby data file. If the StandbyFileManagement property is set to 'MANUAL', you must add a corresponding file to the physical standby database.
Database parameter : DB_FILE_NAME_CONVERT

LogArchiveMaxProcesses
The LogArchiveMaxProcesses configurable property specifies the initial number of archiver processes (ARCn) that are invoked.
The actual number of archiver processes in use may increase subsequently based on the archiving workload.
database parameter: LOG_ARCHIVE_MAX_PROCESSES

LogFileNameConvert
The LogFileNameConvert configurable property converts the filename of an online redo log file on the primary database to the filename of a corresponding online redo log file on the physical standby database.
database parameter: LOG_FILE_NAME_CONVERT

LogShipping
The broker uses the value of the LogShipping property when the primary database is in the TRANSPORT-ON state or when the physical standby or far sync instance forwards redo data to another member.
The other member can be a physical, logical, or snapshot standby, or a far sync instance.
  • If the primary database is in the TRANSPORT-ON state and the value of the LogShipping property is ON, then redo transport services are enabled to send redo data to the particular configuration member. If the LogShipping property is OFF, then redo transport services are disabled to that member.
  • If a configuration member that forwards redo data has its LogShipping property set to ON and the member to which it sends redo data also has its LogShipping property set to ON, then redo transport services are enabled from the member sending redo data to the member that is to receive redo data.
  • If a member that forwards redo data has its LogShipping property set to ON but the member to which it sends redo data has its LogShipping property set to OFF, then redo transport services are disabled from the member sending redo data to the member that is to receive redo data.
database parameter: LOG_ARCHIVE_DEST_STATE_n

StandbyFileManagement
The StandbyFileManagement configurable property affects how the add datafile operation on the primary database is applied on the standby database.

If this property is set to AUTO, in conjunction with valid settings in the DbFileNameConvert configurable property, a corresponding new datafile is automatically created on the standby database. 
The location of this new standby datafile is determined by the value of the DbFileNameConvert property.

If this property is set to MANUAL, you have to create the correct new datafile on the standby database manually.
database parameter: STANDBY_FILE_MANAGEMENT

LogArchiveTrace
Set the LogArchiveTrace configurable instance-specific property to an integer value to see the progression of the archiving of online redo log files on the primary and the standby databases.
The Oracle database writes an audit trail of the archived redo log files received from the primary database into process trace files.
database parameter: LOG_ARCHIVE_TRACE

LogArchiveFormat
The LogArchiveFormat configurable instance-specific property specifies the format for filenames of archived redo log files.
To specify the format, it uses a database ID (%d), thread (%t), sequence number (%s), and resetlogs ID (%r).
database parameter: LOG_ARCHIVE_FORMAT

StandbyArchiveLocation
The StandbyArchiveLocation configurable property specifies the standby redo log archive location. Oracle recommends that you always explicitly set the value.
database parameter: LOG_ARCHIVE_DEST_n

AlternateLocation
The AlternateLocation configurable property specifies an alternate online redo log archive location for primary, logical, and snapshot standby databases when the location specified by the ArchiveLocation configurable property fails.
If the StandbyArchiveLocation property is not empty, the AlternateLocation property specifies an alternate online redo log archive location. If the StandbyArchiveLocation property is empty, the AlternateLocation property specifies an alternate online and standby redo log archive location.
This property has database-specific scope, and the location that it specifies is applicable to all instances in a database.
database parameter: LOG_ARCHIVE_DEST_n

LsbyMaxSga
The LsbyMaxSga configurable instance-specific property specifies the number of megabytes for the allocation of SQL Apply cache in the system global area (SGA).
If the value is 0, SQL Apply uses one quarter of the value set for the SHARED_POOL_SIZE initialization parameter.
database parameter: MAX_SGA

LsbyMaxServers
The LsbyMaxServers configurable instance-specific property specifies the number of parallel query servers specifically reserved for SQL Apply.
If the value is 0, then SQL Apply uses all available parallel query servers to read the log files and apply changes.
database parameter: MAX_SERVERS


For more details on the definitions of the broker parameters you can refer 

Oracle Data Guard Broker Properties

 In the next blog I will show the actual setup (and scripts) of the dataguard broker and fast_start failover on the primary and the physical standby databases.
Till then .. Keep Reading .. Keep Learning :-)


Thought for the day
When pride comes, then comes disgrace, 
  but wisdom is with the humble. 
The integrity of the upright guides them, 
  but the crookedness of the treacherous destroys them. 
Proverbs 11:2-3
at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)