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. But this has been changed now. You can create Container Databases (CDB) with less options – it’s now supported in Oracle 12.1.0.2.

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

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. Your best workaround – as PDBs were allowed to have less options – was simply to create a stand-alone Oracle 12c database with exactly the options you’d like to have. This would become your gold standard. Then you’d plug it in under a remarkable name, for instance PDB$MASTER. In addition you need to switch it to read only (recommended but not necessary anymore in Oracle 12.2 onwards). From now on you must make sure that you’ll provision a new PDB always as a clone from PDB$MASTER instead of PDB$SEED.

All Options in a CDB

That would even work in the Single Tenant case. As long as you have only one PDB it is not required to licene the Oracle Multitenant option. But in this case you’d have the PDB$MASTER always unplugged. The provisioning would mean that you’ll have to plug it in as a new PDB. This sounds quite complicated.

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

The important MOS Notes

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

How about Oracle Database 12.2 and newer?

Beginning with Oracle Database 12.2.0.1 you don’t have to do such stunts anymore. The DBCA by default allows you to customized your container database – but only if you choose ADVANCED and CUSTOM DATABASE during creation. In all other cases you’ll end up with a preconfigured database having all options.

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

Why can this be dangerous?

Please be a bit careful to avoid future issues. Once you are settled in the world of Oracle Multitenant you should make sure to have an identical set of options/components deployed. Otherwise you may end up in a situation where you try to pluigin a PDB with more options than the destination CDB.

In this case CHECK_PLUG_COMPATIBILITY will alert you and populate PDB_PLUG_IN_VIOLATIONS with a lot of warnings about component mismatches. In this case you will have to either:

  • Remove options from your PDB before unplug
  • Install the missing options into the destination CDB
  • Create a new CDB with matching components and plug this PDB into it
  • Use another technique (not unplug/plug), for instance Data Pump to migrate the contents to a newly created PDB

–Mike

20 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!

  8. Pingback: Always create databases as CUSTOM databases - Oracle DBCA

  9. Hi Mike

    Issue – Some components missing in pdb$seed and PDBs.

    I have created the “Create Database” scripts for CDB using Multi-Tenant with the dbca in a 12.2.0.1 environment – using “Advanced configuration” – first of all with all options. I checked out “Create as Container Database” and “Create an empty Container database” – none PDB. All options has been created in my scripts using “Database components.

    As mentioned in MyOracleSupport Note 2001512.1 “Creating A Container Database (CDB) With A Subset Of Options” – of course note is for 12.1 … therefore I comment out all unnecessary option – only JServer should by installed.
    Therefore I changed the script Database.sql and run the complete create database scripts. CDB created successfully.

    After than a PDB database was created manually. Some imports have done – But with errors using Java.
    The pdb$seed and all other pdbs do not have any JServer installed. I checked out dba_registry – no Java in pdb$seed and PDBs

    I compared your scripts, my older 12.1 scripts and the new 12.2 scripts and found the parameter -c ‘CDB$ROOT’ in my dbca scripts in some *.sql scripts. After removing this parameter in my JServer.sql and recreating the new database the JServer was installed correct in pdb$seed and within all my additional created PDBs.

    I hope this helps

    Cheers and many thanks for all my other problems solved on your blog
    Peter Jensch

    • Hi Peter,

      this is coming from the OPTIONS screen in DBCA. When you create the scripts with DBCA and don’t explicitly click on “PDB” in the options check box (e.g. check JAVAVM but don’t click it for the PDB) then your scripts will contain the extra option -c ‘CDB$RROT’ to indicate that this particular option won’t be present in PDBs. Side effect: it won’t be in the PDB$SEED as well. Meaning, every newly provisioned PDB won’t have it either. But you can easily install it – also into the PDB$SEED or in any of the PDBs separately.

      Cheers
      Mike

  10. Pingback: Can you simply switch from SE2 to EE with Oracle Multitenant - Upgrade

  11. Hi Mike,

    Thank you for sharing this post. Notably, your thoughts and explanation surrounding PDB_PLUG_IN_VIOLATRIONS.

    Best regards,
    jeff

  12. Hi Mike,

    I’m in the process of moving some 12.1 EE databases to SE2, and glad I found your post!

    However I am getting and error I’m hoping you can help with.
    I followed this process:

    I started with a 12.1 EE non-cdb (fci3)
    Upgraded to 12.1 EE non-cdb (fci3)

    Created a new empty 12.2 EE CDB with same settings, char sets, etc. (fciEE)
    and
    Created a new empty 12.2 SE CDB with same settings, char sets, etc. (fciSE)

    Created new PDB (fci3) into the 12.2 EE cdb (fciEE) using manifest file (nocopy tempfile reuse )
    This works and I can select my user schema tables.

    Then I shutdown the the PDB and unplug it (into manifest file).

    Then, used DBMS_PDB.CHECK_PLUG_COMPATIBILITY on the SE2 db (fciSE) and its YES.

    CREATE PLUGGABLE DATABASE FCI3 USING the manifest file .

    The PDB will not open, and PDB_PLUG_IN_VIOLATIONS shows:

    ” PDB is Enterprise Edition (8), but CDB is not Enterprise Edition (4) ”

    I verified again that all 3 db (the non-cdb 12.2 EE, the new EE CDB and the new SE CDB) all have same in dba_registry .

    Can you please help with this error?
    Thanks!!
    Nathan

Leave a Reply

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

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.