Data Pump Enhancements in Oracle 21c (and a little support story)

I’ve been having a play with some of the Oracle 21c data pump enhancements. This post links to the resulting articles, and includes a little story about one of the features.

The Articles

Here are the articles I wrote during this investigation.

As an aside, I also knocked up a quick overview of the DBMS_CLOUD package. I’ve used many of the routines in this package in my autonomous database articles over the last few years, but I got a bit sick of jumping around to get the syntax of different operations, so it seemed sensible to have a centralised description of everything, along with working examples.

The Story

The article about using expdp and impdp with a cloud object store (Oracle Cloud Object Storage, AWS S3 or Azure Block Storage) came with a little bit of drama.

Back in the 18c days it was possible to import into an Autonomous Database using a dump file on a cloud object store using the 18c impdp utility. I wrote about this at the time (here). At that time export to a cloud object store using the expdp utility was not supported, and the import wasn’t supported with an on-prem database.

Oracle 21c introduced the ability to export from an Autonomous Database to a cloud object store, which worked fine first time. The documentation also listed a new feature called, “Oracle Data Pump Supports Export to and Import From Cloud Object Stores“. This sounded very much like it meant for on-prem databases, and sure enough it did.

When I started trying to use this feature I pretty quickly hit a road block. The expdp utility couldn’t connect to the object store bucket. I raised a call with Oracle Support about it. While I was waiting for a response I figured this functionality may have a dependency on the DBMS_CLOUD package under the hood, so I installed it in my on-prem database. The on-prem installation of DBMS_CLOUD was working OK, but the expdp utility was still failing to contact the object store bucket.

Due in part to friends in high places, my SR got picked up and it was confirmed the DBMS_CLOUD installation was an undocumented prerequisite, but it was still not working for me. The support engineer confirmed they could replicate the issue too. A few interactions between support and development resulted in bug 33323028, which fortunately had a simple workaround. At that point the support engineer was up and running, but I still had a problem. A bit of tracing later and it turned out my remaining issue was PEBCAK (Problem Exists Between Chair And Keyboard)…

When I installed the DBMS_CLOUD package it said to put a wallet reference in the sqlnet.ora file. I did that and the package seemed to be working OK, so I thought everything was good. Unfortunately I put it under the ORACLE_HOME and Oracle 21c uses a read-only Oracle home, so that’s the wrong place. It didn’t affect the package, as that picks up the wallet location from a database property, but it affected the expdp and impdp utilities. I keep telling people read-only Oracle homes will trip you up if you are not used to them, and sure enough it tripped me up. Damn you muscle memory! Once the correct sqlnet.ora file was amended everything was good.

So the journey to get this feature working involved:

  • An undocumented prerequisite, which I guessed.
  • A bug which Oracle Support and the dev folks gave me a workaround to.
  • An idiot (me) trying to learn not to be an idiot.

With a bit of luck the bug fix/workaround will be rolled into a future release update, so you may never see this. The MOS note about the DBMS_CLOUD package installation suggests this might also be part of the database by default in future. That would be great if it happens.

Anyway, after that little drama I was able to export data from my on-prem database to a dump file located on a cloud object store, and import data from a cloud object store into my on-prem database. Happy days!

Thanks to the support and dev folks who helped get me through this! πŸ™‚

By the way, all the other Oracle 21c data pump new features worked without any issues.

So there you have it. Some new articles and a little bit of drama… πŸ™‚

Cheers

Tim…

Video : Multivalue Function-Based Indexes for JSON_EXISTS in Oracle Database 21c

In today’s video we demonstrate multivalue function-based indexes for JSON_EXISTS, introduced in Oracle 21c.

The video is based on this article.

You may also find these helpful.

The star of today’s video is my sister-in-law and the queen of the database Maria Colgan. It’s a pleasure to be one of your unworthy minions… πŸ™‚

Cheers

Tim…

Video : Capture Privilege Usage (DBMS_PRIVILEGE_CAPTURE)

In today’s video we’ll demonstrate the capture privilege usage functionality,
introduced in Oracle 12.1.

The video is based on this article.

The star of today’s video is Maris Elsins, filmed at a UKOUG event a few years ago.

Cheers

Tim…

Oracle 21c : Being a newbie at upgrades again…

I was having a play with upgrades to 21c over the weekend. It all started when Stephen Butterworth asked this in a comment.

“… do you know if 21c would create a non-PDB if you plied it with beer, a Melton Mowbray and asked nicely?”

I answered with this.

“They claim non-CDB is dead in 21c. It’s desupported, so that means most companies will not consider it. I have no idea if it is actually possible to run it, and to be honest I don’t care. The desupport notice is the killer for me.”

I subsequently found it throws an error if you try to open a non-CDB database in Oracle 21c.

That got me thinking about upgrade scenarios. I’ve done loads of upgrades to 19c PDBs recently and in most cases they followed this format.

  1. Upgrade the existing non-CDB database to 19c.
  2. Test the impact of the 19c upgrade on the non-CDB.
  3. Create a new 19c CDB.
  4. Convert the upgraded 19c non-CDB database to a PDB in the new 19c CDB.
  5. Test the impact of the conversion of non-CDB to PDB.

So how do you do the equivalent of that when you can’t run a non-CDB instance in Oracle 21c? Short answer, you don’t! At least not the same way…

The preupgrade.jar is dead, long live autoupgrade.jar

The first thing I noticed when I started playing with 21c upgrades is the “preupgrade.jar” has been superseded by the “autoupgrade.jar”. If you try to use the “preupgrade.jar”, it tells you to use AutoUpgrade. AutoUpgrade is available for 19c, but I’ve not used it for all my 19c upgrades. During the process of moving to 19c I’ve done a lot of work to clean up some of our crap, and make the resulting databases as “generic” as possible. I think from here on in, using a tool like AutoUpgrade will be fine, but I was nervous about using it before now. Maybe it’s just me being overly cautious. πŸ™‚

Anyway, AutoUpgrade is the main thing from now, and it’s really useful. At first I was using it as if it were the “preupgrade.jar” and I couldn’t see the big deal. Then I realised a bunch of the stuff I was doing manually, like copying config files and managing the instance state were all done for me when I did a deploy. It certainly makes things simpler, which is always a good thing.

Auto-Upgrade of Adopted PDBs

As I mentioned before, one of the things that was freaking me out was I couldn’t follow my “normal” approach.

Expanding on what I said previously, in previous versions I might convert a non-CDB database to a PDB as part of an upgrade like this.

  • Upgrade the non-CDB database to the correct version.
  • Test the impact of the upgrade.
  • Create a new CDB if a suitable one didn’t already exist.
  • Describe the non-CDB database using the DBMS_PDB package, which creates a file containing the description.
  • Create a new PDB in the CDB using the description file.
  • Run the “noncdb_to_pdb.sql” script to convert the non-CDB database to a real PDB.
  • Open the new PDB.
  • Test the impact of the PDB conversion.

In 21c we can’t do the upgrade of the non-CDB database, so that’s not an option. Instead we have to adopt the PDB directly. In the past we would have done this.

  • Create a new CDB if a suitable one didn’t already exist.
  • Describe the non-CDB database using the DBMS_PDB package, which creates a file containing the description.
  • Create a new PDB in the CDB using the description file.
  • Upgrade the PDB.
  • Run the “noncdb_to_pdb.sql” script to convert the non-CDB database to a real PDB.
  • Open the new PDB.
  • Test the impact of both the upgrade and conversion to a PDB.

In 21c this approach is a bit different.

  • Create a new 21c CDB if a suitable one didn’t already exist.
  • Describe the non-CDB database using the DBMS_PDB package, which creates a file containing the description.
  • Create a new PDB in the CDB using the description file.
  • Open the new PDB in read-write mode.
  • Test the impact of both the upgrade and conversion to a PDB.

In Oracle 21c opening the PDB in read-write mode automatically triggers the upgrade of the PDB so it matches the CDB version, and converts the non-CDB database into a PDB. Bingo!

You can choose to do this conversion manually, or let AutoUpgrade do it all for you.

Database Versions for Direct Upgrade

Oracle 21c only supports direct upgrades from Oracle 12.2, 18c and 19c. If you are on an older version, you will have to do one or two upgrades prior to the 21c upgrade.

I suspect this will be similar for Oracle 23c, so that might be an incentive to push your upgrades to 19c now…

But why bother with 21c upgrades?

I’ve asked you not to use Oracle 21c here, so why bother playing with the upgrades? This is the way things are going to work in 23c too, and I will be wanting to upgrade to 23c, so it’s good to get up to date now, rather than start my first upgrade in a couple of years and realise I don’t know what I’m doing.

Articles

Along the way I produced these articles. The first one repeats a lot of what I said here, but without the chatter. The others are upgrade scenarios I wanted to test.

Conclusion

Having spent so much of the last year doing database upgrades to 19c I was thinking about changing my job title to “Upgrade Guru”. With the advent of Oracle 21c I’m back to being a newbie again…

Cheers

Tim…

Oracle Database 21c : Installation Articles, Vagrant and Docker Builds

As you have no doubt heard, Oracle database 21c was released on Friday. I went to work over the weekend doing the relevant articles and builds. They’ve been on the front page of the website since Sunday, but I was waiting for the release of the 21c preinstall package before announcing them. That has arrived now, so this is what I was up to at the weekend.

Installation Articles

First the articles for single instance, Data Guard, RAC and RPM installations.

Vagrant

There are a bunch of associated Vagrant builds on Oracle Linux 8 and 7.

Single instance database installs, using the conventional installation method, and the RPM installation.

Data Guard installs.

RAC installs.

Docker

There are two database builds for 21c database in containers.

Caution

As I wrote in my previous post, Oracle 21c is an innovation release. It’s good to play with this, but be careful when considering it for production.

Cheers

Tim…

PS. I’m struggling to notice the difference between “21” and “12” when reading at the moment. πŸ™‚

Oracle 21c is here! Please don’t use it!

If you were hanging around Twitter a few hours ago you will have seen people tweeting about the release of Oracle 21c for Linux.

  • Linux downloads here (scroll down).
  • Documentation here.

Every new release starts a flurry of activity by people like myself, but with Oracle 21c that excitement has to be tempered because Oracle 21c is an innovation release. I wrote about this here.

To cut a long story short, an innovation release is only supported for a short time, so you are expected to upgrade very soon after the next release is available. The next release will be Oracle 23c, which will be a long term release.

So what should I do?

There is a reason why Oracle 19c is still top of the downloads page and you have to scroll down to see the 21c downloads. Oracle 19c is the latest long term release, so your main focus should be getting all your databases to Oracle 19c, and preferably running on pluggable databases.

If you have a pressing need for some of the features of Oracle 21c, then by all means test it out and consider going to production with it, but only if you *know* you will be upgrading to Oracle 23c as soon as it’s released.

Of course, this doesn’t stop you playing with Oracle 21c now. I always recommend using the latest release for your playgrounds.

What should I expect of 21c?

Let’s be honest. The Oracle database is a very mature product, so the vast majority of new features will not be of interest to most people. That’s not to say there isn’t any cool stuff, there is, but you know you are still using the database like it is Oracle 7… πŸ™‚

A couple of things that may confuse people with 21c are:

  • Non-CDB instances are desupported, so you have to use PDBs. I’ve been telling you to learn this stuff since Oracle 12.1! Remember, you can use up to 3 user-defined PDBs without having to pay for the multitenant option, so there is no licensing cost implication to switching to multitenant provided you stay within this limitation.
  • Read-only Oracle homes are the default in 21c. This functionality was introduced Oracle 18c (see here), but I get the impression most people don’t know it even exists. Well, you better learn! It’s going to take a little time to get used to the new locations of things, but I think it’s a better solution in the long run.

Conclusion

Don’t be stupid, stupid!

Oracle 21c is an innovation release, so make sure you know what you are getting into before launching into using it. If someone is pressuring you into moving to Oracle 21c for real systems, you need to question their motivation.

Over the next few days there will be installation articles appearing here, as well as Vagrant and Docker builds. I’ve already done a first cut of a basic Vagrant build here, but it will probably change a bit over the coming days/weeks as I play with the new release. There will also be RAC and Data Guard builds, but first things first…

Happy upgrading, or not… πŸ™‚

Cheers

Tim…

PS. There are still some people that haven’t got the memo yet. There will be no 22c. Oracle is skipping it and the next release after 21c will be 23c.

Video : APPEND Hint, Redo and NOLOGGING

In today’s video we demonstrate the relationship between the APPEND hint, redo generation and NOLOGGING.

The video is based on a section of this article.

The star of today’s video is Danny Bryant. This clip is from back in his Oracle days, but he’s now moved to the colder climate of Snowflake.

Cheers

Tim…

Video : Proxy Authentication : Connect to the database using proxy authentication

In today’s video we demonstrate using GRANT CONNECT THROUGH to enable proxy authentication.

The video is based on this article.

The star of today’s video is Lonneke Dikmans, who always seems to be doing something cooler than me. Check out DoeMee, an app that “offers a total model for contemporary governance”.

Cheers

Tim…

Database Upgrades : It’s been a long road getting from there to here

Please play the Star Trek – Enterprise theme while you are reading this post. πŸ™‚

I’ve mentioned database upgrades a few times over the last year or more. Like many others, we are pushing hard to get everything upgraded to 19c. Over the last couple of weeks a bunch more systems got upgraded, and we are now looking like this.

The remaining 11.2 and 12.1 databases are all in various stages of migration/upgrade. I would not curse us by giving a deadline for the final databases, but hopefully soon!

The reason for mentioning that theme song is it starts with the words, “It’s been a long road getting from there to here”, and that is exactly how it feels.

Many of the database upgrades are technically simple, but the projects surrounding them are soul destroying. Getting all the relevant people to agree and provide the necessary resources can be really painful. This is especially true for “mature” projects, where the, “if it ain’t broke, don’t fix it”, mentality is strong. I wrote about the problems with that mentality here.

I’m not going to give you any blinding insights into how to do your database upgrades, because every upgrade is potentially unique, as I discussed here.

We always go for the multitenant architecture (CDB/PDB) unless there is a compelling reason not to. I think we only have one non-CDB installation of 19c because of a vendor issue. None of our other 3rd party applications have had a problem with using PDBs, provided we’ve made sure they connect with the service, not a SID. We don’t use the USE_SID_AS_SERVICE_listener_name parameter. I would rather find and fix the connection issues than rely on this sticking plaster fix.

In know I’ve said some of these things before, but they are worth repeating.

  • Oracle 19c is the current long term release, so it’s going to have support for a longer time than an innovation release.
  • Oracle 21c is an innovation release. Even when the on-prem version does drop, you probably shouldn’t use it for your main systems unless you are happy with the short support lifespan.
  • I recently heard there won’t be an Oracle 22c, so the next release after Oracle 21c will be Oracle 23c, which is currently slated to be the next long term release.

In short, get all your databases to Oracle 19c, and you should probably stick there until Oracle 23c is released, unless you have a compelling case for going to Oracle 21c.

Cheers

Tim…