Data Guard made easy

Data Guard made easy

Sometimes I hear stories about struggles to setup Data Guard correctly. It should not be hard. There are lots of ways which lead to a correct setup. Die hards prefer the manual or own scripted way. Other people prefer the point and click method in Enterprise Manager. But one way, which is very easy, and I don’t hear people speaking a lot about it, is using the Database Creation Assistant (dbca).

Yes! You heard it right, as of Oracle 12.2 dbca can also create standby databases for you. That makes life extremely easy.

Especially in Cloud(like) or standardised environments, this method is a big time saver. In my lab all linux hosts are installed exactly the same, which is also the case in the Cloud and that makes this method also applicable if you do Iaas in the cloud.

I have created a primary database with Database Name cdbdemo. It has the instance (and also db unique name) cdbs02n01.
That database will get a standby database with db unique name (and instance) cdbs03n01.

Let’s dig into it.

Prerequisites

The prerequisites are of course the same as a normal Data Guard installation. Apart from that you don’t have to fiddle around too much in your primary database anymore.

I still have my Oracle Lab at home. This time we are playing around with single nodes, no Oracle restart, just simple plain nodes. The good thing is, since Oracle 18c, this approach also works for multitenant and RAC.

We have 5 prerequisites as the bare minimum:

  • The Oracle Home installed at both sides (Primary and standby)
  • A working listener on primary and standby side
  • Primary database must be in archivelog mode
  • Choose an appropriate logging level
  • Configure the standby redo logs ( can be done later as well if needed)

The Oracle Home installation is, since Oracle 18c, pretty straightforward. Download the binaries, unzip them in the appropriate location and run the installer. I use an OFA structure, but you can choose whatever you want.

I just use default listeners. But this is important that you have them on the primary and standby side. Mine looks pretty default:

The archivelogmode is still the same as for every Data Guard setup. Boot the database in mount mode and issue “Alter database archivelog;” and open the database. I also recommend to enable flashback, but this is not a hard requirement for Data Guard (it is a “yes please do it” serious recommendation).

In Oracle 18c we have different logging levels. For a normal standard Physical standby database, we still use the Force logging. More information about the supported logging methods can be found in the Official Documentation.
In short we have 3 logging methods:

  • FORCE LOGGING mode prevents any load operation from being performed in a nonlogged manner. This can slow down the load process because the loaded data must be copied into the redo logs.
  • STANDBY NOLOGGING FOR DATA AVAILABILITY mode causes the load operation to send the loaded data to each standby through its own connection to the standby. The commit is delayed until all the standbys have applied the data as part of running managed recovery in an Active Data Guard environment.
  • STANDBY NOLOGGING FOR LOAD PERFORMANCE is similar to the previous mode except that the loading process can stop sending the data to the standbys if the network cannot keep up with the speed at which data is being loaded to the primary. In this mode it is possible that the standbys may have missing data, but each standby automatically fetches the data from the primary as a normal part of running managed recovery in an Active Data Guard environment.

Standby redo logs are not “currently” needed, but in the end we need them for Data Guard to work properly. So I put this as a requirement instead of a recommendation. Just do it now. I’ll save you 5 minutes. This can be enhanced, so feel free (please do!) change it according needs but at least you have some standby redo logs on your primary after executing this:

There are variants for RAC, for the members, all kinds of sizing, but … at least this is a starting point. As said … adapt it. It is just to get you started.

As you have noticed, one of my personal changes it, that I already start the broker here. Just to have it ready already and save some time later on.

Creating the standby

As of now it is all really simple.

I created a small SQL script for tidying the very basic things up after the duplicate for standby:

Again … adapt this for your own environment. It’s just a start and not holy grail, but in my lab, it does the trick.

I usually prepare my tnsnames.ora files also before starting the duplicate. It not necessary for this method, but that way I cannot forget it. On primary and standby, make sure you have the same entries:

And then it is finally time to start the magic. On the standby server set your Oracle home to the 18c home and issue the dbca (you might need the toggle wrap button in the plugin for this code sample):

The thing which makes it a standby database is “createAsStandby”. More information about the dbca silent options and especially these interesting ones is in the 18c Documentation.

I would recommend to run this command in screen or vnc, because depending on the size of the database, it can take some time.
It gives a nice output to tell you what it is doing:

And you can see it is running also the custom scripts.

Finishing up

To finish up, we only need to create the broker configuration.

During the duplicate I have prepared a little script for the broker on the primary database:

This connects to the local (primary) database. Creates the broker configuration with the primary database as the local one.
After that it adds the standby database on the DR server and finally it enables my configuration.
The cool thing about this is, that you can script the broker and just call the script:

Always trust but verify. So I have the habit to show my configuration

Conclusion

Setting up Data Guard should not be a pain. All this was already possible in 12.2 for single instances and as of 18c this is also possible for RAC and Multitenant.

Of course, doing this, you should test. TEST! and Test. Test your switchovers, test your failover and be rude to it while testing. The next step can be to implement the observer and the fast start failover.

As always, questions, remarks? find me on twitter @vanpupi



Leave a Reply

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

4 + 7 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: