Data Guard Broker Configuration With failover and Switchover:
Setup DG Broker
Primary: cobra
Standby: cobradup
–In below steps above two are used as primary and standby databases.
Enable Data Guard Broker Start on the Primary and Standby databases
SQL> sho parameter dg
NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_config_file1 string /export/home/oracle/product/11
.2.0/dbhome_1/dbs/dr1cobra.dat
dg_broker_config_file2 string /export/home/oracle/product/11
.2.0/dbhome_1/dbs/dr2cobra.dat
dg_broker_start boolean FALSE
SQL>alter system set DG_BROKER_START=TRUE scope=both;
Make Listener Setting:
Set Global database name as <db_name>_DGMGRL on primary and standby both.
EXP:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /export/home/oracle/product/11.2.0/dbhome_1)
(GLOBAL_DBNAME = cobra_DGMGRL)
(SID_NAME = cobra)
)
)
oracle>lsnrctl status ——–will show new service as cobra_DGMGRL
Use dgmgrl CLI and make necessary changes:
oracle>dgmgrl
DGMGRL for Solaris: Version 11.1.0.6.0 – 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@cobra
DGMGRL> CREATE CONFIGURATION cobra AS
PRIMARY DATABASE IS cobra
CONNECT IDENTIFIER IS cobra;
–Add the standby to the configuration and check it
DGMGRL> ADD DATABASE cobradup AS
CONNECT IDENTIFIER IS cobradup
MAINTAINED AS PHYSICAL;
Enabling the configuration and databases
DGMGRL> enable configuration;
Enabled.
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE VERBOSE cobra;
DGMGRL> SHOW DATABASE VERBOSE cobradup;
–Status should be success.
Enabling Fast Start Failover and Observer:
Enabling flashback on Primary and standby:
SQL> startup mount
SQL> alter system set db_recovery_file_dest_size=1G;
SQL> alter system set db_recovery_file_dest='/u01/oradata/cobradup/flash_recovery_area';
SQL> alter database flashback on;
–Ensure standby redo logs are configured on all databases
Setup the LogXptMode Property is to SYNC:
DGMGRL> EDIT DATABASE cobra SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE cobradup SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
Setup the FastStartFailoverTarget property
DGMGRL> EDIT DATABASE cobradup SET PROPERTY FastStartFailoverTarget='cobra';
Property "faststartfailovertarget" updated
DGMGRL> EDIT DATABASE cobra SET PROPERTY FastStartFailoverTarget='cobradup';
Property "faststartfailovertarget" updated
DGMGRL> SHOW DATABASE VERBOSE cobra;
DGMGRL> SHOW DATABASE VERBOSE cobradup;
Upgrade the protection mode to MAXAVAILABILITY, if necessary:
DGMGRL>EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
or
SQL> ALTER DATABASE
2> SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};
Now Check inconsistencies, if exist there just by using below command:
DGMGRL> show database 'cobra' InconsistentProperties;
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
–In above there is no inconsistency exists.
—If exist then make it correct just by using
–edit database 'cobra' set property
Now enable Fast start Failover:
DGMGRL> show fast_start failover
Fast-Start Failover: DISABLED
Threshold: 30 seconds
Target: (none)
Observer: (none)
Lag Limit: 30 seconds
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions:
(none)
–If necessary then set below:
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = '180';
Property "faststartfailoverthreshold" updated
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.
DGMGRL> show configuration;
Configuration – cobra
Protection Mode: MaxAvailability
Databases:
cobra – Primary database
Warning: ORA-16819: fast-start failover observer not started
cobradup – (*) Physical standby database
Warning: ORA-16819: fast-start failover observer not started
Fast-Start Failover: ENABLED
Configuration Status:
WARNING
Start the observer:
DGMGRL> START OBSERVER;
NOTE: Run observer in backbground.
Check configurations should be success without error and warnings.
DGMGRL> show configuration verbose
Configuration – cobra
Protection Mode: MaxAvailability
Databases:
cobra – Primary database
cobradup – (*) Physical standby database
(*) Fast-Start Failover target
Fast-Start Failover: ENABLED
Threshold: 180 seconds
Target: cobradup
Observer: nsnora008.nat.myrio.net
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Configuration Status:
SUCCESS
Now check switchover using below commands:
DGMGRL>switchover to <standb_DB>;
Note: I will post detailed switchover and failover of databases soon.
Download: DG broker conf with failover and switchover
Error Faced During Congiguration:
ORA-16826: APPLY SERVICE STATE IS INCONSISTENT WITH THE DELAYMINS PROPERTY
Request you all to put your valuable comments on posts of this blog, will motivate me to write more good post.