Data Guard Broker

 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;