Configuring Data Guard with DG Broker

Extracted from http://uhesse.com/2013/07/08/the-data-guard-broker-why-it-is-recommended/
When it comes to Data Guard on a recent version, I will always use the Data Guard Broker (Command Line with DGMGRL).
It is for Standby Databases what RMAN is for Backup & Recovery: The recommended way to go. Why? Four reasons at least:

1. The Broker helps during the setup

This demo uses two Linux machines:
Machine uhesse1 has the Primary Database prima. Primary DB name is prima.
Machine uhesse2 is for the Standby Database physt. Primary DB name is prima.

The Oracle Net Configuration on uhesse1:

[oracle@uhesse1 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = uhesse1)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = prima_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = prima)
    )
  )

[oracle@uhesse1 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora 
PRIMA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = uhesse1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = prima)
    )
  )

PHYST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = uhesse2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = physt)
    )
  )

_DGMGRL is the only special part here: The Broker needs that to be able to restart the instance during Role Changes.
Standby Configuration is the same, except that there is physt instead of prima in the listener.ora.
The initialization parameters for prima are

[oracle@uhesse1 ~]$ sqlplus sys/oracle@prima as sysdba
SQL> create pfile='/home/oracle/initprima.ora' from spfile;
SQL> exit

[oracle@uhesse1 ~]$ cat initprima.ora *.compatible='11.2.0.3' *.control_files='/home/oracle/prima/control01.ctl' *.db_block_size=8192 *.db_name='prima' *.db_recovery_file_dest='/home/oracle/flashback' *.db_recovery_file_dest_size=5g *.diagnostic_dest='/home/oracle/prima' *.remote_login_passwordfile='exclusive' *.undo_management='auto' *.undo_tablespace='undotbs1'

I kept that as minimalistic as possible in order to give you an easy overview about what is relevant for Data Guard here Ė defaults almost everywhere. Only a few customizations for the Standby gives me

[oracle@uhesse1 ~]$ cp initprima.ora initphyst.ora 
[oracle@uhesse1 ~]$ cat initphyst.ora *.compatible='11.2.0.3' *.control_files='/home/oracle/physt/control01.ctl' *.db_block_size=8192 *.db_name='prima' *.db_unique_name=physt *.db_file_name_convert='prima','physt' *.log_file_name_convert='prima','physt' *.db_recovery_file_dest='/home/oracle/flashback' *.db_recovery_file_dest_size=5g *.diagnostic_dest='/home/oracle/physt' *.remote_login_passwordfile='exclusive' *.undo_management='auto' *.undo_tablespace='undotbs1'

I copy that pfile and the password file to the Standby host and go there into NOMOUNT before the duplicate command

[oracle@uhesse1 ~]$ scp initphyst.ora uhesse2:/home/oracle 
[oracle@uhesse1 ~]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwprima uhesse2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwphyst
  
[oracle@uhesse1 ~]$ ssh uhesse2 mkdir /home/oracle/physt
[oracle@uhesse1 ~]$ ssh uhesse2 mkdir /home/oracle/flashback
[oracle@uhesse1 ~]$ sqlplus sys/oracle@physt as sysdba

SQL> create spfile from pfile='/home/oracle/initphyst.ora';
SQL> startup nomount
SQL> exit

It is most efficient to create the Standby Redo Logs (SRLs) at this point on the Primary, because (from 11g on) RMAN will duplicate them onto the Standby then.
SRLs are recommended on either side and are required on the Standby for LGWR Transport

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS		 FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
	 1	    1	       9  104857600	   512		1 NO
CURRENT 		238195 19-JAN-12   2.8147E+14

	 2	    1	       8  104857600	   512		1 YES
INACTIVE		234561 18-JAN-12       238195 19-JAN-12

SQL> alter database add standby logfile '/home/oracle/prima/srl_g3.rdo' size 100m;
SQL> alter database add standby logfile '/home/oracle/prima/srl_g4.rdo' size 100m;
SQL> alter database add standby logfile '/home/oracle/prima/srl_g5.rdo' size 100m;

[oracle@uhesse1 ~]$ rman target sys/oracle@prima auxiliary sys/oracle@physt

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jul 8 12:08:56 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PRIMA (DBID=2003897072)
connected to auxiliary database: PRIMA (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 08-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
......
.......
Finished Duplicate Db at 08-JUL-13

So far no DGMGRL involved. RMAN gave me a Physical Standby but did not configure Redo Transport from Primary to Standby nor did it start Redo Apply on the Standby. DGMGRL will now do that:

[oracle@uhesse1 ~]$ dgmgrl sys/oracle@prima
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.

DGMGRL> help create Creates a broker configuration Syntax: CREATE CONFIGURATION AS PRIMARY DATABASE IS CONNECT IDENTIFIER IS ; DGMGRL> CREATE CONFIGURATION myconf AS PRIMARY DATABASE IS prima CONNECT IDENTIFIER IS prima; Error: ORA-16525: the Data Guard broker is not yet available ORA-06512: at "SYS.DBMS_DRS", line 157 ORA-06512: at line 1 DGMGRL> exit [oracle@uhesse1 ~]$ oerr ora 16525 16525, 00000, "the Data Guard broker is not yet available" // *Cause: The Data Guard broker process was either not yet started, was // initializing, or failed to start. // *Action: If the broker has not been started, set the DG_BROKER_START // initialization parameter to true and allow the broker to finish // initializing before making the request. If the broker failed to // start, check the Data Guard log for possible errors. Otherwise, // retry the operation.

Oops, I forgot to set that parameter Ė that was of course intentionally for didactical reasons ;-)

SQL> alter system set dg_broker_start=true;


SQL> connect sys/oracle@physt as sysdba
SQL> alter system set dg_broker_start=true;
SQL> exit

[oracle@uhesse1 ~]$ dgmgrl sys/oracle@prima "CREATE CONFIGURATION myconf AS PRIMARY DATABASE IS prima CONNECT IDENTIFIER IS prima" DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. Configuration "myconf" created with primary database "prima"

The built in help function is so good (wished that RMAN had it also!) that I donít need the documentation here:

[oracle@uhesse1 ~]$ dgmgrl sys/oracle@prima
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> help add

Adds a standby database to the broker configuration

Syntax:

  ADD DATABASE 
    [AS CONNECT IDENTIFIER IS ]
    [MAINTAINED AS {PHYSICAL|LOGICAL}];

DGMGRL> ADD DATABASE physt AS CONNECT IDENTIFIER IS physt MAINTAINED AS PHYSICAL;
Database "physt" added
DGMGRL> enable configuration;
Enabled.

You should monitor the alert.log of the two databases while that enabling is in progress Ė the Broker does a lot here, especially it configures Redo Transport and Redo Apply.

DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxPerformance
  Databases:
    prima - Primary database
    physt - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database physt;  

Database - physt

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    physt

Database Status:
SUCCESS

That was already it. The hardest part was the Oracle Net Configuration, right? Our heroes RMAN & DGMGRL did the rest.

2. Role Changes are much easier with the Broker

Without the Broker, Data Guard Role Changes require a complex sequence of steps (versions before 12c) on both sides that differ between Logical and Physical Standby. Not so with DGMGRL:

DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxPerformance
  Databases:
    prima - Primary database
    physt - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to physt;
Performing switchover NOW, please wait...
New primary database "physt" is opening...
Operation requires shutdown of instance "prima" on database "prima"
Shutting down instance "prima"...
ORACLE instance shut down.
Operation requires startup of instance "prima" on database "prima"
Starting instance "prima"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "physt"
DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxPerformance
  Databases:
    physt - Primary database
    prima - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Try that with SQL commands and you spend a significant amount of time reading the documentation in order to get these steps right. Furthermore, I donít need to bother about LOG_ARCHIVE_DEST_2 because the Broker sets it correctly Ė without manual intervention and without VALID_FOR. The other Role Changes are also one-liners with the Broker:

DGMGRL> failover to physt;

Thatís it for the manual failover. And there is also only one command needed for the Snapshot Standby:

DGMGRL> convert database physt to snapshot standby;

Easy, isnít it?


3. The Data Guard Broker delivers basic monitoring of the Configuration

The Broker is quite sensitive and spots problems with the Data Guard Configuration fast. It is a good indicator that everything is actually okay when you see this:

DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxPerformance
  Databases:
    prima - Primary database
    physt - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS



4. Fast-Start Failover requires the Data Guard Broker

Conclusion: In many ways, the Data Guard Broker respectively DGMGRL is comparable to RMAN: The tool is recommended because it makes critical tasks easier to do with less risk of errors than a manual approach has. Some features even require it, like RMAN for incremental backups and the Broker for Fast-Start Failover. It is the most efficient and reliable way to go. Yes, there have been bugs (some in version 9 for the Broker, and RMAN wasnít reliable in version 8) Ė but we donít live in the past. Iíll go with the Broker for Data Guard anytime. The shown demo should be easy to reproduce for you, so as always: ďDonít believe it, test it!Ē :-)