Written by Nassyam Basha

 

Introduction

 

This article explains how to configure Data Guard between RAC primary and standby alone (Oracle restart) Standby database with easy and all advanced methods to achieve Oracle maximum availability architecture with the Data Guard broker and finally how to register the database with high availability services to manage database using service control utility. This article is purely for 12c and of course the procedure is applicable for even earlier versions and release but with few changes in compatibility parameter.

 

MAA Setup

 

We have seen MAA documents with earlier versions but the data guard configuration is with manual method. In this article we have used all advanced and available methods to build Standby from RAC to stand alone. As we know there are various methods to refresh standby from primary and the preference of the method is purely dependent on the downtime, network transfer rate, space constraints and so on. In this article we have used the Active Duplicate method to refresh the database for standby use and configured Data Guard using broker with easy steps. Basically this article goal is to show how simple is to configure the setup. Usually there will be lot of confusion in Data Guard parameters when the RAC is primary and standby is Stand alone. So to lift out these confusions I will strongly recommend using Broker to simplify the setup and to avoid misconfiguration of the setup.

 

Now question is my customer is not preferring the Data Guard broker, but being DBA its his/her role to explain the things how the Data Guard broker ease the things and how beautiful features introduced in 12c like we can forecast whether the switchover WILL be successful or not and to validate the configuration between primary and standby so on.

 

Before the configuration first let’s understand the proposed configuration and rough architecture. As said earlier the primary is RAC database with server ORA-R2N1 and ORA-R2N2 and the standby stand alone database hostname is ORA-R2N3 and the Data Guard will be managed using the Data Guard Broker. Apart from that the configuration and the steps are same for any Operating system except few things in copying files and using the commands.

 


Configuration on Primary database prior to Duplicate

 

We have to be very careful before creating standby database and the mandatory of them are below one by one. The very important thing is the primary database should be in archive log mode and whether you use FRA or not it is optional but highly recommended to fulfill the MAA concept, so that oracle will manage archive log files, backups and flash logs based on the space considerations.

 

1. Archivelog mode – Indeed production database will be in archivelog mode, if you are using for any test purpose then you can enable archiving from the mount status using the command “alter database archivelog” and then open the database.

 

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 14

Next log sequence to archive 15

Current log sequence 15

SQL> show parameter db_recovery_file_dest

 

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string +DGFRA1

db_recovery_file_dest_size big integer 5025M

SQL>

 

2. Force Logging – Few of the tables/objects might be created in nologging to stop the heavy redo generation, but with this behavior standby will not function, Hence ensure the primary database is in force logging mode to ensure all the changes have redo for standby availability to recover the changes.

 

SQL> select database_role,force_logging from v$database;

 

DATABASE_ROLE FORCE_LOGGING

---------------- ---------------------------------------

PRIMARY NO

 

SQL> alter database force logging;

 

Database altered.

 

SQL>

 

3. Standby Logfile Groups – This is the best practice to create the standby log files on primary so that in the duplicate process the standby log files will be created automatically same as primary if we specify valid LOG_FILE_NAME_CONVERT values. If we do not create the standby log file groups on primary then we can also again create on standby after the duplicate or restore. Ensure the standby log file size is same as the online log file size and the number of standby log filegroups is same as online log file groups.

 

SQL> alter database add standby logfile size 100m;

 

4. Listener & Oracle net service – As we know from 11gR2 we have concept of scan listener for more flexibility on load balancing and no more risk in case of add/delete nodes so on. So review the primary and standby listeners and the net services we have defined. For RAC we are suing scan ip and where for standby we are using regular IP address.

 

[oracle@ora-r2n1 ~]$ srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is running on node ora-r2n1

[oracle@ora-r2n1 ~]$ srvctl status listener -n ora-r2n1

Listener LISTENER is enabled on node(s): ora-r2n1

Listener LISTENER is running on node(s): ora-r2n1

[oracle@ora-r2n1 ~]$ srvctl status listener -n ora-r2n2

Listener LISTENER is enabled on node(s): ora-r2n2

Listener LISTENER is running on node(s): ora-r2n2

[oracle@ora-r2n1 ~]$

 

 

 

5. Initialization parameters - Prior to diag destination introduction we have to create many directories to store adump,bdump, udump , cdump so on. Now our job is so easy and if we want explicitly want to have different location then of course we can assign like adump I have defined in below init configuration.
 

    1. This article is purely for 12c so do not forget about multitenant , if the primary database contains pluggable database then we must add parameter enable_pluggable_database=true.

    2. We can also skip adding PDB database but it DOES NOT MEET MAA.

    3. If you take a look my primary database disk group names are different and for standby disk groups are different, hence I have used DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT for online and standby log files.


 

6. Password file – For RAC primary database we have password file in shared location from 12c and not in local locations, Now copy the password file from primary to standby host and we can place in local location because there are no other instances to use the password file.

[oracle@ora-r2n1 admin]$ srvctl config database -d canada

Database unique name: CANADA

Database name:

Oracle home: /u01/app/oracle/product/12.1.0.1/db_1

Oracle user: oracle

Spfile:

Password file: +DG1/CANADA/orapwcanada

Domain: oracle-ckpt.com

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: CANADA

Database instances: CANADA1,CANADA2

Disk Groups: DG1,DGFRA1

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

Database is administrator managed

 

[oracle@ora-r2n1 admin]$ asmcmd

ASMCMD> pwcopy +DG1/CANADA/orapwcanada /home/oracle/orapwINDIA

copying +DG1/CANADA/orapwcanada -> /home/oracle/orapwINDIA

ASMCMD> exit

 

[oracle@ora-r2n1 admin]$ ls -ltr /home/oracle/orapwINDIA

-rw-r----- 1 oracle oinstall 7680 Mar 14 23:00 /home/oracle/orapwINDIA

[oracle@ora-r2n1 admin]$ scp /home/oracle/orapwINDIA ora-r2n3:/u01/app/oracle/product/12.1.0.1/db_1/dbs/

oracle@ora-r2n3's password:

orapwINDIA 100% 7680 7.5KB/s 00:00

[oracle@ora-r2n1 admin]$

-

[oracle@ora-r2n3 ~]$ cd $ORACLE_HOME/dbs

[oracle@ora-r2n3 dbs]$ hostname

ora-r2n3.oracle-ckpt.com

[oracle@ora-r2n3 dbs]$ ls -ltr orapwINDIA

-rw-r----- 1 oracle oinstall 7680 Mar 14 23:01 orapwINDIA

[oracle@ora-r2n3 dbs]$

 

7. Startup the Instance – Now we have in place of the init file and oracle net service, password file. Start the instance in nomount status after creating directory of adump to store audit files.

[oracle@ora-r2n3 dbs]$ export ORACLE_SID=INDIA

[oracle@ora-r2n3 dbs]$ mkdir -p /u01/app/oracle/admin/INDIA/adump

[oracle@ora-r2n3 dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 21 00:12:35 2016

 

Copyright (c) 1982, 2013, Oracle. All rights reserved.

 

Connected to an idle instance.

 

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area 705662976 bytes

Fixed Size 2292384 bytes

Variable Size 297796960 bytes

Database Buffers 402653184 bytes

Redo Buffers 2920448 bytes

SQL>

 

8. Connectivity Test – We are performing Active duplicate hence ensure you able to connect both primary and standby database using service name and not with “/ “

[oracle@ora-r2n3 dbs]$ rman target sys/oracle@canada auxiliary sys/oracle@india

 

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Mar 14 23:06:59 2016

 

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

 

connected to target database: ORCL (DBID=1434250731)

connected to auxiliary database: ORCL (not mounted)

 

RMAN>

 

9. Script to create Standby – When managing the big tier databases and of course it is time taking to complete duplicate, so ensure to run the duplicate with script to run in background.

 

vi /home/oracle/nassyam/restore_MAADG1.sh

 

export ORACLE_SID=INDIA

export ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/db_1

export PATH=$ORACLE_HOME/bin:$PATH

export NLS_DATE_FORMAT='YYYY-MM-DD:hh24:mi:ss'

date

echo "Begin restore"

rman target sys/oracle@canada auxiliary sys/oracle@india cmdfile=/home/oracle/nassyam/restore_MAADG.rcv log=/home/oracle/nassyam/restore_MAADG.log

date

echo "End restore"

 

 

---> exit

chmod 775 /home/oracle/nassyam/restore_MAADG.sh

vi /home/oracle/nassyam/restore_MAADG.rcv

 

run

{

ALLOCATE CHANNEL MAADG1 DEVICE TYPE disk;

ALLOCATE CHANNEL MAADG2 DEVICE TYPE disk;

ALLOCATE AUXILIARY CHANNEL MAADG3 DEVICE TYPE disk;

ALLOCATE AUXILIARY CHANNEL MAADG5 DEVICE TYPE disk;

duplicate target database for standby from active database;

RELEASE CHANNEL MAADG1;

RELEASE CHANNEL MAADG2;

RELEASE CHANNEL MAADG3;

RELEASE CHANNEL MAADG4;

}

 

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

exit

 

chmod 777 /home/oracle/nassyam/restore_MAADG.rcv

 

$ nohup /home/oracle/nassyam/restore_MAADG.sh &

 

10. Deploy the Duplicate – As mentioned earlier the duplicate we are launching in background,

[oracle@ora-r2n3 nassyam]$ ls -ltr

total 8

-rwxrwxr-x 1 oracle oinstall 363 Mar 15 20:37 restore_MAADG.sh

-rwxrwxr-x 1 oracle oinstall 357 Mar 15 20:41 restore_MAADG.rcv

[oracle@ora-r2n3 nassyam]$ nohup /home/oracle/nassyam/restore_MAADG.sh &

[1] 11406

[oracle@ora-r2n3 nassyam]$ nohup: appending output to `nohup.out'

 

[oracle@ora-r2n3 nassyam]$ tail -100f restore_MAADG.log

 

Recovery Manager: Release 12.1.0.1.0 - Production on Tue Mar 15 20:44:13 2016

 

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

 

connected to target database: ORCL (DBID=1434250731)

connected to auxiliary database: ORCL (not mounted)

 

RMAN> run

2> {

3> ALLOCATE CHANNEL MAADG1 DEVICE TYPE disk;

4> ALLOCATE CHANNEL MAADG2 DEVICE TYPE disk;

5> ALLOCATE AUXILIARY CHANNEL MAADG3 DEVICE TYPE disk;

6> ALLOCATE AUXILIARY CHANNEL MAADG5 DEVICE TYPE disk;

7> duplicate target database for standby from active database;

8> RELEASE CHANNEL MAADG1;

9> RELEASE CHANNEL MAADG2;

10> RELEASE CHANNEL MAADG3;

11> RELEASE CHANNEL MAADG4;

12> }

13>

14>

using target database control file instead of recovery catalog

allocated channel: MAADG1

channel MAADG1: SID=48 instance=CANADA1 device type=DISK

 

......

.....

....

Starting Duplicate Db at 2016-03-15:20:44:28

 

contents of Memory Script:

{

backup as copy reuse

targetfile '+DG1/CANADA/orapwcanada' auxiliary format

'/u01/app/oracle/product/12.1.0.1/db_1/dbs/orapwINDIA' ;

}

executing Memory Script

......

.....

....

Starting backup at 2016-03-15:20:44:28

Finished backup at 2016-03-15:20:44:30

 

sql statement: alter system set control_files = ''+DATA/INDIA/CONTROLFILE/current.275.906583475'', ''+FRA/INDIA/CONTROLFILE/current.261.906583475'' comment= ''Set by RMAN'' scope=spfile

 

 

Starting restore at 2016-03-15:21:06:21

 

channel MAADG3: starting datafile backup set restore

channel MAADG3: using network backup set from service canada

channel MAADG3: restoring control file

channel MAADG3: restore complete, elapsed time: 00:00:07

output file name=+DATA/INDIA/CONTROLFILE/current.274.906584785

output file name=+FRA/INDIA/CONTROLFILE/current.260.906584787

Finished restore at 2016-03-15:21:06:30

 

......

.....

....

Starting restore at 2016-03-15:21:06:38

 

channel MAADG3: starting datafile backup set restore

channel MAADG3: using network backup set from service canada

channel MAADG3: specifying datafile(s) to restore from backup set

channel MAADG3: restoring datafile 00001 to +DATA

channel MAADG5: starting datafile backup set restore

channel MAADG5: using network backup set from service canada

channel MAADG5: specifying datafile(s) to restore from backup set

channel MAADG5: restoring datafile 00003 to +DATA

channel MAADG3: restore complete, elapsed time: 00:00:35

channel MAADG3: starting datafile backup set restore

channel MAADG3: using network backup set from service canada

channel MAADG3: specifying datafile(s) to restore from backup set

channel MAADG3: restoring datafile 00004 to +DATA

channel MAADG5: restore complete, elapsed time: 00:00:35

channel MAADG5: starting datafile backup set restore

channel MAADG5: using network backup set from service Canada

......

.....

....

 

Finished restore at 2016-03-15:21:07:50

 

sql statement: alter system archive log current

 

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

 

datafile 1 switched to datafile copy

input datafile copy RECID=10 STAMP=906584876 file name=+DATA/INDIA/DATAFILE/system.278.906584801

......

.....

....

datafile 8 switched to datafile copy

input datafile copy RECID=16 STAMP=906584878 file name=+DATA/INDIA/DATAFILE/undotbs2.284.906584861

Finished Duplicate Db at 2016-03-15:21:08:31

 

released channel: MAADG1

 

released channel: MAADG2

 

released channel: MAADG3

 

released channel: MAADG5

 

11. Check Standby Database status – After the successful duplicate of database, we can see that standby database is created and it’s in mount status.

SQL> select db_unique_name,database_role,open_mode from v$database;

 

DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE

------------------------------ ---------------- --------------------

INDIA PHYSICAL STANDBY MOUNTED

 

SQL>

 

12. Configure Oracle Restart for standby – The deployed database is normal database, now we have to attach to the Oracle restart so that we can manage database using srvctl, below is the procedure to do. The standby instance must use the SPFILE in order to configure Data Guard broker and hence first we will create pfile from the spfile which was created during Duplicate and then we create spfile in ASM from the local pfile.

SQL> show parameter pfile

 

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string

SQL> create spfile='+DATA' from pfile;

 

File created.

 

SQL>

ASMCMD> pwd

+data/india/parameterfile

ASMCMD> ls -lt

Type Redund Striped Time Sys Name

PARAMETERFILE UNPROT COARSE MAR 15 21:00:00 Y spfile.293.906585955

ASMCMD>

[oracle@ora-r2n3 dbs]$ mv initINDIA.ora initINDIA_15Mar.ora

[oracle@ora-r2n3 dbs]$ vi initINDIA.ora

[oracle@ora-r2n3 dbs]$ cat initINDIA.ora

spfile='+DATA/INDIA/PARAMETERFILE/spfile.293.906585955'

[oracle@ora-r2n3 dbs]$

SQL> shut immediate;

ORA-01109: database not open

 

 

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

 

Total System Global Area 705662976 bytes

Fixed Size 2292384 bytes

Variable Size 297796960 bytes

Database Buffers 402653184 bytes

Redo Buffers 2920448 bytes

Database mounted.

SQL> show parameter pfile

 

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string +DATA/INDIA/PARAMETERFILE/spfi

le.293.906585955

SQL>

 

  • Now the database is started with spfile which located in ASM. Next add the database to the oracle restart.

 

[oracle@ora-r2n3 dbs]$ echo $ORACLE_HOME

/u01/app/oracle/product/12.1.0.1/db_1

[oracle@ora-r2n3 dbs]$ srvctl add database -d INDIA -o /u01/app/oracle/product/12.1.0.1/db_1 -m oracle-ckpt.com -n orcl -p +DATA/INDIA/PARAMETERFILE/spfile.293.906585955 -s OPEN -r PHYSICAL_STANDBY -y automatic -a DATA,FRA

[oracle@ora-r2n3 dbs]$

[oracle@ora-r2n3 dbs]$ srvctl config database -d india

Database unique name: INDIA

Database name: orcl

Oracle home: /u01/app/oracle/product/12.1.0.1/db_1

Oracle user: oracle

Spfile: +DATA/INDIA/PARAMETERFILE/spfile.293.906585955

Password file:

Domain: oracle-ckpt.com

Start options: open

Stop options: immediate

Database role: PHYSICAL_STANDBY

Management policy: AUTOMATIC

Database instance: INDIA

Disk Groups: DATA,FRA

Services:

[oracle@ora-r2n3 dbs]$

 

After adding database to the configuration, we can manage database using srvctl to stop and start.

 

[oracle@ora-r2n3 dbs]$ srvctl status database -d india

Database is not running.

[oracle@ora-r2n3 dbs]$ srvctl stop database -d india

PRCC-1016 : INDIA was already stopped

[oracle@ora-r2n3 dbs]$ ps -ef|grep pmon

oracle 9311 1 0 18:18 ? 00:00:01 asm_pmon_+ASM

oracle 9388 1 0 18:18 ? 00:00:01 ora_pmon_CDBGG

oracle 14029 1 0 21:37 ? 00:00:00 ora_pmon_INDIA

oracle 14407 12859 0 21:47 pts/2 00:00:00 grep pmon

[oracle@ora-r2n3 dbs]$

 

SQL> shut immediate;

ORA-01109: database not open

 

 

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

[oracle@ora-r2n3 dbs]$ srvctl start database -d india -o mount

[oracle@ora-r2n3 dbs]$ srvctl status database -d india

Database is running.

[oracle@ora-r2n3 dbs]$

 

13. Configure Data Guard – Surprised why we are using broker without configuring any Data Guard parameters in SPFILE? Yes that is true, we are going to use the advanced method and simplified method to avoid misconfiguration to the Data Guard.

    1. Enable the data guard broker using the dg_broker_start to TRUE.

       

SQL> select database_role from v$database;

 

DATABASE_ROLE

----------------

PRIMARY

 

SQL> alter system set dg_broker_start=true scope=both sid='*';

 

System altered.

 

SQL>

SQL> select database_role from v$database;

 

DATABASE_ROLE

----------------

PHYSICAL STANDBY

 

    1. Configure the Data Guard configuration files to create under the ASM disk group, because there will be common configuration files for the RAC node 1 and Node 2.

       

SQL> alter system set dg_broker_config_file1='+DG1/ORCL/dr1CANADA.dat' scope=both sid='*';

 

System altered.

 

SQL> alter system set dg_broker_config_file2='+DGFRA1/ORCL/dr2CANADA.dat' scope=both sid='*';

 

System altered.

 

SQL> alter system set dg_broker_start=true scope=both sid='*';

 

System altered.

 

SQL>

[oracle@ora-r2n1 admin]$ ps -ef|grep dmon

oracle 1323 1 0 21:55 ? 00:00:00 ora_dmon_CANADA1

oracle 1353 30647 0 21:56 pts/1 00:00:00 grep dmon

root 23119 1 0 18:14 ? 00:00:09 /u01/app/12.1.0.1/grid/bin/cssdmonitor

 

    1. The whole Data Guard configuration is only three commands to create configuration, adding standby database and enabling the configuration like below.

       

[oracle@ora-r2n1 admin]$ dgmgrl /

DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

 

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

 

Welcome to DGMGRL, type "help" for information.

Connected as SYSDG.

DGMGRL>

DGMGRL> create configuration haconfig as primary database is canada connect identifier is canada;

Configuration "haconfig" created with primary database "canada"

 

DGMGRL> add database india as connect identifier is india maintained as physical;

Database "india" added

DGMGRL>

 

DGMGRL> enable configuration

Enabled.

DGMGRL> show configuration

 

Configuration - haconfig

 

Protection Mode: MaxPerformance

Databases:

canada - Primary database

india - Physical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

SUCCESS

 

DGMGRL>

 

 

2016-03-15 22:31:05.292 >> Starting Data Guard Broker bootstrap <<

2016-03-15 22:31:05.293 Broker Configuration File Locations:

2016-03-15 22:31:05.293 dg_broker_config_file1 = "+DG1/ORCL/dr1CANADA.dat"

2016-03-15 22:31:05.294 dg_broker_config_file2 = "+DGFRA1/ORCL/dr2CANADA.dat"

2016-03-15 22:31:05.298 DMON: Attach state object

2016-03-15 22:31:05.366 DMON: Broker state reconciled, version = 0, state = 00000000

2016-03-15 22:31:05.366 DMON: Broker State Initialized

 

Summary

 

We’ve seen the step by step procedure for “MAA – Creating Single instance Physical Standby for a RAC primary - 12c” using the simple steps to achieve flexible architecture and how easy to configure Data Guard using broker which is highly recommended with advanced methods to achieve such configuration.