Install components in Multitenant ALWAYS with catcon.pl

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

5 thoughts on “Install components in Multitenant ALWAYS with catcon.pl

  1. 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

  2. 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

  3. 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!

Leave a Reply

Your email address will not be published. Required fields are marked *