Oracle Data Guard Configuration

Introduction

Configuration of Primary database

Configuration of physical standby database

Oracle Data Guard Broker Configuration

Introduction

Oracle data guard is an integrated feature of oracle database software that enables us to implement a DR(disaster recovery) site for protection and high availability of enterprise data. It enables us to create one or more standby databases ,and manage/control the entire database ,including the production database.

A standby database is a synchronized copy of the primary database. And it is created from the primary/production database using either oracle data guard command-line  interface(DGMGRL) or a graphical user interface that is integrated in Oracle Enterprise Manager.

There are three types of standby databases:

  • Physical standby database
  • Logical standby database
  • Snapshot standby database

In this topic, we will  going to see a  step by step configuration and implementation of a physical standby database using DGMGRL.

Configuration of Primary database

First Install  oracle 12c database software and create a database  using database configuration assistant(DBCA).

After configuration let’s connect to the database and configure the following for data guard settings.

Enable FORCE LOGGING and ARCHIVING by making the database at mount stage

D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 3 11:02:05 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn sys/orcl@to_primary as sysdba
Connected to an idle instance.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 2499805184 bytes
Fixed Size 8749920 bytes
Variable Size 687869088 bytes
Database Buffers 1795162112 bytes
Redo Buffers 8024064 bytes
Database mounted.
SQL> Alter database ArchiveLog;

Database altered.

SQL> Alter database Force Logging;
Alter database Force Logging
Database altered.
SQL>

Set initialization parameters and create standby redo logs

While the database is on a  mount state, let’s create  Pfile  from SPfile. Because we can not directly edit on the Spfile parameter file.

SQL> create pfile from spfile='D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\database\SPFILEPRIMARY.ORA';

File created.

SQL>

Now, we have pfile created from spfile and then let’s  add the following  data guard initialization parameter settings in the pfile , INITPRIMARY.ORA, file .

*.db_unique_name='primary'
*.FAL_Client='to_primary'
*.fal_server='to_standby'
*.local_listener='LISTENER_PRIMARY'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.Log_archive_dest_1='Location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primary'
*.Log_archive_dest_2='Service=to_standby lgwr sync VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=standby'
*.Log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.Standby_File_Management='AUTO'

And after that let’s also create standby redo logfile for the standby database. And the standby redo logfile size should be the same to the primary redo logfile size.

SQL> Alter database add standby logfile 'D:\app\Admin\virtual\oradata\orcl\StandbyRedo01.log’ size 200m;

Database altered.

SQL> Alter database add standby logfile 'D:\app\Admin\virtual\oradata\orcl\StandbyRedo02.log’ size 200m;

Database altered.

SQL> Alter database add standby logfile 'D:\app\Admin\virtual\oradata\orcl\StandbyRedo03.log’ size 200m;

Database altered.

SQL>

Then shutdown the primary database and copy all the datafiles, standby redo logfiles from the primary database installation folder to the standby folder  like the pic below.

And again startup the primary database to mount  state and create standby controlfile for the standby database.

SQL> startup mount
Total System Global Area 2499805184 bytes
Fixed Size 8749920 bytes
Variable Size 687869088 bytes
Database Buffers 1795162112 bytes
Redo Buffers 8024064 bytes
Database mounted.
SQL> Alter database create standby controlfile as 'D:\app\Admin\virtual\oradata\orcl\standcontrol01.ctl';

Database altered.

SQL>

Then shutdown the database and copy the newly created control file to the same location where the standby redo logfiles is located and rename it  to  control01.ctl and  control02.ctl  on  the standby database side.

Copy also the  INITPRIMARY.ORA  and PWDprimary.ORA  parameter files to the standby database folder location. Once we coped the parameter files rename it to INITSTANDBY.ORA and PWDstandby.ORA respectively on the standby database.

And then startup the primary database from pfile and create spfile from it.

SQL> startup PFILE='D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\database\INITPRIMARY.ORA'
ORACLE instance started.

Total System Global Area 2499805184 bytes
Fixed Size 8749920 bytes
Variable Size 687869088 bytes
Database Buffers 1795162112 bytes
Redo Buffers 8024064 bytes
Database mounted.
Database opened.
SQL> Create spfile from pfile='D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\database\INITPRIMARY.ORA';

File created.

SQL>

Now, restart the primary database and create a service using net manager/net configuration assistant to establish connection  to the standby database.

Service creation through net manager

Contents of tnsnames.ora file on the primary database on my machine.        

TO_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

TO_PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

Configuration of physical standby database

To begin the configuration of oracle data guard on standby database, let’s Install oracle software with an option of software only on a separate location but on the same machine.

Physical standby database installation

After installing the software, add/modify the following  oracle data guard setting parameter files to the INITSTANDBY.ORA file located on the standby database installation folder.

*.db_unique_name='standby'
*.FAL_Client='to_standby'
*.FAL_Server='to_primary'
*.Log_archive_config='DG_CONFIG=(primary,standby)'
*.Log_archive_dest_1='Location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=standby'
*.Log_archive_dest_2='Service=to_primary lgwr sync VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=primary'
*.Log_archive_dest_state_1=ENABLE
*.Log_archive_dest_state_2=ENABLE
*.Standby_File_Management='AUTO'

And then let’s create an oracle database service instance for the standby database using ORADIM  ( oracle’s window utility).

C:\Windows\system32>ORADIM -NEW -SID standby -STARTMODE AUTO -PFILE 'C:\app\Admin\virtual\product\12.2.0\dbhome_1\database\INITSTANDBY.ORA'
Instance created.

Let’s also create net service name for both standby and primary databases  on the standby database side.

service naming through net manager

Contents of tnsnames.ora file on the standby database on my machine. 

LISTENER_DB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
)

TO_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

TO_PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

And then let’s connect to the standby database and create spfile from pfile.

SQL> conn sys/orcl@to_standby as sysdba
Connected to an idle instance.
SQL> startup pfile='C:\app\Admin\virtual\product\12.2.0\dbhome_1\database\INITSTANDBY.ORA' mount;
ORACLE instance started.

Total System Global Area 2499805184 bytes
Fixed Size 8749920 bytes
Variable Size 687869088 bytes
Database Buffers 1795162112 bytes
Redo Buffers 8024064 bytes
Database mounted.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\backup
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1

--Create spfile from pfile.

SQL> create spfile from pfile='C:\app\Admin\virtual\product\12.2.0\dbhome_1\database\INITSTANDBY.ORA'
2 ;

File created.

Then restart the standby database to mount state and also  start  the Redo Apply.

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount

Total System Global Area 2499805184 bytes
Fixed Size 8749920 bytes
Variable Size 687869088 bytes
Database Buffers 1795162112 bytes
Redo Buffers 8024064 bytes
SQL> alter database mount standby database;

Database altered.

-- Start Redo Apply

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> Alter Database Recover Managed Standby Database Disconnect;

Database altered.
SQL>


To check the role of the database, run the following query.

SQL> select dbid,database_role from v$database;

DBID DATABASE_ROLE
---------- ----------------
1623117495 PHYSICAL STANDBY

SQL>

Now , let’s connect to the primary database and check the status of standby archiving destination.

SQL> conn sys/orcl@to_primary as sysdba
Connected.
SQL> Select Status, Error from v$Archive_dest where dest_id=2;

STATUS ERROR
--------- -----------------------------------------------------------------
VALID

SQL>

 

Oracle Data Guard Broker Configuration

Let’s enable oracle Data Guard Broker on both primary and standby database.

--- on primary database;
SQL> Alter system set dg_broker_start=True scope=both;
System Altered

--- on standby database

SQL> Alter system set dg_broker_start=True scope=both;

System Altered

Then start oracle data guard command-line interface, DGMGRL, and connect through primary database and create broker configuration.

C:\Users\Admin>DGMGRL
DGMGRL for 64-bit Windows: Release 12.2.0.1.0 - Production on Mon Jan 31 11:48:22 2022

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/orcl@to_primary
Connected to "primary"
Connected as SYSDG.

---Create broker configuration

DGMGRL> create configuration 'BROKER_Config' as primary database is 'primary' connect identifier is to_primary;
Configuration "BROKER_Config" created with primary database "primary"
DGMGRL>


---Add Standby Database to the configuration

DGMGRL> Add database 'standby' as connect identifier is to_standby maintained as physical;
Database "standby" added
DGMGRL>

Once the oracle data guard broker configuration is created, show the data guard broker configuration by executing  ‘show configuration’  command and enable it.

DGMGRL> show configuration

Configuration - BROKER_Config

Protection Mode: MaxPerformance
Members:
primary - Primary database
standby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL>


--Enable the configuration ‘BROKER_Config’.

DGMGRL> enable configuration
Enabled.
DGMGRL>

DGMGRL> show configuration

Configuration - BROKER_Config

Protection Mode: MaxPerformance
Members:
primary - Primary database
standby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 42 seconds ago)

DGMGRL>

Finally, Oracle Data Guard Broker is configured and ready to switchover and failover between primary and standby database roles.

-- For switchover
DGMGRL> switchover to 'standby';

Primary Database Successfully converted to Physical Standby.

DGMGRL> switchover to 'primary';

Standby Database is successfully converted to Primary Database.

--- For Failover

DGMGRL> failover to 'standy';

Failover to standby succeeded.

DGMGRL> failover to 'primary';

Failover to primary succeeded.