Sunday , March 7 2021

How To Create Data Guard Broker Configuration

In this article, we will create data guard broker configuration. You can see the overview of data guard broker in the article named “What is Oracle Data Guard Broker“.

Our current data guard environment is as follows;

[Primary Side] [Physical Standby Side] [Logical Standby Side] Create Data Guard Broker Configuration

Step1:

Stop Redo Apply Services on Standby Database
[Physical-1]

[Standby-1]

[Standby-2]

[Physical-2]

The reason of ORA-16136: Managed Standby Recovery not active is that log apply services are running on one instance.
[Logical-1]

The reason of ORA-16283: operation not permitted on non-apply instance is that log apply services are running on one instance. If it is not running on Logical-1, it must be running on the other node.
[Logical-2]

[Logical-2] –>ALERT LOG

Step2:

Since I use RAC structure, I move Broker Configuration files to Shared area (ASM) where all instances can be read.
I want to create one to DATA and the other to FRA in terms of being a backup. That’s why I’m creating a directory in DATA and FRA.

Set parameters to new locations.

Start the DMON process and check the locations of the configuration files.

Apply these processes in the same way in Physical and Logical Standbys.

Step3:

Clear LOG_ARCHIVE_DEST_n parameters with SERVICE attribute in all instances.

I am running it in a single instance in RAC structure. With the sid = ‘*’ command, I make the parameter change in all instances.

Connect to DGMGRL from Primary Database and login with SYS user.

If we were using 12c, we would be logging in with the “sysdg” user. But there is no such user in 11g.
For this reason, we will do our transactions with the SYS user. We only have a SYS user in Password File.

In 12c, in addition to SYS, there are users such as SYSDG, SYSBACKUP for security reasons.

Step5:

Create Broker Configuration

Step6:

Add Physical Standby Database to the configuration.

Step7:

Add Logical Standby Database to the configuration.

Step8:

Make instance and database level checks before the broker configuration is enabled.

“Database:primary” => Indicates DB_UNIQUE_NAME.
“Role:PRIMARY” =>Indicates DB Role.
“Intended State:OFFLINE” => Indicates the status of Redo Transport services. OFFLINE indicates that there is no transport operation at the moment. The reason is that LOG_ARCHIVE_DEST_n parameters are deleted before Broker Configuration.
Instance(s):
primary1
primary2

Properties:
“DGConnectIdentifier = ‘primary'” => It searches for this name in the tnsnames.ORA file and tries to connect with that information.
ObserverConnectIdentifier = ”
LogXptMode = ‘ASYNC’ => Indicates how Redo Transport mode is. It can be SYNC, ASYNC or FASTSYNC.
DelayMins = ‘0’
Binding = ‘optional’ => Indicates whether the Redo destination is MANDATORY or OPTIONAL.
MANDATORY: = It must be written to that location. If it cannot be written, it stops the system from working because it will not overwrite ONLINE REDO LOG files.
MaxFailure = ‘0’ => It specifies the maximum number of sequential archive file errors before Redo Transport Services stops trying to transmit archive redo log files to the standby database.
MaxConnections = ‘1’ => It indicates how many processes an ARCH process will perform its operations in parallel.
ReopenSecs = ‘300’ => Specifies how many seconds after the ARCn process will try the next connect attempt for the destination where it fails to connect.
NetTimeout = ’30’ => Indicates that the LGWR process will continue its normal operation after how many seconds it does not receive feedback from Oracle Net Services.
RedoCompression = ‘DISABLE’ => It allows Redo to be sent by compression. Subject to an extra license. It makes tremendous compression.
LogShipping = ‘ON’ => It is the equivalent of the LOG_ARCHIVE_DEST_STATE_n parameter. ON = ENABLE, OFF = DEFER
PreferredApplyInstance = ”
ApplyInstanceTimeout = ‘0’
ApplyParallel = ‘AUTO’
LsbyASkipTxnCfgPr = ‘0,0,0’
LsbyDSkipTxnCfgPr = ‘0,0,0’
LsbyASkipCfgPr = ”
LsbyDSkipCfgPr = ”
LsbyASkipErrorCfgPr = ”
LsbyDSkipErrorCfgPr = ”
LsbyMaxEventsRecorded = ‘0’
LsbyPreserveCommitOrder = ”
LsbyRecordSkipErrors = ”
LsbyRecordSkipDdl = ”
LsbyRecordAppliedDdl = ”
StandbyFileManagement = ‘AUTO’
ArchiveLagTarget = ‘0’ => It specifies how many seconds the Log Switch operation will be performed without waiting for the Online Redo Log to fill.
LogArchiveMaxProcesses = ‘8’
LogArchiveMinSucceedDest = ‘1’
DbFileNameConvert = ‘standby, primary’
LogFileNameConvert = ‘standby, primary’
FastStartFailoverTarget = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
LsbyParameters = ‘(monitor)’
LsbySkipTxnTable = ‘(monitor)’
LsbySkipTable = ‘(monitor)’
LsbyFailedTxnInfo = ‘(monitor)’
ApplyLagThreshold = ‘0’ => Allows Apply Lag to throw a warning error above the specified threshold.
TransportLagThreshold = ‘0’ => Allows Transport Lag to throw a warning error above the specified threshold.
TransportDisconnectedThreshold = ’30’ => Throws a warning error when communication is broken for the time specified by the Primary Database of Standby database or Far Sync instances.
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*) => If it fails to send archives to the location specified in StandbyArchiveLocation, alternatively the location to send archives is specified.
LogArchiveTrace(*)
LogArchiveFormat(*)
LsbyMaxSga(*)
LsbyMaxServers(*)
TopWaitEvents(*)
(*) – Please check specific instance for the property value

Database Status:
DISABLED => Indicates that the Configuration is not enabled.

Step9:

Enable the configuration.

[Primary-1]–> ALERT LOG

[Standby-2] —> ALERT LOG

[Logical-2]—> ALERT LOG

From now on, we have to make all Parameter changes in the Data Guard Environment through the Broker. Otherwise the Broker may not work properly.

Step10:

Check the status of LOG_ARCHIVE_DEST_n parameters after the broker is ENABLED.

As you can see, LOG_ARCHIVE_DEST_2 and LOG_ARCHIVE_DEST_3 parameters, which were deleted before the Broker Configuration was created, were re-set with Default values. This is to enable Redo-Transport. It also offers us great convenience so that we can see the settings after the Broker Configuration is removed.

Step11:

Check everything is OK.

I stopped Redo-Apply before starting Broker Configurations, and then I saw that the dropped table was gone from Standby even though I never opened it. This is because while enabling Broker Configuration, it also launches Redo-Apply. We can see this with the following query.

Step12:

Configuration can be managed not only from the Primary Database, but from all instances in the Broker Environment. The reason is that the configuration file is in all Instances and the DMONs in the instances communicate with each other for the changes to be made and the files are updated.

About Onur ARDAHANLI

Leave a Reply

Your email address will not be published. Required fields are marked *