Database Patching : It’s a difficult subject

If you came hear hoping I was going to say there are valid reasons not to patch, you are out of luck. There is never a valid reason not to patch…

Instead this post is more about the general approach to patching. I’ve spent 22+ years writing about Oracle, including how to install it, but I’ve written practically nothing about how to patch a database. My stock answer is “read the patch notes”, and to be honest that is probably the best thing anyone can do. Although patching is a lot more standardized these days, it’s still worth reading the patch notes in case something unexpected happens. In this post I just want to talk about a few top-level things…

Patching to a new ORACLE_HOME

There are two big reasons for patching to a new ORACLE_HOME, or out-of-place patching.

  1. You can apply the binary patches to the new home while the database is still running in the old home, so you reduce the total amount of downtime.
  2. You have a natural fallback in the event of the wanting to revert the patch. You don’t have to wait for the patch rollback to complete.

There are some downsides though.

  1. It requires extra space to hold both the unpatched and patched homes, until you reach a point where you are happy to remove the unpatched home.
  2. If you have any scripts that reference the ORACLE_HOME, they will need to be updated. Hopefully you’ve centralized this into a single environment setup script.
  3. I guess it’s a little more complicated, and the patch notes are not that helpful.

So should you follow the recommendation of patching to a new home or not? The answer as always is “it depends”.

The reduction in downtime for a single instance database is good, but if you are running RAC or Data Guard, this isn’t really an issue as the database remains online for most of the patching anyway. Having a quick fallback is great, but once again if you are running RAC or Data Guard this isn’t a big deal.

If you are running without RAC or Data Guard, you have made a decision that you can tolerate a certain level of downtime, so is taking the system down for an hour every quarter that big a deal? I’ve heard of folks who use RAC and/or Data Guard who still bring the whole system offline to patch, so the decision is probably going to be very different for people, depending on their environment and the constraints they are working with.

I hope you’re taking OS and database backups before patching. If something catastrophic happens, such that a rollback of the patch is not possible, you can recover your original home and database from the backups. Clearly this could take a long time, depending on how your backups are done, but the risk of loss is low. So the question is, can you tolerate the additional downtime?

You have to make a decision on the pros and cons of each approach for you, and of course deal with the consequences. If in doubt, go with the recommendation and patch to a new home.

Read-only Oracle homes

Read-only Oracle homes were introduced in 18c (here) as an option, and are the default from Oracle 21c onward. One of the benefits of read-only Oracle homes is they make switching homes so much easier. You haven’t got to worry about copying configuration files between homes, as they are already located outside the home.

Release Update (RU) or Release Update Revision (RUR)?

You have a choice between patching using a Release Update (RU), or a Release Update Revision (RUR). To put it simply, a RU contains not only the latest security patches and regression fixes, but may also include additional functionality, so the risk of introducing a new bug is higher. A RUR is just the security patches and regression fixes. Unlike the Critical Patch Updates (CPUs) of the past, that ran on endlessly, RURs are tied to specific RUs, so you will end up applying the RUs, but at a later date, when hopefully the bugs have been sorted by the RUR…

The folks at Oracle suggest applying the RUs, which is what I (currently) do. Some in the Oracle community suggest applying RURs is the safer strategy. If you look at the “Known Issues” for each RU, and the list of recommended one-off patches that should be applied after the RU, you can see why some people are nervous of going directly to RUs.

Once again, this comes down to you and your experience of patching with the feature set you use. If you are finding RUs are too problematic, go with the RUR approach. You can always change your mind at any time…

Monthly Recommended Patches (MRPs)

There’s a new kid on the block starting with 19.17 on Linux, which are monthly recommended patches (MRPs). They replace RURs. There are 6 MRPs per RU, with each MRP containing the RU and the current batch of recommended one-off patches, as documented in MOS Note 555.1.

I’m assuming these are rolling and standby-first patches, but I can’t confirm that yet.

RAC Patching : Rolling Patches

Rolling patches can be applied one node at a time, so there are always database instances running, which means the database remains available for the whole of the patching process.

Release Updates (RUs) and Release Update Revisions (RURs) are always rolling patches, so it makes sense to take advantage of this approach. If you are applying one-off patches, these may not be rolling patches, so always check the patch notes to make sure.

Even when rolling patches are available, you can still make the decision to take the whole system offline to apply the patches. I’m not sure why you would want to do this, but the option is there for you.

Data Guard : Standby-First Patches

Release Updates (RUs) and Release Update Revisions (RURs) are always standby-first patches. This gives you some flexibility on how you approach patching your system. Here are two scenarios with a two node Data Guard setup, where node 1 is the primary and node 2 is the standby.

Scenario 1 : Switchovers

  • Patch the node 2 binaries (not datapatch) and bring the standby back into recovery mode.
  • Switchover roles, making the node 2 the primary and node 1 the standby.
  • Patch the node 1 binaries (not datapatch) and bring the standby back into recovery mode.
  • Run datapatch against node 2 (the primary database).
  • Optionally switchover roles making node 1 the primary database again.

Scenario 2 : No switchovers

  • Patch the node 2 binaries (not datapatch), but don’t start the standby.
  • Patch the node 1 binaries (not datapatch) and start the database.
  • Start the standby on node 2.
  • Run datapatch on node 1 (the primary).

Scenario 1 reduces downtime, as the primary is always running while the standby is having the binaries patched. Scenario 2 is simpler, but has a more extensive downtime as the primary is out of action while the binaries are being patched.

Remember, one-off patches may not be standby-first patches, so you may only have the option of scenario 2 when applying them. You have to read the patch notes.

OJVM Patching : Which approach?

Oracle 19c and 21c have simplified the OJVM patching situation. Previously the OJVM patches were completely separate. The grid infrastructure (GI) and database patches now include the OJVM patches from the previous quarter, and the latest OJVM patches are still shipped as a separate patch. So now we have a choice.

  • Only apply the GI/DB patches, and remain one quarter behind on the OJVM patches.
  • Apply both the GI/DB and OJVM patches and be up to date on both.

The separate OJVM patches come with additional restrictions. They are not standby-first patches, and according to the patch notes, they can only be applied as RAC rolling patches if you use out-of-place patching.

Personally I would say just apply the GI/DB patches and forget about the separate OJVM patches. Life is much simpler that way, and being three months behind on the OJVM patches is not the worst thing in the world.

Why don’t you write about patching much?

Writing about patching is difficult, because everyone has a unique environment, and their own constraints placed on them by their business. I’ve always avoided writing too much about patching because I know it’s opening myself up for criticism. Whatever you say, someone will always disagree because of their unique situation, or demand yet another patching scenario because of their unique environment. You’re damned if you do, and damned if you don’t.

I’ve recently written a few patching articles for specific scenarios (here). I may add some more, but it’s not going to be a complete list, and don’t expect me to write articles about stuff I don’t use, like Exadata. These are purely meant as inspiration for new people. Ultimately, you need to read the patch notes and decide what is best for you!

Let the cloud do it!

If all this is too much hassle, you do have the option of moving your database to the cloud and letting them worry about patching it. 🙂

Conclusion

Read the patch notes!

Cheers

Tim…

Operating Systems for Oracle Databases (Poll Results Discussed)

I put out some questions on Twitter a couple of days ago, asking about the operating systems people were using for their Oracle database servers.

As with all these polls, we have to discuss some caveats. Most of the people that follow me are from the Oracle community, so that puts a heavy bias on the outcome. The questions relate to Oracle databases, which also influences the results. Someone may choose one distribution to run Oracle workloads, and a different distribution to run non-Oracle workloads. We also have to remember the sample size is small. Despite this, I’m going to discuss the results as if this were a representative sample of people, even though I accept it may not be. 🙂

This was the first question I asked.

Which operating system are you using for your Oracle Databases servers?

You’ll notice I totally forgot to include Windows, which was a shame because it would have been nice to see that. My main focus was to see how many people were still holding on to the traditional UNIX systems. There was a really strong showing for Linux over UNIX, which was hardly surprising. Every year the dominance of Linux is increasing. A few years back a lot of big companies were still using the traditional UNIX systems, but I guess a lot of people have got sick of spending that sort of cash, and some have probably switched to buying Exadata kit instead. I cant say I’m surprised by this result.

Something I’ve said repeatedly over the years is you should stick to the operating system that is the most popular, as that is the one that is going to get tested the most. There is no point in purposely making yourself a minority IMHO. Having lived through the death of Oracle on Tru64 and HP-UX, I wouldn’t dream of using anything other than Linux now.

This was the next question.

For Linux users, which Linux distro are you using for your Oracle database servers?

Over 65% of the folks picked Oracle Linux, and about 27% picked RHEL. The fact this is a poll about Oracle database servers no doubt added to the skew in this result. Oracle have done a good job of promoting Oracle Linux, and the fact it is free probably helps a lot. I thought Oracle Linux would be the winner here, but I’m not sure I expected it to be by this much. Personally I wouldn’t run on anything other than Oracle Linux by choice. Remember, this is what Exadata uses, and this is what Oracle Cloud uses.

I suspect some of the people that picked “Other” were speaking about non-production systems. Perhaps I should have made it clear I was thinking about production, not test labs…

This was the final question.

For Enterprise Linux users, which version of Oracle Linux and/or RHEL are you using for your Oracle database servers?

It’s good to see that nobody is owning up to OL5/RHEL5. There are still a few things lingering on OL6/RHEL6, but I guess those are probably running old versions of the database.

OL7/RHEL7 is still the most common version, but I guess a lot of this is down to the long lifespan of database servers. I suspect many of these servers were provisioned some time ago. I’m hoping most new deployments are using OL8/RHEL8.

So nothing really that surprising about the outcome of this batch of questions. Pity I didn’t include Windows in the first question. Maybe next time…

Cheers

Tim…

Are you running production databases on the cloud? Poll results discussed.

It can be quite difficult to know if your impression of technology usage is skewed. Your opinion is probably going to depend on a number of factors including what you read, who you follow, and the type of company you work for. For this reason I asked some questions on Twitter the other day, just to gauge the response.

Let me start by saying, this is a small sample size, and most of my followers come from the Oracle community, including a number of Oracle staff. This may skew the results compared to other database engines, and technology stacks. I’m commenting on the results as if this were a representative sample, but you can decide if think it is…

So this was the first question I asked.

Is your company running production relational databases in the cloud?

We can see there was a fairly even spread of answers.

  • All prod DBs in cloud: A response of nearly 19% picking this option kind-of surprised me. I speak to a lot of people, and there always seems to be something they’ve got that doesn’t fit well in the cloud for them. Having this many people saying they’ve managed to make everything fit is interesting.
  • Some prod DBs in cloud: I expected this response to be high and with over 27% it was. When we add this to the previous category, we can see that over 46% of companies have got some or all of their production relational databases in the cloud. That’s a lot.
  • Not yet, but planned: At over 24%, when added to the previous categories, it would seem that over 70% of companies see some perceived value in running their databases in the cloud. Making that initial step can be difficult. I would suggest people try with a greenfield project, so they can test the water.
  • Over my dead body: At 29%, this is a lot of people that have no intention of moving their databases to the cloud at this moment in time. We might get some answers about why from the next question.

This was my second question.

What’s stopping you from moving your databases to the cloud?

Once again, we get a fairly even spread of responses.

  • Legal/Compliance: Over 17% of respondents have hit this brick wall. Depending on your industry and your country, cloud may not be an option for you yet. Cloud providers are constantly opening up data centres around the world, but there are still countries and regions that are not well represented. Added to that, some organisations can’t use public cloud. Most cloud providers have special regions for government or defence systems, but they tend to be focused in certain geographical regions. This is a show stopper, until the appropriate services become available, or some hybrid solution becomes acceptable.
  • Company Culture: At over 30%, this is a road block to lots of things. Any sort of technology disruption involves a change in company culture, and that’s one of the hardest things to achieve. It’s very hard to push this message from the bottom up. Ultimately it needs senior management who understand the need for change and *really* want to make that change. I say *really* because I get the feeling most management like to talk the talk, but very few can walk the walk.
  • Cloud is Expensive: At nearly 29%, this is an interesting one. The answer to the question, “is cloud more expensive?”, is yes and no. 🙂 If you are only looking at headline figures for services, then it can seem quite expensive, but the cloud gives us a number of ways to reduce costs. Reserved instances reduce the cost of compute power. Selecting the correct shape and tier of the service can change costs a lot. Spinning down non-production services when they are not used, and down-scaling production services during off-peak hours can save a lot of money, and these are not things that necessarily result in a saving on-prem. I also get the impression many companies don’t work out their total cost of ownership (TCO) properly. They forget that their on-prem kit requires space, power, lighting, cooling, networking, staffing etc. When they check the price of a service on the cloud, it includes all that, but if you don’t take that into consideration, you are not making a fair comparison. Some things will definitely be cheaper on the cloud. Some things, not so much. 🙂
  • Cloud Sucks: At nearly 23%, this is a big chunk of people. It’s hard to know if they have valid reasons for this sentiment or not. Let’s take it on face value and assume they do. If this were a reflection of the whole industry, it’s going to be interesting to see how these people will be won over by the cloud providers.

The comments resulted in a few interesting things. I’ve responded to some of them here.

  • “Lack of cloud skills.” We all have to start somewhere. I would suggest starting with small proof of concept (POC) projects to test the water.
  • “Unreasonable Oracle licencing restrictions.” In case you don’t know, the core factor doesn’t apply to clouds other than Oracle Cloud, which makes Oracle licensing more expensive on non-Oracle clouds. Of course, everything can be negotiated.
  • “Lack of availability of Cloud experts to assist/advise.” I’m sure there are lots of people that claim they would be able to help, but how many with a proven track record is questionable. 🙂
  • “We have a massive legacy estate to consider.” Certainly, not everything is easy to move the the cloud, and the bigger your estate, the more daunting it is. I’m sure most cloud providers would love to help. 🙂
  • “Latency with fat client applications.” I had this conversation myself when discussing moving some of our SQL Server databases to Azure. It can be a problem!
  • “Seasonal businesses with uncertain money flow may not able to meet the deadlines for subscription payments.” Scaling services correctly could help with this. Scale down services during low periods, and scale up during high periods.
  • “The prime fear is being pulled off from the grid. Undependable internet connections.” Sure. Not every place has dependable networking.
  • “Bandwidth requirements & limited customization possibilities.” Ingress and egress costs vary with cloud providers. It may be intelligent design of your processes can reduce the amount of data being pushed outside the cloud provider. The cloud is very customisable, so I’m not sure what the issue is here, but I’m sure there are some things that will be problematic to some people.

Overall I think this was an interesting exercise. Even five years ago I would have expected the responses to skew more in favour of on-prem. Barring some huge change in mindset, I would expect the answers to be even more in favour of cloud in another 5 years.

Regardless of your stance, it seems clear that familiarity with cloud services should be on your radar, if it’s not already. Your current company may not be fans of the cloud, but if you change jobs the cloud may be a high priority for your new company.

Cheers

Tim…

PS. I’ve been running my website on AWS since 2016 . I started to write about some services on AWS and Azure in 2015. I’ve been playing with Oracle Cloud since its inception in 2016 (I think). Despite all this, I consider myself a dabbler, rather than an expert.

Video : Gradual Database Password Rollover Time

In today’s video we demonstrate gradual database password rollover time, introduced in Oracle database 21c, and backported to 19c.

The video is based on this article.

This is a small, but really useful quality of life feature!

The stars of today’s video are the offspring of Jeff Smith. It was cold, dark and miserable when I recorded the video, and this is one of my favourite clips, so I included it to bring a touch a summer! 🙂

Cheers

Tim…

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…