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).
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).
In order to switch over to the standby database, I simply need to execute the SWITCHOVER TO <standby database> command (Image 3).
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).
From looking at this configuration problem, lets try restarted the database that it is complaining about and see if it clears the error.
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).
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.
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.
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.
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).
At this point, I want to use the Availability menu to access the Data Guard Administration Page (Image 3) (Availability –> Data Guard Administration).
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.
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 (Image 5).
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.
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.
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.
After clicking Yes, the switchover begins (Image 9). I can monitor the status of the switchover from the output being displayed in OEM.
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).
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.