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.
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 Node1:
In Node2:
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> @tln.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> @tln.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
COMPLETED!!!