Create Container Databases (CDB) with less options – it’s now supported in Oracle 12.1.0.2

When Oracle Multitenant was launched Roy and I amongst many other people always mentioned that the requirement of having all options in a Container Database (CDB$ROOT), and therefore also for the PDB$SEED with the immediate result that all PDBs provisioned from the PDB$SEED will have all options as well, will hinder customer adoption significantly.

Almost all customers I have talked to in the past 3-4 years about Oracle Multitenant mentioned immediately that it will be a huge problem for them to install all options as (1) their policy is to install only things they are licensed for to (2) prevent developers, users and DBAs to use things accidentally without even knowing that this or that will require a license.

As it is not allowed to manipulate and change the PDB$SEED the workaround – as PDBs were allowed to have less options – has been to create a stand-alone Oracle 12c database with exactly the options you’d like to have configured as your gold standard – and then plug it in under a remarkable name, for instance PDB$MASTER. Switch it to read only and make sure from now on you’ll provision a new PDB always as a clone from PDB$MASTER, and not from PDB$SEED.

All Options in a CDB

That would have even worked in the Single Tenant case, which does not require licensing the Oracle Multitenant option and where you have only one active (“customer-created PDB”) PDB. For this purpose you would have unplugged your PDB$MASTER after making it a pluggable database and provision new PDBs with just your desired options set as plugging in PDB$MASTER under a new name (e.g. PDB26) using the COPY option of the command.

Now this will become all obsolete as from now you it is allowed to have a CDB installation with less options. This applies to linked kernel modules (e.g. RAT) as well as to configured database components (e.g. JAVA, OWM, SPATIAL etc).

Please see the following new/rephrased MOS Notes:

MOS Note:2001512.1 basically explains the following steps:

  • Do all the click work in DBCA (Database Creation Assistant) to create a container database – but let DBCA only create the scripts
  • Edit the <SID>.sql script and remove the unwanted options according to the dependency table in the MOS Note
  • Edit the CreateDBCatalog.sql in case you want to remove OWM (Oracle Workspace Manager) creation as well
  • Add the Oracle PERL $ORACLE_HOME/perl/bin in front of your $PATH variable
  • Start the <SID>.sh script on the shell prompt

Here’s an example of a CreateDBCatalog.sql and a XXXX.sql creating a CDB with no options except XDB (which is mandatory in Oracle Database 12c):

cat CreateDBCatalog.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/XXXX/scripts/CreateDBCatalog.log
append
alter session set "_oracle_script"=true;
alter pluggable database pdb$seed close;
alter pluggable database pdb$seed open;
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1
-l /u01/app/oracle/admin/XXXX/scripts -b catalog
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catalog.sql;
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1
-l /u01/app/oracle/admin/XXXX/scripts -b catproc
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catproc.sql;
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1
-l /u01/app/oracle/admin/XXXX/scripts -b catoctk
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catoctk.sql;
-- host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n
1 -l /u01/app/oracle/admin/XXXX/scripts -b owminst
/u01/app/oracle/product/12.1.0.2/rdbms/admin/owminst.plb;
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1
-l /u01/app/oracle/admin/XXXX/scripts -b pupbld -u
SYSTEM/&&systemPassword
/u01/app/oracle/product/12.1.0.2/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /u01/app/oracle/admin/XXXX/scripts/sqlPlusHelp.log
append
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1
-l /u01/app/oracle/admin/XXXX/scripts -b hlpbld -u
SYSTEM/&&systemPassword -a 1
/u01/app/oracle/product/12.1.0.2/sqlplus/admin/help/hlpbld.sql
1helpus.sql;
spool off
spool off
cat XXXX.sql

set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
host /u01/app/oracle/product/12.1.0.2/bin/orapwd file=/u01/app/oracle/product/12.1.0.2/dbs/orapwXXXX force=y format=12
@/u01/app/oracle/admin/XXXX/scripts/CreateDB.sql
@/u01/app/oracle/admin/XXXX/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/XXXX/scripts/CreateDBCatalog.sql
-- @/u01/app/oracle/admin/XXXX/scripts/JServer.sql
-- @/u01/app/oracle/admin/XXXX/scripts/context.sql
-- @/u01/app/oracle/admin/XXXX/scripts/ordinst.sql
-- @/u01/app/oracle/admin/XXXX/scripts/interMedia.sql
-- @/u01/app/oracle/admin/XXXX/scripts/cwmlite.sql
-- @/u01/app/oracle/admin/XXXX/scripts/spatial.sql
-- @/u01/app/oracle/admin/XXXX/scripts/labelSecurity.sql
-- @/u01/app/oracle/admin/XXXX/scripts/apex.sql
-- @/u01/app/oracle/admin/XXXX/scripts/datavault.sql
-- @/u01/app/oracle/admin/XXXX/scripts/CreateClustDBViews.sql
@/u01/app/oracle/admin/XXXX/scripts/lockAccount.sql
@/u01/app/oracle/admin/XXXX/scripts/postDBCreation.sql
@/u01/app/oracle/admin/XXXX/scripts/PDBCreation.sql
@/u01/app/oracle/admin/XXXX/scripts/plug_PDB.sql
@/u01/app/oracle/admin/XXXX/scripts/postPDBCreation_PDB.sql

This results in a database having only these components – the minimal component set in Oracle 12.1.0.2:

COMP ID  NAME
-------- --------------------------------------
CATALOG  Oracle Database Catalog View
CATPROC  Oracle Database Packages and Types
XDB      Oracle XML Database

–Mike

11 thoughts on “Create Container Databases (CDB) with less options – it’s now supported in Oracle 12.1.0.2

  1. With non-CDBs you don’t face the issue as the DBCA does allow you to deselect options.

    But you can use the script approach as well of course for non-CDBs.

    Cheers
    Mike

  2. Hello Mike,

    for a POC i am working finally with PDB’s 😉

    one thing I noticed is that the PDB_PLUGIN_VIOLATIONS view displays the options missing when you plugin a noncdb

    for example :

    Database option SDO mismatch: OPTION PDB installed version NULL. CDB installed version 12.1.0.2.0 PENDING Fix the database option in the PDB or the CDB

    I want to have it clean so decided to create the cdb exactly with the same options as the noncdb.
    So from your blogpost above I understand that the messages in PDB_PLUGIN_VIOLATIONS is strictly informational and not an error ?

  3. Hi Philippe,

    the PDB_PLUG_IN_VIOLATRIONS contains a lot of useless information and does not get purged as far as I know. Bugs had been filed years ago but got ignored.

    The only thing you can do is query for errors.

    select message from pdb_plug_in_violations
    where status<> ‘RESOLVED’ and TYPE=’ERROR’;

    The issue I see:
    Sometimes things which cause a road block are not tagged as error but as WARNING such as "you’ll have to run noncdb_to_pdb.sql".

    Sorry for the inconvenience 🙁
    Mike

  4. Philippe,

    to add:
    There are ERRORS and WARNINGS – but there’s no clear rules. Everything needs to be really checked and verified – some things can be ignored (such as the SDO error in your case which is complete nonsense – why should you care that your PDB you’ll plug in has no SDO but the CDB your are gonna plug into has SDO – useless information).

    Cheers
    Mike

  5. Hi Mike,

    Is there a way to not install these options when using templates to create the cdb ? I did try to edit the template and do this however no success.

    Thanks
    Steve

  6. Hi Steve,

    "templates" in DBCA unfortunately don’t allow you this customization as far as I remember. Only the "create scripts" approach will work for CDBs. But for non-CDBs you should have the choice.

  7. Pingback: Why you should remove APEX from the CDB$ROOT | Upgrade your Database - NOW!

Leave a Reply

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