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…

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. πŸ˜‰

ORDS, SQLcl, SQL Developer and SQL Data Modeler New Releases (21.4.x)

We’ve got some early Christmas presents!

I’ve updated my Docker and Vagrant builds to use the new versions of ORDS and SQLcl. You can see them here.

You can read Jeff Smith‘s announcement posts here, which include an overview of some of the new features.

Only the SQL Developer and Data Modeler releases were as a result the Log4j issue. They are client tools, so they are not really a threat, but it’s good to get the updates through so fast.

You have permission to open your presents early! 😁

Cheers

Tim…

Updates to Vagrant and Docker Builds : ORDS and SQLcl 21.2

The 21.2 version of ORDS and SQLcl dropped at the start of the month. I guess I missed that, as the first I noticed was Alex Nuijten talking about SQLcl 21.2 nearly two weeks later. As soon as I realised they had arrived I downloaded them and went to work.

All of the relevant Vagrant and Docker builds were updated to use ORDS 21.2, SQLcl 21.2 and Tomcat 9.0.50.

You can check out what’s new in this post by Jeff Smith.

The Oracle security patches come out next week, so these builds will be updated again to include the latest versions of OpenJDK (AdoptOpenJDK) and the Oracle database patches where necessary.

SQL Developer and Database Modeler

These aren’t anything to do with my builds, but thought is was worth mentioning. The 21.2 version of SQL Developer and Data Modeler have been available for the last few days. You can read Jeff’s announcement here.

Cheers

Tim…

Vagrant and Docker Builds: ORDS 20.4, SQLcl 20.4 and Database Patches

The January Oracle quarterly patches were released yesterday, which prompted me to do some new builds.

We got Oracle REST Data Services (ORDS) 20.4 and SQLcl 20.4, which I use in a number of my Vagrant and Docker builds, so I updated them and ran some builds.

The Vagrant database builds, which include ORDS, can be found here.

The Docker ORDS builds can be found here.

I also updated Tomcat to 9.0.41 and OpenJDK to 11.0.10_9 from AdoptOpenJDK.

Once I finished those I decided to try out the Oracle database 19c (19.10) OJVM+DB combo patch on a single instance build. That went fine. You can see that build here.

Since that went OK, I figured it was worth trying to update my OL8 19c RAC build with the 19.10 OJVM+GI combo patch. That also went fine. You can see that build there.

I wasn’t really expecting to cover so much ground so quickly, but that’s the great thing about automation. πŸ™‚

Tomorrow I’ve got to start putting together all the patch scripts for work. It’s always a bit tedious because I have to deal with a lot more products and variations, and I have to make sure I don’t screw up. Happy patching… πŸ™‚

Cheers

Tim…

PS. If you are interesting in ORDS, SQLcl, Vagrant or Docker, these might help.

Vagrant & Docker Builds : ORDS and SQLcl 20.3

In a previous post I discussed the recent release of APEX 20.2 and the subsequent builds it triggered. Last night I pulled down ORDS 20.3 and SQLcl 20.3, so I updated my Vagrant and Docker builds again.

The ORDS download page is here. At the time of writing, the SQLcl download page is still showing 20.2, but the SQL Developer download page has a link for the 20.3 SQLcl download at the bottom. Both these versions have been available for about a week. Update: It’s showing 20.3 on both SQLcl pages now. πŸ™‚

Vagrant

All my GitHub Vagrant builds that include ORDS and SQLcl have been updated to use version 20.3.

I had previously updated Tomcat, and a few days ago I updated Java as soon as AdoptOpenJDK was available.

Docker

This is pretty much the same as the Vagrant story.

The relevant GitHub Docker builds, like the ORDS containers, have been updated to include ORDS and SQLcl 20.3.

They are on the latest release of Tomcat and Java from AdoptOpenJDK.

Conclusion

As always, this is made simple using automation! πŸ™‚

Cheers

Tim…

Data Pump : API for PL/SQL (DBMS_DATAPUMP) and REST (ORDS)

I’m on holiday, so it’s a lot easier to tick things off my giant list at the moment. πŸ™‚

Someone asked me about the DBMS_DATAPUMP package, which is a PL/SQL API for Data Pump. I knew I had written about it, but it turned out I only had one little example in my main Data Pump article here. I put it on my list to expand it a little with some other common examples. I finally got round to this here.

It’s by no means an exhaustive list of what the DBMS_DATAPUMP package can do, but if someone new is coming to it, it puts a bit more meat on the bones compared to what I had before.

One of the reasons that moved that up the list was I was thinking about looking at the ORDS Database API endpoints related to Data Pump, which resulted in this.

That was a rather odd experience for a few of reasons.

  • There were some issues with authentication. I expected it to work with both the default administrator and an ORDS enabled schema, but it only worked with the latter. That was confusing the hell out of me for a time. I’m told it will work with both in the future…
  • There are two ways to use Data Pump. There is a REST endpoint “/database/datapump/jobs/” that can perform all the actions, and there are two RPC-style endpoints “/database/datapump/export” and “/database/datapump/import”. I decided that RPC is not for me and chose to ignore it. I’m a lot happier with the REST endpoint.
  • The API has extremely limited functionality at the moment.

I guess I was expecting this to feel like a wrapper over the DBMS_DATAPUMP package, but it is not like that at all. In this version it’s an API that covers a few basic tasks and completely locks you out of everything else. I can’t imagine many DBAs being able to use it, but it might be ideal for some developers if you are OK with giving them DBA credentials to the database. I wrote some of my thoughts/concerns at the bottom of the article here.

Despite my reservations, this is effectively version 1.0 of this functionality. I would suggest people try it out and feedback their requirements to the ORDS folks, so it can be improved.

On a more general note, it’s clear the ORDS Database API needs a rethink in terms of access management. It seems to be a bit of an all or nothing at the moment. I think it’s safe to say that as the APIs expand, there will be a range of people needing to use them, each with very different security constraints. I might want someone to be able to perform table or schema-level exports/imports, but I will want to control what they can do, and by giving them the access to do this, it doesn’t mean I want them to be able to run DBCA commands to create new instances as well. I think you get my point. It would seem like the access to the APIs need to respond to the underlying roles and privileges for the user, not using a separate ORDS parameter mechanism. Just my thought…

Cheers

Tim…

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 : 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…