Oracle DBA – A lifelong learning experience

Configuring Dataguard Broker in 11G

Posted by John Hallas on October 12, 2009

DataGuard Broken?

A guest blog from colleague Simon Ryan

Last year we did some sandpit work on implementing DataGuard.  This was 10g on a Windows platform.  When it came to the broker we didn’t have much luck.  At the UKOUG Michael Moller referred to the Broker as DataGuard Broken (DataGuard 11g New Features).  From the audience reaction this seemed to be a popular perception.

Since then we’ve successfully implemented DataGuard on most of our Production databases (both 10g and 11g) on HP-UX.  However, we’ve still not embraced the Broker!  The ‘broken’ tag still taints its reputation.  A recent visit by Oracle attempted to assure us that the Broker with 11g is much improved.

The following is a basic guide to implementing the broker and our findings:

Our Setup

o       Primary – Two node RAC Cluster (XXXPRD1A)

o       Physical Standby – Single node (XXXPRD1B)

o       HP-UX 11.31 Itanium

o       Database using ASM (both 11.1.0.7).

o       DataGuard running in Max Availability with Real Time Apply.

Configuration

Add the following entries to tnsnames.ora on all instances:

 

XXXPRD1A_DGMGRL =

   (DESCRIPTION=

     (ADDRESS=(PROTOCOL=tcp)(HOST=xxxem03-vip)(PORT=1526))

     (CONNECT_DATA=

       (SERVICE_NAME=XXXPRD1A_DGMGRL)

     )

    )

 

XXXPRD2A_DGMGRL =

   (DESCRIPTION=

     (ADDRESS=(PROTOCOL=tcp)(HOST=xxxem04-vip)(PORT=1526))

     (CONNECT_DATA=

       (SERVICE_NAME=XXXPRD2A_DGMGRL)

     )

    )

 

XXXPRD1B_DGMGRL =

   (DESCRIPTION=

     (ADDRESS=(PROTOCOL=tcp)(HOST=xxxem01-vip)(PORT=1526))

     (CONNECT_DATA=

       (SERVICE_NAME=XXXPRD1B_DGMGRL)

     )

    )

 

Add the following entry to SID_LIST of your listener in listener.ora on all instances:

 

 (SID_DESC = 

      (ORACLE_HOME = /app/oracle/product/11.1.0/db_1)

      (SID_NAME = XXXPRDxx)

      (GLOBAL_DBNAME = XXXPRDxx_DGMGRL)

    )

 Stop/start listener.

 Test you can now connect:

 

 sqlplus sys@XXXPRD2A_dgmgrl as sysdba

  

Make the following parameter changes:

 

 XXXPRD1A:
<pre>
 

alter system set dg_broker_start=true scope=spfile;

alter system set dg_broker_config_file1='+DATA/XXXPRD1A/broker1.dat' scope=spfile;

alter system set dg_broker_config_file2='+FRA/XXXPRD1A/broker2.dat' scope=spfile;

 XXXPRD1B:

alter system set dg_broker_start=true scope=spfile;

alter system set dg_broker_config_file1='+DATA/XXXPRD1B/broker1.dat' scope=spfile;

alter system set dg_broker_config_file2='+FRA/XXXPRD1B/broker2.dat' scope=spfile; 

 Stop/Start the databases:

 

 Stop XXXPRD1A:
<pre>
 

srvctl stop database -d XXXPRD1A -o immediate

 Stop XXXPRD1B:

shutdown immediate

 Start XXXPRD1B:

startup mount

 Start XXXPRD1A:

srvctl start database -d XXXPRD1A

 

 Check for the broker process:

 

 XXXPRD1A:
<pre>
 

ps –ef | grep ora_dmon_XXXPRD1A

XXXPRD1B:

ps –ef | grep ora_dmon_XXXPRD1B

 

Create DG Broker Configuration:

 

1 Primary:

dgmgrl

connect sys

<em>enter sys password</em>

 

create configuration XXXPRD1A_DGCONFIG as primary database is “XXXPRD1A” connect identifier is XXXPRD1A;

Configuration "XXXPRD1A_dgconfig" created with primary database "XXXPRD1A"

 

add database “XXXPRD1B” as connect identifier is XXXPRD1B maintained as physical;

Database "XXXPRD1B" added

 

enable configuration;

Enabled.

 

show configuration;

 

Configuration

  Name:                XXXPRD1A_dgconfig

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Databases:

    XXXPRD1A - Primary database

    XXXPRD1B - Physical standby database

 

Fast-Start Failover: DISABLED

 

Current status for "XXXPRD1A_dgconfig":

SUCCESS

 

 Check Broker Files Exist:

Primary and Standby ASM:

Check they are there.

 

Show Database:

 Primary: 

show database verbose “XXXPRD1A”;

 

Database

  Name:            XXXPRD1A

  Role:            PRIMARY

  Enabled:         YES

  Intended State:  TRANSPORT-ON

  Instance(s):

    XXXPRD1A

    XXXPRD2A

 

  Properties:

    DGConnectIdentifier             = 'XXXPRD1A'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'SYNC'

    DelayMins                       = '0'

    Binding                         = 'OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '10'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = 'XXXPRD1B, XXXPRD1A'

    LogFileNameConvert              = 'XXXPRD1B, XXXPRD1A'

    FastStartFailoverTarget         = ''

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName(*)

    SidName(*)

    StaticConnectIdentifier(*)

    StandbyArchiveLocation(*)

    AlternateLocation(*)

    LogArchiveTrace(*)

    LogArchiveFormat(*)

    LatestLog(*)

    TopWaitEvents(*)

    (*) - Please check specific instance for the property value

 

Current status for "XXXPRD1A":

SUCCESS

 

Standby:

show database verbose “XXXPRD1B”;

 

Database

  Name:            XXXPRD1B

  Role:            PHYSICAL STANDBY

  Enabled:         YES

  Intended State:  APPLY-ON

  Instance(s):

    XXXPRD1B

 

  Properties:

    DGConnectIdentifier             = 'XXXPRD1B'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'SYNC'

    DelayMins                       = '0'

    Binding                         = 'OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '60'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '10'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = 'XXXPRD1A, XXXPRD1B'

    LogFileNameConvert              = 'XXXPRD1A, XXXPRD1B'

    FastStartFailoverTarget         = ''

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'xxxem01'

    SidName                         = 'XXXPRD1B'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxem01)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=XXXPRD1B_DGMGRL)(INSTANCE_NAME=XXXPRD1B)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    LatestLog                       = '(monitor)'

    TopWaitEvents                   = '(monitor)'

 

Current status for "XXXPRD1B":

SUCCESS

 Ensure logs are still being transported and applied

All still works!

 

What does it give us?

  • Improved monitoring in OEM.
  • Simpler administration via DGMGRL or OEM i.e. changing Protection Mode.
  • Easy switchover/failover
    • DGMGRL: switchover to “XXXPRD1A”
    • Click of a button in OEM.
  • Fast-Start Failover (with an observer).

 

Conclusions

The broker configuration was relatively painless and I have successfully performed a switchover via both DGMGRL and OEM.  I’ve also used the various OEM functions without issue too.  I’m uneasy about performing such tasks via OEM but then most DBA’s prefer to be at the helm via the command line rather than clicking a button (that’s too easy!). 

If you plan to use DataGuard Broker then embrace it and make sure you don’t alter parameters behind the covers.  It does that for you and it doesn’t like you meddling!  I also think it’s a good idea to setup DataGuard initially without the broker and then configure it once you’re happy that DataGuard is working.

So what’s made the difference since our experience last year?  UNIX v Windows (maybe), 10g v 11g (probably).  A big factor would also have to be our experience level.  In the last year we’ve increased our DataGuard count from zero to ten so we are therefore much more comfortable in the DataGuard world.

When a sand-pit database becomes available I’d like to try and setup the observer and test Fast-Start Failover.  I’m inclined to remove the ‘broken’ tag but I’ll reserve judgment until I’ve tested the remaining functions.

Simon Ryan

About these ads

6 Responses to “Configuring Dataguard Broker in 11G”

  1. [...] Configuring Dataguard Broker in 11G « Oracle DBA – A lifelong learning experience jhdba.wordpress.com/2009/10/12/configuring-dataguard-broker-in-11g – view page – cached Last year we did some sandpit work on implementing DataGuard. This was 10g on a Windows platform. When it came to the broker we didn’t have much luck. At the UKOUG Michael Moller referred to the… (Read more)Last year we did some sandpit work on implementing DataGuard. This was 10g on a Windows platform. When it came to the broker we didn’t have much luck. At the UKOUG Michael Moller referred to the Broker as DataGuard Broken (DataGuard 11g New Features). From the audience reaction this seemed to be a popular perception. (Read less) — From the page [...]

  2. thanks for the post, i had some unpleasant experiences with data guard broker with 10g, so as you said it’s not my choice to switchover with just clicking a button :) But if “Broker with 11g is much improved” is correct, maybe it’s time to be ready to face with broker again:)

    • John Hallas said

      Thanks Emre, certainly 10g was not good at all and it seems quite neat in 11g now. Well worth trying out.
      Thanks for reading the blog

      John

  3. dbametrix said

    Hi,

    Very interesting. Nice article and discussion.
    Thanks a lot for same.

    Regards,
    Gitesh Trivedi

    http://www.dbametrix.com

  4. [...] Simon Ryan-Configuring Dataguard Broker in 11G [...]

  5. Thanks for the post. It helped me so much for I am also having problems unpleasant experiences with data guard broker with 10g, so as you said it’s not my choice to switchover with just clicking a button, but if “Broker with 11g is much improved” is correct, maybe it’s time to be ready to face with broker again.

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 203 other followers

%d bloggers like this: