Step by Step Data Guard Configuration in 19c
27 min read1) 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 ..
