Description:-
In this article we are going to see step by Step to configure Oracle 19c Data Guard Physical Standby on oracle VM Cloud servers
The environment is single instance database.
Below link to complete oracle 19c step by step creating oracle 19c dataguard
Step:-1 Oracle Cloud-Build VM Machine For Oracle 19c Dataguard Configuration
Step:-2 Oracle Cloud-Preparing To Create A Oracle 19c Dataguard
Step:-3 Oracle Cloud-Deploying Oracle 19c Data Guard Physical Standby
Environment Details:-
Primary Server side Configurations:-
Step1:-Change Archivelog mode and force logging mode
SQL> startup mount
ORACLE instance started.
Total System Global Area 4966053832 bytes
Fixed Size 8906696 bytes
Variable Size 889192448 bytes
Database Buffers 4060086272 bytes
Redo Buffers 7868416 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/DB19C/redo04.log’ size 200m;
Database altered.
SQL> alter database add standby logfile group 5 ‘/u01/app/oracle/oradata/DB19C/redo05.log’ size 200m;
Database altered.
SQL> alter database add standby logfile group 6 ‘/u01/app/oracle/oradata/DB19C/redo06.log’ size 200m;
Database altered.
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
Step3:-Adding the network entry in primary and standby side(Both servers)
DB19C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod121.subnet.vcn.oraclevcn.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db19c)
)
)
DB19Cstby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod122.subnet.vcn.oraclevcn.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db19cstby)
)
)
Standby side listener
[oracle@prod122 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db19c)
(ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
(SID_NAME = db19c)
)
(SID_DESC =
(GLOBAL_DBNAME = db19cstby)
(ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
(SID_NAME=db19cstby)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod122.subnet.vcn.oraclevcn.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
Output like the below
[oracle@prod121 admin]$ tnsping db19c
TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 13-NOV-2019 15:33:12
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prod121.subnet.vcn.oraclevcn.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db19c)))
OK (10 msec)
[oracle@prod121 admin]$ tnsping db19cstby
TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 13-NOV-2019 15:33:16
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prod122.subnet.vcn.oraclevcn.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db19cstby)))
OK (10 msec)
Step4:-Changing parameters in primary database
SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(db19c,db19cstby)’ 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=db19c’ SCOPE=both;
System altered.
SQL> ALTER SYSTEM SET log_archive_dest_2=’service=db19cstby async valid_for=(online_logfiles,primary_role) db_unique_name=db19cstby’ SCOPE=both;
System altered.
SQL> ALTER SYSTEM SET fal_server=’db19cstby’ SCOPE=both;
System altered.
SQL> ALTER SYSTEM SET fal_client=’db19c’ SCOPE=both;
System altered.
SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=both;
System altered.
Standby Server side Configurations:-
Step:-1 Create a password file on standby side
orapwd file=orapwdb19c password=orac#123 entries=10
Step:-2 Changing parameters in standby database
In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initdb19cstby.ora
Containing a single parameter: DB_NAME=db19c
[oracle@prod122 dbs]$ cat initdb19cstby.ora
db_name=db19c
[oracle@prod122 dbs]$ pwd
/u01/app/oracle/product/19c/dbhome_1/dbs
Step:-3 Create directory Structure in Standby database
[oracle@prod122 ~]$ cd $ORACLE_BASE/admin/
[oracle@prod122 admin]$ mkdir -p /u01/app/oracle/admin/db19cstby/adump
[oracle@prod122 admin]$ mkdir -p /u01/app/oracle/fast_recovery_area
Step:-4 Start the standby database using pfile
[oracle@prod122 ~]$ cd $ORACLE_HOME/dbs
[oracle@prod122 dbs]$ echo $ORACLE_SID
db19cstby
[oracle@prod122 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Wed Nov 13 16:02:41 2019
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=’initdb19cstby.ora’ nomount
ORACLE instance started.
Total System Global Area 306183456 bytes
Fixed Size 8895776 bytes
Variable Size 239075328 bytes
Database Buffers 50331648 bytes
Redo Buffers 7880704 bytes
SQL>
Step:5 connect to the rman and execute the rman script
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 ‘db19c’,’db19cstby’
set db_name=’db19c’
set db_unique_name=’db19cstby’
set db_file_name_convert=’/u01/app/oracle/oradata/DB19C’,’/u01/app/oracle/oradata/DB19CSTBY’
set log_file_name_convert=’/u01/app/oracle/oradata/DB19C’,’/u01/app/oracle/oradata/DB19CSTBY’
set control_files=’/u01/app/oracle/oradata/DB19CSTBY/standby1.ctl’
set log_archive_max_processes=’5′
set fal_client=’db19cstby’
set fal_server=’db19c’
set standby_file_management=’AUTO’
set log_archive_config=’dg_config=(db19c,db19cstby)’
set compatible=’19.0.0.0.0′
set memory_target=’6420m’
nofilenamecheck;
}
[oracle@prod122 dbs]$ rman target sys/orac#123@db19c auxiliary sys/orac#123@db19cstby
Recovery Manager: Release 19.0.0.0.0 – Production on Wed Nov 13 17:09:17 2019
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.02.00.00 in TARGET database is not current
PL/SQL package SYS.DBMS_RCVMAN version 19.02.00.00 in TARGET database is not current
connected to target database: DB19C (DBID=2152193899)
connected to auxiliary database: DB19C (not mounted)
RMAN> run
2> {
allocate channel p1 type disk;
3> allocate channel p2 type disk;
allocate channel p3 type disk;
4> 5> 6> allocate channel p4 type disk;
7> allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database
8> spfile
parameter_value_convert ‘db19c’,’db19cstby’
set db_name=’db19c’
9> set db_unique_name=’db19cstby’
10> 11> 12> set db_file_name_convert=’/u01/app/oracle/oradata/DB19C’,’/u01/app/oracle/oradata/DB19CSTBY’
set log_file_name_convert=’/u01/app/oracle/oradata/DB19C’,’/u01/app/oracle/oradata/DB19CSTBY’
13> 14> set control_files=’/u01/app/oracle/oradata/DB19CSTBY/standby1.ctl’
set log_archive_max_processes=’5′
15> set fal_client=’db19cstby’
16> 17> 18> set fal_server=’db19c’
set standby_file_management=’AUTO’
19> set log_archive_config=’dg_config=(db19c,db19cstby)’
20> 21> set compatible=’19.0.0.0.0′
set memory_target=’6420m’
nofilenamecheck;
22> 23> 24> }
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=273 device type=DISK
allocated channel: p2
channel p2: SID=400 device type=DISK
allocated channel: p3
channel p3: SID=23 device type=DISK
allocated channel: p4
channel p4: SID=145 device type=DISK
allocated channel: s1
channel s1: SID=150 device type=DISK
Starting Duplicate Db at 13-NOV-19
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format ‘/u01/app/oracle/product/19c/dbhome_1/dbs/orapwdb19cstby’ targetfile
‘/u01/app/oracle/product/19c/dbhome_1/dbs/spfiledb19c.ora’ auxiliary format
‘/u01/app/oracle/product/19c/dbhome_1/dbs/spfiledb19cstby.ora’ ;
sql clone “alter system set spfile= ”/u01/app/oracle/product/19c/dbhome_1/dbs/spfiledb19cstby.ora””;
}
executing Memory Script
Starting backup at 13-NOV-19
Finished backup at 13-NOV-19
sql statement: alter system set spfile= ”/u01/app/oracle/product/19c/dbhome_1/dbs/spfiledb19cstby.ora”
contents of Memory Script:
{
sql clone “alter system set audit_file_dest =
”/u01/app/oracle/admin/db19cstby/adump” comment=
”” scope=spfile”;
sql clone “alter system set dispatchers =
”(PROTOCOL=TCP) (SERVICE=db19cstbyXDB)” comment=
”” scope=spfile”;
sql clone “alter system set db_name =
”db19c” comment=
”” scope=spfile”;
sql clone “alter system set db_unique_name =
”db19cstby” comment=
”” scope=spfile”;
sql clone “alter system set db_file_name_convert =
”/u01/app/oracle/oradata/DB19C”, ”/u01/app/oracle/oradata/DB19CSTBY” comment=
”” scope=spfile”;
sql clone “alter system set log_file_name_convert =
”/u01/app/oracle/oradata/DB19C”, ”/u01/app/oracle/oradata/DB19CSTBY” comment=
”” scope=spfile”;
sql clone “alter system set control_files =
”/u01/app/oracle/oradata/DB19CSTBY/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 =
”db19cstby” comment=
”” scope=spfile”;
sql clone “alter system set fal_server =
”db19c” 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=(db19c,db19cstby)” comment=
”” scope=spfile”;
sql clone “alter system set compatible =
”19.0.0.0.0” comment=
”” scope=spfile”;
sql clone “alter system set memory_target =
6420m comment=
”” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ”/u01/app/oracle/admin/db19cstby/adump” comment= ”” scope=spfile
sql statement: alter system set dispatchers = ”(PROTOCOL=TCP) (SERVICE=db19cstbyXDB)” comment= ”” scope=spfile
sql statement: alter system set db_name = ”db19c” comment= ”” scope=spfile
sql statement: alter system set db_unique_name = ”db19cstby” comment= ”” scope=spfile
sql statement: alter system set db_file_name_convert = ”/u01/app/oracle/oradata/DB19C”, ”/u01/app/oracle/oradata/DB19CSTBY” comment= ”” scope=spfile
sql statement: alter system set log_file_name_convert = ”/u01/app/oracle/oradata/DB19C”, ”/u01/app/oracle/oradata/DB19CSTBY” comment= ”” scope=spfile
sql statement: alter system set control_files = ”/u01/app/oracle/oradata/DB19CSTBY/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 = ”db19cstby” comment= ”” scope=spfile
sql statement: alter system set fal_server = ”db19c” 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=(db19c,db19cstby)” comment= ”” scope=spfile
sql statement: alter system set compatible = ”19.0.0.0.0” comment= ”” scope=spfile
sql statement: alter system set memory_target = 6420m comment= ”” scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 6744439736 bytes
Fixed Size 8911800 bytes
Variable Size 2667577344 bytes
Database Buffers 4060086272 bytes
Redo Buffers 7864320 bytes
allocated channel: s1
channel s1: SID=135 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/u01/app/oracle/oradata/DB19CSTBY/standby1.ctl’;
}
executing Memory Script
Starting backup at 13-NOV-19
channel p1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/19c/dbhome_1/dbs/snapcf_db19c.f tag=TAG20191113T171007
channel p1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-NOV-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/DB19CSTBY/temp01.dbf”;
switch clone tempfile all;
set newname for datafile 1 to
“/u01/app/oracle/oradata/DB19CSTBY/system01.dbf”;
set newname for datafile 3 to
“/u01/app/oracle/oradata/DB19CSTBY/sysaux01.dbf”;
set newname for datafile 4 to
“/u01/app/oracle/oradata/DB19CSTBY/undotbs01.dbf”;
set newname for datafile 7 to
“/u01/app/oracle/oradata/DB19CSTBY/users01.dbf”;
backup as copy reuse
datafile 1 auxiliary format
“/u01/app/oracle/oradata/DB19CSTBY/system01.dbf” datafile
3 auxiliary format
“/u01/app/oracle/oradata/DB19CSTBY/sysaux01.dbf” datafile
4 auxiliary format
“/u01/app/oracle/oradata/DB19CSTBY/undotbs01.dbf” datafile
7 auxiliary format
“/u01/app/oracle/oradata/DB19CSTBY/users01.dbf” ;
sql ‘alter system archive log current’;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/DB19CSTBY/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 13-NOV-19
channel p1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/DB19C/system01.dbf
channel p2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/DB19C/sysaux01.dbf
channel p3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/DB19C/undotbs01.dbf
channel p4: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/DB19C/users01.dbf
output file name=/u01/app/oracle/oradata/DB19CSTBY/users01.dbf tag=TAG20191113T171013
channel p4: datafile copy complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/DB19CSTBY/undotbs01.dbf tag=TAG20191113T171013
channel p3: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/DB19CSTBY/system01.dbf tag=TAG20191113T171013
channel p1: datafile copy complete, elapsed time: 00:00:15
output file name=/u01/app/oracle/oradata/DB19CSTBY/sysaux01.dbf tag=TAG20191113T171013
channel p2: datafile copy complete, elapsed time: 00:00:15
Finished backup at 13-NOV-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=1024247429 file name=/u01/app/oracle/oradata/DB19CSTBY/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1024247429 file name=/u01/app/oracle/oradata/DB19CSTBY/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1024247429 file name=/u01/app/oracle/oradata/DB19CSTBY/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1024247429 file name=/u01/app/oracle/oradata/DB19CSTBY/users01.dbf
Finished Duplicate Db at 13-NOV-19
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1
RMAN>
Step:-6 Connect to standby database
SQL> select open_mode,instance_name,database_role from v$database,v$instance;
OPEN_MODE INSTANCE_NAME DATABASE_ROLE
——————– —————- —————-
MOUNTED db19cstby PHYSICAL STANDBY
Primary side
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
Step:-7 Enable MRP process
SQL> alter database recover managed standby database disconnect nodelay;
Database altered.
Step:8 Query to check status
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 5 5 0
Step:-9 Double check in alertlog from standby side too
PR00 (PID:4219): Media Recovery Log /u01/app/oracle/fast_recovery_area/DB19CSTBY/archivelog/2019_11_13/o1_mf_1_4_gwrh7d2v_.arc
2019-11-13T17:27:09.248402+00:00
PR00 (PID:4219): Media Recovery Log /u01/app/oracle/fast_recovery_area/DB19CSTBY/archivelog/2019_11_13/o1_mf_1_5_gwrh7d2p_.arc
PR00 (PID:4219): Media Recovery Waiting for T-1.S-6 (in transit)
2019-11-13T17:27:09.364803+00:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 6 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/DB19CSTBY/redo04.log
2019-11-13T17:36:03.410536+00:00
PR00 (PID:4219): Media Recovery Waiting for T-1.S-7
2019-11-13T17:36:03.448789+00:00
rfs (PID:4838): Primary database is in MAXIMUM PERFORMANCE mode
rfs (PID:4838): Re-archiving LNO:4 T-1.S-6
2019-11-13T17:36:03.469524+00:00
rfs (PID:4838): Selected LNO:5 for T-1.S-7 dbid 2152193899 branch 1024162733
2019-11-13T17:36:04.419608+00:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 7 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/DB19CSTBY/redo05.log
Connect with me:-
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/hariprasathdba
