JAVAVM and XML clean up in Oracle Database 11.2-12.2 is a tough topic. Many have suffered from the rolling patch upgrade issues with the OJVM (Oracle Java Virtual Machine) even though most of the issues have been cured with the recent patches.
Please see my previous blog posts starting here:
- The OJVM Saga – and how to solve it (Part 1) – Sep 5, 2016
- OJVM and Standby-First Patching – Aug 8, 2016
- OJVM Removal – Jun 22, 2015
And please see also this very important MOS Note: 2217053.1 – RAC Rolling Install Process for the “Oracle JavaVM Component Database PSU” (OJVM PSU) Patches explaining the improved process for OJVM patching avoiding downtime in many cases.
JAVAVM and XML Clean Up in Oracle Database 11.2-12.2
Before you start removing anything from your database please make sure you read my introductory blog post about how to Remove and Clean Up Components from Oracle 11.2 – 12.2 . You’ll find links to monitor proper component removal there as well as in the SQL Scripts Repository on this blog.
In the database’s registry, the two components JAVAVM and XML are bound closely together. I will describe below how to remove both at the same time.
Please be aware that the removal of JAVAVM and XML will invalidate the dependent components:
- Oracle Multimedia
- Oracle Spatial
- Oracle OLAP
- Oracle Expression Filter
- Oracle Rules Manager
as well. Please check and understand my blog post OJVM Removal from Jun 22, 2015 explaining more about dependent objects and components.
If you want to read and learn more about Oracle’s JVM please check this link:
Oracle Database 11.2.0.4
You will find the process for the JAVAVM removal in Oracle Database 11.2.0.4 clearly described in MOS Note:1112983.1 – How to Reload the JVM in 11.2.0.x. Please check this note as it contains extra steps for verification. Basically the removal process looks like this:
$ sqlplus / as sysdba SQL> @?/rdbms/admin/catnojav.sql SQL> @?/xdk/admin/rmxml.sql SQL> @?/javavm/install/rmjvm.sql SQL> @?/rdbms/admin/utlrp.sql SQL> delete from registry$ where status='99' and cid in ('XML','JAVAVM','CATJAVA'); SQL> commit;
The recompilation is required. In my environments the database doesn’t have to be restarted or in upgrade mode as the above note implies. But as you’ll test this first in a test environment you may consider extra steps from the above note.
If you are removing JAVAVM in Oracle 11.2.0.4 make sure to remove also Rules Manager and Expression Filter as otherwise a subsequent upgrade will raise an error:
ORA-06550: line 1, column 7: PL/SQL: Statement ignored PLS-00201: identifier ‘SYS.DBMS_JAVA’ must be declared
Thanks to B. Müller for alerting me on this missing puzzle piece (see the comments section below).
Oracle Database 12.1.0.2
In Oracle Database 12.1.0.2 the process to remove JAVAVM and XML is more or less the same as in Oracle 11.2.0.4. The only difference: you must clean up two objects after removal manually.
$ sqlplus / as sysdba SQL> @?/rdbms/admin/catnojav.sql SQL> @?/xdk/admin/rmxml.sql SQL> @?/javavm/install/rmjvm.sql SQL> @?/rdbms/admin/utlrp.sql SQL> delete from registry$ where status='99' and cid in ('XML','JAVAVM','CATJAVA'); SQL> commit;
As the process will result in two leftover packages you will have to clean up these manually:
$ sqlplus / as sysdba SQL> drop package SYS.JVMRJBCINV; SQL> drop package SYS.JAVAVM_SYS;
Then the removal process has been completed successfully.
Oracle Database 12.2.0.1
non-CDB
The removal process in Oracle Database 12.2.0.1 for a non-CDB is exactly the same as in Oracle Database 12.1.0.2 for a non-CDB:
$ sqlplus / as sysdba SQL> @?/rdbms/admin/catnojav.sql SQL> @?/xdk/admin/rmxml.sql SQL> @?/javavm/install/rmjvm.sql SQL> @?/rdbms/admin/utlrp.sql SQL> delete from registry$ where status='99' and cid in ('XML','JAVAVM','CATJAVA'); SQL> commit;
As the process will result in two leftover packages you will have to clean up these manually:
$ sqlplus / as sysdba SQL> drop package SYS.JVMRJBCINV; SQL> drop package SYS.JAVAVM_SYS;
Then the removal process has been completed successfully.
CDB
The removal process from a container database looks differently due to the need to execute the removal scripts with catcon.pl
.
First of all you must execute the removal scripts. You can run them in all containers.
$ cd $ORACLE_HOME/rdbms/admin $ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -b catnojav -d /u01/app/oracle/product/12.2.0.1/rdbms/admin catnojav.sql $ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -b rmxml -d /u01/app/oracle/product/12.2.0.1/xdk/admin rmxml.sql $ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -b rmjvm -d /u01/app/oracle/product/12.2.0.1/javavm/install rmjvm.sql $ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
After the mandatory recompilation is completed you need to create a script to remove the component entries from REGISTRY$. This script needs to be executed in each container separately to avoid ORA-65023
errors:
$ vi reg.sql delete from registry$ where status='99' and cid in ('XML','JAVAVM','CATJAVA'); commit;
Execute the script in each container, one after another:
$ $ORACLE_HOME/perl/bin/perl catcon.pl -c 'PDB2' -n 1 -b reg_pdb2 -d /u01/app/oracle/product/12.2.0.1/rdbms/admin reg.sql $ $ORACLE_HOME/perl/bin/perl catcon.pl -c 'PDB1' -n 1 -b reg_pdb1 -d /u01/app/oracle/product/12.2.0.1/rdbms/admin reg.sql $ $ORACLE_HOME/perl/bin/perl catcon.pl -c 'PDB$SEED' -n 1 -b reg_seed -d /u01/app/oracle/product/12.2.0.1/rdbms/admin reg.sql $ $ORACLE_HOME/perl/bin/perl catcon.pl -c 'CDB$ROOT' -n 1 -b reg_cdb -d /u01/app/oracle/product/12.2.0.1/rdbms/admin reg.sql
And interesting enough, you don’t have to cleanup anything afterwards. All set.
Component Clean Up Series
- Remove and Clean Up Components from Oracle 11.2 – 12.2 – General Guidelines and Precautions
- APEX – Oracle Application Express Clean Up
- OWM – Oracle Workspace Manager Clean Up
- DV – Oracle Database Vault Clean Up
- OLS – Oracle Label Security Clean Up
- SDO – Oracle Spatial Data Option Clean Up
- CONTEXT – Oracle Text Clean Up
- ORDIM – Oracle Multi Media Clean Up
- XOQ – Oracle OLAP API Clean Up
- APS – Oracle OLAP Analytical Workspace Clean Up
- AMD – Oracle OLAP Catalog Clean Up
- OWB – Oracle Warehouse Builder Clean Up
- EXF/RUL – Oracle Expression Filters and Rules Manager Clean Up
- EM – Enterprise Manager Database Control Clean Up
- JAVAVM/XML – Oracle Java Virtual Machine and XDK Clean Up
- XDB – Oracle XML Database Clean Up
Finally download a slide deck about the Component Clean Up on this blog:
https://mikedietrichde.com/slides/
Hi,
just upgraded a 11.2.0.4 DB to 12.2.0.1.
On this DB the JAVAVM was removed (in 11.2.0.4) with the commands from this post.
The upgrade failed with
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
PLS-00201: identifier ‘SYS.DBMS_JAVA’ must be declared
The JAVAVM removal process wasn’t complete. One more script must be run for acomplete removal:
@?/rdbms/admin/catnoexf.sql
It is also listed in the MOS articel linked in the post.
Upgrade went fine after running the script.
If you are wondering what I did with the failed upgrade:
Just flashed back the Database to a guaranteed restore point created just before the upgrade.
Regards,
B. Müller
Thanks “B”,
that’s an important hint – and you are correct, I missed to point to EXF/RUL in my text for 11.2.0.4. Added this now.
Again thanks!
Mike
Why do you remove the deinstalled components from registry by:
delete from registry$ where status=’99’ and cid in (‘XML’,’JAVAVM’,’CATJAVA’);
As far as I know when the status is “REMOVED” an upgrade/patch will not be performed for those components.
Raise theses entries any trouble with future upgrades? What is the reeason of the deleting?
I like CLEAN solutions.
And as far as I remember from my tests, the component status did not change at all.
Thanks!
Mike
I understand that JavaVM is used for XSU, XDKJ & XDKC. Is there a query to run to determine if the database is using any of these 3 components of XDK? Or how XDK is being used in the database?
Thanks!
Hi Karl,
the queries I’m aware of are all mentioned in:
MOS Note: 2217053.1 – RAC Rolling Install Process for the “Oracle JavaVM Component Database PSU” (OJVM PSU) Patches
But I have learned over time, that it is not simple to detect if a component is used or not. Unfortunately, this is not documented centrally nor is there a clear guideline for every component. Some have published queries on MOS, others haven’t or refuse to.
Cheers,
Mike
Our application team isn’t sure if they are using XDK? Is there a query that can be run to determine if the application code is using it? Is it possible to just remove the xml by running @?/xdk/admin/rmxml.sql?
Hi Karl,
I commented below to your earlier question.
I don’t think that you can remove only XDK as far as I remember. I think, it will leave a lot of invalid things. You either remove the entire block (as explained) or leave it inside.
Thanks,
Mike
Hi
I tested with 19.6 non-cdb database, now I know that you tested until 12.2, but still.
I did all the steps, there were no invalid objects left after the utlrp.sql (there was no need to drop 2 leftover packages JVMRJBCINV and JAVAVM_SYS, since these were already dropped), but I noticed, that there are some JAVA objects present after this.
SQL> select object_type, count(1) from dba_objects where object_name like ‘%JAVA%’ group by object_type;
OBJECT_TYPE COUNT(1)
———————– ———-
INDEX 11
SYNONYM 18
PACKAGE BODY 2
PACKAGE 2
DIRECTORY 1
TYPE 6
SEQUENCE 1
TABLE 12
VIEW 21
9 rows selected.
Bear in mind these are all sys objects, not user defined java objects, since dba_feature_usage_statistics did not show usage for user.
Also, these are all in VALID state, but is this normal behaviour, that some sys JAVA objects left present..?
Who is the owner of these?
And what is in your DBA_REGISTRY by then (select comp_id, status, version, owner from dba_registry order by 1;)
Cheers,
Mike
After running these commands on a 19.3.0 “vanilla” database (CDB and one PDB), I was left with the following invalid objects in the PDB (and PDB$SEED) that could only be dropped by using these commands (the CDB had no remaining invalid objects, and the JAVAVM and XML registry components were successfully removed from the CDB and PDBs):
connect / as sysdba
alter session set container = PDB1;
col owner format a10
col object_name format a30
— list invalid objects…
select owner, object_type, object_name
from sys.dba_objects where status != ‘VALID’
and object_type != ‘UNDEFINED’ — EXCLUDE Materialized-Views
order by 1, 2, 3
/
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$ATTRIBUTES$’);
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$BINDINGS$’);
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$INODE$’);
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$NODE_NUMBER$’);
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$PERMISSIONS$’);
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$REFADDR$’);
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$SHARED$OBJ$’);
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$SHARED$OBJ$SEQ$’);
exec dbms_pdb.exec_as_oracle_script(‘drop package SYS.JAVAVM_SYS’);
exec dbms_pdb.exec_as_oracle_script(‘drop package SYS.JVMRJBCINV’);
exec dbms_pdb.exec_as_oracle_script(‘drop package SYS.OJDS_CONTEXT’);
exec dbms_pdb.exec_as_oracle_script(‘drop package SYS.DBMS_JAVA_MISC’);
exec dbms_pdb.exec_as_oracle_script(‘drop trigger SYS.OJDS$ROLE_TRIGGER$’);
NOTE: for the PDB$SEED database you need to open it read-write to drop these objects! Command sequence shown here:
connect / as sysdba
alter session set container=PDB$SEED;
col comp_id format a10
col comp_name format a40
col version format a20
col status format a15
select comp_id, version, status, comp_name from dba_registry order by 1;
alter session set “_oracle_script”=TRUE;
alter pluggable database pdb$seed close immediate instances=all;
alter pluggable database pdb$seed OPEN READ WRITE;
col owner format a10
col object_name format a30
— list invalid objects…
select owner, object_type, object_name
from sys.dba_objects where status != ‘VALID’
and object_type != ‘UNDEFINED’ — EXCLUDE Materialized-Views
order by 1, 2, 3
/
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$ATTRIBUTES$’);
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$BINDINGS$’);
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$INODE$’);
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$NODE_NUMBER$’);
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$PERMISSIONS$’);
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$REFADDR$’);
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$SHARED$OBJ$’);
exec dbms_pdb.exec_as_oracle_script(‘drop synonym SYS.OJDS$SHARED$OBJ$SEQ$’);
exec dbms_pdb.exec_as_oracle_script(‘drop package SYS.JAVAVM_SYS’);
exec dbms_pdb.exec_as_oracle_script(‘drop package SYS.JVMRJBCINV’);
exec dbms_pdb.exec_as_oracle_script(‘drop package SYS.OJDS_CONTEXT’);
exec dbms_pdb.exec_as_oracle_script(‘drop package SYS.DBMS_JAVA_MISC’);
exec dbms_pdb.exec_as_oracle_script(‘drop trigger SYS.OJDS$ROLE_TRIGGER$’);
— list invalid objects…
select owner, object_type, object_name
from sys.dba_objects where status != ‘VALID’
and object_type != ‘UNDEFINED’ — EXCLUDE Materialized-Views
order by 1, 2, 3
/
— optional if needed…
–@?/rdbms/admin/utlrp.sql
select comp_id, version, status, comp_name from dba_registry order by 1;
alter pluggable database pdb$seed close immediate instances=all;
alter pluggable database pdb$seed OPEN READ ONLY;
alter session set “_oracle_script”=FALSE;
alter pluggable database pdb$seed close immediate instances=all;
alter pluggable database pdb$seed OPEN READ ONLY;
— list invalid objects…
select owner, object_type, object_name
from sys.dba_objects where status != ‘VALID’
and object_type != ‘UNDEFINED’ — EXCLUDE Materialized-Views
order by 1, 2, 3
/