Datapach issues

This week I worked on a messy patch. I have a RAC environment with 12.1.0.2 and an old PSU and all kind of one-off patches and I wanted to install the latest PSU (180417). It sounds simple, but it’s not so simple. The thing is that I have 4 database running from the same ORACLE_HOME, all of them are RAC, while some of them are stand-alone, some are primary for standby located on a different RAC and some are standby for a primary located on a different RAC. And the problem with that is that you cannot install a PSU on the primary first, either together or patch the standby first.

I had a chat with Mike Dietrich and he suggested kind of a cool solution: install a new ORACLE_HOME with the PSU and all of the one-offs (I’ve got about 8 or 9 of them) and then just move the database to the new home and run the datapatch script.
So that’s what I did. I installed a new ORACLE_HOME with the PSU (and OJVM PSU) and all the one-offs that I needed (or were recommended by Oracle) and was ready to start moving the database.
The process is quite simple, we stop the database using srvctl (a complete outage), then using “srvctl modify” we set the ORACLE_HOME for the db resource to the new home and start the database. After the database is up we need to run the datapatch script to load all the SQL scripts to the database. And this is where the problems began…
The first database I moved to the new home is a stand-alone database. This database has spatial installed and one of the one-off patches is because of a spatial bug we hit (patch 27655364). I executed the datapatch and it seemed fine until it got to the spatial patch’s script. I then got an error:

Patch 27655364 apply: WITH ERRORS
Error at line 8: new 5: p_description => 'ORA-06502: PL/SQL: NUMERIC OR6512: AT MDSYS.SDO_GCDR',

I checked the log and it seemed fine, but it had a comment containing this ORA- message. It seems that the log validator just search for “ORA-” string or something, so it identified that as an error. Funny. I actually found a bug on this (note 2330169.1), but there is no patch for my PSU and I don’t know if it’s critical anyway (I opened an SR to see if that’s true or if I need to do something about it).
The next database was one with a standby. Now this is still a test environment, so I could patch both sides. I started by restarting the standby side, then I did the same with the primary side and executed the datapatch script. In this database I didn’t have all the extras installed (intermedia, spatial, JVM, etc.). With this database, datapatch had more errors. One of the patches I had is for expdp remap_schema issue (bug 19501696), and datapatch reported lots of errors related to objects in MDSYS schema (user does not exists, object does not exists, etc.). I didn’t understand 2 things here: 1) why does expdp patch need MDSYS user? and 2) if a patch needs a user and the user does not exist, why does it fail? I would expect datapatch to check it first and finish successfully if this script is not needed (because, like in my case, sometimes you have different databases with different features running from the same ORACLE_HOME). I wouldn’t be bothered about it too much, unless I could see in the logfile all kind of “create” statements that I suspect created some junk objects in my SYS schema. Bummer…
So, I’ve got two new SRs from today, and I’m now waiting for updates from Oracle. Will keep you posted.