The DBA’s Guide to Setting Up Oracle RAC One Node and Oracle Data Guard

With these guidelines, using Oracle RAC One Node in conjunction with Oracle Data Guard for disaster recovery is a straightforward task.

Oracle RAC One Node, a high availability option for Oracle Database 11g Release 2, is a hybrid of a classic active/passive cluster and the Oracle Real Application Clusters (RAC) option, the main difference with standard Oracle RAC being that an Oracle RAC One Node database will typically be active on one instance only. Oracle RAC One Node also allows the online migration of databases and their sessions for scheduled maintenance.

Oracle RAC One Node lowers the risk of unplanned outages as well. In case of a node failure, an Oracle RAC One instance will automatically be restarted on another candidate server. Furthermore, because the Oracle RAC One Node database is actually a cluster database, making the transition from Oracle RAC One Node to full-blown Oracle RAC is relatively straightforward.

Most businesses with a low Recovery Time Objective (RTO) rely on Oracle Data Guard, an Oracle Database Enterprise Edition feature, to restore service in the event of a severe failure or catastrophe. The initial lack of support of Data Guard for Oracle RAC One Node proved to be detrimental to the latter's adoption in many companies. But beginning with the 11.2.0.2 patch set released by Oracle in 2010, Oracle Data Guard can now be used to protect an Oracle RAC One Node database. (Oracle RAC One Node initially was available for a limited number of platforms only in the form of a one-off patch on top of the 11.2.0.1 base release, but it’s now available on every supported platform for which the patch set has been released.)

In this article we will first explore the “broker” approach to an Oracle Data Guard setup, generally intended for less experienced DBAs or those who otherwise want less fine-grained control over the configuration. In Part 2, we will examine how to set up Oracle Data Guard with Oracle RAC One Node manually – the more advanced method. For simplicity, in both cases only one physical standby database is involved. (Basic understanding of Oracle Data Guard is assumed.)

The Setup

In the following sections we will assume that a primary Oracle RAC One Node database (named RON) has already been created and is up and running. In this example a four-node primary and an identical DR cluster are configured with Oracle 11.2.0.2 on Oracle Linux 5.5 64-bit. The databases are located in ASM, with db_create_file_dest set to disk group DATA, and a fast recovery area in disk group FRA.

The primary database RON is registered in the primary cluster’s Oracle Cluster Registry with candidate servers node1 and node2 and domain example.com. A database service, RON_APP.example.com, has been created per Oracle’s recommendation. (Note that user connections should always use this service instead of the ORACLE_SID to minimize problems during an online relocation.)

All diagnostic and administrative files are stored on an existing ACFS mount, called /data/oracle/RON/. This way the information is available across the cluster, a feature that greatly simplifies administration.

The below example shows the database configuration in the primary cluster’s OCR:

$ srvctl config database -d RON
Database unique name: RON
Database name: RON
Oracle home: /u01/app/oracle/product/11.2.0.2/
Oracle user: oracle
Spfile: +DATA/RON/spfileRON.ora
Domain: example.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RON
Database instances:
Disk Groups: DATA,FRA
Mount point paths:
Services: RON_APP.example.com
Type: RACOneNode
Online relocation timeout: 30
Instance name prefix: RON
Candidate servers: node1,node2
Database is administrator managed

The DR database will be built on a different four-node cluster, with database nodes called drnode1 and drnode2. Note that it is not necessary to use four nodes; the majority of Oracle RAC One databases reside on two-node clusters.

Preparing the Standby Database Host

As stated previously, here we are going to use a shared ACFS filesystem -- mounted on all nodes in the same cluster -- to store the diagnostic destination directory, the data pump directory, and other information. The directory already exists for the primary database but not the standby. As a first step the ASM Cluster File System has to be created on the DR cluster.

Begin by creating a new volume for the ASM Cluster File System as the grid infrastructure owner. (In environments without separation of duties this command would most likely be executed as the oracle user.) Note that the ORACLE_SID is set to the first ASM instance.

[grid@drnode1 ~] $ asmcmd volcreate -G ACFSDG -s 1G RONDG_V1

[grid@drnode1 ~] $ asmcmd volinfo -G ACFSDG RONDG_V1
Diskgroup Name: ACFSDG

         Volume Name: RONDG_V1
         Volume Device: /dev/asm/RONDG_v1-329
         State: ENABLED
         Size (MB): 1024
         Resize Unit (MB): 256
         Redundancy: UNPROT
         Stripe Columns: 4
         Stripe Width (K): 128
         Usage:
         Mountpath:

[grid@drnode1 ~] $

In the above example the ASM dynamic volume has been created on a dedicated disk group, called ACFSDG. Although convenient, it is not necessary to use a separate disk group for ADVM/ACFS. The reason in this case is simple:  while the database is located on “premium” classed storage, the ACFS volume is not, hence the use of a different disk group.

The above command created a 1GB ASM dynamic volume, called RONDG_V1 for later use by ACFS in disk group ACFSDG. Next the volume has to be formatted. You need to use the mkfs utility as root to do so, as shown in this example:

[root@drnode1 ~]# mkfs -t acfs /dev/asm/RONDG_v1-329
mkfs.acfs: version                   = 11.2.0.2.0
mkfs.acfs: on-disk version           = 39.0
mkfs.acfs: volume                    = /dev/asm/RONDG_v1-329
mkfs.acfs: volume size               = 1073741824
mkfs.acfs: Format complete.
[root@drnode1 ~]#

After which the volume has to be registered with the ACFS registry. (Alternatively it could have been created as a cluster resource with a dependency on the RONDG database.)

	
[root@drnode1 ~]# /sbin/acfsutil registry -a -n \
> drnode1,drnode2  \
> /dev/asm/RONDG_v1-329 /data/oracle/RONDG/
acfsutil registry: mount point /data/oracle/RONDG successfully added to Oracle Registry

Translated into English, we have instructed the clusterwide mount registry to add a new filesystem, called /dev/asm/RONDG_v1-329, and to mount it to /data/oracle/RONDG on all cluster nodes.

Think of the ACFS registry as an extended, portable, cluster wide /etc/fstab file. With this in use, it is not necessary or encouraged to add a mount point into each cluster node’s fstab! You may have to mount the cluster filesystem manually once after its initial creation.

Let’s walk through this with an example. First, create the mount point on all cluster nodes, then mount the cluster file system. (Again, the ACFS registry will perform this step at the next server reboot!) In the next step the filesystem permissions are relaxed to allow the oracle user to read, write, and change into the directory. The shell script shown below makes use of user equivalence and the fact that the oracle user has suitable entries in /etc/sudoers. You would not normally find this in production environments – in which case you need to ask your system administrator to perform this step for you.

	
[oracle@drnode1 tmp: RON]$ mkdir /data/oracle/RONDG/
[oracle@drnode1 tmp: RON]$ for i in 2 3 4
> do
> ssh drnode$i sudo mkdir –p /data/oracle/RONDG/
> done

[oracle@drnode1 tmp: RON]$ for i in 2 3 4 ; do
>  ssh drnode$i sudo mount -t acfs /dev/asm/RONDG_v1-329 \
>  /data/oracle/RONDG/;
>  mount;
>  done

[oracle@drnode1 oracle]# for i in 2 3 4
> do
> ssh drnode$i sudo chown -R oracle:oinstall /data/oracle/RONDG
> done

In a similar way I created the needed subdirectories for the administrative files under the mount point /data/oracle/RONDG. They were:

• admin
• admin/adump
• admin/pfile
• admin/scripts
• admin/dpump

Creating the Standby Database

With the infrastructure in place, the next step is to create an initialization file for the standby database, RONDG. You usually create the file on the primary by executing a create pfile=’/tmp/initRONDG_1.ora’ from spfile… command before transferring the file to the DR cluster. It needs a little editing, mainly for the db_unique_name and the removal of the control_files parameter. A sample is shown below. (Note the use of directories using the ACFS mount for audit_dump_dir and diagnostic_dest):

[oracle@drnode1 dbs]$ vi /tmp/initRONDG_1.ora
*.archive_lag_target=900
*.audit_file_dest='/data/oracle/RONDG/admin/adump'
*.audit_sys_operations=TRUE
*.audit_trail='os'
*.cluster_database=true
*.db_domain='example.com'
*.compatible='11.2.0.2.0'
*.cpu_count=1
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_file_multiblock_read_count=16
*.db_name='RON'
*.db_unique_name='RONDG'    
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='+FRA'
*.diagnostic_dest='/data/oracle/RONDG/admin'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=RONDGXDB)'
*.memory_target=4294967296
*.open_cursors=300
*.processes=450
*.remote_listener='scan2.example.com:1999'
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.resource_manager_plan='default_plan'
*.standby_file_management='auto'
RONDG_1.undo_tablespace=UNDOTBS1
*.undo_management=AUTO	

The local listener is not set intentionally: Oracle Clusterware 11.2.0.2 will identify a non-default local listener in Oracle RAC and Oracle RAC One databases and automatically add it to the configuration.

Finally, the password file has to be copied from the primary cluster and linked to the ACFS mount. Change to the $ORACLE_HOME/dbs directory on the standby host:

	
[oracle@drnode1 dbs]$ scp oracle@node1:$ORACLE_HOME/dbs/orapwRON* orapwRONDG
[oracle@drnode1 dbs]$ mv orapwRONDG /data/oracle/RONDG/admin/pfile -iv
`orapwRONDG' -> `/data/oracle/RONDG/admin/pfile/orapwRONDG'
removed `orapwRONDG'
[oracle@drnode1 dbs: RON]$ ln -s /data/oracle/RONDG/admin/pfile/orapwRONDG

You should repeat the creation of the symbolic link as just shown on all other cluster nodes acting as candidates for the database-remember that the password file is always called orapwDBUniqueName regardless of instance name, unlike the init_R{ORACLE_SID}.ora.

To allow Oracle Data Guard to ship logs between primary and standby a new set of entries is added to all RDBMS $ORACLE_HOME/network/admin/tnsnames.ora on all cluster nodes:

	
RON =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scan1.example.com)(PORT = 1999))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RON.example.com)
    )
  )

RONDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scan2.example.com)(PORT = 1999))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RONDG.example.com)
    )
  )

Note that it is not possible to use the service name RON_APP.example com here -- if database RON is in standby role, the service won’t be started, preventing the logs from being shipped to the destination with an ORA-12514 error. It is however necessary to use the SERVICE_NAME instead of the SID in the CONNECT_DATA section.

Now it is time to create the standby database. Start the RONDG_1 instance into “nomount” stage. Below is the output of the RMAN command I used to create the standby database:

	

[oracle@drnode1 admin]$ rman target sys/sysPassword@ron auxiliary /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Mar 28 16:25:08 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RON (DBID=1589116261)
connected to auxiliary database (not started)

RMAN> startup clone nomount

Oracle instance started

Total System Global Area     534462464 bytes

Fixed Size                     2228200 bytes
Variable Size                381681688 bytes
Database Buffers             142606336 bytes
Redo Buffers                   7946240 bytes

RMAN> duplicate target database for standby;

Starting Duplicate Db at 28-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=15 instance=RONDG_1 device type=DISK

contents of Memory Script:
{
   restore clone standby controlfile;
}
executing Memory Script

Starting restore at 28-MAR-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/ctrl_04m8c636_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/ctrl_04m8c636_1_1 tag=TAG20110328T155934
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+DATA/rondg/controlfile/current.256.746987137
output file name=+FRA/rondg/controlfile/current.256.746987139
Finished restore at 28-MAR-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 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;
   set newname for clone datafile  5 to new;
   restore
   clone database
   ;
}
executing Memory Script

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

executing command: SET NEWNAME

Starting restore at 28-MAR-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/db_01m8c5on_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/db_01m8c5on_1_1 tag=TAG20110328T155358
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:16
Finished restore at 28-MAR-11

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

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=746987293 file name=+DATA/rondg/datafile/system.257.746987155
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=746987294 file name=+DATA/rondg/datafile/sysaux.258.746987155
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=746987294 file name=+DATA/rondg/datafile/undotbs1.259.746987157
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=746987294 file name=+DATA/rondg/datafile/users.261.746987157
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=746987294 file name=+DATA/rondg/datafile/undotbs2.260.746987157
Finished Duplicate Db at 28-MAR-11

The finishing touch is to add the control files to the pfile, the file names are shown in the “output file name” lines above. You should then create a server parameter file in ASM and register the database in the cluster registry:

	


SQL> create spfile='+DATA/RONDG/spfileRONDG.ora' from pfile;

File created.
SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Don’t forget to update init${ORACLE_SID}.ora in $ORACLE_HOME/dbs to point to the spfile. This needs to be done on the current node only, as you will see in a later step we are going to define a pointer to the spfile in the OCR. Any missing pfile in $ORACLE_HOME/dbs will automatically be created if needed, and points to the spfile as indicated in the configuration.

Register the database as an Oracle RAC One database with candidate servers drnode1 and drnode2. Additionally, instruct database to be a physical standby, and to start into mount mode only. A recommended service is created as well. Consider this example:

	
[oracle@drnode1 admin: RON]$ srvctl add database -d RONDG –o \
> $ORACLE_HOME -c RACONENODE -e drnode1,drnode2 -w 10 -p
>'+DATA/RONDG/spfileRONDG.ora' -r PHYSICAL_STANDBY -s mount

[RONDG_1]oracle@drnode1 $ srvctl add service -d RONDG -s \
> RONDG_APP.example.com -e session

Note that you can’t set a dependency on an ACFS resource here if it hasn’t been registered in the OCR via srvctl add filesystem -- and it has not. The ACFS mount is registered differently, using the mount registry.

	

[oracle@drnode1 ~] $ srvctl config database -d RONDG
Database unique name: RONDG
Database name:
Oracle home: /u01/app/oracle/product/11.2.0.2/
Oracle user: oracle
Spfile: +DATA/RONDG/spfileRONDG.ora
Domain: example.com
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: RONDG
Database instances:
Disk Groups: DATA,FRA
Mount point paths:
Services: RONDG_APP.example.com
Type: RACOneNode
Online relocation timeout: 10
Instance name prefix: RONDG
Candidate servers: drnode1,drnode2
Database is administrator managed

[oracle@drnode1 ~] $ srvctl config service -d RONDG
Service name: RONDG_APP.example.com
Service is enabled
Server pool: RONDG
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SESSION
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Preferred instances: RONDG_1
Available instances:

Any Clusterware-managed database should be controlled via srvctl rather than SQL*Plus. The database started with these commands:

	

[oracle@drnode1 dbs]$ srvctl start database -d RONDG -n drnode1

[oracle@drnode1 dbs]$ srvctl status database -d RONDG
Instance RONDG_1 is running on node drnode1
Online relocation: INACTIVE

Great, the first step toward a Oracle Data Guard configuration is done!

Review

Let’s pause a minute at this stage to digest what has been done: We have successfully created a standby database for an Oracle RAC One database using recovery manager. All files are in ASM, and ASM takes care of file name conversion. The standby_file_management initialization parameter is set to auto, preventing name clashes from happening. As an added benefit ASM created a “subdirectory” RONDG underneath disk group DATA (instead of RON as on the primary), allowing us to distinguish between the primary and standby database. Anything reducing the human error factor in this regard is very welcome.

The next step involves creating the Oracle Data Guard configuration, to allow redo information to be shipped between primary and standby (and vice versa). Now the focus is on Oracle Data Guard Broker. To make it easier to follow the examples the command line interface has been chosen.

The Broker Approach

Oracle Data Guard Broker was introduced in Oracle9i Database and has helped greatly in situations where automation is key or perhaps where IT staff is less experienced. The broker interface was extended in Oracle Database 10g and is responsible for managing Fast Start Failover, a feature which can automate role transitions in extremely automated environments.

The easiest way to create a Oracle Data Guard configuration with the broker is to use Oracle Enterprise Manager Grid Control, but an alternative exists in the form of a command-line interface called Data Guard Manager Line Mode, or dgmgrl. It can be executed on any host to which a SSH connection is available and has minimum overhead. (Because it is universally accessible, the dgmgrl approach will be the main subject here. Remember that the broker takes care of setting all initialization parameters you set manually. It therefore is recommended that you don’t modify any Oracle Data Guard-related parameters outside the broker interface.)

Oracle Data Guard Broker relies on two configuration files to store meta-information. Users of a single instance probably didn’t notice them as their location is not of real importance in such a setup and the default works fine. For any clustered environment, and this includes Oracle RAC as well as Oracle RAC One Node, the broker configuration files need to be on shared storage. Since we are using ASM as the storage backend anyway, it’s a good idea to place the broker configuration files there. Alternatively a supported clustered filesystem can be used.

Configuring the Initialization Files

You can define the broker configuration files in SQL*Plus, as in this example for the standby database RONDG:

	
SQL> alter system set dg_broker_config_file1='+DATA/rondg/dr1.dat';
SQL> alter system set dg_broker_config_file2='+DATA/rondg/dr2.dat';

Don’t forget to set the parameter for the primary as well- a roll change is sure to come:

	
SQL> alter system set dg_broker_config_file1='+DATA/ron/dr1.dat';
SQL> alter system set dg_broker_config_file2='+DATA/ron/dr2.dat';

If your local listener doesn’t listen on default port 1521 and the initialization parameter local_listener isn’t set yet, you have to set it manually. If you don’t know what your listener port is you can find out about it by executing srvctl config listener. Other than that, no further configuration is required for the initialisation files; the broker will take care of all the others.

Now start the broker on both primary and standby databases:

	
SQL> alter system set dg_broker_start=true;

You can see in the alert.log that the broker processes start.

Preparing the Listener

The broker will have to restart database instances during role transitions. To be able to do this, it requires statically registered database instances. By default, the static service name follows this naming convention for Oracle Data Guard Broker:

DB_NAME_DGMGRL.db_domain

It is important to add the domain if one exists! In our example, the primary database would have RON_DGMGRL.example.com as its static connection identifier.

(NOTE: Remember that the ORACLE_SID for the RON database is dynamic, and you don’t have a static mapping between instance and cluster node! The ORACLE_SID will be in the form of dbUniqueName_N where N can be either 1 or 2. There is an option to deviate from the naming standard by defining a custom instance name prefix [option -i to srvctl add database] if you don’t like it. Most often you will find that your database uses the dbUniqueName_1 SID, but you are bound to see the dbUniqueName_2 SID during relocations and possibly during unplanned outages as well.)

Each of the possible ORACLE_SIDs needs to be statically registered on each candidate’s server’s listener.ora. In our example, RON has 2 candidate servers, and hence can have two ORACLE_SIDs: RON_1 and RON_2. The same applies to RONDG which also has two candidate servers. It therefore is required to register each ORACLE_SID statically with the listeners of their respective candidate server. The requirement can be seen in the broker’s database property StaticConnectIdentifier.

You need to modify the listener.ora file in $GRID_HOME/network/admin/listener.ora to add the static connection identifiers. The following is an example of a SID_LIST for the listener on our primary cluster (note there was no SID_LIST_LISTENERNAME before). The service name has been statiscally registered as well, which is optional.

	
SID_LIST_LISTENER=
# data guard broker
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=RON_DGMGRL.example.com)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/)
      (SID_NAME=RON_1)
    )
    (SID_DESC=
      (GLOBAL_DBNAME=RON_DGMGRL.example.com)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/)
      (SID_NAME=RON_2)
    )
# the same but not exclusively for the broker
    (SID_DESC=
      (GLOBAL_DBNAME=RON.example.com)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/)
      (SID_NAME=RON_1)
    )
    (SID_DESC=
      (GLOBAL_DBNAME=RON.example.com)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/)
      (SID_NAME=RON_2)
    )
  )

All other services register dynamically. The setup for the DR cluster’s listener.ora files is as follows:

	
SID_LIST_LISTENER=
# data guard broker
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=RONDG_DGMGRL.example.com)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/)
      (SID_NAME=RONDG_1)
    )
    (SID_DESC=
      (GLOBAL_DBNAME=RONDG_DGMGRL.example.com)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/)
      (SID_NAME=RONDG_2)
    )
# the same but not exclusively for the broker
    (SID_DESC=
      (GLOBAL_DBNAME=RONDG.example.com)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/)
      (SID_NAME=RONDG_1)
    )
    (SID_DESC=
      (GLOBAL_DBNAME=RONDG.example.com)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/)
      (SID_NAME=RONDG_2)
    )
  )

After the configuration has been added, each local listener has been reloaded.

Creating the Broker Configuration

With all the preparation completed it is time to start dgmgrl and create a configuration. The broker requires a connection as SYSDBA, the user martin has this privilege granted. Alternatively, connect as the SYS user to your database.

The broker configuration is created in two steps: in step one you create the configuration, in step two you add the standby databases. Finally, the configuration is enabled. See the below for an example of these:

	
$ dgmgrl martin/xxx
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.
Connected.
DGMGRL>  CREATE CONFIGURATION brokertest as
> PRIMARY DATABASE IS RON
> CONNECT IDENTIFIER IS RON;
Configuration "brokertest" created with primary database "RON"

DGMGRL> add database RONDG
> as connect identifier is RONDG
> maintained as physical;
Database "RONDG" added

With the databases added, you can check the status of your configuration:

DGMGRL> show configuration verbose

Configuration - brokertest

  Protection Mode: MaxPerformance
  Databases:
    RON   - Primary database
    RONDG - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

You can also view individual databases. The “verbose” attribute prints more information.

DGMGRL> show database verbose RON

Database - RON

  Role:            PRIMARY
  Intended State:  OFFLINE
  Instance(s):
    RON_1

  Properties:
    DGConnectIdentifier             = 'RON'
    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                = '900'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'RON_1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.91)(PORT=2001))(CONNECT_DATA=(SERVICE_NAME=RON_DGMGRL.example.com)(INSTANCE_NAME=RON_1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
DISABLED

The remarkable fact to notice here is that the broker recognizes the fact that RON is a clustered database. This is visible in the first few lines of the above output:

	
DGMGRL> show database verbose RON

Database - RON

  Role:            PRIMARY
  Intended State:  OFFLINE
  Instance(s):
    RON_1

Similarly you can view the properties of the standby database:

	
DGMGRL> show database verbose RONDG

Database - RONDG

  Role:            PHYSICAL STANDBY
  Intended State:  OFFLINE
  Transport Lag:   (unknown)
  Apply Lag:       (unknown)
  Real Time Query: OFF
  Instance(s):
    RONDG_2

  Properties:
    DGConnectIdentifier             = 'RONDG'
    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                = '900'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'RONDG_2'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.99)(PORT=2001))(CONNECT_DATA=(SERVICE_NAME=RONDG_DGMGRL.example.com)(INSTANCE_NAME=RONDG_2)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
DISABLED

Have a look at the TopWaitEvents line in the output; you can easily query the standby and primary for their top wait events. Where this was always possible to get the top n events for the primary (or a logical standby), this is new for physical standby.

Judging by the output everything looks good, so let’s enable the configuration.  We have created standby redo logs on the primary and standby database to resolve the warning message in the output of “show configuration”. (Later I will describe the creation of standby redo logs in more detail.)

	
DGMGRL> enable configuration
Enabled.

DGMGRL> show configuration verbose

Configuration - brokertest

  Protection Mode: MaxPerformance
  Databases:
    RON   - Primary database
    RONDG - 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

Role Transitions

One of the main goals of Oracle Data Guard is to allow for role transitions. Two different types of role transitions are possible: a graceful switchover and a failover. These are quite often used interchangeably in conversations, but they are quite different. 

The following sections explain how to perform both of them.

Performing a Graceful Switchover

A switchover is a very easy operation to be performed when the Oracle Data Guard Broker is in place. Instead of using a few SQL commands to switch roles, all that is needed is one command. As for the configuration you need to use the dgmgrl tool to initiate the switchover. You should connect as SYSDBA via Net*8:

	
[oracle@node1 ~]$ dgmgrl sys/xxx@ron

The beauty is that there is only one command to type to initiate the switchover. Have a look at this example:

	
DGMGRL> show configuration

Configuration - brokertest

  Protection Mode: MaxPerformance
  Databases:
    RON   - Primary database
    RONDG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to RONDG
Performing switchover NOW, please wait...
New primary database "RONDG" is opening...
Operation requires shutdown of instance "RON_2" on database "RON"
Shutting down instance "RONDG_2"...
ORACLE instance shut down.
Operation requires startup of instance "RON_2" on database "RON"
Starting instance "RON_2"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "RONDG"

That wasn’t too painful, was it? If you hadn’t registered the instances statically with the listeners the startup command would have failed. If that happens, you can always manually complete the missing step. After the line “switchover succeeded, new primary is «RONDG»” you can verify the new state of your configuration:

	

DGMGRL> show configuration

Configuration - brokertest

  Protection Mode: MaxPerformance
  Databases:
    RONDG  - Primary database
    RON    - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> exit

This concludes the switchover operation. To switch back to the RON database at a convenient time you use the same sequence of steps and reverse the database names.

Performing a Failover Operation

Similar to the switchover operation, the broker interface greatly simplifies the task of performing a failover operation. The failover can be either complete (default) or immediate. The default failover tries to recover as much redo information as possible before activating the standby database, thus trying to minimize the data loss. For scheduled, controlled failover tests, it is recommended to flush all the redo using the alter system flush redo to db_unique_name confirm apply statement when the primary is mounted to avoid data loss. (Be aware of Bug 9044026  ORA-16447 from ALTER SYSTEM FLUSH REDO affecting Oracle < 11.2.0.2 causing redo not to be flushed under certain conditions.)

Before we fail over, let’s have a look at the current configuration:

	
DGMGRL> show configuration

Configuration - brokertest

  Protection Mode: MaxPerformance
  Databases:
    RON   - Primary database
    RONDG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

The setup is working properly, and there are no warnings. With this knowledge in mind we can perform a failover as shown below:

	
DGMGRL> failover to RONDG
Performing failover NOW, please wait...
Failover succeeded, new primary is "RONDG"
DGMGRL> show configuration

Configuration - brokertest

  Protection Mode: MaxPerformance
  Databases:
    RONDG - Primary database
    RON   - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

As you can see the failover has completed, however the new standby database RON needs to be reinstated. With enabled Flashback Database, the broker can take care of this as well using the reinstate database command, shown in the below example:

	

DGMGRL> reinstate database RON
Reinstating database "RON", please wait...
Reinstatement of database "RON" succeeded
DGMGRL> show configuration

Configuration
  Name:                brokertest
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    RONDG - Primary database
    RON   - Physical standby database

Current status for "brokertest":
SUCCESS

DGMGRL>

If the automatic reinstate fails, the database will most likely have to be restored from backup. As a last resort you could try using the flashback database command to flash it back to the point where the primary has been activated (this information is available in the v$database view).

Summary

This has been quite a lot of information to digest so far. The aim of the article was to demonstrate that Oracle RAC One Node is as easy to deal with in Oracle Data Guard as any other Oracle database type. Adhering to the rules outlined in the course of the article, a failover and subsequent switchover operation are nothing to fear.

As always, practice your DR strategy regularly and write down which procedure needs to be followed. Many DBAs have learned through experience that a role transition of the database is usually the easier part in a DR scenario.

In Part 2, we will examine the manual, more advanced approach to Oracle Data Guard configuration.

Martin Bach is a freelance Oracle consultant in the UK and is the co-author of Pro Oracle Database 11g RAC on Linux (Apress).