XMAGE

RANLYTICS

https://oracledbwr.com/oracle-19c-step-by-step-configuration-of-data-guard-broker-in-oracle-19c/
Step1:-Change Archivelog mode and force logging mode

[oracle@dev19c ~]$ export ORACLE_SID=chennai
[oracle@dev19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 � Production on Fri Oct 18 12:19:23 2019
Version 19.4.1.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.4.1.0.0

SQL> startup mount
ORACLE instance started.
Total System Global Area 1048575776 bytes
Fixed Size 8904480 bytes
Variable Size 272629760 bytes
Database Buffers 763363328 bytes
Redo Buffers 3678208 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> ALTER DATABASE FORCE LOGGING;
Database altered.

SQL> alter database open;
Database altered.

SQL> select FORCE_LOGGING,log_mode from v$database;

FORCE_LOGGING           LOG_MODE
������������� ����
YES                                      ARCHIVELOG

Step2:-Adding Redologfile for standby database

SQL> alter database add standby logfile group 4 �/u01/app/oracle/oradata/CHENNAI/redo04.log� size 50m;
Database altered.

SQL> alter database add standby logfile group 5 �/u01/app/oracle/oradata/CHENNAI/redo05.log� size 50m;
Database altered.

SQL> alter database add standby logfile group 6 �/u01/app/oracle/oradata/CHENNAI/redo06.log� size 50m;
Database altered.

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

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

Step3:-Adding the network entry in primary and standby side(Both servers)

Tnsnames entry:-
**************
chennai =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.155)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = chennai)
)
)

delhi =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.156)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = delhi)
)
)

Listener Entry:-
************
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = chennai)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1/)
(SID_NAME = chennai)
)
(SID_DESC =
(GLOBAL_DBNAME = delhi)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1/)
(SID_NAME = delhi)
)
)

Output like the below

[oracle@dev19c ~]$ tnsping chennai
TNS Ping Utility for Linux: Version 19.0.0.0.0 � Production on 18-OCT-2019 13:12:42
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.155)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = chennai)))
OK (10 msec)

[oracle@dev19c ~]$ tnsping delhi
TNS Ping Utility for Linux: Version 19.0.0.0.0 � Production on 18-OCT-2019 13:12:47
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.125.156)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = delhi)))
OK (0 msec)

step4:-Changing parameters in primary database

SQL> ALTER SYSTEM SET log_archive_config=�dg_config=(chennai,delhi)� SCOPE=both;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_1=�location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=chennai� SCOPE=both;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_2=�service=delhi async valid_for=(online_logfiles,primary_role) db_unique_name=delhi� SCOPE=both;
System altered.

SQL> ALTER SYSTEM SET fal_server=�DELHI� SCOPE=both;
System altered.

SQL> ALTER SYSTEM SET fal_client=�CHENNAI� SCOPE=both;
System altered.

SQL> ALTER SYSTEM SET standby_file_management=�AUTO� SCOPE=both;
System altered.

Step5:- Password file creation

copy the remote login password file (orapwchennai) from the primary database server to the $ORACLE_HOME/dbs directory on the
standby database server, renaming it to orapwdelhi.

[oracle@dev19c dbs]$ scp orapwchennai [email protected]:$ORACLE_HOME/dbs
The authenticity of host �192.168.125.156 (192.168.125.156)� can�t be established.
ECDSA key fingerprint is SHA256:9ngU7dHd3U+4VprdmTolulpeCnX3zlJKbX2qqXMgZSg.
ECDSA key fingerprint is MD5:f8:69:0d:e3:68:0d:24:30:cf:e3:17:6c:7a:59:05:94.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added �192.168.125.156� (ECDSA) to the list of known hosts.
[email protected]�s password:
orapwchennai 100% 2048 192.4KB/s 00:00

[oracle@devdr19c dbs]$ mv orapwchennai orapwdelhi

Step6:- Changing parameters in standby database

In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initdelhi.ora
Containing a single parameter: DB_NAME=chennai

[oracle@devdr19c dbs]$ cat initdelhi.ora
db_name=chennai
[oracle@devdr19c dbs]$ pwd
/u01/app/oracle/product/19.3.0/db_1/dbs

Step7:- Create directory Structure in Standby database

[oracle@devdr19c dbs]$ cd $ORACLE_BASE/admin/
[oracle@devdr19c admin]$ mkdir delhi
[oracle@devdr19c admin]$ cd delhi
[oracle@devdr19c delhi]$ mkdir adump
[oracle@devdr19c delhi]$ mkdir -p /u01/app/oracle/oradata/DELHI

Step8:- start the standby database using pfile

[oracle@devdr19c delhi]$ cd $ORACLE_HOME/dbs
[oracle@devdr19c dbs]$ export ORACLE_SID=delhi
[oracle@devdr19c dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 � Production on Fri Oct 18 13:55:45 2019
Version 19.4.1.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup pfile=�initdelhi.ora� nomount
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

Step9:- connect to the rman

[oracle@devdr19c admin]$ export ORACLE_SID=chennai
[oracle@devdr19c admin]$ rman target sys/oracle@chennai auxiliary sys/oracle@delhi

Recovery Manager: Release 19.0.0.0.0 � Production on Fri Oct 18 14:02:07 2019
Version 19.4.1.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: CHENNAI (DBID=114377971)
connected to auxiliary database: DELHI (not mounted)

Step10:-Build the oracle 19c standby using rman active duplicate method 

RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert �chennai�,�delhi�
set db_name=�chennai�
set db_unique_name=�delhi�
set db_file_name_convert=�/u01/app/oracle/oradata/CHENNAI/�,�/u01/app/oracle/oradata/DELHI/�
set log_file_name_convert=�/u01/app/oracle/oradata/CHENNAI/�,�/u01/app/oracle/oradata/DELHI/�
set control_files=�/u01/app/oracle/oradata/DELHI/standby1.ctl�
set log_archive_max_processes=�5'
set fal_client=�delhi�
set fal_server=�chennai�
set standby_file_management=�AUTO�
set log_archive_config=�dg_config=(chennai,delhi)�
set compatible=�19.4.0.1.0'
set memory_target=�1200m�
nofilenamecheck;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24>

using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=4 device type=DISK
allocated channel: p2
channel p2: SID=427 device type=DISK
allocated channel: p3
channel p3: SID=461 device type=DISK
allocated channel: p4
channel p4: SID=6 device type=DISK
allocated channel: s1
channel s1: SID=38 device type=DISK
Starting Duplicate Db at 18-OCT-19
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format �/u01/app/oracle/product/19.3.0/db_1/dbs/orapwdelhi� targetfile
�/u01/app/oracle/product/19.3.0/db_1/dbs/spfilechennai.ora� auxiliary format
�/u01/app/oracle/product/19.3.0/db_1/dbs/spfiledelhi.ora� ;
sql clone �alter system set spfile= �/u01/app/oracle/product/19.3.0/db_1/dbs/spfiledelhi.ora��;
}
executing Memory Script
Starting backup at 18-OCT-19
Finished backup at 18-OCT-19
sql statement: alter system set spfile= �/u01/app/oracle/product/19.3.0/db_1/dbs/spfiledelhi.ora�
contents of Memory Script:
{
sql clone �alter system set audit_file_dest =
�/u01/app/oracle/admin/delhi/adump� comment=
�� scope=spfile�;
sql clone �alter system set dispatchers =
�(PROTOCOL=TCP) (SERVICE=delhiXDB)� comment=
�� scope=spfile�;
sql clone �alter system set db_name =
�chennai� comment=
�� scope=spfile�;
sql clone �alter system set db_unique_name =
�delhi� comment=
�� scope=spfile�;
sql clone �alter system set db_file_name_convert =
�/u01/app/oracle/oradata/CHENNAI/�, �/u01/app/oracle/oradata/DELHI/� comment=
�� scope=spfile�;
sql clone �alter system set log_file_name_convert =
�/u01/app/oracle/oradata/CHENNAI/�, �/u01/app/oracle/oradata/DELHI/� comment=
�� scope=spfile�;
sql clone �alter system set control_files =
�/u01/app/oracle/oradata/DELHI/standby1.ctl� comment=
�� scope=spfile�;
sql clone �alter system set log_archive_max_processes =
5 comment=
�� scope=spfile�;
sql clone �alter system set fal_client =
�delhi� comment=
�� scope=spfile�;
sql clone �alter system set fal_server =
�chennai� comment=
�� scope=spfile�;
sql clone �alter system set standby_file_management =
�AUTO� comment=
�� scope=spfile�;
sql clone �alter system set log_archive_config =
�dg_config=(chennai,delhi)� comment=
�� scope=spfile�;
sql clone �alter system set compatible =
�19.4.0.1.0� comment=
�� scope=spfile�;
sql clone �alter system set memory_target =
1200m comment=
�� scope=spfile�;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = �/u01/app/oracle/admin/delhi/adump� comment= �� scope=spfile
sql statement: alter system set dispatchers = �(PROTOCOL=TCP) (SERVICE=delhiXDB)� comment= �� scope=spfile
sql statement: alter system set db_name = �chennai� comment= �� scope=spfile
sql statement: alter system set db_unique_name = �delhi� comment= �� scope=spfile
sql statement: alter system set db_file_name_convert = �/u01/app/oracle/oradata/CHENNAI/�, �/u01/app/oracle/oradata/DELHI/� comment= �� scope=spfile
sql statement: alter system set log_file_name_convert = �/u01/app/oracle/oradata/CHENNAI/�, �/u01/app/oracle/oradata/DELHI/� comment= �� scope=spfile
sql statement: alter system set control_files = �/u01/app/oracle/oradata/DELHI/standby1.ctl� comment= �� scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= �� scope=spfile
sql statement: alter system set fal_client = �delhi� comment= �� scope=spfile
sql statement: alter system set fal_server = �chennai� comment= �� scope=spfile
sql statement: alter system set standby_file_management = �AUTO� comment= �� scope=spfile
sql statement: alter system set log_archive_config = �dg_config=(chennai,delhi)� comment= �� scope=spfile
sql statement: alter system set compatible = �19.4.0.1.0� comment= �� scope=spfile
sql statement: alter system set memory_target = 1200m comment= �� scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 503316480 bytes
Database Buffers 738197504 bytes
Redo Buffers 7880704 bytes
allocated channel: s1
channel s1: SID=34 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format �/u01/app/oracle/oradata/DELHI/standby1.ctl�;
}
executing Memory Script
Starting backup at 18-OCT-19
channel p1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/19.3.0/db_1/dbs/snapcf_chennai.f tag=TAG20191018T144453
channel p1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-OCT-19
contents of Memory Script:
{
sql clone �alter database mount standby database�;
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
�/u01/app/oracle/oradata/DELHI/temp01.dbf�;
switch clone tempfile all;
set newname for datafile 1 to
�/u01/app/oracle/oradata/DELHI/system01.dbf�;
set newname for datafile 3 to
�/u01/app/oracle/oradata/DELHI/sysaux01.dbf�;
set newname for datafile 4 to
�/u01/app/oracle/oradata/DELHI/undotbs01.dbf�;
set newname for datafile 7 to
�/u01/app/oracle/oradata/DELHI/users01.dbf�;
backup as copy reuse
datafile 1 auxiliary format
�/u01/app/oracle/oradata/DELHI/system01.dbf� datafile
3 auxiliary format
�/u01/app/oracle/oradata/DELHI/sysaux01.dbf� datafile
4 auxiliary format
�/u01/app/oracle/oradata/DELHI/undotbs01.dbf� datafile
7 auxiliary format
�/u01/app/oracle/oradata/DELHI/users01.dbf� ;
sql �alter system archive log current�;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/DELHI/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 18-OCT-19
channel p1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/CHENNAI/system01.dbf
channel p2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/CHENNAI/sysaux01.dbf
channel p3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/CHENNAI/undotbs01.dbf
channel p4: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/CHENNAI/users01.dbf
output file name=/u01/app/oracle/oradata/DELHI/users01.dbf tag=TAG20191018T144500
channel p4: datafile copy complete, elapsed time: 00:00:07
output file name=/u01/app/oracle/oradata/DELHI/undotbs01.dbf tag=TAG20191018T144500
channel p3: datafile copy complete, elapsed time: 00:00:55
output file name=/u01/app/oracle/oradata/DELHI/system01.dbf tag=TAG20191018T144500
channel p1: datafile copy complete, elapsed time: 00:01:17
output file name=/u01/app/oracle/oradata/DELHI/sysaux01.dbf tag=TAG20191018T144500
channel p2: datafile copy complete, elapsed time: 00:01:16
Finished backup at 18-OCT-19
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=1 STAMP=1021992380 file name=/u01/app/oracle/oradata/DELHI/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1021992380 file name=/u01/app/oracle/oradata/DELHI/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1021992380 file name=/u01/app/oracle/oradata/DELHI/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1021992380 file name=/u01/app/oracle/oradata/DELHI/users01.dbf
Finished Duplicate Db at 18-OCT-19
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1
RMAN>

Step11:- connect to the standby database

[oracle@devdr19c dbs]$ export ORACLE_SID=delhi
[oracle@devdr19c dbs]$ sqlplus �/as sysdba�

SQL*Plus: Release 19.0.0.0.0 � Production on Fri Oct 18 14:54:43 2019
Version 19.4.1.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.4.1.0.0

SQL> alter database recover managed standby database disconnect nodelay;;
Database altered.

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
���- ��� ��� ���
13 18-OCT-19 18-OCT-19 YES
14 18-OCT-19 18-OCT-19 YES
15 18-OCT-19 18-OCT-19 YES

QUERY to check the difference 

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

Christopher D O'Mahony - 4 
Andrew K Waddell - 2 
Andrew P J Davies - 2 
James D Heyes - 2 
Mary C Symes - 2 
Basil E Potts - 1 
Betty F Stracey - 1 
David C Bath - 1 
Edward J Reid - 1 
Esther M Perrins - 1 
Ian A Douglas - 1 
Ian D Harris - 1 
Ian P McCulloch - 1 
James G Waddell - 1 
James W Perrins - 1 
Katherine M Downs - 1 
Michael E Stracey - 1 
Peter I Harrison - 1 
Robin R Churchill - 1 
Ronald E J Shepherd - 1 
Thomas M Perrins - 1 
William T Perrins - 1 

1 Hilen Amin
1 Henry Attfield
1 Bob Blanden
1 Colin Brown
1 Leo Brown
1 Michael Bryant
1 Helen Campbell
1 Emma Carnuccio
1 Billy Clarke
1 Andrew Cole
1 David Collins
1 Michael Collinson
1 Gordon Connon
1 Stephen Croxall
1 Shane Daly
1 Andrew Davies
1 Paul Doyle
1 Corinne Duncan
1 Matthew Farthing
1 Tom Goodyer
1 Vicki Greenhalgh
1 Alison Hall
1 Ian Harris
1 Peter Harrison
1 David Heyes
1 Jayne Hodgkin
1 Tracy Huang
1 David Killner
1 Elaine Lee
1 James Li
1 Gabby Ling
1 Roger Lubbock
1 Robert McManus
1 Allison McMillan
1 Simon McMillan
1 Patrick Meyer
1 Andrew Mills
1 Geoff Milne
1 Tony Murray-Feist
1 Sally Nicols
1 Graham Nobbs
1 Viv Nobbs
1 Fran Novitski
1 Claire O'Mahony
1 Ian Oram
1 Margaret Oram
1 Tom Ovens
1 Sue Pacey
1 David Parker
1 Murray-Luke Peard
1 Kay Pendlebury
1 Stephen Relf
1 Andrew Reynolds
1 Maria Ross
1 Frank Sluter
1 Ann Smith
1 Ian Smith
1 Michelle Sorell
1 Luka Sostarko
1 Valerie Stone
1 Eileen Sykes
1 Ian Sykes
1 Georgina Templeton
1 Roy Tillcock
1 Bert Woolven
2 Andrew Ling
2 Bill Perrins
2 Callum Crofton
2 David Pacey
2 David Smith
2 Denis Woodhams
2 James Perrins
2 John Cater
2 John Sibbald
2 Julie Taylor
2 Kellie Hajek
2 Laura Ivey
2 Lexi Prabhakar
2 Mary Townsend
2 Pam Brock
2 Phoebe Houghton
2 Richard Offen
2 Sam Rorke
2 Thomas Perrins
2 Valerie Wayte
2 Will Ryan
3 Christian Peckham
3 Deryn Griffiths
3 Enid Roberts
3 Richard Thomas
4 Adam Beer
4 Kathi Downs
4 Rachael Eagle
4 Steve Skitmore
4 Susan Tonkin
4 Ted Reid
5 Ian Douglas
5 Matthew Sait
6 Penny Hill
10 Christopher O'Mahony
15 Jim Waddell
16 Andrew Waddell
19 James Heyes
25 Julie Doyle