Just for the Health of It

How to Configure Data Guard Using a Physical Standby Database

I recently decided to test out a rollback strategy that we were thinking of using while upgrading our databases to Oracle 11g. We were using Oracle 10g at the time and we had the following data guard set-up:

  • Primary DB,
  • Physical standby DB 1,
  • Physical standby DB 2,

NOTE: The operating system that we are running is Windows Server 2003, SP2. Although these instructions are for the Windows environment they will also work on any other environment, the only difference being some of the Windows specific configuration, such as Windows service creation.

The Plan For Upgrading Data Guard

The plan was to upgrade both data guard DBs to 11g at the same time as the primary. As part of the upgrade we needed to have a failback plan so that if the upgrade was to fail we would be able to restore the primary and standby databases back to a point in time before the upgrade took place. We didn’t want to have to rebuild the standby databases if the upgrade failed because that would be very time consuming and require a lot more work. The primary database rollback plan was relatively easy:

  • Shutdown the database
  • Take a “snap” of the LUNs at the SAN level
  • Bring the database up and run in the upgrade
  • Drop snap if upgrade successful
  • Rollback to the snap if the upgrade failed

Initial Questions?

The problem that we faced with the standby databases was that we didn’t have the ability to snap the disks on the SANs that they were running on because the SAN technology was older and we didn’t have the licenses for it. We also had some questions about the configuration of data guard and how it would work:

  • If we rollback the primary DB, how do we roll back the physical standby DBs if they have been applying logs during the upgrade?
  • Can we stop the standby DBs applying logs, rollback the primary DB to the snap version and start shipping logs again?
  • Will the physical standby DB pick up from where it left off if we remove the logs which were created while the upgrade was running?
  • If the primary DB was at archivelog sequence 100 at the time of the snap and it creates 50 logs throughout the upgrade process and we snap it back will it continue from sequence 100 again? kho lanh cong nghiep

We made educated guesses about exactly what we thought but the questions above should be able to give you some idea about the kinds of questions we were unsure of before we had completed any testing. The best way to prove anything is to actually test it out so I set out to test our theory. What was the theory? Well, we thought (I mean hoped… ) that if we cancelled the log shipping while the upgrade was running (DEFER the log archive destinations), rolled the primary DB back to the snap version of the disks, deleted all the logs which were produced while the upgrade was running and then enabled the log archive destinations again that the data guard physical standby databases would pick up from where they left off, none the wiser to the failed upgrade on the primary database.

What We are Going to Test

Here is a list of exactly what I will be walking through in the example below:

  • Configure data guard on version (although this will apply to 11g data guard, also)
  • Create the backup of your primary DB in preparation to restore it to create your physical standby databases
  • The restore process to set up your physical standby databases
  • How to get log shipping working from the primary to the standby databases
  • Configuring the rollback strategy above
  • Simulation of a failed upgrade attempt
  • Rolling back to the snap version of the database (at the SAN level)
  • A test to see if it is all working as was the case prior to the upgrade

SAN Vs Flashback Database – Personal View…

I should point out that for this test I have worked with the storage team to get a snapshot of the disk(s) before any upgrade work is complete. Personally, I find that this is a much better solution for this kind of work than using Oracle’s flashback technology for several reasons:

  1. Flashback requires a large amount of space to store any changes
  2. I have encountered issues when using Oracle flashback technology while performing an upgrade which corrupted my guaranteed restore point. very bad!
  3. The SAN snapshot is very quick, clean and requires no configuration changes from an Oracle point of view – just a clean shutdown of the DB while the snapshot is taken

So, in this test I show you how to create a standby DB from your primary DB with all of the commands that I needed to use. Hopefully it is all self-explanatory for a knowledgeable Oracle DBA. If not, please ask any questions at the bottom of the page in the comments section or drop me an email and I will do my best to help.

Leave a Reply

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