More than 10 years ago I was invited to work several weeks in Redwood Shores at the Oracle HQ for Lawrence To‘s MAA team together with Ray Dutcher, one of the greatest persons I have ever met and worked with in my over 20 years at Oracle. We had to work out a doable process for smooth rolling upgrades utilizing a physical standby database. Somebody named it Transient Logical Standby – Rolling Upgrades. And here’s a quick overview on it for clarification purposes. When you scroll to the end you will find a lot of useful links and resources mostly published by others.
Transient Logical Standby – Rolling Upgrades – Overview
In my presales days at Oracle Germany I was responsible for Oracle Data Guard and Database Upgrades and Migrations. Customers often asked me for minimal downtime upgrade strategies. But one common answer wasn’t accepted very often: Use a Logical Standby.
The next question I heard frequently: Why can’t I use a physical standby databases for a rolling upgrade? Logical Standby databases have so many pitfalls (unsupported data types, the log miner speed etc).
And my answer was (and still is) quite simple:
You can’t use a physical standby database for such a move as a physical standby is a block-identical copy of your primary. Hence you can’t have an Oracle 126.96.36.199 dictionary on primary, and already an Oracle 188.8.131.52 dictionary on the physical standby.
In 2007 this became the starting point for Transient Logical Standby Rolling Upgrades. You create a logical standby database from a physical standby. But then you open it, and it will be kept current with SQL Apply. The log miner will extract the SQL commands from the log files and apply it to the open logical standby database. This gives you the possibility to upgrade the logical standby database. And the idea was, to convert it back into a physical standby afterwards.
Transient Logical Standby – Versions and Methods
First of all, we support Transient Logical Standby database rolling upgrades since Oracle 184.108.40.206 officially. There was a base technique developed with two customers, one in the US, one in Austria, for 10.2 databases which did require special treatments with “nid” and recreating control files and such. I won’t discuss this here as you hopefully don’t work with 10.2 databases anymore (see also: Why does your most important database run on Oracle 10.2.0.4) but you can find all the 10.2 details in this white paper.
With Oracle 220.127.116.11 we got some useful extensions to the process (e.g. the
CONVERT TO PHYSICAL command).
And in the Oracle 11.2 days the
physru.sh scripts got released easing the process a bit more (see MOS Note: 949322.1 – Oracle11g Data Guard: Database Rolling Upgrade Shell Script)
Be aware that these scripts don’t work with Oracle Multitenant. As far as I’m aware, the scripts are not supported for higher versions than 11g. They may work as long as you have a non-CDB environment. But certainly not with Multitenant (or Single Tenant).
Thanks to Ernest Kalwa – and sorry for the inconvenience this caused.
Since Oracle 18.104.22.168 we introduced the
DBMS_ROLLING package in the database. This package allows you to leave the Data Guard Broker on and drive the rolling upgrade semi-automatic and/or from OEM. Please find a detailed showcase of DBMS_ROLLING written by Data Guard Product Manager Pieter van Pyumbroeck.
With the manual methods (command line or
physru.sh) you have to turn off the Data Guard Broker for the entire process.
Transient Logical Standby – License Situation
I promised I will never explain license situations here on the blog as I’m not an expert on it. And of course licensing may change over time. Therefore, please always consult the most up-to-date Oracle License Guide or contact the Oracle license specialists for questions.
You won’t find the term “Transient” and/or “Logical Standby” in the Oracle License Guide but you may find this:
I interpret this as you can do rolling upgrades without an extra license for Transient Logical. But as soon as you use the DBMS_ROLLING package you’ll use functionality of Active Data Guard, and therefore must license it. Manual Transient Logical Standby or the use of
physru.sh should not require an ADG license.
But again, I don’t give license advice here. For clarification please contact the Oracle license specialists.
Transient Logical Standby – Additional (not alternate!) Facts
Another question I receive from time to time: Can this procedure go cross platform?
The answer is straight forward: It can go cross platform in some rare cases but never across Endianness. This means, you can have such a process between Windows and Linux but never between AIX and Linux. Please see MOS Note: 1085687.1 – Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration for supported configurations.
The log miner got improved from version to version but it still is the critical part of the entire process. First of all, it has to “translate” and apply the statements from the log files which by definition is more resource intense than simply apply redo. And you must check in your database for
DBA_LOGSTDBY_UNSUPPORTED data types and more. And
DBA_ROLLING_UNSUPPORTED in case you use
DBMS_ROLLING. Furthermore, the SQL Apply process may need some extra tuning – and most of the recommendations in this fairly old paper are still valid.
I also received this question a few times: Can I jump from Oracle 22.214.171.124 to Oracle 18c?
Yes, you can. You can “jump” forward as far as the database upgrade certification allows you to go forward. And of course you can use the process as well for patching in case you don’t have a RAC but still would like to ensure reduced downtime to a minimum.
Using a transient logical standby procedure does not give you zero downtime. But you can switch to a new release or to a new patch bundle with an overall downtime of less than 5 minutes in most cases. I have seen customer having less than 2 minutes of overall downtime.
Why do you encounter downtime?
You will encounter downtime due to the two role changes (switchover). When you upgraded the logical standby then you force the first switch. This switch is fast as the logical standby is open and it does not have to be restarted. The second switchover may take a bit longer as it requires a restart of the physical standby.
And the last question I got recently: Why do I have to do the second switchover?
Here the answer is a bit more complicated. First of all, there’s a logical reason. Most people prefer to have their standby in the designated data center at the end of the process. The second reason is technical if I remember correctly: The INCARNATION# of the database needs to be brought in sync. Otherwise the next attempt to do a Transient Logical Standby Rolling Upgrade may fail.
- MOS Note: 949322.1
Oracle11g Data Guard: Database Rolling Upgrade Shell Script
- MOS Note: 1085687.1
Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration
- White Paper: Oracle Rolling Upgrades – Using a Data Guard Physical Standby
- White Paper: SQL Apply Best Practices (from 2008)
- Data Guard PM Pieter van Pyumbroeck on DBMS_ROLLING – Feb 2020
- Robert Bialek’s excellent DOAG Conference Talk from 2017
- Uwe Hesse’s Blog Post about Transient Logical Standby Rolling Upgrades
- Marcin Przepiorowski’s Blog Post about Transient Logical Standby Rolling Upgrades
- Gavin Sorma’s Blog Post about Transient Logical Standby Rolling Upgrades
- Arun Gupta’s Blog Post about Transient Logical Standby Rolling Upgrades including many pictures