Lately I started patching a client’s database (126.96.36.199) 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:
- Run the datapatch script and let this patch fail
- Manually drop all the bad objects that got created under sys (there is a list of them)
- 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?