Written By

Introduction

This article explains to the Oracle users how easy to manage broker if you are friendly with the Broker utility where many Oracle users think it as lousy product andalso covers what are the new commands of broker in 12c and how it simplified DBA job when managing Data Guard with broker. 

Data Guard Broker

Of course broker is introduced from 9i but still even today users try to avoid using of it because managing with broker was not easy and the commands are not so friendly likewise SQL. But in fact Data Guard Broker is giving you more scope of information of commands by using “HELP” command rather than SQL mode. Moreover managing Data Guard configuration is very simple regardless of whether you have physical standby or logical standby or snapshot standby or FarSync instance of any protection modes. You no need to jump to DR site or primary site to check the configuration or for any monitoring purpose, you can connect to any location and no rules you must operate from only primary or only standby…such of comfort is only with Data Guard Broker.

Data Guard Broker 12c

This section most of the content is based on 12.1.0.2 because of the few new features were introduced, Probably even in 12.1.0.1 if you check for the status of the configuration then the result may be success or warning or error but are we sure is it the latest update? It was challenging to know when the configuration is updated. But from 12.1.0.2 we can see when the last configuration was updated of course using the same command, take a look below.


DGMGRL> show configuration

Configuration - ckpt12c

Protection Mode: MaxPerformance
Members:
canada - Primary database
canfar - Far sync instance
india - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 31 seconds ago)

DGMGRL>

Like above we can get confirmation when the configuration was updated.

Validation of Databases

Prior to 12c we have commands to check the configuration but you cannot verify or compare to primary and standby database if we are planning standby as future primary database, But from 12c one single command will give you tons of information of complete configuration of primary and standby database.
When you run the validation for standby then it compares the complete with primary and standby database such as primary database name, standby database name and the role of databases as well, when it comes to role transitions it clears whether the configuration is ready for the switchover or the standby is ready to perform failover or not. It does not gives information of only role transitions and role types but also gives information of database structure like how many temp files available in primary and how many are in standby, whether the flashback enabled on primary and standby and how many log files are available with status and how many standby redo log files available on standby with the current status is clearing or not. It also provides very critical information such as whether there is any corruption in the primary or standby.
For example to know what is the value of MaxConnections, then I have to consult both primary and standby but the new feature will give entire report which is readable and easily understandable format in compare mode. Now your question is how? Then you must take a look of below command and of output.
DGMGRL> validate database verbose india

Database Role: Physical standby database
Primary Database: canada

Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)

Capacity Information:
Database Instances Threads
canada 1 1
india 1 1

Temporary Tablespace File Information:
canada TEMP Files: 3
india TEMP Files: 3

Flashback Database Status:
canada: Off
india: Off

Data file Online Move in Progress:
canada: No
india: No

Standby Apply-Related Information:
Apply State: Running
Apply Lag: 0 seconds
Apply Delay: 0 minutes

Transport-Related Information:
Transport On: Yes
Gap Status: No Gap
Transport Lag: 0 seconds
Transport Status: Success

Log Files Cleared:
canada Standby Redo Log Files: Cleared
india Online Redo Log Files: Cleared

Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups
(canada) (india)
0 2 0
Warning: standby redo logs not configured for thread 2 on india

Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups
(india) (canada)
0 2 4

Future Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(india) (canada)
0 0 Bytes 50 MBytes

Apply-Related Property Settings:
Property canada Value india Value
DelayMins 0 0
ApplyParallel AUTO AUTO

Transport-Related Property Settings:
Property canada Value india Value
LogXptMode ASYNC ASYNC
Dependency <empty> <empty>
DelayMins 0 0
Binding optional optional
MaxFailure 0 0
MaxConnections 1 1
ReopenSecs 300 300
NetTimeout 30 30
RedoCompression DISABLE DISABLE
LogShipping ON ON

Automatic Diagnostic Repository Errors:
Error canada india
No logging operation NO NO
Control file corruptions NO NO
SRL Group Unavailable NO NO
System data file missing NO NO
System data file corrupted NO NO
System data file offline NO NO
User data file missing NO NO
User data file corrupted NO NO
User data file offline NO NO
Block Corruptions found NO NO

DGMGRL>

Dealing Far Sync with Broker

As we know farsync is introduced from 12cR1, the new attributes are introduced to configure from where to receive the redo and to which terminals have to send the redo. If we have multiple far sync instances for alternate purpose then to know the current redo routing we have to check from each instance for remote destinations, whereas broker will give complete details if we validate across the far sync instances. From the below output if we see the Farsync describes the redo source and also the redo destinations and the validate command also gives if the configuration is not proper.
Example: How it works?

DGMGRL> validate far_sync canfar
Member Role: Far Sync Instance
When Primary Is: canada

Active Redo Source: canada
Redo Destinations:
india

Thread # Online Redo Log Groups Standby Redo Log Groups Status
canada canfar
1 3 2 Insufficient SRLs

DGMGRL>

Apart from just verify, there are additional commands we can check what is the future status of FarSync instance if there in any switchover. This command applicable only for the Far Sync but not primary or standby databases.

DGMGRL> validate far_sync canfar when primary is canada
Member Role: Far Sync Instance
When Primary Is: canada

Active Redo Source: canada
Redo Destinations:
india

Thread # Online Redo Log Groups Standby Redo Log Groups Status
canada canfar
1 3 2 Insufficient SRLs

DGMGRL> validate far_sync canfar when primary is india
Member Role: Far Sync Instance
When Primary Is: india
Redo Destinations:
(none)

Thread # Online Redo Log Groups Standby Redo Log Groups Status
india canfar
1 3 2 Insufficient SRLs

DGMGRL>

Predict the Database Role

Above we have seen how the redo will be transported between Primary, Far Sync and standby databases, we can also predict what will be the role of the database. This will helps much when there are more than one standby databases part of the Data Guard, so that we can determine what will be the role of current primary if the future standby is standby1 or standby2.


DGMGRL> show configuration when primary is canada

Configuration when canada is primary - ckpt12c

Members:
canada - Primary database
canfar - Far sync instance
india - Physical standby database
india - Physical standby database (alternate of canfar)

DGMGRL> show configuration when primary is india

Configuration when india is primary - ckpt12c

Members:
india - Primary database
canada - Physical standby database

Members Not Receiving Redo:
canfar - Far sync instance

DGMGRL>

From the above example If the India database becomes primary then Far Sync instance canfar will not receive the redo, it is because no redo routing configured from standby database india to the far sync. Based on the output we can update/edit the properties to have good configuration even now and after the switchover.
Changing the configuration name
In some circumstances, we might have to change the configuration name. In order to do in prior versions we have to remove the configuration and then create again. Now it is more simpler, we can rename the configuration with a single command without harming the current configuration.

DGMGRL> show configuration

Configuration - ckpt12c

Protection Mode: MaxPerformance
Members:
canada - Primary database
canfar - Far sync instance
india - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 2 seconds ago)

DGMGRL> edit configuration rename to DELL_CKPT12c;
Succeeded.
DGMGRL> show configuration

Configuration - dell_ckpt12c

Protection Mode: MaxPerformance
Members:
canada - Primary database
canfar - Far sync instance
india - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 35 seconds ago)

DGMGRL>

After renaming the changes are immediately visible.

Support of SQL

Prior to 12c we are not allowed to run any sql commands from the Broker, this is same as how the sql statements we can run now from RMAN prompt without disconnecting the current session.


DGMGRL> sql "alter database add logfile size 50m";
Succeeded.
DGMGRL>

Thu Dec 31 04:51:20 2015
Archived Log entry 51 added for thread 1 sequence 36 ID 0x68ae0a33 dest 1:
Thu Dec 31 04:59:07 2015
alter database add logfile size 50m
Completed: alter database add logfile size 50m

Summary

We’ve seen go through with the Broker new features and how easy to validate the configuration and verifying the configuration of far sync instance. Apart from that we have also seen how to predict the database role changes after the switchover/Role transitions and few other broker features

References

http://docs.oracle.com/database/121/DGBKR/release_changes.htm#DGBKR3847