Oracle Data Guard Switchover via DGMGRL vs. #em12c

When you start to look at high availability and replication of data many people look at either Oracle Data Guard or Oracle GoldenGate.  Personally, I opt for Oracle GoldenGate; however, it is not always the best fit for smaller environments where cost is a concern.  When cost is a concern, Oracle Data Guard is a good choice and can be used with Enterprise (EE)  and Standard (SE) editions of Oracle Database.  There are multiple options for Oracle Data Guard, i.e. Redo Apply, SQL Apply and Active Data Guard.  All these options have its pros and cons.

Advice: I’ll say this up front; after configuring Oracle Data Guard from the command line (SQL) and from OEM 12c, I would take OEM 12c if I have the option.  Much easier, personal opinion.  I’ll write a post on this at some point.

In this post I want to cover the ways that Oracle Data Guard can switchover between Primary database and Physical Standby database from the command line and then from OEM12c. 

DGMRL – Command Line

Let’s take a look at how to perform a switch over from the command line.

In order to perform a switchover, I will be using the Oracle Data Guard Broker (DGMGRL).   With DGMGRL, I can manage the configuration of  Oracle Data Guard and provides an easier way to interact with the environment after it is setup. 

To start working with DGMGRL, first log into the standby database server.  Then execute DGMGRL and connect to the local database (Image 1).

Image 1:
image

Before doing anything with Oracle Data Guard, it is good to check the status of the configuration.  This can be done by using the SHOW CONFIGURATION command (Image 2).

Image 2:
image

In order to switch over to the standby database, I simply need to execute the SWITCHOVER TO <standby database> command (Image 3).

Image 3:
image

Once the switch over starts, the broker tries to switch everything over to db11g2tst.  During the switch over, I get an error on shutting down the original primary database and disconnects me from the databases.  Upon trying to look at the configuration, I see that the configuration is in ERROR status (Image 4).

Image 4:
image

From looking at this configuration problem, lets try restarted the database that it is complaining about and see if it clears the error. 

Image 5:
image

Now that the database has been restarted in mount mode, the DGMGRL is reporting that the switchover is in progress (Image 5).  With the database bounced and placed in mount mode, the switchover was able to complete successfully (Image 6).

Image 6:
image

Although, the switchover was successful, I had to intervene by rebooting the database that was becoming the new standby database.  Successful yes, but I’m not completely happy with the process.  Thought this was suppose to be hands free?  Let’s take a look at the OEM 12c approach.

OEM12c Approach

Taking a look at how to administer Oracle Data Guard from OEM12c, I will be using the broker again; this time from within OEM 12c. 

Note: The nice thing is that when you configure a standby database with OEM12c, the broker is setup for you.  Smile

To access the the broker items for Oracle Data Guard in OEM12c, I first have to go to the Database landing page (Targets –> Databases).  In Image 1, I’m looking for my test database (%tst%).  Once I have them listed, then I need to click on the database  that is the standby. 

Image 1:

image

After clicking on the standby database, OEM takes me to the landing page for that standby database.  Just as with any database, I see a set of menus under the database name (Image 2). 

Image 2:image

At this point, I want to use the Availability menu to access the Data Guard Administration Page (Image 3) (Availability –> Data Guard Administration).

Image 3:
image

Before OEM will take me to the Data Guard Administration page, I have to log into the standby database.  Since the standby database is in mount mode, the only way to log in is using the SYS AS SYSDBA user.  Image 4 shows that I have already setup a Named Credential for my standby database.

Image 4:image

Once logged in to the standby database, the Data Guard Administration page, provides you with a  lot of information pertaining to the Data Guard environment (Ima
ge 5).   

Image 5:

image

The important part on this page is the Standby Databases (Image 6) section.  This section provides all the information needed for the standby database.  In my example, I can quickly see the status of Data Guard, what role it is in, the last received and applied logs and the estimated time it will take to failover.

Image 6:

image

Now that I know what standby databases are available, I can quickly switchover to the standby selected by using the Switchover button (Image 7) in the Standby Databases section.

Image 7:

image

After clicking the Switchover button, OEM will ask you to log into both hosts that will partake in the switchover (not pictured).   Once logged into both hosts, a confirmation page for switching over is provided (Image 8).  At the time of this switchover, I have the option to swap monitoring settings as well (check box).  Since I want to swing everything over, I clicked the check box and then click the Yes button.

Image 8:

image

After clicking Yes, the switchover begins (Image 9).  I can monitor the status of the switchover from the output being displayed in OEM.

Image 9:image

Once the switchover is complete, OEM returns me to the Data Guard Administration page.  Where I can clearly see that everything was successful and that the new standby database is the old primary database (Image 10).

Image 10:image

Summary

I have showed two different ways of using the Data Guard Broker to perform a switch over.  Both methods are valid.  The main difference in the approaches is that OEM 12c approach took a few more steps due to the screen involved.  Where as the DGMGRL command line option I only had to run one (1) command to switch over.  In the end, everything switched over and I would leave it up to personal preference on which approach is used. 

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com

Please follow and like:
Comments
  • Hi,
    If you will configure your Data Guard Broker in proper way – it will be working fine from command line as well.
    You need to have DG Broker services registered in listeners and connect to database using sys user with password like
    DGMGRL> connect sys/@yourdatabase
    regards,
    Marcin

    • Marcin,
      You are correct; however, when I setup this test environment I set it up through OEM not the command line. Either OEM missed a step when it configured the databases (primary/standby) or something else went wrong. Ideally it should work from the command line as I mentioned and you have confirmed. I’m still looking into this issue and will update once I have a moment too.
      Thanks for the feedback.
      Bobby

Comments are closed.

Enquire now

Give us a call or fill in the form below and we will contact you. We endeavor to answer all inquiries within 24 hours on business days.