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…

Working From Home : Again

I just read this article on Wired.

I’ve written about my thoughts on working from home here, but I’m going to have a little rant…

I keep expressing my opinions in meetings, but I’m often countered by various points, which I think are flawed, but I want respond to here.

It’s not healthy to be remote all the time

I tell you what’s also not healthy. Making people go to the office when they don’t want to! Making people do crappy commutes that drain their souls. Making people waste several hours of their day, when they could be doing other things that more positively affect their wellbeing.

If people feel more healthy working from the office, they should work from the office. If they would prefer not to, they should be allowed to make that choice. You can’t use the “healthy” argument in favour of one stance and ignore the “healthy” argument for the other.

Some staff can’t work remotely

No shit Sherlock! I don’t expect an ER doctor to get approval to work from home when all the patients are waiting to be seen in the ER. If a job role is customer facing, then clearly working from home is not an option, but many people in organisations are not customer facing, myself being one of them.

Some people can’t work from home because they don’t have a suitable work space. Those people either have to work from the office, or move house to a place with a suitable work space. You can’t stop other people working from home because Billy lives in a bedsit with his wife and 3 kids…

We don’t want 100% remote work

Fine. I’ll come in for 1 hour a year. Thanks.

Having arbitrary quotas is wrong. It should be based on the person and the role. Remember, I’m not demanding you work from home. I’m saying I want to!

We need to bond as a team

I don’t touch people at work. My bonding is done equally well on a video call. If you want me to “bond with the team”, you can start off by getting rid of all the idiots I dislike, and the people who are not pulling their weight.

This argument is even more flawed when companies agree to working 2-3 days from home. Are you even going to see members of your team on the days you choose to be in? Does the whole team have to be in on the same days? How does that work with hot-desking and space saving? I think this is a weak argument.

We need to do workshops and brain storming meetings in person

Sometimes this is true. Sometimes it’s not. The vast majority of meetings are unproductive, with people wasting time walking between buildings to achieve nothing. It’s actually a lot more efficient to use tools like Zoom or Teams…

I don’t think many people would argue with coming into the office for a specific event if it actually added value, but that is not the same as discussing the same old rubbish for hours on end, that nobody is going to own or progress.

I’m also irritated by people complaining of back-to-back meetings with no breaks. Either don’t accept them, or switch to a 45 minute meeting format. You know you just spend the first 10 minutes waiting for people to turn up anyway. This is another example of a dysfunctional business practice being used to argue the point.

People are scared from a health perspective of returning to the office, we need to show them it is ok

Is anyone else thinking of the scene in Jaws where the Mayor is forcing people into the water to “prove” it is safe? In my company we’ve had several “back to the office” pushes, that have ultimately been cancelled due to new lockdown restrictions. I am not surprised people are worried. I believe they should be.

Me not being in the office makes the office a safer place, because there is one less person who could transmit a virus. I’m happy to work from home, so there is no need to thank me for the service I’m am doing to office health!

People will be lazy

Well, the evidence seems to counter that argument. My own boss has seen an uptick in productivity since we’ve worked from home. So you want us to come back to the office so our team can get less work done?

I covered the flaws in this argument in this post. Suffice to say, if a manager thinks this, it just shows they are a rubbish manager, who manages by presence checking, not actual work done. Grow up!

Conclusion

I know I’m going to get some responses from people saying their preferences, and I would just like you to remember, they are “your preferences”! I would just like to reiterate the following.

  • These are my opinions.
  • I am not forcing everyone to work from home. I am pro-choice in this matter.
  • If you think differently, that is fine, but it doesn’t negate my feelings on this matter!

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. ๐Ÿ™‚

Video : DML Error Logging

In today’s video we demonstrate DML Error Logging, introduced in Oracle 10.2.

The video is based on a section of this article.

The star of today’s video is Oren Nakdimon, who’s chilling in his garden along with the birds. ๐Ÿ™‚

Connor McDonald has done some videos on this feature, which are worth checking out.

Cheers

Tim…

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.

You’ve got to learn to walk before you can run!

This is going to be a rant. If you are not into reading rants, please don’t continue.

I put out a tweet yesterday that said this.

“I’m so done. People can’t follow basic instructions, but expect to jump straight into building something complex on day one, then expect me to help when it inevitably goes wrong. You’ve got to learn to walk before you can run!”

I’ve had a few incidents recently that have nearly brought me to breaking point.

  • Someone wanted to install an Oracle database on Linux, but didn’t even know what an environment variable was. They claimed to have Linux experience, but literally couldn’t grasp what it meant to set or reference an environment variable, even when it was on the page in front of them.
  • Someone was “following” one of my installation guides, and said it was well written and easy to read, but wanted a step-by-step breakdown of what they had to do. What? I know I’m not perfect, but that is literally what the article was.
  • Someone else seemed incapable of pasting code into a shell. Having said they were doing exactly what was in my article, it became clear they were doing nothing of the sort.

These are just three incidents, but they are the tip of the iceberg.

Back in the day there used to be a forum on my website, but I closed it down because I was wasting loads of my time trying to help people, who had no intention of helping themselves. I wrote some posts about it over the years.

Over the years I’ve had several discussions about this with other members of the community. I know this happens a lot to everyone.

Part of me feels really bad, because I’m supposed to be this community guy, but I really can’t cope with people who have not even tried to get some basic skills under their belt before launching into something more complicated. I’m sorry, but if you’ve never seen Linux before, you probably shouldn’t be trying to install Oracle RAC on it. If you’ve never installed a database before, you probably shouldn’t be thinking about installing Cloud Control.

How many people turned up to the Olympics this year with no previous experience and took home a medal? How many people sit university final exams without ever studying the subject before? It sounds bloody stupid right? Yet people expect to do complex tech stuff without any grounding in basic skills.

I don’t know if these people are delusional. I don’t know if their boss is an idiot, and asking them to do something that is clearly beyond their capabilities. I feel sorry for them if they are under pressure to do this, but I can’t work miracles, and I’m not being paid to do their job for them. Simple as that.

I look at my website stats and I’m clearly helping a large number of people, so I think I’m doing my share already. Sorry, but not sorry!

Cheers

Tim…

Continuous Delivery : Is there a problem with the sales pitch?

I saw Simon Haslam point to this very short video of Charity Majors speaking about Continuous Delivery.

This answer to why companies are not using continuous delivery is perfect!

“It’s a failure of will, and a failure of courage on the part of our technical leadership.”

I love this answer, because this is exactly how I feel about it!

After seeing that, it got me thinking about why technical leadership are so disengaged from continuous integration/continuous delivery (CI/CD), and I started to wonder if it was actually a problem with the sales pitch.

Have you ever been in a discussion where you provide compelling evidence for your stance, then say one stupid thing, which allows people with the opposing view to jump all over you, and effectively ignore all the stuff you said previously? Been there! Done that! I wonder if the same thing is happening during the CI/CD sales pitch.

When people write or speak about this stuff, they will often bring up things that provide an instant get-out for people. Let’s imagine I am trying to convince someone that CD is the way forward. I might say things like,

  • Automation means it’s not dependent on a specific person being there to complete the deployment.
  • We can eliminate human error from the delivery process.
  • It makes delivery more reliable, as we have a well tested and proven process.
  • That proven reliability makes both us and our customers more confident that deployments will be successful, so it reduces the fear, uncertainty and doubt that often surround deployments.
  • As a result of all of the above, it makes the delivery process quicker and more efficient.

That all sounds great, and surely seals the deal, but then we hit them with this.

  • Amazon does 23,000 production deployments a day!

And now you’ve lost your audience. The room of people who are scared of change, and will look for any reason to justify their stagnation, will likely go through this thought process.

  • Amazon use CI/CD to get 23,000 production deployments a day.
  • We don’t need to do 23,000 production deployments a day.
  • Therefore we don’t need CI/CD.

I know this sounds stupid, but I’m convinced it happens.

I’ve read a bunch of stuff over the years and I’m invested in this subject, but I still find myself irritated by some of the things I read because they focus on the end result, rather than the core values that got them to that end result. Statements like, “Amazon does 23,000 production deployments a day” or “this is what Twitter does”, are unhelpful to say the least. I feel like the core values should be consistent between companies, even if the end result is very different.

This is just a thought and I could be talking complete crap, but I’m going to try and declutter myself of all this hype bullshit and try to focus on the core values of stuff, and hopefully stop giving people a reason to ignore me…

Cheers

Tim…