Oracle | The ORACLE-BASE Blog

UKOUG Breakthrough 2022 : Day 1

The evening before the conference the Oracle ACEs met up for some food at a curry place in the city centre. Thanks to the Oracle ACE Program for organising this! Earlier that day I presented my first face to face session in 3 years, and now it was time for my first social event in a similar timescale. I was pretty nervous going into it, and quite standoffish at first, but I gradually relaxed and “conference Tim” started to come back. By the end of the evening I was feeling a lot more comfortable with the situation. It was nice to get to meet up with a bunch of people I had not seen for a long time. It also made me feel a bit more relaxed about going to the conference the next day.

The one downside of going to a conference in your hometown is commuting. I don’t live far away from the venue, but regardless of the mode of transportation, commuting during rush hour is a nightmare. Instead I chose to wait for the rush hour traffic to die down and be fashionably late. That was the right move.

The first session I went to was Simon Haslam speaking about “Platform Engineering for the Modern Oracle World”. I’ve got a lot of time for Simon. Him and Lonneke Dikmans sowed some seeds in my brain a long time ago, which have ultimately had a big influence on me over the years. In this session he talked about the various approaches to automation over the years, culminating in where many people find themselves today. I found myself nodding my head in agreement with most of what Simon was saying during this session. I joked later that his session gave me post traumatic stress disorder (PTSD) when I thought back through many of those stages. 😁 A group of us continued the conversation about the topic after the session, which is always fun.

Next I moved on to the Oracle ACE Briefing. The first rule of the Oracle ACE Briefing is don’t talk about the Oracle ACE Briefing. Once again, it was good to see a lot of familiar faces, and some new ones. Once the session was over, and we knew everything there was to know about the Oracle Games Console (#OGC), I spent some time talking with Dominic Giles, while he desperately looked for ways to get rid of me. He didn’t succeed. 😉

By the time I finally let Dom go, it was time to watch Jasmin Fluri and Gianni Ceresa presenting “Git Branching – the battle of the ages”, or “Development Workflows: The Battle of The Ages!”, depending on which title you prefer. The session was a celebrity death match between trunk-based development (Jasmin) and Gitflow style development (Gianni). Gianni fought dirty, but ultimately Jasmin was able to overpower him and grind him into the dirt. At least that’s how I saw it. 🙂 Both sides gave compelling reasons for their preferred method, and ultimately there is a lot more similarity between them than some people would have you believe. As is often the case, there is no “best”, but what is “best for you”. Not surprisingly, this sparked another conversation at the end of the session, with a few war stories thrown in for good measure.

At that point I had to head off to beat the traffic across town, as I had some “real life” things to do.

So that’s was day 1 of UKOUG Breakthrough 2022. I know it will sound silly to most people, but I was stressing about the conference and it turned out to be a lot easier than I expected. Fingers crossed day 2 will go well also.

Cheers

Tim…

Fedora 37 and Oracle

Fedora 37 was released recently. Here comes the standard warning.

Do not install Oracle on Fedora before reading this!

Here are the usual things I do when a new version of Fedora comes out.

Why do I do this? As mentioned in the first link, Fedora is a proving ground for future versions of RHEL, and therefore Oracle Linux. I like to see what is coming around the corner. Doing this has no “real world” value, but I’m a geek, and this is what geeks do.

I pushed Vagrant builds to my GitHub.

If you want to try these you will need to build a Fedora 37 box. You can do that using Packer. There is an example of that here.

https://github.com/oraclebase/vagrant/tree/master/packer/f37

So now you know how to do it, please don’t! 🙂

What’s New?

So what’s new with Fedora 37? You can read about it here.

Cheers

Tim…

VirtualBox 7.0.4 and Vagrant 2.3.3 – Another VirtualBox Fail (For Me)…

VirtualBox 7.0.4

Please read the update at the bottom of this post before making any conclusions. I wanted to leave the rest of the post unedited, but needed to update my current situation as it has changed since this post was written…

VirtualBox 7.0.4 has been released.

The downloads and changelog are in the normal places.

From my previous posts on VirtualBox 7.0.x you will know I’ve been having problems with it. They all seem to come down to networking. I can often, but not always, start up an existing VM, but if I try to build a new VM with Vagrant it will fail to setup the networking. Also, if I attempt to use Packer, it will fail to find the kickstart file, which it attempts to access over HTTPS. Both cases seem to be network related.

I’ve done all the usual firewall and antivirus stuff, and trawled the internet to see if anyone else has a solution. None of that has helped.

When I saw VirtualBox 7.0.4 I was hoping this might solve my problem, but no. I get the same issues on Windows 11, Windows 10 and macOS (Intel). This makes VirtualBox 7.0.4 unusable for me.

When I revert back to VirtualBox 6.1.40, everything works as expected…

I don’t know if this is just me, or if a lot of people are having problems with VirtualBox. It seems odd that I am getting the same result on multiple machines on two fundamentally different architectures though.

So my current suggestion would be use this at your own risk…

Vagrant 2.3.3

Vagrant 2.3.3 has also been born. Upgrading to this didn’t cause or solve any issues for me. Even though I’ve had to revert back to VirtualBox 6.1.40, this new version of Vagrant is working fine for me.

Packer 1.8.4

At some point since my last run of Packer builds version 1.8.4 was released. This follows the same pattern. It works great with VirtualBox 6.1.40, but doesn’t work at all with VirtualBOx 7.0.x.

Conclusion

Sadly VirtualBox 7.0.x is still a bust for me. Maybe I’m the only one on the planet, but it is unusable for me. I hope this changes in the future as I rely on VirtualBox bigtime…

Cheers

Tim…

Update: I’ve now switched to using VirtualBox 7.0.4 completely, and all issues have been resolved. This post by Frits Hoogland explained what my problem was. This is solved by adding the “–nat-localhostreachable1” parameter. That solved my Packer problem, and subsequent VM problems. Happy days…

Update 2: An update from Simon Coter suggests the issue is due to the way Vagrant and Packer use the VirtualBox CLI, rather than the API. The workaround mentioned in the previous update is still the easiest way to move forward.

APEX 22.2 : Vagrant and Docker Updates

I know it’s hard to believe that anything happened last week other than the implosion of Twitter, but APEX 22.2 was also released.

As normal, this resulted in a bunch of updates to my builds.

Vagrant

All relevant Vagrant builds were updated to include APEX 22.2. Many had been updated recently to bring them in line with the latest Oracle security patches. You can find the build here.

https://github.com/oraclebase/vagrant

Docker/Container

As with Vagrant, all the relevant Docker/Container builds have been updated to 22.2. You can find the build here.

https://github.com/oraclebase/dockerfiles

Real World

If the release had been a couple of weeks earlier I would have been able to push it out during this quarters patching cycle. Unfortunately this release will now how to wait until the January 2023 patching cycle for me to push it out at work.

Our APEX upgrades/patches are automated (as mentioned here), so I could push this release out at the press of a button, but all the relevant teams would have to do their testing, and that probably isn’t going to happen until the next patching cycle, so it’s just going to wait until then. 🙁

My Suggestion

Even if your work environment moves forward at a slower pace than you would like, it still makes sense to keep a test/play environment at the latest and greatest versions, so you can learn the new stuff and see what issues are coming round the corner for your applications.

All my home builds are now on APEX 22.2, running on the beta release of the Oracle Games Console (#OGC). 😉

Cheers

Tim…

VirtualBox 7.0.2 and Vagrant 2.3.2

VirtualBox 7.0.2

Hot on the heels of VirtualBox 7.0, we now have VirtualBox 7.0.2. I’m not going to lie, I expected this release to happen really quickly, as VirtualBox 7.0 gave me a number of headaches.

The downloads and changelog are in the normal places.

I’m hoping this fix solves a lot of my problems.

Main: Fixed issue when VBoxSVC could become unresponsive if Extension Pack was not installed (bug #21167)

I’ve installed 7.0.2 on a couple of Windows machines (10 and 11), and I’m going to play with it today.

Vagrant 2.3.2

As mentioned in my post about VirtualBox 7.0, Vagrant 2.3.1 doesn’t support VirtualBox 7.0 directly, so I was expecting a quick release of Vagrant and here it is. Vagrant 2.3.2 has a single feature in its changelog.

provider/virtualbox: Add support for VirtualBox 7.0 [GH-12947]

I’ll be attempting some new Packer builds of my Vagrant boxes, then working through my Vagrant and Docker builds to add in the new patches. That should give it a pretty good test. I’ll update this post with the results.

If this doesn’t work out, I’ll be reverting to VirtualBox 6.1.40…

Cheers

Tim…

Updates

Update 1: The good news is VMs seem to start OK now using Vagrant 2.3.2 and VirtualBox 7.0.2. (not true, see below)

The bad news is I can’t get Packer to work, so I’m unable to build new Vagrant boxes with the 7.0.2 guest additions. I’ve tried on Windows 10, Windows 11 and macOS (Intel) and all result in the same issue. It seems the kickstart is not working, like the networking on VirtualBox 7.0.2 is screwed up somewhere.

I’ve downgraded my Windows 10 PC to use VirtualBox 6.1.40 and I’m building new Vagrant boxes on that. So far so good. I’ll upload them to Vagrant Cloud and attempt to use them with VirtualBox 7.0.2. The guest additions will be out of date, but it should still work fine, I hope. I’ll keep updating…

Update 2: After a couple of successes starting up VMs using VirtualBox 7.0.2 and Vagrant 2.3.2, it all seems to have caved in now.

I’m going to switch to VirtualBox 6.1.40 for the moment. I don’t have more time to waste on this. VirtualBox and Vagrant are tools I use to make my life easier, not an end in themselves, so I don’t have the time to keep working on this. I’m sure a future version of VirtualBox will have sorted out its networking issues and I’ll be able to use it…

Update 3: Everything is reverted to VirtualBox 6.1.40. Packer builds of OL7, OL8 and OL9 are completed and uploaded to Vagrant Cloud. I’ve started to do some Vagrant builds with them now and they seem to be working fine, so what I’m seeing is on VirtualBox 6.1.40 everything works as usual. On VirtualBox 7.0.2 I see these two issues.

Packer just doesn’t work. It fails getting the HTTP access to the kickstart file. I get the same issue on Windows 10, Windows 11 and macOS (Intel).
Starting VirtualBox VMs with Vagrant is hit-or-miss. Sometimes they work, but sometimes they fail. It also seems network related, but I can’t be 100% about that.

Update 4: An update from Simon Coter suggests the issue is due to the way Vagrant and Packer use the VirtualBox CLI, rather than the API. The workaround for this was posted by Frits Hoogland here.

Joel Kallman Day 2022 : It’s a Wrap! (#JoelKallmanDay)

Yesterday was an Oracle community day called the Joel Kallman Day (#JoelKallmanDay) 2022.

I would like to say a big thank you to everyone who took the time to join in. Here is the list of posts I saw in chronological order. If I missed you out, give me a shout and I’ll add you.

Thanks to everyone who got involved with blog posts, tweets and retweets. It’s always fun to see what people come up with on this day. It’s also good to see people making contributions in their own language too. It’s easy for us to forget that people out there are doing good work for the community in their primary language!

A few people mentioned Joel specifically, and included some pictures, which was both nice and sad at the same time.

Thanks again, and remember you’ve got a year to get ready for the 2023 event! 🙂

Cheers

Tim…

VirtualBox 7.0 (and 6.1.40)

VirtualBox 7.0 has been released.

The downloads and changelog are in the usual places.

VirtualBox installed with no drama (see update below) on my Windows hosts. Something a little funky happened on macOS (Intel), so I removed VirtualBox and installed it again. It worked fine the second time.

The current version of Vagrant (2.3.1) fails with a version compatibility error when used with VirtualBox 7. Fortunately Simon Coter has our back, and previously published this workaround. I’m sure a new version of Vagrant will come soon, making this workaround unnecessary.

I’ll be doing new Packer builds of my Vagrant boxes over the next few days. If they are delayed, it means I’m having some drama with the new version of VirtualBox. 🙂

Cheers

Tim…

Update: So things aren’t looking so great. Even with the Vagrant workaround there seems to be a few issues. Sometimes Vagrant just hangs. No apparent reason why. Sometimes VirtualBox networking just doesn’t work. Vagrant can’t create the network. At this point I’m not sure if the issues are with Virtual or just a problem with VirtualBox. At one point I got a very similar issue when using VirtualBox directly (no Vagrant). It is happening to me on both Windows 10 and Windows 11, so it seems pretty consistently bad. Let’s see if there are any quick updates to VirtualBox or Vagrant in the next few days, but for now I would avoid VirtualBox 7 until the issue is a little clearer.

Update 2: VirtualBox 6.1.40 has been released. It may be safer to use that rather than version 7 at the moment…

Update 3: An update from Simon Coter suggests the issue is due to the way Vagrant and Packer use the VirtualBox CLI, rather than the API. The workaround for this was posted by Frits Hoogland here.

Understand the history of your subject matter #JoelKallmanDay

When dealing with mature environments it’s not good enough to only understand what the current trend or “best practice” is. We also have to understand the history of the environment, and the technology stack we are using, as that will help inform us about why decisions were made they way they were. Let’s look at a few examples…

DECODE and CASE Expressions

CASE expressions were first released in Oracle 8i (1998), with the CASE statement added to PL/SQL in Oracle 9i (2001). You can read more about them here. Prior to that if we wanted something similar to a CASE expression in our SQL, we had to use the DECODE function. Here’s some examples so you can see what they look like.

# Value Match CASE Expression
select case id
         when 1 then 'Banana',
         when 2 then 'Apple',
         when 3 then 'Cherry'
         else 'Unknown'
       end as fruit
from   t1;

# Searched CASE Expression
select case
         when id = 1 then 'Banana',
         when id = 2 then 'Apple',
         when id = 3 then 'Cherry'
         else 'Unknown'
       end as fruit
from   t1;

# DECODE        
select decode (id,
               1, 'Banana', 
               2, 'Apple', 
               3, 'Cherry',
               'Unknown') as fruit
from   t1;

We all know it takes people years to introduce new syntax into their code, so we will probably have examples of code well into the 2000’s still using DECODE, even though CASE expressions are easier to read, far more flexible and part of the ANSI standard.

It may be hard for some of the younger crowd to believe, but there is code out there that has been running for a very long time. About 30 years worth of PL/SQL and about 40 years of SQL. When we are looking at the code base for a mature application, we may run into things that don’t look familiar. Languages and design patterns evolve, but we have to be capable of dealing with mature code.

ANSI Joins

ANSI joins were introduced in Oracle 9i (2001). You can read about them here, and here.

I know I’m going to get a lot of hate from the Oracle crowd for this, but ANSI joins are superior to the old-style Oracle joins, where the join conditions were in the WHERE clause, with a sprinkling of (+) symbols everywhere for outer joins. I’m sure the comments will try and defend them saying things like ANSI joins are just syntax candy, and that’s fine, but I still believe ANSI joins are superior…

The point is, even if we are writing new code using ANSI joins, we still have to be capable of understanding the old syntax, because we will run into a lot of code that still uses it, because it is historical code, or new code written by historical developers. 🙂

One True Lookup Table (OTLT)

The One True Lookup Table design pattern, or rather anti-pattern, is one of those disasters that managed to work it’s way into loads of systems. There was a period when many of us were using Oracle Designer/2000 as a central repository for our ERD and physical modelling, and Designer encouraged the One True Lookup Table anti-pattern. It used to generate a number of tables, one of which was called CG_REF_CODES. This was essentially a dumping ground for name-value pairs, which grew to encompass almost all reference type data. Many people brought up on this design approach continued to use it in other projects, even when they were not using Designer anymore. I can think of one popular student system that makes heavy use of this anti-pattern. 🙁

OTLT has always been a terrible idea. I’ve been guilty of using it too, but it has always been a nightmare. If you have a system that has SQL statements that do 20 different joins to the same table, you have probably encountered the OTLT anti-pattern. You have to understand how to deal with it, and treat it as a learning experience of what not to do!

You will look at it and think the people who designed it must have been crazy, but you have to remember this is what the tools were encouraging us to do back then. If someone worked as a database designer/modeller (they used to exist), they might never actually write code, so they would not see what a disaster this design pattern was, and never learn not to do it again.

Conclusion

I always encourage people to stay up to date with technology. It’s important we keep progressing, but that doesn’t mean we can afford to forget the past. Not every system we work on is brand new, using all the latest tech and doing things using the latest approach. To be useful, we need to be able to work with both the old and the new.

Also, not everything new is good. There are plenty of examples of an “old” approach to solving a problem being superior to the new “cool” approach. It’s important we sample all of them and pick the right tool/approach for the job.

Cheers

Tim…

Separation of Duties (Poll Results Discussed)

On the back of the recent patching polls I asked a couple of questions about separation of duties.

As always, the sample size is small and my followers have an Oracle bias, so you can decide how representative you think these number are…

Separation of Duties

Here was the first question.

This is exactly what I expected. For the vast majority of companies, the DBAs are responsible for the Grid Infrastructure (GI), Automatic Storage Manager (ASM) and the database (DB).

When Oracle first started floating the idea of separation of duties it kind-of surprised me, as I had never worked with a company that cared about it. Sure they have System Administrators that look after the OS, and maybe provision new disks on the server, but I have never experienced a situation where anyone other than the DBAs do anything with the Oracle side of things.

Don’t get me wrong, if that’s what a company wants to do, it’s good that Oracle make it possible, but I think the vast majority of people just don’t care! What’s more, I think it’s likely to cause more problems than it solves.

GI/DB Ownership

This was the second question, which was suggested by Aishwarya Kala.

Regarding GI/DB installations where the DBAs do all work on the system, have you split the ownership of the GI and DB installations between different users?

It’s interesting that nearly 90% of people have the DBAs doing all the work on the servers, but nearly 50% still split the ownership of the Grid Infrastructure and the database software.

Back in the day nobody talked about separation of duties and the “oracle” OS user owned everything. When discussing separation of duties, Oracle suggested the Grid Infrastructure should be owned by a different OS user, maybe “grid”, and the database carries on as before, typically using the “oracle” OS user. Then the documentation started to push the separation of ownership. Next the installation started to warn you if you used a common user. So now it’s got to the point where people think it is wrong to use a single user as the owner of the GI and database.

I am one of those people that use the “oracle” user as the owner of both the Grid Infrastructure and the database. If you have no separation of duties, I see no point in splitting these between two users. Occasionally I get questions about this in relation to my Vagrant RAC builds, and my response is simple. I don’t work in an environment with separation of duties, so I think splitting the ownership of the GI and database is pointless.

Personally, I think Oracle should remove the warnings from the installer and be more balanced in the documentation. If the poll results of representative of the wider audience, clearly very few people care about separation of duties. It should be an option, not the default assumption.

Cheers

Tim…

Oracle Database Patching (Poll Results Discussed)

Having recently put out a post about database patching, I was interested to know what people out in the world were doing, so I went to Twitter to ask.

As always, the sample size is small and my followers have an Oracle bias, so you can decide how representative you think these number are…

Patching Frequency

Here was the first question.

How often do you patch your production Oracle GI/DB installations? (Pick the nearest that applies)

There was a fairly even spread of answers, with about a third of people doing quarterly patching, and a quarter doing six-monthly patching. I feel like both these options are reasonable. About 20% were doing yearly patching, which is starting to sound a little risky to me. The real downer was over 22% of people never patch their databases. This is interesting when you consider the recent announcement about monthly recommended patches (MRPs).

For those people that never patch, I can think of a few reasons off the top off my head why.

Lack of testing resource. I think patch frequency has more to do with testing than any other factor. If you have a lot of databases, the testing resource to get through a patching cycle can be quite considerable. This is why you have to invest some time and money into automated testing.
If it ain’t broke, don’t fix it. The problem is, it is broken! How long after your system has been compromised will it be before you notice? How are your customers going to feel when you have a data breach and they find out you haven’t even taken basic steps to protect them? I don’t envy you explaining this…
Fear of downtime. I know downtime is a real issue to some companies, but there are several ways to mitigate this, and you have to balance the pros and the cons. I think if most people are honest, they can afford the downtime to patch their systems. They are just using this as an excuse.
Patching is risky. I understand that patches can introduce new issues, but that is why there are multiple ways to patch, with some being more conservative from a risk perspective. I think this is just another excuse.
Out of support database versions. I think this is a big factor. A lot of people run really old versions of the database that are no longer in support, and are no longer receiving patches. I don’t even think I need to explain why this is a terrible idea. Once again, how are you going to explain this to your customers?
Lack of skills. We like to think that every system is looked after by a qualified DBA, but the reality is that is just not true. I get a lot of questions from people who are SQL Server and MySQL DBAs that have been given some Oracle databases to look after, and they freely admit to not having the skills to look after them. Even amongst Oracle DBAs there is a massive variation in skills. Oracle patching has improved over the years, but it is still painful compared to other database engines. Just saying.

Type of Patching

This was the second question.

When patching your production Oracle GI/DB installations, which method do you use?
In-Place = Current ORACLE_HOME
Out-Of-Place = New ORACLE_HOME

This was a fairly even split, with In-Place winning by a small margin. Oracle recommend Out-Of-Place patching, but I think both options are fine if you understand the implications. I discussed these in my previous post.

Conclusion

I think of patch frequency in a similar way to upgrade frequency. If you do it very rarely, it’s really scary, and because nobody remembers what they did last time, there are a bunch of problems that occur, which makes everyone nervous about the next patch/upgrade. There are two ways to respond to this. The first is to delay patching and upgrades as long as possible, which will result in the next big disaster project. The second is to increase your patch/upgrade frequency, so everyone becomes well versed in what they have to do, and it becomes a well oiled machine. You get good at what you do frequently. As you might expect, I prefer the second option. I’ve fought long and hard to get my company into a quarterly patching schedule, and it will only decrease in frequency over my dead body!

Assuming the results of these polls are representative of the wider community, I feel like Oracle need to sit up and take notice. Patching is better than it was, but “less bad” is not the same as “good”. It is still too complicated, and too prone to introducing new issues IMHO!

Cheers

Tim…