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.
-
-
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.
-
We can also skip adding PDB database but it DOES NOT MEET MAA.
-
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.
-
-
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
-
-
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
-
-
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.
Excellent article!
Easy to follow and implement.
Thank you.