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.
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.
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.
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.