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;

 Database altered.

v. Check database log mode again

SQL> select log_mode from v$database;

 LOG_MODE

------------

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)

    )

  )

 Add Standby server TNS:

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!