GotoDBA Features One-off Patch Frustration

One-off Patch Frustration

Lately I started patching a client’s database (12.1.0.2) to the latest PSU (180417). This is a RAC environment with streams and all kind of other features, so over the time we hit quite a lot of different bugs. When we planned this PSU (we installed the bundle patch version), we added about 7 one-off patches (some are recommended by Oracle and some we had to add because the bugs affected us quite badly).

So far so good, the thing is that I a few databases running from one ORACLE_HOME and these databases have different features installed. One of the databases has spatial installed and needed a one-off patch installed, so one of the 7 one-off patches I installed was 19501696.
The problem started when I patched one of the databases that doesn’t have spatial. When I got to the datapatch part, this specific patch failed with a bunch of errors. The more disturbing thing was the messages in the log file. The sqlpatch script performs “ALTER SESSION SET CURRENT_SCHEMA = MDSYS;” (which failed of course, as I don’t have the MDSYS user), and then did some stuff to spatial objects, including creating new objects…
I opened an SR for that, and after some back and forth, cleaning these objects, etc. I got the final answer. The best practice for this scenario is this:

  1. Run the datapatch script and let this patch fail
  2. Manually drop all the bad objects that got created under sys (there is a list of them)
  3. Manually update the REGISTRY$SQLPATCH table and set STATUS to SUCCESS

Seriously? This is how this process should go?
I know that I can tell datapatch which patches to install, but when I have 7 currently (and maybe more in the future), that would be really annoying.
Is it too much to expect the sqlpatch to check if the feature/user exists and just complete successfully if not?

2 thoughts on “One-off Patch Frustration”

  1. That is why we have dedicated oracle binaries for each oracle database in clustered/RAC environments.

    1. Yes, it helps with patching in general, but it wasn’t my call and they still want to keep the same home for all of the DBs. In any case, this shouldn’t happen, datapatch should take care of it and not fail and create stuff under SYS…

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Post