Configuring Dataguard Broker 11.2

Hello Everyone!
I hope everyone is doing great!

This next article discusses how to setup Oracle Dataguard Broker for Oracle Database 11.2. Please visit my previous posts which shows how to setup Oracle Dataguard 11.2.

If you have questions and clarifications, please let me know and I will provide my feedback as soon as I can.

Thanks.

Let's share and make others love Oracle, too.

========================================================================

As stated in the Oracle Official Documentation, Oracle® Data Guard Broker 11g Release 2 (11.2)             Part Number E17023-08:

The Oracle Data Guard broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations. The following list describes some of the operations the broker automates and simplifies:

1.    Creating Data Guard configurations that incorporate a primary database, a new or existing (physical, logical, or snapshot) standby database, redo transport services, and log apply services, where any of the databases could be Oracle Real Application Clusters (Oracle RAC) databases.

2.    Adding additional new or existing (physical, snapshot, logical, Oracle RAC or non-Oracle RAC) standby databases to an existing Data Guard configuration, for a total of one primary database, and from 1 to 30 standby databases in the same configuration.

3.    Managing an entire Data Guard configuration, including all databases, redo transport services, and log apply services, through a client connection to any database in the configuration.

4.    Managing the protection mode for the broker configuration.

5.    Invoking switchover or failover with a single command to initiate and control complex role changes across all databases in the configuration.

6.    Configuring failover to occur automatically upon loss of the primary database, increasing availability without manual intervention.

7.    Monitoring the status of the entire configuration, capturing diagnostic information, reporting statistics such as the Redo Apply rate and the redo generation rate, and detecting problems quickly with centralized monitoring, testing, and performance tools.
7.

Follow the steps below to configure the Dataguard Broker:


1.    Set the parameter dg_broker_config_file1 and dg_broker_config_file2 on the both the primary and standby databases.

SQL> ALTER SYSTEM SET dg_broker_config_file1 = '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/banc01broker.dat' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET dg_broker_config_file2 = '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/banc02broker.dat' scope=spfile;

System altered.

2.    Enable dataguard broker on both primary and standby servers

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both;

System altered.

3.    Restart the database by following the section: Starting Up and Shutting Down the Data Guard Instance.
4.    On the primary server, connect to the database using DGMGRL

[oracle@server01 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.






5.    Create the configuration from the primary database server

DGMGRL> create configuration 'BANC01CONF' as primary
        database is 'banc01' connect identifier is banc01;
Configuration "BANC01CONF" created with primary database "banc01"
DGMGRL> add database 'banc02' as connect identifier is
        banc02 maintained as physical;
Database "banc02" added
DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;

Configuration - BANC01CONF

  Protection Mode: MaxPerformance
  Databases:
    banc01 - Primary database
    banc02 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

6.    Your configuration files are stored inside $ORACLE_HOME/dbs on both servers.

[oracle@server01 dbs]$ cd $ORACLE_HOME/dbs
[oracle@server01 dbs]$ ls -ltr
total 17196
-rw-r--r-- 1 oracle oinstall     2851 May 15  2009 init.ora
-rw-r----- 1 oracle oinstall     3584 Dec 29 16:57 spfilebanc01.ora.orig
-rw-rw---- 1 oracle oinstall     1544 Dec 29 18:39 hc_DBUA0.dat
-rw-r----- 1 oracle oinstall       24 Dec 29 18:46 lkBANC01
-rw-r--r-- 1 oracle oinstall     1510 Dec 29 20:17 initbanc01.ora
-rw-r----- 1 oracle oinstall 17514496 Dec 29 20:32 snapcf_banc01.f
-rw-r----- 1 oracle oinstall     1536 Dec 29 20:44 orapwbanc01
-rw-rw---- 1 oracle oinstall     1544 Feb 11 14:52 hc_banc01.dat
-rw-r----- 1 oracle oinstall    16384 Feb 11 14:52 banc01broker.dat
-rw-r----- 1 oracle oinstall     5632 Feb 11 14:52 spfilebanc01.ora

-rw-r----- 1 oracle oinstall    16384 Feb 11 15:05 banc02broker.dat


Managing Data Guard using the Broker


1.    Starting up the database instance

On the primary server:

[oracle@server01 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> startup
ORACLE instance started.
Database mounted.
Database opened.

On the standby server:

[oracle@server02 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> startup mount
ORACLE instance started.
Database mounted.




2.    Shutting down the database instance

On the primary database:

[oracle@server01 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

On the standby database:


[oracle@server02 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.






3.    Performing database switchover

Follow the steps shown in the section Role Transitions to validate redo log transport and apply before doing the switchover.

Check the primary database:

DGMGRL> show database verbose 'banc01';

Database - banc01

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    banc01

  Properties:
    DGConnectIdentifier             = 'banc01'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    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               = '/u02/oracle/oradata/banc02, /u02/oracle/oradata/banc01'
    LogFileNameConvert              = '/u02/oracle/oradata/banc02, /u02/oracle/oradata/banc01'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'server01.exchange.com'
    SidName                         = 'banc01'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server01.exchange.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=banc01_DGMGRL)(INSTANCE_NAME=banc01)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/archivelogs/banc01'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arch'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS


Check the standby database:

DGMGRL> show database verbose 'banc02';

Database - banc02

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

  Properties:
    DGConnectIdentifier             = 'banc02'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    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               = '/banc01/, /banc02/'
    LogFileNameConvert              = '/banc01/, /banc02/'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'server02.exchange.com'
    SidName                         = 'banc02'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server02.exchange.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=banc02_DGMGRL)(INSTANCE_NAME=banc02)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/archivelogs/banc02'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arch'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS





4.    Issue the switchover command on the primary server

DGMGRL> switchover to 'banc02';
Performing switchover NOW, please wait...
New primary database "banc02" is opening...
Operation requires shutdown of instance "banc01" on database "banc01"
Shutting down instance "banc01"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "banc01" on database "banc01"
Starting instance "banc01"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
     start up and mount instance "banc01" of database "banc01"

DGMGRL> quit     
[oracle@server01 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> startup mount
ORACLE instance started.
Database mounted.



DGMGRL> show database verbose 'banc02';

Database - banc02

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    banc02

  Properties:
    DGConnectIdentifier             = 'banc02'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    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               = '/banc01/, /banc02/'
    LogFileNameConvert              = '/banc01/, /banc02/'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'server02.exchange.com'
    SidName                         = 'banc02'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server02.exchange.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=banc02_DGMGRL)(INSTANCE_NAME=banc02)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/archivelogs/banc02'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arch'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

Comments

Post a Comment

Popular posts from this blog

RMAN Restoration to New Server with Different Directory and New Database Name

In this case, I have an existing production database with RMAN Level 0 backup: CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\flash_recovery_area\galaxy\backupsets\autobackup\SAMSUNG-ctl_%F'; CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\flash_recovery_area\galaxy\backupsets\level0\level0-%d-%T-%U' MAXPIECESIZE 4G; DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 7'; SHOW ALL; REPORT SCHEMA; LIST BACKUP OF DATABASE; REPORT NEED BACKUP; REPORT UNRECOVERABLE; LIST EXPIRED BACKUP BY FILE; LIST ARCHIVELOG ALL; REPORT OBSOLETE; CROSSCHECK BACKUP DEVICE TYPE DISK; CROSSCHECK COPY OF ARCHIVELOG ALL; DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK; DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK; RUN { sql 'alter system archive log current'; BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG DELETE INPUT; } LIST BACKUP ...
Read more

[Script] Tablespace Usage Alert

Hi, This is another variation of the previous scripts presented in this blog site. The building blocks of this script is the same as the Disk Space and ASM Diskgroup Space Monitoring scripts. This will alert you via email if a certain tablespace  reaches the set threshold. Let's share and make others love Oracle, too. For tablespace monitoring alert scripts. We have the following: Tablespace Monitoring Script You will notice that there is a pattern in the scripts creation presented in this blog.  Part 1: Report Generation : Here it is the tablespace report Part 2: Report output string process : Scripts are created to process further report into columns. Conditional statement was created to test the values in the report. Part 3: Check if the file generates alert : Email is sent if entities met the threshold. # # # # # #----- Start of Script -----###### #!/bin/bash  # Report tablespace usage  sqlplus "/ as sysdba" << EOF SET LINES 700...
Read more

[Script] ASM Diskgroup Space Usage Alert

Hi Again, Another important task that a DBA should do is to monitor the ASM diskgroup usage. We do not just add space to tablespaces, create more tablespaces or resize datafiles to increase storage without checking if there is space to our ASM diskgroup. Also, when we perform RMAN backups stored in ASM or Datapump Exports stored in ASM, we have to monitor and get alerts if we are running out of space. This is a simple monitoring script. I hope this could help you. Let's share and make others love Oracle, too. ASM Diskgroup  Space Monitoring ######--- Start of Script -----###### #!/bin/bash  # Report ASM Diskgroup space usage  #NOTES:  # 1. There is really a slash on the v$asm_diskgroup so Unix interpreter will treat dollar sign as a literal $ # 2. Set your tablespace threshold by changing the value of the AND clause  " >= 80" sqlplus "/ as sysdba" << EOF SET LINES 700 SET FEEDBACK OFF SET TRIMSPOOL ON SET ECHO OFF SPOOL /tmp/ASMs...
Read more