Step by Step Configuration Of Oracle 11gR2 Two Node RAC Dataguard Setup

Step by step configuration of Oracle 11gR2 Two Node RAC Dataguard Setup

Description:-

In this article we are going to configure Oracle 11gR2 two node RAC dataguard setup in RHEL 6.8.

Two Node RAC to Two Node RAC Physical Dataguard:

· Prepare Primary Site
· Prepare Standby Site
· Create RAC Physical Standby Database

Below is the environment details for two node RAC dataguard setup.

Let us consider that we have already configured two node 11gR2 RAC database in prodrac101 & prodrac102 with database name ORCL and database unique name ORCL_PRODRAC1 for primary site.

For two node RAC setup please refer here. It is in 12cR1, whereas both 11gR2 and 12cR1 RAC setup is same. So, we can follow the same steps for two node RAC primary setup in 11gR2 also.

Let’s start the two node dataguard setup for two node RAC in 11gR2.

As similar to primary(prodrac101 & prodrac102) site, the grid software and database software should be installed already in standby(prodrac201 & prodrac202) site.

Overview of Primary RAC environment:-

IP Address Details of Primary:

#Public IP
192.168.1.211  prodrac101.oracledbwr.com  prodrac101
192.168.1.212  prodrac102.oracledbwr.com  prodrac102

#Private IP
192.168.2.211  prodprv101.oracledbwr.com  prodprv101
192.168.2.212  prodprv102.oracledbwr.com  prodprv102

#Virtual IP
192.168.1.214  prodvip101.oracledbwr.com  prodvip101
192.168.1.215  prodvip102.oracledbwr.com  prodvip102

#Scan IP
192.168.1.218  prodscn101.oracledbwr.com  prodscn101
192.168.1.219  prodscn101.oracledbwr.com  prodscn101
192.168.1.220  prodscn101.oracledbwr.com  prodscn101

Overview of Standby RAC environment:-

IP Address Details of Primary:

IP Address Details of Standby:

#Public IP
192.168.1.221  prodrac201.oracledbwr.com  prodrac201
192.168.1.222  prodrac202.oracledbwr.com  prodrac202

#Private IP
192.168.2.221  prodprv201.oracledbwr.com  prodprv201
192.168.2.222  prodprv202.oracledbwr.com  prodprv202

#Virtual IP
192.168.1.224  prodvip201.oracledbwr.com  prodvip201
192.168.1.225  prodvip202.oracledbwr.com  prodvip202

#Scan IP
192.168.1.228  prodscn201.oracledbwr.com  prodscn201
192.168.1.229  prodscn201.oracledbwr.com  prodscn201
192.168.1.230  prodscn201.oracledbwr.com  prodscn201

Prepare Primary Site:-

Prepare Primary RAC database for DR configuration:-

  • Change Database Unique Name & Enable Force Logging.
  •  Enable Archivelog Mode.
  • Modify init Parameters.
  • Create the SLRs (Standby Redo Logs).
  •  Create pfile for standby database.
  • Update the tnsnames.ora.

Change Database Unique Name & Enable Force Logging:-

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;  <--- 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
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:

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

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

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

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

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

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

Run the below query in both the nodes of primary 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;

In Node 1:In Node 2:Now, verify all the required values have the appropriate values.

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');


NAME                      VALUE
-------------------       -------------------------------
log_archive_dest_1        LOCATION=+DBWR_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

Create pfile and password in primary and copy to standby servers.

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


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

Once the password file is copied to other node, rename the file with it’s respective $ORACLE_SID name. For example, Primary & Standby Node1’s password file name should be like orapwORCL1 and Primary & Standby Node2’s password file name should be like orapwORCL2

Create static listener in Oracle database home temporarily in the Standby Node1 and start it for active duplicate dataguard setup

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.oracledbwr.com)(PORT = 1525))
    )
  )

ADR_BASE_LISTENER1 = /oradb/app/oracle

Start the new listener:

lsnrctl start LISTENER1

Make the below tns entries in tnsnames.ora file of Oracle Database Home of all the Nodes in the cluster.

cat tnsnames.ora <-- All Nodes

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

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

ORCL_PRODVIP102 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip102.oracledbwr.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.oracledbwr.com)(PORT = 1521)))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(UR = A)))

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


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

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

Prepare Standby Site:-

Prepare Standby Node 1 for DR configuration:-

· Create required directories for Standby Servers.
· Modify the pfile for standby.

Create required dump file directories in the Standby Servers:-

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

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 initORCL1.ora file (under $ORACLE_HOME/dbs) which was copied from primary to 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='+DBWR_DATA'
*.db_create_online_log_dest_1='+DBWR_DATA'
*.db_domain=''
*.db_name='ORCL'
*.control_files='+DBWR_DATA','+DBWR_DATA','+DBWR_FRA'
*.db_recovery_file_dest='+DBWR_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=+DBWR_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.oracledbwr.com:1521'

Create the Physical Standby Database:-

· Start the Instance in NOMOUNT using the pfile_for_standby.txt.
· Restore the Standby Database using RMAN DUPLICATE command.
· Start the Managed Recovery Process
· Create the spfile and start the database on both the nodes using spfile.
· Register the New Standby database to the OCR.

Start the Instance in NOMOUNT state:-

Set the environment in Standby Node1 and start the instance in nomount:

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

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

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 14 02:17:48 2018

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

connected to target database: ORCL (DBID=1511765518)
connected to auxiliary database: ORCL (not mounted)

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 14-AUG-18 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 14-AUG-18 02:17:56
Finished backup at 14-AUG-18 02:17:57
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DBWR_DATA/orcl_prodrac2/controlfile/current.272.984104279';
restore clone controlfile to '+DBWR_DATA/orcl_prodrac2/controlfile/current.273.984104279' from
'+DBWR_DATA/orcl_prodrac2/controlfile/current.272.984104279';
restore clone controlfile to '+DBWR_FRA/orcl_prodrac2/controlfile/current.267.984104279' from
'+DBWR_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 =
''+DBWR_DATA/orcl_prodrac2/controlfile/current.272.984104279'', ''+DBWR_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 14-AUG-18 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 14-AUG-18 02:18:06
Starting restore at 14-AUG-18 02:18:06
channel ch4: skipped, AUTOBACKUP already found
channel ch3: copied control file copy
Finished restore at 14-AUG-18 02:18:14
Starting restore at 14-AUG-18 02:18:15
channel ch4: skipped, AUTOBACKUP already found
channel ch3: copied control file copy
Finished restore at 14-AUG-18 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 = ''+DBWR_DATA/orcl_prodrac2/controlfile/current.272.984104279'', ''+DBWR_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 14-AUG-18 02:18:59
channel ch1: starting datafile copy
input datafile file number=00001 name=+DBWR_DATA/orcl_prodrac1/datafile/system.256.983984619
channel ch2: starting datafile copy
input datafile file number=00002 name=+DBWR_DATA/orcl_prodrac1/datafile/sysaux.257.983984621
output file name=+DBWR_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=+DBWR_DATA/orcl_prodrac1/datafile/example.265.983984851
output file name=+DBWR_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=+DBWR_DATA/orcl_prodrac1/datafile/undotbs1.258.983984621
output file name=+DBWR_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=+DBWR_DATA/orcl_prodrac1/datafile/undotbs2.266.983985173
output file name=+DBWR_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=+DBWR_DATA/orcl_prodrac1/datafile/users.259.983984621
output file name=+DBWR_DATA/orcl_prodrac2/datafile/undotbs2.267.984104523 tag=TAG20180814T021900
channel ch1: datafile copy complete, elapsed time: 00:00:08
output file name=+DBWR_DATA/orcl_prodrac2/datafile/users.266.984104523 tag=TAG20180814T021900
channel ch2: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-AUG-18 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=+DBWR_DATA/orcl_prodrac2/datafile/system.274.984104339
datafile 2 switched to datafile copy
input datafile copy RECID=13 STAMP=984104541 file name=+DBWR_DATA/orcl_prodrac2/datafile/sysaux.275.984104341
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=984104541 file name=+DBWR_DATA/orcl_prodrac2/datafile/undotbs1.268.984104487
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=984104541 file name=+DBWR_DATA/orcl_prodrac2/datafile/users.266.984104523
datafile 5 switched to datafile copy
input datafile copy RECID=16 STAMP=984104541 file name=+DBWR_DATA/orcl_prodrac2/datafile/example.269.984104459
datafile 6 switched to datafile copy
input datafile copy RECID=17 STAMP=984104542 file name=+DBWR_DATA/orcl_prodrac2/datafile/undotbs2.267.984104523
Finished Duplicate Db at 14-AUG-18 02:22:33
released channel: ch1
released channel: ch2
released channel: ch3
released channel: ch4

Now stop the temporarily created listener and add the cluster parameters in standby and convert to two node RAC dataguard.

Stop the new listener:

lsnrctl stop LISTENER1

Create pfile from spfile and add the cluster parameters:

SQL> create pfile from spfile;
File created.

SQL> shut immediate
*.cluster_database_instances=2
*.cluster_database=true
ORCL1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.224)(PORT=1521))'
ORCL2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.225)(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 new pfile to Node2 of Standby and startup the instance in both the nodes

Environment variable setup on node1:

$ 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

Environment variable setup on node2:

$ 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

Register the database instances with CRS framework using this command:

$ 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 the instances are registered with CRS, shutdown both the instance in standby and start using srvctl command

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

Start the recovery process in Standby:

alter database recover managed standby database disconnect from session;

Create spfile in ASM and point it using the pfile’s in $ORACLE_HOME/dbs location of both the nodes in Standby.

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

File created.

Use the following command to check the status of all instances converted RAC database:

SQL> set lines 180
SQL> col INST_NAME for a80
SQL> select * from v$active_instances;
INST_NUMBER   INST_NAME
------------  -------------------------------------------
          1   prodrac201.oracledbwr.com:ORCL1
          2   prodrac202.oracledbwr.com:ORCL2

In Node1 & Node2 of Standby:-

$ cat initORCL1.ora
spfile='+DBWR_DATA/ORCL_PRODRAC2/PARAMETERFILE/spfileORCL.ora'
$ cat initORCL2.ora
spfile='+DBWR_DATA/ORCL_PRODRAC2/PARAMETERFILE/spfileORCL.ora'

Now shutdown both the instances and startup anyone of the instance using the spfile from ASM and start the media recovery:-

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;

We can verify the sync between Primary(Node1 & Node2) and Standby(Node1) by switch logfile in primary and tail alert log from OS or query the alert log from sqlplus. Here, we go with the query.

In Primary – Node1:

SQL> alter system switch logfile;

System altered.

In Standby – Node1:

SQL> @taln.sql

 ID Date Message
-------- -------------------- ------------------------------------------------------------------------------------------------------------------------
6161 14.08.2018 07:13:21 RFS[14]: Assigned to RFS process 56469
6162 14.08.2018 07:13:21 RFS[14]: Selected log 10 for thread 2 sequence 34 dbid 1511765518 branch 983984785
6163 14.08.2018 07:15:21 RFS[14]: Selected log 11 for thread 2 sequence 35 dbid 1511765518 branch 983984785
6164 14.08.2018 07:15:21 Archived Log entry 33 added for thread 2 sequence 34 ID 0x5a1b820b dest 1:
6165 14.08.2018 07:40:19 RFS[13]: Selected log 8 for thread 1 sequence 40 dbid 1511765518 branch 983984785
6166 14.08.2018 07:40:19 Archived Log entry 34 added for thread 1 sequence 39 ID 0x5a1b820b dest 1:
6167 14.08.2018 07:40:40 Archived Log entry 35 added for thread 2 sequence 35 ID 0x5a1b820b dest 1:
6168 14.08.2018 07:40:40 RFS[14]: Selected log 10 for thread 2 sequence 36 dbid 1511765518 branch 983984785
6169 14.08.2018 07:41:08 RFS[13]: Selected log 7 for thread 1 sequence 41 dbid 1511765518 branch 983984785
6170 14.08.2018 07:41:08 Archived Log entry 36 added for thread 1 sequence 40 ID 0x5a1b820b dest 1:

In Primary – Node2:

SQL> alter system switch logfile;

System altered.

In Standby – Node1:

SQL> @taln.sql

 ID Date Message
-------- -------------------- ------------------------------------------------------------------------------------------------------------------------
6163 14.08.2018 07:15:21 RFS[14]: Selected log 11 for thread 2 sequence 35 dbid 1511765518 branch 983984785
6164 14.08.2018 07:15:21 Archived Log entry 33 added for thread 2 sequence 34 ID 0x5a1b820b dest 1:
6165 14.08.2018 07:40:19 RFS[13]: Selected log 8 for thread 1 sequence 40 dbid 1511765518 branch 983984785
6166 14.08.2018 07:40:19 Archived Log entry 34 added for thread 1 sequence 39 ID 0x5a1b820b dest 1:
6167 14.08.2018 07:40:40 Archived Log entry 35 added for thread 2 sequence 35 ID 0x5a1b820b dest 1:
6168 14.08.2018 07:40:40 RFS[14]: Selected log 10 for thread 2 sequence 36 dbid 1511765518 branch 983984785
6169 14.08.2018 07:41:08 RFS[13]: Selected log 7 for thread 1 sequence 41 dbid 1511765518 branch 983984785
6170 14.08.2018 07:41:08 Archived Log entry 36 added for thread 1 sequence 40 ID 0x5a1b820b dest 1:
6171 14.08.2018 07:46:20 Archived Log entry 37 added for thread 2 sequence 36 ID 0x5a1b820b dest 1:
6172 14.08.2018 07:46:21 RFS[14]: Selected log 10 for thread 2 sequence 37 dbid 1511765518 branch 983984785

DONE!!!

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Leave a Reply

Your email address will not be published. Required fields are marked *