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
Hi Mike,
How about this option for ODA? As far as I know the recommendation is to use the supplied templates. And I can not find any reference to create a database on ODA with less database options. Do you have any practical solution for this?
Jan,
I can’t tell you any details about ODA here. I know that things get scripted a lot without plenty of flexibility.
Unfortunately you may please open an SR and check with Oracle Support whether ODA gives you such options.
I’d guess there is a way – but I have no access to an ODA to check by myself.
Cheers and sorry for the inconvenience,
Mike
Hi Mike
in HUGO.sh there first a part like this
# Prompt if script is run on remote nodes
echo “Is the script run on all the remote nodes? [y/n] (n)”
read ans
if [ $ans = ‘y’ -o $ans = “Y” ]; then
echo “”
else
echo “Please run the script on all the remote nodes first.”
exit 0
fi
Do have any idea which script is meant to execute ?
I can not run this “HUGO.sh” run on all nodes as it written run only on one node …
Any idea?
thx
Regards
Rainer
Hi Rainer,
in which environment do you create a CDB?
If it is <>12.1 then please use the DBCA instead. It allows you to configure options, and you can create operate it silently as well.
Cheers,
Mike