Oracle related rants (and lots of off-topic stuff)…
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…
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…
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…
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…
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…
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.
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 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:
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…
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…
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.
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 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.
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.
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.
In 21c this approach is a bit different.
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.
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…
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.
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.
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…
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.
First the articles for single instance, Data Guard, RAC and RPM installations.
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.
There are two database builds for 21c database in containers.
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. 🙂
If you were hanging around Twitter a few hours ago you will have seen people tweeting about the release of Oracle 21c for Linux.
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.
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.
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:
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.