Data Guard Broker configuration with failover and switchover testing

Preamble

Even if you can manage your Data Guard configuration with SQL commands it is strongly suggested to use Data Guard Broker (DGB) as its command line tool dgmgrl ease a lot day-to-day administration and monitoring.

The product is apparently a bit buggy in 10g but except few remaining *strange* things product is now quite efficient in 11gR2.

This article is the follow-up of previous post on creating a physical standby database.

Please note that once you have decided to start using DGB you cannot use anymore SQL commands as DGB takes preference.

Data Guard Broker configuration

On primary and all your standby database set dg_broker_start parameter to true:

SQL> show parameter dg
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /ora_rac/software/dbs/dr1rac1.
                                                 dat
dg_broker_config_file2               string      /ora_rac/software/dbs/dr2rac1.
                                                 dat
dg_broker_start                      BOOLEAN     FALSE
SQL> ALTER SYSTEM SET dg_broker_start=TRUE;
 
SYSTEM altered.

One of the most *strange* requirement for Data Guard Broker is the need to have all listener listening for a global database name made of <db_name>_DGMGRL.<db_domain> which means for our environment the following listener.ora file (do crossover configuration of all your standby instances for failover and switchover capabilities):

LISTENER_RAC1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_RAC1))))
 
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_RAC1=ON
 
SID_LIST_LISTENER_RAC1 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = rac1.world)
      (ORACLE_HOME = /ora_rac/software)
      (SID_NAME = rac1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = rac1_DGMGRL.world)
      (ORACLE_HOME = /ora_rac/software)
      (SID_NAME = rac1)
    )
  )
[orarac@server1 ~]$ lsnrctl status listener_rac1
 
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 26-MAY-2011 16:10:22
 
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_RAC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_RAC1
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                26-MAY-2011 14:12:44
Uptime                    0 days 1 hr. 57 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora_rac/software/network/admin/listener.ora
Listener Log File         /ora_rac/software/log/diag/tnslsnr/server1/listener_rac1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_RAC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1541)))
Services Summary...
Service "rac1.world" has 2 instance(s).
  Instance "rac1", status UNKNOWN, has 1 handler(s) for this service...
  Instance "rac1", status READY, has 1 handler(s) for this service...
Service "rac1_DGMGRL.world" has 1 instance(s).
  Instance "rac1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Enter is Data Guard Broker command line interface and create the configuration:

[orarac@server1 admin]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
 
Copyright (c) 2000, 2009, Oracle. All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/password
Connected.
DGMGRL> create configuration 'DGConfig1' as
primary database is 'rac1'
connect identifier is rac1;
Configuration "DGConfig1" created with primary database "rac1"
DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
DISABLED
 
DGMGRL> add database 'rac2' as
connect identifier is rac2;
Database "rac2" added
DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
    rac2 - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
DISABLED
 
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
    rac2 - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with database setting
 
Fast-Start Failover: DISABLED
 
Configuration Status:
WARNING

If you get few inconsistencies, no issue, correct them with:

DGMGRL> show database 'rac2' InconsistentProperties;
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
            rac2     ArchiveLagTarget                    0                                         0
            rac2 LogArchiveMaxProcesses                    4                                         4
            rac2 LogArchiveMinSucceedDest                    1                                         1
            rac2      LogArchiveTrace                    0            (missing)                    0
            rac2     LogArchiveFormat         %t_%s_%r.dbf            (missing)         %t_%s_%r.dbf
 
DGMGRL> edit database 'rac2' set property 'ArchiveLagTarget'=0;
Property "ArchiveLagTarget" updated
DGMGRL>  edit database 'rac2' set property 'LogArchiveMaxProcesses'=4;
Property "LogArchiveMaxProcesses" updated
DGMGRL> edit database 'rac2' set property 'LogArchiveMinSucceedDest'=1;
Property "LogArchiveMinSucceedDest" updated
DGMGRL> edit database 'rac2' set property 'LogArchiveTrace'=0;
Property "LogArchiveTrace" updated
DGMGRL>  edit database 'rac2' set property 'LogArchiveFormat'='%t_%s_%r.dbf';
Property "LogArchiveFormat" updated
DGMGRL> show database 'rac2' InconsistentProperties;
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

But if all went fine when creating physical standby database you should directly get the below configuration:

DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection MODE: MaxPerformance
  Databases:
    rac1 - PRIMARY DATABASE
    rac2 - Physical standby DATABASE
 
Fast-START Failover: DISABLED
 
Configuration Status:
SUCCESS
 
DGMGRL>

To get detailed status of a database:

DGMGRL> show database verbose rac2;
 
Database - rac2
 
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    rac2
 
  Properties:
    DGConnectIdentifier             = 'rac2'
    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          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'rac2'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)(PORT=1541))(CONNECT_DATA=(SERVICE_NAME=rac2_DGMGRL.world)(INSTANCE_NAME=rac2)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'
 
Database Status:
SUCCESS

Remark:
Strangely one service is added to your listeners configuration i.e. <db_name>_DGB.<db_domain> service is added (Oracle preparing next step ?):

[orarac@server1 ~]$ lsnrctl status listener_rac1
 
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 26-MAY-2011 16:17:12
 
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_RAC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_RAC1
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                26-MAY-2011 14:12:44
Uptime                    0 days 2 hr. 4 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora_rac/software/network/admin/listener.ora
Listener Log File         /ora_rac/software/log/diag/tnslsnr/server1/listener_rac1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_RAC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1541)))
Services Summary...
Service "rac1.world" has 2 instance(s).
  Instance "rac1", status UNKNOWN, has 1 handler(s) for this service...
  Instance "rac1", status READY, has 1 handler(s) for this service...
Service "rac1_DGB.world" has 1 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service...
Service "rac1_DGMGRL.world" has 1 instance(s).
  Instance "rac1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Switchover

You start with a configuration like the following:

[orarac@server1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
 
Copyright (c) 2000, 2009, Oracle. All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/password
Connected.
DGMGRL> show configuration verbose
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
    rac2 - Physical standby database
 
  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

To test it create first a test database on the primary instance (rac1):

SQL> CREATE TABLE yjaquier.test(val NUMBER, descr VARCHAR2(20));
 
TABLE created.
 
SQL> INSERT INTO yjaquier.test VALUES(1,'One');
 
1 ROW created.
 
SQL> INSERT INTO yjaquier.test VALUES(2,'Two');
 
1 ROW created.
 
SQL> INSERT INTO yjaquier.test VALUES(3,'Three');
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT * FROM yjaquier.test;
 
       VAL DESCR
---------- --------------------
         1 One
         2 Two
         3 Three

Switchover is as simple as one command:

DGMGRL> switchover to rac2
Performing switchover NOW, please wait...
New primary database "rac2" is opening...
Operation requires shutdown of instance "rac1" on database "rac1"
Shutting down instance "rac1"...
ORACLE instance shut down.
Operation requires startup of instance "rac1" on database "rac1"
Starting instance "rac1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "rac2"
DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac2 - Primary database
    rac1 - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

Rac2 instance is now your new primary instance and you can connect to it and check that your test table is there with its rows:

SQL> SELECT * FROM yjaquier.test;
 
       VAL DESCR
---------- --------------------
         1 One
         2 Two
         3 Three

Then you can go back to initial configuration with:

DGMGRL> switchover to rac1
Performing switchover NOW, please wait...
New primary database "rac1" is opening...
Operation requires shutdown of instance "rac2" on database "rac2"
Shutting down instance "rac2"...
ORACLE instance shut down.
Operation requires startup of instance "rac2" on database "rac2"
Starting instance "rac2"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "rac1"
DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
    rac2 - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

Failover

This option should be your last choice and you must first try to recover your primary database. If this cannot be done or not done within a correct time frame you can perform a (complete) failover. Please note that your initial primary will be lost (you will need to reinstate or recreate it from backup).

First you need to remove your primary database from Oracle restart configuration to avoid an automatic startup. To check current status use:

[oracrs@server1 ~]$ srvctl config database -d rac1 -a
Database unique name: rac1
Database name: rac1
Oracle home: /ora_rac/software
Oracle user: oracrs
Spfile: +DATA/rac1/spfilerac1.ora
Domain: world
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services:
Database is enabled

Disable automatic restart with:

[oracrs@server1 ~]$ srvctl disable database -d rac1
[oracrs@server1 ~]$ srvctl config database -d rac1 -a
Database unique name: rac1
Database name: rac1
Oracle home: /ora_rac/software
Oracle user: oracrs
Spfile: +DATA/rac1/spfilerac1.ora
Domain: world
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services:
Database is disabled
[orarac@server1 ~]$ ps -ef | grep pmon | grep -v grep
oracrs    3520     1  0 May24 ?        00:00:49 asm_pmon_+ASM
orarac   14138     1  0 May26 ?        00:00:12 ora_pmon_rac1
[orarac@server1 ~]$ kill -9 14138
[orarac@server1 ~]$ ps -ef | grep orarac
root      1555  1389  0 May26 pts/2    00:00:00 su - orarac
orarac    1556  1555  0 May26 pts/2    00:00:00 -bash
orarac   12423  1556  0 10:28 pts/2    00:00:00 ps -ef
orarac   12424  1556  0 10:28 pts/2    00:00:00 grep orarac
orarac   14036     1  0 May26 ?        00:00:00 oraclerac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Your DGB configuration will now shows:

DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
    rac2 - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
ORA-01034: ORACLE not available
ORA-16625: cannot reach database "rac1"
DGM-17017: unable to determine configuration status

Failover is simply:

DGMGRL> failover to rac2;
Performing failover NOW, please wait...
Failover succeeded, new primary is "rac2"

Now your old standby is the new primary and, as expected, the original primary needs to be reinstated:

DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac2 - Primary database
    rac1 - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

We can try to reinstate it with:

DGMGRL> reinstate database rac1;
Reinstating database "rac1", please wait...
Error: ORA-16653: failed to reinstate database
 
Failed.
Reinstatement of database "rac1" failed

But failing and as explained in Oracle note we would have needed to configure flashback database to be able to do so automatically…

If you try to start rac1 database in MOUNT state and issue again a request to reinstate you will now get clear error message:

DGMGRL> reinstate database rac1;
Reinstating database "rac1", please wait...
Error: ORA-16827: Flashback Database is disabled
 
Failed.
Reinstatement of database "rac1" failed
DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac2 - Primary database
    rac1 - Physical standby database (disabled)
      ORA-16795: the standby database needs to be re-created
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

Message is pretty clear and we will use an RMAN backup to recreate the original primary database. We will create a backup on a local filesystem and transfer it to other server (in same directory). It would be obviously more convenient to have a NFS mount point and be able to share backupset between servers:

[orarac@server2 dbs]$ rman target /
 
Recovery Manager: Release 11.2.0.2.0 - Production on Fri May 27 14:21:03 2011
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: RAC1 (DBID=656357419)
 
RMAN> run {
allocate channel disk1 type disk;
backup as compressed backupset database format '/var/tmp/failover_%U.bck' tag 'failover';
backup as compressed backupset current controlfile for standby format '/var/tmp/failover_%U.bck' tag 'failover';
sql 'alter system archive log current';
backup as compressed backupset format '/var/tmp/failover_%U.bck' archivelog all tag 'failover';
release channel disk1;
}
 
using target database control file instead of recovery catalog
allocated channel: disk1
channel disk1: SID=35 device type=DISK
 
Starting backup at 27-MAY-11
channel disk1: starting compressed full datafile backup set
channel disk1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/rac2/datafile/system.257.752168731
input datafile file number=00002 name=+DATA/rac2/datafile/sysaux.256.752168733
input datafile file number=00003 name=+DATA/rac2/datafile/undotbs1.259.752168801
input datafile file number=00004 name=+DATA/rac2/datafile/users.258.752168801
channel disk1: starting piece 1 at 27-MAY-11
channel disk1: finished piece 1 at 27-MAY-11
piece handle=/var/tmp/failover_0jmdcr6n_1_1.bck tag=FAILOVER comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:26
channel disk1: starting compressed full datafile backup set
channel disk1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel disk1: starting piece 1 at 27-MAY-11
channel disk1: finished piece 1 at 27-MAY-11
piece handle=/var/tmp/failover_0kmdcr7h_1_1.bck tag=FAILOVER comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-MAY-11
 
Starting backup at 27-MAY-11
channel disk1: starting compressed full datafile backup set
channel disk1: specifying datafile(s) in backup set
including standby control file in backup set
channel disk1: starting piece 1 at 27-MAY-11
channel disk1: finished piece 1 at 27-MAY-11
piece handle=/var/tmp/failover_0lmdcr7p_1_1.bck tag=FAILOVER comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:03
Finished backup at 27-MAY-11
 
sql statement: alter system archive log current
 
Starting backup at 27-MAY-11
current log archived
channel disk1: starting compressed archived log backup set
channel disk1: specifying archived log(s) in backup set
input archived log thread=1 sequence=81 RECID=2 STAMP=752169049
input archived log thread=1 sequence=82 RECID=1 STAMP=752169049
input archived log thread=1 sequence=83 RECID=3 STAMP=752169081
input archived log thread=1 sequence=84 RECID=4 STAMP=752169986
input archived log thread=1 sequence=85 RECID=5 STAMP=752170022
input archived log thread=1 sequence=86 RECID=6 STAMP=752170030
input archived log thread=1 sequence=87 RECID=7 STAMP=752170030
input archived log thread=1 sequence=88 RECID=8 STAMP=752170389
input archived log thread=1 sequence=89 RECID=9 STAMP=752170705
input archived log thread=1 sequence=90 RECID=10 STAMP=752170711
input archived log thread=1 sequence=91 RECID=11 STAMP=752170721
input archived log thread=1 sequence=92 RECID=12 STAMP=752170722
input archived log thread=1 sequence=93 RECID=13 STAMP=752170741
input archived log thread=1 sequence=94 RECID=17 STAMP=752170766
input archived log thread=1 sequence=95 RECID=20 STAMP=752170796
input archived log thread=1 sequence=96 RECID=22 STAMP=752171712
input archived log thread=1 sequence=97 RECID=23 STAMP=752171717
input archived log thread=1 sequence=98 RECID=26 STAMP=752171759
input archived log thread=1 sequence=99 RECID=27 STAMP=752171760
input archived log thread=1 sequence=100 RECID=25 STAMP=752171759
input archived log thread=1 sequence=101 RECID=28 STAMP=752171761
input archived log thread=1 sequence=102 RECID=29 STAMP=752171765
input archived log thread=1 sequence=103 RECID=30 STAMP=752171788
input archived log thread=1 sequence=104 RECID=31 STAMP=752173007
input archived log thread=1 sequence=105 RECID=32 STAMP=752173020
input archived log thread=1 sequence=106 RECID=33 STAMP=752173144
input archived log thread=1 sequence=107 RECID=34 STAMP=752173149
input archived log thread=1 sequence=108 RECID=35 STAMP=752176484
input archived log thread=1 sequence=109 RECID=36 STAMP=752176488
input archived log thread=1 sequence=110 RECID=37 STAMP=752198466
input archived log thread=1 sequence=111 RECID=40 STAMP=752236328
channel disk1: starting piece 1 at 27-MAY-11
channel disk1: finished piece 1 at 27-MAY-11
piece handle=/var/tmp/failover_0mmdcr8c_1_1.bck tag=FAILOVER comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:07
channel disk1: starting compressed archived log backup set
channel disk1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=39 STAMP=752236292
input archived log thread=1 sequence=2 RECID=38 STAMP=752236292
input archived log thread=1 sequence=3 RECID=41 STAMP=752237116
input archived log thread=1 sequence=4 RECID=42 STAMP=752250116
input archived log thread=1 sequence=5 RECID=43 STAMP=752250116
channel disk1: starting piece 1 at 27-MAY-11
channel disk1: finished piece 1 at 27-MAY-11
piece handle=/var/tmp/failover_0nmdcr8j_1_1.bck tag=FAILOVER comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-MAY-11
 
released channel: disk1

You can verify it with:

RMAN> list backup;
 
using target database control file instead of recovery catalog
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    59.73M     DISK        00:00:18     27-MAY-11
        BP Key: 5   Status: AVAILABLE  Compressed: YES  Tag: FAILOVER
        Piece Name: /var/tmp/failover_0jmdcr6n_1_1.bck
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 776663     27-MAY-11 +DATA/rac2/datafile/system.257.752168731
  2       Full 776663     27-MAY-11 +DATA/rac2/datafile/sysaux.256.752168733
  3       Full 776663     27-MAY-11 +DATA/rac2/datafile/undotbs1.259.752168801
  4       Full 776663     27-MAY-11 +DATA/rac2/datafile/users.258.752168801
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    1.05M      DISK        00:00:03     27-MAY-11
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: FAILOVER
        Piece Name: /var/tmp/failover_0kmdcr7h_1_1.bck
  SPFILE Included: Modification time: 27-MAY-11
  SPFILE db_unique_name: RAC2
  Control File Included: Ckp SCN: 776687       Ckp time: 27-MAY-11
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    1.05M      DISK        00:00:04     27-MAY-11
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: FAILOVER
        Piece Name: /var/tmp/failover_0lmdcr7p_1_1.bck
  Standby Control File Included: Ckp SCN: 776714       Ckp time: 27-MAY-11
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8       16.76M     DISK        00:00:05     27-MAY-11
        BP Key: 8   Status: AVAILABLE  Compressed: YES  Tag: FAILOVER
        Piece Name: /var/tmp/failover_0mmdcr8c_1_1.bck
 
  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    81      605606     26-MAY-11 608163     26-MAY-11
  1    82      608163     26-MAY-11 608247     26-MAY-11
  1    83      608247     26-MAY-11 608496     26-MAY-11
  1    84      608496     26-MAY-11 609567     26-MAY-11
  1    85      609567     26-MAY-11 609607     26-MAY-11
  1    86      609607     26-MAY-11 609612     26-MAY-11
  1    87      609612     26-MAY-11 609617     26-MAY-11
  1    88      609617     26-MAY-11 609990     26-MAY-11
  1    89      609990     26-MAY-11 610821     26-MAY-11
  1    90      610821     26-MAY-11 630832     26-MAY-11
  1    91      630832     26-MAY-11 630835     26-MAY-11
  1    92      630835     26-MAY-11 630841     26-MAY-11
  1    93      630841     26-MAY-11 630948     26-MAY-11
  1    94      630948     26-MAY-11 630971     26-MAY-11
  1    95      630971     26-MAY-11 631035     26-MAY-11
  1    96      631035     26-MAY-11 632269     26-MAY-11
  1    97      632269     26-MAY-11 652279     26-MAY-11
  1    98      652279     26-MAY-11 652282     26-MAY-11
  1    99      652282     26-MAY-11 652289     26-MAY-11
  1    100     652289     26-MAY-11 652395     26-MAY-11
  1    101     652395     26-MAY-11 652403     26-MAY-11
  1    102     652403     26-MAY-11 652418     26-MAY-11
  1    103     652418     26-MAY-11 652469     26-MAY-11
  1    104     652469     26-MAY-11 653997     26-MAY-11
  1    105     653997     26-MAY-11 654034     26-MAY-11
  1    106     654034     26-MAY-11 654180     26-MAY-11
  1    107     654180     26-MAY-11 654189     26-MAY-11
  1    108     654189     26-MAY-11 678241     26-MAY-11
  1    109     678241     26-MAY-11 678249     26-MAY-11
  1    110     678249     26-MAY-11 710680     27-MAY-11
  1    111     710680     27-MAY-11 757966     27-MAY-11 (TERMINAL)
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9       3.02M      DISK        00:00:01     27-MAY-11
        BP Key: 9   Status: AVAILABLE  Compressed: YES  Tag: FAILOVER
        Piece Name: /var/tmp/failover_0nmdcr8j_1_1.bck
 
  List of Archived Logs in backup set 9
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1       757966     27-MAY-11 757969     27-MAY-11
  1    2       757969     27-MAY-11 757976     27-MAY-11
  1    3       757976     27-MAY-11 759115     27-MAY-11
  1    4       759115     27-MAY-11 776732     27-MAY-11
  1    5       776732     27-MAY-11 776744     27-MAY-11

Start in NOMOUNT state the original primary database. The spfile to use is either the original one (that you should still have) or a copy of the standby initialization parameters file that you modify to match the original primary instance environment:

[orarac@server1 /]$ sqlplus / AS sysdba
 
SQL*Plus: RELEASE 11.2.0.2.0 Production ON Fri May 27 14:33:25 2011
 
Copyright (c) 1982, 2010, Oracle.  ALL rights reserved.
 
Connected TO an idle instance.
 
SQL> startup nomount
ORACLE instance started.
 
Total SYSTEM Global Area  208769024 bytes
Fixed SIZE                  2225064 bytes
Variable SIZE             167775320 bytes
DATABASE Buffers           33554432 bytes
Redo Buffers                5214208 bytes

Remote copy backupset files to server1 in exact same target directory i.e. /var/tmp in our case (or better use a NFS shared mount point).

Duplicate rac2 instance to recreate rac1 instance with following RMAN script:

[orarac@server1 /]$ rman target sys/password@rac2 auxiliary /
 
Recovery Manager: Release 11.2.0.2.0 - Production on Fri May 27 14:33:42 2011
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: RAC1 (DBID=656357419)
connected to auxiliary database: RAC1 (not mounted)
 
RMAN> run {
allocate channel disk1 device type disk;
allocate auxiliary channel disk2 device type disk;
duplicate target database for standby nofilenamecheck dorecover;
release channel disk1;
release channel disk2;
}
 
using target database control file instead of recovery catalog
allocated channel: disk1
channel disk1: SID=47 device type=DISK
 
allocated channel: disk2
channel disk2: SID=25 device type=DISK
 
Starting Duplicate Db at 27-MAY-11
 
contents of Memory Script:
{
   set until scn  776744;
   sql clone "alter system set  control_files =
  ''+DATA/rac1/controlfile/current.267.752250849'', ''+DATA/rac1/controlfile/current.257.752250851'' comment=
 ''Set by RMAN'' scope=spfile";
   restore clone standby controlfile;
}
executing Memory Script
 
executing command: SET until clause
 
sql statement: alter system set  control_files =   ''+DATA/rac1/controlfile/current.267.752250849'', ''+DATA/rac1/controlfile/current.257.752250851'' comment= ''Set by RMAN'' scope=spfile
 
Starting restore at 27-MAY-11
 
channel disk2: starting datafile backup set restore
channel disk2: restoring control file
channel disk2: reading from backup piece /var/tmp/failover_0lmdcr7p_1_1.bck
channel disk2: piece handle=/var/tmp/failover_0lmdcr7p_1_1.bck tag=FAILOVER
channel disk2: restored backup piece 1
channel disk2: restore complete, elapsed time: 00:00:07
output file name=+DATA/rac1/controlfile/current.269.752250853
output file name=+DATA/rac1/controlfile/current.256.752250857
Finished restore at 27-MAY-11
 
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 until scn  776744;
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   restore
   clone database
   ;
}
executing Memory Script
 
executing command: SET until clause
 
executing command: SET NEWNAME
 
renamed tempfile 1 to +DATA in control file
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
Starting restore at 27-MAY-11
 
channel disk2: starting datafile backup set restore
channel disk2: specifying datafile(s) to restore from backup set
channel disk2: restoring datafile 00001 to +DATA
channel disk2: restoring datafile 00002 to +DATA
channel disk2: restoring datafile 00003 to +DATA
channel disk2: restoring datafile 00004 to +DATA
channel disk2: reading from backup piece /var/tmp/failover_0jmdcr6n_1_1.bck
channel disk2: piece handle=/var/tmp/failover_0jmdcr6n_1_1.bck tag=FAILOVER
channel disk2: restored backup piece 1
channel disk2: restore complete, elapsed time: 00:01:56
Finished restore at 27-MAY-11
 
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
 
datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=752250993 file name=+DATA/rac1/datafile/system.258.752250877
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=752250993 file name=+DATA/rac1/datafile/sysaux.259.752250879
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=752250993 file name=+DATA/rac1/datafile/undotbs1.285.752250883
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=752250993 file name=+DATA/rac1/datafile/users.260.752250883
 
contents of Memory Script:
{
   set until scn  776744;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script
 
executing command: SET until clause
 
Starting recover at 27-MAY-11
 
starting media recovery
 
channel disk2: starting archived log restore to default destination
channel disk2: restoring archived log
archived log thread=1 sequence=4
channel disk2: restoring archived log
archived log thread=1 sequence=5
channel disk2: reading from backup piece /var/tmp/failover_0nmdcr8j_1_1.bck
channel disk2: piece handle=/var/tmp/failover_0nmdcr8j_1_1.bck tag=FAILOVER
channel disk2: restored backup piece 1
channel disk2: restore complete, elapsed time: 00:00:07
archived log file name=+DATA/rac1/archivelog/2011_05_27/thread_1_seq_4.284.752251001 thread=1 sequence=4
channel clone_default: deleting archived log(s)
archived log file name=+DATA/rac1/archivelog/2011_05_27/thread_1_seq_4.284.752251001 RECID=2 STAMP=752251005
archived log file name=+DATA/rac1/archivelog/2011_05_27/thread_1_seq_5.262.752251003 thread=1 sequence=5
channel clone_default: deleting archived log(s)
archived log file name=+DATA/rac1/archivelog/2011_05_27/thread_1_seq_5.262.752251003 RECID=1 STAMP=752251004
media recovery complete, elapsed time: 00:00:02
Finished recover at 27-MAY-11
Finished Duplicate Db at 27-MAY-11
 
released channel: disk1
 
released channel: disk2

Then the final magic command:

DGMGRL> enable database rac1;
Enabled.
DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac2 - Primary database
    rac1 - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

You can even switchover to be back in your original configuration:

DGMGRL> switchover to rac1
Performing switchover NOW, please wait...
New primary database "rac1" is opening...
Operation requires shutdown of instance "rac2" on database "rac2"
Shutting down instance "rac2"...
ORACLE instance shut down.
Operation requires startup of instance "rac2" on database "rac2"
Starting instance "rac2"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "rac1"
DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
    rac2 - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

Finally you can re-enable automatic startup of your primary database:

[oracrs@server1 ~]$ srvctl enable database -d rac1
[oracrs@server1 ~]$ srvctl config database -d rac1 -a
Database unique name: rac1
Database name: rac1
Oracle home: /ora_rac/software
Oracle user: oracrs
Spfile: +DATA/rac1/spfilerac1.ora
Domain: world
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services:
Database is enabled

References

  • 11gR2 Clusterware and Grid Home – What You Need to Know [ID 1053147.1]
  • Failover/Reinstate within Dataguard configuration fails with ORA-16653 /ORA-16795 [ID 1161094.1]
  • RMAN: ORA-19504, ORA-27038 Errors During Backup when a Backupset is in Place [ID 1082911.6]
  • Creating a Standby Database on a new host [ID 374069.1]
  • Reinstating a Physical Standby Using Backups Instead of Flashback [ID 416310.1]
This entry was posted in Oracle and tagged . Bookmark the permalink.

3 thoughts on “Data Guard Broker configuration with failover and switchover testing

  1. Hello yannick

    My opinion is that, to always setup your DG broker on a separate client server which can observe both primary & standby DBs and also ensure that the broker is not affected by primary system crash.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>