Data Guard Broker Configuration:
===========================
dgmgrl of Data Guard Broker is a client of oracle binary assists to ensure high availability at database downtime situation. If primary database server is down permanently or not available then using this dgmgrl command prompt we can failover to standby database to reduce the downtime issue. Especially there are three exclusive feature switchover,failover,reinstate in data guard. dgmgrl can take action and monitor the status of data guard. Easily we can test our dr site activity using switchover to change database role at runtime to different connection identifier. Here it can be added that if dgmgrl configuration is enabled then physical standby database mrp process will be started automatically at mount mode.
step: 1 enable dg broker both in primary and standby server:
sql>ALTER SYSTEM SET dg_broker_start=true;
step: 2 dgbroker create configuration
DGMGRL> CREATE CONFIGURATION cfg_name AS PRIMARY DATABASE IS primary_db CONNECT IDENTIFIER IS primary_db
step: 3 add standby db to dgbroker
DGMGRL> ADD DATABASE stbydb1 AS CONNECT IDENTIFIER IS stbydb1 MAINTAINED AS PHYSICAL;
DGMGRL> ADD DATABASE stbydb2 AS CONNECT IDENTIFIER IS stbydb2 MAINTAINED AS PHYSICAL;
The following error may be handled
Error: ORA-16796: one or more properties could not be imported from the database
solution: check whether data guard broker facility is on or not both in primary and physical standby db.
step:4 tune configuration status
DGMGRL> show configuration
step:5 enable configuration
step:6 monitor standby db
DGMGRL> show database stbydb1
note: connection identifier from the standby db tnsname.ora file
The following error can be raised.
ORA-16714: the value of property ArchiveLagTarget is inconsistent with the member setting
Solution:
on standby
SQL> select name,open_mode,db_unique_name,database_role from v$database;
NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
stbydb1 MOUNTED stbydb1DG PHYSICAL STANDBY
SQL> alter system set log_archive_max_processes=15 scope=both;
System altered.
SQL> alter system set archive_lag_target=0 scope=both;
System altered.
SQL> alter system set log_archive_min_succeed_dest=1 scope=both;
System altered.
value of parameters should be same both in primary and physical standby db
#after change disable and enable the dgmgrl configuration
DGMGRL> disable configuration
DGMGRL> enable configuration
-->hands on:
#on/off mrp apply at any physical standby db
DGMGRL> edit database stydb set state ='LOG-APPLY-OFF';
#to trace error text and its severity level instance wise
DGMGRL> show database stbydb1 statusreport;
#to trace InconsistentProperties instancewise
DGMGRL> show database stbydb1 InconsistentProperties;
#check readiness for switchover
DGMGRL> validate database stbydb;
#Fast-Start Failover(FSFO):
Enable Fast-Start Failover
step: 1
enable fast_start failover;
step: 2
Start the observer;
#Switchover:
To change database role from physical standby to primary database.
DGMGRL> switchover database to stby_db
#Failover:
DGMGRL> failover database to stby_db
#warning:
standby redo logs not configured for thread 1 on dbdcicbs
#solution:
ok please apply this
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
alter database drop standby logfile group 7;
alter database add standby logfile thread 1 '/u02/oradata/CDB1DG/stbylog/stby4.log' size 200M reuse;
alter database add standby logfile thread 1 '/u02/oradata/CDB1DG/stbylog/stby5.log' size 200M reuse;
alter database add standby logfile thread 1 '/u02/oradata/CDB1DG/stbylog/stby6.log' size 200M reuse;
alter database add standby logfile thread 1 '/u02/oradata/CDB1DG/stbylog/stby7.log' size 200M reuse;
#warning:
Ensure primary database's StaticConnectIdentifier property
is configured properly so that the primary database can be restarted
by DGMGRL after switchover
#solution:
Single Instance Database
For this type of database the static service registration
is formatted as follows:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
(ORACLE_HOME=oracle_home)
(SID_NAME=sid_name)
)
)
# Convert database
For resource & development for testing before deployment to production according to business requirement we need to convert database to physical standby to snapshot standby database.
DGMGRL> convert database testdb to snapshot standby;
DGMGRL> convert database testdb to physical standby;
