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

Share this: