Creating an Oracle 19c Physical Standby RAC Database from a Primary RAC database
in this article we will cover steps by step method to create RAC standby database on 19c from Primary RAC database using ACTIVE DUPLICATION.
1=> Environment.
Currenty Only Primary site database is UP and running. On standby site only Cluster_Home and DB_HOME is running, NO database installed yet.(Marked in Yellow)
We will be creating Standby Database using RMAN DUPLICATE DATABASE FROM ACTIVE DATABASE fetaure, where DB backup not reuired.
-> From Primary
[oracle@racsetn1 ~]$
[oracle@racsetn1 ~]$ ps -eaf |grep pmon
grid 4635 1 0 17:38 ? 00:00:00 asm_pmon_+ASM1
oracle 7618 1 0 17:39 ? 00:00:00 ora_pmon_NTNT1
oracle 25661 5060 0 17:44 pts/0 00:00:00 grep --color=auto pmon
[oracle@racsetn1 ~]$
[oracle@racsetn1 ~]$ /grid_home/app/19c/grid/bin/crsctl check cluster -all
**************************************************************
racsetn1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
racsetn2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
[oracle@racsetn1 ~]$
[oracle@racsetn1 ~]$
[oracle@racsetn1 ~]$ /grid_home/app/19c/grid/bin/crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE racsetn1 STABLE
ONLINE ONLINE racsetn2 STABLE
ora.chad
ONLINE ONLINE racsetn1 STABLE
ONLINE ONLINE racsetn2 STABLE
ora.net1.network
ONLINE ONLINE racsetn1 STABLE
ONLINE ONLINE racsetn2 STABLE
ora.ons
ONLINE ONLINE racsetn1 STABLE
ONLINE ONLINE racsetn2 STABLE
ora.proxy_advm
OFFLINE OFFLINE racsetn1 STABLE
OFFLINE OFFLINE racsetn2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE racsetn1 STABLE
2 ONLINE ONLINE racsetn2 STABLE
3 ONLINE OFFLINE STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE racsetn1 STABLE
2 ONLINE ONLINE racsetn2 STABLE
3 OFFLINE OFFLINE STABLE
ora.FRA.dg(ora.asmgroup)
1 ONLINE ONLINE racsetn1 STABLE
2 ONLINE ONLINE racsetn2 STABLE
3 OFFLINE OFFLINE STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE racsetn2 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE racsetn1 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE racsetn1 STABLE
ora.OCR.dg(ora.asmgroup)
1 ONLINE ONLINE racsetn1 STABLE
2 ONLINE ONLINE racsetn2 STABLE
3 OFFLINE OFFLINE STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE racsetn1 Started,STABLE
2 ONLINE ONLINE racsetn2 Started,STABLE
3 OFFLINE OFFLINE STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE racsetn1 STABLE
2 ONLINE ONLINE racsetn2 STABLE
3 OFFLINE OFFLINE STABLE
ora.cvu
1 ONLINE ONLINE racsetn1 STABLE
ora.ntnt.db
1 ONLINE ONLINE racsetn1 Open,HOME=/ora_home/app/19c/db,STABLE
2 ONLINE ONLINE racsetn2 Open,HOME=/ora_home/app/19c/db,STABLE
ora.qosmserver
1 ONLINE ONLINE racsetn1 STABLE
ora.racsetn1.vip
1 ONLINE ONLINE racsetn1 STABLE
ora.racsetn2.vip
1 ONLINE ONLINE racsetn2 STABLE
ora.scan1.vip
1 ONLINE ONLINE racsetn2 STABLE
ora.scan2.vip
1 ONLINE ONLINE racsetn1 STABLE
ora.scan3.vip
1 ONLINE ONLINE racsetn1 STABLE
--------------------------------------------------------------------------------
[oracle@racsetn1 ~]$
-> From Standby
[oracle@racnoden1 ~]$ ps -eaf |grep pmon
grid 4398 1 0 17:39 ? 00:00:00 asm_pmon_+ASM1
oracle 22437 4242 0 17:44 pts/0 00:00:00 grep --color=auto pmon
[oracle@racnoden1 ~]$
[oracle@racnoden1 ~]$ /grid_home/app/19c/grid/bin/crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE racnoden1 STABLE
ONLINE ONLINE racnoden2 STABLE
ora.chad
ONLINE ONLINE racnoden1 STABLE
ONLINE ONLINE racnoden2 STABLE
ora.net1.network
ONLINE ONLINE racnoden1 STABLE
ONLINE ONLINE racnoden2 STABLE
ora.ons
ONLINE ONLINE racnoden1 STABLE
ONLINE ONLINE racnoden2 STABLE
ora.proxy_advm
OFFLINE OFFLINE racnoden1 STABLE
OFFLINE OFFLINE racnoden2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE racnoden1 STABLE
2 ONLINE ONLINE racnoden2 STABLE
3 ONLINE OFFLINE STABLE
ora.DATA_REP.dg(ora.asmgroup)
1 ONLINE ONLINE racnoden1 STABLE
2 ONLINE ONLINE racnoden2 STABLE
3 OFFLINE OFFLINE STABLE
ora.FRA_RECO.dg(ora.asmgroup)
1 ONLINE ONLINE racnoden1 STABLE
2 ONLINE ONLINE racnoden2 STABLE
3 OFFLINE OFFLINE STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE racnoden2 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE racnoden1 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE racnoden1 STABLE
ora.OCR.dg(ora.asmgroup)
1 ONLINE ONLINE racnoden1 STABLE
2 ONLINE ONLINE racnoden2 STABLE
3 OFFLINE OFFLINE STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE racnoden1 Started,STABLE
2 ONLINE ONLINE racnoden2 Started,STABLE
3 OFFLINE OFFLINE STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE racnoden1 STABLE
2 ONLINE ONLINE racnoden2 STABLE
3 OFFLINE OFFLINE STABLE
ora.cvu
1 ONLINE ONLINE racnoden1 STABLE
ora.qosmserver
1 ONLINE ONLINE racnoden1 STABLE
ora.racnoden1.vip
1 ONLINE ONLINE racnoden1 STABLE
ora.racnoden2.vip
1 ONLINE ONLINE racnoden2 STABLE
ora.scan1.vip
1 ONLINE ONLINE racnoden2 STABLE
ora.scan2.vip
1 ONLINE ONLINE racnoden1 STABLE
ora.scan3.vip
1 ONLINE ONLINE racnoden1 STABLE
--------------------------------------------------------------------------------
[oracle@racnoden1 ~]$
[oracle@racnoden1 ~]$ /grid_home/app/19c/grid/bin/crsctl check cluster -all
**************************************************************
racnoden1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
racnoden2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
[oracle@racnoden1 ~]$
Steps to be performed on PRIMARY DATABASE (racsetn1.ace2oracle.lab / racsetn2.ace2oracle.lab)
We need to set few database parameter on PRIMARY DB as below.
2=> Check DB status
SQL> select name, open_mode,cdb from gv$database;
NAME OPEN_MODE CDB
--------- -------------------- ---
NTNT READ WRITE NO
NTNT READ WRITE NO
SQL>
3=> Check and Set FORCE_LOGGING as below.
SQL> select force_logging from gv$database;
FORCE_LOGGING
---------------------------------------
NO
NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> select force_logging from gv$database;
FORCE_LOGGING
---------------------------------------
YES
YES
4=> Check database SYS user password and copy to Standby database as below.
[oracle@racsetn1 dbs]$ sqlplus sys/Oracle_4U@NTNT_TNS as sysdba
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
[oracle@racsetn1 dbs]$ su - grid
Password:
[grid@racsetn1 ~]$ asmcmd
ASMCMD>
ASMCMD> pwget --dbuniquename NTNT
+DATA/NTNT/PASSWORD/pwdntnt.365.1123698383
ASMCMD>
ASMCMD> pwcopy +DATA/NTNT/PASSWORD/pwdntnt.365.1123698383 /tmp/pwfile_stndby
copying +DATA/NTNT/PASSWORD/pwdntnt.365.1123698383 -> /tmp/pwfile_stndby
ASMCMD> exit
[grid@racsetn1 ~]$ chmod 777 /tmp/pwfile_stndby
[grid@racsetn1 ~]$ ls -ld /tmp/pwfile_stndby
-rwxrwxrwx. 1 grid oinstall 2048 Jan 27 17:50 /tmp/pwfile_stndby
[grid@racsetn1 ~]$
[oracle@racsetn1 dbs]$ scp /tmp/pwfile_stndby oracle@racnoden1:$ORACLE_HOME/dbs/orapwSTDBY1
oracle@racnoden1's password:
pwfile_stndby 100% 2048 2.5MB/s 00:00
[oracle@racsetn1 dbs]$ scp /tmp/pwfile_stndby oracle@racnoden2:$ORACLE_HOME/dbs/orapwSTDBY2
oracle@racnoden2's password:
pwfile_stndby 100% 2048 2.9MB/s 00:00
[oracle@racsetn1 dbs]$
5=> Check if DB in ARCHIVELOG MODE
[oracle@racsetn1 dbs]$ sqlplus "/as sysdba"
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 66
Next log sequence to archive 67
Current log sequence 67
SQL>
6=> Configuring Standby Redo Logfiles on Primary as below.
Determine the number of required standby redo log file groups and create them.
Obtain the number of redo log files and the size of each file.
The number of required standby redo log files is determined by the following formula:
(maximum number of redo logfiles per thread +1) * number of threads
Based on the output of the following SELECT statements, you need (4+1)*2=10 standby redo log file.
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
2 ONLINE +DATA/NTNT/ONLINELOG/group_2.272.1123698529 NO 0
2 ONLINE +FRA/NTNT/ONLINELOG/group_2.789.1123698531 YES 0
1 ONLINE +DATA/NTNT/ONLINELOG/group_1.280.1123698529 NO 0
1 ONLINE +FRA/NTNT/ONLINELOG/group_1.783.1123698531 YES 0
3 ONLINE +DATA/NTNT/ONLINELOG/group_3.279.1123699801 NO 0
3 ONLINE +FRA/NTNT/ONLINELOG/group_3.821.1123699803 YES 0
4 ONLINE +DATA/NTNT/ONLINELOG/group_4.278.1123699813 NO 0
4 ONLINE +FRA/NTNT/ONLINELOG/group_4.851.1123699813 YES 0
8 rows selected.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('+DATA','+FRA') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('+DATA','+FRA') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('+DATA','+FRA') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 8 ('+DATA','+FRA') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 9 ('+DATA','+FRA') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 10 ('+DATA','+FRA') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 11 ('+DATA','+FRA') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 12 ('+DATA','+FRA') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 13 ('+DATA','+FRA') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 14 ('+DATA','+FRA') SIZE 200M;
SQL> set lines 180
col MEMBER for a60
select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;SQL> SQL>
THREAD# GROUP# MEMBER BYTES
---------- ---------- ------------------------------------------------------------ ----------
1 2 +DATA/NTNT/ONLINELOG/group_2.272.1123698529 209715200
1 2 +FRA/NTNT/ONLINELOG/group_2.789.1123698531 209715200
1 1 +DATA/NTNT/ONLINELOG/group_1.280.1123698529 209715200
1 1 +FRA/NTNT/ONLINELOG/group_1.783.1123698531 209715200
2 3 +DATA/NTNT/ONLINELOG/group_3.279.1123699801 209715200
2 3 +FRA/NTNT/ONLINELOG/group_3.821.1123699803 209715200
2 4 +DATA/NTNT/ONLINELOG/group_4.278.1123699813 209715200
2 4 +FRA/NTNT/ONLINELOG/group_4.851.1123699813 209715200
8 rows selected.
SQL> set pages 50
SQL> /
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
2 ONLINE +DATA/NTNT/ONLINELOG/group_2.272.1123698529 NO 0
2 ONLINE +FRA/NTNT/ONLINELOG/group_2.789.1123698531 YES 0
1 ONLINE +DATA/NTNT/ONLINELOG/group_1.280.1123698529 NO 0
1 ONLINE +FRA/NTNT/ONLINELOG/group_1.783.1123698531 YES 0
3 ONLINE +DATA/NTNT/ONLINELOG/group_3.279.1123699801 NO 0
3 ONLINE +FRA/NTNT/ONLINELOG/group_3.821.1123699803 YES 0
4 ONLINE +DATA/NTNT/ONLINELOG/group_4.278.1123699813 NO 0
4 ONLINE +FRA/NTNT/ONLINELOG/group_4.851.1123699813 YES 0
5 STANDBY +DATA/NTNT/ONLINELOG/group_5.259.1127246559 NO 0
5 STANDBY +FRA/NTNT/ONLINELOG/group_5.399.1127246559 NO 0
6 STANDBY +DATA/NTNT/ONLINELOG/group_6.258.1127246577 NO 0
6 STANDBY +FRA/NTNT/ONLINELOG/group_6.401.1127246577 NO 0
7 STANDBY +DATA/NTNT/ONLINELOG/group_7.260.1127246595 NO 0
7 STANDBY +FRA/NTNT/ONLINELOG/group_7.398.1127246595 NO 0
8 STANDBY +DATA/NTNT/ONLINELOG/group_8.261.1127246613 NO 0
8 STANDBY +FRA/NTNT/ONLINELOG/group_8.397.1127246615 NO 0
9 STANDBY +DATA/NTNT/ONLINELOG/group_9.256.1127246631 NO 0
9 STANDBY +FRA/NTNT/ONLINELOG/group_9.396.1127246633 NO 0
10 STANDBY +DATA/NTNT/ONLINELOG/group_10.362.1127246651 NO 0
10 STANDBY +FRA/NTNT/ONLINELOG/group_10.395.1127246651 NO 0
11 STANDBY +DATA/NTNT/ONLINELOG/group_11.361.1127246685 NO 0
11 STANDBY +FRA/NTNT/ONLINELOG/group_11.394.1127246685 NO 0
12 STANDBY +DATA/NTNT/ONLINELOG/group_12.360.1127246719 NO 0
12 STANDBY +FRA/NTNT/ONLINELOG/group_12.393.1127246719 NO 0
13 STANDBY +DATA/NTNT/ONLINELOG/group_13.359.1127246741 NO 0
13 STANDBY +FRA/NTNT/ONLINELOG/group_13.392.1127246741 NO 0
14 STANDBY +DATA/NTNT/ONLINELOG/group_14.331.1127246761 NO 0
14 STANDBY +FRA/NTNT/ONLINELOG/group_14.391.1127246761 NO 0
28 rows selected.
7=> Set TNS ENTRY on both nodes of Primary database as below.
[oracle@racsetn1 dbs]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /ora_home/app/19c/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
NTNT_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racset-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NTNT.ace2oracle.lab)
)
)
STDBY_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STDBY.ace2oracle.lab)(UR=A)
)
)
[oracle@racsetn1 dbs]$ ssh racsetn2
[oracle@racsetn2 ~]$ cat /ora_home/app/19c/db/network/admin/tnsnames.ora
# tnsnames.ora.racsetn2 Network Configuration File: /ora_home/app/19c/db/network/admin/tnsnames.ora.racsetn2
# Generated by Oracle configuration tools.
NTNT_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racset-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NTNT.ace2oracle.lab)
)
)
STDBY_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STDBY.ace2oracle.lab)(UR=A)
)
)
[oracle@racsetn2 ~]$
[oracle@racsetn1 dbs]$ tnsping STDBY_TNS
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STDBY.ace2oracle.lab)(UR=A)))
OK (0 msec)
[oracle@racsetn1 dbs]$ tnsping NTNT_TNS
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racset-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = NTNT.ace2oracle.lab)))
OK (0 msec)
[oracle@racsetn1 dbs]$
8=> Setting parameter on Primary Side.
SQL> show parameter db_uniq
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string NTNT
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/NTNT/PARAMETERFILE/spfile_new.ora
SQL>
SQL> alter system set db_unique_name='NTNT' scope=spfile sid='*';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(NTNT,STDBYDG)' scope=both sid='*';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=NTNT' scope=both sid='*';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STDBY_TNS LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBYDG' scope=both sid='*';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid='*';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*';
System altered.
SQL> ALTER SYSTEM SET fal_client=NTNT_TNS scope=both sid='*';
System altered.
SQL> ALTER SYSTEM SET fal_server=STDBY_TNS scope=both sid='*';
System altered.
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='+DATA_REP','+DATA' SCOPE=SPFILE sid='*';
System altered.
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='+DATA_REP','+DATA','+FRA_RECO','+FRA' SCOPE=SPFILE sid='*';
System altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';
System altered.
Settings for Stadby database as below.
Below settingw will be done on Standby database side.
9=> Create required directories and update pfile as below,
[oracle@racnoden1 dbs]$ mkdir -p /ora_home/app/oracle/admin/STDBY/adump
[oracle@racnoden1 dbs]$ ssh racnoden2
[oracle@racnoden2 ~]$ mkdir -p /ora_home/app/oracle/admin/STDBY/adump
10=> Update PFILE as below for STANDBY database.
[oracle@racnoden1 dbs]$ cat initSTDBY1.ora
*.audit_file_dest='/ora_home/app/oracle/admin/STDBY/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='19.0.0'
*.control_files='+DATA_REP/STDBYDG/CONTROLFILE/cont01.ctl','+FRA_RECO/STDBYDG/CONTROLFILE/cont02.ctl'
*.db_block_size=8192
*.db_domain='ace2oracle.lab'
*.db_file_name_convert='+DATA','+DATA_REP'
*.db_name='NTNT'
*.db_recovery_file_dest='+FRA_RECO'
*.db_recovery_file_dest_size=8256m
*.db_unique_name='STDBYDG'
*.diagnostic_dest='/ora_home/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=NTNTXDB)'
*.fal_server='NTNT_TNS'
*.fal_client='STDBY_TNS'
family:dw_helper.instance_mode='read-only'
STDBY1.instance_number=1
*.local_listener='-oraagent-dummy-'
*.log_archive_config='DG_CONFIG=(NTNT,STDBYDG)'
*.log_archive_dest_1='LOCATION=+FRA_RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STDBYDG'
*.log_archive_dest_2='SERVICE=NTNT_TNS LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=NTNT'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATA/NTNT','+DATA_REP/STDBYDG','+FRA/NTNT','+FRA_RECO/STDBYDG'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=871m
*.processes=320
*.remote_login_passwordfile='exclusive'
*.sga_target=2612m
*.standby_file_management='AUTO'
STDBY1.thread=1
STDBY1.undo_tablespace='UNDOTBS1'
11=> Startup Standby database in NOMOUNT as below.
[oracle@racnoden1 dbs]$ sqlplus "/as sysdba"
Connected to an idle instance.
SQL> startup nomount pfile='initSTDBY1.ora';
ORACLE instance started.
Total System Global Area 2751463160 bytes
Fixed Size 8900344 bytes
Variable Size 570425344 bytes
Database Buffers 2164260864 bytes
Redo Buffers 7876608 bytes
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@racnoden1 dbs]$
12=> Set TNS Entry as below on BOTH NODES of Standby database.
--> NODE1
[oracle@racnoden1 dbs]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /ora_home/app/19c/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
NTNT_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racset-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NTNT.ace2oracle.lab)
)
)
STDBY_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STDBY.ace2oracle.lab)(UR=A)
)
)
[oracle@racnoden1 dbs]$ ssh racnoden2
--> Node2
[oracle@racnoden2 dbs]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /ora_home/app/19c/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
NTNT_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racset-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NTNT.ace2oracle.lab)
)
)
STDBY_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.104)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STDBY.ace2oracle.lab)(UR=A)
)
)
13=> Set Listener Entry ONLY ON NODE-1 of Standby database side as below,
--> NODE1
[grid@racnoden1 ~]$ cat /grid_home/app/19c/grid/network/admin/listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STDBY.ace2oracle.lab)
(ORACLE_HOME = /ora_home/app/19c/db)
(SID_NAME = STDBY1)
)
)
[grid@racnoden1 ~]$
[grid@racnoden1 ~]$
[grid@racnoden1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 27-JAN-2023 21:43:06
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 27-JAN-2023 17:40:50
Uptime 0 days 4 hr. 2 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /grid_home/app/19c/grid/network/admin/listener.ora
Listener Log File /grid_home/app/oracle/diag/tnslsnr/racnoden1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.103)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA_REP" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA_RECO" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_OCR" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "STDBY.ace2oracle.lab" has 1 instance(s).
Instance "STDBY1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[grid@racnoden1 ~]$
--> NODE 2
[grid@racnoden2 ~]$ cat $ORACLE_HOME/network/admin/listener.ora
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
[grid@racnoden2 ~]$
[grid@racnoden2 ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 27-JAN-2023 21:43:40
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 27-JAN-2023 17:41:56
Uptime 0 days 4 hr. 1 min. 44 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /grid_home/app/19c/grid/network/admin/listener.ora
Listener Log File /grid_home/app/oracle/diag/tnslsnr/racnoden2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.102)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.104)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA_REP" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA_RECO" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_OCR" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@racnoden2 ~]$
14=> Test connection from STANDBY Database for Source and Target as below.
[oracle@racnoden1 dbs]$ sqlplus sys/Oracle_4U@NTNT_TNS as sysdba
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@racnoden1 dbs]$ sqlplus sys/Oracle_4U@STDBY_TNS as sysdba
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@racnoden1 dbs]$
15=> Start DUPLICATE command on from STANDBY Database as below.
[oracle@racnoden1 dbs]$ rman target sys/Oracle_4U@NTNT_TNS auxiliary sys/Oracle_4U@STDBY_TNS
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jan 27 21:28:07 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: NTNT (DBID=3125572952)
connected to auxiliary database: NTNT (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 27-JAN-23
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=141 device type=DISK
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/ora_home/app/19c/db/dbs/orapwSTDBY1' ;
}
executing Memory Script
Starting backup at 27-JAN-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 instance=NTNT2 device type=DISK
Finished backup at 27-JAN-23
contents of Memory Script:
{
restore clone from service 'NTNT_TNS' standby controlfile;
}
executing Memory Script
Starting restore at 27-JAN-23
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service NTNT_TNS
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
output file name=+DATA_REP/STDBYDG/CONTROLFILE/cont01.ctl
output file name=+FRA_RECO/STDBYDG/CONTROLFILE/cont02.ctl
Finished restore at 27-JAN-23
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
"+DATA_REP";
switch clone tempfile all;
set newname for datafile 1 to
"+DATA_REP";
set newname for datafile 2 to
"+DATA_REP";
set newname for datafile 3 to
"+DATA_REP";
set newname for datafile 4 to
"+DATA_REP";
set newname for datafile 5 to
"+DATA_REP";
set newname for datafile 7 to
"+DATA_REP";
restore
from nonsparse from service
'NTNT_TNS' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA_REP 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 restore at 27-JAN-23
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service NTNT_TNS
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA_REP
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:28
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service NTNT_TNS
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA_REP
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service NTNT_TNS
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA_REP
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:27
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service NTNT_TNS
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA_REP
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service NTNT_TNS
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA_REP
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service NTNT_TNS
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA_REP
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
Finished restore at 27-JAN-23
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=13 STAMP=1127251891 file name=+DATA_REP/STDBYDG/DATAFILE/system.277.1127251783
datafile 2 switched to datafile copy
input datafile copy RECID=14 STAMP=1127251892 file name=+DATA_REP/STDBYDG/DATAFILE/rman_tspitr.256.1127251811
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1127251893 file name=+DATA_REP/STDBYDG/DATAFILE/sysaux.259.1127251831
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=1127251894 file name=+DATA_REP/STDBYDG/DATAFILE/undotbs1.258.1127251861
datafile 5 switched to datafile copy
input datafile copy RECID=17 STAMP=1127251896 file name=+DATA_REP/STDBYDG/DATAFILE/undotbs2.257.1127251873
datafile 7 switched to datafile copy
input datafile copy RECID=18 STAMP=1127251897 file name=+DATA_REP/STDBYDG/DATAFILE/users.261.1127251879
Finished Duplicate Db at 27-JAN-23
RMAN>
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@racnoden1 dbs]$
16=> Let we verify files on STANDBY database as below.
oracle@racnoden1 dbs]$ sqlplus "/as sysdba"
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA_REP/STDBYDG/DATAFILE/system.277.1127251783
+DATA_REP/STDBYDG/DATAFILE/rman_tspitr.256.1127251811
+DATA_REP/STDBYDG/DATAFILE/sysaux.259.1127251831
+DATA_REP/STDBYDG/DATAFILE/undotbs1.258.1127251861
+DATA_REP/STDBYDG/DATAFILE/undotbs2.257.1127251873
+DATA_REP/STDBYDG/DATAFILE/users.261.1127251879
6 rows selected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+FRA_RECO/STDBYDG/ONLINELOG/group_2.257.1127251939
+FRA_RECO/STDBYDG/ONLINELOG/group_3.654.1127251973
+FRA_RECO/STDBYDG/ONLINELOG/group_1.259.1127251903
+FRA_RECO/STDBYDG/ONLINELOG/group_4.690.1127252011
+FRA_RECO/STDBYDG/ONLINELOG/group_5.258.1127252047
+FRA_RECO/STDBYDG/ONLINELOG/group_6.706.1127252093
+FRA_RECO/STDBYDG/ONLINELOG/group_7.501.1127252147
+FRA_RECO/STDBYDG/ONLINELOG/group_8.260.1127252187
+FRA_RECO/STDBYDG/ONLINELOG/group_9.492.1127252223
+FRA_RECO/STDBYDG/ONLINELOG/group_10.703.1127252265
+FRA_RECO/STDBYDG/ONLINELOG/group_11.256.1127252303
+FRA_RECO/STDBYDG/ONLINELOG/group_12.699.1127252339
+FRA_RECO/STDBYDG/ONLINELOG/group_13.695.1127252387
+FRA_RECO/STDBYDG/ONLINELOG/group_14.692.1127252439
14 rows selected.
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA_REP/STDBYDG/CONTROLFILE/cont01.ctl, +FRA_RECO/STDBYDG/CONTROLFILE/cont02.ctl
SQL>
17=> Copy parameter file to ASM as below.
[oracle@racnoden1 dbs]$ su - grid
Password:
[grid@racnoden1 ~]$ asmcmd
ASMCMD> cd DATA_REP/STDBYDG/
ASMCMD> mkdir PARAMETERFILE
ASMCMD> cd PARAMETERFILE
ASMCMD> pwd
+DATA_REP/STDBYDG/PARAMETERFILE
ASMCMD> exit
[oracle@racnoden1 dbs]$ sqlplus "/as sysdba"
SQL> create spfile='+DATA_REP/STDBYDG/PARAMETERFILE/spfilestdby.ora' from pfile='initSTDBY1.ora';
File created.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
exit
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
-> Take backup fo Current SPFILE and Edit SPFILE entry in init file on Both nodes as below.
[oracle@racnoden1 dbs]$ mv initSTDBY1.ora initSTDBY1.ora_BKP
[oracle@racnoden1 dbs]$ cat initSTDBY1.ora
spfile='+DATA_REP/STDBYDG/PARAMETERFILE/spfilestdby.ora'
[oracle@racnoden1 dbs]$
[oracle@racnoden2 dbs]$ mv initSTDBY1.ora initSTDBY1.ora_BKP
[oracle@racnoden2 dbs]$ cat initSTDBY1.ora
spfile='+DATA_REP/STDBYDG/PARAMETERFILE/spfilestdby.ora'
[oracle@racnoden2 dbs]$
[oracle@racnoden1 dbs]$ sqlplus "/as sysdba"
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2751463160 bytes
Fixed Size 8900344 bytes
Variable Size 570425344 bytes
Database Buffers 2164260864 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
18=> Set parameter for Standby database as below to Enable NODE-2 as RAC
SQL> alter system set undo_tablespace=UNDOTBS2 sid='STDBY2' scope=spfile;
System altered.
SQL> alter system set instance_number=1 sid='STDBY1' scope=spfile;
System altered.
SQL> alter system set instance_number=2 sid='STDBY2' scope=spfile;
System altered.
SQL> alter system set instance_name='STDBY1' sid='STDBY1' scope=spfile;
System altered.
SQL> alter system set instance_name='STDBY2' sid='STDBY2' scope=spfile;
System altered.
SQL> alter system set thread=1 sid='STDBY1' scope=spfile;
System altered.
SQL> alter system set thread=2 sid='STDBY2' scope=spfile;
System altered.
SQL> alter system set cluster_database=TRUE scope=spfile;
System altered.
SQL> alter system set remote_listener='racnode-scan:1521' scope=spfile;
System altered.
SQL>
19=> Stop database and add the same to CRS as below.
[oracle@racnoden1 dbs]$
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
/ora_home/app/19c/db
[oracle@racnoden1 dbs]$ srvctl add database -db STDBYDG -oraclehome /ora_home/app/19c/db -role physical_standby -startoption mount -spfile +DATA_REP/STDBYDG/PARAMETERFILE/spfilestdby.ora
[oracle@racnoden1 dbs]$ srvctl add instance -db STDBYDG -instance STDBY1 -node racnoden1
[oracle@racnoden1 dbs]$ srvctl add instance -db STDBYDG -instance STDBY2 -node racnoden2
[oracle@racnoden1 dbs]$ srvctl start database -d STDBYDG
[oracle@racnoden1 dbs]$ srvctl status database -d STDBYDG
Instance STDBY1 is running on node racnoden1
Instance STDBY2 is running on node racnoden2
[oracle@racnoden1 dbs]$ sqlplus "/as sysdba"
SQL> select name,open_mode,database_role,cdb from gv$database;
NAME OPEN_MODE DATABASE_ROLE CDB
--------- -------------------- ---------------- ---
NTNT MOUNTED PHYSICAL STANDBY NO
NTNT MOUNTED PHYSICAL STANDBY NO
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
-> Check CRS status as below.
[oracle@racnoden1 dbs]$
--------------------------------------------------------------------------------
[oracle@racnoden1 ~]$ /grid_home/app/19c/grid/bin/crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE racnoden1 STABLE
ONLINE ONLINE racnoden2 STABLE
ora.chad
ONLINE ONLINE racnoden1 STABLE
ONLINE ONLINE racnoden2 STABLE
ora.net1.network
ONLINE ONLINE racnoden1 STABLE
ONLINE ONLINE racnoden2 STABLE
ora.ons
ONLINE ONLINE racnoden1 STABLE
ONLINE ONLINE racnoden2 STABLE
ora.proxy_advm
OFFLINE OFFLINE racnoden1 STABLE
OFFLINE OFFLINE racnoden2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE racnoden1 STABLE
2 ONLINE ONLINE racnoden2 STABLE
3 ONLINE OFFLINE STABLE
ora.DATA_REP.dg(ora.asmgroup)
1 ONLINE ONLINE racnoden1 STABLE
2 ONLINE ONLINE racnoden2 STABLE
3 OFFLINE OFFLINE STABLE
ora.FRA_RECO.dg(ora.asmgroup)
1 ONLINE ONLINE racnoden1 STABLE
2 ONLINE ONLINE racnoden2 STABLE
3 OFFLINE OFFLINE STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE racnoden2 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE racnoden1 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE racnoden1 STABLE
ora.OCR.dg(ora.asmgroup)
1 ONLINE ONLINE racnoden1 STABLE
2 ONLINE ONLINE racnoden2 STABLE
3 OFFLINE OFFLINE STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE racnoden1 Started,STABLE
2 ONLINE ONLINE racnoden2 Started,STABLE
3 OFFLINE OFFLINE STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE racnoden1 STABLE
2 ONLINE ONLINE racnoden2 STABLE
3 OFFLINE OFFLINE STABLE
ora.cvu
1 ONLINE ONLINE racnoden1 STABLE
ora.qosmserver
1 ONLINE ONLINE racnoden1 STABLE
ora.racnoden1.vip
1 ONLINE ONLINE racnoden1 STABLE
ora.racnoden2.vip
1 ONLINE ONLINE racnoden2 STABLE
ora.scan1.vip
1 ONLINE ONLINE racnoden2 STABLE
ora.scan2.vip
1 ONLINE ONLINE racnoden1 STABLE
ora.scan3.vip
1 ONLINE ONLINE racnoden1 STABLE
ora.stdbydg.db
1 ONLINE INTERMEDIATE racnoden1 Mounted (Closed),HOME=/ora_home/app/19c/db,STABLE
2 ONLINE INTERMEDIATE racnoden2 Mounted (Closed),HOME=/ora_home/app/19c/db,STABLE
--------------------------------------------------------------------------------
[oracle@racnoden1 ~]$
20 => Adding Passwordfile to ASM for STANDBY database as below.
[oracle@racnoden1 dbs]$ srvctl config database -d STDBYDG
Database unique name: STDBYDG
Database name:
Oracle home: /ora_home/app/19c/db
Oracle user: oracle
Spfile: +DATA_REP/STDBYDG/PARAMETERFILE/spfilestdby.ora
Password file: <<< NO INFO AVAILABLE
Domain: ace2oracle.lab
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA_REP,FRA_RECO
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: oinstall
OSOPER group: oper
Database instances: STDBY1,STDBY2
Configured nodes: racnoden1,racnoden2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
-> copy passwordfile from $ORACLE_HOME/dbs to /tmp/orapwSTDBY location and move to ASM as below
[oracle@racnoden1 dbs]$ su - grid
Password:
[grid@racnoden1 ~]$ asmcmd
ASMCMD> ls
DATA_REP/
FRA_RECO/
OCR/
ASMCMD> pwcopy --dbuniquename STDBYDG /tmp/orapwSTDBY +DATA_REP/STDBYDG/PASSWORD/orapwSTDBYDG -f
copying /tmp/orapwSTDBY -> +DATA_REP/STDBYDG/PASSWORD/orapwSTDBYDG
ASMCMD-9453: failed to register password file as a CRS resource
ASMCMD> exit
-> Add password file to CRS as below
[oracle@racnoden1 dbs]$ srvctl modify database -d STDBYDG -pwfile +DATA_REP/STDBYDG/PASSWORD/orapwSTDBYDG
-> Get passwordfile info as below
[oracle@racnoden1 ~]$ srvctl config database -d STDBYDG
Database unique name: STDBYDG
Database name: NTNT
Oracle home: /ora_home/app/19c/db
Oracle user: oracle
Spfile: +DATA_REP/STDBYDG/PARAMETERFILE/spfilestdby.ora
Password file: +DATA_REP/STDBYDG/PASSWORD/orapwSTDBYDG
Domain: ace2oracle.lab
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA_REP,FRA_RECO
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: oinstall
OSOPER group: oper
Database instances: STDBY1,STDBY2
Configured nodes: racnoden1,racnoden2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
->Move/ Remove orapwSTDBYDG1 and orapwSTDBYDG2 from NODE-1 and NODE-2 of $ORACLE_HOME/dbs location
[oracle@racnoden1 dbs]$ mv orapwSTDBY1 orapwSTDBY1_OLD
[oracle@racnoden2 dbs]$ mv orapwSTDBY2 orapwSTDBY2_OLD
21=> Enable MRP as below.
[oracle@racnoden1 ~]$ sqlplus "/as sysdba"
SQL> alter database recover managed standby database disconnect from session;
Database altered.
22=> Check status of logs at Primary and then on Standby as below
-> Primary.
SQL> select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread#;
System altered.
SQL> SQL> SQL> SQL> SQL> SQL> SQL>
THREAD# MAX(SEQUENCE#)
---------- --------------
1 103
2 105
SQL>
->Standby
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 101
2 98
SQL>
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#))
APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 103 102 1
2 104 104 0
-> Check Stats as below
SQL> set linesize 120
col START_TIME format a20
col ITEM format a20
SELECT TO_CHAR(START_TIME, 'DD-MON-RR HH24:MI:SS') START_TIME, ITEM , SOFAR, UNITS
FROM V$RECOVERY_PROGRESS
WHERE ITEM IN ('Active Apply Rate', 'Average Apply Rate', 'Redo Applied');SQL> SQL> SQL> 2 3
START_TIME ITEM SOFAR UNITS
-------------------- -------------------- ---------- --------------------------------
27-JAN-23 22:42:51 Active Apply Rate 124 KB/sec
27-JAN-23 22:42:51 Average Apply Rate 34 KB/sec
27-JAN-23 22:42:51 Redo Applied 84 Megabytes
SQL>
col NAME for a13
col VALUE for a13
col UNIT for a30
set LINES 132
SELECT NAME, VALUE, UNIT, TIME_COMPUTED
FROM V$DATAGUARD_STATS WHERE NAME IN ('transport lag', 'apply lag');SQL> SQL> SQL> SQL> SQL> 2
NAME VALUE UNIT TIME_COMPUTED
------------- ------------- ------------------------------ ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval 01/27/2023 23:25:36
apply lag +00 00:00:00 day(2) to second(0) interval 01/27/2023 23:25:36
SQL>
