Step by Step Build of Standby (dataguard) in two node RAC

Hello All,

Here are the steps to implement the Standby in RAC and the following is the test environment

Production RAC:
Geek DBA12cprmy1
Geek DBA12cprmy2
Standby RAC:-
Geek DBA12cdr1
Geek DBA12cdr2

1 . Add standby logs on Primary Database

alter database add standby logfile thread 1 group 10 ('+PRMY_DATA') size 500M;
alter database add standby logfile thread 1 group 11 ('+PRMY_DATA') size 500M;
alter database add standby logfile thread 1 group 12 ('+PRMY_DATA') size 500M;
alter database add standby logfile thread 2 group 13 ('+PRMY_DATA') size 500M;
alter database add standby logfile thread 2 group 14 ('+PRMY_DATA') size 500M;
alter database add standby logfile thread 2 group 15 ('+PRMY_DATA') size 500M;

2. Enable force logging on Primary Database

alter database force logging;

3. In the standby database home, create and start a listener that offers a static SID entry for the standby database .

In Database home

LISTENER_Geek DBA12cdr1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST = Geek DBA12cdr1-vip.localdomain)(PORT = 1521))
)
)
)

SID_LIST_LISTENER_Geek DBA12cdr1 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/sq/oracle/db/11.2.0.4)
(SID_NAME = STBY)
)
)

tnsnames.ora

PRMY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Geek DBA12cprmy1-vip.localdomain )(PORT = 2001))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRMY.localdomain)
)
)

STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Geek DBA12cdr1-vip.localdomain)(PORT = 1521))
(CONNECT_DATA = (UR=A)
(SERVER = DEDICATED)
(SERVICE_NAME = STBY)
)
)

NOTE1 : for STBY tns string "(UR=A)" this required to connect to the standby instance even though the standby instance broguht down or in blocked state .

NOTE2 : Create a dedicated Primary database connection (tns entry shld point directly to any of the instance using VIP ) .Scan ip shld not be used .

4 .Create a TNS entry on Primary server for standby entry.

STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Geek DBA12cdr1-vip.localdomain)(PORT = 1521))
(CONNECT_DATA = (UR=A)
(SERVER = DEDICATED)
(SERVICE_NAME = STBY)
)
)

4. Time being modify the tnsnames.ora in primary to local vip or create new tns rather scan as like below

PRMY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Geek DBA12cprmy1-vip.localdomain )(PORT = 2001))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRMY.localdomain)
)
)

5. Copy the Passwordfile from Primary server to the standby server and rename it as per the standby instance name.

scp $ORACLE_HOME/dbs/orapwPRMY1 Geek DBA12cdr1-vip.localdomain:/u01/sq/oracle/db/11.2.0.4/dbs/orapwSTBY

6. on standby host create a pfile as given below.

cat initSTBY1.ora
DB_NAME=PRMY
db_unique_name='STBY'
STBY1.instance_name='STBY1'
STBY2.instance_name='STBY2'
STBY1.instance_number=1
STBY2.instance_number=2
local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Geek DBA12cdr1-vip.localdomain)(PORT=1521))))'

NOTE : Local_listener parameter is required because we have another listener running from GRID . We are explicituly registering the STBY1 auxiliary instance with the Static Listener

7. Create Audit directory on standby server . Look the Primart database value and create the same directory structure on DR.

mkdir -p /u01/sq/oracle/admin/STBY/adump
export ORACLE_SID=STBY
sqlplus
startup nomount

8 . TNSPING all the tns alias on both primary and standby to crosscheck everthing is working fine .

9 . create a RMAN script on DR server as below and execute it from RMAN prompt

cat rman_script.sql

########################
From production server
########################

connect target sys/*****@PRMY;

connect auxiliary sys/*****@STBY;

run
{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby1 type disk;
allocate auxiliary channel stby2 type disk;
allocate auxiliary channel stby3 type disk;
allocate auxiliary channel stby4 type disk;

DUPLICATE DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK SPFILE
PARAMETER_VALUE_CONVERT 'PRMY','STBY'
SET instance_name='STBY1'
SET instance_number='1'
SET db_unique_name='STBY'
SET control_files='+STBY_DATA','+STBY_FRA'
SET db_file_name_convert='+PRMY_DATA','+STBY_DATA','+PRMY_FRA01','+STBY_FRA','+PRMY_DATA/PRMY','+STBY_DATA/STBY','+PRMY_FRA/PRMY','+STBY_FRA/STBY'
SET log_file_name_convert='+PRMY_DATA','+STBY_DATA','+PRMY_FRA01','+STBY_FRA','+PRMY_DATA/PRMY','+STBY_DATA/STBY','+PRMY_FRA/PRMY','+STBY_FRA/STBY'
SET db_recovery_file_dest='+STBY_FRA'
SET db_recovery_file_dest_size='20G'
SET log_archive_max_processes='5'
SET fal_client='STBY'
SET fal_server='PRMY'
SET standby_file_management='AUTO'
SET log_archive_config='dg_config=(PRMY,STBY)'
SET log_archive_dest_2='service=PRMY lgwr async noaffirm COMPRESSION=ENABLE valid_for=(online_logfiles,primary_role) db_unique_name=PRMY';

SQL channel prmy1 "alter system set log_archive_config=''dg_config=(PRMY,STBY)''";
SQL channel prmy1 "alter system set log_archive_dest_2=''service=STBY lgwr async noaffirm COMPRESSION=ENABLE valid_for=(online_logfiles,primary_role) db_unique_name=STBY ''";
SQL channel prmy1 "alter system set log_archive_max_processes=5";
SQL channel prmy1 "alter system set fal_client=STBY";
SQL channel prmy1 "alter system set fal_server=PRMY";
SQL channel prmy1 "alter system set standby_file_management=''AUTO''";
SQL channel prmy1 "alter system archive log current";
sql channel stby1 "alter database recover managed standby database using current logfile disconnect from session";
}

exit

rman
@rman_script.sql

10 . copy the Password file to the second instance .

scp $ORACLE_HOME/dbs/orapwPRMY1 Geek DBA12cdr1:/u01/sq/oracle/db/11.2.0.4/dbs/orapwSTBY1
scp $ORACLE_HOME/dbs/orapwPRMY1 Geek DBA12cdr2:/u01/sq/oracle/db/11.2.0.4/dbs/orapwSTBY2

11 . create pfile from the curennt spfile and then create spfile in to the ASM .

create pfile='/home/oracle/test.ora' from spfile;

Modify the parameters, PRMY1 to STBY1 and PRMY2 to STBY2 (attached)

create spfile='+STBY_DATA/STBY/spfileSTBY.ora' from pfile='/home/oracle/test.ora';

12 . Create pfile with the instance names on standby nodes to point to the spfile .

Host: Primary
cd $ORACLE_HOME/dbs/
vi initSTBY1.ora
spfile='+STBY_DATA/STBY/spfileSTBY.ora'

Host: Second standby host
cd $ORACLE_HOME/dbs/
vi initSTBY2.ora
spfile='+STBY_DATA/STBY/spfileSTBY.ora'

13 . Register the database with the crs.

srvctl add database -d STBY -o /u01/sq/oracle/db/11.2.0.4/ -p +STBY_DATA01/STBY/spfileSTBY.ora
srvctl add instance -d STBY -i STBY1 -n Geek DBA12cdr1
srvctl add instance -d STBY -i STBY2 -n Geek DBA12cdr2
srvctl modify database -d STBY -n STBY -o /u01/sq/oracle/db/11.2.0.4/ -r physical_standby -s mount
srvctl modify database -d STBY -o /u01/sq/oracle/db/11.2.0.4/ -p +STBY_DATA/STBY/spfileSTBY.ora

14 .stop and start the database using srvctl .

srvctl start database -d STBY

15. start the recovery mode

alter database recover managed standby database using current logfile disconnect from session;

16 .Check the log synch status on primary and DR . (optional)

set lines 200 pages 1000
select PROCESS,CLIENT_PROCESS,THREAD#,sequence#,status from v$managed_standby;

17. Also set the remote_listener parameter in standby to scap_ip to ensure the connectivity.

Hope this helps!