Step by Step Configuration Of Data Guard Broker in Oracle 12c
Description:-
- Data Guard broker is a centralized framework to manage entire Data Guard configuration through a client connection to any database in the configuration
- DGMGRL does not have the ability to create standby (GUI can do it). CLI is used mostly for configuration and management.
- Easy switchover/failover with one command thereby minimizing overall downtime associated with the planned/unplanned outage
- Instead of managing primary and standby databases with various SQL*Plus statements, broker provides a single unified configuration
- Broker keeps its configuration details in flat file. The files are stored at each database nodes in the Data Guard configuration.Additionally two copies of the configuration files are always stored on each database for redundancy.
Below parameters control where the configuration files will be stored.
DG_BROKER_CONFIG_FILE1 & DG_BROKER_CONFIG_FILE2
Step by Step to configure Oracle 12c Data Guard Physical Standby Here
Environment Details:-
Let’s Start the Demo:-
Step1:-Creating a Service Listener in primary and standby side(Both Side)
[oracle@primary dbs]$ cd /u01/app/oracle/product/12.1.0.2/db_1/network/admin/
[oracle@primary admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prime)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = prime)
)
(SID_DESC =
(GLOBAL_DBNAME = stand)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = stand)
)
(SID_DESC =
(GLOBAL_DBNAME = prime_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = prime)
)
(SID_DESC =
(GLOBAL_DBNAME = stand_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = stand)
)
)
After the changes in the listener file reload the listener
[oracle@primary dbs]$ lsnrctl stop
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 12-JUN-2018 08:16:51
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1539)))
The command completed successfully
[oracle@primary dbs]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 12-JUN-2018 08:16:53
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 12.1.0.2.0 – Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/primary/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1539)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1539)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 12-JUN-2018 08:16:53
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/primary/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1539)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service “prime” has 1 instance(s).
Instance “prime”, status UNKNOWN, has 1 handler(s) for this service…
Service “prime_DGMGRL” has 1 instance(s).
Instance “prime”, status UNKNOWN, has 1 handler(s) for this service…
Service “stand” has 1 instance(s).
Instance “stand”, status UNKNOWN, has 1 handler(s) for this service…
Service “stand_DGMGRL” has 1 instance(s).
Instance “stand”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
Step2:-Changing the parameters for both side
Primary
SQL> ALTER SYSTEM SET dg_broker_start=true scope=spfile;
System altered.
SQL> ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24)(PORT = 1539))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prime)))’;
System altered.
Standby
SQL> ALTER SYSTEM SET dg_broker_start=true scope=spfile;
System altered.
SQL>ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.25)(PORT = 1539))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stand)))’;
System altered.
Step3:- login in to the primary database through DGMGRL
[oracle@primary ~]$ export ORACLE_SID=prime
[oracle@primary ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 – 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys/oracle@prime
Connected as SYSDBA.
DGMGRL> show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
Step4:-Configure Dataguard Broker for primary
DGMGRL> create configuration ‘hari’ as primary database is ‘prime’ connect identifier is prime;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added Failed.
Fix for this issue
SQL> alter system set LOG_ARCHIVE_DEST_2= ‘ ‘; (both Side)
System altered.
DGMGRL> create configuration ‘hari’ as primary database is ‘prime’ connect identifier is prime;
Configuration “hari” created with primary database “prime”
DGMGRL> show configuration;
Configuration – hari
Protection Mode: MaxPerformance
Members:
prime – Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
Step5:-Configure Dataguard Broker for standby
DGMGRL> Add database ‘stand’ as connect identifier is stand maintained as physical;
Database “stand” added
DGMGRL> show configuration;
Configuration – hari
Protection Mode: MaxPerformance
Members:
prime – Primary database
stand – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
Step6:-Check using Verbose command
DGMGRL> show database verbose ‘stand’
Database – stand
Role: PHYSICAL STANDBY
Intended State: OFFLINE
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Active Apply Rate: (unknown)
Maximum Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
stand
Properties:
DGConnectIdentifier = ‘stand’
ObserverConnectIdentifier = ”
LogXptMode = ‘ASYNC’
RedoRoutes = ”
DelayMins = ‘0’
Binding = ‘optional’
MaxFailure = ‘0’
MaxConnections = ‘1’
ReopenSecs = ‘300’
NetTimeout = ’30’
RedoCompression = ‘DISABLE’
LogShipping = ‘ON’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ‘0’
ApplyLagThreshold = ‘0’
TransportLagThreshold = ‘0’
TransportDisconnectedThreshold = ’30’
ApplyParallel = ‘AUTO’
StandbyFileManagement = ‘AUTO’
ArchiveLagTarget = ‘0’
LogArchiveMaxProcesses = ‘5’
LogArchiveMinSucceedDest = ‘1’
DbFileNameConvert = ‘/u01/app/oracle/oradata/PRIME/datafile/, /u01/app/oracle/oradata/STAND/datafile/’
LogFileNameConvert = ‘/u01/app/oracle/oradata/PRIME/onlinelog/, /u01/app/oracle/oradata/STAND/onlinelog/’
FastStartFailoverTarget = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.25)(PORT=1539))(CONNECT_DATA=(SERVICE_NAME=stand_DGMGRL)(INSTANCE_NAME=stand)(SERVER=DEDICATED)))’
StandbyArchiveLocation = ‘USE_DB_RECOVERY_FILE_DEST’
AlternateLocation = ”
LogArchiveTrace = ‘0’
LogArchiveFormat = ‘%t_%s_%r.dbf’
TopWaitEvents = ‘(monitor)’
Database Status:
DISABLED
DGMGRL> show database verbose ‘prime’
Database – prime
Role: PRIMARY
Intended State: OFFLINE
Instance(s):
prime
Properties:
DGConnectIdentifier = ‘prime’
ObserverConnectIdentifier = ”
LogXptMode = ‘ASYNC’
RedoRoutes = ”
DelayMins = ‘0’
Binding = ‘optional’
MaxFailure = ‘0’
MaxConnections = ‘1’
ReopenSecs = ‘300’
NetTimeout = ’30’
RedoCompression = ‘DISABLE’
LogShipping = ‘ON’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ‘0’
ApplyLagThreshold = ‘0’
TransportLagThreshold = ‘0’
TransportDisconnectedThreshold = ’30’
ApplyParallel = ‘AUTO’
StandbyFileManagement = ‘AUTO’
ArchiveLagTarget = ‘0’
LogArchiveMaxProcesses = ‘4’
LogArchiveMinSucceedDest = ‘1’
DbFileNameConvert = ‘/u01/app/oracle/oradata/STAND/datafile, /u01/app/oracle/oradata/PRIME/datafile’
LogFileNameConvert = ‘/u01/app/oracle/oradata/STAND/onlinelog, /u01/app/oracle/oradata/PRIME/onlinelog’
FastStartFailoverTarget = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1539))(CONNECT_DATA=(SERVICE_NAME=PRIME_DGMGRL)(INSTANCE_NAME=prime)(SERVER=DEDICATED)))’
StandbyArchiveLocation = ‘USE_DB_RECOVERY_FILE_DEST’
AlternateLocation = ”
LogArchiveTrace = ‘0’
LogArchiveFormat = ‘%t_%s_%r.dbf’
TopWaitEvents = ‘(monitor)’
Database Status:
DISABLED
Step7:-enable the Dataguard broker
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration – hari
Protection Mode: MaxPerformance
Members:
prime – Primary database
stand – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 12 seconds ago)
DGMGRL> enable database ‘stand’;
Enabled.
DGMGRL> show database ‘stand’;
Database – stand
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 3.00 KByte/s
Real Time Query: OFF
Instance(s):
stand
Database Status:
SUCCESS
Successfully configured the Dataguard Broker in 12c database.
Reference:-
https://docs.oracle.com/database/121/DGBKR/concepts.htm#DGBKR001
Catch Me On:- Hariprasath Rajaram
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
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
