Written By Nassyam Basha
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.
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.
This section most of the content is based on 18.104.22.168 because of the few new features were introduced, Probably even in 22.214.171.124 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 126.96.36.199 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)
Like above we can get confirmation when the configuration was updated.
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
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
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
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
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
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 nameIn 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.
Configuration Status:SUCCESS (status updated 2 seconds ago)
DGMGRL> edit configuration rename to DELL_CKPT12c;Succeeded.DGMGRL> show configuration
Configuration - dell_ckpt12c
Configuration Status:SUCCESS (status updated 35 seconds ago)
After renaming the changes are immediately visible.
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 2015Archived Log entry 51 added for thread 1 sequence 36 ID 0x68ae0a33 dest 1:Thu Dec 31 04:59:07 2015alter database add logfile size 50mCompleted: alter database add logfile size 50m
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