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. Not the Enterprise Manager; don’t get me wrong: Strictly 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: uhesse1 has the Primary Database prima running. uhesse2 is for the Standby Database physt. 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*Plus: Release 11.2.0.3.0 Production on Mon Jul 8 11:44:05 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> create pfile='/home/oracle/initprima.ora' from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[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 ~]$ 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
initphyst.ora       100%  431     0.4KB/s   00:00    
[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
orapwprima          100% 1536     1.5KB/s   00:00    
[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*Plus: Release 11.2.0.3.0 Production on Mon Jul 8 12:07:30 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

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

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  238034944 bytes
Fixed Size		    2227136 bytes
Variable Size		  180356160 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    5120000 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

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;

Database altered.

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

Database altered.

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

Database altered.

[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

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwprima' auxiliary format 
 '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwphyst'   ;
}
executing Memory Script

Starting backup at 08-JUL-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
Finished backup at 08-JUL-13

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/home/oracle/physt/control01.ctl';
}
executing Memory Script

Starting backup at 08-JUL-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prima.f tag=TAG20130708T121004 RECID=3 STAMP=820239005
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 08-JUL-13

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/home/oracle/physt/temp01.dbt";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/home/oracle/physt/system01.dbf";
   set newname for datafile  2 to 
 "/home/oracle/physt/sysaux01.dbf";
   set newname for datafile  3 to 
 "/home/oracle/physt/undotbs01.dbf";
   set newname for datafile  4 to 
 "/home/oracle/physt/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/home/oracle/physt/system01.dbf"   datafile 
 2 auxiliary format 
 "/home/oracle/physt/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/home/oracle/physt/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/home/oracle/physt/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/physt/temp01.dbt in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 08-JUL-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/prima/system01.dbf
output file name=/home/oracle/physt/system01.dbf tag=TAG20130708T121013
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/prima/sysaux01.dbf
output file name=/home/oracle/physt/sysaux01.dbf tag=TAG20130708T121013
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/prima/undotbs01.dbf
output file name=/home/oracle/physt/undotbs01.dbf tag=TAG20130708T121013
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/prima/users01.dbf
output file name=/home/oracle/physt/users01.dbf tag=TAG20130708T121013
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 08-JUL-13

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=820239080 file name=/home/oracle/physt/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=820239080 file name=/home/oracle/physt/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=820239080 file name=/home/oracle/physt/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=820239080 file name=/home/oracle/physt/users01.dbf
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;

System altered.

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

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[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!” :-)

Addendum: I didn’t want to give the impression that you shouldn’t use Enterprise Manager for Data Guard – it is of course perfectly valid to do so. It is just my personal preference to maintain Data Guard on the Command Line. EM also triggers the Broker under the covers, pretty much like it triggers RMAN when you manage Backup & Recovery over EM. Check out this great article when you are interested in Data Guard administration with EM 12c Cloud Control: http://www.oracle.com/technetwork/articles/oem/havewala-odg-oem12c-1999410.html

About these ads

,

  1. #1 by alexandruneda on July 8, 2013 - 21:19

    Excellent post and a good reminder!

  2. #2 by Amit Saraswat on July 9, 2013 - 09:42

    Good Post, and a great refresher for setup.

  3. #3 by Stefan Koehler on July 9, 2013 - 09:42

    Hi Uwe,
    i totally agree, that Data Guard Broker should be the first choice in ordinary environments. In my experience there are/were two main cases when we are/were not able to use it.

    1) Different word-size across platforms (like heterogeneous primary and physical standbys in same data guard configuration), but luckily this restriction was removed from Data Guard 11g onward (but not for EM)

    2) Cascading standby databases, but luckily this restriction is removed from Oracle 12c onward as well (have not tested it until yet, but parameter RedoRoutes got it here)

    Regards
    Stefan

  4. #4 by Uwe Hesse on July 9, 2013 - 20:17

    Alexandruneda & Amit Saraswat, thank you for the nice feedback :-)
    Stefan, my impression was always that cascaded Standby DB is a rare animal and about to vanish completely – then came 12c with Broker support for it…

  5. #5 by ajay on July 10, 2013 - 16:42

    Thanks for this excellent post, it reminds a lot of what I actually forgot.

    Ajay.

  6. #6 by nagarjuna reddy on July 10, 2013 - 18:37

    Hi Uwe

    Thanks for the post, i always feel dataguard is a complex subject but you made the configuration of dg very easily understandable for novices like me. keep posting sir

  7. #7 by rajesh on July 17, 2013 - 19:18

    Hi Uwe,

    What parameter is need to shipping the archivelog to standby database, for particular time period to send.

  8. #8 by Uwe Hesse on July 19, 2013 - 19:13

    Ajay, nagarjuna reddy, you’re welcome! Thanks for taking the time to leave such a nice feedback :)

    rajesh, Archivelog shipping is deprecated in 11g. It is only seen for FAL (Fetch Archive Log) cases now. I suppose technically it works still with
    LOG_ARCHIVE_DEST_2=’service=physt db_unique_name=physt ARCH’ But don’t do it :)

  9. #9 by Ron on July 30, 2013 - 10:22

    Hi Uwe,
    I’ve built many a standby this way. However I’m currently using Grid 12c to do this. It’s very quick and simple and I get a working broker enabled configuration at the end of it. I just need to ensure the TNS entries are created up front.

  10. #10 by Uwe Hesse on August 13, 2013 - 09:43

    Hi Ron, thank you for sharing that information! Yes, already with earlier versions of the Enterprise Manager it was quite easy to setup & maintain a Data Guard Configuration. I just prefer to show that on the command line because a) it is more instructive b) many customers do not use Grid/Cloud Control

  11. #11 by Jay vardhan on September 3, 2013 - 15:48

    Hi Sir,

    We have been using Data Guard but not Data Guard Broker. May i please request what feature we can lack in this case.

  12. #12 by Uwe Hesse on September 4, 2013 - 08:50

    Jay vardhan,

    in terms of features: You cannot use Fast-Start Failover without the broker.
    A good summary about the benefits is also here:

    http://docs.oracle.com/cd/E16655_01/server.121/e17641/concepts.htm#i1013106

    Although that is 12c documentation, it applies to 11g as well.

  13. #13 by Srikant Gopalan on September 6, 2013 - 01:17

    Uwe,

    In a 11202 environment

    I had to have something like

    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)
    )

    (SID_DESC =
    (GLOBAL_DBNAME = physt_DGMGRL)
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    (SID_NAME = physt)
    )
    )

    on both primary and standby the listener.ora files to get the switchover working smoothly.

    OR ALTERNATIVELY

    Had to define static registration in the listener.ora like

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

    and set the DG Broker parameter StaticConnectIdentifier to
    ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = uhesse1)(PORT = 1521))) (CONNECT_DATA = (SID = prima) ) )’

    on both the Primary and Standby to get the switchover working smoothly.

    Any comments on this…..

  14. #14 by José Guilherme on June 2, 2014 - 22:24

    Hi Uwe

    We actually have a dataguard environment with only tow nodes, the primary and the stand by.
    But we are not using the Broker.
    Is it possible to activate the Broker on our databases? What are the implications?

    regards

  15. #15 by Uwe Hesse on June 3, 2014 - 13:21

    Hi Jose´, of course it is possible – just as shown in the posting. Only expected implications are that your environment is more reliable and easier to manage afterwards :-)

  16. #16 by mwasiq on July 1, 2014 - 10:15

    hi Uwe

    Thanks for your posting.Is it good to configure Dataguard broker in Production environment.
    I successfully configured in Test Env and added in EM12c which works perfectly and easy to monitor.

    Could you please suggest me about prod Env.

  17. #17 by konark on September 12, 2014 - 15:21

    Hi Owe,

    We are still using 10g in our env. So is it possible to use dataguard broker in it. If yes, what are the things that we should be careful about. Is there any changes in configuration of broker as well? Please help

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,743 other followers

%d bloggers like this: