Using Oracle pluggable databases to make patching easier

We have a client that has multiple applications in one database (I know), each separated into different tablespaces. We inherited this and they want to stick to it.

The problem is that we haven't been able to patch for years because although some applications say we can, others say not yet.

I thought a solution might be to use pluggable databases. They have an existing V19C SE database which is not a container DB. 

The theory is that we create a Container database and patch that to the latest release, then migrate the applications that can be patched into pluggable databases - we can export / import initially and then use the pluggable features to move the databases.

Eventually we'll have all the applications in their own pluggable database in the container running the latest patch, with the original non-container database now not in use. We drop that, recreate it as a container database and leave it empty.

The next patch comes out, we patch the empty one and then unplug and plug in the application PDBs that are able to be patched. 

Eventually all the application PDBs will be in this updated one, the other one empty and we just rinse and repeat.

There are a a couple of issues - first, we need enough space on the host to install another ORACLE_HOME. Second, Oracle allows 3 PDBs in a container DB for free - any more and a license is needed. Note: during testing I discovered that Standard Edition (which the client is running) is limited to 2 PDBs, so I suspect this idea isn't going to fly.

I did wonder about application connectivity, but we should be able to use the same service name, especially since the databases are on the same host.

They use VM snapshots for DR, so that won't be a problem, but if DataGuard was running we'd need to consider that as well, some config changes would probable be needed, or the configuration may need recreating each time. You'd also probably want 2 separate listeners.

On to the testing. I created a new VM (I use Proxmox) with enough space for 2 ORACLE_HOMEs, and installed V19 in each.

I then created a database in each home, one was a container, one was non container to replicate the existing client database. I then patched the container database to the n-1 release (April 2024).

I created 3 tablespaces, then 3 tables, in the non-container database to simulate the different applicants in the client environment.

I tried to create 3 PDBs in the container database, but Standard Edition is limited to 2. 

                                        

I then exported the data from the non container db into the PDBs.

I needed to create a user in the pdb to allow for the import:

create user impuser identified by <password>;

grant create session to impuser;

grant dba to impuser;

Then imported the data

So there is now a PDB with the data from the source data1 tablespace. I repeated the process for data2.

So there are now 2 PDBs with the data from the source. The applications can now be pointed to this, the database is a recent patch.

We can now drop the original source database and recreate it as a container. I just used dbca to both drop and create.

This is where I encountered something strange. I created the data1 pluggable database, then when I came to create the data2 PDB I got this message:

But the message I got when I did this before said I could only create 2 in a Standard Edition database, and there are 2:

Turns out this is a bug, and a second PDB can be created using SQLPlus, so I did that.

Now we have 2 container databases, one patched to April 2024 running the live databases, the other the base 19.3 release, both with data1 and data2 pluggable databases.

I then patched the new container database to the July 2024 patch, and will copy the pluggable databases across from the April 2024 patched database.

I'll use a db link to clone the PDBs.

Login to the source and create a user to clone the databases

Shutdown the pluggable database then open in RO mode

Create tnsnames.ora entries on both the source and target hosts

Check a tnsping

Create a database link in the target database pointing to the data1 PDB

Drop the TARGET pluggable database

Create the pluggable database from the source using the database link, then open it

You can now drop the source pluggable database, then clone the other one when it's ready to move to the latest patched database and drop that.

The next patch can be applied to the now empty database and the pluggable databases moved across when ready.

If this patch method is considered, you'd need to do it in non prod first with databases of the same size to determine the time it takes - if you are able to get an outage for all the databases in the same ORACLE_HOME at the same time, it may be that patching the home takes less time than a PDB clone, in which case this method may not be of use.