Workaround for sdoloadj.sql errors with Datapatch in 19.9.0 and 19.10.0

The other week a customer from my hometown alerted me about an issue they saw when applying the 19.9.0 RU. A long sequence of errors in the datapatch run, all of them signaled from sdoloadj.sql. They opened an SR. And Support had an – understandable – recommendation the customer didn’t want to implement: Install JAVAVM. But thanks to my PM mate, Hans Viehmann (Mr Spatial), here is a simple Workaround for sdoloadj.sql errors with Datapatch in 19.9.0 and 19.10.0. And it may even apply to later releases, too.

What happens?

Take any Oracle 19c database pre-19.9.0. And now you’d like to patch it to Oracle 19.9.0 or even better, 19.10.0. In case you have SPATIAL installed but – regardless whether intentionally or unintentionally – no JAVAVM component, you will receive errors now.

At first, having SPATIAL in a database does NOT require JAVAVM to be present. This is good for many reasons. At first, as you may be aware, you don’t need to license Spatial anymore since December 2019. And Spatial is available even in Standard Edition 2 now. This rule applies even for 11.2.0.4 you hopefully have upgraded already to Oracle 19c already.

But back to the topic I wanted to discuss here – the errors you may see now. When you patch now to 19.9.0 or 19.10.0 with SPATIAL in CDB_REGISTRY but no JAVAVM present there, you will see a long list of such errors:

-> Error at line 79364: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name

And when you did into this sql file, it becomes very obvious what’s failing here. The scripts has such calls – actually many of them:

Rem ********************************************************************
Rem #16473696: Indicate Oracle-Supplied object
@@?/rdbms/admin/sqlsessstart.sql
Rem ********************************************************************

alter session set current_schema=SYS;

call dbms_java.grant_permission('SYSTEM', 'java.io.FilePermission',
                                 '<<ALL FILES>>', 'read');
call dbms_java.grant_permission('MDSYS', 'SYS:java.io.FilePermission',
                                'md/jlib/*', 'read');
call dbms_java.grant_permission('MDSYS', 'SYS:java.io.FilePermission',
                                'sdo/demo/georaster/jlibs/*', 'read');
...

These calls obviously will need to fail when JAVAVM is not present in your database.

Datapatch shows errors

When you invoke “datapatch -verbose“, it calls this script sdoloadj.sql. And then it will fail with many errors. The output will look similar to mine here:

$ ./datapatch -verbose
SQL Patching tool version 19.9.0.0.0 Production on Thu Feb 25 23:12:49 2021
Copyright (c) 2012, 2020, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18227_2021_02_25_23_12_49/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.9.0.0.0 Release_Update 200930183249: Installed
  SQL registry:
    Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 25-FEB-21 10.15.45.340817 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  No interim patches need to be rolled back
  Patch 31771877 (Database Release Update : 19.9.0.0.201020 (31771877)):
    Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.9.0.0.0 Release_Update 200930183249
  No interim patches need to be applied

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...done
Patch 31771877 apply: WITH ERRORS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31771877/23869227/31771877_apply_HUGO_2021Feb25_23_15_25.log (errors)
  -> Error at line 79364: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79372: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79380: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79392: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79400: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79411: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79419: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79426: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79433: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79440: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79447: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79459: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79467: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79475: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79483: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79491: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79502: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79518: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79531: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79539: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79549: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79560: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79570: script md/admin/sdoloadj.sql
      - ORA-06576: not a valid function or procedure name
  -> Error at line 79614: script md/admin/sdoloadj.sql
      - ORA-06550: line 9, column 12:
      - PLS-00201: identifier 'SYS.DBMS_JAVA' must be declared
      - ORA-06550: line 9, column 12:
      - PL/SQL: Statement ignored
      - ORA-06550: line 18, column 12:
      - PLS-00201: identifier 'SYS.DBMS_JAVA' must be declared
      - ORA-06550: line 18, column 12:
      - PL/SQL: Statement ignored
  -> Error at line 79656: script md/admin/sdoloadj.sql
      - ORA-06550: line 2, column 4:
      - PLS-00201: identifier 'DBMS_JAVA.GRANT_PERMISSION' must be declared
      - ORA-06512: at line 24
      - ORA-06550: line 2, column 4:
      - PL/SQL: Statement ignored
      - ORA-06512: at line 4

Please refer to MOS Note 1609718.1 and/or the invocation log
/u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18227_2021_02_25_23_12_49/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Thu Feb 25 23:17:57 2021

In addition, the customer told me that they’ve had now invalid objects in their databases. Which is not nice and unwanted of course.

Workaround

Now Hans comes into play. He checked and got back quickly to me with a workaround.

“Exchange the sqlloadj.sql with an empty file.”

And this is what I tried right away.

cd $ORACLE_HOME/md/admin
mv sqlloadj.sql sqlloadj.sql_withcontent
touch sqlloadj.sql

Then you need to kick of datapatch again:

$ ./datapatch -verbose
SQL Patching tool version 19.9.0.0.0 Production on Thu Feb 25 23:24:01 2021
Copyright (c) 2012, 2020, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18412_2021_02_25_23_24_01/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.9.0.0.0 Release_Update 200930183249: Installed
  SQL registry:
    Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 25-FEB-21 10.15.45.340817 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  No interim patches need to be rolled back
  Patch 31771877 (Database Release Update : 19.9.0.0.201020 (31771877)):
    Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.9.0.0.0 Release_Update 200930183249
  No interim patches need to be applied

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...done
Patch 31771877 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31771877/23869227/31771877_apply_HUGO_2021Feb25_23_26_36.log (no errors)
SQL Patching tool complete on Thu Feb 25 23:29:06 2021

And “datapatch -verbose” completes now flawless. No invalid objects are found anymore.

BUT … of course, there is always a “but” … you should be careful with this workaround if this home serves several databases with mixed installs. In case you have another database with SPATIAL and JAVAVM, the sdoloadj.sql needs to be run by datapatch.

Summary

As you read above, datapatch invocation may fail when you have SPATIAL in your database(s) but no JAVAVM. And please don’t follow the advice of installing now JAVAVM. Of course, this is a quick workaround but I guess not what you want. There may be many reasons why you don’t have JAVAVM in your database(s).

Use the simple workaround of exchanging sqlloadj.sql with an empty file of the same name.

And then “datapatch -verbose” will apply everything correctly. Plus, you won’t see any invalid objects in your database afterwards.

Why has this happened? I guess this is credited to the fact that tests are done with all components installed. But most likely no test run has happened with “SPATIAL only but no JAVAVM”.

I hope this gets fixed with 19.11.0.

The issue gets tracked internally as Bug 32573942 (DATAPATCH FLAGS ORA-6576 FROM SDOLOADJ.SQL WHEN SPATIAL PRESENT BUT NO JAVAVM 19.9.0 19.10.0).

Further Links and Information

–Mike

 

Share this: