It is the time of year to apply critical patches. This time round we have some databases at version 19c. We tend to install a new oracle home for each patch, as we find this helps us manage the migration. It also reduces downtime, especially if you have not yet taken advantage of pluggable databases as we haven’t.

Oracle Installer

I notice from the documentation that there is an applyRU parameter that can be passed to the installer to apply the patch. The documentation is quite poor though because it doesn’t specify what should be passed after that. Even more annoying is that if the patch attempt fails, it will leave the home in an intermediate state and it will have to be deleted and recreated.

Automating Tools Patches

I have already automated the VM builds, so now I want to automate the application of the patch into the database. Oracle have done some work here with their Change Assistant tool, which once the database is set up can be used to apply the patch. Hopefully I can find a way to run change assistant from the Linux command line and apply the patch using the automated procedure.

I read a lot about the flexibility of Oracle commands for pluggable databases. I haven’t seen as much about the old fashioned way to copy data files around and manually creating a control file. So lets see if that still works. I have a campus solutions demo instance, lets see if I can copy it and rename it.

Back Up and Edit the Control File

Running a familiar command is promising:

Why start using Java in the database?

I have had a couple of situations recently where using Java in the database might come in handy.

One is replacing a self-hosted database with one on the cloud. The provider gives access to the database, which means remote procedure calls don’t work - the provider doesn’t give access to the underlying OS, they provide the database as a service. This means the remote procedure call will have to be replaced in some way.

I expect the question after the last I/O article is: How did we make our server go faster?

Things That Did Work

Looking at the AWR report, I could see the main problem was that async I/O was slow. We set

1

filesystemio_options='SETALL'

in the spfile, and this helped a lot. As I understand it, this enables asynchronous I/O, which means that rather than waiting for confirmation after writing, the database keeps on sending data to the SAN. This sped up the I/O by a factor of about 3.

How Does a Database Use IO?

We have a shiny new database server. We ran some tests on it, and found it was about the same speed as the old one. Worryingly the IO system seemed to be struggling to keep up with the load.

In case you don’t know, IO is short for Input/Output, and is used to describe the use of a hard disc, or similar storage. We all know that hard discs are slow compared to memory, but for a database to work well, the IO system needs to be able to keep up with the work the database is doing. Otherwise the database will eventually appear to lock up.

We use a Virtual Private Network - a VPN to access all our work servers. But we use MS teams for meetings, and our VPN admin asks us not to to push loads of video over the VPN. So what to do? Disconnecting from the VPN every time a call came in got old pretty fast. And what do you do if you want to show a colleague something which required VPN access?

A colleague had created a new service operation in peoplesoft integration broker, but it wasn’t working. After a lot of investigation, and a call to Oracle, we found the cause was SNI: Service Name Indication. This is a facility which allows several different hostnames to be on the same IP address, but still listen in HTTPS.

It seems the Integration Broker converts the hostname to an IP address before trying to contact the remote site. The problem is then that the remote site doesn’t know which website it wishes to talk to, and this causes a handshake failure. The messages in the logs are not helpful to say the least.