One of the advantages of standby-first patch apply, is that I can test the patches in a production-like environment (the standby) before applying them to the primary. Should I find any issues with the patches, I can stop the process and avoid impacting the primary database.

Here’s an overview of the process.

For demo purposes, my Data Guard configuration consists of two databases:

• SID: SALES
• Databases: SALES_COPENHAGEN and SALES_AARHUS
• Hosts: copenhagen and aarhus
• Primary database: SALES_COPENHAGEN running on copenhagen

How To

This procedure starts right after I’ve patched the standby (SALES_AARHUS). It runs out of the target Oracle home, whereas the primary database (SALES_COPENHAGEN) still runs on the source Oracle home.

• Convert the patched standby to a snapshot standby:

  DGMGRL> convert database SALES_AARHUS to snapshot standby;
  

  • The database sets a guaranteed restore point before opening the standby in READ WRITE mode.

• Test the patch apply by running Datapatch on the standby:

  [oracle@aarhus] $ORACLE_HOME/OPatch/datapatch
  

  • One always runs Datapatch on the primary database and the changes made by the patches goes into redo to the standby.
  • But, since I converted to a snapshot standby, it is now opened like a normal database and I can run Datapatch on it.
  • If Datapatch completes without problems on the standby, I can be pretty sure it will do so on the primary as well. The standby is after all an exact copy of the primary database.

• Optionally, perform additional testing on the standby.

  • I can connect any application and perform additional tests.
  • I can use SQL Performance Analyzer to check for regressing SQL statements.
  • I can make changes to any data in the standby. It is protected by a restore point.

• When done, convert the snapshot standby back to a physical standby:

  DGMGRL> convert database SALES_AARHUS to physical standby;
  

  • This implicitly shuts down the standby, flashes back to the restore point and re-opens the database as a physical standby.
  • All changes made when it was a snapshot standby, including the Datapatch run, are undone.

Continue the patching procedure on the primary database as described elsewhere.

Is It Safe?

Sometimes, when I suggest using the standby for testing, people are like: Huh! Seriously?

What Happens If I Need to Switch Over or Fail Over?

I can still perform a switchover or a failover. However, they will take a little bit longer.

When I convert to snapshot standby:

• Redo transport is still active.
• Redo apply is turned off.

So, the standby receives all redo from the primary but doesn’t apply it. Since you normally test for 10-20 minutes, this would be the maximum apply lag. On a well-oiled standby, it shouldn’t take more than a minute or two to catch up.

When performing a switchover or failover on a snapshot standby, you should expect an increase with the time it takes to:

• Shut down
• Flashback
• Apply redo

I’d be surprised if that would be more than 5 minutes. If your RTO doesn’t allow for a longer period:

• Get a second standby.
• Consider the reduction in risk you get when you test on the standby. Perhaps a short increase in RTO could be allowed after all.

What Happens If Datapatch Fails

If Datapatch fails on my snapshot standby, I should be proud of myself. I just prevented the same problem from hitting production.

• I grab all the diagnostic information I need, so I can work with Oracle Support on the issue.
• Convert back to physical standby, which will undo the failed Datapatch run.
• If I expect to solve the issue quickly, leave the standby running in the target Oracle home. Otherwise, put it back into the source Oracle home.

So, yes, it’s safe to use!

Happy testing

Appendix

Other Blog Posts in the Series

• Introduction
• How To Patch Oracle Data Guard Using AutoUpgrade For Non-Standby-First Installable Patches
• How To Patch Oracle Data Guard Using AutoUpgrade And Standby-First Patch Apply With Restart
• How To Patch Oracle Data Guard Using AutoUpgrade And Standby-First Patch Apply With Switchover
• Avoid Problems on the Primary Database by Testing on a Snapshot Standby