Video : DBMS_CLOUD : External Tables

In today’s video we’ll demonstrate the functionality of the DBMS_CLOUD package, with specific reference to external tables based on objects in a cloud object store.

The video is based on a section in this article.

You may find these useful.

The star of today’s video is Alex Zaballa, who has got to be one of the most certified people I’ve ever met. πŸ™‚

Cheers

Tim…

Video : DBMS_CLOUD : Objects and Files

In today’s video we’ll demonstrate the functionality of the DBMS_CLOUD package, with specific reference to objects in a cloud object store and files on the database server file system.

The video is based on part of this article.

You might find these useful also.

The star of today’s video is Roel Hartman, who used to do APEX, but now just runs marathons I think… πŸ™‚

Cheers

Tim…

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

Database Patching Revisited : Take off and nuke the entire site from orbit…

I was reading a post by Pete Finnigan the other day.

I put out a tweet mentioning it, and linking to one of my old posts on the subject too.

This started a bit of a debate on Twitter about how people patch their databases. In this post I want to touch on a few points that came out Pete’s post an some of the other Twitter comments.

You have to have a plan!

An extremely important point made by Pete was you have to have a plan. That doesn’t have to be the same for everyone, and there may be compromises due to constraints in your company, but that doesn’t stop you making a plan. Your plan might be:

  • We will start a new round of patching immediately when a new on-off patch is released, and every quarter with the security announcements. I can’t see how this is possible.
  • We will patch every quarter with the security announcements. That’s what my company does.
  • We will patch once per (six months, year etc.)

Hopefully your plan will not be:

  • We will never patch and person X will take the blame when we have a problem.

Release Updates (RUs) or Release Update Revisions (RURs)

Database quarterly patches are classified as release updates (RUs) and release update revisions (RURs). First let’s explain what they are.

  • Release Updates (RUs) : These are like the old proactive bundle patches. They contain bug fixes, security fixes and limited new features. Let’s call that “extra stuff”. In 19c the blockchain tables and immutable tables features were introduced in RUs. Backporting and new features can introduce new risks.
  • Release Update Revisions (RURs) : These are just bug fixes and security fixes. In theory these are safer than RUs as less new stuff is introduced, but… See below.

So from first glance you are saying to yourself I want the safest option, so I want to go for RURs. The problem is RURs aren’t like the old security patches that you could continue applying forever. Ultimately you have to include all the “extra stuff” from the previous RUs, but you get the option of doing it later. This page in the documentation explains things quite well.

This table from that link is quite useful, showing you what version you will be on during a quarterly patching cycle.

What does this mean?

  • If you patch using the RUs, you are going to the latest and greatest each quarter.
  • If you use RUR-1, you are constantly 1 quarter behind on the RUs extra content, but you add in the missing bug fixes and security fixes using the RUR-1 patch.
  • If you use RUR-2, you are constantly 2 quarters behind on the RUs extra content, but you add in the missing bug fixes and security fixes using the RUR-2 patch.

In all cases you have the latest bug fixes and security fixes. You are just delaying getting the “extra bits”. So at first glance it seems like you might as well go with the RUs. The issue is some of the RUs are a bit buggy. If you go for the RUR-1 or RUR-2 there is a chance the bugs introduced in the base RU have been fixed in the subsequent RURs for that RU. So we could say this.

  • RUs: Oracle have zero time to identify and fix the bugs they’ve introduced in the RU.
  • RUR-1: Oracle have 3 months to find and fix the bugs they’ve introduced in the base RU.
  • RUR-2: Oracle have 6 months to find and fix the bugs they’ve introduced in the base RU.

I tend to stick with the RUs, although I am considering changing. Ilmar Kerm said he’s found RUs too buggy and tends to stick with the RUR-1 approach. I guess a more conservative approach would be to stick with the RUR-2 approach.

Your experience of the RUs verses the RURs will depend on what features you use, what extra stuff Oracle decide to include in the RU and what they break by including that extra stuff. The biggest problem I got was 19.10 breaking hot-cloning of PDBs, which was kind-of important. If I had used the RUR-1 approach I would never have seen that issue. Different people using different features see different bugs.

How good is your testing?

The biggest factor in the decision of which approach to take is probably the quality of your testing.

  • If your testing of applications against new patches is good, you can probably stick with the RUs. If the RU fails testing, go with the RUR-1 that quarter.
  • If you just work on the “generally considered safe” approach, meaning you apply the patches and don’t do any testing, maybe you should be using the RUR-1 or RUR-2 approach!
  • The ultra-conservative approach would be to stick with the RUR-2 approach.

Just patch!

Regardless of which approach you take, you’ve got to have a plan, and you should be patching. I know some of you don’t care about patching, and you are fools. I know some of you would like to patch, but your companies are dinosaurs. All I can say to you is keep trying.

In my current company we never used to patch. I spent years sending out quarterly reports summarising all the vulnerabilities in our systems and still nothing. Eventually a few other people jumped on the bandwagon, we had a couple of embarrassing issues, and the constant threat of GDPR gave us some more leverage. Now we have a quarterly patching schedule for all our databases and middle tier servers. We are not perfect, but it can be done.

Even now, we still have questions like, “can we miss out this quarter?”, but we push back very hard against this. One quarter becomes two, becomes three, becomes never.

New patches on the 20th July (see here). Good luck everyone!

Cheers

Tim…

PS. If you are not patching externally facing WebLogic servers you might as well close your company now. You have already given all your data away. Good luck with that GDPR fine…

Database Upgrades : It’s been a long road getting from there to here

Please play the Star Trek – Enterprise theme while you are reading this post. πŸ™‚

I’ve mentioned database upgrades a few times over the last year or more. Like many others, we are pushing hard to get everything upgraded to 19c. Over the last couple of weeks a bunch more systems got upgraded, and we are now looking like this.

The remaining 11.2 and 12.1 databases are all in various stages of migration/upgrade. I would not curse us by giving a deadline for the final databases, but hopefully soon!

The reason for mentioning that theme song is it starts with the words, “It’s been a long road getting from there to here”, and that is exactly how it feels.

Many of the database upgrades are technically simple, but the projects surrounding them are soul destroying. Getting all the relevant people to agree and provide the necessary resources can be really painful. This is especially true for “mature” projects, where the, “if it ain’t broke, don’t fix it”, mentality is strong. I wrote about the problems with that mentality here.

I’m not going to give you any blinding insights into how to do your database upgrades, because every upgrade is potentially unique, as I discussed here.

We always go for the multitenant architecture (CDB/PDB) unless there is a compelling reason not to. I think we only have one non-CDB installation of 19c because of a vendor issue. None of our other 3rd party applications have had a problem with using PDBs, provided we’ve made sure they connect with the service, not a SID. We don’t use the USE_SID_AS_SERVICE_listener_name parameter. I would rather find and fix the connection issues than rely on this sticking plaster fix.

In know I’ve said some of these things before, but they are worth repeating.

  • Oracle 19c is the current long term release, so it’s going to have support for a longer time than an innovation release.
  • Oracle 21c is an innovation release. Even when the on-prem version does drop, you probably shouldn’t use it for your main systems unless you are happy with the short support lifespan.
  • I recently heard there won’t be an Oracle 22c, so the next release after Oracle 21c will be Oracle 23c, which is currently slated to be the next long term release.

In short, get all your databases to Oracle 19c, and you should probably stick there until Oracle 23c is released, unless you have a compelling case for going to Oracle 21c.

Cheers

Tim…

Video : TRUCATE TABLE … CASCADE

In today’s video we demonstrate the TRUNCATE TABLE … CASCADE feature added in Oracle 12.1.

The video is based on this article.

The star of today’s video is Øyvind Isene, who reluctantly took a break from his coffee to let me film this clip. πŸ™‚

Cheers

Tim…

Don’t be a moron. Install APEX in all your Oracle databases!

If you come from an Oracle background you know what Application Express (APEX) is. You know it’s an awesome low-code development environment, and you know it can turn DBAs and PL/SQL developers into productive members of society in no time at all. πŸ™‚

Even if you don’t want to use APEX, you should install APEX anyway, because it comes with some really handy PL/SQL packages. These come to mind.

APEX_WEB_SERVICE : I’ve written my own APIs in the past, but I never use them anymore. I recommend people make web service callouts from the database using the APEX_WEB_SERVICE package. You can use it for REST and SOAP calls, and it works equally well for JSON and XML web services.

APEX_JSON : Oracle included some JSON functionality in Oracle 12.1, but they only completed the implementation in Oracle 12.2. If you are working with a database version older than Oracle 12.2 and you care about JSON, you need the APEX_JSON package.

APEX_DATA_PARSER : This package allows you to convert CSV, JSON, XML and XLSX data into rows and columns. It’s really simple to use!

APEX_ZIP : You can do some simple gzip/gunzip stuff with the UTL_COMPRESS package, but the APEX_ZIP package allows you to zip up multiple files into an archive, or extract files from an existing archive.

APEX_MAIL : This package gives you an API over the APEX mail functionality. It’s more flexible than the UTL_MAIL package, and is much simpler than writing your own APIs using the UTL_SMTP package.

APEX_STRING : I always used to use the STRING_TO_TABLE and TABLE_TO_STRING functions in the APEX_UTIL package. Those have now been moved to the APEX_STRING package. You might prefer to use the SPLIT and JOIN functions instead. There is also a bunch of other string handling stuff worth checking out.

Every release brings more goodies for PL/SQL programmers, regardless of whether you want to use APEX or not!

So do yourself a favour. Install APEX into all your Oracle databases and make your PL/SQL developers happy.

Cheers

Tim…

PS. I don’t actually think you are a moron if you don’t install APEX. If you take the title of blog posts seriously, when they are clearly clickbait, maybe you are a moron…

Update: There seems to be a little confusion on some social media comments. Installing APEX into a database doesn’t mean APEX is available for people to develop applications. You can only use APEX proper if you have a gateway (ORDS, EPG, mod_plsql) fronting it. Without a gateway, APEX doesn’t work. Someone can’t take it upon themselves to run a gateway somewhere else if they don’t know all the passwords, so installing APEX and using it are not the same thing. Installing it does make the built-in packages available for normal PL/SQL development, which is really handy. Hence this post.