// // Step by Step Data Guard Configuration in 19c
December 3, 2024

A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Step by Step Data Guard Configuration in 19c

27 min read
asrblogger 
1) PREREQUISITES: Requirements before starting the DATA GUARD CONFIGURATION.

NOTE: 

1) In PRIMARY SERVER DG11 we need to install the BINARIES OF ORACLE 19c and then create only 1 PRIMARY DB NAMED ORCL by using ./dbca or manually as required.

2) In STANDBY SERVER DG11S we only need to install the BINARIES OF ORACLE 19c. Do not create any STANDBY DB as we will created in during CONFIDURATION of DATA GUARD which is discussed ahead.
2) COMPLETE STEP BY STEP CONFIGURATION FOR PRIMARY DATABASE.

The high level steps require to configure a Primary DB are given below. These are as follow:

Step 01: Create LISTENER for PRIMARY DB and start the LISTENER by DISABLING FIREWALL.
Step 02: Create TNSNAMES.ORA FILE for PRIMARY DB and check TNSPING for PRIMARY DB and STANDBY DB.
Step 03: ENABLE ARCHIVE LOG for Primary DB.
Step 04: ENABLE FORCE LOGGING for Primary DB.
Step 05: Check DB_NAME and DB_UNIQUE_NAME for the Primary DB.
Step 06: Set LOG_ARCHIVE_CONFIG parameter for Primary DB.
Step 07: Set the LOG_ARCHIVE_DEST parameter for the Primary DB.
Step 08: Set the LOG_ARCHIVE_DEST_STATE parameter to ENABLE for the Primary DB.
Step 09: Set the LOG_ARCHIVE_FORMAT parameter for the Primary DB.
Step 10: Set the LOG_ARCHIVE_MAX_PROCESSES parameter to 30 for the Primary DB.
Step 11: Set the REMOTE_LOGIN_PASSWORDFILE parameter to EXCLUSIVE for the Primary DB. 
Step 12: Set the FAL_SERVER and FAL_CLIENT parameter for the Primary DB.
Step 13: Set the STANDBY_FILE_MANAGEMENT parameter to AUTO for Primary DB.
Step 14: Add STANDBY REDOLOG FILES (SRLs) to the Primary DB while the STANDBY_FILE_MANAGEMENT=MANUAL
Step 15: Send PASSWORD FILE from PRIMARY DB to the STANDBY DB.

STEP 1: Create LISTENER for PRIMARY DB and start the LISTENER by DISABLING FIREWALL.

A) Create a LISTENER for PRIMARY DB

[oracle@dg11s admin]$ . oraenv
ORACLE_SID = [oracle] ? orcl
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0/dbhome_1/
The Oracle base has been set to /u01/app/oracle

[oracle@dg11 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/
[oracle@dg11 admin]$ netmgr
[oracle@dg11 admin]$ ll
total 8
-rw-r--r--. 1 oracle oinstall  471 Sep  4 14:05 listener.ora
drwxr-xr-x. 2 oracle oinstall   64 Apr 17  2019 samples
-rw-r--r--. 1 oracle oinstall 1536 Feb 14  2018 shrept.lst

[oracle@dg11 admin]$ cat listener.ora 

# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.36)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

B) DISABLE the FIREWALL for the PRIMARY DB.

To disable the firewall for the Primary DB we need to first connect with the root user and then by
giving command we have to follow the below steps.

[oracle@dg11 ~]$ su root
Password: oracle

[root@dg11]# service firewalld stop  
          
[root@dg11]# systemctl disable firewalld

[oracle@dg11 ~]$ su oracle

[oracle@dg11 ~]$ service firewalld status

Redirecting to /bin/systemctl status firewalld.service
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)

C) Start the LISTENER created for the PRIMARY DB.

[oracle@dg11 admin]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 07-SEP-2022 09:53:01

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dg11/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.36)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.36)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                07-SEP-2022 09:53:02
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/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dg11/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.36)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

STEP 2: Create TNSNAMES.ORA FILE for PRIMARY DB and check TNSPING for PRIMARY DB and STANDBY DB.

A) Creation of TNSNAMES.ORA FILE in Primary DB

[oracle@dg11 admin]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/
[oracle@dg11 admin]$ netmgr
[oracle@dg11 admin]$ ll
total 12
-rw-r--r--. 1 oracle oinstall  471 Sep  4 14:05 listener.ora
drwxr-xr-x. 2 oracle oinstall   64 Apr 17  2019 samples
-rw-r--r--. 1 oracle oinstall 1536 Feb 14  2018 shrept.lst
-rw-r--r--. 1 oracle oinstall  575 Sep  4 14:15 tnsnames.ora


[oracle@dg11 admin]$ cat tnsnames.ora 

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCLS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.37)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcls)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.36)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

B) Check TNSPING for PRIMARY DB and STANDBY DB

Remember that to check the TNSPING for both, Primary DB and Standby DB, LISTENER and TNSNAMES.ORA files
should be created in both the DBs and LISTENER should be STARTED in both the DBs.

SERVER DG11
===========

[oracle@dg11 ~]$ tnsping orcl

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-SEP-2022 12:04:34
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.36)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

SERVER DG11S
============

[oracle@dg11 ~]$ tnsping orcls

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-SEP-2022 12:04:44
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.37)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcls)))
OK (10 msec)

STEP 3: ENABLE ARCHIVE LOG for Primary DB.

If the Primary DB is in No Archive Log Mode, then we need to Enable it.

SQL> select name,open_mode,database_role from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
ORCL	  READ WRITE	       PRIMARY

SQL> archive log list

Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence	       5

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

Here the Archive log mode for the Primary DB is already Enabled, hence no need to enable the Archive log mode.

If Archive log mode is Disabled, then

Shutdown the DB.
Start the DB in MOUNT mode.
Enable the Archive log mode by giving command "SQL> ALTER DATABASE archivelog;"
Check if Enabled by giving command "SQL> SELECT log_mode FROM v$database;"
Alter the Primary DB in Open mode by giving command "SQL> ALTER DATABASE open;"

STEP 4: ENABLE FORCE LOGGING for Primary DB.

SQL> select FORCE_LOGGING from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> alter database FORCE LOGGING;

Database altered.

SQL> select FORCE_LOGGING from v$database;

FORCE_LOGGING
---------------------------------------
YES

STEP 5: Check DB_NAME and DB_UNIQUE_NAME for the Primary DB.

SQL> show parameter DB_NAME;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_name 			     string	 orcl

SQL> show parameter DB_UNIQUE_NAME;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 orcl

SQL> show parameter NAME

NAME				     TYPE	 VALUE
-------------         -------- -------
db_name 			    string	 orcl
db_unique_name			string	 orcl
global_names			boolean	 FALSE
instance_name			string	 orcl

STEP 6: Set LOG_ARCHIVE_CONFIG parameter for Primary DB.

SQL> show parameter LOG_ARCHIVE_CONFIG 

NAME				     TYPE	 VALUE
------------------    ---------  -------
log_archive_config		 string

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcls)';

System altered.

SQL> show parameter LOG_ARCHIVE_CONFIG 

NAME				     TYPE	 VALUE
-------------------- ----------- --------------------
log_archive_config		 string	 DG_CONFIG=(orcl,orcls)

STEP 7: Set the LOG_ARCHIVE_DEST parameter for the Primary DB.

SQL> show parameter LOG_ARCHIVE_DEST_2

NAME				     TYPE	 VALUE
---------------------  --------- ---------
log_archive_dest_2		string

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=orcls NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcls';

System altered.

SQL> show parameter LOG_ARCHIVE_DEST_2

NAME				     TYPE	 VALUE
------------------- ----------- ------------------------------
log_archive_dest_2		 string	 SERVICE=orcls NOAFFIRM ASYNC
						 VALID_FOR=(ONLINE_LOGFILES,PRI
						 MARY_ROLE) DB_UNIQUE_NAME=orcls

STEP 8: Set the LOG_ARCHIVE_DEST_STATE parameter to ENABLE for the Primary DB.

SQL> show parameter LOG_ARCHIVE_DEST_STATE_2

NAME				         TYPE	 VALUE
------------------------- ----------- --------
log_archive_dest_state_2	 string	 enable

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=enable;

System altered.

SQL> show parameter LOG_ARCHIVE_DEST_STATE_2

NAME				        TYPE	 VALUE
----------------------   ----------- ---------
log_archive_dest_state_2   string	 ENABLE

STEP 9: Set the LOG_ARCHIVE_FORMAT parameter for the Primary DB.

SQL> show parameter LOG_ARCHIVE_FORMAT

NAME				     TYPE	 VALUE
------------------ ----------- --------------------
log_archive_format		 string	 %t_%s_%r.dbf

SQL> alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile;

System altered.

SQL> show parameter LOG_ARCHIVE_FORMAT

NAME				     TYPE	 VALUE
------------------- ----------- ---------------
log_archive_format		 string	 %t_%s_%r.arc

STEP 10: Set the LOG_ARCHIVE_MAX_PROCESSES parameter to 30 for the Primary DB.

SQL> show parameter LOG_ARCHIVE_MAX_PROCESSES

NAME				                TYPE	 VALUE
-------------------------      -----------   -------------
log_archive_max_processes	     integer	 4

SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=30;

System altered.

SQL> show parameter LOG_ARCHIVE_MAX_PROCESSES

NAME				                TYPE	 VALUE
--------------------------      ----------- ---------------
log_archive_max_processes	     integer	 30

STEP 11: Set the REMOTE_LOGIN_PASSWORDFILE parameter to EXCLUSIVE for the Primary DB.

SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

NAME				              TYPE	   VALUE
-----------------------         ----------- --------
remote_login_passwordfile	     string	 INCLUSIVE

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=exclusive scope=spfile;

System altered.

SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

NAME				                TYPE	 VALUE
--------------------------     ----------- -------------
remote_login_passwordfile	     string	    EXCLUSIVE

STEP 12: Set the FAL_SERVER and FAL_CLIENT parameter for the Primary DB.

SQL> show parameter FAL

NAME				     TYPE	 VALUE
--------------------- ----------- ------------------------------
fal_client			     string
fal_server			     string

SQL> alter system set FAL_CLIENT=orcl;

System altered.

SQL> alter system set FAL_SERVER=orcls;

System altered.

SQL> show parameter FAL

NAME				     TYPE	 VALUE
------------------   ----------- ------------------------------
fal_client			     string	 ORCL
fal_server			     string	 ORCLS

STEP 13: Set the STANDBY_FILE_MANAGEMENT parameter to MANUAL for Primary DB.

To add Standby Redo Log files to the Primary DB, it is always necessary to first set the
STANDBY_FILE_MANAGEMENT parameter to MANUAL and then add SRL’s to the DB.

SQL> show parameter STANDBY_FILE_MANAGEMENT

NAME				             TYPE	 VALUE
---------------------------- ----------- -------------
standby_file_management 	     string	 AUTO

SQL> alter system set STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> show parameter STANDBY_FILE_MANAGEMENT
    
NAME				             TYPE	 VALUE
---------------------------   ----------- ------------
standby_file_management 	     string	 MANUAL

STEP 14: Add STANDBY REDOLOG FILES (SRLs) to the Primary DB while the STANDBY_FILE_MANAGEMENT=MANUAL

A) Check the existing REDO LOG FILES in Primary DB.

SQL> select a.GROUP#, a.THREAD#, a.BYTES/1024/1024, a.MEMBERS, a.SEQUENCE#, a.STATUS, b.TYPE, b.MEMBER from 
     v$log a, v$logfile b where a.GROUP# = b.GROUP#;

GROUP# THREAD#    BYTES/1024/1024   MEMBERS    SEQUENCE#  STATUS	   TYPE    MEMBER
------ ---------- ----------------- ---------- ---------- ---------------- ------- ---------------------------------------
3       1	        200 	 1	   12     INACTIVE	   ONLINE  /u01/app/oracle/oradata/ORCL/redo03.log
2	    1		    200 	 1	   14     CURRENT	   ONLINE  /u01/app/oracle/oradata/ORCL/redo02.log
1	    1		    200 	 1	   13     INACTIVE	   ONLINE  /u01/app/oracle/oradata/ORCL/redo01.log

Before adding any SRLs to the Primary DB, it is neccessary to check the existing Redo-log files in the
Primary DB because the GROUP NUMBER of the new SRLs we create should not overlap with the old ones i.e. 1,2,3.
Also the size of the new SRLs should be same as size of existing Redo-log files in the Primary DB i.e. 200MB.

B) Add new SRLs to the Primary DB.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/app/oracle/oradata/ORCL/standby_redo04.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/u01/app/oracle/oradata/ORCL/standby_redo05.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('/u01/app/oracle/oradata/ORCL/standby_redo06.log') SIZE 200M;

Database altered.

C) Verify if SRLs are added to the PRIMARY DB.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
	 4	    1	       0 YES UNASSIGNED
	 5	    1	       0 YES UNASSIGNED
	 6	    1	       0 YES UNASSIGNED

Also in another tab check if physically the SRLs are created.

[oracle@dg11 ~]$ cd /u01/app/oracle/oradata/ORCL/
[oracle@dg11 ORCL]$ ll
total 3194868
-rw-r-----. 1 oracle oinstall  10600448 Sep  7 04:40 control01.ctl
-rw-r-----. 1 oracle oinstall 209715712 Sep  7 02:01 redo01.log
-rw-r-----. 1 oracle oinstall 209715712 Sep  7 04:40 redo02.log
-rw-r-----. 1 oracle oinstall 209715712 Sep  7 02:01 redo03.log
-rw-r-----. 1 oracle oinstall 209715712 Sep  7 04:39 standby_redo04.log
-rw-r-----. 1 oracle oinstall 209715712 Sep  7 04:40 standby_redo05.log
-rw-r-----. 1 oracle oinstall 209715712 Sep  7 04:40 standby_redo06.log
-rw-r-----. 1 oracle oinstall 618668032 Sep  7 04:40 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 954212352 Sep  7 04:36 system01.dbf
-rw-r-----. 1 oracle oinstall 136323072 Sep  4 14:05 temp01.dbf
-rw-r-----. 1 oracle oinstall 298852352 Sep  7 04:36 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Sep  7 02:01 users01.dbf

STEP 15: Set the STANDBY_FILE_MANAGEMENT parameter to AUTO for Primary DB.

Now that we have added Standby Redo Log files to the Primary DB, it is always necessary to set the
STANDBY_FILE_MANAGEMENT parameter to AUTO in Primary DB.

SQL> show parameter STANDBY_FILE_MANAGEMENT

NAME				             TYPE	 VALUE
-------------------------    ----------- -----------
standby_file_management 	     string	 MANUAL

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL> show parameter STANDBY_FILE_MANAGEMENT

NAME				            TYPE	 VALUE
--------------------------   ----------- ---------- 
standby_file_management 	     string	 AUTO

STEP 16: Send PASSWORD FILE from PRIMARY DB to the STANDBY DB.

[oracle@dg11 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/dbs/
[oracle@dg11 dbs]$ ll
total 10376
-rw-rw----. 1 oracle oinstall     1544 Sep  7 21:20 hc_orcl.dat
-rw-r--r--. 1 oracle oinstall     3079 May 14  2015 init.ora
-rw-r--r--. 1 oracle oinstall     1395 Sep  7 22:04 initorcl.ora
-rw-r-----. 1 oracle oinstall       24 Aug 17 14:30 lkORCL
-rw-r-----. 1 oracle oinstall     2048 Aug 17 14:33 orapworcl
-rw-r-----. 1 oracle oinstall 10600448 Sep  7 14:15 snapcf_orcl.f
-rw-r-----. 1 oracle oinstall     3584 Sep  7 21:20 spfileorcl.ora

[oracle@dg11 dbs]$ vi initorcl.ora 

[oracle@dg11 dbs]$ scp orapworcl oracle@192.168.1.37:/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapworcls
oracle@192.168.1.37's password: 
orapworcl                                                                                                           100% 2048     1.3MB/s   00:00    
[oracle@dg11 dbs]$ 

3) COMPLETE STEP BY STEP CONFIGURATION FOR STANDBY DATABASE.

The high level steps require to configure a Standby DB are given below. These are as follow:

Step 01: Make entry in ORATAB FILE before restoring Standby DB.
Step 02: Create LISTENER for STANDBY DB and start the LISTENER by DISABLING FIREWALL.
Step 03: Create TNSNAMES.ORA FILE for STANDBY DB and check TNSPING for PRIMARY DB and STANDBY DB.
Step 04: Set the ENVIRONMENT of Standby DB for 1st time by ECHOING.
Step 05: Create a P-FILE for Standby DB and create required DIRECTORIES.
Step 06: STARTUP the DB in NOMOUNT mode using new P-FILE created for the Standby DB.
Step 07: Connect to AUXILIARY DB using RMAN PROMPT.
Step 08: RUN the SCRIPT in RMAN PROMPT to CLONE the Primary DB to Standby DB.
Step 09: STARTUP the DB in READ_ONLY mode and Validate some parameters.

STEP 1: Make entry in ORATAB FILE before restoring Standby DB.

[oracle@dg11s ~]$ vi /etc/oratab 

[oracle@dg11s ~]$ cat /etc/oratab

# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
orcls:/u01/app/oracle/product/19.0.0/dbhome_1:N

STEP 2: Create LISTENER for STANDBY DB and start the LISTENER by DISABLING FIREWALL.

A) Create a LISTENER for STANDBY DB

[oracle@dg11s ~]$ . oraenv
ORACLE_SID = [oracle] ? orcls
The Oracle base has been set to /u01/app/oracle
[oracle@dg11s ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/
[oracle@dg11s admin]$ netmgr
[oracle@dg11s admin]$ ll
total 8
-rw-r--r--. 1 oracle oinstall  469 Sep  7 11:46 listener.ora
drwxr-xr-x. 2 oracle oinstall   64 Apr 17  2019 samples
-rw-r--r--. 1 oracle oinstall 1536 Feb 14  2018 shrept.lst

[oracle@dg11s admin]$ cat listener.ora 

# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcls)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = orcls)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.37)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

B) DISABLE the FIREWALL for the PRIMARY DB.

To disable the firewall for the Primary DB we need to first connect with the root user and then by
giving command we have to follow the below steps.

[oracle@dg11s ~]$ su root
Password: oracle

[root@dg11s Desktop]# service firewalld stop  
          
[root@dg11s Desktop]# systemctl disable firewalld

[oracle@dg11s ~]$ su oracle

[oracle@dg11s admin]$ service firewalld status

Redirecting to /bin/systemctl status firewalld.service
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)

C) Start the LISTENER created for the STANDBY DB.

[oracle@dg11s admin]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 07-SEP-2022 11:54:34

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dg11s/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.37)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.37)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                07-SEP-2022 11:54:35
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/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dg11s/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.37)(PORT=1521)))
Services Summary...
Service "orcls" has 1 instance(s).
  Instance "orcls", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

STEP 3: Create TNSNAMES.ORA FILE for STANDBY DB and check TNSPING for PRIMARY DB and STANDBY DB.

A) Creation of TNSNAMES.ORA FILE in Standby DB

[oracle@dg11s admin]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/
[oracle@dg11s admin]$ netmgr
[oracle@dg11s admin]$ ll
total 12
-rw-r--r--. 1 oracle oinstall  469 Sep  7 11:46 listener.ora
drwxr-xr-x. 2 oracle oinstall   64 Apr 17  2019 samples
-rw-r--r--. 1 oracle oinstall 1536 Feb 14  2018 shrept.lst
-rw-r--r--. 1 oracle oinstall  566 Sep  7 12:00 tnsnames.ora

[oracle@dg11s admin]$ cat tnsnames.ora 

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCLS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.37)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcls)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.36)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

B) Check TNSPING for PRIMARY DB and STANDBY DB

Remember that to check the TNSPING for both, Primary DB and Standby DB, LISTENER and TNSNAMES.ORA files
should be created in both the DBs and LISTENER should be STARTED in both the DBs.

SERVER DG11S
============

[oracle@dg11s admin]$ tnsping orcls

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-SEP-2022 12:02:58
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.37)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcls)))
OK (10 msec)

[oracle@dg11s admin]$ tnsping orcl

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-SEP-2022 12:03:02
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.36)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (30 msec)

SERVER DG11
===========

[oracle@dg11s admin]$ tnsping orcl

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-SEP-2022 12:03:02
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.36)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (30 msec)

[oracle@dg11s admin]$ tnsping orcls

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-SEP-2022 12:02:58
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.37)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcls)))
OK (10 msec)

STEP 4: Set the ENVIRONMENT of Standby DB

[oracle@dg11s ~]$ . oraenv
ORACLE_SID = [oracle] ? orcls
The Oracle base has been set to /u01/app/oracle

[oracle@dg11s ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.0.0/dbhome_1

[oracle@dg11s ~]$ echo $ORACLE_SID
orcls

[oracle@dg11s ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 7 12:14:24 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>

STEP 5: Create a P-FILE for Standby DB and create required DIRECTORIES.

A) Create a P-FILE for Standby DB with only GLOBAL_DB_Name inside it.

[oracle@dg11s ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/dbs/
[oracle@dg11s dbs]$ ll
total 12
-rw-rw----. 1 oracle oinstall 1544 Aug 21 14:04 hc_orcl.dat
-rw-rw----. 1 oracle oinstall 1544 Sep  7 11:21 hc_orcls.dat
-rw-r--r--. 1 oracle oinstall 3079 May 14  2015 init.ora

[oracle@dg11s dbs]$ vi initorcls.ora

[oracle@dg11s dbs]$ ll
total 16
-rw-rw----. 1 oracle oinstall 1544 Aug 21 14:04 hc_orcl.dat
-rw-rw----. 1 oracle oinstall 1544 Sep  7 11:21 hc_orcls.dat
-rw-r--r--. 1 oracle oinstall 3079 May 14  2015 init.ora
-rw-r--r--. 1 oracle oinstall   17 Sep  7 12:20 initorcls.ora

[oracle@dg11s dbs]$ cat initorcls.ora 
*.db_name='orcl'

NOTE: 
We created a blank P-FILE for the Standby DB and just added the Global_DB_NAME as *.db_name='orcl' to it.

B) Create required directories needed for Standby DB Cloning.

[oracle@dg11s dbs]$ mkdir -p /u01/app/oracle/admin/orcl/adump
[oracle@dg11s dbs]$ mkdir -p /u01/app/oracle/oradata/ORCL
[oracle@dg11s dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/ORCL/

NOTE: 
We are creating above directories keeping in mind that GLOBAL DB NAME 'orcl' is used instead of DB UNIQUE NAME 'orcls'. If we use DB UNIQUE NAME 'orcls' then the DUPLICATING/CLONING process of Primary DB to Standby DB may fail because the Primary DB does not have any directory named with DB UNIQUE NAME 'orcls'.

STEP 6: STARTUP the DB in NOMOUNT mode using new P-FILE created for the Standby DB.

SQL> startup nomount pfile='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initorcls.ora';

ORACLE instance started.

Total System Global Area  243268216 bytes
Fixed Size		    8895096 bytes
Variable Size		  180355072 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    3686400 bytes
SQL>

STEP 7: Connect to AUXILIARY DB using RMAN PROMPT.

SQL> !

[oracle@dg11s ~]$ rman target sys/oracle@orcl auxiliary sys/oracle@orcls

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 7 13:35:08 2022
Version 19.3.0.0.0

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

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

STEP 8: RUN the SCRIPT in RMAN PROMPT to CLONE the Primary DB to Standby DB.

RMAN> run
{
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
SPFILE
PARAMETER_VALUE_CONVERT='orcl','orcls'
SET DB_NAME='orcl'
SET DB_UNIQUE_NAME='orcls'
SET STANDBY_FILE_MANAGEMENT='AUTO'
set audit_file_dest ='/u01/app/oracle/admin/orcl/adump'
set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/'
set fal_server='orcl'
set fal_client='orcls'
set control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl'
NOFILENAMECHECK;
}

+++++++++++++ OUTPUT OF SCRIPT ++++++++++++++++++++++++


RMAN> run
{
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
SPFILE
PARAMETER_VALUE_CONVERT='orcl','orcls'
SET DB_NAME='orcl'
SET DB_UNIQUE_NAME='orcls'
SET STANDBY_FILE_MANAGEMENT='AUTO'
set audit_file_dest ='/u01/app/oracle/admin/orcl/adump'
set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/'
set fal_server='orcl'
set fal_client='orcls'
set control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
NOFILENAMECHECK;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 

Starting Duplicate Db at 07-SEP-22
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=39 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapworcls'   ;
   restore clone from service  'orcl' spfile to 
 '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileorcls.ora';
   sql clone "alter system set spfile= ''/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileorcls.ora''";
}
executing Memory Script

Starting backup at 07-SEP-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=139 device type=DISK
Finished backup at 07-SEP-22

Starting restore at 07-SEP-22
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 orcl
channel ORA_AUX_DISK_1: restoring SPFILE
output file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileorcls.ora
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 07-SEP-22

sql statement: alter system set spfile= ''/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileorcls.ora''

contents of Memory Script:
{
   sql clone "alter system set  dispatchers = 
 ''(PROTOCOL=TCP) (SERVICE=orclsXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_name = 
 ''orcl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''orcls'' comment=
 '''' scope=spfile";
   sql clone "alter system set  STANDBY_FILE_MANAGEMENT = 
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  audit_file_dest = 
 ''/u01/app/oracle/admin/orcl/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_recovery_file_dest = 
 ''/u01/app/oracle/fast_recovery_area/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server = 
 ''orcl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_client = 
 ''orcls'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files = 
 ''/u01/app/oracle/oradata/orcl/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=orclsXDB)'' comment= '''' scope=spfile

sql statement: alter system set  db_name =  ''orcl'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''orcls'' comment= '''' scope=spfile

sql statement: alter system set  STANDBY_FILE_MANAGEMENT =  ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/orcl/adump'' comment= '''' scope=spfile

sql statement: alter system set  db_recovery_file_dest =  ''/u01/app/oracle/fast_recovery_area/'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''orcl'' comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''orcls'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''/u01/app/oracle/oradata/orcl/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1157627168 bytes

Fixed Size                     8895776 bytes
Variable Size                704643072 bytes
Database Buffers             436207616 bytes
Redo Buffers                   7880704 bytes
duplicating Online logs to Oracle Managed File (OMF) location

contents of Memory Script:
{
   restore clone from service  'orcl' standby controlfile;
}
executing Memory Script

Starting restore at 07-SEP-22
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
Finished restore at 07-SEP-22

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05538: warning: implicitly using DB_FILE_NAME_CONVERT
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/system01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/sysaux01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/undotbs01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/users01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name /u01/app/oracle/oradata/ORCL/temp01.dbf conflicts with a file used by the target database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/app/oracle/oradata/ORCL/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/ORCL/system01.dbf";
   set newname for datafile  3 to 
 "/u01/app/oracle/oradata/ORCL/sysaux01.dbf";
   set newname for datafile  4 to 
 "/u01/app/oracle/oradata/ORCL/undotbs01.dbf";
   set newname for datafile  7 to 
 "/u01/app/oracle/oradata/ORCL/users01.dbf";
   restore
   from  nonsparse   from service 
 'orcl'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/ORCL/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 07-SEP-22
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 orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 07-SEP-22

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=2 STAMP=1114784259 file name=/u01/app/oracle/oradata/ORCL/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1114784259 file name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1114784259 file name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=1114784259 file name=/u01/app/oracle/oradata/ORCL/users01.dbf
Finished Duplicate Db at 07-SEP-22

RMAN> 

Hence the PRIMARY DB has been successfully duplicated as STANDBY DB and can now be checked if both the DB's are in SYNC or not.

STEP 9: STARTUP the DB in READ_ONLY mode and VALIDATE some parameters and RECTIFY them if required.

A) STARTUP the DB in READ_ONLY mode.

RMAN> exit

Recovery Manager complete.

[oracle@dg11s ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 7 18:53:18 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter database open; 

Database altered.

SQL> select name, open_mode, DB_UNIQUE_NAME, DATABASE_ROLE from v$database;

NAME	  OPEN_MODE	       DB_UNIQUE_NAME		      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL	  MOUNTED	       orcls			      PHYSICAL STANDBY

B) Validate some Parameters in READ_ONLY mode.

SQL> show parameter name

NAME				           TYPE	    VALUE
--------------------     ----------- -----------
cdb_cluster_name		     string
cell_offloadgroup_name		 string
db_file_name_convert		 string
db_name 			         string	 	orcl
db_unique_name			     string	 	orcls
global_names			     boolean	FALSE
instance_name			     string	 	orcls
lock_name_space 		     string
log_file_name_convert		 string
pdb_file_name_convert		 string
processor_group_name		 string
service_names			     string	     orcls

SQL> show parameter control

NAME				                    TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time	     integer	  7
control_files			              string	  /u01/app/oracle/oradata/orcl/c
						                          ontrol01.ctl, /u01/app/oracle/
						                          fast_recovery_area/orcl/contro
						                          l02.ctl
control_management_pack_access	     string	      DIAGNOSTIC+TUNING

SQL> show parameter spfile

NAME				   TYPE	      VALUE
---------------- ----------- ------------------------------
spfile				  string	 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileorcls.ora

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> select FORCE_LOGGING from v$database;

FORCE_LOGGING
---------------------------------------
YES

SQL> show parameter LOG_ARCHIVE_CONFIG

NAME				          TYPE	        VALUE
---------------------      ----------- ------------------------------
log_archive_config		     string	      DG_CONFIG=(orcl,orcls)


SQL> show parameter dest

NAME				     				TYPE	 VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest 		    		 string	      /u01/app/oracle/admin/orcl/adump						 
background_dump_dest				 string	      /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log						 
core_dump_dest			    		 string	      /u01/app/oracle/diag/rdbms/orcls/orcls/cdump						 
cursor_bind_capture_destination      string	      memory+disk
db_recovery_file_dest		         string	      /u01/app/oracle/fast_recovery_area/						 
db_recovery_file_dest_size	         big integer  8256M
diagnostic_dest 		             string	      /u01/app/oracle
log_archive_dest_2		             string	      SERVICE=orcls NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcls
log_archive_dest_state_2	         string	      ENABLE
log_archive_min_succeed_dest	     integer	  1
user_dump_dest			             string	      /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log

NOTE:

Here the ‘log_archive_dest_2’ is displaying name of STANDBY DB i.e ‘ORCLS’ which is WRONG.
—- Instead it should display the name of PRIMARY DB i.e ‘ORCL’. Hence we need to rectify this.

RESET the ‘log_archive_dest_2’ parameter

RESET the 'log_archive_dest_2' parameter
========================================

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both;

System altered.

SQL> show parameter log_archive_dest_2

NAME				     TYPE	 VALUE
------------------- ----------- ---------
log_archive_dest_2		string


SET the new location of 'log_archive_dest_2' parameter
======================================================

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=orcl NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl';

System altered.

SQL> show parameter log_archive_dest_2

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		 string	 SERVICE=orcl NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl

Now the new location for ‘log_archive_dest_2’ parameter is set to PRIMARY DB which is ‘ORCL’.

Hence all parameters looks fine.

4) COMPLETE STEP BY STEP PROCESS FOR MAKING THE PRIMARY DATABASE and STANDBY DATABASE IN SYNC.

The high level steps require to configure a Primary DB are given below. These are as follow:

Step 01: Check the MAXIMUM SEQUENCE of ARCHIVED LOGS applied in Primary DB and Standby DB.
Step 02: START the MRP Process in the STANDBY DB for applying ARCHIVES of PRIMARY DB.
Step 03: Check whether the REMAINING ARCHIVED LOGS are applied by MRP to the Standby DB.

STEP 1: Check the MAXIMUM SEQUENCE of ARCHIVED LOGS applied in Primary DB and Standby DB.

FOR PRIMARY DB - ORCL
=====================

SQL> select MAX(SEQUENCE#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	    23

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select MAX(SEQUENCE#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	    25

Here in PRIMARY DB the Maximum logs that were archived were 23.
So we did ‘alter system switch logfile’ twice to archive 2 more logs and thus the Maximum logs that were archived becomes 25.

Now these archives should be applied to the Standby DB also and the Maximum archived logs applied in the Standby DB should also be 25.

FOR STANDBY DB - ORCLS
======================

SQL> show parameter standby

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
enabled_PDBs_on_standby 	     string	 *
standby_db_preserve_states	     string	 NONE
standby_file_management 	     string	 AUTO
standby_pdb_source_file_dblink	     string
standby_pdb_source_file_directory    string

SQL> select MAX(SEQUENCE#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	    23

This query verifies that archives from the Primary DB are being received by the RFS in Standby DB.

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		          25		         23 	          2

Here we can see that since the ‘standby_file_management’ is ‘AUTO’, the Archives from the Primary DB are
being received by RFS of Standby DB but not getting applied in Standby DB.
Hence Max Sequence = 25 but Sequence Applied = 23 creating a difference of 2 Archives.

This is because the MRP process of the Standby DB is not started. When MRP process will be started then the remaining Archives will be taken from SRL by MRP and applied in the Standby DB.
This will make the Archive difference 0.

STEP 2: START the MRP Process in the STANDBY DB for applying ARCHIVES of PRIMARY DB.

FOR STANDBY DB – ORCLS

A) View to check if MRP process is running or not.

SQL> select process, status,client_process, thread#, sequence#, block#, blocks from v$managed_standby where process='MRP0';

no rows selected

               +++++++ OR ++++++++++

[oracle@dg11s ~]$ ps -ef | grep mrp
oracle    4223  4155  0 21:32 pts/1    00:00:00 grep --color=auto mrp

Hence MRP process in the Standby DB is not yet Started.

B) START the MRP process in the STANDBY DB and verify.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM SESSION;

Database altered.

If we want to STOP the MRP process in the STANDBY DB
In case of you want to cancel apply process, issue following command:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

C) Verify if MRP process in Standby DB is started or not.

SQL> select process, status,client_process, thread#, sequence#, block#, blocks from v$managed_standby where process='MRP0';

PROCESS   STATUS          CLIENT_P    THREAD#  SEQUENCE#	  BLOCK#     BLOCKS
--------- ------------     -------- ---------- ---------- ---------- ----------
MRP0	  APPLYING_LOG       N/A		 1	      26	      2044     409600


                  +++++++ OR ++++++++++

[oracle@dg11s dbs]$ ps -ef | grep mrp

oracle   16276     1  0 00:09 ?        00:00:00 ora_mrp0_orcls
oracle   16612 10046  0 00:14 pts/0    00:00:00 grep --color=auto mrp

Hence the MRP process has STARTED in the STANDBY DB.

Now lets see if the remaining archives are being applied to the Standby DB by the MRP.

STEP 3: Check whether the REMAINING ARCHIVED LOGS are applied by MRP to the Standby DB.

A) Check SEQUENCE NUMBER of the archives received by Standby DB.

SQL> select MAX(SEQUENCE#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	    25

B) Check SEQUENCE APPLIED is same as SEQUENCE NUMBER in Standby DB.

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		          25		          25         	 0

Hence we can see that the Difference in Archive logs is 0 and hence the MRP is working properly
and applying the Archived logs of the Primary DB to the Standby DB efficiently.

Hope it helped ..

More Articles

text on computer monitor 1 min read

How to check FRA ( Fast Recovery area) Utilization in Oracle ?

asrblogger
text on computer monitor 6 min read

How to check Tablespace Utilization in Oracle ?

asrblogger
wooden world map on wall 4 min read

How to Rename ASM Diskgroup ??

asrblogger

You may have missed

text on computer monitor 1 min read

How to check FRA ( Fast Recovery area) Utilization in Oracle ?

asrblogger
text on computer monitor 6 min read

How to check Tablespace Utilization in Oracle ?

asrblogger
15 min read

Install and Configure OpenSearch & OpenSearch Dashboard

Avnish Singh
wooden world map on wall 4 min read

How to Rename ASM Diskgroup ??

asrblogger