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…

Oracle Database Upgrades : One Size Does Not Fit All

If you follow me on Twitter, you’ll know I’ve been doing a lot of upgrades recently. Whenever I mention upgrades, someone comes back with a comment asking me to describe in detail what I’ve done. This always makes me nervous, as every upgrade is potentially unique. What you have to do depends on a number of factors.

  • The database version you are upgrading from, and the version you are upgrading to.
  • The options you have installed, especially if some are deprecated or desupported in the new release.
  • The topology of your system. If you are running single instance on a regular file system, you’ve got a lot less to do compared to someone working with RAC on ASM with a Data Guard standby.
  • Are you taking the opportunity to move to new kit and/or base operating system? From OL6 to OL7/8 for example.
  • Are you changing OS completely? Maybe you’ve finally made the decision to upgrade from AIX/HP-UX/Solaris to Oracle Linux. πŸ™‚
  • Are you planning to convert from non-CDB to the multitenant architecture? You should be!
  • How big is the database you are upgrading? I’ve done some small ones with data pump rather than doing a regular upgrade.
  • What is your tolerance for downtime? If you have a reasonable downtime window, you can turn everything off and upgrade it, which is a lot simpler than trying to keep the lights on.
  • Are there any vendor restrictions that alter how you approach the upgrade?

All of these things, and more I can’t think of off the top of my head, have to be factored in when planning an upgrade, and this is why I say every upgrade is potentially unique.

The types of upgrades I’ve done recently fall into the following general groups, with varying numbers in each group. A number of them have included a move to new kit, because they were running on Oracle Linux 6.

  • 11.2.0.4 to 19c non-CDB (Vendor support issues)
  • 11.2.0.4 to 19c PDB
  • 12.1.0.2 non-CDB to 19c PDB
  • 18c PDB to 19c PDB
  • 18c PDB to 19c PDB using Data Pump
  • 11.2.0.4 to 12.1.0.2 as stepping stone to 19c

The last one may seem odd to people, but this is due to an application dependency issue. The full process for this is as follows.

  • Upgrade database from 11.2.0.4 to 12.1.0.2.
  • Upgrade application to a version that supports both 12.1.0.2 and 19c.
  • Migrate to new kit.
  • Upgrade database from 12.1.0.2 to 19c.
  • Upgrade application to latest version.

What we are trying to do is make everything as “one size fits all” as possible, to make future upgrades, or moves to the cloud, easier, but that’s not always possible due to other constraints.

I do have a couple of upgrade articles on the site, but they are intentionally basic and I never intend to write anything more detailed about upgrades, because it’s impossible to write something that will satisfy every possibility.

So in summary, there is no one size fits all solution to upgrades unless you have already commoditized all your systems, like the cloud providers do. If you are working with a load of on-prem systems, some of which you have inherited from others, each upgrade will be a voyage of discovery, so don’t ask me for a detailed breakdown of what I did, because I’m just going to say no. There is a reason why there is a great big upgrade manual released with every version of the database!

Cheers

Tim…

Upgrades : You have to do them. When are you going to learn? (TLSv1.2)

Questions:

  • Do you remember when SSLv3 was a thing?
  • Do you remember when everyone disabled SSLv3 on their websites?
  • Do you remember how loads of people running Oracle database version 11.2.0.2 and lower cried because all their database callouts failed?
  • Do you remember how they were all forced to patch to 11.2.0.3 or 11.2.0.4 to get support for TLS?
  • Do you remember thinking, I’ll never let something like that happen again?

I’m so sick of saying this. I know I sound like a broken record, but it’s like I’m living in the movie Groundhog Day.

There is no such thing as standing still in tech. It’s like swimming upstream in a river. It takes work to remain stationary. The minute you stop for a rest you are actually moving backwards. I’m sure your next response is,

“But Tim, if it ain’t broke, don’t fix it!”

The minute you stop patching and upgrading, your application is already broken. Yesterday you had an up-to-date system. Today you don’t. You have stopped, but the world around you continued to move on, and sometimes what they do will have a direct impact on you.

The security folks have been complaining about TLSv1.0 and TLSx1.1 for ages, but we are now in the position where the world and their dog are switching off those protocols, and the “we don’t need no stinking patches or upgrades” brigade are pissing and moaning again.

You knew this was going to happen. You had plenty of warning. It is your fault things are now failing. The bad decisions you made have led you to this point, so stop blaming other people. IT IS YOUR FAULT!

Where do you go from here?

First things first, start planning your patch cycles and upgrade cycles. That isn’t a “one time and done” plan. That is from now until forever. You’ve got to keep your server operating systems and software up to date.

If you can’t cope with that, then move to a cloud service that will patch your shit for you!

I know upgrades aren’t necessarily a quick fix, as they need some planning, so you will need some sticking plasters to get your through the immediate issues. Things to consider are:

  • Your load balancers and/or reverse proxies can hide some of your crap from the outside world. You can support TLSv1.2+ between the client and the reverse proxy, then drop down to a less secure protocol between your reverse proxy and your servers.
  • You can do a similar thing with database callouts to the outside world. Use an internal proxy between you and the external resource. The connection between your proxy and the outside world will speak on TLSv1.2+, but the callout from the database to your proxy will speak using a protocol your database can cope with.

These are not “fixes”. They are crappy sticking-plaster solutions to hide your incompetence. You need to fix your weak infrastructure, but these will buy you some time…

I don’t really care if you think you have a compelling counter argument, because I’m still going to scream “WRONG” at you. If you don’t think patching and upgrades are important, please quit your tech job and go be incompetent somewhere else. Have a nice life and don’t let the door hit you on the ass on your way out!

Cheers

Tim…

PS. You know this is going to happen again soon, when the world decides that anything less than TLSv1.3 is evil.

To upgrade or not to upgrade? That’s the question!

Saturday’s post about 19c generated a lot of feedback on a number of my social media networks. To generalise, you could probably split the responses into two camps.

Of course, this discussion applies equally to other technologies, including the middle tier and development frameworks. It can also be applied to people’s attitudes to patching, as well as to upgrades.

We don’t want to upgrade.

I totally get why people think this is a viable option. Such arguments might include:

  • Stability is more important than new features. With new versions come new bugs and new instabilities.
  • We don’t have time/resources to test our application against the new version.
  • The cost (in time and resources) of upgrading is not worth the pay-off of being at the new version. It’s cheaper to pay for extended support than upgrade.
  • The version we are using has all the features we need, so upgrading is a waste of time.
  • Our customers don’t care about new versions.
  • We do all our business logic in the middle tier, so the DB is just a bit bucket. New features are irrelevant.

We want to upgrade to the latest versions.

This is closer to my position, with a few important caveats.

  • You have to have time to learn the new version, so you can get the most out of it, and not fall foul of new features that do things you might not expect.
  • You need to test your application properly against it, to make sure the new version doesn’t break anything. This seems to be the biggest sticking point for companies that haven’t invested in automated testing. A lacklustre approach to testing your application will often result in a disastrous upgrade.

So why would you bother? This is where I do my totally biased sales pitch. πŸ™‚

New versions contain new features, some of which actually work. πŸ™‚ There are headline new features that are just marketing bumf and irrelevant to me, but there are also some really useful things, which make life easier for you and your company. Look what’s happened just for online operations in the last few versions. I know some of these features have saved loads of downtime for people.

I tend to think development new features drive change more than DBA new features, because they outwardly affect more people in the company. For people who do development in the database, the last few releases have included a lot of really useful things. Let’s look at just one of them that is dear to my heart.

If you know what is in the new releases and you don’t find anything compelling, then a choice to stay where you are is fine. We don’t all want the same things, and different opinions are fine. If you are just sticking with what you’ve got because you can’t be bothered to learn the new stuff, and you are content to do the same thing you’ve always done forever, I think you are selling yourself and your company short. Just my opinion though.

I’ve already declared my bias towards upgrades. Why? Because many of the problems I come across actually come from not staying up to date with versions and/or patches. The rest of the industry keeps moving, and somehow DBAs want to be totally static. I don’t understand that. Caution is good. Static is bad IMHO! πŸ™‚

Cheers

Tim…

Oracle Application Express (APEX) 18.2 : Upgrades Complete

A few days ago Joel Kallman announced the release of Oracle Application Express (APEX) 18.2.

In a previous post I mentioned the updates to my Vagrant builds to include this version, as well as updates of Tomcat and Java. I’ve subsequently done the updates for APEX 18.2 on Docker too. If you are interested you can see them here.

In addition to this we’ve rolled APEX 18.2 out at work. We already had some installations of APEX 18.1, but many were stuck on version 5.1.4 because of time constraints. Now everything is up to APEX 18.2. We still have a range of database versions (11.2, 12.1, 12.2 and soon to be 18c) at work, and it’s worked fine on all of them.

I spied a couple of people asking about the upgrade process. There’s no difference to previous versions. In the past, if one of the first two numbers change you do a regular install. If it’s not one of those major version changes you download the patch from MOS and apply it. Since this is a major version number change, I installed it in the normal way and everything was fine. I’m not sure how this will work going forward, as I suspect all releases will start to use the new version format, so does that mean every release from now on will be an “install”, not a “patch”? (see update) Someone has probably discussed this already and I missed it. πŸ™‚

I only have one little gripe about the upgrades, which is I have to run an ORDS Validate once it’s complete to make sure ORDS is working fine. It would be really nice if APEX could fix whatever gets broken in ORDS, so I don’t have to do it. It’s just one less step to do… πŸ™‚

Happy upgrading…

Cheers

Tim…

Update: The subject of install vs. patch was raised at OpenWorld 2018. I sounds like the current plan is to get rid of patches and take the install approach each time. The APEX team are working on reducing the downtime associated with the upgrades…

Why Automation Matters : Patching and Upgrading

As I said in a recent post, you know you are meant to, but you don’t. Why not?

The reasons will vary a little depending on the tech you are using, but I’ll divide this answer into two specific parts. The patch/upgrade process itself and testing.

The Patch/Upgrade Process

I’ve lived through the bad old days of Oracle patching and upgrades and it was pretty horrific. In comparison things are a lot better these days, but they are still not what they should be in my opinion. I can script patches and upgrades, but I shouldn’t have to.  I’m sure this will get some negative feedback, but I think people need to stop navel gazing and see how simple some other products are to deal with. I’ll stop there…

That said, I don’t think patches and upgrades are actually the problem. Of course you have to be careful about limiting down time, but much of the this is predictable and can be mitigated.

One of the big problems is the lack of standardisation within a company. When every system is unique, automating a patch or upgrade procedure can become problematic. You have to include too much logic in the automation, which can make the automation a burden. What the cloud has taught us is you should try to standardise as much as possible. When everything most things are the same, scripting and automation gets a lot easier. How do you guarantee things conform to a standard? You automate the initial build process. πŸ™‚

So if you automate your build process, you actually make automating your patch/upgrade process easier too. πŸ™‚

The app layer is a lot simpler than the database layer, because it’s far easier to throw away and replace an application layer, which is what people aim to do nowadays.

Testing

Testing is usually the killer part of the patch/upgrade process. I can patch/upgrade anything without too much drama, but getting someone to test it and agree to moving it forward is a nightmare. Spending time to test a patch is always going to lose out in the war for attention if there is a new spangly widget or screen needed in the application.

This is where automation can come to the rescue. If you have automated testing not only can you can move applications through the development pipeline quicker, but you can also progress infrastructure changes, such as patches and upgrades, much quicker too, as there will be a greater confidence in the outcome of the process.

Conclusion

Patching and upgrades can’t be considering in isolation where automation is concerned. It doesn’t matter how quick and reliably you can patch a database or app server if nobody is ever going to validate it is safe to progress to the next level.

I’m not saying don’t automate patching and upgrades, you definitely should. What I’m saying is it might not deliver on the promise of improved roll-out speed as a chain is only as strong as the weakest link. If testing is the limiting factor in your organisation, all you are doing by speeding up your link in the chain is adding to the testing burden down the line.

Having said all that, at least you will know your stuff is going to work and you can spend your time focusing on other stuff, like maybe helping people sort out their automated testing… πŸ™‚

Check out the rest of the series here.

Cheers

Tim…

MySQL Upgrades

I read Wim CoekaertsΒ post about the MySQL 5.6.20-4 this morning. I logged on to my server and did the following command as root.

# yum update -y

That’ll be the upgrade done then… πŸ™‚

If you are using MySQL on Linux you can use the MySQL Repository for your distribution, rather than using the bundled MySQL version, to make sure you stay up to date with the latest and greatest. As long as you stay within a point release (5.6, 5.7 etc.) of the latest version, upgrades should really be as simple as a “yum update”.

I’ve started the ball rolling for the upgrades to the MySQL servers at work. That will take a bit longer because of the required testing. πŸ™‚

Now I know that Oracle is a very different beast to MySQL or SQL Server, but the patches for MySQL and SQL Server are so much easier that patching Oracle, it’s not surprising people gravitate to them. I’m sure the pluggable database stuffΒ in 12c is going to simplify things somewhat, but it’s still not going to be anywhere near as simple as this stuff.

Cheers

Tim…

Upgrades to 11g are finally complete

Just a little slice of reality to cut through all the 12c stuff that is floating around at the moment. I’ve just moved the last of our databases to 11g. Yay! As well as upgrading, we’ve been culling or consolidating old and unused stuff, which has drastically reduced and simplified our Oracle database landscape.

We currently have four projects running databases on HP-UX on Itanium (spit), one project on Solaris and the rest on Oracle Linux under VMware. If I had my way we would kick out HP-UX and Solaris and do everything on Oracle Linux.

We’ve still got one project on 11gR1, but that is being held back intentionally because of some issues with the vendor of the application that runs against it. Hopefully that will soon be on 11gR2 also.

So about 7 years after the release of 11gR1 and 5 years after the release of 11gR2 we have finally managed to get there. Judging by the conversations I’ve had over the last year, I would say we are ahead of the curve. There are still plenty of people out there with old versions lurking around for a variety of reasons…

With this in mind, what do you think our timescales are for a move to 12c? πŸ™‚ Like many people, I don’t think it will even be considered until 12cR2 is released and even then it won’t happen over night.

Even so, I still believe it is important that people get their heads around what 12c has to offer. Of all the releases in my time working with Oracle products, I think 12c is the one that is really going to mess with people’s heads. If people don’t spend a significant time getting to know this stuff they are going to make really bad decisions and totally stuff up their installations!

Cheers

Tim…