Huh, what is this I hear you ask? Part 3b? Oracle 12.1? Well, there’s a bit of a story to this post. Back in December 2016 I started to write a series of blog posts (part 1 | part 2 | part 3 | part 4) about how I created a standby database on RAC 12.1. For some reason I forgot to post this part. Up until now the step where I am creating the broker configuration was missing. Thanks to a friend I discovered my mistake. I decided to post this article to complete the series. There’s a pretty big logical gap that needed filling :)
I also deliberately decided against making changes to my notes, so this is written in the same style as the articles preceding this one. Also bear in mind that this is Oracle 12.1!
NOTE: As always, this is just a demonstration using VMs in my lab, based on my notes. Your system is most likely different, so in real-life you might take a different approach. The techniques I am using here were suitable for me, and my own small-scale testing. I tried to make sure they are valid, but you may want to allocate more resources in your environment. Test, test, test on your own environment on test kit first!
Here is the original blog post
In a last-minute decision I decided to split part 3 into half: first the database duplication, followed by the Data Guard configuration. It really got a bit too long (even for my standards), and would have put readers off the article. I appreciate your patience though…
What’s the situation?
At the end of part 3 I had a physical standby database ready for use, including its registration in the OCR. it was looking promising, but there was more work to do.
At the moment, the standby database is out of sync with the primary, a fact that needs to be rectified by creating a Data Guard configuration. I prefer to do this on the command line. If you are so inclined you can of course push the mouse around and do it with OEM.
Step 1: Data Guard Broker pre-requisites
Before creating the configuration you should change the location of the broker configuration files to shared storage. This needs to be done on each side – primary and every standby – before you can start the broker.
SQL> alter system set dg_broker_config_file1='+DATA/NCDBA/dr1NCDBA.dat' scope=both sid='*'; System altered. SQL> alter system set dg_broker_config_file2='+DATA/NCDBA/dr2NCDBA.dat' scope=both sid='*'; System altered. SQL> show parameter dg_broker NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file1 string +DATA/NCDBA/dr1NCDBA.dat dg_broker_config_file2 string +DATA/NCDBA/dr2NCDBA.dat dg_broker_start boolean FALSE SQL> alter system set dg_broker_start=true scope=both sid='*'; System altered. SQL>
I had to repeat the step on NCDBB, of course with adapted paths and file names.
Step 2: Creating a Data Guard Broker configuration
Once that’s done, you create the configuration using dgmgrl, the Data Guard Broker Manager Line Mode tool. I named my configuration RACTEST and added both databases. Here is an example of my session:
[oracle@rac12pri1 ~]$ dgmgrl DGMGRL for Linux: Version 220.127.116.11.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys@ncdba Password: Connected as SYSDBA. DGMGRL> create configuration ractest as primary database is 'NCDBA' connect identifier is 'NCDBA'; Configuration "ractest" created with primary database "NCDBA" DGMGRL> add database 'NCDBB' as connect identifier is 'NCDBB'; Database "NCDBB" added DGMGRL> show configuration Configuration - ractest Protection Mode: MaxPerformance Members: NCDBA - Primary database NCDBB - Physical standby database Fast-Start Failover: DISABLED Configuration Status: DISABLED
As a first step I connect as SYSDBA before creating the configuration. You can usually type help command to remind you of the exact syntax. After the configuration is created you can start adding (standby) databases. In my case there is only 1 – physical standby – database, but you can think of environments where you have more than one standby environment although that is less common. When specifying the connect identifier you use valid net*8 names found in the default tnsnames.ora (the one in the RDBMS home).
At this point it is important to set standby_file_management to auto. Otherwise managed recovery can abort and you will have to do extra work managing data files that can easily be avoided. The broker syntax to do so involves changing database properties (type show database verbose 'NCDBA' to show all of them). Many Data Guard broker properties are not easily mapped back to the corresponding initialisation parameter. In the case of standby_file_management it is not hard, and the documentation helps with the rest.
Eventually I enable the configuration and check the status. I have found that it can take a couple of minutes at times to get the SUCCESS message.
DGMGRL> edit database 'NCDBA' set property standbyfilemanagement=auto; Property "standbyfilemanagement" updated DGMGRL> edit database 'NCDBB' set property standbyfilemanagement=auto; Property "standbyfilemanagement" updated DGMGRL> enable configuration Enabled. DGMGRL> show configuration Configuration - ractest Protection Mode: MaxPerformance Members: NCDBA - Primary database NCDBB - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 21 seconds ago)
There are other things you must consider in accordance with your company’s (data protection/availability/business continuity/…) standards and other documentation – again my example is rather basic, but deliberately so. It’s my lab playground after all.
For the sake of completeness, here is the current configuration of NCDBA and NCDBB:
DGMGRL> show database 'NCDBA' Database - NCDBA Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): NCDBA1 NCDBA2 Database Status: SUCCESS DGMGRL> show database 'NCDBB'; Database - NCDBB Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) Average Apply Rate: 223.00 KByte/s Real Time Query: OFF Instance(s): NCDBB1 NCDBB2 (apply instance) Database Status: SUCCESS DGMGRL> exit
Please note that the broker detected the fact that my systems are cluster databases and lists both instances for each database. It also shows that NCDBB2 is the apply instance. I was positively surprised about the broker’s ability to detect both instances automatically. I seem to remember cases in earlier releases where said instance detection occasionally required a little nudging to get it right.
This example is deliberately kept as minimalist as possible: it works. Most likely it won’t satisfy your requirements. Review your Data Guard (and any other relevant) standards and change the configuration accordingly.
… back to present time :) This post completes the series, all my notes are now transcribed to the blog. I hope you find this useful.