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.
Photo by Mael BALLAND on Unsplash
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 sdoloadj.sql with an empty file.”
And this is what I tried right away.
cd $ORACLE_HOME/md/admin mv sdoloadj.sql sdoloadj.sql_withcontent touch sdoloadj.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.
Additional Information – May 3, 3021
Thanks to Ian who pointed me to this issue, the problem affects also a blank database creation with PDBs. The PDBs including the PDB$SEED will only open RESTRICTED – and even a “datapatch -verbose” run won’t fix this but results in the same recurring errors. This issue happens in:
- Oracle 19.9.0 – 19.11.0
- You create a CUSTOM database with DBCA and select SPATIAL for inclusion in CDB and PDBs, but you must not select JVM
Afterwards, you are in this situation. This is tagged under:
- Bug 32841297 – NEW DATABASE WITH SPATIAL ONLY LEADS TO PDBS OPEN RESTRICTED ONLY – RELATED TO BUG 32573942
If all goes well, you won’t hit neither issue anymore with the July 2021 Release Upgrade.
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 sdoloadj.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
Thank you Mike,
First of all, in the rename script you mention the file ‘sqlloadj.sql’ when renaming, in stead of ‘sdoloadj.sql’. Not a big issue when paying attention…
Secondly, this problem is also annoying if you create new CDB with PDB’s. When installing (19.3) and patching (19.10) first without creating databases, the creation of a new CDB and PDB will not have the datapatch applied as it runs in the same issue. (see dbca postDBCreation.log).
I have patched a freshly installed software only home (Windows) and created a CDB/PDB afterwards (with Spatial, no Java). When opening the PDB, it gave the error: ‘Warning: PDB altered with errors.’ and querying PDO_PLUG_IN_VIOLATIONS gave:
‘19.10.0.0.0 Release_Update 2101221802: APPLY with status WITH ERRORS in the PDB’.
After running datapatch (which shouldn’t be necessary), it gave this error with pending:
‘Patch 32062765 (Windows Database Bundle Patch : 19.10.0.0.210119 (32062765)):
Apply from 19.1.0.0.0 Feature Release to 19.10.0.0.0 Release_Update 210122180248’.
I have tested it with the workaround just after a fresh install/patch and now you will get a new CDB/PDB without problems. It seems when creating a new CDB/PDB, you will get an ‘old’ (19.1 or 19.3) first which will be datapatch-ed during creation (dbca).
Cheers, Ian
Thanks a lot, Ian – I was too puzzled by sqlload … I corrected it now – great catch! Thanks!!
I’m verifying the issue with a fresh 19.10.0 CDB now as well. If I can reproduce it, I will let the bug owners know right away.
And unfortunately, this issue is still open in 19.11.0 as well 🙁
Cheers,
Mike
Thank you Mike, First of all, in the rename script you mention the file ‘sqlloadj.sql’ when renaming, in stead of ‘sdoloadj.sql’. Not a big issue when paying attention…
Secondly, this problem is also annoying if you create new CDB with PDB’s. When installing (19.3) and patching (19.10) first without creating databases, the creation of a new CDB and PDB will not have the datapatch applied as it runs in the same issue. (see dbca postDBCreation.log).
I have patched a freshly installed software only home (Windows) and created a CDB/PDB afterwards (with Spatial, no Java). When opening the PDB, it gave the error: ‘Warning: PDB altered with errors.’ and querying PDO_PLUG_IN_VIOLATIONS gave:
‘19.10.0.0.0 Release_Update 2101221802: APPLY with status WITH ERRORS in the PDB’.
After running datapatch (which shouldn’t be necessary), it gave this error with pending: ‘Patch 32062765 (Windows Database Bundle Patch : 19.10.0.0.210119 (32062765)): Apply from 19.1.0.0.0 Feature Release to 19.10.0.0.0 Release_Update 210122180248’.
I have tested it with the workaround just after a fresh install/patch and now you will get a new CDB/PDB without problems. It seems when creating a new CDB/PDB, you will get an ‘old’ (19.1 or 19.3) first which will be datapatch-ed during creation (dbca).
Cheers, Ian
(sorry for the markup of previous post, it seems ‘newlines’ are removed?
Hi Ian,
I filed an additional bug for the creation of a new database problem. The workaround I proposed should work as well (an empty sdoloadj.sql instead of the existing one) before you create a database with SPATIAL but no JAVAVM. I updated the blog post with the bug number for your reference – and thanks again for the hint.
Thanks, Mike
This really help !! Thanks a lot