How to setup Data Guard Broker Configuration
DG Broker setup in Oracle
Source:
DB Version: 19.12.0.0.0
OS : Redhat Linux 7.6
Target :
DB Version: 19.12.0.0.0
OD: Oracle Linux 7.9
On Primary
==============
ARCHIVE LOG LIST (If No Archive Mode, do the following)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE FLASHBACK ON;
SELECT FORCE_LOGGING,FLASHBACK_ON FROM V$DATABASE;
ALTER DATABASE OPEN;
ALTER USER SYS IDENTIFIED BY <password>;
ALTER USER SYSDG ACCOUNT UNLOCK;
ALTER USER SYSDG IDENTIFIED BY <password>;
GRANT SYSDBA TO SYSDG;
SELECT * FROM V$PWFILE_USERS;
SELECT INSTANCE_NAME,STATUS,DATABASE_STATUS,STARTUP_TIME FROM V$INSTANCE;
SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME,CONTROLFILE_TYPE FROM V$DATABASE;
Gap between Primary and Secondary
==================================
SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#)
“Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
[oracle@DR_Srvr ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Thu Oct 20 11:29:17 2022
Version 19.12.0.0.0
Welcome to DGMGRL, type “help” for information.
Connected to “ORCLPDR”
Connected as SYSDG.
DGMGRL> show configuration
ORA-16525: The Oracle Data Guard broker is not yet available.
Configuration details cannot be determined by DGMGRL
DGMGRL>
on primary and secondary
============================
SQL> alter system set dg_broker_start=true scope=both;
System altered.
on primary
============
SQL> alter user sysdg identified by *****;
User altered.
SQL>
On primary
==============
dgmgrl SYS/*****@ORCLPDR
Primary_Srvr:~#dgmgrl SYS/*****@ORCLPDR
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Thu Oct 20 12:39:33 2022
Version 19.12.0.0.0
Welcome to DGMGRL, type “help” for information.
Connected to “PRMDB”
Connected as SYSDBA.
DGMGRL>
2) CREATE CONFIGURATION ORCLPDR_DGMGRL AS PRIMARY DATABASE IS PRMDB CONNECT IDENTIFIER IS ORCLPDR;
DGMGRL> CREATE CONFIGURATION ORCLPDR_DGMGRL AS PRIMARY DATABASE IS PRMDB CONNECT IDENTIFIER IS ORCLPDR;
Configuration “ORCLPDR_dgmgrl” created with primary database “PRMDB”
DGMGRL>
3 ) ADD DATABASE ORCLPDR AS CONNECT IDENTIFIER IS STDBY_ORCLPDR MAINTAINED AS PHYSICAL;
DGMGRL> ADD DATABASE ORCLPDR AS CONNECT IDENTIFIER IS STDBY_ORCLPDR MAINTAINED AS PHYSICAL;
Database “ORCLPDR” added
DGMGRL>
5) ENABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION;
Enabled.
6) SHOW CONFIGURATION VERBOSE
DGMGRL> SHOW CONFIGURATION VERBOSE
Configuration – ORCLPDR_dgmgrl
Protection Mode: MaxPerformance
Members:
PRMDB – Primary database
ORCLPDR – Physical standby database
Properties:
FastStartFailoverThreshold = ’30’
OperationTimeout = ’30’
TraceLevel = ‘USER’
FastStartFailoverLagLimit = ’30’
CommunicationTimeout = ‘180’
ObserverReconnect = ‘0’
FastStartFailoverAutoReinstate = ‘TRUE’
FastStartFailoverPmyShutdown = ‘TRUE’
BystandersFollowRoleChange = ‘ALL’
ObserverOverride = ‘FALSE’
ExternalDestination1 = ”
ExternalDestination2 = ”
PrimaryLostWriteAction = ‘CONTINUE’
ConfigurationWideServiceName = ‘ORCLPDR_CFG’
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS
7) SHOW DATABASE VERBOSE ORCLPDR
DGMGRL> SHOW DATABASE VERBOSE ORCLPDR
Database – ORCLPDR
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: 7.00 KByte/s
Active Apply Rate: 0 Byte/s
Maximum Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
ORCLPDR
Properties:
DGConnectIdentifier = ‘STDBY_ORCLPDR’
ObserverConnectIdentifier = ”
FastStartFailoverTarget = ”
PreferredObserverHosts = ”
LogShipping = ‘ON’
RedoRoutes = ”
LogXptMode = ‘ASYNC’
DelayMins = ‘0’
Binding = ‘OPTIONAL’
MaxFailure = ‘0’
ReopenSecs = ‘300’
NetTimeout = ’30’
RedoCompression = ‘DISABLE’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ‘0’
ApplyLagThreshold = ’30’
TransportLagThreshold = ’30’
TransportDisconnectedThreshold = ’30’
ApplyParallel = ‘AUTO’
ApplyInstances = ‘0’
StandbyFileManagement = ”
ArchiveLagTarget = ‘0’
LogArchiveMaxProcesses = ‘0’
LogArchiveMinSucceedDest = ‘0’
DataGuardSyncLatency = ‘0’
LogArchiveTrace = ‘0’
LogArchiveFormat = ”
DbFileNameConvert = ”
LogFileNameConvert = ”
ArchiveLocation = ”
AlternateLocation = ”
StandbyArchiveLocation = ”
StandbyAlternateLocation = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
LogXptStatus = ‘(monitor)’
SendQEntries = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘stdbysrvr’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stdbysrvr)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDR_DGMGRL)(INSTANCE_NAME=ORCLPDR)(SERVER=DEDICATED)))’
TopWaitEvents = ‘(monitor)’
SidName = ‘(monitor)’
Log file locations:
Alert log : /u01/app/odaorabase/oracle/diag/rdbms/ORCLPDR/ORCLPDR/trace/alert_ORCLPDR.log
Data Guard Broker log : /u01/app/odaorabase/oracle/diag/rdbms/ORCLPDR/ORCLPDR/trace/drcORCLPDR.log
Database Status:
SUCCESS
DGMGRL>
8) SHOW DATABASE VERBOSE ORCLPDR
DGMGRL> show configuration
Configuration – ORCLPDR_dgmgrl
Protection Mode: MaxPerformance
Members:
PRMDB – Primary database
ORCLPDR – Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 7 seconds ago)
DGMGRL> SHOW DATABASE VERBOSE ORCLPDR
Database – ORCLPDR
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
Active Apply Rate: 0 Byte/s
Maximum Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
ORCLPDR
Properties:
DGConnectIdentifier = ‘STDBY_ORCLPDR’
ObserverConnectIdentifier = ”
FastStartFailoverTarget = ”
PreferredObserverHosts = ”
LogShipping = ‘ON’
RedoRoutes = ”
LogXptMode = ‘ASYNC’
DelayMins = ‘0’
Binding = ‘OPTIONAL’
MaxFailure = ‘0’
ReopenSecs = ‘300’
NetTimeout = ’30’
RedoCompression = ‘DISABLE’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ‘0’
ApplyLagThreshold = ’30’
TransportLagThreshold = ’30’
TransportDisconnectedThreshold = ’30’
ApplyParallel = ‘AUTO’
ApplyInstances = ‘0’
StandbyFileManagement = ”
ArchiveLagTarget = ‘0’
LogArchiveMaxProcesses = ‘0’
LogArchiveMinSucceedDest = ‘0’
DataGuardSyncLatency = ‘0’
LogArchiveTrace = ‘0’
LogArchiveFormat = ”
DbFileNameConvert = ”
LogFileNameConvert = ”
ArchiveLocation = ”
AlternateLocation = ”
StandbyArchiveLocation = ”
StandbyAlternateLocation = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
LogXptStatus = ‘(monitor)’
SendQEntries = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘stdbysrvr’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stdbysrvr)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDR_DGMGRL) (INSTANCE_NAME=ORCLPDR)(SERVER=DEDICATED)))’
TopWaitEvents = ‘(monitor)’
SidName = ‘(monitor)’
Log file locations:
Alert log : /u01/app/odaorabase/oracle/diag/rdbms/ORCLPDR/ORCLPDR/trace/alert_ORCLPDR.log
Data Guard Broker log : /u01/app/odaorabase/oracle/diag/rdbms/ORCLPDR/ORCLPDR/trace/drcORCLPDR.log
Database Status:
SUCCESS
9) DGMGRL> validate database ORCLPDR
Database Role: Physical standby database
Primary Database: PRMDB
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
PRMDB: Off
ORCLPDR: Off
Managed by Clusterware:
PRMDB: YES
ORCLPDR: NO
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(PRMDB) (ORCLPDR)
1 3 2 Insufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(ORCLPDR) (PRMDB)
1 3 0 Insufficient SRLs
Warning: standby redo logs not configured for thread 1 on PRMDB
DGMGRL>
DGMGRL> validate database PRMDB;
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
PRMDB: Off
Managed by Clusterware:
PRMDB: YES
DGMGRL>
See also: