Creating CDBs and non-CDBs with less options

I wanted to write up this blog post a long time ago. When Helen Vanderheide did ask me a few weeks ago if she can omit catoctk.sql during the creation of the database, I realized that I should update my previous blog posts about Creating CDBs and non-CDBs with less options. And of course, the following is usable with non-CDBs as well.

You’ll find an example and especially the component dependency matrix below as well as many links to related blog posts at the end. If you don’t want to read all this, just scroll down to the Summary.

Why “Creating CDBs and non-CDBs with less options”?

First question: Why would you even think about creating a database or a CDB with less options? There are many potential motivations. A lot of people I know simply try to avoid license implications. Others have the paradigm: “Less options mean less risk for issues”. And some may just say: “Less options mean faster upgrades”.

Especially with the introduction of Oracle Multitenant the need for “less options” was quite understandable. Well, after a long while, even the Multitenant development agreed and allowed to create Container Databases (CDB) with less options.

The way to achieve this in Oracle 12.1.0.2: Use the DBCA, let it create the scripts and edit the scripts based on your needs afterwards. It was simply to late to change the DBCA: And hence, you had to do the job. But also the control on it. Since Oracle 12.2.0.1 the DBCA allows you to choose some of the options. But lets have a closer look into the process to see whether you should still go the “script” path or not.

Component Dependencies in Oracle 12.1.0.2

In MOS Note: 2001512.1 you will find a very good description – and if you scroll down quite a bit, you’ll see also the table showing the dependencies of components on each other. That’s is important to understand: If you remove the creation script for JVM, you can’t have the creation scripts for SPATIAL and MULTIMEDIA in it as this will fail. Both options are based on the existence of JVM.

This is the component dependencies table for Oracle 12.1.0.2:

DBCA in Oracle 18.3.0

Let the DBCA create the scripts

Let us check in Oracle 18.3.0. I start the DBCA, choose “Advanced Configuration“, “Custom Database” and “Container Database”. Afterwards, I click on all possible options to create components:

And at the last screen, I choose to “Create Scripts” as the only option. The DBCA will place the scripts in $ORACLE_BASE/admin/<SID>:

-rw-r-----. 1 oracle dba 2199 Aug  8 13:04 init.ora
-rwxr-xr-x. 1 oracle dba  726 Aug  8 13:04 HUGO.sh
-rw-r-----. 1 oracle dba 1493 Aug  8 13:04 CreateDB.sql
-rw-r-----. 1 oracle dba  389 Aug  8 13:04 CreateDBFiles.sql
-rw-r-----. 1 oracle dba 1852 Aug  8 13:04 CreateDBCatalog.sql
-rw-r-----. 1 oracle dba 1272 Aug  8 13:04 JServer.sql
-rw-r-----. 1 oracle dba 1057 Aug  8 13:04 context.sql
-rw-r-----. 1 oracle dba  352 Aug  8 13:04 ordinst.sql
-rw-r-----. 1 oracle dba  359 Aug  8 13:04 interMedia.sql
-rw-r-----. 1 oracle dba  370 Aug  8 13:04 cwmlite.sql
-rw-r-----. 1 oracle dba  352 Aug  8 13:04 spatial.sql
-rw-r-----. 1 oracle dba  361 Aug  8 13:04 labelSecurity.sql
-rw-r-----. 1 oracle dba  403 Aug  8 13:04 apex.sql
-rw-r-----. 1 oracle dba  367 Aug  8 13:04 datavault.sql
-rw-r-----. 1 oracle dba  991 Aug  8 13:04 CreateClustDBViews.sql
-rw-r-----. 1 oracle dba 1759 Aug  8 13:04 lockAccount.sql
-rwxr-xr-x. 1 oracle dba 1180 Aug  8 13:04 HUGO.sql
-rw-r-----. 1 oracle dba  892 Aug  8 13:04 postDBCreation.sql
[CDB2] oracle@localhost:/u01/app/oracle/admin/HUGO/script

The interesting files are HUGO.sql and CreateDBCatalog.sql.

Editable Scripts

You may ask yourself, why there’s not only one script for the components and options? I don’t know. But actually two options are placed into the CreateDBCatalog.sql scripts: Oracle Workspace Manager and the Oracle Cryptographic ToolKit.

This brings me back to Helen’s question:
“Is it okay to also comment out line for catoctk component?”

You can definitely comment out the OWM creation script owminst.plb if you are not planning to use the OWM. And in theory you potentially could comment out the Cryptographic ToolKit creaton with catoctk.sql as well without any harm to the database. But the latter is not a “component” which registers itself into DBA_REGISTRY. Hence, I don’t see a deeper reason for commenting it out.

My CreateDBCatalog.sql for HUGO:

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/HUGO/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/18/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/HUGO/scripts -v  -b catalog  -U "SYS"/"&&sysPassword" /u01/app/oracle/product/18/rdbms/admin/catalog.sql;
host perl /u01/app/oracle/product/18/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/HUGO/scripts -v  -b catproc  -U "SYS"/"&&sysPassword" /u01/app/oracle/product/18/rdbms/admin/catproc.sql;
host perl /u01/app/oracle/product/18/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/HUGO/scripts -v  -b catoctk  -U "SYS"/"&&sysPassword" /u01/app/oracle/product/18/rdbms/admin/catoctk.sql;
-- host perl /u01/app/oracle/product/18/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/HUGO/scripts -v  -b owminst  -U "SYS"/"&&sysPassword" /u01/app/oracle/product/18/rdbms/admin/owminst.plb;
host perl /u01/app/oracle/product/18/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/HUGO/scripts -v  -b pupbld -u SYSTEM/&&systemPassword  -U "SYS"/"&&sysPassword" /u01/app/oracle/product/18/sqlplus/admin/pupbld.sql;
host perl /u01/app/oracle/product/18/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/HUGO/scripts -v  -b pupdel -u SYS/&&sysPassword  -U "SYS"/"&&sysPassword" /u01/app/oracle/product/18/sqlplus/admin/pupdel.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /u01/app/oracle/admin/HUGO/scripts/sqlPlusHelp.log append
host perl /u01/app/oracle/product/18/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/HUGO/scripts -v  -b hlpbld -u SYSTEM/&&systemPassword  -U "SYS"/"&&sysPassword" -a 1  /u01/app/oracle/product/18/sqlplus/admin/help/hlpbld.sql 1help
us.sql;
spool off
spool off

In the main script, here: HUGO.sql, you’ll find all the other options. And if you edit it, the above component dependency matrrx is important as I explained before.

My HUGO.sql:

If I’d like to keep just Oracle TEXT and Oracle Label Security, I have to comment out quite a few things.
Note: I added comments behind the compoent scripts to point to the component

set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
ACCEPT dvOwnerPassword CHAR PROMPT 'Enter c##dbv1 user password: ' HIDE
ACCEPT dvAccountManagerPassword CHAR PROMPT 'Enter c##dbv1 user password: ' HIDE
host /u01/app/oracle/product/18/bin/orapwd file=/u01/app/oracle/product/18/dbs/orapwHUGO force=y format=12
@/u01/app/oracle/admin/HUGO/scripts/CreateDB.sql
@/u01/app/oracle/admin/HUGO/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/HUGO/scripts/CreateDBCatalog.sql
-- @/u01/app/oracle/admin/HUGO/scripts/JServer.sql            -- <-- Oracle JVM
@/u01/app/oracle/admin/HUGO/scripts/context.sql               -- <-- Oracle TEXT
-- @/u01/app/oracle/admin/HUGO/scripts/ordinst.sql            -- <-- Oracle Multimedia - Deprecated in 18c
-- @/u01/app/oracle/admin/HUGO/scripts/interMedia.sql         -- <-- Oracle Multimedia - Deprecated in 18c
-- @/u01/app/oracle/admin/HUGO/scripts/cwmlite.sql            -- <-- Oracle OLAP
-- @/u01/app/oracle/admin/HUGO/scripts/spatial.sql            -- <-- Oracle SPATIAL
@/u01/app/oracle/admin/HUGO/scripts/labelSecurity.sql         -- <-- Oracle Label Security
-- @/u01/app/oracle/admin/HUGO/scripts/apex.sql               -- <-- Oracle APEX
-- @/u01/app/oracle/admin/HUGO/scripts/datavault.sql          -- <-- Oracle Database Vault
@/u01/app/oracle/admin/HUGO/scripts/CreateClustDBViews.sql    -- <-- Leave it in! Not what it looks like
@/u01/app/oracle/admin/HUGO/scripts/lockAccount.sql
@/u01/app/oracle/admin/HUGO/scripts/postDBCreation.sql

You may recognize that the calls are done with simple script calls and not – as normally in Multitenant – with catcon.pl. The reason is that the CDB$ROOT needs to be created first, then the PDB$SEED will be created. And it does not happen in sort-of-parallel. Hence, as a result, the custom creation of a Multitenant container database usually takes twice as long as for a non-CDB.

In addition, please leave the CreateClustDBViews.sql in as it does much more then the name tells you. It does not only create the cluster views with catclust.sql but this in addition:

create or replace directory ORACLE_HOME as '/u01/app/oracle/product/18';
create or replace directory ORACLE_BASE as '/u01/app/oracle';
execute LBACSYS.configure_ols();
execute LBACSYS.ols_enforcement.enable_ols();
grant sysdg to sysdg container=all;
grant sysbackup to sysbackup container=all;
grant syskm to syskm container=all;

So this script is mandatory and shall not be removed.

Minimum Options in DBCA

I’m doing a quick comparison with the mimimum options set the DBCA does allow. Of course, OWM and OCT are in as they are both in the CreateDBCatalog.sql script. But the equivalent to HUGO.sql is different:

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/18/bin/orapwd file=/u01/app/oracle/product/18/dbs/orapwMIKE force=y format=12
@/u01/app/oracle/admin/MIKE/scripts/CreateDB.sql
@/u01/app/oracle/admin/MIKE/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/MIKE/scripts/CreateDBCatalog.sql
@/u01/app/oracle/admin/MIKE/scripts/CreateClustDBViews.sql
@/u01/app/oracle/admin/MIKE/scripts/lockAccount.sql
@/u01/app/oracle/admin/MIKE/scripts/postDBCreation.sql

And this is exactly what you – at least – should have. The calls to LBACSYS aren’t there in this case in CreateClusterDBViews.sql either.

Somebody else did ask me the other day on the blog why we create the password file with “format=12” instead of “format=12.2” but I have no answer to it.

Summary

In brief, you can still configure CDBs and non-CDBs quite well with the DBCA and have exactly the options you want. One exception: if you’d like to get rid of Oracle Workspace Manager during creation of a database, you’ll have to use the “scripts” approach. Or you have to remove OWM afterwards. You can find a description here: Oracle Workspace Manager (OWM) 11.2-12.2 Clean Up.

The component dependencies are still the same as far as I can see in Oracle 18c. And I personally would not create Oracle Multimedia anymore as it is deprecated in Oracle 18c.

Further Information

I wrote quite a bit in the past months and years about this topic:

• Create Container Databases (CDB) with less options – it’s now supported in Oracle 12.1.0.2 (Apr 23, 2015)
• Be aware of database options selections for PDBs in DBCA (May 15, 2018)
• Create Multitenant databases with DBCA – Things to Know (Sep 17, 2017)
• Always create databases as CUSTOM databases (Jul 11, 2017)
• Use your own templates in DBCA to create databases (Jul 12, 2017)
• Install components in Multitenant ALWAYS with catcon.pl (Mar 2, 2017)
• Remove and Clean Up Components from Oracle Database 11.2 – 12.2 (Jul 26, 2017)
• Why you should remove APEX from the CDB$ROOT (Nov 19, 2015)

–Mike