Video : Data Pump Checksums in Oracle Database 21c

In today’s video we demonstrate the use of Data Pump checksums in Oracle Database 21c.

The video is based on part of this article.

The star of today’s video is Franck Pachot, who is checking out his escape routes. 🙂

Cheers

Tim…

Video : RANK and DENSE_RANK Aggregate Functions : Making Ranking Predictions

In today’s video we demonstrate the RANK and DENSE_RANK aggregate functions, using them to make ranking predictions.

The video is based on part of this analytic functions article.

RANK and DENSE_RANK Analytic Functions

You might find these useful.

The star of today’s video is Kim Berg Hansen, who is an all round SQL and PL/SQL good guy, as well as a tireless contributor to the Dev Gym.

Cheers

Tim…

What architecture are you using for your production Oracle databases? (Poll Results Discussed)

I was having an email discussion with the folks from DBmarlin, and the question came up about the adoption of the multitenant architecture. Were people using the multitenant architecture, or sticking with the non-CDB architecture for now?

Followers of the blog know I threw my hat in with the multitenant architecture from Oracle 12.1.0.2 onward. We use Oracle 19c for everything of value now (there are a couple of dead projects still on 11.2) and all but one of those projects use PDBs. Suspecting I was not the norm here, I asked some questions on Twitter.

As usual I’m going to start with some caveats. The sample size is small. People who interact about tech on social media my not be a diverse sample. I’m going to act like these results are representative of the wider scene, but they may not be.

Here is the first question.

What architecture are you using for your production Oracle databases?

The fact that only 33% were on non-CDB only was a positive sign in my view. The introduction of the multitenant architecture made a lot of people nervous, and it was not without its problems in the early days. Even so, the combined total of only non-CDB and mostly non-CDB is still 50% of respondents. When you consider Oracle 21c makes the multitenant architecture mandatory, and the next long term release is 23c, there is a lot of work for people to do when they make the switch to 23c. The conversion is simple enough. It’s the testing resource that could hurt people.

Having 50% of people using mostly PDBs or all PDBs is a really good sign, and will make life much easier for them when they come to upgrade to the next long term release.

To dig a little deeper I asked this question.

Are you provisioning new production Oracle databases as PDBs?

So just over 65% of people said they are provisioning new Oracle databases as PDBs. That’s very positive, and makes a lot of sense going forward. Why would 35% of people stick with the non-CDB architecture for new databases? Some things I can think of include.

  • They are using an older versions of the database, and don’t have the option of using the multitenant architecture.
  • They want a one-size-fits-all approach to the database, and will convert everything when they are forced to.
  • Vendors don’t support the multitenant architecture. I have one project where I suspect the vendor doesn’t even know the multitenant architecture exists, let alone supports it.
  • Internal development teams haven’t caught up with the database version. From my experience, the only thing that was really affected by our move to PBDs was CRON jobs using OS authentication. We switched to using secure external password stores and everything was fine. I wrote an article on possible solutions to the OS authentication issue here.

If I was not working at my company, I don’t believe they would have been running on 19c with PDBs. I’ve been pushing for many years to improve the attitudes to upgrades and patching. The easy path is to do nothing…

Finally I wondered how many people were purchasing the multitenant option. Remember, from 19c onward you can run up to 3 user-defined PDBs without having to buy the multitenant option.

For those people using PDBs for production Oracle databases, have you bought the Multitenant Option?

At 35%, I’m actually surprised how many people have purchased the multitenant option. I expected it to be a lot lower. Don’t get me wrong, I think the multitenant architecture is fine. I’ve been advocating for people to switch to it and use lone-pdb since it was introduced on Oracle 12.1. I would like to use more than 3 PDBs per 19c instance, but I can’t justify the cost for a feature that I could argue should be free in all editions.

So there you have it. A quick snapshot of what my followers are saying.

Remember, the multitenant architecture is mandatory from Oracle 21c onward, with Oracle 23c being the next long term support release, so you are going to have to get comfortable with this stuff if you want to remain in support long term.

If you want any help getting to grips with the multitenant architecture I have a load of articles and videos.

Cheers

Tim…

Video : SQLcl : Data Pump (Part 2)

In today’s video we demonstrate table-level export and imports using the integration between SQLcl and Data Pump.

This video builds on the first video, so it makes sense to watch that first.

Both these videos are based on this article.

I have a few articles on other SQLcl features, which you can find here.

The star of today’s video is Craig Shallahamer. Craig is the only person I know who defies the first law of thermodynamics, as he’s able to produce more energy than he consumes. If you’ve seen him teach or present, you’ll know what I mean. If you haven’t, you really should! 🙂

Cheers

Tim…

Fedora 36 and Oracle

Fedora 36 was released recently. Here comes the standard warning.

Here are the usual things I do when a new version of Fedora comes out.

Why do I do this? As mentioned in the first link, Fedora is a proving ground for future versions of RHEL, and therefore Oracle Linux. I like to see what is coming around the corner. Doing this has no “real world” value, but I’m a geek, and this is what geeks do. 🙂

As an aside, when Fedora 35 was released I was having a lot of trouble getting 19c and 21c installed on it. I tried a number of times over the course of a few weeks and failed each time. When I tried those same installations on Fedora 36 they just worked, so I went back and tried on Fedora 35 again, and they worked there too. Clearly there have been some changes to underlying Fedora 35 packages that have fixed whatever the problem was with the Oracle installations. As a result, I also produced these.

Now that Fedora 36 exists, these Fedora 35 installations are not really necessary, but it’s nice to do them for the sake of completeness.

I pushed Vagrant builds to my GitHub.

If you want to try these out, you will need to build the base Vagrant boxes using Packer. You can find the Packer builds on my GitHub too.

So now you know how to do it, please don’t! 🙂

What’s New?

So what’s new with Fedora 36? It’s a bleeding edge distribution, so as you might expect, loads of package version updates, bringing most things to the latest and greatest versions. The things that stand out for me are Ansible 5 and Podman 4.0. If you want a more complete perspective on this, you might want to look here.

Cheers

Tim…

ORDS : Migration of Legacy Configuration – Why You Shouldn’t Do It!

Just in case you didn’t get the memo, the installation and configuration of ORDS has changed in version 22.1 onward. As well has the changes to the command line, there is also a different structure for the configuration files. ORDS gives you an option to migrate your existing configuration from the legacy format to the new format, which is good right? Maybe not…

Let me start by saying, there is absolutely nothing wrong with doing this conversion if that’s what you want, but I’m going to explain why it’s a bad idea from my perspective. You are allowed to disagree, and I would like to hear your reasons, but I’m going to explain why I think you shouldn’t do this…

Build Scripts

We run ORDS using Tomcat in Docker containers. Each instance gets one or more ORDS containers. There is a load balancer in front of ORDS to handle CA certificates etc.

Every time we have an update of ORDS, Java, Tomcat or SQLcl, we build a new image, throw away the old containers and create new containers from the new image. As a result, it’s really important we are able to rebuild everything cleanly. The Docker image build is essentially our build script. Even if you don’t use containers, I would suggest you have a build script for everything. You want to be sure that in a nightmare scenario, you can rebuild everything quickly and cleanly.

What’s this got to do with migrating the old configuration? What is your build script? Build and configure in the old version of ORDS and convert the config to the new version? I don’t think so. IMHO you need to invest a little time and create new build scripts using the new version of ORDS. You can’t rely on a bunch of commands you ran in an old version of the product…

But we have a complex build!

I can hear the cries of, “but we have a complex build, so the conversion is easier!” If you have a complex build, replacing it is even harder should something go wrong, so having a version controlled build script is even more important for you than people with simple builds. I wouldn’t be happy about going live with something I couldn’t rebuild. Added to that, the time and effort of getting to grips with the new command line will teach you more about the product, which allows you to support it better.

Web Service Definitions

As I’ve said before, the changes for ORDS are mostly around installation and configuration. Your existing web service definitions will work just fine. They are stored in the database, and will remain untouched by the upgrade. You still need version controlled scripts for their definitions, but you shouldn’t need to run any of them when upgrading ORDS.

So when I’m talking about the migration of config, I am not talking about your web service definitions. I’m talking about the installation and configuration of ORDS at the top-level.

Conclusion

I completely understand why this conversion option exists. Someone will want to use it and giving people choices is typically a good thing. I just don’t think people should use it.

Remember, my thoughts are based on my experience of using ORDS, and I understand that can be a very limited view point. I would be interested to know if someone has a compelling reason why my view on this is wrong. It’s unlikely what you say will alter the way we use ORDS, but it’s always good to know of different approaches, because you never know when they will come in handy in the future…

Tim…

Oracle REST Data Services (ORDS) 22.1 : Article Updates

A couple of weeks ago I wrote about the big changes that have come with version 22.1 of ORDS (here). I mentioned that the changes meant I had to revisit a bunch of my content. I think that process is done now.

As I predicted, there are three new articles so far. I mentioned the first of these in my last post.

These had changed so much is made sense to do them as new articles, rather than trying to cope the “pre-22.1” and “22.1 onward” approaches in one article.

Since the changes are in the configuration, rather than feature usage, most of the other articles needed less work. Typically just references about how to enable/disable features. I’ve worked through all the articles now and I think I’ve sorted everything. I’m sure over time I’ll spot other things I want to add or edit. 🙂 Here’s a link to all the ORDS articles on my website.

ORDS : All Articles

Cheers

Tim…

ORDS and APEX 22.1 : Vagrant and Docker Builds

I was on top of the recent ORDS 22.1 release, but somehow I managed to miss the APEX 22.1 release. Here’s an update on what’s been going on with my builds…

Vagrant Builds

A number of my Vagrant database builds include APEX. Those that do have been updated to use APEX 22.1. You can find them here.

They had already been updated to use the latest versions of Java, Tomcat, ORDS and SQLcl where appropriate, so the APEX upgrade was a small change. The ORDS update was done 11 days ago. It was a bigger update, as the installation process for ORDS 22.1 has changed quite a bit. I wrote about that here.

You can read my beginner’s guide to Vagrant here.

Docker/Container Builds

I have some ORDS containers that include the APEX images, so those image builds have been updated to use ORDS 22.1 and APEX 22.1 images now.

APEX is also included in the database builds. You can find them here.

I was a little slow to add the updates to the Docker builds. The Tomcat and SQLcl changes were done a couple of weeks ago, but I’ve only added the Java, ORDS and APEX changes today. I held back a little on the ORDS 22.1 changes, as I was rewriting a bunch of articles that were affected by the installation and configuration changes.

You can read my Docker/Container articles here.

Real Life

We probably won’t be putting APEX 22.1 live until the next patching cycle, which will be July. In the past I could be quite aggressive about the upgrades, but as APEX is becoming more important in the organisation, the rollout of updates has to be a bit more considered. 🙂

The same will probably be true for the ORDS 22.1 rollout. We run ORDS in containers, so it’s really quick and easy to replace all the infrastructure, but I wanted to be a bit cautious because of all the changes in the new version. Having played with it a bit now, I’m feeling a lot more confident, but at the time we were starting our patching cycle I was too nervous to include it.

As far as home systems are concerned, I only use the latest and greatest, unless there is a compelling reason not to. 😉

Cheers

Tim…

Oracle REST Data Services (ORDS) 22.1 : All Change!

You may have heard version 22.1 of Oracle REST Data Services (ORDS) has been released. For the versions between 3.0 and 21.4 the installation process was pretty much the same. From version 22.1 it’s out with the old and in with the new…

I’ve put out an installation article, but remember it’s early days for me, so I will probably be revisiting this over the coming weeks as I learn more.

The Big News

  • We no longer run commands using “ords.war” directly. Instead we use an “ords” script/executable in the “bin” subdirectory. That kind-of makes every installation or configuration article you’ve ever read wrong.
  • The above change means standalone mode is also different, so even starting and stopping ORDS has changed.
  • The configuration location is no longer written into the “ords.war” file, so you have to make sure standalone, Tomcat, WebLogic knows where to find the config.
  • The contents/structure of the configuration has changed, so once again anything you’ve read about configuration has probably changed.

It all sounds quite dramatic, and it certainly confused the hell out of me, but I think a couple of weeks down the line I will forget it was ever any other way. 🙂

I’ve updated one of my Vagrant builds to use the new version. I’ll do the others over time…

Over the next few days/weeks I’ve got to visit all my ORDS content (over 30 articles) to check how these changes have impacted it. Off the top of my head I think I’ve got about 3 rewrites to do, and some corrections of other articles.

Don’t Panic

From a usage perspective, ORDS looks the same, so there is no need to panic. It’s just one of those administration evolutions you expect in the lifetime of a product.

Cheers

Tim…

PS. I’ve been making Jeff Smith‘s life miserable regarding the documentation. Thanks for the feedback and changes Jeff. 😉

VirtualBox 6.1.34

VirtualBox 6.1.34 has been released.

The downloads and changelog are in the usual places.

I’ve installed it on Windows 11 and macOS Big Sur hosts with no dramas. Some time in the next 24 hours I’ll upload updated versions of my Oracle Linux 7 and Oracle Linux 8 vagrant boxes.

This is going to get plenty of testing as I run all my Vagrant builds using the latest Oracle patches.

Cheers

Tim…