Prerequisite
Primary side
2 node RAC primary TROY(TROY1,TROY2) already setup and running on oel6-112-rac1, oel6-112-rac1
for setting up 2 node RAC on primary follow this post
https://dbatracker.com/2020/05/15/11-2-0-3-rac-install-on-oel-6/
Standby side
Grid and DB software installed on oel6-112-dr-rac1, oel6-112-dr-rac2
/etc/hosts on all 4 nodes (2 primary and 2 standby)
127.0.0.1 localhost.localdomain localhost
# Public
192.168.43.101 ol6-112-rac1.localdomain ol6-112-rac1
192.168.43.102 ol6-112-rac2.localdomain ol6-112-rac2
# Private
192.168.190.201 ol6-112-rac1-priv.localdomain ol6-112-rac1-priv
192.168.190.202 ol6-112-rac2-priv.localdomain ol6-112-rac2-priv
# Virtual
192.168.43.103 ol6-112-rac1-vip.localdomain ol6-112-rac1-vip
192.168.43.104 ol6-112-rac2-vip.localdomain ol6-112-rac2-vip
# SCAN
192.168.43.105 ol6-112-scan.localdomain ol6-112-scan
192.168.43.106 ol6-112-scan.localdomain ol6-112-scan
192.168.43.107 ol6-112-scan.localdomain ol6-112-scan
#for duplicate testing
192.168.189.102 oel6-node02.localdomain oel6-node02
#DR details
# Public
192.168.43.131 ol6-112-dr-rac1.localdomain ol6-112-dr-rac1
192.168.43.132 ol6-112-dr-rac2.localdomain ol6-112-dr-rac2
# Private
192.168.190.211 ol6-112-dr-rac1-priv.localdomain ol6-112-dr-rac1-priv
192.168.190.212 ol6-112-dr-rac2-priv.localdomain ol6-112-dr-rac2-priv
# Virtual
192.168.43.133 ol6-112-dr-rac1-vip.localdomain ol6-112-dr-rac1-vip
192.168.43.134 ol6-112-dr-rac2-vip.localdomain ol6-112-dr-rac2-vip
# SCAN
192.168.43.135 ol6-112-dr-scan.localdomain ol6-112-dr-scan
192.168.43.136 ol6-112-dr-scan.localdomain ol6-112-dr-scan
192.168.43.137 ol6-112-dr-scan.localdomain ol6-112-dr-scan
tnsnames.ora file in Database oracle home on all 4 nodes (/u01/app/oracle/product/11.2.0.3/db_1/network/admin/tnsnames.ora)
## Prod tns entry with scan
TROY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TROY.localdomain)
)
)
## DR tns entry with scan
TROY_DR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-dr-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TROY_DR.localdomain)
)
)
## To transfer of archives from prod to standby - used in prod log_archive_dest_2
TROY1_DR_VIP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-dr-rac1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TROY_DR.localdomain)
)
)
##To transfer archives from standby to prod - used in standby log_archive_dest_2
TROY1_VIP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-rac1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TROY.localdomain)
)
)
##these are in case PROD node 1 or DR node 1 is down we will use them to transfer archivelogs
##from standby to PROD
TROY2_VIP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-rac2-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TROY.localdomain)
)
)
## from prod to standby
TROY2_DR_VIP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-dr-rac2-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TROY_DR.localdomain)
)
)
## this one is for listener static entry which will be used for active database duplicate it port is 1525
TROY_DUP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-dr-rac1)(PORT = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = TROY_DUP)
)
)
Static listener on standby node 1 for duplicate purpose. It is using port 1525
[oracle@ol6-112-dr-rac1 admin]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/network/admin
[oracle@ol6-112-dr-rac1 admin]$ cat listener.ora
#####this is static entry for duplicate
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TROY_DUP)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
(SID_NAME = TROY1)
)
)
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-dr-rac1.localdomain)(PORT = 1525))
)
)
ADR_BASE_LISTENER1 = /u01/app/oracle
Env files to set env – this is useful as you have to set env lot of times
ol6-112-rac1
[oracle@ol6-112-rac1 ~]$ cat db_env
ORACLE_SID=TROY1; export ORACLE_SID
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
[oracle@ol6-112-rac1 ~]$ cat grid_env
ORACLE_SID=+ASM1; export ORACLE_SID
ORACLE_HOME=$GRID_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
ol6-112-rac2
[oracle@ol6-112-rac2 ~]$ cat db_env
ORACLE_SID=TROY2; export ORACLE_SID
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
[oracle@ol6-112-rac2 ~]$ cat grid_env
ORACLE_SID=+ASM2; export ORACLE_SID
ORACLE_HOME=$GRID_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
ol6-112-dr-rac1
[oracle@ol6-112-dr-rac1 ~]$ cat db_env
ORACLE_SID=TROY1; export ORACLE_SID
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
[oracle@ol6-112-dr-rac1 ~]$ cat grid_env
ORACLE_SID=+ASM1; export ORACLE_SID
ORACLE_HOME=$GRID_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
ol6-112-dr-rac2
[oracle@ol6-112-dr-rac2 ~]$ cat db_env
cORACLE_SID=TROY2; export ORACLE_SID
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
[oracle@ol6-112-dr-rac2 ~]$ cat grid_env
ORACLE_SID=+ASM2; export ORACLE_SID
ORACLE_HOME=$GRID_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
Bash profile on ol6-112-rac1 and ol6-112-rac2
[oracle@ol6-112-rac1 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# Oracle Settings
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=ol6-112-rac1.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=TROY; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
GRID_HOME=/u01/app/11.2.0.3/grid; export GRID_HOME
DB_HOME=$ORACLE_BASE/product/11.2.0.3/db_1; export DB_HOME
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
ORACLE_SID=TROY1; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
alias grid_env='. /home/oracle/grid_env'
alias db_env='. /home/oracle/db_env'
[oracle@ol6-112-rac2 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# Oracle Settings
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=ol6-112-rac2.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=TROY; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
GRID_HOME=/u01/app/11.2.0.3/grid; export GRID_HOME
DB_HOME=$ORACLE_BASE/product/11.2.0.3/db_1; export DB_HOME
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
ORACLE_SID=TROY2; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
alias grid_env='. /home/oracle/grid_env'
alias db_env='. /home/oracle/db_env'
Bash profile on ol6-112-dr-rac1 and ol6-112-dr-rac2
[oracle@ol6-112-dr-rac1 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# Oracle Settings
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=ol6-112-dr-rac1.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=TROY_DR; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
GRID_HOME=/u01/app/11.2.0.3/grid1; export GRID_HOME
DB_HOME=$ORACLE_BASE/product/11.2.0.3/db_1; export DB_HOME
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
ORACLE_SID=TROY1; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
alias grid_env='. /home/oracle/grid_env'
alias db_env='. /home/oracle/db_env'
[oracle@ol6-112-dr-rac2 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# Oracle Settings
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=ol6-112-dr-rac2.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=TROY_DR; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
GRID_HOME=/u01/app/11.2.0.3/grid1; export GRID_HOME
DB_HOME=$ORACLE_BASE/product/11.2.0.3/db_1; export DB_HOME
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
ORACLE_SID=TROY2; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
alias grid_env='. /home/oracle/grid_env'
alias db_env='. /home/oracle/db_env'
Prepare Primary RAC database for DR configuration
Enable force logging and ensure database is in archivelog mode
TROY1@ol6-112-rac1 > SELECT force_logging FROM v$database;
FOR
---
NO
TROY1@ol6-112-rac1 > ALTER DATABASE FORCE LOGGING;
Database altered.
TROY1@ol6-112-rac1 > SELECT force_logging FROM v$database;
FOR
---
YES
TROY1@ol6-112-rac1 > archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 24
Next log sequence to archive 25
Current log sequence 25
set unique name , log_archive_config, dest_2, fal client, fal server and few other parameters
TROY1@ol6-112-rac1 > SHOW PARAMETER DB_UNIQUE_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string TROY
TROY1@ol6-112-rac1 > ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TROY,TROY_DR)' sid='*';
System altered.
TROY1@ol6-112-rac1 > ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=TROY1_DR_VIP NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TROY_DR' sid='*';
System altered.
TROY1@ol6-112-rac1 > ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE sid='*';
System altered.
TROY1@ol6-112-rac1 > sho parameter LOG_ARCHIVE_FORMAT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
TROY1@ol6-112-rac1 > ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='TROY_%t_%s_%r.arc' SCOPE=SPFILE;
System altered.
TROY1@ol6-112-rac1 > ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 sid='*';
System altered.
TROY1@ol6-112-rac1 > ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.
TROY1@ol6-112-rac1 > ALTER SYSTEM SET FAL_SERVER='TROY_DR' sid='*';
System altered.
TROY1@ol6-112-rac1 > ALTER SYSTEM SET FAL_CLIENT='TROY' sid='*';
System altered.
TROY1@ol6-112-rac1 > ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH SID='*';
System altered.
Add the standby redolog files in primary:
TROY1@ol6-112-rac1 > select GROUP#,THREAD#, bytes/1024/1024 MB from gv$log order by GROUP#,THREAD#;
GROUP# THREAD# MB
------ ---------- ----------
1 1 50
1 1 50
2 1 50
2 1 50
3 2 50
3 2 50
4 2 50
4 2 50
8 rows selected.
TROY1@ol6-112-rac1 > alter database add standby logfile thread 1 group 5 '+DATA' size 50M;
Database altered.
TROY1@ol6-112-rac1 > alter database add standby logfile thread 1 group 6 '+DATA' size 50M;
Database altered.
TROY1@ol6-112-rac1 > alter database add standby logfile thread 1 group 7 '+DATA' size 50M;
Database altered.
TROY1@ol6-112-rac1 > alter database add standby logfile thread 2 group 8 '+DATA' size 50M;
Database altered.
TROY1@ol6-112-rac1 > alter database add standby logfile thread 2 group 9 '+DATA' size 50M;
Database altered.
TROY1@ol6-112-rac1 > alter database add standby logfile thread 2 group 10 '+DATA' size 50M;
Database altered.
verify
TROY1@ol6-112-rac1 > select group#,thread#,sequence#,archived,status from gV$STANDBY_LOG order by group#,thread#;
GROUP# THREAD# SEQUENCE# ARCHIVE STATUS
------ ---------- ---------- ------- ----------
5 1 0 YES UNASSIGNED
5 1 0 YES UNASSIGNED
6 1 0 YES UNASSIGNED
6 1 0 YES UNASSIGNED
7 1 0 YES UNASSIGNED
7 1 0 YES UNASSIGNED
8 2 0 YES UNASSIGNED
8 2 0 YES UNASSIGNED
9 2 0 YES UNASSIGNED
9 2 0 YES UNASSIGNED
10 2 0 YES UNASSIGNED
10 2 0 YES UNASSIGNED
12 rows selected.
Now, verify all the required values have the appropriate values.
TROY1@ol6-112-rac1 > 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_archiveTROY1@ol6-112-rac1 > TROY1@ol6-112-rac1 > TROY1@ol6-112-rac1 > 2 3 4 _dest_state_2', 'remote_login_passwordfile',
'log_archive_format','log_archive_max_processes','fal_server','fal_client','standby_file_management','db_recovery_file_dest','db_recovery_file_dest_size'); 5
NAME VALUE
-------------------------------------------------- --------------------------------------------------------------------------------------------------------------
log_archive_dest_1
log_archive_dest_2 SERVICE=TROY1_DR_VIP NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TROY_DR
log_archive_dest_state_1 enable
log_archive_dest_state_2 ENABLE
fal_client TROY
fal_server TROY_DR
log_archive_config DG_CONFIG=(TROY,TROY_DR)
log_archive_format TROY_%t_%s_%r.arc
log_archive_max_processes 30
db_recovery_file_dest +DATA
db_recovery_file_dest_size 5368709120
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name TROY
db_unique_name TROY
15 rows selected.
Create pfile and password in primary and copy to standby servers. Prod and standby node1 file will have same name and similarly prod and standby node2 files will have same name
TROY1@ol6-112-rac1 > create pfile='/tmp/initTROY1.ora' from spfile;
File created.
[oracle@ol6-112-rac1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=10 force=y
[oracle@ol6-112-rac1 dbs]$ scp orapwTROY1 ol6-112-dr-rac1:/u01/app/oracle/product/11.2.0.3/db_1/dbs
[oracle@ol6-112-rac1 dbs]$ scp orapwTROY1 ol6-112-rac2:/u01/app/oracle/product/11.2.0.3/db_1/dbs/orapwTROY2
[oracle@ol6-112-rac1 dbs]$ scp orapwTROY1 ol6-112-dr-rac2:/u01/app/oracle/product/11.2.0.3/db_1/dbs/orapwTROY2
Copy the pfile created above to standby node1
[oracle@ol6-112-rac1 dbs]$ scp /tmp/initTROY1.ora ol6-112-dr-rac1:/u01/app/oracle/product/11.2.0.3/db_1/dbs/
Create static listener in Oracle database home temporarily in the Standby Node1 and start it for active duplicate dataguard setup
[oracle@ol6-112-dr-rac1 admin]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/network/admin
[oracle@ol6-112-dr-rac1 admin]$ cat listener.ora
#####this is static entry for duplicate
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TROY_DUP)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
(SID_NAME = TROY1)
)
)
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-dr-rac1.localdomain)(PORT = 1525))
)
)
ADR_BASE_LISTENER1 = /u01/app/oracle
[oracle@ol6-112-dr-rac1 admin]$
start the listener1. TROY_DUP is not registered but in unknown state which is fine as database is not yet started. this will be used for auxiliary connection while duplicating the database
[oracle@ol6-112-dr-rac1 admin]$ lsnrctl start LISTENER1
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 27-MAY-2020 19:52:18
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0.3/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ol6-112-dr-rac1/listener1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol6-112-dr-rac1.localdomain)(PORT=1525)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol6-112-dr-rac1.localdomain)(PORT=1525)))
STATUS of the LISTENER
------------------------
Alias LISTENER1
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 27-MAY-2020 19:52:19
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ol6-112-dr-rac1/listener1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol6-112-dr-rac1.localdomain)(PORT=1525)))
Services Summary...
Service "TROY_DUP" has 1 instance(s).===>
Instance "TROY1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Prepare Standby Site
make these directories on standby both nodes
[oracle@ol6-112-dr-rac1 TROY]$ pwd
/u01/app/oracle/admin/TROY
[oracle@ol6-112-dr-rac1 TROY]$ ls
adump
[oracle@ol6-112-dr-rac2 TROY]$ pwd
/u01/app/oracle/admin/TROY
[oracle@ol6-112-dr-rac2 TROY]$ ls
adump
[oracle@ol6-112-dr-rac1 diag]$ mkdir -p /u01/app/oracle/diag/rdbms/troy/TROY1
[oracle@ol6-112-dr-rac1 diag]$ cd /u01/app/oracle/diag/rdbms/troy/TROY1
[oracle@ol6-112-dr-rac1 TROY1]$ ls
[oracle@ol6-112-dr-rac1 TROY1]$ mkdir trace cdump
[oracle@ol6-112-dr-rac1 TROY1]$
[oracle@ol6-112-dr-rac2 diag]$ mkdir -p /u01/app/oracle/diag/rdbms/troy/TROY2
[oracle@ol6-112-dr-rac2 diag]$ cd /u01/app/oracle/diag/rdbms/troy/TROY2/
[oracle@ol6-112-dr-rac2 TROY2]$ mkdir trace cdump
[oracle@ol6-112-dr-rac2 TROY2]$ ls
cdump trace
edit and make below changes in standby pfile which was copied from primary node 1 in above step
[oracle@ol6-112-dr-rac1 dbs]$ cat initTROY1.ora
*.audit_file_dest='/u01/app/oracle/admin/TROY/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='localdomain'
*.db_name='TROY'
*.db_recovery_file_dest_size=5368709120
*.db_recovery_file_dest='+DATA'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TROYXDB)'
*.fal_client='TROY_DR'
*.fal_server='TROY'
*.log_archive_config='DG_CONFIG=(TROY,TROY_DR)'
*.log_archive_dest_2='SERVICE=TROY1_VIP NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TROY'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='TROY_%t_%s_%r.arc'
*.log_archive_max_processes=30
*.open_cursors=300
*.processes=150
*.remote_listener='ol6-112-dr-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.sga_max_size=629145600
*.sga_target=629145600
*.db_unique_name='TROY_DR'
Create the Physical Standby Database
On standby node 1 start standby database in nomount state using the pfile created above
[oracle@ol6-112-dr-rac1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 27 20:28:24 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0.3/db_1/dbs/initTROY1.ora
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2227944 bytes
Variable Size 738197784 bytes
Database Buffers 503316480 bytes
Redo Buffers 8921088 bytes
SQL> exit
Run rman duplicate
[oracle@ol6-112-dr-rac1 admin]$ rman target sys/oracle@TROY1_VIP auxiliary sys/oracle@TROY_DUP
Recovery Manager: Release 11.2.0.3.0 - Production on Wed May 27 21:42:39 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TROY (DBID=4201044407)
connected to auxiliary database: TROY (not mounted)
[oracle@ol6-112-dr-rac1 ~]$ rman target sys/oracle@TROY1_VIP auxiliary sys/oracle@TROY_DUP
Recovery Manager: Release 11.2.0.3.0 - Production on Thu May 28 10:38:59 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TROY (DBID=4201044407)
connected to auxiliary database: TROY (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;
Starting Duplicate Db at 28-MAY-20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=32 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0.3/db_1/dbs/orapwTROY1' auxiliary format
'/u01/app/oracle/product/11.2.0.3/db_1/dbs/orapwTROY1' ;
}
executing Memory Script
Starting backup at 28-MAY-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=84 instance=TROY1 device type=DISK
Finished backup at 28-MAY-20
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATA/troy/controlfile/current.256.1041590391';
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''+DATA/troy/controlfile/current.256.1041590391'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Starting backup at 28-MAY-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_TROY1.f tag=TAG20200528T103954 RECID=6 STAMP=1041590401
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 28-MAY-20
sql statement: create spfile from memory
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 188745048 bytes
Database Buffers 432013312 bytes
Redo Buffers 3338240 bytes
sql statement: alter system set control_files = ''+DATA/troy/controlfile/current.256.1041590391'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 188745048 bytes
Database Buffers 432013312 bytes
Redo Buffers 3338240 bytes
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 +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 28-MAY-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/troy/datafile/system.256.1040416307
output file name=+DATA/troy/datafile/system.262.1041590469 tag=TAG20200528T104106
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/troy/datafile/sysaux.257.1040416309
output file name=+DATA/troy/datafile/sysaux.261.1041590483 tag=TAG20200528T104106
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/troy/datafile/example.264.1040416383
output file name=+DATA/troy/datafile/example.260.1041590499 tag=TAG20200528T104106
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/troy/datafile/undotbs1.258.1040416309
output file name=+DATA/troy/datafile/undotbs1.259.1041590513 tag=TAG20200528T104106
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/troy/datafile/undotbs2.265.1040416497
output file name=+DATA/troy/datafile/undotbs2.258.1041590523 tag=TAG20200528T104106
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/troy/datafile/users.259.1040416309
output file name=+DATA/troy/datafile/users.257.1041590525 tag=TAG20200528T104106
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 28-MAY-20
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=6 STAMP=1041590535 file name=+DATA/troy/datafile/system.262.1041590469
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=1041590535 file name=+DATA/troy/datafile/sysaux.261.1041590483
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=1041590535 file name=+DATA/troy/datafile/undotbs1.259.1041590513
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=1041590535 file name=+DATA/troy/datafile/users.257.1041590525
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=1041590535 file name=+DATA/troy/datafile/example.260.1041590499
datafile 6 switched to datafile copy
input datafile copy RECID=11 STAMP=1041590535 file name=+DATA/troy/datafile/undotbs2.258.1041590523
Finished Duplicate Db at 28-MAY-20
RMAN> exit
Recovery Manager complete.
Once duplicate completed stop the listener1 . It is no more required
[oracle@ol6-112-dr-rac1 ~]$ cd
[oracle@ol6-112-dr-rac1 ~]$ db_env
[oracle@ol6-112-dr-rac1 ~]$ lsnrctl stop LISTENER1
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 28-MAY-2020 10:43:03
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol6-112-dr-rac1.localdomain)(PORT=1525)))
The command completed successfully
[oracle@ol6-112-dr-rac1 ~]$
Verify the database but it is not yet ready
[oracle@ol6-112-dr-rac1 ~]$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Thu May 28 10:43:39 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
TROY1@ol6-112-dr-rac1 > select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TROY MOUNTED PHYSICAL STANDBY
create pfile from spfile and add cluster parameters – during duplicate rman creates a spfile automatically so make these changes
[oracle@ol6-112-dr-rac1 ~]$ cd $ORACLE_HOME
[oracle@ol6-112-dr-rac1 db_1]$ cd dbs
[oracle@ol6-112-dr-rac1 dbs]$ ls -ltr
total 18108
-rwxrwxr-x. 1 oracle oinstall 2851 May 15 2009 init.ora
-rwxrwxr-x. 1 oracle oinstall 18497536 May 15 20:00 snapcf_TROY1.f
-rwxrwxr-x. 1 oracle oinstall 163 May 27 14:26 initDBUA2643761.ora
-rwxrwxr-x. 1 oracle oinstall 1536 May 27 14:26 orapwDBUA2643761
-rwxrwxr-x. 1 oracle oinstall 1544 May 27 14:27 hc_DBUA2643761.dat
-rw-r--r--. 1 oracle oinstall 915 May 28 10:36 initTROY1.ora
-rw-r-----. 1 oracle oinstall 2560 May 28 10:39 orapwTROY1
-rwxrwxr-x. 1 oracle oinstall 1544 May 28 10:40 hc_TROY1.dat
-rw-r-----. 1 oracle oinstall 14848 May 28 10:40 spfileTROY1.ora
==>
[oracle@ol6-112-dr-rac1 dbs]$
[oracle@ol6-112-dr-rac1 dbs]$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Thu May 28 10:45:00 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
TROY1@ol6-112-dr-rac1 > sho parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
.3/db_1/dbs/spfileTROY1.ora
TROY1@ol6-112-dr-rac1 > create pfile from spfile;
File created.
[oracle@ol6-112-dr-rac1 dbs]$ ls -ltr
total 18112
-rwxrwxr-x. 1 oracle oinstall 2851 May 15 2009 init.ora
-rwxrwxr-x. 1 oracle oinstall 18497536 May 15 20:00 snapcf_TROY1.f
-rwxrwxr-x. 1 oracle oinstall 163 May 27 14:26 initDBUA2643761.ora
-rwxrwxr-x. 1 oracle oinstall 1536 May 27 14:26 orapwDBUA2643761
-rwxrwxr-x. 1 oracle oinstall 1544 May 27 14:27 hc_DBUA2643761.dat
-rw-r-----. 1 oracle oinstall 2560 May 28 10:39 orapwTROY1
-rwxrwxr-x. 1 oracle oinstall 1544 May 28 10:40 hc_TROY1.dat
-rw-r-----. 1 oracle oinstall 14848 May 28 10:40 spfileTROY1.ora
-rw-r--r--. 1 oracle oinstall 6550 May 28 10:45 initTROY1.ora ==>
Shutdown the standby
TROY1@ol6-112-dr-rac1 > shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
TROY1@ol6-112-dr-rac1 >
add rac parameter in pfile created above
add these parameters at the end of pfile just created above
*.cluster_database_instances=2
*.cluster_database=true
TROY1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.43.133)(PORT=1521))'
TROY2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.43.134)(PORT=1521))'
TROY1.instance_number=1
TROY2.instance_number=2
TROY1.instance_name='TROY1'
TROY2.instance_name='TROY2'
TROY1.thread=1
TROY2.thread=2
TROY1.undo_tablespace='UNDOTBS1'
TROY2.undo_tablespace='UNDOTBS2'
copy pfile to second dr node
[oracle@ol6-112-dr-rac1 dbs]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/dbs
[oracle@ol6-112-dr-rac1 dbs]$ scp initTROY1.ora ol6-112-dr-rac2:/u01/app/oracle/product/11.2.0.3/db_1/dbs/initTROY2.ora
initTROY1.ora 100% 6965 6.8KB/s 00:00
[oracle@ol6-112-dr-rac1 dbs]$
start instance manually in both nodes using these pfiles
[oracle@ol6-112-dr-rac1 dbs]$ cd
[oracle@ol6-112-dr-rac1 ~]$ db_env
[oracle@ol6-112-dr-rac1 ~]$ env|grep ORA
ORACLE_UNQNAME=TROY_DR
ORACLE_SID=TROY1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=ol6-112-dr-rac1.localdomain
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
[oracle@ol6-112-dr-rac1 ~]$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Thu May 28 10:53:01 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
TROY1@ol6-112-dr-rac1 > startup mount pfile='/u01/app/oracle/product/11.2.0.3/db_1/dbs/initTROY1.ora'
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 188745048 bytes
Database Buffers 432013312 bytes
Redo Buffers 3338240 bytes
Database mounted.
[oracle@ol6-112-dr-rac2 ~]$ cd
[oracle@ol6-112-dr-rac2 ~]$ db_env
[oracle@ol6-112-dr-rac2 ~]$ env|grep ORA
ORACLE_UNQNAME=TROY_DR
ORACLE_SID=TROY2
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=ol6-112-dr-rac2.localdomain
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
[oracle@ol6-112-dr-rac2 ~]$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Thu May 28 10:56:30 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup mount pfile='/u01/app/oracle/product/11.2.0.3/db_1/dbs/initTROY2.ora';
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 251659608 bytes
Database Buffers 369098752 bytes
Redo Buffers 3338240 bytes
Database mounted.
SQL>
Register the database instances with CRS
[oracle@ol6-112-dr-rac1 dbs]$ srvctl add database -d TROY_DR -o /u01/app/oracle/product/11.2.0.3/db_1
[oracle@ol6-112-dr-rac1 dbs]$ srvctl add instance -d TROY_DR -i TROY1 -n ol6-112-dr-rac1
[oracle@ol6-112-dr-rac1 dbs]$ srvctl add instance -d TROY_DR -i TROY2 -n ol6-112-dr-rac2
[oracle@ol6-112-dr-rac1 dbs]$
Now the instances are registered with CRS, shutdown both the instance in standby and start using srvctl command
[oracle@ol6-112-dr-rac1 dbs]$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Thu May 28 11:08:44 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
TROY1@ol6-112-dr-rac1 > shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
before that create spfile
TROY1@ol6-112-dr-rac1 > create spfile='/u01/app/oracle/product/11.2.0.3/db_1/dbs/spfileTROY1.ora' from pfile='/u01/app/oracle/product/11.2.0.3/db_1/dbs/initTROY1.ora';
File created.
similarly on node2
[oracle@ol6-112-dr-rac2 ~]$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Thu May 28 10:56:30 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> create spfile='/u01/app/oracle/product/11.2.0.3/db_1/dbs/spfileTROY2.ora' from pfile='/u01/app/oracle/product/11.2.0.3/db_1/dbs/initTROY2.ora';
File created.
Start the standby database using srvctl
[oracle@ol6-112-dr-rac1 dbs]$ srvctl start database -d TROY_DR -o mount
[oracle@ol6-112-dr-rac1 dbs]$ srvctl status database -d TROY_DR
Instance TROY1 is running on node ol6-112-dr-rac1
Instance TROY2 is running on node ol6-112-dr-rac2
start the recovery process in Standby node 1
[oracle@ol6-112-dr-rac1 dbs]$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Thu May 28 11:13:47 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
TROY1@ol6-112-dr-rac1 > alter database recover managed standby database disconnect from session;
Database altered.
TROY1@ol6-112-dr-rac1 >
Move spfile in ASM and point it using the pfile’s in $ORACLE_HOME/dbs location of both the nodes in Standby
I am using the pfile which was modified with rac parameters
TROY1@ol6-112-dr-rac1 > create spfile='+DATA/troy/spfiletroy.ora' from pfile='/u01/app/oracle/product/11.2.0.3/db_1/dbs/initTROY1.ora';
File created.
[oracle@ol6-112-dr-rac1 dbs]$ rm -rf spfileTROY1.ora
[oracle@ol6-112-dr-rac1 dbs]$ rm -rf initTROY1.ora
[oracle@ol6-112-dr-rac1 dbs]$ vi initTROY1.ora
[oracle@ol6-112-dr-rac1 dbs]$ cat initTROY1.ora
spfile='+DATA/troy/spfiletroy.ora'
node 2
[oracle@ol6-112-dr-rac2 dbs]$ rm -rf initTROY2.ora spfileTROY2.ora
[oracle@ol6-112-dr-rac2 dbs]$ vi initTROY2.ora
[oracle@ol6-112-dr-rac2 dbs]$ cat initTROY2.ora
spfile='+DATA/troy/spfiletroy.ora'
TROY1@ol6-112-dr-rac1 > sho parameter spfi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/troy/spfiletroy.ora
start stop the database one more time
[oracle@ol6-112-dr-rac1 dbs]$ srvctl stop database -d TROY_DR
[oracle@ol6-112-dr-rac1 dbs]$ srvctl start database -d TROY_DR -o mount
[oracle@ol6-112-dr-rac1 dbs]$ srvctl status database -d TROY_DR
Instance TROY1 is running on node ol6-112-dr-rac1
Instance TROY2 is running on node ol6-112-dr-rac2
start mrp from 1st node
[oracle@ol6-112-dr-rac1 dbs]$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Thu May 28 11:21:07 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
TROY1@ol6-112-dr-rac1 > alter database recover managed standby database disconnect from session;
Database altered.
TROY1@ol6-112-dr-rac1 >
switch logfile on prod and check the sync
TROY1@ol6-112-rac1 > alter system switch logfile;
System altered.
TROY1@ol6-112-dr-rac1 > select THREAD#,max(sequence#) from v$archived_log group by THREAD#;
select THREAD#,max(sequence#) from v$archived_log where applied='YES' group by THREAD#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 60
2 45
TROY1@ol6-112-dr-rac1 >
THREAD# MAX(SEQUENCE#)
---------- --------------
1 59
2 44
DBATracker 