Increasing the Dataguard Protection Level

I thought I understood testing. Before I run anything in my production environment, I’m utterly strict that I test in a non-production environment first. Does not matter where that change comes from, it is always run into test first. This naturally includes any changes at the database level, rather than just inside a particular schema.

When I have a set of instructions or steps to take the database from one particular environment, or to install a particular feature, I don’t tend to test just 1/2 the steps, but generally, if I have a sequence of steps I tend to test the entire sequence.

Recently, I’ve been working on a project to increase the protection level of a dataguard environment from Maximum Performance to Maximum Availability. This is a 10gR2 environment, so I pull up the 10gR2 dataguard documentation. To me, the steps seem pretty clear. Let me highlight step 1:

Step 1 If you are upgrading the protection mode, perform this step.

Perform this step only if you are upgrading the protection mode (for example, from maximum performance to maximum availability mode). Otherwise, go to Step 3.

Assume this example is upgrading the Data Guard configuration from the maximum performance mode to the maximum availability mode. Shut down the primary database and restart it in mounted mode:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

It’s clear right? To upgrade the protection level, you have got to shutdown the instance and have it in the mount mode. I would not just run this in production, I’d always want to test these steps in my test infrastructure that was the same environment as my production setup. Question is, would anyone test upgrading the protection level, but just skip this step? Would it really occur to someone, oh, I wonder if I can just skip this first step and keep my instance up and running?

It did not occur to me, but then I read the (excellent) Oracle Data Guard 11g Handbook by Larry Carpenter, et. al. It’s pretty explicit that you don’t need to shutdown your instance!

availability2

The above was run on a 10.2.0.4 instance. I’d already set the log_archive_dest_n to a LGWR SYNC mode. One thing to note, you must explicitly set AFFIRM here as well, it’s not good enough just using LGWR SYNC, as NOAFFIRM is the default and this leads to the protection_level being in continual resynchronization.

Resynchronization occurs when you first increase the protection_mode or when there is a network outage. It means your configuration is effectively at that point running in maximum performance, and while the protection_level is not at maximum availability the potential exists for data loss.

This really does contradict the documentation so this has been a really useful find for me, as just following the documentation would have led to me having to take downtime on my RAC cluster. It has always been the case that you can drop the protection level without incurring downtime. Note to go all the way to MAXIMUM PROTECTION still requires the database to be in the mounted state.

About these ads
Leave a comment

6 Comments

  1. Larry Carpenter

     /  November 2, 2009

    Hi,

    Well, as the book title says “Undocumented Best Practices and Real-World Techniques”, there will be things in the book that do not necessarily agree with the documentation because the manuals can’t be updated once the version releases. You have hit one of those which is why I made sure to make note of it in the book for the exact reason you stated – “as just following the documentation would have led to me having to take downtime on my RAC cluster”.

    This was something that was fixed in 10.2.0.3 I believe (it might have been 10.2.0.4) and in 11.1.0.6 but the we could not update the 10g manual and we missed that in the 11g manual. It has been fixed in the 11.2 manual.

    Also, as of 11.2 you can go directly to Maximum Protection without down time as long as your Primary was already at Maximum Availability, which just means you have to follow a two step process.

    Hope this helps. Glad you liked the book.

    Larry

    Reply
  2. jarneil

     /  November 2, 2009

    Hi Larry,

    Thanks for reading!

    I meant to say a big thanks for this information at OpenWorld, but did get the chance to question some of your people managing the Dataguard booth.

    Hope to see you in Birmingham!

    I never knew that the manuals could not be updated after release.

    Loved the book :-) (always thought there was a gap in market for an HA book). Full review coming! ;-)

    jason.

    Reply
  3. Larry Carpenter

     /  November 3, 2009

    Cool! I will be in Birmingham (UK) for UKOUG! See you there.

    Larry

    Reply
  4. Jagjeet Singh

     /  November 4, 2009

    Hi Jason,

    I am not able to recall properly, but somewhere I read if your standby has not been opened as read only
    then you do not need to shutdown … otherwise it’s required.

    Do you think you can check this out as well ?

    Regards,
    Jagjeet Singh

    Reply
  5. jarneil

     /  November 4, 2009

    Hello Jagjeet,

    I assume you mean if your doing a switchover/failover to a standby, then yes it does not need to be shutdown, if it’s not open read-only.

    jason.

    Reply
  6. Larry Carpenter

     /  November 4, 2009

    As of 11.1 it no longer necessary to shutdown the standby after a switchover even if it has been open read only since it was last started. In 10g you do need to bounce if the standby was open read only since it was started after you finish the switchover/failover.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 56 other followers

%d bloggers like this: