This blog post is created because I got some questions in the last weeks about upgrading using Data Guard. For migration to the cloud we now have Oracle Move and ZDM ( https://www.oracle.com/database/technologies/cloud-migration.html and https://www.oracle.com/database/technologies/rac/zdm.html) Check them out as well!

Data Guard is meant as a Disaster Recovery (DR) solution, but to leverage the usage, one of the things it is often used for is also migrations. 
My colleague Mike Dietrich ( https://mikedietrichde.com ) can tell you all the differences between upgrade and migration, but for short. When you stay on the same version and move the system, you migrate. When you want to go to a higher version, you upgrade.

That said, wouldn’t it be cool if Data Guard could be used for both? I think yes, and extra cool, it is even possible. Of course,we cannot use the classic physical standby for this as the redo stream is different between versions, but the solution is to use a Transient Logical Standby database (TLS). You can also find this information in the Oracle Documentation of course.

How does this work? Well, a drawing says more than 1000 words.

The proof of the pudding if of course the eating and there we go.

1. Create the guaranteed restore point
2. Build the logminer dictionary
3. Convert the physical standby to a logical standby
4. Upgrade the logical standby
5. Start the apply again and let it recover
6. Switchover the primary database to the Logical standby, at that point, you are upgraded already!
7. The old primary is now a logical standby, so it needs to be flashed back 
8. Then converted to a physical standby.
9. Due to that conversion, the redo (with the upgrade info in) is sent to the old primary, the new physical standby
10. Finally,a switchover again to put the primary back in place 

These are ten easy steps. So let’s do the flow with an example. In this example we will upgrade a 11.2.0.4 database to 19c. 

REMARK: after doing this, it is highly recommended to take another step to convert the non-cdb to a cdb in 19c.

Create the guaranteed restore point

To do so, we need the primary database to be in archivelog mode and have flashback turned on. If this is your production database, this should be the case. If not, I would highly recommend you to enable flashback on both primary and standby database anyhow.

Then on the primary create the guaranteed restore point:

Build the logminer dictionary

Before we can convert the database to a logical standby, we must be sure that it will work.

First we check the schema’s which will be skipped in the logical standby

Then we verify if all the objects in our database are supported to go to a logical standby.

This is a good case, we don’t have anything unsupported so we can convert this physical standby to a logical standby.

Convert the physical standby to a logical standby

We do not support the Logical Standby Database in the broker. So that also implies that we will have to stop the broker for this operation.

First step is to disable the broker configuration.

And we stop the redo apply on the standby database.

Then, on the primary database, we build the logminer dictionary

Also keep an eye on the alert.log to make sure all goes well.
On the standby database we convert it to a logical standby

Of course, trust but verify!

Then open the logical standby database

Make sure deletion of foreign archived logs at the logical standby is not happening.

You can find more info on how to do that in the Oracle Documentation in Step 2 of this table https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/using-sql-apply-to-perform-rolling-upgrade.html#GUID-C5DF6148-C1E9-4ADF-A975-AC95FC64E0C4
And finally start logical standby apply

Upgrade the logical standby

For this upgrade best practices, I would like to point you to Mike’s website (this is a good starting point https://mikedietrichde.com/2019/04/29/the-new-autoupgrade-utility-in-oracle-19c/ ). It is full of good advice to perform a successful upgrade. Focus on upgrading itself will bring us a bit too far in this blogpost, so I just took the quick and easy way with DBUA to keep the focus on the Data Guard.

Select the logical standby database and specify the SYSDBA credentials.

I have an old apex and itis telling me about it, but for this purpose this warning is less important. For production upgrades, you should care and make sure you fix what is necessary.

I did leave this default.

Better safe than sorry, DBUA will create a guaranteed restore point in case the upgrade would fail. I like this! Only take into account that you need sufficient pace in the Fast Recovery Area (FRA).
For real life situations it is of course much better to select that you have your own backup and recovery strategy as DBUA sometimes has interesting behaviour. This is not important for this blog post though.

In my demovm’s I already have a 19c listener running, so I prefer that one.

It’s a demo, so for me there was no need for monitoring.

After a nice summary we start the upgrade.

Waiting waiting waiting …

All done! We have a 19c logical standby database.

Start the apply again and let it recover

One of the easy things in DBUA is, it also adapts your oratab for this database.

That said, when you log on to your database, you might need a new shell or at least reset your oraenv.

Monitor the lag and make sure it is in sync before continuing

Roleswitch

Switchover the primary database to the Logical standby, at that point, you are upgraded already!

Perform the switchover on the primary. As we disabled the broker, we need to do this manually here:

when all is ok, on standby db

And when it lists “To Primary” you can complete the switchover

And the role switch has been performed.

The old primary is now a logical standby, so it needs to be flashed back 

As said, the old primary is now a logical standby

Before we can convert it to a physical standby, we need to flash it back

As we do not have to run the upgrade on this system, but receive it via redo, we need to the steps ourselves.

First edit oratab to the 19c environment

And set it of course

Copy the necessary files to the NEW 19c oracle home

Then start the database from the New 19c Oracle home in mount mode

Then converted to a physical standby.

Convert the database to a physical standby again and stop the database. We need to reboot it anyway

Due to that conversion, the redo (with the upgrade info in it) is sent to the old primary, the new physical standby

So,startup the instance again and enable managed recovery. Managed recovery waits until the new incarnation branch from the primary is registered before it starts applying redo. 
So monitor the logfiles very carefully to make sure the incarnation has been updated.

Finally,a switchover is done again to put the primary back in place 

On the primary database (the one we switched over to earlier) we ask Data Guard if it is safe to switchover 

That’s why you need to have a continuous tail -f on the alert.log. 

For me this is ok, the switchover just will take a little longer in this case that is not an issue. For production systems you might want to fix this upfront. One of the ways to fix it is use the log_file_name_convert parameter. Take into account, that this setup is a very standard default setup. So you might not hit this issue.

There we go

On the new primary (again dgdemovm1 in this case) we need to open it and then check if the role transition succeeded

Of course, the new standby (dgdemovm2 in this case) should be a physical standby again and we can start redo apply again

At this point, the upgrade is happening on this physical standby database. We get the information via the redo stream and is applied to this standby database which will eventually result in an upgraded database the moment it has catched up.

Enable the broker 

One last step in this list of steps, we highly recommend to use the Data Guard Broker. 

Positive: It detects the broker files and I don’thave to manually enable it again.

And use a 19c new feature to check the lag of the configuration

So folks, that’s all for now. 

Try this before you do it, to get it ready in the head and the fingers and then Happy upgrading!

As always, questions, remarks? find me on twitter @vanpupi