Step by Step Data Guard Configuration oracle 19c
Primary Server-side Configurations
Step – 1 – Enable Archive log mode and Flashback on
SQL>shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1543500144 bytes Fixed Size 8896880 bytes Variable Size 1006632960 bytes Database Buffers 520093696 bytes Redo Buffers 7876608 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database flashback on; Database altered. SQL> alter database open; Database altered.
step -2 – Adding redo log file for standby logfile (in the primary database)
sql>alter database add standby logfile group 4
'/u01/app/oracle/oradata/ORCL/redo04.log' size 50m;
sql>alter database add standby logfile group 5
'/u01/app/oracle/oradata/ORCL/redo05.log' size 50m;
sql>alter database add standby logfile group 6
'/u01/app/oracle/oradata/ORCL/redo06.log' size 50m;
sql>alter database add standby logfile group 7
'/u01/app/oracle/oradata/ORCL/redo07.log' size 50m;
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 7 0 0 YES UNASSIGNED
step -3: – Ping Listener And Tnsnames Both Server……like output below,,
[oratest@oracle admin]$ tnsping orcl TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 24-OCT-2021 14:53:31 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.localdomain))) OK (0 msec) [oratest@oracle admin]$ tnsping stand TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 24-OCT-2021 14:53:36 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.20)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracle))) OK (10 msec)
step 4: – In the primary Database server (192.168.1.24)
SQL>ALTER SYSTEM SET log_archive_config=’dg_config=(orcl,stand)’ 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=orcl' SCOPE=both; System altered. SQL>alter system set log_archive_dest_2='service=stand async valid_for=(online_logfiles,primary_role) db_unique_name=stand' scope=both; System altered. SQL>ALTER SYSTEM SET fal_server='stand' SCOPE=both; System altered. SQL>ALTER SYSTEM SET fal_client='orcl' SCOPE=both; System altered. SQL>ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=both; System altered.
step -5: In Standby Server Side
1.. Copy password file in primary Database
in primary server, [oratest@oracle dbs]$ scp orapworcl [email protected]:/u01/app/oracle/product/19.0.0/dbhome_1/dbs [email protected]'s password: orapworcl 100% 2048 2.0KB/s 00:00 After completed scp in standby server side change the password file name,
like name below, [oracle@oracletest dbs]$ mv orapworcl orapwstand
2..Changing Parameters in Standby Database
In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initstand.ora Containing a single parameter: DB_NAME=orcl [oracle@oracletest dbs]$ cat initstand.ora db_name=orcl [oracle@oracletest dbs]$
3:- Create Directory Structure in Standby Database
[oracle@oracletest dbs]$ cd $ORACLE_BASE/admin/ [oracle@oracletest admin]$ mkdir stand [oracle@oracletest admin]$ cd stand/ [oracle@oracletest stand]$ mkdir adump [oracle@oracletest stand]$ mkdir -p /u01/app/oracle/oradata/stand
4:- Start the Standby Database using Pfile
[oracle@oracletest TEST]$ export ORACLE_SID=stand [oracle@oracletest TEST]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 25 10:05:00 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup pfile='$ORACLE_HOME/dbs/initstand.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
5-–> Connect RMAN for Primary Database in Standby server
[oracle@oracletest stand]$ export ORACLE_SID=orcl [oracle@oracletest stand]$ rman target sys/oracle@orcl auxiliary sys/oracle@stand Recovery Manager: Release 19.0.0.0.0 - Production on Mon Nov 8 16:29:37 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1613336523) connected to auxiliary database: ORCL (not mounted)
6—–> Run the below command,
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 'orcl','stand'
set db_name='orcl'
set db_unique_name='stand'
set
db_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradat
a/
stand/'
set
log_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/orada
ta/stand/'
set control_files='/u01/app/oracle/oradata/stand/standby1.ctl'
set log_archive_max_processes='5'
set fal_client='stand'
set fal_server='orcl'
set standby_file_management='auto'
set log_archive_config='dg_config=(orcl,stand)'
set compatible='19.0.0'
set memory_target='1200m'
nofilenamecheck;
}Output like below that,
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=62 device type=DISK
allocated channel: p2
channel p2: SID=24 device type=DISK
allocated channel: p3
channel p3: SID=78 device type=DISK
allocated channel: p4
channel p4: SID=61 device type=DISK
allocated channel: s1
channel s1: SID=37 device type=DISK
Starting Duplicate Db at 08-NOV-21
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwstand'
targetfile
'/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileorcl.ora' auxiliary format
'/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilestand.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilestand.ora''";
}
executing Memory Script
Starting backup at 08-NOV-21
Finished backup at 08-NOV-21
sql statement: alter system set spfile=
''/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilestand.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/stand/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=standXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set db_name =
''orcl'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''stand'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''/u01/app/oracle/oradata/ORCL/'', ''/u01/app/oracle/oradata/stand/'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/u01/app/oracle/oradata/ORCL/'', ''/u01/app/oracle/oradata/stand/'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/stand/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 =
''stand'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''orcl'' 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=(orcl,stand)'' comment=
'''' scope=spfile";
sql clone "alter system set compatible =
''19.0.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/stand/adump'' comment= ''''
scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=standXDB)'' comment= ''''
scope=spfile
sql statement: alter system set db_name = ''orcl'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''stand'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/ORCL/'',
''/u01/app/oracle/oradata/stand/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/ORCL/'',
''/u01/app/oracle/oradata/stand/'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/stand/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 = ''stand'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''orcl'' 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=(orcl,stand)'' comment= '''' scope=spfile
sql statement: alter system set compatible = ''19.0.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 738197504 bytes
Database Buffers 503316480 bytes
Redo Buffers 7880704 bytes
allocated channel: s1
channel s1: SID=35 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format
'/u01/app/oracle/oradata/stand/standby1.ctl';
}
executing Memory Script
Starting backup at 08-NOV-21
channel p1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_orcl.f
tag=TAG20211108T163937
channel p1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 08-NOV-21
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/stand/temp01.dbf";
set newname for tempfile 2 to
"/u01/app/oracle/oradata/stand/temp02.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/stand/system01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/stand/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/stand/undotbs01.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/stand/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/stand/system01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/stand/sysaux01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/stand/undotbs01.dbf" datafile
7 auxiliary format
"/u01/app/oracle/oradata/stand/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/stand/temp01.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/stand/temp02.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 08-NOV-21
channel p1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
channel p2: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
channel p3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel p4: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
output file name=/u01/app/oracle/oradata/stand/users01.dbf tag=TAG20211108T163951
channel p4: datafile copy complete, elapsed time: 00:00:56
output file name=/u01/app/oracle/oradata/stand/undotbs01.dbf tag=TAG20211108T163951
channel p3: datafile copy complete, elapsed time: 00:06:33
output file name=/u01/app/oracle/oradata/stand/system01.dbf tag=TAG20211108T163951
channel p2: datafile copy complete, elapsed time: 00:13:49
output file name=/u01/app/oracle/oradata/stand/sysaux01.dbf tag=TAG20211108T163951
channel p1: datafile copy complete, elapsed time: 00:14:10
Finished backup at 08-NOV-21
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=1088096063 file
name=/u01/app/oracle/oradata/stand/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1088096063 file
name=/u01/app/oracle/oradata/stand/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1088096063 file
name=/u01/app/oracle/oradata/stand/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1088096063 file
name=/u01/app/oracle/oradata/stand/users01.dbf
Finished Duplicate Db at 08-NOV-21
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1
RMAN>
step -6 : connect to the standby Database
[oracle@oracletest stand]$ export ORACLE_SID=stand [oracle@oracletest stand]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 8 17:03:57 2021 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 recover managed standby database disconnect nodelay; Database altered.
SQL> select NAME,OPEN_MODE,DATABASE_ROLE from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- ORCL MOUNTED PHYSICAL STANDBY SQL> select DB_UNIQUE_NAME from v$database; DB_UNIQUE_NAME ------------------------------ stand
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
