two Node RAC Dataguard setup

Oracle 11g R2 two Node RAC Dataguard setup

by

In this article, we are going to learn the oracle 11g R2 two node RAC dataguard setup in RHEL 6.8 OS.

Here we going to configure the data guard (primary and standby) and RAC with physical standby.

Environment Details

two node RAC dataguard setup

We have already set up two RAC databases on 11gR2 nodes in prodrac101 and prodrac102, with the database name ORCL and unique database name ORCL_PRODRAC1 to be the main site.

If you are looking for the two-node RAC setup, refer to this page. It’s available in 12cR1, however, the 11gR2 and 12cR1 RAC setups are the same. We can therefore use the same procedure in the case of the dual-node RAC primary setup for 11gR2 too.

Let’s get started on the two node RAC dataguard setup in 11gR2.

As with the primary(prodrac101 and prodrac102), site grid software and database software must be installed in the standby (prodrac201 and prodrac202) site.

two Node RAC Dataguard setup

Primary RAC Environment

#Public IP
192.168.1.11  prodrac101.oracle.com  prodrac101
192.168.1.12  prodrac102.oracle.com  prodrac102

#Private IP
192.168.2.11  prodprv101.oracle.com  prodprv101
192.168.2.12  prodprv102.oracle.com  prodprv102

#Virtual IP
192.168.1.14  prodvip101.oracle.com  prodvip101
192.168.1.15  prodvip102.oracle.com  prodvip102

#Scan IP
192.168.1.18  prodscn101.oracle.com  prodscn101
192.168.1.19  prodscn101.oracle.com  prodscn101
192.168.1.20  prodscn101.oracle.com  prodscn101

Standby RAC Environment

#Public IP
192.168.1.21  prodrac201.oracle.com  prodrac201
192.168.1.22  prodrac202.oracle.com  prodrac202

#Private IP
192.168.2.21  prodprv201.oracle.com  prodprv201
192.168.2.22  prodprv202.oracle.com  prodprv202

#Virtual IP
192.168.1.24  prodvip201.oracle.com  prodvip201
192.168.1.25  prodvip202.oracle.com  prodvip202

#Scan IP
192.168.1.28  prodscn201.oracle.com  prodscn201
192.168.1.29  prodscn201.oracle.com  prodscn201
192.168.1.30  prodscn201.oracle.com  prodscn201

Configure below prameters on Primary RAC database for DR configuration

The parameters listed below must be configure on primary RAC database (two node RAC dataguard setup)

  1. Database Unique Name
  2. Enable Force Logging
  3. Enable Archivelog Mode
  4. Modify init Parameters
  5. Create the Standby Redo Logs
  6. Create pfile for standby database
  7. Update the tnsnames.ora

Update Database Unique Name & enable Force Logging

Use the below commands to update unique name and other parameters.

SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='ORCL_PRODRAC1' SCOPE=SPFILE SID='*';
System altered.

$ srvctl stop database -d ORCL
$ srvctl start database -d ORCL_PRODRAC1 -o mount

SQL> ALTER DATABASE FORCE LOGGING;
Database altered.

SQL> ALTER DATABASE ARCHIVELOG;  <--- Run If not in archivelog mode
Database altered.

$ srvctl stop database -d ORCL_PRODRAC1

$ srvctl start database -d ORCL_PRODRAC1

SQL> SHOW PARAMETER DB_UNIQUE_NAME

NAME           TYPE        VALUE
-------------  ----------  ------------------------------
db_unique_name string      ORCL_PRODRAC1

Enable archive locations

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL_PRODRAC1,ORCL_PRODRAC2)' sid='*';
System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL_PRODVIP201 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_PRODRAC2' sid='*';
System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE sid='*';
System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='ORCL_%t_%s_%r.arc' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 sid='*';
System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET FAL_SERVER='ORCL_PRODRAC2' sid='*';
System altered.

SQL> ALTER SYSTEM SET FAL_CLIENT='ORCL_PRODRAC1' sid='*';
System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH SID='*';
System altered.

Add the standby redolog files in primary database

Add the standby redolog files and complete the two node RAC dataguard setup.



SQL> alter database add standby logfile thread 1 group 7 '+OCP_DATA' size 50M;
Database altered.                                          
														   
SQL> alter database add standby logfile thread 1 group 8 '+OCP_DATA' size 50M;
Database altered.                                          
														   
SQL> alter database add standby logfile thread 1 group 9 '+OCP_DATA' size 50M;
Database altered.

SQL> alter database add standby logfile thread 2 group 10 '+OCP_DATA' size 50M;
Database altered.                                           
															
SQL> alter database add standby logfile thread 2 group 11 '+OCP_DATA' size 50M;
Database altered.                                           
															
SQL> alter database add standby logfile thread 2 group 12 '+OCP_DATA' size 50M;
Database altered.

Use the below query on both primary database nodes to find the newly added standby redlog files:

set lines 999 pages 999
col inst_id for 9999
col group# for 9999
col member for a60
col archived for a7

select * from
(select '[ ONLINE REDO LOG ]' AS "REDOLOG FILE TYPE", a.inst_id AS inst_id, a.group#, b.thread#, b.sequence#, a.member, b.status, b.archived, (b.BYTES/1024/1024) AS SIZE_MB
from gv$logfile a, gv$log b
where a.group#=b.group#
and a.inst_id=b.inst_id
and b.thread#=(select value from v$parameter where name = 'thread')
and a.inst_id=( select instance_number from v$instance)
UNION
select '[ STANDBY REDO LOG ]' AS "REDOLOG FILE TYPE", a.inst_id AS inst_id, a.group#, b.thread#, b.sequence#, a.member, b.status, b.archived, (b.BYTES/1024/1024) AS SIZE_MB
from gv$logfile a, gv$standby_log b
where a.group#=b.group#
and a.inst_id=b.inst_id
and b.thread#=(select value from v$parameter where name = 'thread')
and a.inst_id=( select instance_number from v$instance))
order by 2,3;

Node-1

Node1 stbylogs

Node-2

Node2 stbylogs

Verify the configured parameter values using the below query:

set lines 999 pages 999
col value for a110
col name for a50

select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',
'log_archive_format','log_archive_max_processes','fal_server','fal_client','standby_file_management');

Output from above query:

NAME                      VALUE
-------------------       -------------------------------
log_archive_dest_1        LOCATION=+OCP_FRA
log_archive_dest_2        SERVICE=ORCL_PRODVIP201 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_PRODRAC2
log_archive_dest_state_1  enable
log_archive_dest_state_2  ENABLE
fal_client                ORCL_PRODRAC1
fal_server                ORCL_PRODRAC2
log_archive_config        DG_CONFIG=(ORCL_PRODRAC1,ORCL_PRODRAC2)
log_archive_format        ORCL_%t_%s_%r.arc
log_archive_max_processes 30
standby_file_management   AUTO
remote_login_passwordfile EXCLUSIVE
db_name                   ORCL
db_unique_name            ORCL_PRODRAC1

Now create pfile from spfile and create password file also then send these files on standby machine.

SQL> create pfile='/tmp/initORCL1.ora' from spfile;
File created.


$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=manager entries=5 force=y

After copying the password file on standby node, rename the password file with it’s respective $ORACLE_SID name. For example, Primary & Standby Node1 password file name should be like orapwORCL1 and Primary & Standby Node2 password file name should be like orapwORCL2.

Create a temporarily static listener inside the oracle home in standby node1 and start it for active duplicate data guard setup.

Listener file entries like:

cat listener.ora <-- Standby Node1

SID_LIST_LISTENER1 =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = ORCL_PRODRAC201)
   (ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
   (SID_NAME = ORCL1)
  )
 )

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = prodrac201.oracle.com)(PORT = 1525))
    )
  )

ADR_BASE_LISTENER1 = /oradb/app/oracle

Start this listener using the below commands (two node RAC dataguard setup)

$lsnrctl start LISTENER1

Add the below entries in the tnsnames.ora file for all the nodes in cluster.

cat tnsnames.ora <-- All Nodes

ORCL_PRODRAC1 =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = prodscn101.oracle.com)(PORT = 1521)))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC1)))

ORCL_PRODVIP101 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip101.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC1)
(INSTANCE_NAME = ORCL1)))

ORCL_PRODVIP102 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip102.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC1)
(INSTANCE_NAME = ORCL2)))

ORCL_PRODRAC2 =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = prodscn201.oracle.com)(PORT = 1521)))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(UR = A)))

ORCL_PRODRAC201 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodrac201.oracle.com)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(INSTANCE_NAME = ORCL1)
(UR = A)))


ORCL_PRODVIP201 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip201.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(INSTANCE_NAME = ORCL1)
(UR = A)))

ORCL_PRODVIP202 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip202.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(INSTANCE_NAME = ORCL2)
(UR = A)))

Start configuration on Standby Node 1 for DR setup

  • Make required directories
  • Update pfile for standby

Make directories on prodrac201

mkdir -p /oradb/app/oracle/admin/ORCL_PRODRAC2/adump
mkdir -p /oradb/app/oracle/diag/rdbms/orcl_prodrac2/ORCL1
cd /oradb/app/oracle/diag/rdbms/orcl_prodrac2/ORCL1
mkdir trace cdump

Make directories on prodrac202

mkdir -p /oradb/app/oracle/admin/ORCL_PRODRAC2/adump
mkdir -p /oradb/app/oracle/diag/rdbms/orcl_prodrac2/ORCL2
cd /oradb/app/oracle/diag/rdbms/orcl_prodrac2/ORCL2
mkdir trace cdump

Modify pfile for standby

cat $ORACLE_HOME/dbs/initORCL1.ora

__oracle_base='/oradb/app/oracle' #ORACLE_BASE set from environment
*.audit_file_dest='/oradb/app/oracle/admin/ORCL_PRODRAC2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_create_file_dest='+OCP_DATA'
*.db_create_online_log_dest_1='+OCP_DATA'
*.db_domain=''
*.db_name='ORCL'
*.control_files='+OCP_DATA','+OCP_DATA','+OCP_FRA'
*.db_recovery_file_dest='+OCP_FRA'
*.db_recovery_file_dest_size=12G
*.db_unique_name='ORCL_PRODRAC2'
*.diagnostic_dest='/oradb/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.fal_client='ORCL_PRODRAC2'
*.fal_server='ORCL_PRODRAC1'
*.log_archive_config='DG_CONFIG=(ORCL_PRODRAC1,ORCL_PRODRAC2)'
*.log_archive_dest_1='LOCATION=+OCP_FRA'
*.log_archive_dest_2='SERVICE=ORCL_PRODVIP101 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_PRODRAC1'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='ORCL_%t_%s_%r.arc'
*.log_archive_max_processes=30
*.memory_target=1048576000
*.nls_date_format='DD-MON-YYYY HH24:MI:SS'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.job_queue_processes=20
*.memory_max_target=1200M
*.memory_target=1200M
*.undo_tablespace='UNDOTBS1'
*.undo_management='AUTO'
*.undo_retention=10800
*.service_names='ORCL_PRODRAC2'
*.remote_listener='prodscn201.oracle.com:1521'

Start creating physical standby database

Follow the below steps for physical standby database (two node RAC dataguard setup).

Start the instance in nomount mode

export ORACLE_SID=ORCL1
export ORACLE_BASE=/oradb/app/oracle
export ORACLE_HOME=/oradb/app/oracle/product/11.2.0.4/db_1
export PATH=$ORACLE_HOME/bin:$PATH

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 13 22:12:49 2018

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

Connected to an idle instance.

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initORCL1.ora
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 788533224 bytes
Database Buffers 452984832 bytes
Redo Buffers 8892416 bytes
SQL> exit

Restore standby database using RMAN duplicate command

$ rman target sys/rider@ORCL_PRODVIP101 auxiliary sys/rider@ORCL_PRODRAC201

Use the below script for recovery:

RMAN> run
2> {
3> allocate channel ch1 device type disk;
4> allocate channel ch2 device type disk;
5> allocate auxiliary channel ch3 device type disk;
6> allocate auxiliary channel ch4 device type disk;
7> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;
8> release channel ch1;
release channel ch2;
9> 10> release channel ch3;
11> release channel ch4;
12> }

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=105 instance=ORCL1 device type=DISK
allocated channel: ch2
channel ch2: SID=112 instance=ORCL1 device type=DISK
allocated channel: ch3
channel ch3: SID=29 device type=DISK
allocated channel: ch4
channel ch4: SID=1 device type=DISK
Starting Duplicate Db at 30-MAR-22 02:17:56
contents of Memory Script:
{
backup as copy reuse
targetfile '/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL1' auxiliary format
'/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL1' ;
}
executing Memory Script
Starting backup at 30-MAR-22 02:17:56
Finished backup at 30-MAR-22 02:17:57
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+OCP_DATA/orcl_prodrac2/controlfile/current.272.984104279';
restore clone controlfile to '+OCP_DATA/orcl_prodrac2/controlfile/current.273.984104279' from '+OCP_DATA/orcl_prodrac2/controlfile/current.272.984104279';
restore clone controlfile to '+OCP_FRA/orcl_prodrac2/controlfile/current.267.984104279' from '+OCP_DATA/orcl_prodrac2/controlfile/current.272.984104279';
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =''+OCP_DATA/orcl_prodrac2/controlfile/current.272.984104279'', ''+OCP_DATA/orcl_prodrac2/controlfile/current.273.984104279'', ''+DBWR_FRA/orcl_prodrac2/controlfile/current.267.984104279'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Starting backup at 30-MAR-22 02:17:58
channel ch1: starting datafile copy
copying standby control file
output file name=/oradb/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_ORCL1.f tag=TAG20180814T021759 RECID=12 STAMP=984104282
channel ch1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 30-MAR-22 02:18:06
Starting restore at 30-MAR-22 02:18:06
channel ch4: skipped, AUTOBACKUP already found
channel ch3: copied control file copy
Finished restore at 30-MAR-22 02:18:14
Starting restore at 30-MAR-22 02:18:15
channel ch4: skipped, AUTOBACKUP already found
channel ch3: copied control file copy
Finished restore at 30-MAR-22 02:18:23
sql statement: create spfile from memory
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 805310440 bytes
Database Buffers 436207616 bytes
Redo Buffers 8892416 bytes
allocated channel: ch3
channel ch3: SID=29 device type=DISK
allocated channel: ch4
channel ch4: SID=30 device type=DISK
sql statement: alter system set control_files = ''+OCP_DATA/orcl_prodrac2/controlfile/current.272.984104279'', ''+OCP_DATA/orcl_prodrac2/controlfile/current.273.984104279'', ''+DBWR_FRA/orcl_prodrac2/controlfile/current.267.984104279'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 805310440 bytes
Database Buffers 436207616 bytes
Redo Buffers 8892416 bytes
allocated channel: ch3
channel ch3: SID=29 device type=DISK
allocated channel: ch4
channel ch4: SID=30 device type=DISK
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;
set newname for clone datafile 6 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
datafile 5 auxiliary format new
datafile 6 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DBWR_DATA in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 30-MAR-22 02:18:59
channel ch1: starting datafile copy
input datafile file number=00001 name=+OCP_DATA/orcl_prodrac1/datafile/system.256.983984619
channel ch2: starting datafile copy
input datafile file number=00002 name=+OCP_DATA/orcl_prodrac1/datafile/sysaux.257.983984621
output file name=+OCP_DATA/orcl_prodrac2/datafile/sysaux.275.984104341 tag=TAG20180814T021900
channel ch2: datafile copy complete, elapsed time: 00:01:57
channel ch2: starting datafile copy
input datafile file number=00005 name=+OCP_DATA/orcl_prodrac1/datafile/example.265.983984851
output file name=+OCP_DATA/orcl_prodrac2/datafile/system.274.984104339 tag=TAG20180814T021900
channel ch1: datafile copy complete, elapsed time: 00:02:24
channel ch1: starting datafile copy
input datafile file number=00003 name=+OCP_DATA/orcl_prodrac1/datafile/undotbs1.258.983984621
output file name=+OCP_DATA/orcl_prodrac2/datafile/undotbs1.268.984104487 tag=TAG20180814T021900
channel ch1: datafile copy complete, elapsed time: 00:00:36
channel ch1: starting datafile copy
input datafile file number=00006 name=+OCP_DATA/orcl_prodrac1/datafile/undotbs2.266.983985173
output file name=+OCP_DATA/orcl_prodrac2/datafile/example.269.984104459 tag=TAG20180814T021900
channel ch2: datafile copy complete, elapsed time: 00:01:04
channel ch2: starting datafile copy
input datafile file number=00004 name=+OCP_DATA/orcl_prodrac1/datafile/users.259.983984621
output file name=+OCP_DATA/orcl_prodrac2/datafile/undotbs2.267.984104523 tag=TAG20180814T021900
channel ch1: datafile copy complete, elapsed time: 00:00:08
output file name=+OCP_DATA/orcl_prodrac2/datafile/users.266.984104523 tag=TAG20180814T021900
channel ch2: datafile copy complete, elapsed time: 00:00:07
Finished backup at 30-MAR-22 02:22:10
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=12 STAMP=984104541 file name=+OCP_DATA/orcl_prodrac2/datafile/system.274.984104339
datafile 2 switched to datafile copy                    
input datafile copy RECID=13 STAMP=984104541 file name=+OCP_DATA/orcl_prodrac2/datafile/sysaux.275.984104341
datafile 3 switched to datafile copy                    
input datafile copy RECID=14 STAMP=984104541 file name=+OCP_DATA/orcl_prodrac2/datafile/undotbs1.268.984104487
datafile 4 switched to datafile copy                    
input datafile copy RECID=15 STAMP=984104541 file name=+OCP_DATA/orcl_prodrac2/datafile/users.266.984104523
datafile 5 switched to datafile copy                    
input datafile copy RECID=16 STAMP=984104541 file name=+OCP_DATA/orcl_prodrac2/datafile/example.269.984104459
datafile 6 switched to datafile copy                    
input datafile copy RECID=17 STAMP=984104542 file name=+OCP_DATA/orcl_prodrac2/datafile/undotbs2.267.984104523
Finished Duplicate Db at 30-MAR-22 02:22:33
released channel: ch1
released channel: ch2
released channel: ch3
released channel: ch4

Stop the temporary listener and add the cluster parameters in standby and convert to two node RAC dataguard (two node RAC dataguard setup)

$lsnrctl stop LISTENER1

Create pfile from spfile and add cluster parameters in pfile then start the standby instance.

SQL> create pfile from spfile;
File created.

SQL> shut immediate

Add below cluster parameters in pfile.

*.cluster_database_instances=2
*.cluster_database=true
ORCL1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1521))'
ORCL2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.25)(PORT=1521))'
ORCL1.instance_number=1
ORCL2.instance_number=2
ORCL1.instance_name='ORCL1'
ORCL2.instance_name='ORCL2'
ORCL1.thread=1
ORCL2.thread=2
ORCL1.undo_tablespace='UNDOTBS1'
ORCL2.undo_tablespace='UNDOTBS2'

Copy the updated pfile on standby Node2 and startup the instance in both nodes (two node RAC dataguard setup)

Standby Node 1.

$ export ORACLE_SID=ORCL1
$ export ORACLE_HOME=/oradb/app/oracle/product/11.2.0.4/db_1
$ orapwd file=$ORACLE_HOME/dbs/orapwORCL1 password=manager force=y

$ sqlplus / as sysdba

SQL> startup mount pfile=$ORACLE_HOME/dbs/initORCL1.ora

Standby Node 2.

$ export ORACLE_SID=ORCL2
$ export ORACLE_HOME=/oradb/app/oracle/product/11.2.0.4/db_1
$ orapwd file=$ORACLE_HOME/dbs/orapwORCL2 password=manager force=y

$ sqlplus / as sysdba

SQL> startup mount pfile=$ORACLE_HOME/dbs/initORCL2.ora

Now it’s time to register both standby database instances with CRS framework using following commands:

$ srvctl add database -d ORCL_PRODRAC2 -o /oradb/app/oracle/product/11.2.0.4/db_1
$ srvctl add instance -d ORCL_PRODRAC2 -i ORCL1 -n prodrac201
$ srvctl add instance -d ORCL_PRODRAC2 -i ORCL2 -n prodrac202

Now both instances are registered with CRS, shutdown both the instance in standby side and start using srvctl command. (two node RAC dataguard setup)

$ srvctl start database -d ORCL_PRODRAC2 -o mount
$ srvctl status database -d ORCL_PRODRAC2 -v

Start MRP process in standby side.

SQL> alter database recover managed standby database disconnect from session;

Create spfile from pfile in ASM environment on both nodes.

SQL> create spfile='+OCP_DATA/ORCL_PRODRAC2/PARAMETERFILE/spfileORCL.ora' from pfile;

File created.

Check both instances are converted in RAC environment or not using the below commands.

SQL> set lines 180
SQL> col INST_NAME for a80

SQL> select * from v$active_instances;

INST_NUMBER   INST_NAME
------------  -------------------------------------------
          1   prodrac201.oracle.com:ORCL1
          2   prodrac202.oracle.com:ORCL2

Here, I am starting Node1 of Standby and starting the Media Recovery Process in it to apply archivelog from both nodes of primary.

$ srvctl stop database -d ORCL_PRODRAC2
$ srvctl start instance -d ORCL_PRODRAC2 -i ORCL1 -o mount 
$ srvctl status database -d ORCL_PRODRAC2 -v

$ sqlplus / as sysdba

SQL> alter database recover managed standby database disconnect from session;

Now check the archives is coming from primary database of both nodes, it yes that means your two node RAC dataguard setup is completed.

1 thought on “Oracle 11g R2 two Node RAC Dataguard setup”

  1. Pingback: Why we use FAL_SERVER and FAL_CLIENT

Leave a Comment