Oracle Data Guard Multiple Standby Databases 12c

Oracle Data Guard provides enterprise data with high availability, data security, and disaster recovery. Oracle Data Guard is a suite of services that allows production Oracle databases to withstand calamities and data corruptions by creating, maintaining, managing, and monitoring one or more backup databases.

These standby databases are kept as duplicates of the production database by Oracle Data Guard. Then, if indeed the production database is uncertain due to a scheduled or unexpected outage, Oracle Data Guard may convert any standby database towards the production role, reducing the outage’s unavailability. Traditional backup, restore, and clustering approaches is used with Oracle Data Guard to give a high level of data security and availability.

How to Create Oracle Data Guard Multiple Standby Databases 12c

Here we will configure two data guard at time with production:

A single instance database is used in this scenario.

Details about the environment:-

Configurations on the server’s Production side:-

Step 1:- Set Archivelog and Force Logging modes

[oracle@dev12c ~]$ export ORACLE_SID=Prod
[oracle@dev12c ~]$ sqlplus / as sysdba
SQL> startup mount
SQL> alter database archivelog;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> alter database open;
SQL> select FORCE_LOGGING,log_mode from v$database;

Step 2:- Configure the network entry on the primary and both Standby sides (Both servers)

Tnsnames entry:-

**************

Prod =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.111)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Prod)
)
)
DR1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.112)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DR1)
)
)
DR2=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.113)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DR2)
)
)

Listener Entry:-

************

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1/)
(SID_NAME = PROD)
)
(SID_DESC =
(GLOBAL_DBNAME = DR1)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1/)
(SID_NAME = DR1)
)
(SID_DESC =
(GLOBAL_DBNAME = DR2)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1/)
(SID_NAME = DR2)
)
)

Step3:- Changing parameters in the primary database

SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(PROD,DR1,DR2)’ 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=PROD’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_2=’service=DR1 async valid_for=(online_logfiles,primary_role) db_unique_name=DR1’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_3=’service=DR2 async valid_for=(online_logfiles,primary_role) db_unique_name=DR2’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=both;

System altered.

You have to set parameters like this in production. Later follow the steps at both the standby server as we do for single standby database creation.

Pros of Oracle Data Guard in Brief

Oracle Data Guard provides the following advantages:

High availability, disaster recovery, and data security

Oracle Data Guard is a disaster recovery and high availability solution that is both quick and comprehensive. Switchover and failover features that are simple to operate permit role changes of direction between primary and backup databases, reducing primary database downtime for scheduled and unforeseen failures.

Complete data security

Even in the event of a disaster, Oracle Data Guard can assure that no data is lost. A standby database protects against all forms of unanticipated outages, such as data corruption and administrative mistake. Physical corruptions that can occur at a primary database are not transmitted to the standby database because the redo data received from the main database is checked at a standby database. Logic intra-block corruptions and lost-write corruptions are also prevented from spreading to the standby database by additional validation done at the standby database.

Similarly, administrative failures such as a storage administrator’s unintentional file deletions are not transferred to a standby database. A physical standby database may also be used to defend against user mistakes by postponing the redo application or by rewinding the standby and extracting a valid copy of data via Flashback Database.

Optimal use of system resources

The standby database tables that are updated with redo data from the primary database may be utilized for other operations like backups, reports, summations, and queries, lowering the stress on the primary database and conserving critical CPU and I/O cycles.

Data protection flexibility to balance availability and performance needs

Oracle Data Guard provides maximum protection, maximum availability, and maximum performance modes to assist businesses in balancing data availability with system performance needs.

Gap identification and closure is done automatically

If the primary database and one or more standby databases lose connectivity (for example, owing to network difficulties), redo data created on the primary database cannot be transferred to the backup databases. The missing archived redo log files (referred to as a gap) are immediately recognized by Oracle Data Guard once a connection is regained, and the missing archived redo log files are automatically sent to the standby databases. Without the DBA’s assistance, the standby databases are synced with the primary database.

Management that is centralized and straightforward

In an Oracle Data Guard setup, the Oracle Data Guard broker provides a graphical user interface and a command-line interface for automating administrative and operational operations across several databases. In addition, the broker keeps track of all the systems in a single Oracle Data Guard configuration.

Database integration using Oracle

Oracle Data Guard is included with Oracle Database Enterprise Edition and does not need to be installed separately.

Transitions between roles are made automatically

When fast-start failover is enabled, the Oracle Data Guard broker instantly switches to a synchronized standby site in the case of a primary site disaster, without the need for DBA interaction. In addition, the position shift is immediately informed to apps.

AuthorityHunter.com covered informative topics. Learn more about oracle here.