Setting Active Data Guard Physical Standby in RAC One Node Architecture - Part 1

This article is Part 1 of a two-part series that describes the steps for Setting Active Data Guard Physical Standby in RAC One Node Architecture.

Introduction

Oracle Data Guard is one of the main components from Oracle Database High Availability (HA), an integrated set of solutions, which helps organizations to minimize inactivity, whether planned or not, to guarantee their business flowing. Active Data Guard is a licensed option for Oracle Database Enterprise Edition that broadens basic Data Guard [1] functionality by allowing advanced resources, such as: consultations, reports, standby database updates which do not affect the primary database, automatic corrupted data blocks recovery, incremental backups, among others.

Oracle RAC One Node utilizes a shared disk architecture to provide a high database availability solution, similar to Oracle Real Application Clusters (RAC). Unlike Oracle RAC, which executes several instances simultaneously, Oracle RAC One Node provides a failover solution in one instance only, plus, it can make the infrastructure in cluster easier, being easily updated to an Oracle Real Application Clusters settings with several instances.

Environment

Primary database is using Oracle RAC with 2 Nodes
Protection mode used will be “Maximum Performance” using mode “asynchronous redo transport”
“Fast Start Failover” feature will not be used.
Data Guard Broker settings

1. Set db_unique_name to Primary Database:

sqlplus> alter system set db_unique_name='analytics' scope=spfile sid='*';

2. Copy “Oracle password file” to server standby:

scp orapwanalytics oracle@hostdr:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs

3. In Nodes 1 and 2 bank instances of the primary server, add entries in listener.ora:

Node 1:

(SID_DESC =
(GLOBAL_DBNAME = analytics1_dgmgrl)
(SID_NAME = analytics1)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)
)

Node 2:

 
(SID_DESC =
(GLOBAL_DBNAME = analytics2_dgmgrl)
(SID_NAME = analytics2)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)
)

4. In standby server, add entry in listener.ora

 
(SID_DESC =
(GLOBAL_DBNAME = analytics_DG_DGMGRL)
(SID_NAME = analytics_dg)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)
)

After these settings, executing command “listener reload” is needed in order to force listener to read the listener.ora entries again. Attention! This operation will affect listener’s availability.

5. Add entries in file tnsnames.ora in Primary and Standby Database;

5.1. Comment old entry

 
#analytics =
# (DESCRIPTION =
# (ADDRESS = (PROTOCOL = TCP)(HOST = hostdr-pscan1)(PORT = 1521))
# (CONNECT_DATA =
# (SERVER = DEDICATED)
# (SERVICE_NAME = analytics)
# ))

5.2. Add new entries:

 
analytics =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostdr-pscan1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = analytics)
(UR=A)
))

analytics1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostdr01vmp01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = analytics)
(SID = analytics1)
(UR=A)
))

analytics2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostdr02vmp01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = analytics)
(SID = analytics2)
(UR=A)
))

analytics1_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostdr01vmp01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = analytics_DGMGRL)
(UR=A)
))

analytics2_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostdr02vmp01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = analytics_DGMGRL)
(UR=A)
))

5.3. DG’s entries:

analytics_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01vmp01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = analytics_dg) (UR = A)
))

analytics_DG_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01vmp01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = analytics_dg_dgmgrl)
(UR=A)
))

6. Test new entries in file tnsnames.ora

 
tnsping analytics
tnsping analytics1
tnsping analytics2
tnsping analytics_DG
tnsping analytics1_DGMGRL
tnsping analytics2_DGMGRL
tnsping analytics_DG_DGMGRL

7. Enable “Force Logging” and “Archivelog mode” in Primary Database. Note: The instance was already configured in archivelog mode.

 
sqlplus> alter database force logging;
sqlplus> select force_logging, log_mode from v$database;

FORCE_LOGGING LOG_MODE
--------------------------------------- ------------
YES ARCHIVELOG

8. Verify RedoLogs settings in Primary Database. Used script logfiles.sql is available at oracle-base.com.

9. Add Standby Log Files.

To determine the recommended number of standby redo logs, use the following formula [2]:

(maximum # of logfile groups +1) * maximum # of threads

My primary database has two threads and each one has four redo online groups, therefore, we should have ten standby redo logs.

(maximum # of logfile groups +1) * maximum # of threads = ((4 + 1) * 2 = 10)

 
sqlplus> alter database add standby logfile thread 1 group 41 ('+DG_RECO_DR') size 1024M;
sqlplus> alter database add standby logfile thread 2 group 42 ('+DG_RECO_DR') size 1024M;
sqlplus> alter database add standby logfile thread 1 group 43 ('+DG_RECO_DR') size 1024M;
sqlplus> alter database add standby logfile thread 2 group 44 ('+DG_RECO_DR') size 1024M;
sqlplus> alter database add standby logfile thread 1 group 45 ('+DG_RECO_DR') size 1024M;
sqlplus> alter database add standby logfile thread 2 group 46 ('+DG_RECO_DR') size 1024M;
sqlplus> alter database add standby logfile thread 1 group 47 ('+DG_RECO_DR') size 1024M;
sqlplus> alter database add standby logfile thread 2 group 48 ('+DG_RECO_DR') size 1024M;
sqlplus> alter database add standby logfile thread 2 group 49 ('+DG_RECO_DR') size 1024M;
sqlplus> alter database add standby logfile thread 2 group 50 ('+DG_RECO_DR') size 1024M;

10. In Primary database create a pfile which will be the standby database parameters files’s base.

 
create pfile='/tmp/initanalytics_dg_aux.ora' from spfile;

11. In Standby database create the file pfile with the remote server’s information. The easiest way to create this file is to copy Primary Database’s parameters file (item 10) to Standby Database and edit it.

$ cd $ORACLE_HOME/dbs
$ vi initanalytics_dg_aux.ora

#*.audit_file_dest='/u01/app/oracle/admin/analytics/adump'
*.audit_file_dest='/u01/app/oracle/admin/analytics_dg/adump'
*.audit_trail='db'
#.cluster_database=true
*.cluster_database=false
*.compatible='12.1.0.2.0'
#*.control_files='+DG_DATA_DR/analytics/controlfile/current.1257.954609365','+DG_RECO_DR/analytics/controlfile/current.527.954609365'
*.control_files='+DG_DATA/analytics_dg/controlfile/current1.ctl','+DG_RECO/analytics_dg/controlfile/
current2.ctl'
*.db_block_size=8192
#*.db_create_file_dest='+DG_DATA_DR'
*.db_create_file_dest='+DG_DATA'
#*.db_create_online_log_dest_1='+DG_DATA_DR'
*.db_create_online_log_dest_1='+DG_DATA'
#*.db_create_online_log_dest_2='+DG_RECO_DR'
*.db_create_online_log_dest_2='+DG_RECO'
*.db_domain=''
*.db_name='analytics'
*.db_files=2000
#*.db_recovery_file_dest='+DG_RECO_DR'
*.db_recovery_file_dest='+DG_RECO'
*.db_recovery_file_dest_size=100G
#*.db_unique_name='analytics'
*.db_unique_name='analytics_dg'

12. Create directory hierarchies.

12.1. Create a directory for “audit dump” in which the files and database auditing will be created.

 
mkdir -p /u01/app/oracle/admin/analytics_dg/adump

12.2. In ASM, create directories for control file and parameter files:

 
mkdir +DG_DATA/analytics_DG/
mkdir +DG_DATA/analytics_DG/PARAMETERFILE
mkdir +DG_DATA/analytics_DG/CONTROLFILE
mkdir +DG_DATA/analytics_DG/BROKERCFG
mkdir +DG_RECO/analytics_DG
mkdir +DG_RECO/analytics_DG/BROKERCFG

13. Create spfile and pfile in Standby Database

 
$ export ORACLE_SID=analytics_dg
$ sqlplus / as sysdba

13.1. Start standby database with pfile initanalytics_dg_aux.ora and convert parameter file (pfile) to spfile.

 
sqlplus> startup nomount pfile='/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/initanalytics_dg_aux.ora';
sqlplus> create spfile='+DG_DATA/analytics_DG/PARAMETERFILE/spfileanalytics_dg.ora' from pfile='/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/initanalytics_dg_aux.ora';

13.2. Create pfile and in this file, insert the following entry:

 
echo "spfile='+DG_DATA/analytics_DG/PARAMETERFILE/spfileanalytics_dg.ora'" >> initanalytics_dg.ora

13.3. Instance’s Startup with spfile to posterior database restoration.

$ export ORACLE_SID=analytics_dg
$ sqlplus sys as sysdba

sqlplus> shutdown immediate;
sqlplus> startup nomount;

14. Run restoration with database’s duplicate;

 
connect target sys/xxxxxx@analytics1;
connect auxiliary sys/xxxxxx@analytics_dg_dgmgrl;
RUN {
Allocate channel pr1 device type disk;
Allocate channel pr2 device type disk;
allocate auxiliary channel stby1 type disk;
allocate auxiliary channel stby2 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;
RELEASE CHANNEL pr1;
RELEASE CHANNEL pr2;
RELEASE CHANNEL stby1;
RELEASE CHANNEL stby2;
}

The next post we will continue this saga!

References

[1] Oracle Active Data Guard Best Practices. Available at: https://www.oracle.com/database/technologies/active-data-guard-12c-best-practice.html

[2] Oracle Database High Availability Best Practices 11g Release 2 (11.2). Available at: https://docs.oracle.com/cd/E24693_01/server.11203/e10803/config_dg.htm

[3] Data Guard Broker. Available at: https://docs.oracle.com/cd/E11882_01/server.112/e40771/dbpropref.htm#DGBKR3781

Emerson Lima