Does OJVM patching fail again with ORA-1403 in 19.31?

Some of you had been trapped by ORA-1403 during patching to Oracle Database 19.30. I blogged about this in February this year, and I had to update the blog post a few times until we collected all the issues. The root cause got fixed and included in Release Update 19.31. But over the past days I received a good number of messages about the issue being there in 19.31 again. So,  does OJVM patching fail again with ORA-1403 in 19.31?. Yes, and no.

Photo by Towfiqu barbhuiya on Unsplash

Why do some see the error again?

First of all, the issue is clearly fixed in 19.31.

You don’t believe me?

Then check with oradiff, and see that the fix for bug 38844367: 19.30 OJVM PATCH APPLY IS FAILING ON SECOND NODE IN RAC ENVIRONMENT WITH ERROR IN JAVAVM is included in RU 19.31. And let me point out that the bug text can be ignored since it is misleading. This has nothing to do with RAC. Some of you commented about this on the blog but the subject gets set by the bug filer, and if it doesn’t get updated early enough, it will stay as the subject forever.

But if it is fixed, why do you see it again? And why only some of you, and others won’t?

This has to do with three factors.

At first, this patch has a patch_apply.sql and a patch_rollback.sql script. These scripts get shipped with 19.30, and of course with 19.31. The RU 19.30 shipped a faulty version of this script, it missed the exception handling for the case that you have JAVAVM in the root container but not in an individual PDB. This was the problem described in my previous blog post.

Secondly, while the fix for bug 38844367 ships now an updated patch_apply.sql with the corrections into 19.31, it does not ship a corrected patch_rollback.sql ==> sqlpatch/38523609/28341036/rollback_files/19.1.0.0.0/javavm/install/bug35933540_rollback.sql. While this looks like another mistake, we need to look at how JVM patches get process during apply and rollback.

Which brings me to the third factor. JVM patches get applied and rolled back SQL/PLSQL-wise at the beginning of a patching run, even before the RU changes get merged into each other. They are treated like one-off fixes, even when they come with an OJVM bundle. When datapatch detects a divergence between binary install and database install, it will fix this. For JVM patches, this means in that specific case going from 19.30 to 19.31, datapatch will detect:

1. Binary is 19.31, database dictionary is 19.30
2. Rollback the 19.30 JVM changes
3. Apply the 19.31 JVM changes
4. Rollback all changes tied to one-off patches (incl MRPs and CSPUs)
5. Then merge the RU changes between 19.30 and 19.31
6. Finally, apply one-off changes (incl MRP and CSPU changes)

The (2=>3) step is the friction point. The script  sqlpatch/38523609/28341036/rollback_files/19.1.0.0.0/javavm/install/bug35933540_rollback.sql is called, and it does not have the proper error handling. Hence, it fails again.

How do you overcome it?

There are several ways to overcome this issue.

Option 1 – Apply a backport to 19.30

In case  you did apply the one-off patch 38844367 – 19.30 OJVM PATCH APPLY IS FAILING ON SECOND NODE IN RAC ENVIRONMENT WITH ERROR IN JAVAVM/INSTALL/BUG35933540_APPLY.SQL FILE you should not see the error.

This is the sequence being tested by the developer:

• Apply
  • applied OVJMRU 19.29
  • ran datapatch
  • applied OJVMRU 19.30
  • applied BLR 38844367 for OJVMRU 19.30
  • ran datapatch for OJVMRU 19.30 successfully
  • apply OJVMRU 19.31
  • ran datapatch for OJVMRU 19.31 successfully
• Rollback
  • rollback -id 38906621 ( 19.31)
  • ran datapatch
  • rollback -id 38844367 ( BLR 38844367)
  • rollback -id 38523609 (19.30)
  • ran datapatch==> Back to 19.29 successfully. 
• From 19.30 to 19.31
  • apply 38844367
  • apply 38906621
  • run datapatch
• From pre-19.30 to 19.30
  • apply 38523609
  • apply 38844367
  • run datapatch
• Rollback
  • rollback 19.30
  • apply 38844367
  • rollback -id 38844367
  • rollback -id 38523609
  • run datapatch

I just share this with you, I haven’t verified it by myself.

Option 2 – AutoUpgrade

Unfortunately, AutoUpgrade has a flaw. And customers helped us to nail it down. It will be fixed in one of the coming versions of AutoUpgrade.  AutoUpgrade fails with:

Info
[Exception: PATCH112 Err message: AutoUpgradePatchingException]
[AutoUpgrade failed to move the source DB to the target ORACLE_HOME. For details, view the log file located at /u01/app/oracle/autoupgrade/log/DBMTEST/DBMTEST/103/db_patching/autoupgrade-jar.log]

What is the issue? Even if you use prefix.ignore_errors=ORA-01403,ORA-06512 AutoUpgrade will skip it when you patch. Joe confirmed that this happens only when patching, and that it is going to be fixed by us.

Alexander from MUC Airport shared his workaround with me until we fix it in AutoUpgrade:

1. Run AutoUpgrade for patching
2. It will hit the error during the SQL/PLSQL rollback part of the patch
3. Start datapatch manually with ./datapatch -ignorable_errors=ORA-01403,ORA-06512 from the 19.31 home
4. Invoke AutoUpgrade again, and it will continue from where it was left of

He commented that this works but is a bit nasty since he can’t include it easily into his Ansible playbook.

Option 3 – Copy files around manually

In case you applied the 19.31 OJVMRU (patch 38906621) to the new $ORACLE_HOME, you must copy the backup file $ORACLE_HOME/javavm/install/bug35933540_rollback.sql to $ORACLE_HOME/sqlpatch/38523609/28341036/rollback_files/19.1.0.0.0/javavm/install/ .

cp $ORACLE_HOME/javavm/install/bug35933540_rollback.sql sqlpatch/38523609/28341036/rollback_files/19.1.0.0.0/javavm/install/

$ORACLE_HOME/OPatch/datapatch

An alternative should be to copy the file from the patch-unzip directory manually given the setup that you currently have OJVMRU 19.30 (38523609), and you plan to go to OJVM 19.31 (38906621).

cp <unzip_patch_dir>/38906621/files/javavm/install/bug35933540_rollback.sql $ORACLE_HOME/sqlpatch/38523609/28341036/rollback_files/19.1.0.0.0/javavm/install/

$ORACLE_HOME/OPatch/datapatch

.

Option 4 – Gold Image and copying files

This was shared with me by Jon since he builds the gold images for deployment on their target servers.

1. Copy the following directories from 19.30 home to our 19.31 gold image:

   $ORACLE_HOME/sqlpatch/38523609
   $ORACLE_HOME/sqlpatch/38844367

    

2. Replace the following files in our 19.31 gold image with $ORACLE_HOME/javavm/install/bug35933540_rollback.sql from 19.30 home:

   $ORACLE_HOME/sqlpatch/38523609/28341036/rollback_files/19.1.0.0.0/javavm/install/bug35933540_rollback.sql
   $ORACLE_HOME/sqlpatch/38844367/28466564/rollback_files/19.30.0.0.0-RU-Release_Update-251216232638/javavm/install/bug35933540_rollback.sql

Option 5 – Try again ignoring the errors

As Christian Pfundtner wrote in his blog post (in German language), when you patch manually, you can of course call datapatch again with the option to ignore errors:

cd $ORACLE_HOME/OPatch/

./datapatch -ignorable_errors=ORA-01403,ORA-06512

Summary

This error is caused by the faulty rollback script which gets executed automatically when you move from one OJVM to another. The previous changes will be rolled back as if it was a one-off patch, and the changes for the target version will be applied. The error happens during the implicit rollback.

You can overcome this by calling datapatch and have it ignore the errors. And normally, AutoUpgrade would allow the same but does skip the “ignore” routine during patching. Something we are going to fix.

Hopefully, this is going to be my last blog post. I fear that the MOS note KB867506 covering the original error hasn’t been updated with a lot of clarification except that you could use ./datapatch -ignorable_errors=ORA-01403,ORA-06512 to ignore the errors also during rollback.

Further Links and Information

• OJVM patching fails with ORA-1403 in 19.30 if JAVAVM is not installed
• oradiff
• Christian Pfundtner’s Blog Post
• KB867506 – Datapatch Apply of Patch 38523609: OJVM RELEASE UPDATE 19.30.0.0.0 fails while running javavm/install/bug35933540_apply.sql with the error ORA-01403: no data found

–Mike