Video : DBMS_XPLAN : Compare Execution Plans in Oracle Database 19c and 21c

In today’s video we’ll demonstrate the compare plans routines, added to the DBMS_XPLAN package in Oracle database 19c and 21c.

The video is based on the following article.

The star of today’s video Lumpy the donkey, who is being held by Bjoern Rost.

Cheers

Tim…

Video : ANY_VALUE Aggregate Function in Oracle Database 21c

In today’s video we’ll demonstrate the ANY_VALUE aggregate function, introduced in Oracle database 21c.

The video is based on this article.

The star of today’s video is Scott Spendolini, who I suspect has used APEX at least once or twice in his life…

Cheers

Tim…

Video : Analytic Function Exclude Clause in Oracle Database 21c

In today’s video we’ll demonstrate the analytic function exclude clause, introduced in Oracle database 21c.

The video is based on the following article.

You might also find these useful.

The star of todayโ€™s video is Rene Antunez, who took a few of us to see the Pyramids of Teotihuacan when we were in Mexico on an ACE speaker tour.

Cheers

Timโ€ฆ

Video : Analytic Function Window Clause in Oracle Database 21c

In today’s video we’ll demonstrate the analytic functions window clause, introduced in Oracle database 21c.

The video is based on the following article.

You might also find these useful.

The star of today’s video is Peter Scott, who took time out of his life of luxury in France to say “.com” for this video. ๐Ÿ™‚

Cheers

Tim…

Video : Gradual Database Password Rollover Time

In today’s video we demonstrate gradual database password rollover time, introduced in Oracle database 21c, and backported to 19c.

The video is based on this article.

This is a small, but really useful quality of life feature!

The stars of today’s video are the offspring of Jeff Smith. It was cold, dark and miserable when I recorded the video, and this is one of my favourite clips, so I included it to bring a touch a summer! ๐Ÿ™‚

Cheers

Tim…

Video : Oracle Database 21c Express Edition (XE) Installation

In today’s video we’ll demonstrate how to install Oracle Express Edition 21c on Oracle Linux 8.

The video is based on this article.

Here are some other things you might find useful.

The star of today’s video is Martin Widlake, doing his best Monty Python impression.

Cheers

Tim…

Oracle Database 21c Express Edition (XE) : Article and Vagrant Builds

You probably saw a stream of Tweets yesterday mentioning the release of Oracle database 21c Express Edition (XE) for Linux. It’s a very important release as it’s totally free to use.

Of course I had a play as soon as I got hold of it. If you’ve used one of the RPM installations of Oracle before, there shouldn’t be any surprises. The approach for the XE RPM installation is similar to that of the Enterprise Edition RPM installation. Here’s a quick run through.

I also added a couple of Vagrant builds for Oracle 21c XE on Oracle Linux 7 and 8.

I suspect this release has made a lot of people very happy! ๐Ÿ™‚

Cheers

Tim…

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…

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…