JAVAVM and XML Clean Up in Oracle Database 11.2-19c

JAVAVM and XML Clean Up in Oracle Database 11.2-12.2JAVAVM and XML clean up in Oracle Database 11.2-19c 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:

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-19c

JAVAVM and XML Clean Up in Oracle Database 11.2-12.2Before 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 – 19c . 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 – 19c

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

At first, please be aware that there is a MOS Note: 2262919.1 (Repairing/Reinstalling JVM To A Multitenant Database (CDB and all PDBs) describing the theoretical removal process. But I learned from recent SRs that Support needs to correct your attempts as the note is incorrect. Please ask Support why the note isn’t updated when engineers know that it is incorrect and requires corrections. Using this note, you would stop after step (b). And I doubt that you need to remove EXF (Expression Filter) since this option did never exist in 12c – and Multitenant does not exist in 11g.

So you’ll find below my removal process.

It looks of course different to a non-container database 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 '''.''' catnojav.sql 
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -b rmxml -d '''.''' rmxml.sql
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -b rmjvmx -d $ORACLE_HOME/javavm/install rmjvm.sql
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

After these scripts completed, your REGISTRY$ looks like this in Oracle 19c:

    CON_ID COMP_ID    VERSION	      STATUS
---------- ---------- --------------- ---------------
	 1 CATALOG    19.0.0.0.0      VALID
	 1 CATJAVA    19.0.0.0.0      REMOVED
	 1 CATPROC    19.0.0.0.0      VALID
	 1 JAVAVM     19.0.0.0.0      REMOVED
	 1 ORDIM      19.0.0.0.0      VALID
	 1 RAC	      19.0.0.0.0      OPTION OFF
	 1 XDB	      19.0.0.0.0      VALID
	 1 XML	      19.0.0.0.0      REMOVED
	 2 CATALOG    19.0.0.0.0      VALID
	 2 CATJAVA    19.0.0.0.0      REMOVED
	 2 CATPROC    19.0.0.0.0      VALID
	 2 JAVAVM     19.0.0.0.0      REMOVED
	 2 ORDIM      19.0.0.0.0      VALID
	 2 RAC	      19.0.0.0.0      OPTION OFF
	 2 XDB	      19.0.0.0.0      VALID
	 2 XML	      19.0.0.0.0      REMOVED
...

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 $ORACLE_HOME/rdbms/admin reg.sql
$ORACLE_HOME/perl/bin/perl catcon.pl -c 'PDB1' -n 1 -b reg_pdb1 -d $ORACLE_HOME/rdbms/admin reg.sql
$ORACLE_HOME/perl/bin/perl catcon.pl -c 'PDB$SEED' -n 1 -b reg_seed -d $ORACLE_HOME/rdbms/admin reg.sql
$ORACLE_HOME/perl/bin/perl catcon.pl -c 'CDB$ROOT' -n 1 -b reg_cdb -d $ORACLE_HOME/rdbms/admin reg.sql

 

With an Oracle 12.2.0.1 database, we are all set.

Oracle Database 19.8.0 and newer

But since Oracle Database 19.8.0, there is a significant change in the java removal script – and this leads to leftovers. Unfortunately a lot of them.


Annotation:

If you intend to remove JVM from a Multitenant environment after 19.8.0 I highly recommend that you open an SR and discuss and chase Oracle Support. Customers reported several issues with my process below. This starts with leftover objects to “not dropped from PDB$SEED” and more. It is not under my control. And I know that Support needs to fix and adjust the procedures published on MOS. I asked for a bullet proof routine but so far there doesn’t seem any. So please please, check and discuss with Oracle Support. The below routine may not work flawless anymore unfortunately due to changes in the removal scripts.


 

And in addition, there is an issue with rmjvm.sql since Oracle 18c (and fixed only with 21c and newer with no backports yet)

Be aware that the MOS note may not be complete. It does not refer to the bug and gives advice on doing an UPDATE statement directly in the dictionary. Before you do this by yourself, open an SR and double-check with Oracle Support. Ensure that Support, once confirmed, adds the above bug number to your SR in the SR header page. The result of this issue will be that a DROP USER … CASCADE will fail.

So I will create two cleanup scripts:

  • Cleanup in the PDBs: drop_from_pdbs.sql
    DROP PACKAGE BODY SYS.DBMS_JAVA;
    DROP PACKAGE BODY SYS.JAVAVM_SYS;
    DROP PACKAGE BODY SYS.JVMRJBCINV;
    DROP PACKAGE BODY SYS.OJDS_CONTEXT; 
    DROP SYNONYM SYS.OJDS$ATTRIBUTES$;
    DROP SYNONYM SYS.OJDS$BINDINGS$;		       
    DROP SYNONYM SYS.OJDS$INODE$;		       	
    DROP SYNONYM SYS.OJDS$NODE_NUMBER$;	       	
    DROP SYNONYM SYS.OJDS$PERMISSIONS$;	       	
    DROP SYNONYM SYS.OJDS$REFADDR$;		       	
    DROP SYNONYM SYS.OJDS$SHARED$OBJ$;	       	 
    DROP SYNONYM SYS.OJDS$SHARED$OBJ$SEQ$;
  • Cleanup in CDB$ROOT: drop_from_cdb.sql
    DROP PACKAGE BODY SYS.DBMS_JAVA;

Execute the two scripts:

$ORACLE_HOME/perl/bin/perl catcon.pl -S -n 1 -b drop_from_pdbs -d $ORACLE_HOME/rdbms/admin drop_from_pdbs.sql
$ORACLE_HOME/perl/bin/perl catcon.pl -c 'CDB$ROOT PDB$SEED' -n 1 -b drop_from_cdb -d $ORACLE_HOME/rdbms/admin drop_from_cdb.sql

And now everything is clean again.

Component Clean Up Series

Finally download a slide deck about the Component Clean Up on this blog:
https://mikedietrichde.com/slides/

Share this: