Data Guard Configuration In Oracle 19c Step by Step Tutorial
In this article we are going to show step by step configuration oracle 19c data guard physical standby. This article we will show oracle 19c data guard configuration for single instance database.
Assumptions:
Primary
Server |
Standby
Server |
Database
is running |
Software
only installed |
Database Unique Name: DBPRIMARY |
Database Unique Name: DBSTANDBY |
IP:
192.168.1.104 |
IP:
192.168.1.106 |
@@ Primary Server
Configuration:
Step 1: Check primary database archive log mode whether it
is enable or disable
SQL> select log_mode from v$database;
LOG_MODE
---------------------
NOARCHIVELOG
Since database is in noarchive mode, the log mode of the database needs
to be changed. Now we show the log mode
change steps.
i. Shutdown database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down
ii. Startup database in mount mode
SQL> startup mount
ORACLE instance started.
Total System Global Area 1174404432 bytes
Fixed Size
8895824 bytes
Variable Size
318767104 bytes
Database Buffers
838860800 bytes
Redo Buffers 7880704 bytes
Database mounted.
iii. Change log mode from noarchive to archivelog
SQL> alter database archivelog;
Database altered.
iv. Open database
SQL> alter database open;
v. Check database log mode again
SQL> select log_mode from v$database;
------------
ARCHIVELOG
Now, database is in archivelog mode.
Step 2: Now check the db_name and db_unique_name using
following command
SQL> show parameter db_name
NAME TYPE VALUE
--------------------- -----------
--------------------------
db_name string DBPRIMARY
SQL> show parameter db_unique_name
NAME TYPE VALUE
----------------------- -----------
--------------------------
db_unique_name string DBPRIMARY
Note: Oracle DB_NAME is same for both primary and standby
database but only difference at DB_UNIQUE_NAME.
Step 3: Change following parameter in primary database
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> alter system set standby_file_management ='AUTO'
scope=both;
System altered.
SQL> alter system set
log_archive_config='dg_config=(DBPRIMARY,DBSTANDBY)' scope=both;
System altered.
Note: Primary database unique_name =DBPRIMARY and Standby
database unique_name=DBSTANDBY
SQL> alter system set
log_archive_dest_2='service=DBSTANDBY async
valid_for=(online_logfile,primary_role) db_unique_name=DBSTANDBY' scope=both;
System altered.
SQL> alter system set fal_server=DBSTANDBY scope=both;
System altered.
Note: fal_server=dr database_unique_name
Step 4: Create standby redo logs on the primary database. In
my case, the following standby redo logs must be created on both servers.
Now check the existing log group and log file location using
below command.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------------------------------------------------------------
1 /u01/oracle/oradata/DBPRIMARY/redo01.log
2 /u01/oracle/oradata/DBPRIMARY/redo02.log
3 /u01/oracle/oradata/DBPRIMARY/redo03.log
Now add the standby redo logs on primary database.
SQL> alter database add standby logfile THREAD 1 group 5
('/u01/oracle/oradata/DBPRIMARY/stb_redog5.log') size 100m;
Database altered.
SQL> alter database add standby logfile THREAD 1 group 6
('/u01/oracle/oradata/DBPRIMARY/stb_redog6.log') size 100m;
Database altered.
SQL> alter database add standby logfile THREAD 1 group 7
('/u01/oracle/oradata/DBPRIMARY/stb_redog7.log') size 100m;
Database altered.
SQL> alter database add standby logfile THREAD 1 group 8
('/u01/oracle/oradata/DBPRIMARY/stb_redog8.log') size 100m;
Database altered.
Note: Standby redo log should be one extra group per thread compared with the online redo logs.
Step 5: On primary database create static /dynamic listener
Primary Database listener entry:
# Listener location:
$ORACLE_HOME/network/admin/listener.ora
# Generated by Oracle configuration tools (netca)
SID_LIST_LISTENER
=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DBPRIMARY)
(ORACLE_HOME =
/u01/oracle/product/19c/dbhome_1)
(SID_NAME = DBPRIMARY)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.104)(PORT
= 1521))
)
)
Primary
Server TNS:
# TNS file location: $ORACLE_HOME/admin/tnsnames.ora
# Generated by Oracle configuration tools (netca)
DBPRIMARY
=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.104)(PORT
= 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DBPRIMARY)
)
)
DBSTANDBY=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.1.106)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DBSTANDBY)
)
)
Note:
Check tnsping both primary and standby server
tnsping DBPRIMARY >>Must be ok
tnsping DBSTANDBY >>Must be ok
Step 6: Create pfile on primary database.
sqlplus / as sysdba
SQL> create pfile='/tmp/init_standby.ora'
from spfile;
File created.
@@ Standby Server Configuration:
Step 7: Scp the listener, tns and password file from primary to standby server and change entry as follow.
scp listener.ora tnsname.ora oracle:192.168.1.106:$ORACLE_HOME/network/admin
scp orapwPRIMARYDB oracle:192.168.1.106:$ORACLE_HOME/dbs
Send pfile that was created in primary database to standby
database
scp init_standby.ora oracle:192.168.1.106:/tmp
Step 8: Listener entry change as follow:
SID_LIST_LISTENER
=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DBSTANDBY)
(ORACLE_HOME =
/u01/oracle/product/19c/dbhome_1)
(SID_NAME = DBSTANDBY)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1521))
)
)
@ Start listener on standby database
# lsnrctl start
Step 9: Change password file and parameter file as follow.
cd $ORACLE_HOME/dbs
# mv orapwDBPRIMARY orapwDBSTANDBY
# Change the following entry in pfile for standby database
*.db_unique_name='DBSTANDBY' ==this parameter is needed to be
added
*.fal_server='DBPRIMARY'
==change the value
*.log_archive_dest_2='SERVICE=DBPRIMARY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBPRIMARY'
Note: change the service and db_unique_name
Step 10: Create Physical Location as per requirement for the Data file, archive log location, Control file Location, online log location, audit location. This log location find from standby parameter file.
# mkdir -p /u01/oracle/admin/DBPRIMARY/adump
# mkdir -p /u01/oracle/oradata
# mkdir -p /u01/oracle/oradata/DBPRIMARY
# export
ORACLE_SID=DBSTANDBY
Step 11: Check tnsping from standby database it must be okay from primary database also
[oracle@dbstandby
~]$ tnsping dbprimary
TNS
Ping Utility for Linux: Version 19.0.0.0.0 - Production on 04-JUL-2021 16: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.104)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DBPRIMARY)))
OK
(30 msec)
[oracle@dbstandby ~]$ tnsping dbstandby
TNS
Ping Utility for Linux: Version 19.0.0.0.0 - Production on 04-JUL-2021 16:05:31
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.106)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DBSTANDBY)))
OK
(60 msec)
Step 12: startup standby database in nomount mode
SQL> startup nomount pfile='/tmp/init_standby.ora'
ORACLE
instance started.
Total
System Global Area 838858176 bytes
Fixed
Size 8902080 bytes
Variable
Size 226492416 bytes
Database
Buffers 599785472 bytes
Redo
Buffers 3678208 bytes
Step 13: Now run the duplicate database creation to create standby database using following command.
[oracle@dbstandby dbs]$ rman target sys/sys123@dbprimary
AUXILIARy sys/sys123@dbstandby
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jul
4 16:08:36 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBPRIMAR (DBID=2199024026)
connected to auxiliary database: DBPRIMAR (not mounted)
RMAN> DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;
2> 3> 4> 5>
Starting Duplicate Db at 04-JUL-21
using target database control file instead of recovery
catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=427 device type=DISK
current log archived
……..
……….
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
deleted archived log
archived log file
name=/u01/oracle/oradata/DBSTANDBY/archivelog/2021_07_04/o1_mf_1_12_jg38k391_.arc
RECID=1 STAMP=1077040643
deleted archived log
archived log file
name=/u01/oracle/oradata/DBSTANDBY/archivelog/2021_07_04/o1_mf_1_13_jg38k5fv_.arc
RECID=2 STAMP=1077040645
Deleted 2 objects
Finished Duplicate Db at 04-JUL-21
Done!
Post a Comment
1 Comments
Hi,
ReplyDeletemany thanks for your helpfull tutorial. I am a new oracle DBA and i have some questions regarding Data Guard Setup.
As far as i know a primary can have a up to 9 standby databases. Now, i would like to know if it is possible to setup a data guard with
1 Primary and simultaneous 1 physical and 1 logical standby databases.
If this is possible, how to configure it? in the log_archive_dest_2=Location to point to Physical standby and log_archive_dest_3=Location to point o logical standby?
What is about the dg_config parameter and other data guard specific parameter?
For example:
#==> Primary database
alter system set LOG_ARCHIVE_DEST_1='LOCATION= VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)';
#<== Primary database
#==> physical standby
alter system set LOG_ARCHIVE_DEST_2='SERVICE= LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=';
#<== logical standby
#==> logical standby
alter system set LOG_ARCHIVE_DEST_3='LOCATION= VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)';
#<== logical standby
#show parameter log_archive_dest_2 before and after
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY, PHYSICAL_STBY, LOGICAL_STBY)' SCOPE=BOTH;
How to implement such a system with 1 primary and 2 standby (physical & logical)?
How the rman duplicate for standby setup should be?
In one RMAN Duplicate? Or It should be 2 different setups/ RMAN duplicate:?
1 for physical standby
1 for logical standby
Or first create 2 physical standby and then convert the 2nd physical to logical?
Many thanks in advance for your time and help.
Ousseini Oumarou