Oracle REST Data Services (ORDS) : Database APIs – First Steps

In my never ending quest for automation, I finally got round to looking at the Oracle REST Data Services (ORDS) Database APIs.

These have been around for some time, but I was testing them for the first time using ORDS version 20.2, so I was basing my tests on that version of the documentation, and more importantly version 20 of the APIs.

The are several sets of APIs, and they don’t have the same dependencies or authentication methods. It’s not that big a deal once you know what’s going on, but it confused the hell out of me for a while, and the documentation doesn’t give you much of a steer for some of this.

PDB Lifecycle Management

My first tests were of the PDB Lifecycle Management endpoints. I enable all the relevant features in my normal installation, but there was one big road block. I always install ORDS in the PDB, and this feature only works if ORDS is installed in the root container. This makes sense as the management of PDBs is done at the root container level, but I prefer not to put anything in the root container if I can help it. I uninstalled and reinstalled ORDS so I could give it a go. This resulted in this article.

The PDB Lifecycle Management functionality seemed better suited to a self-contained article, as it is only available from a CDB installation, has its own authentication setup and only has a small number of endpoints. The available APIs are kind-of basic, but they could still be useful. It will be interesting to see if this expands to fit all the possible requirements for a PDB, which are now pretty large. I suspect not.

Most of the other stuff

Next up was “most of the other stuff”. There are too many endpoints to go into any level of detail in a single article, so I figured this should focus on the setup to use most of the other endpoints.

There are two methods of authentication discussed. The default administrator approach, which is good because it hides the database credentials from the user making the API calls. Instead they use application server credentials mapped to the “System Administrator” role. This is similar to that used by the PDB Lifecycle Management feature, except that uses the “SQL Administrator” role, and the ORDS properties are different..

The other approach is to use an ORDS enabled schema. This will be very familiar to people already using ORDS, but it comes with one big disadvantage compared to the previous method. For this functionality you have to expose the database credentials of the ORDS enabled schema to the person calling the API. Normally we would not expose these, instead using another form of authentication (Basic, OAUTH2 etc.) to allow the user to gain access. Even then the ORDS enabled schema would be a weak user that only has access to the specific objects we want it to interact with, but in this case it’s a DBA user, so it makes me nervous. Using the default administrator method the caller is constrained to some extent by the APIs, but with the database credentials they have everything if they have direct access to the database server. It’s probably insignificant when you consider the amount of damage someone could do with the APIs alone, but I feel myself wincing a little when putting DBA credentials into a HTTPS call.

For me as a DBA/Developer I would see myself as the person using these APIs to develop something, whether that was an automation, or an application. If this were to be handed over to a developer to do the work, these security questions may be a much bigger issue.

Having read that, you are probably thinking, just use the default administrator method then. I would, only some APIs don’t work with that method. Some seem to only work with the ORDS enabled schema method for authentication, while others only work with the default administrator method. What’s more, I don’t see any reference to this in the documentation. The API doc doesn’t even mention the default administrator approach, and the setup doc doesn’t mention the limitations on any of the approaches except the PDB lifecycle management. As a result, I think you will need to use a mix of the authentication methods if you plan to use a variety of functionality.

The good thing is they can all live side-by-side. At one point I was testing with a CDB installation of ORDS with credentials for PDB Lifecycle Management, default administrator and ORDS enabled schema authentication all configured at the same time. No problem. It’s just confusing when endpoints fail and you have to “trial and error” your way through them. It would be nice if there was a grid of which groups of endpoints need which type of authentication.

Now I am a noob, so maybe I’ve missed the point here, but I spent a long time trying out variations, and this seems like the way it is. If someone can educate me about why I am wrong I will willingly amend the articles, and this blog post. ๐Ÿ™‚

Thoughts and what next?

At this point I’ve just been finding my feet, and I’m not sure what I will do next. There are some endpoints that interest me, so I might do separate articles on those, and refer back to the setup in the above articles. Then again, it may feel like just regurgitating the API documentation, so I may not. It’s worth taking a look at the available endpoints, broken down into these main sections.

  • Clusterware CLIs
  • Data Dictionary
  • Environment
  • Fleet Patching and Provisioning
  • General
  • Monitoring
  • Performance
  • Pluggable Database Lifecycle Management

Some will require additional setup, but many will not.

From the look of it, the vast majority of the endpoints are for reporting purposes. There are far fewer that actually allow you to manipulate the contents of the database. You can always write your own services for that, or use REST Enabled SQL to do it I guess. The question will be, can I get enough value out of these APIs as they stand to warrant the investment in time? I’m not sure at this point.

Cheers

Tim…

PS. If you were watching my twitter feed over the weekend and wondered what bit of tech I gave up on. It was this. I’m very stubborn though, so I came back…

Video : Multitenant : Memory Resource Management for Pluggable Databases (PDBs)

In today’s video we’ll discuss how Resource Manager allows us to manage the memory usage in PDBs. This can be useful to stop a small number of PDBs using all memory assigned to the instance.

This video was based on this article.

You might also find these useful.

The star of today’s video is Emanuel Oliveira. He wanted his clip to go on a video about machine learning, but I suggested I may not be the guy to do that… ๐Ÿ™‚

Cheers

Tim…

Packer by HashiCorp : Second Steps?

In a previous post I mentioned my first steps with Packer by HashiCorp. This is a brief update to that post.

I’ve created a new box called “oracle-7” for Oracle Linux 7 + UEK. This will track the latest OL7 spin. You can find it on Vagrant Cloud here.

I’ve altered all my OL7 Vagrant builds to use this box now.

You will see a new sub-directory called “ol7” under the “packer” directory. This contains the Packer build for this new image.

Cheers

Tim…

Video : Multitenant : Dynamic CPU Scaling – Resource Manager Control of CPU using CPU_COUNT and CPU_MIN_COUNT

In today’s video we’ll discuss how Resource Manager can control CPU usage in PDBs using the CPU_COUNT and CPU_MIN_COUNT parameters. Oracle call this Dynamic CPU Scaling. This can be useful to stop a small number of PDBs using all CPU resources assigned to the instance.

This video is based on the following article.

Most of this information was in my instance caging article, but I’ve moved it into this separate article now.

You might also find these useful.

The star of today’s video is Bailey. He has a human called Connor McDonald. I suspect Bailey got is human to voice the video…

Cheers

Tim…

Do you know where your installation media is?

This was inspired by a Twitter comment and subsequent DMs, but I’m not going to name names. You know who you are… ๐Ÿ™‚

Let me ask you some questions.

  • Do you have an archive of all your OS installation media?
  • Do you have an archive of all your software versions?
  • Do you have an archive of the patches you’ve downloaded over the years, along with any supporting tools?

If you answer “No” to any of those questions, you probably need to rethink your approach to managing your software.

Why?

You never know when there will be a catastrophic event and you will need to rebuild something. If you don’t have the exact software, you might not be able to get your system up and running again.

Don’t even get me started on build automation and/or documentation…

But I only use the latest software, so I can download it again!

I’m tempted to scream, “Liar!”

Every company I’ve worked for over the last 25+ years has had a mix of products, including some out of support old crap they try not to talk about. If they say they don’t, they are either a new startup, or they are lying.

But I can contact the vendor and get the media!

Can you? Do you know that? In the past I’ve had to open service requests to get old versions of the Oracle database software, and I’ve never been told no yet, but that’s a big risk to take. There is nothing to stop a vendor from hitting the delete key and making it impossible for you to get a copy of that software from them in future.

This is especially important if you are running old versions of products that are out of support.

When should I purge my archive?

I’m tempted to say never, but lets put a few ground rules in place.

  • A piece of software can only be removed if it is not used in your company anymore.
  • That includes offsite backups that might need the software if a rebuild were needed to allow you to restore/recover the backup. Some places keep old backups for several years, so this could be a long time.
  • For vendors, only when you can 100% guarantee the last of your customers has stopped using that version of the software. 100% guarantee. That probably means never.

Vendors: But we can rebuild that version using our build process!

Shut up. You need to keep all your build artefacts. You can’t guarantee that several years later your build process will be able to build exactly what you need. I know you kid yourself you can, but I think you are probably wrong. Just keep the bloody build artefacts.

What do I do?

I’m sure what I do is not perfect, but it’s pretty good. At work I have all the software we use. For each product version there is a directory containing the base installation media, along with sub-directories for all the patches we’ve downloaded, which includes any supporting tools. In the case of Oracle database software that will include the latest version of OPatch and tools like the PreUpgrade.jar etc.

Conclusion

It is your responsibility to keep hold of all your installation media and patches. If you don’t and a vendor won’t/can’t give you a download, you only have yourself to blame.

I don’t agree with vendors ever deleting old versions of their software, but you have to protect yourself against them potentially doing that.

Cheers

Tim…

PS. Don’t ask me to send you old copies of stuff. It is illegal to do that, and I may not have what you are looking for anyway…

PPS. I reserve the right to post a, “I’ve messed up”, post next week when something happens and I don’t have the software. But at least I’ve tried… ๐Ÿ™‚

PPPS. Just to prove nothing is ever truly original these days, see Jon Adams’ post on a similar subject here. ๐Ÿ™‚

Video : Instance Caging to Manage CPU Usage

In today’s video we’ll discuss instance caging to manage CPU usage. This can be useful when we are trying to consolidate multiple instances on a single server.

This video is based on the following article.

The star of today’s video is the beard belonging to Victor Torres. I feel totally inadequate with my patchy stubble… ๐Ÿ™‚

Cheers

Tim…

Packer by HashiCorp : First Steps

A few days ago I wrote about some Vagrant Box Drama I was having. Martin Bach replied saying I should build my own Vagrant boxes. I’ve built Vagrant boxes manually before, as shown here.

The manual process is just boring, so I’ve tended to use other people’s Vagrant boxes, like “bento/oracle-8”, but then you are at the mercy of what they decide to include/exclude in their box. Martin replied again saying,

“Actually I thought the same until I finally managed to get around automating the whole lots with Packer and Ansible. Works like a dream now and with minimum effort”

Martin Bach

So that kind-of shamed me into taking a look at Packer. ๐Ÿ™‚

I’d seen Packer before, but had not really spent any time playing with it, because I didn’t plan on being in the business of maintaining Vagrant box images. Recent events made me revisit that decision a little.

So over the weekend I spent some time playing with Packer. Packer can build all sorts of images, including Vagrant boxes (VirtualBox, VMware, Hyper-V etc.) and images for Cloud providers such as AWS, Azure and Oracle Cloud. I focused on trying to build a Vagrant box for Oracle Linux 8.2 + UEK, and only for a VirtualBox provider, as that’s what I needed.

The Packer docs are “functional”, but not that useful in my opinion. I got a lot more value from Google and digging around other people’s GitHub builds. As usual, you never find quite what you’re looking for, but there are pieces of interest, and ideas you can play with. I was kind-of hoping I could fork someone else’s repository and go from there, but it didn’t work out that way…

It was surprisingly easy to get something up and running. The biggest issue is time. You are doing a Kickstart installation for each test. Even for minimal installations that takes a while to complete, before you get to the point where you are testing your new “tweak”. If you can muscle your way through the boredom, you quickly get to something kind-of useful.

Eventually I got to something I was happy with and tested a bunch of my Vagrant builds against it, and it all seemed fine, so I then uploaded it to Vagrant Cloud.

I’ve already made some changes and uploaded a new version. ๐Ÿ™‚

You will see a couple of older manually built boxes of mine under oraclebase. I’ll probably end up deleting those as they are possibly confusing, and definitely not maintained.

I’ve also altered all my OL8 Vagrant builds to use this box now.

You will also see a new sub-directory called “packer”. I think you can guess what’s in there. If I start to do more with this I may move it to its own repository, but for now this is fine.

I’m not really sure what else I will do with Packer from here. I will probably do an Oracle Linux 7 build, which will be very similar to what I already have. This first image is pretty large, as I’ve not paid much attention to reducing it’s size. I’ve looked at what some other builds do, and I’m not sure I agree with some of the stuff they remove. I’m sure I will alter my opinion on this over time.

I’m making no promises about these boxes. That same way I make no promised about any of my GitHub stuff. It’s stuff I’m playing around with, and I will mostly try to keep it up to date, but I’m not an expert and it’s not my job to maintain this. It’s just something that is useful for me, and if you like it, great. If not, there are lots of other places to look for inspiration. ๐Ÿ™‚

Cheers

Tim…

Video : Simple Oracle Document Access (SODA) for SQLcl

In today’s video we give a demonstration of Simple Oracle Document Access (SODA) for SQLcl. SODA is a feature of Oracle REST Data Services (ORDS),
but this allows to access the document store directly from SQLcl.

This video is based on the following article.

You might find these useful.

The star of today’s video is Kris Rice, who’s essentially singing “dot com” underwater. ๐Ÿ™‚

Cheers

Tim…

Video : Simple Oracle Document Access (SODA) for PL/SQL

In today’s video we give a demonstration of Simple Oracle Document Access (SODA) for PL/SQL. SODA is a feature of Oracle REST Data Services (ORDS),
but this PL/SQL interface for SODA was introduced in Oracle Database 18c.

The video is based on this article.

You might find these useful.

The star of today’s video is the son of Dan Iverson. Dan, not his son, is an Oracle ACE focusing on PeopleSoft and Oracle Cloud Architect. He’s also in Army National Guard, which means he’s already prepared for the zombie apocalypse! ๐Ÿ™‚

Cheers

Tim…

Vagrant Box Drama

I had a little bit of VirtualBox and Vagrant drama today.

I was doing my normal thing of recreating some test VMs and I started to get errors like this during the first part of the VM build, before the config scripts ran.

==> default: Machine booted and ready!
Got different reports about installed GuestAdditions version:
Virtualbox on your host claims:
VBoxService inside the vm claims: 6.1.12
Going on, assuming VBoxService is correctโ€ฆ
[default] GuestAdditions seems to be installed (6.1.12) correctly, but not running.
Got different reports about installed GuestAdditions version:
Virtualbox on your host claims:
VBoxService inside the vm claims: 6.1.12
Going on, assuming VBoxService is correctโ€ฆ
Redirecting to /bin/systemctl start vboxadd.service
Job for vboxadd.service failed because the control process exited with error code.
See "systemctl status vboxadd.service" and "journalctl -xe" for details.
Redirecting to /bin/systemctl start vboxadd-service.service
Job for vboxadd-service.service failed because the control process exited with error code.
See "systemctl status vboxadd-service.service" and "journalctl -xe" for details.
Got different reports about installed GuestAdditions version:
Virtualbox on your host claims:
VBoxService inside the vm claims: 6.1.12
Going on, assuming VBoxService is correctโ€ฆ
VirtualBox Guest Additions: Starting.
VirtualBox Guest Additions: Building the VirtualBox Guest Additions kernel
modules. This may take a while.
VirtualBox Guest Additions: To build modules for other installed kernels, run
VirtualBox Guest Additions: /sbin/rcvboxadd quicksetup
VirtualBox Guest Additions: or
VirtualBox Guest Additions: /sbin/rcvboxadd quicksetup all
VirtualBox Guest Additions: Kernel headers not found for target kernel
5.4.17-2011.5.3.el8uek.x86_64. Please install them and execute
/sbin/rcvboxadd setup
ValueError: File context for /opt/VBoxGuestAdditions-6.1.12/other/mount.vboxsf already defined
modprobe vboxguest failed
The log file /var/log/vboxadd-setup.log may contain further information.
==> default: Checking for guest additions in VMโ€ฆ
default: No guest additions were detected on the base box for this VM! Guest
default: additions are required for forwarded ports, shared folders, host only
default: networking, and more. If SSH fails on this machine, please install
default: the guest additions and repackage the box to continue.
default:
default: This is not an error message; everything may continue to work properly,
default: in which case you may ignore this message.
The following SSH command responded with a non-zero exit status.
Vagrant assumes that this means the command failed!
/usr/sbin/rcvboxadd setup
Stdout from the command:
VirtualBox Guest Additions: Starting.
VirtualBox Guest Additions: Building the VirtualBox Guest Additions kernel
modules. This may take a while.
VirtualBox Guest Additions: To build modules for other installed kernels, run
VirtualBox Guest Additions: /sbin/rcvboxadd quicksetup
VirtualBox Guest Additions: or
VirtualBox Guest Additions: /sbin/rcvboxadd quicksetup all
VirtualBox Guest Additions: Kernel headers not found for target kernel
5.4.17-2011.5.3.el8uek.x86_64. Please install them and execute
/sbin/rcvboxadd setup
Stderr from the command:
ValueError: File context for /opt/VBoxGuestAdditions-6.1.12/other/mount.vboxsf already defined
modprobe vboxguest failed
The log file /var/log/vboxadd-setup.log may contain further information.

The VM had booted, but because the guest additions weren’t working it couldn’t mount the shared folders, so none of the setup scripts had run.

Reading the output I figured the kernel-uek-devel package was missing from the Vagrant box, so I did the following…

I connected to the VM, installed the “kernel-uek-devel” package and exited from the VM.

vagrant ssh

sudo dnf install -y kernel-uek-devel
exit

Then I restarted the VM.

vagrant halt
vagrant up

During this second startup of the VM the problem kernel module was rebuilt, and the rest of the configuration steps ran the way you would expect from a normal first-time startup.

One of the issues about using someone else’s Vagrant box is you are at the mercy of what they decide to do with it. In this case I was using the ‘bento/oracle-8’ Vagrant box, which was built using Oracle Linux 8.2 with UEK 6, but the installed guest additions were not happy, and the packages were not present to allow the kernel module to be rebuilt on the fly.

If you are trying to use my Vagrant builds, which mostly use the ‘bento/oracle-8’ Vagrant box, and you are getting this type of issue, now you know what to do about it. Hopefully the next release of this Vagrant box will be less problematic.

Cheers

Tim…

Update: I spent some time figuring out Packer, and now I’ve switched all my OL8 builds to use my own image called ‘oraclebase/oracle-8’.