I did blog several times about how to remove an unwanted component from a database. But yesterday I came across this interesting scenario worth a blog post.
How to install a component afterwards in Single-/Multitenant?
I haven’t checked all the MOS Notes but I recognized that some MOS Notes explaining how to (re-)create a component such as JVM are not updated to deal with a Single and/or Multitenant environment as they simply call the scripts from SQL*Plus. But the key to script execution in such an environment is catcon.pl, the perl driver meant to execute database scripts not only in the CDB$ROOT but also in the PDB$SEED and in all opened PDBs.
If you don’t use catcon.pl for script executions then you may get something in CDB$ROOT but not in the PDB$SEED and therefore not in any future PDB.
Example: Create JVM after database creation
Creating JVM is pretty well handled and described in MOS Note:1612279.1. With one exception: It does not talk about what to do in a Multitenant environment. I learned about this as one of my colleagues ran the steps described in the note from SQL*Plus connected to the CDB$ROOT – and recognized that JVM did not get created in the existing PDB(s).
The MOS Note:1612279.1 proposes the following steps:
-- Start of File full_jvminst.sql spool full_jvminst.log; set echo on connect / as sysdba startup mount alter system set "_system_trig_enabled" = false scope=memory; alter database open; select obj#, name from obj$ where type#=28 or type#=29 or type#=30 or namespace=32; @?/javavm/install/initjvm.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; @?/xdk/admin/initxml.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; @?/xdk/admin/xmlja.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; @?/rdbms/admin/catjava.sql select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; shutdown immediate set echo off spool off exit -- End of File full_jvminst.sql
But if you’d execute it you’ll get the Java components only in the CDB$ROOT – but not in the PDB$SEED: And as the PDB$SEED is untouchable for users you can’t simply force it open and run the script again.
Install JVM correctly in a Single-/Multitenant environment
Assuming that the above script does create everything correctly it needs to be transformed to deal with a Single-/Multitenant environment by using catcon.pl.:
-- Start of File full_jvminst_mike.sql spool full_jvminst.log; set echo on alter pluggable database all open; alter system set "_system_trig_enabled" = false scope=memory; select obj#, name from obj$ where type#=28 or type#=29 or type#=30 or namespace=32; host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initjvm /u01/app/oracle/product/12.1.0.2/javavm/install/initjvm.sql; select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initxml /u01/app/oracle/product/12.1.0.2/xdk/admin/initxml.sql; select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b xmlja /u01/app/oracle/product/12.1.0.2/xdk/admin/xmlja.sql; select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catjava /u01/app/oracle/product/12.1.0.2/rdbms/admin/catjava.sql; select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type; shutdown immediate set echo off spool off exit -- End of File full_jvminst_mike.sql
By the way, the (still) undocumented “-n 1” option simply does start only one worker and creates only one logfile. It is usually used to execute database standard scripts.
Testing it in my environment
Of course I did execute the script in my environment – an Oracle Database 12.1.0.2 container database:
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 2 10:03:36 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1048576000 bytes
Fixed Size 2932336 bytes
Variable Size 369099152 bytes
Database Buffers 671088640 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL> create pluggable database PDB1
admin user adm identified by adm
file_name_convert=('/u02/oradata/CDB1/pdbseed','/u02/oradata/CDB1/pdb1');
Pluggable database created.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> @full_jvminst_mike.sql
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> alter system set "_system_trig_enabled" = false scope=memory;
System altered.
SQL> select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
no rows selected
SQL> host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initjvm /u01/app/oracle/product/12.1.0.2/javavm/install/initjvm.sql;
catcon: ALL catcon-related output will be written to /home/oracle/initjvm_catcon_13696.lst
catcon: See /home/oracle/initjvm*.log files for output generated by scripts
catcon: See /home/oracle/initjvm_*.lst files for spool files, if any
catcon.pl: completed successfully
SQL> select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
COUNT(*) OBJECT_TYPE
---------- ---------------------------------------------------------------------
302 JAVA DATA
832 JAVA RESOURCE
25893 JAVA CLASS
SQL> host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initxml /u01/app/oracle/product/12.1.0.2/xdk/admin/initxml.sql;
catcon: ALL catcon-related output will be written to /home/oracle/initxml_catcon_15160.lst
catcon: See /home/oracle/initxml*.log files for output generated by scripts
catcon: See /home/oracle/initxml_*.lst files for spool files, if any
catcon.pl: completed successfully
SQL> select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
COUNT(*) OBJECT_TYPE
---------- ---------------------------------------------------------------------
302 JAVA DATA
908 JAVA RESOURCE
27097 JAVA CLASS
SQL> host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b xmlja /u01/app/oracle/product/12.1.0.2/xdk/admin/xmlja.sql;
catcon: ALL catcon-related output will be written to /home/oracle/xmlja_catcon_16210.lst
catcon: See /home/oracle/xmlja*.log files for output generated by scripts
catcon: See /home/oracle/xmlja_*.lst files for spool files, if any
catcon.pl: completed successfully
SQL> select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
COUNT(*) OBJECT_TYPE
---------- ---------------------------------------------------------------------
302 JAVA DATA
908 JAVA RESOURCE
27097 JAVA CLASS
SQL> host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catjava /u01/app/oracle/product/12.1.0.2/rdbms/admin/catjava.sql;
catcon: ALL catcon-related output will be written to /home/oracle/catjava_catcon_16273.lst
catcon: See /home/oracle/catjava*.log files for output generated by scripts
catcon: See /home/oracle/catjava_*.lst files for spool files, if any
catcon.pl: completed successfully
SQL> select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
COUNT(*) OBJECT_TYPE
---------- ---------------------------------------------------------------------
302 JAVA DATA
911 JAVA RESOURCE
27473 JAVA CLASS
2 JAVA SOURCE
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> set echo off
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[CDB1] oracle@localhost:/u01/app/oracle/product/12.1.0.2/rdbms/admin
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 2 10:28:53 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1048576000 bytes
Fixed Size 2932336 bytes
Variable Size 369099152 bytes
Database Buffers 671088640 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> select comp_id from dba_registry order by 1;
COMP_ID
--------------------------------------------------------------------------------
CATALOG
CATJAVA
CATPROC
JAVAVM
RAC
XDB
XML
7 rows selected.
SQL> alter session set container=pdb1;
Session altered.
SQL> select comp_id from dba_registry order by 1;
COMP_ID
--------------------------------------------------------------------------------
CATALOG
CATJAVA
CATPROC
JAVAVM
RAC
XDB
XML
7 rows selected.
So it looks good – the required components got all created within each container. But will the component be there as well when I provision a new pluggable database?
Provisioning a new pluggable database
SQL> alter session set container=cdb$root;
Session altered.
SQL> create pluggable database PDB2
admin user adm identified by adm
file_name_convert=('/u02/oradata/CDB1/pdbseed','/u02/oradata/CDB1/pdb2');
Pluggable database created.
SQL> alter session set container=pdb2;
Session altered.
SQL> startup
Pluggable Database opened.
SQL> select comp_id from dba_registry order by 1;
COMP_ID
--------------------------------------------------------------------------------
CATALOG
CATJAVA
CATPROC
JAVAVM
RAC
XDB
XML
7 rows selected.
SQL>
This looks good as well.
Summary
It’s important to change habits when dealing with Single Tenant or Multitenant environments. You must ensure to execute scripts with catcon.pl in order to ensure that those will be executed in all containers, not only in the CDB$ROOT. Of course it is hard to find the right balance between either too many components or the need to recreate components here and there. Especially in a Multitenant environment it is key that you don’t alter component sets to heavily as otherwise you’ll see yourself ending up in situations where you want to unplug the above PDB1 and plug it in somewhere else – but if the destination’s CDB has no JVM installed you can’t really plugin.
–Mike
Mike, in the 12.2 home, DBCA does not install JVM into PDBs, only into CDB root. I think it’s a bug. Do you agree?
I never thought I will have to deal with Java, but today the need arose.
I found a new note 2262919.1 and your article.
I opted for the “official” way and it looks it was a mistake. Unexpected error ‘pickNextProc: unexpected error in next_proc’ during the deinstall stage.
Max,
this is not a bug – it’s buried in the DBCA screen – when you get to the OPTIONS you’ll have to “click” twice for a component to get into the CDB$ROOT and into PDBs (which means during creation that it will be in PDB$SEED and therefore in all later cloned PDBs).
What do you mean you’Ve “opted for the official way”? Where do you receive the error?
CHeers
Mike
Mike, the note 2262919.1 instructs to run the bunch of scripts, and one of them, jvm_deinstall, exits with the error. Very depressing.
I have read somewhere that Standard Edition databases should be created with the multitenant configuration, but now I’m sceptical.
Thank you for sharing DBCA tricks, though now I have checked and it seems the corresponding screen ( http://www.imagebam.com/image/55bf3f601590093 ) does not appear during the installation path I chose AT ALL! I thought I had missed it.
Max,
tell me what you want to do? Deinstall JVM from a Multitenant database?
And there#s no need to create ANY database with the Multitenant (or Single Tenant) option right now unless you either plan to do Multitenant in the future or benefit from any of the features it offers.
Cheers
Mike
Mike, basically I just wanted to complain about Oracle 😉
I think I should install JVM into my single PDB, but I’m not totally sure.
I’ll explain.
I installed 12.2 Standard Edition software for the first time and
created CDB + PDB database for the first time too.
Now I am importing from a 10.2 database and receive thousands of
PLS-00201: identifier ‘DBMS_JAVA.START_IMPORT’ must be declared.
It turned out, that DBCA didn’t install Java into PDB.
So I had to learn something about Oracle JVM, which we don’t really use.
Metalink has a solution, but it fails (‘unexpected error in next_proc’). I expected some errors in log-files, but programs should not exit unexpectedly I think.
So today I ran your script and it succeeded (it seems). The datapump import went smoothly. The only dubious thing: there was the long heavy CPU activity in one of Oracle background processes AFTER IMPORT, something related to ‘JIT SLAVE’, but looks like it has finished.
Thank you for the script again!
I have a weird situation in oracle 12.2.0 patched with the April 2018 Patch. It’s a RAC cluster database. MY CDB has all the components installed. see output below:
APS VALID
CATALOG VALID
CATJAVA VALID
CATPROC VALID
CONTEXT VALID
JAVAVM VALID
ORDIM VALID
OWM VALID
RAC VALID
SDO VALID
XDB VALID
XML VALID
XOQ VALID
And here’s the weird thing. All the PDBs created using DBCA have only a subset of these components. Here’s the output from one of the pdbs.
CATALOG VALID
CATPROC VALID
OWM VALID
RAC VALID
XDB VALID
Now my question is how do i resolve this. There doesn’t seem to be any guide anywhere on this. Thanks if you can point me to a possible solution.
Azuka,
please do the following:
1) Set this parameter: https://mikedietrichde.com/2017/07/21/why-exclude_seed_cdb_view-is-now-an-underscore-in-oracle-12-2/
2) Use this query and copy&paste the output:
set pages 100
column status format a14
column comp_id format a14
select con_id, comp_id, status from cdb_registry order by 1,2;
I’d guess that your CON_ID=2 (aka PDB$SEED) has less components than the CON_ID=1 (CDB$ROOT). This means: all PDBs provisioned as a copy of PDB$SEED will have the same options as the SEED. But not the same options as the ROOT.
Workaround would be to install the missing and wanted components either just into each PDB, or generally into the PDB$SEED (and into additional PDBs) with catcon.pl.
Cheers,
Mike
Thanks for your prompt reply. Apparently it was my mistake. During the installation I did not check the “include in pdb” boxes for the options. So by default they were only installed in the CDB. So I simply delete the database and recreated it since it is a new build for a project I am working on. Once again thank you.
Azuka,
not exactly YOUR mistake – I think we made it hard:
https://mikedietrichde.com/2018/05/15/be-aware-of-database-options-selections-for-pdbs-in-dbca/
Cheers and thanks for your reply,
Mike