In this Document

APPLIES TO:

GOAL

For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

Primary hosts: exa503,exa504
Standby hosts: exa505,exa506
Primary Db_unique_name: CHICAGO
Standby Db_unique_name: BOSTON
Primary instance names: chicago1, chicago2
Standby instance names: boston1, boston2
Service name:  srv_rman

Maximum Availability Architecture

The Maximum Availability Architecture (MAA) defines Oracle’s most comprehensive architecture for reducing downtime for scheduled outages as well as preventing, detecting and recovering from unscheduled outages. Real Application Clusters (RAC) and Oracle Data Guard are integral components of the Database MAA reference architectures and solutions.

More detailed information, such as a discussion of the purpose of MAA and the benefits it provides, can be found on the Oracle Technology Network (OTN) at http://www.oracle.com/technetwork/database/features/availability/maa-096107.html

Purpose of this Document

The purpose of this document is to provide a step-by-step guide for creating a standby database in an 11.2 environment integrating the MAA Data Guard configuration best practices. This paper assumes that the following conditions exist:

1. A Primary RAC or single instance database utilizing ASM for data file storage
2. The Primary database is in archive log mode
3. All Standby target hosts have existing Oracle software installation
4. The Standby target database storage will utilize ASM

•  It is recommended that you consult the Data Guard Concepts and Administration guide as well as the HA Best Practice guide for more information and full MAA Best Practices.

Supported Versions

This document applies to both Oracle Server versions 11.2.0.x and 12.1.0.x or higher.  There are some important differences in how the DUPLICATE FOR STANDBY FROM ACTIVE DATABASE functions between the two releases which are noted below:

11.2:

• DUPLICATE FROM ACTIVE DATABASE uses datafile image copies and does not support section size, compression, or encryption.

12c:

• DUPLICATE FROM ACTIVE DATABASE supports backup sets.
• SECTION SIZE support is available. If section size is used, then use multiple auxiliary channels for parallelism.
• Compression is supported but do not use compression on backups or data that has already been compressed (e.g. using OLTP, HCC compression) or encrypted since the compression benefits is very small and the overall impact (e.g. CPU resources and increased in elapsed time) can be significant.
• Encryption is supported.

All of the examples illustrated in this document use the following fictitious naming used only as examples to describe the procedure:

SOLUTION

Prerequisite:

To expedite active database RMAN duplicate commands, use multiple RMAN channels to spread across primary nodes. By doing this we can utilize more network interfaces for the data transfer.

Parallelize backups across all primary database nodes leveraging disk and network bandwidth as available.

MAA best practice recommend using  8 channels minimum or 2 channels per node.

Use automatic load balancing to distribute RMAN channels among the allocated nodes. With auto load balancing the channel distribution is approximate point in time. Analyze the impact on existing production databases and reduce the RMAN parallelism if necessary.

Make the following RMAN configuration changes at the Primary. Our example uses 16 pre-configured channels for RMAN to use during the standby creation step. These 16 channels will suffice for 2-4 node RAC clusters but will need to be increased if your Production RAC cluster is larger than 4 nodes.

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 16;

 You will use Oracle Net service load balancing to distribute these RMAN channels evenly among the allocated instances.

1) Create a service to run RMAN on allocated instances in the cluster:

srvctl start service -db chicago -service srv_rman

 2) When running RMAN, use the service name in the connect string for the “target” parameter.

Steps to Duplicate the Primary Database

The following are the steps used to create the Data Guard Standby database. The ORACLE_HOME used in the examples uses the latest release Oracle 12.2. Please change as per your environment.

1.  Create standby redo logs on the primary database that are the same size of the online redo logs. This will ensure that standby redo log files are automatically created at the standby during the RMAN duplication process and that standby redo log files are available on the current Primary after a role transition occurs in the future and the Primary becomes a standby.

Oracle recommends having the same number of standby redo log files as there are online redo log files plus one additional standby redo log for each thread. Our primary database has 6 online redo log files, 3 per thread. We therefore need 4 Standby Redo Log files per thread (Primary 3 plus 1) for a total of 8 Standby Redo Log files. As per MAA  Best Practice we recommend to create only one member for standby redo log . For example:

SQL> alter database add standby logfile thread 1
group 7 ('+DATAC1') size 512M,
group 8 ('+DATAC1') size 512M,
group 9 ('+DATAC1') size 512M,
group 10 ('+DATAC1')size 512M;

SQL> alter database add standby logfile thread 2
group 11 ('+DATAC1') size 512M,
group 12 ('+DATAC1') size 512M,
group 13 ('+DATAC1') size 512M,
group 14 ('+DATAC1') size 512M;

2.  Database force logging is recommended as an MAA Data Guard best practice.  To enable force logging, use the following command on the primary:

3. For the RMAN duplication process it is necessary to setup a temporary listener which will later be removed.

In the standby database home, create and start a listener that offers a static SID entry for the standby database with the ORACLE_SID for the standby (boston1) and ORACLE_HOME for the standby.

LISTENER_duplicate =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = exa505)
(PORT = 1525)(IP = FIRST))))

SID_LIST_LISTENER_duplicate =
(SID_LIST =
(SID_DESC =
(SID_NAME = boston1)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1)))

4. In the database home on the primary node, create an Oracle Net alias to connect to the listener created in the above step.

5. For active database duplication, you must create a password file for the auxiliary instance and establish Oracle Net connectivity. This is a temporary password file as it will be overwritten during the duplicate operation.

Create a password file on the standby host in the $ORACLE_HOME/dbs directory with the same SYS (or SYSDG if you use that username in 12c) as the Primary database. 

Copy the password file from the primary to the standby and rename to it's SID if it's different.  This has not been allowed to have a different password file since 11gR1.

For example:

6. On the standby host in the ORACLE_HOME/dbs directory create a pfile (initboston1.ora) with the following parameters. It is recommended to set the sga_target same as that of the primary database. For example:

7. On all standby hosts create the audit directory for the boston database:

8. On all primary hosts create an Oracle Net alias to reach the boston database on the standby nodes. Make sure that all hosts have both a chicago and boston Oracle Net alias and that all the aliases reference the scan listener and not the node vip. Also, if local_listener is set to an alias on the primary create a corresponding entry on the standby side that points to the local listener on that system.  For example:

9. On the standby host set the SID to the standby SID (boston1) and startup nomount the standby/auxiliary instance.

10. If the cluster_interconnects parameter is set on the primary it will be necessary to temporarily unset it in the SPFILE only. It will be returned to the original values in Step 13.

The values of cluster_interconnects for each instance should be noted before resetting the SPFILE as they will be replaced after standby has been created. For example, execute the following commands and note down the values for each instance in the Primary cluster.

INST_ID      INSTANCE_NAME   NAME                                        VALUE
---------- ---------------- --------------------------------------------- -------------------------
1            chicago1            cluster_interconnects                         192.168.10.149
2            chicago2            cluster_interconnects                         192.168.10.150

Now reset the cluster_interconnects parameter temporarily in the SPFILE only:

11. On the primary host run an RMAN script that duplicates the primary database using the “duplicate target database for standby from active database” command. Note that the contents of the duplicate command will vary depending on your environment. The following example covers the majority of use cases. For any case not covered by the examples please consult chapter 24 of the Oracle® Database Backup and Recovery User's Guide for complete information.

The first example illustrates how to duplicate between two systems where the ASM diskgroup names are the same. The section_size clause in below example can be used with Oracle 12.1.0.2 or higher.

rman <<EOF
connect target sys/<password>@<prim_scan>/srv_rman;
connect auxiliary sys/<password>@dup;
run {
duplicate target database for standby from active database
spfile
parameter_value_convert 'chicago','boston'
set db_unique_name='boston'
SET CLUSTER_DATABASE='FALSE'
set control_files='+DATA/boston/standby.ctl'
set local_listener=’boston_local_listener’
set remote_listener=’stby-scan:1521’
set audit_file_dest='/u01/app/oracle/admin/boston/adump'
section size=5g;
}
EOF

The following example illustrates how to duplicate between two systems where the source is on a file system and the target is using an ASM diskgroup. The same process will work where both databases use ASM but they have different Disk Group names.

In our example we use the file system names for the Primary but if this was ASM to ASM with different disk group names you would substitute the Primary Disk group name.

rman <<EOF
connect target sys/<password>@<prim_scan>/srv_rman;
connect auxiliary sys/<password>@dup;
run {
duplicate target database for standby from active database
spfile
parameter_value_convert 'chicago','boston'
set db_file_name_convert '/u01/data/','+DATA'
set db_unique_name='boston'
SET CLUSTER_DATABASE='FALSE'
set db_create_online_log_dest_1='+DATA'
set db_create_file_dest='+DATA'
set db_recovery_file_dest='+RECO'
set log_file_name_convert '/u01/data/','+DATA','/u01/reco/','+RECO'
set control_files='+DATA/boston/standby.ctl'
set local_listener=’boston_local_listener’
set remote_listener=’stby-scan:1521’
set audit_file_dest='/u01/app/oracle/admin/boston/adump'
section size 5g;
}
EOF

 12. Reset CLUSTER_INTERCONNECTS to original value from Step 11 in the SPFILE with the following commands. Do NOT restart any primary instance.

13. Stop and remove the listener created in step 3. Also remove the TNS entry created in Step 4.

14. Copy the password file to the respective location.

If the standby is version 11.2 then no action is required.

If the standby database is at Version 12.1 or higher copy the password file to ASM. For Example:

Remove the original password file.

$rm /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwboston1

 15. Create the standby spfile in ASM.

 Create a spfile in +DATA for the standby database:

SQL> create spfile='+DATA/boston/spfileboston.ora' from pfile='/tmp/p.ora'

$rm /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/spfileboston1.ora

16. On standby host create an initboston1.ora file that points to the spfile created in the above step.

 17. Restart the standby instance. 

 18. If the standby database is single instance(SI), then only execute the following steps. If standby is a RAC configuration this step will be executed later as part of RAC configuration.
       If the standby database is version 12.1 then add the standby role and password file to ASM.

12c :
srvctl add database -db boston -oraclehome /u01/app/oracle/product/12.2.0.1/dbhome_1 -node exa505 -instance boston1
srvctl modify database -db boston -role physical_standby -spfile '+DATA/boston/spfileboston.ora' -pwfile '+DATA/BOSTON/PASSWORD/pwboston'

Complete Standby RAC Configuration

After completing the steps in the above section the standby database has been created on the standby host. When the primary is RAC configuration and you want the standby to be RAC configuration, additional configuration steps need to be completed on the standby. These remaining configuration steps are covered below.

1. Create a temporary pfile from spfile on the standby.

2. Modify the parameters on the Standby to update the instance specific RAC parameters.  For example:

3. Create a spfile in +DATA for the standby database:

4. Copy the password file to the respective location.

If the standby database is version 11.2, you must copy the password file to the $ORACLE_HOME/dbs directory on all the other standby hosts and name it per the standby SID on each host. For example on node2 you would name it $ORACLE_HOME/dbs/orapwboston2.

If the standby database is version 12c then no action is needed. The password was copied to ASM in Step 14, which is visible to all nodes across the cluster.

5. On all standby hosts create an initboston<SID Number>.ora file that points to the spfile created in the above step. For example:

6. Restart all standby instances. Register the database with CRS.

 7. Modify the primary initialization parameter for dataguard on primary,

Creating a Data Guard Broker configuration:

The following section describes the basic steps on how to create a Data Guard broker configuration. For complete information on the Data Guard broker consult the Oracle® Data Guard Broker guide.

1. On both the primary and standby configure the Data Guard broker metadata files and enable the broker:

Primary:

Standby

2. Add static sid entries into the local node listener.ora located in the grid infrastructure home on all hosts in the configuration. Please refer to <Note 1387859.1> for instructions on how to complete this):

 For example:

LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =boston_DGMGRL)
(SID_NAME =boston1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)))
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON

Note that each static entry references the SID for each node, boston1, boston2 or chicago1, chicago2 for an ADMIN managed RAC cluster.  If the RAC is a Policy Managed RAC please refer to the note above for specific instructions on static entries in 11g Release 2.

 3. Bounce or reload all the listeners where the above modification was made (primary and standby nodes):

4. On a primary host connect with dgmgrl and create the configuration:

[oracle@exa503 /etc]$ dgmgrl sys/password

DGMGRL> create configuration 'dg_config' as  primary database is 'chicago' connect identifier is chicago;

Configuration "dg_config" created with primary database "chicago"

DGMGRL> add database 'boston' as connect identifier is boston;

Database "boston" added

DGMGRL> enable configuration;

Enabled.

5. Verify that the configuration created successfully by using the show configuration command:

DGMGRL> show configuration;

Configuration - dg_config

Protection Mode: MaxPerformance
Databases:
chicago - Primary database
boston - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

6.  Flashback database is required to reinstate a failed primary after a failover role transition. Optionally enable flashback on both the primary and standby:

Primary:

To enable flashback database on the standby the redo apply process must first be stopped. Once flashback has been enabled redo apply can be restarted:

The above steps can also be accomplished using the broker with the following commands. (you must be connected to the standby database via DGMGRL):

Application Service Considerations with Data Guard:

In an Oracle Data Guard configuration, applications must use Services to connect to the databases as there is more than one database with different SID names that could be the Primary database.  And with Oracle Active Data Guard, any physical standby database could be made available to provide read only access to the data.  To enable client connections, services need to be created for the workload that performs updates which will only be started on the database in the configuration that is currently in the 'Primary' role as well as services for read only reports and queries that should only be present on physical standbys that are open in Active Data Guard.

The following is an example of how to configure a Read/Write service (workload) and a Read/Only service (reports) on the Primary and Standby database. 

If the primary / standby is configured with Oracle Restart, RAC One Node or RAC, the services are created using srvctl. (Note for JDBC application server side TAF attributes should be set to NONE). If you are creating services for a multitenant database then use service commands that include the –pdb option. 

For example, here we add show adding two services to each database in the configuration.  WORKLOAD for Read/Write connections and REPORTS for Read/Only connections.  WORKLOAD will only run on the database that is running in the Primary role and REPORTS will only run on the database that is in the PHYSICAL STANDBY role.

Primary cluster:

Standby Cluster:

2. Create Oracle Net aliases that reference the above services for the client applications to use.