Online DBA

<<Back to Oracle Data Guard Broker Page Oracle Dataguard BystandersFollowRoleChange Property  BystandersFollowRoleChange Valid Values:         ALL| NONE Broker Default:     ALL Lets first look at What is Bystander Database ? The concept of bystander comes only if you have multiple standby databases. Bystander standby database is the standby database that is not directly involved in switchover or failover. In other words all standby databases except failover target database is considered as bystander standby. v$database.fs_failover_status = 'BYSTANDER' can we queried to find the list of bystander databases. The BystandersFollowRoleChange configuration property establishes whether bystander standby databases are evaluated during failover (value = ALL) or after failover (value = NONE). Descriptions of the ALL and NONE options are as follows: ALL - During the failover process, the broker determines whether the bystander standby databases are ahe...

<<Back to Oracle Data Guard Broker Page Oracle Dataguard FastStartFailoverPmyShutdown Property  FastStartFailoverPmyShutdown Valid Values:       TRUE| FALSE Broker Default:    TRUE The FastStartFailoverPmyShutdown configuration property causes the primary database to shut 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. DGMGRL> edit configuration set property FastStartFailoverPmyShutdown=TRUE; Property "FastStartFailoverPmyShutdown" updated

<<Back to Oracle Data Guard Broker Page Oracle Dataguard FastStartFailoverAutoReinstate Property  FastStartFailoverAutoReinstate Valid Values:      TRUE| FALSE Broker Default:   TRUE If set to true this property caused the former primary database to automatically re-instantiate and become Standby in case failover occurs. If you want to prevent the automatic re-instantiation set the property value FALSE NOTE: Automatic re-instantiate will only succeed if the database is already configured in flashback mode and necessary archive logs are still present.   DGMGRL> edit configuration set property FastStartFailoverAutoReinstate=FALSE; Property "FastStartFailoverAutoReinstate" updated

<<Back to Oracle Data Guard Broker Page Enable Auto Reinstate of Crashed Primary In case of Failover Setting FastStartFailoverAutoReinstate  property value TRUE enables the feature of Reinstating the failed primary database in case of automatic failover. Valid value TRUE or FALSE Broker default TRUE DGMGRL> edit configuration set property FastStartFailoverAutoReinstate=TRUE; Property "FastStartFailoverAutoReinstate" updated NOTE: Auto Reinstate only succeed if flashback is enabled and necessary Archivelog/flshabcklogs are available

<<Back to Oracle Data Guard Broker Page Oracle Dataguard Broker ObserverReconnect  Property  This configuration property specifies how often the observer establishes a new connection to the primary database. Valid values >=0 Broker default 0 If ObserverReconnect=0 then the observer creates and maintains a connection to the primary database all the time. DGMGRL> edit configuration set property ObserverReconnect=60 ; Property "ObserverReconnect" updated

<<Back to Oracle Data Guard Broker Page Oracle Dataguard Broker CommunicationTimeout  Property  configuration property allows us to decide how many seconds the broker should wait before timing out its network communication between databases in the configuration. A value of zero indicates that a network communication should never be timed out. Valid values >= 0 Broker default 180 seconds DGMGRL> DGMGRL> edit configuration set property CommunicationTimeout=240; Property "communicationtimeout" updated

<<Back to Oracle Data Guard Broker Page How to Enable fast Start Failover in Maximum Performance Mode FSFO can be enabled even if the configuration is operating in Maximum Performance mode by setting configuration Property  FastStartFailoverLagLimit Configuration property FastStartFailoverLagLimit allows to occur failover to Physical standby database even if it is lagging behind Primary database. The value is defined in seconds. If the physical standby database is lagging behind Primary with in this limit and Primary is not available, Failover can occur. In this situation it is acceptable by business to have data loss. The value for FastStartFailoverLagLimit must not be less than RPO (recovery point objective) defined by business.The Property is used only if the configuration is operating in MaxPerformance mode and FSFO is enabled. B Broker Default value  is 30 seconds Minimum configurable value is 5 sec Value 0 means property is ignored and is the default value in case ...

<<Back to Oracle Data Guard Broker Page How to Define Dataguard Broker Tracing Level Configuration property TraceLevel is used to control the amount of tracing performed by broker for every member in the configuration Values : USER | SUPPORT Broker Default USER TraceLevel= USER := Traces only completed , warning and error messages of an operation TraceLevel= SUPPORT := Traces more detailed operation messages needed by oracle support DGMGRL> edit configuration set property TraceLevel=SUPPORT; Property "tracelevel" updated

<<Back to Oracle Data Guard Broker Page How to Define Oracle Dataguard Broker Health Check Timeout OperationTimeout specifies the maximum time period (in seconds ) the broker should wait for health check, get property and set property operation to complete. Value >=30 and <=3600 Broker Default 30 Seconds DGMGRL> edit configuration set property OperationTimeout=120; Property "operationtimeout" updated NOTE: if OperationTimeout is set too low for your environment or sometimes due to intermittent network issue , you will notice below error in broker log file. DMON: Operation CTL_GET_STATUS stalled during phase BEGIN. Error=ORA-16713

<<Back to Oracle DataGuard Main Page How to Configure Observer and Enable Fast Start Failover for Oracle Dataguard The article covers configuring observer on Windows Server only. Although setting up Observer on Linux to Monitor your DG setup for automatic Failover doesn't differ much from Windows, Installing Observer on Linux will be covered in different post. To install broker on Windows Server,  The first requirement is to find a way to run the  observer process in background. You can use one of following methods for the same.             1. HSTART ( Hidden Start ) software (keep in mind its not free)              2. SilentCMD (its free)              3. Windows task Scheduler. Lets pick up the easiest way and quickly configure Observer on Windows without wasting much time. I am not going to u...

<<Back to Oracle Data Guard Broker Page FastStartFailoverThreshold: Define Failover Time  Failover occurs if both observer and target standby database lose connection with Primary database for the period of time specified by FastStartFailoverThreshold property. The value of the property is in seconds. Default value is 30 seconds and lowest possible value is 6 seconds. DGMGRL> edit configuration set property FastStartFailoverThreshold=300; Property "faststartfailoverthreshold" updated The command above sets the FastStartFailoverThreshold to 5min. This means, the failover will occur after 5min after Observer and Target Standby lose the connection with Primary. If you want to wait for longer duration to initiate the failover, specify larger value for FastStartFailoverThreshold property. Here is list of other properties related to Fast Start Failover. In most of the cases the default value is sufficient. FastStartFailoverLagLimit FastStartFailoverPmyShutdown FastStartFai...

<<Back to Oracle Data Guard Broker Page How Oracle Dataguard Broker Works Oracle Dataguard Broker has server side and client side components. At client side it has OEM and DGMGRL. Both the utilities can be used to read and manipulate Broker Configuration data. At server Side it has got 2 components 1> Binary Configuration File 2> DMON process DMON processes is responsible to monitor the health of the broker configuration and ensures that every database has a consistent description of the configuration. To Ensure this DMON process persistently maintains information about all members of the broker configuration in a binary configuration file. In Configuration file Broker stores various properties associated with each database to control the database's behavior. Whenever you add databases to a broker configuration, or make a change to an existing database's properties, each DMON process records the new information in its copy of the configuration file. The param...

<<Back to Oracle Data Guard Broker Page Understanding  Oracle Data Guard Broker and DGMGRL Utility Oracle Data Guard Broker is an Utility which logically groups all the dataguard members in oracle dataguard configuration to centrally  manage and monitor them together as an integrated unit. Oracle Dataguard Broker stores the necessary information in a file locally on each dataguard member server. The file location is configured using following database parameters dg_broker_config_file1 dg_broker_config_file2 The content of above the file is accessed and managed by Dataguard Monitor  ( DMON ) Process. The DMON process is an Oracle background process that runs on every database instance that is managed by the broker. The start/stop of DMON process (which interns enables or disables) broker is managed by database  parameter  called dg_broker_start , the value of which is TRUE (enabled) or FALSE (disabled). The broker configuration can be queried and/o...