
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:
1 2 3 4 5 6 7 8 9 10 11 12 |
[oracle@labvms03n01 ~]$ cat /u01/app/oracle/product/18.0.0/dbhome_1/network/admin/listener.ora listener.ora Network Configuration File: /u01/app/oracle/product/18.0.0/dbhome_1/network/admin/listener.ora Generated by Oracle configuration tools. LISTENER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = labvms03n01.labo.internal.stepi.net)(PORT = 1521)) ) ) [oracle@labvms03n01 ~]$ |
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:
1 2 3 4 5 6 7 8 9 10 |
BEGIN FOR srlno IN (select group# from v$log) LOOP DBMS_OUTPUT.PUT_LINE( 'This adds Standby redolog '||srlno.group#||' to your database;' ); execute immediate 'ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M'; END LOOP; DBMS_OUTPUT.PUT_LINE( 'Standby redologs added to your Database.'); execute immediate 'alter system set dg_broker_start=true scope=both'; END; / |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[oracle@labvms03n01 ~]$ cat postDg.sql connect / as sysdba set serveroutput on alter database flashback on; shutdown immediate; startup mount; alter system register; BEGIN FOR srlno IN (select group# from v$log) LOOP DBMS_OUTPUT.PUT_LINE( 'This adds Standby redolog '||srlno.group#||' to your database;' ); execute immediate 'ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M'; END LOOP; DBMS_OUTPUT.PUT_LINE( 'Standby redologs added to your Database.'); END; / alter system set dg_broker_start=true scope=both; exit [oracle@labvms03n01 ~]$ |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[oracle@labvms03n01 ~]$ cat /u01/app/oracle/product/18.0.0/dbhome_1/network/admin/tnsnames.ora CDBS02N01 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = labvms02n01.labo.internal.stepi.net)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDBS02N01.labo.internal.stepi.net) ) ) CDBS03N01 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = labvms03n01.labo.internal.stepi.net)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDBS03N01.labo.internal.stepi.net) ) ) [oracle@labvms03n01 ~]$ |
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):
1 |
dbca -silent -createDuplicateDB -primaryDBConnectionString labvms02n01.labo.internal.stepi.net:1521/CDBS02N01.labo.internal.stepi.net -gdbName cdbdemo.labo.internal.stepi.net -sid cdbs03n01 -initParams instance_name=cdbs03n01 -createAsStandby -customScripts /home/oracle/postDg.sql |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[oracle@labvms03n01 ~]$ dbca -silent -createDuplicateDB -primaryDBConnectionString labvms02n01.labo.internal.stepi.net:1521/CDBS02N01.labo.internal.stepi.net -gdbName cdbdemo.labo.internal.stepi.net -sid cdbs03n01 -initParams instance_name=cdbs03n01 -createAsStandby -customScripts /home/oracle/postDg.sql Enter SYS user password: Prepare for db operation 18% complete Listener config step 36% complete Auxiliary instance creation 55% complete RMAN duplicate 73% complete Post duplicate database operations 82% complete Running Custom Scripts 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdbdemo/cdbdemo.log" for further details. [oracle@labvms03n01 ~]$ |
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:
1 2 3 4 5 6 |
[oracle@labvms02n01 ~]$ cat create_broker_config.broker CONNECT / ; create configuration cdbdemo as primary database is cdbs02n01 connect identifier is cdbs02n01; add database cdbs03n01 as connect identifier is cdbs03n01 maintained as physical; enable configuration; [oracle@labvms02n01 ~]$ |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[oracle@labvms02n01 ~]$ dgmgrl @create_broker_config.broker DGMGRL for Linux: Release 18.0.0.0.0 - Production on Thu Mar 28 13:57:19 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "CDBS02N01" Connected as SYSDG. Configuration "cdbdemo" created with primary database "cdbs02n01" Database "cdbs03n01" added Enabled. [oracle@labvms02n01 ~]$ |
Always trust but verify. So I have the habit to show my configuration
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
[oracle@labvms02n01 ~]$ dgmgrl DGMGRL for Linux: Release 18.0.0.0.0 - Production on Thu Mar 28 14:00:01 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect / Connected to "CDBS02N01" Connected as SYSDG. DGMGRL> show configuration Configuration - cdbdemo Protection Mode: MaxPerformance Members: cdbs02n01 - Primary database cdbs03n01 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 53 seconds ago) DGMGRL> |
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