Upgrade Testing with a Refreshable PDB – does this work?

Yesterday, Daniel pinged me to discuss an upgrade testing scenario I had evaluated briefly by myself a while ago. I can’t remember why I gave up. But Daniel had some great ideas and tried already a lot in our OCI cloud. We want to use a refreshable PDB. And keep it up to date, then upgrade it. The idea of this approach: Whereas a CLONE or RELOCATE uses the initial command as the marker, a REFRESH would allow me to be way more in synch. So the question is: Upgrade Testing with a Refreshable PDB – does this work?

Upgrade Testing with a Refreshable PDB - does this work?

Photo by Douglas Bagg on Unsplash

And happy that there are so many sheep pictures on Unsplash πŸ™‚

A clarification upfront

Even though my blog post is headlined with “Testing“, I didn’t mean to use this for testing only. Of course, you can (and actually, you should) use this approach for your production upgrades as well. The idea is really:

  1. Test it first
  2. Then use the approach for your production upgrade

I just realized the other day that my wording is a bit misleading

My setup

As usual, let me start with a quick description of my setup. I have an Oracle 12.2.0.1 CDB with a PDB, and another Oracle 19.7.0 CDB. I will create a refreshable PDB from 12.2.0.1 to the higher version CDB. Then I would like to refresh it several times and see if this works. Once I’m good to go, I’d like top STARTUP UPGRADE the PDB, and upgrade it to match with the CDB. Sounds strange? Let’s see.

Upgrade Testing with a Refreshable PDB - does this work?

I can trigger the “REFRESH” operation as often as possible. But as soon as I open the PDB with STARTUP UPGRADE, there is no way back. And fallback strategies, also for testing, such as FLASHBACK PLUGGABLE DATABASE don’t work here due to the fact that my PDB hasn’t been upgraded to 19.7.0 yet.

But I may be able to decrease the downtime significantly as the point. I trigger the upgrade at a timestamp AFTER the cloning operation has been finished, and not when I started the cloning potentially hours ago.

Refreshable PDB

Others have written a lot about like Franck Pachot about the Poor-Man’s Standby. But I’d like to go with a very simplified approach:

CDB1 CDB2
Create a common user for the refreshable PDB clone operation and assign necessary privileges:

CREATE USER c##borg IDENTIFIED BY oracle DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp CONTAINER=ALL;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE, SYSOPER TO c##borg CONTAINER = ALL;
Create the public database link for the relocate operation:

CREATE public DATABASE link clonemypdb CONNECT TO c##borg IDENTIFIED BY oracle USING 'CDB1';
Initiate the refreshable PDB clone:

CREATE PLUGGABLE DATABASE PDB1 FROM PDB1@clonemypdb REFRESH MODE MANUAL file_name_convert=('CDB1','CDB2');
Refresh once, or repeat several times:

ALTER PLUGGABLE DATABASE PDB1 REFRESH;
End the refresh:

ALTER PLUGGABLE DATABASE PDB1 REFRESH MODE NONE;
Open the PDB:

ALTER PLUGGABLE DATABASE PDB1 OPEN UPGRADE;

Automatic Refresh?

Yes, magically this works as well. Just exchange:

  • CREATE PLUGGABLE DATABASE PDB1 FROM PDB1@clonemypdb REFRESH MODE MANUAL file_name_convert=('CDB1','CDB2');
    

with:

  • CREATE PLUGGABLE DATABASE PDB1 FROM PDB1@clonemypdb REFRESH MODE EVERY 1 MINUTES file_name_convert=('CDB1','CDB2');
    

in the above step-by-step path and see what happens (snippet from the alert.log):

PDB3(3):alter pluggable database refresh
2020-05-05T09:22:15.818637+02:00
Applying media recovery for pdb-4099 from SCN 1303052 to SCN 1303086
Remote log information: count-1
thr-1,seq-14,logfile-/u02/fast_recovery_area/CDB1/CDB1/foreign_archivelog/PDB3/2020_05_05/o1_mf_1_14_3210936238_.arc,los-1201204,nxs-18446744073709551615,maxblks-0
PDB3(3):Media Recovery Start
2020-05-05T09:22:15.819146+02:00
PDB3(3):Serial Media Recovery started
PDB3(3):max_pdb is 3
2020-05-05T09:22:15.869461+02:00
PDB3(3):Media Recovery Log /u02/fast_recovery_area/CDB1/CDB1/foreign_archivelog/PDB3/2020_05_05/o1_mf_1_14_3210936238_.arc
2020-05-05T09:22:16.032668+02:00
PDB3(3):Incomplete Recovery applied until change 1303086 time 05/05/2020 09:22:14
2020-05-05T09:22:16.042738+02:00
PDB3(3):Media Recovery Complete (CDB2)
PDB3(3):Completed: alter pluggable database refresh
2020-05-05T09:23:14.881440+02:00
PDB3(3):alter pluggable database refresh
2020-05-05T09:23:16.234524+02:00
Applying media recovery for pdb-4099 from SCN 1303086 to SCN 1303696
Remote log information: count-1
thr-1,seq-14,logfile-/u02/fast_recovery_area/CDB1/CDB1/foreign_archivelog/PDB3/2020_05_05/o1_mf_1_14_3210936238_.arc,los-1201204,nxs-18446744073709551615,maxblks-0
PDB3(3):Media Recovery Start
2020-05-05T09:23:16.235308+02:00
PDB3(3):Serial Media Recovery started
PDB3(3):max_pdb is 3
2020-05-05T09:23:16.298760+02:00
PDB3(3):Media Recovery Log /u02/fast_recovery_area/CDB1/CDB1/foreign_archivelog/PDB3/2020_05_05/o1_mf_1_14_3210936238_.arc
2020-05-05T09:23:16.477247+02:00
PDB3(3):Incomplete Recovery applied until change 1303696 time 05/05/2020 09:23:14
2020-05-05T09:23:16.484314+02:00
PDB3(3):Media Recovery Complete (CDB2)
PDB3(3):Completed: alter pluggable database refresh

Pretty neat!

In order to avoid the below error, make sure your CDBs are in archivelog mode:

2020-05-05T09:54:14.842820+02:00
PDB3(3):alter pluggable database refresh
Applying media recovery for pdb-4099 from SCN 1303729 to SCN 1306371
Remote log information: count-1
thr-1,seq-17,logfile-/u02/fast_recovery_area/CDB1/CDB1/foreign_archivelog/PDB3/2020_05_05/o1_mf_1_17_3210936238_.arc,los-1305794,nxs-18446744073709551615,maxblks-0
PDB3(3):Media Recovery Start
2020-05-05T09:54:14.985606+02:00
PDB3(3):Serial Media Recovery started
PDB3(3):max_pdb is 3
PDB3(3):ORA-65345 signalled during: alter pluggable database refresh...

See MOS Note: 2613419.1 – ORA-65345 during hot clone of pluggable database for more information.

Upgrade the cloned PDB

As next step, I will upgrade my cloned PDB. I explained and showed this already in detail here:

Of course, I can’t revert. If I would like to test again, I need to repeat this action.Β  But this looks to be even smarter than simply cloning. And of course, the upgrade completes successfully.

Summary

Kudos to Daniel for bringing this topic up.

This is a pretty smart way. You clone your PDB. But instead of waiting until the clone is finished before you can upgrade – where your PDB may have been hours old already – you can refresh it as long as needed. Hence, YOU determine the point in time, when you trigger your database upgrade, and not the cloning process.

In addition, I think it is soon time for a summary blog post with an overview, especially on the different time lines for each approach, as well as the pros and cons of each technique. In addition, Daniel and I will publish some blog posts about upgrades in the cloud. There you will see how helpful this refresh can be, especially in cases where you database is larger and/or the cloning time is unpredictable.

Addition

Thanks to Andrea Cremonesi who pointed me to this important fact:
If you are not going into Oracle 19c but Oracle 18c instead, you will need patch 28374604 in addition.

Well, and as it turns out, there are more pitfalls.

Daniel just send me this one he hit. If you see the error below during CREATE PLUGGABLE DATABASE:

ERROR at line 1:
ORA-19505: failed to identify file "+DATA/CDB1_FRA1H3/A495BBFD2B921151E0532301000A47E5/DATAFILE/users.275.1039261905"
ORA-15173: entry 'CDB1_FRA1H3' does not exist in directory '/'

you may please need to apply:
HOT CLONE OF PDB FAILS WITH ORA-15001 – bug 29469563

TDE – Transparent Data Encryption

My dear PM colleague, Peter Wahl, created 3 excellent videos on how to achieve the above with Transparent Data Encryption (TDE).

  • TDE Academy #1:
    Remote Clone an encrypted PDB, and upgrade from Oracle 18.10 to Oracle 19.7
  • TDE Academy #2:
    Remote Clone an encrypted PDB, and upgrade from Oracle 12.2.0.1 to Oracle 19.7.0
  • TDE Academy #3:
    Remote Clone an encrypted PDB, and upgrade from Oracle 12.1.0.2 to Oracle 19.7.0

Further Information and Links

–Mike

Share this: