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 12.1.0.2 dictionary on primary, and already an Oracle 12.2.0.1 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 11.1.0.6 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 11.1.0.7 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 12.1.0.1 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 11.2.0.4 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.
Downtime?
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.
Important Links
- 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
–Mike
Hi Mike,
We are planning 12.1.0.2 upgrade to 12.2.0.1 coming August. We have a near zero downtime requirement.
We also want to convert database to cdb with single tenant. Is it possible to use transient logical standby for this purpose ?
Can we not only upgrade logical standby to 12.2.0.1 but also plug it into container ? Will the sync continue working and switch over succeed ?
Regards,
Artem
Artem,
converting a non-CDB to a PDB always requires downtime. The only way to ensure near zero downtime for this move is to use Oracle GoldenGate.
And no, standbys can always only match their setup – meaning non-CDB to non-CDB or CDB to CDB. But not non-CDB to CDB. That’s why you can’t use “Transient” for such a move.
Cheers,
Mike
Hello Mike,
Is the setting PRESERVE_COMMIT_ORDER=FALSE allowable for the SQL Apply phase of “Transient Logical Standby – Rolling Upgrades”?
Sincerely, Igor
Igor,
I have not seen any restrictions. But in order to clarify you may need to open an SR or drop me an email please (I will forward it to the DG team). Since Larry Carpenter retired, I can’t ask him anymore 🙁
Cheers,
Mike
Hi Mike,
Regarding those DBA_LOGSTDBY_UNSUPPORTED data types in a logical standby (and this is the main reason the customer cannot use a logical stby in his ADG configuration)… They have CLOB and NCLOB data types in the code.
So, does this mean that we won’t be able to use rolling upgrade/ transient logical standby, correct?
Thanks,
Bogdan
Bogdan,
what is their source version (exactly)?
Thanks,
Mike
Well, now it’s 10.2.0.5. We’ll migrate it soon in the Oracle Cloud (18c) in an active data guard config. (1 primary and 1 physical standby). Not much to do here, we’ll use data pump. But I was thinking more further, for when we’ll need to patch or upgrade. That physical stby (during the rolling upgrade) will be converted into a transient logical standby, and here comes my question 🙂
Thanks,
Bogdan
Hi Bogdan,
got it. Your source version is the limiting factor unfortunately. So yes, your assumption is right.
If your source would be on a newer version, these data types would be supported and not appear in DBA_LOGSTBY_UNSUPPORTED anymore.
The problem is that for the initial sync, you have a matching 10.2.0.5=>10.2.0.5 environment. And hence, the support for these data types in a higher version does not help you.
Furthermore, your version wouldn’t be “upgrade supported” either. You would need to jump from 10.2.0.5 to 11.2.0.4 and then to 18c or 19c.
So Rolling Upgrades aren’t an option.
And one final thing, please consider 19c instead of 18c as the support for 18c will run out in mid 2021 – which is in 2 years already whereas 19c will give you time until March 2023 (3.75 years).
Cheers,
Mike
Hi Mike,
First of all, there won’t be an “initial sync”. We’ll just full export the 10g database and import it into the 18c database. After that, we’ll create the physical standby from the primary and create the ADG config. So, through all these activities, those unsupported data types will not be an issue. But they will be, as soon as we’ll want to perform a rolling upgrade. This is what I was talking about.
Second, about 19c, you’re right. Unfortunately, 19c is not available yet in our Oracle Cloud at Customer 🙂 So this upgrade will happen somewhere in the near future, most likely.
Regards,
Bogdan
Hi Bogdan,
so you move FIRST to 18c, and then you setup the standby environment.
And then, from 18c on, you are asking about Transient Logical Standby (with DBMS_ROLLING — requires ADG — or without DBMS_ROLLING — no ADG–)?
Then these data types shouldn’t play any role anymore. From 18c on, as far as I remember, even object types are fully supported with Transient. And if course, yours are, too. Only Nested Tables (I believe with object types in it) aren’t. But since 18c, SQL Apply is almost complete.
Cheers,
Mike
Thanks Mike! I cannot argue on that. Although it’s a bit confusing. I’ve read that the logical standby limitations are those related to some unsupported data types & unsupported objects. Cannot remember which version was, still. So, because the dev guys are using CLOB, NCLOB… that’s why I have these topics i am trying to clarify:
1. if they are using those unsupported data types means that they won;t be able to use a logical standby db?
2. will it be an issue when we’ll try to perform a rolling upgrade (with dbms_rolling)?
Cheers,
Bogdan
Hi Bogdan,
reg 1) yes
reg 2) maybe
More on this:
The logminer which is used for the SQL Apply (extraction of redo log information and transformation into applicable SQL statements) has been improved a lot over the releases. You’ll get almost full data type support when your source will be 18c in the future. This includes CLOBs and NCLOBs as well as far as I remember. You may need to open an SR with support for a final clarification. But when you upgraded to 18c, these objects should disappear from DBA_LOGSTBDY_UNSUPPORTED.
But even IF you have unsupported data types, it does not necessarily mean that you can’t use Transient Logical Standby for a rolling upgrade. You may be able to ensure that certain types of data don’t get updated during the switchover phases. Usually the time between the two switchover phases including the synching operations should be less than 2 hours. Hence, if you know, these objects don’t appear in the logs within this period, you can still use the process.
Hope this helps – cheers,
Mike
Hi Mike ,
When we switch over from logical to primary . lets say my primary is now 19c and logical is 12c . Will sql apply will go on ? Will thr be any impact ?
You will never have the Primary at 19c and the Logical Standby on 12c “in operation”. I think you refer to STEP 6 in the graph. Your new PROD will be 19c, your old STBY will be now 12-whatever. But you won’t synch it with 19c as this would obviously fail as you assumed already. You will flash it back STEP 7 to the guaranteed restore point you created before invoking the process. Then you will convert the control file, and make the logical standby control file (which it is after STEP 6) into a physical standby control file.
So you never send logs from the higher to the lower “executable”.
And you are right, this wouldn’t technically work.
Cheers,
Mike
Is this transient Logical Standby approach still valid? Or does oracle completely recommend using OGG nowadays? (of course OGG needs extra licensing)
Hi Pavol,
OF COURSE IT IS … no doubt here.
So this is the situation:
1. Transient Logical Standby manually – still works, does not require any extra option such as ADG
2. Transient Logical Standby with shell scripts (phrys-can’t remember) – still works, does not require any extra option such as ADG
3. Transient Logical Standby with DBMS_ROLLING – still works, but does require ADG
And of course, you can use OGG as well.
Cheers,
Mike
Hey Mike,
Please update this blog post with the CAVEAT: The physru.sh (latest is physru_v3.sh) script is NOT Multitenant-aware or compatible! Trust me, I’ve spent several months on a Service Request with Oracle Support on this. We confirmed the current version of that script has NO commands inside it to accommodate PLUGGABLE databases (CDBs and PDBs). So this script will only work with Single Tenant environments, including RAC with DataGuard. I’m hoping Oracle Development will update this script to make it work with CDBs and PDBs, but don’t hold your breath on that!
Thanks a lot, Ernest.
Unfortunately, I think the phrysru scripts are desupported as far as I’m aware.
Which makes sense if it doesn’t work with MT. I doubt that anybody in development will update them.
Thanks, and of course I will update the blog post.
Cheers,
Mike
Great! Thanks Mike. I’ll submit a further comment here if Oracle Development replies to my SR that they will be updating the physru.sh script to be Multitenant-aware. Meantime, for the many customers I have with regular (not Active) DataGuard, we’ll have to work out detailed steps to do the work of the physru script manually, to achieve “minimal downtime” for RAC db’s with DG physical standby.
Cheers,
-Ernie.
Hi Mike,
I understand this upgrade procedure, We can use logical standby to replicate 12c -> 19c
But can we go 19c -> 12c, in case it fails means if the 19c does not perform well, can they roll back to 12c database. Is this doable?
thanks,
Meeta
Within the process, you can go backwards via Guaranteed Restore Points. And you have to differentiate between “issues during the process / upgrade” or “issues after the upgrade”. As I read your email, you are referring to “issues after the upgrade”, for instance a performance problem you haven’t seen during testing. In this case, you can’t revert the Transient Logical Standby Rolling Upgrade process. What you will need to do then is a downgrade of the database.
See here:
https://mikedietrichde.com/hol-19c-fallback-issues-after-upgrade/
It will be a key requirement to NOT change COMPATIBLE. Otherwise you can only export/import, and maybe synch with Oracle GoldenGate.
Cheers,
Mike
Hi Mike,
I’m planning an upgrade from 12.2 to 19c of a dataguard environment. I will be using the transient logical standby method for this. Is it possible to perform the upgrade of the logical standby using autoupgrade?
I have used both methods in the past but never together…
Thanks,
Peter
Peter, we are testing this right now – but I will drop you an email with some advice.
Cheers,
Mike
Has anyone succesfully used physru_v3.sh to upgrade 12.2 to 19c? Secondly, the advice for using autoupgrade also interests us a lot. Can you please share this info, or send us an email as well?
Hi Gys,
I checked with the DG team and the scripts are supposed to work for this case but it hasn’t been tested anymore since you can either do it the manual way, or use DBMS_ROLLING when you have an Active Data Guard license. So it “should” work but we can’t confirm.
The missing info regarding AU in this scenario is coming soon on Daniel’s blog – but I will add a link here, too.
Cheers,
Mike
Hi Mike.
Firstly greatly appreciate all the information you and your colleagues provide.
We currently have two sites replicating via Data Guard, they are identical from a hardware perspective and we regularly switch roles. To provide near zero downtime migration we first create restore points on both sides while the dbs are in sync , gather new changes via GG and then activate the standby database. Deploy new code to the activated environment, test the environment , replicate the changes via golden gate and, if all good redirect user traffic to the new primary. We then rollback the old primary to the original restore point and then convert it to a physical standby.
We are ok with this working for a 19c upgrade but firstly just need to confirm we can use autoupgrade on the activated standby (should be ok as it is effectively a primary now), we will then go live on that upgraded environment and then flashback the old primary , start it up with the 19c oracle home , convert it to a physical standby and all the 19c database changes will come over via the redo.
With a 19c upgrade comes the potential to migrate to a multi tenant architecture. Though we see no benefits in this architecture for our production environment, there are many benefits for non-prod and we do understand we have to make the move at some time and therefore it should be considered as part of this upgrade.
However, the fact that a conversion to a CDB is not reversible (via the usual flashback) makes me believe that following the above plan is unlikely to work when it comes to the migration to CDB.
In a perfect world something like :-
Activate the standby
Upgrade to 19c
Migrate to CDB
Go live.
Flashback the old primary
Convert to CDB
Convert to physical standby
resync via redo.
Even in a worst case scenario and I need to rebuild the standby. I have 100TB on the standby site which I would prefer not to recopy from the primary. It would be great if the standby could be recreated as a CDB from the non-cdb standby
Certainly open to other options and also happy to further explain if required.
Thanks in advance
Phil
Hi Philip,
you can use AU on the activated standby – this will work for sure.
And actually, the only quick way to reverse the migration is OGG in my opinion. All other approaches take too much time.
See here:
https://mikedietrichde.com/2019/08/05/database-migration-from-non-cdb-to-pdb-the-fallback-challenge/
Cheers,
Mike