Automating Multi-Tenant Container Database Creation

There are two ways of creating multi-tenant container databases (CDB): Database Configuration Assistant and scripts. With the latter you have to run the script catcdb.sql for building data dictionary. This script calls a cascade of other Perl and sqlplus scripts, which are primarily intended for interactive use. Simply put, the aforementioned scipt catcdb.sql is just a wrapper for the Perl program catcdb.pl:

host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2

As you can see, you can supply a log directory and a log file on the command line to avoid the interaction.

Also, notice that the perl executable isn’t referenced with the absolute path. Consequently, depending on your PATH variable, some arbitrary Perl installation might be picked up. Below is the typical error message when this happens:

Can't locate Term/ReadKey.pm in @INC (you may need to install the Term::ReadKey module) (@INC contains: /u00/oracle/orabase/product/18.7.0.0.190716_a/rdbms/admin /usr/perl5/site_perl/5.22/i86pc-solaris-thread-multi-64 /usr/perl5/site_perl/5.22 /usr/perl5/vendor_perl/5.22/i86pc-solaris-thread-multi-64 /usr/perl5/vendor_perl/5.22 /usr/perl5/5.22/lib/i86pc-solaris-thread-multi-64 /usr/perl5/5.22/lib) at /u00/oracle/orabase/product/18.7.0.0.190716_a/rdbms/admin/catcdb.pl line 38.
BEGIN failed--compilation aborted at /u00/oracle/orabase/product/18.7.0.0.190716_a/rdbms/admin/catcdb.pl line 38.)

Therefore, it’s necessary to include Perl delivered with the Oracle home in your PATH: $ORACLE_HOME/perl/bin and unset PERL5LIB, too.

Moreover, catcdb.pl will be calling other Perl scripts (catcon.pl) which, in turn, will be invoking some sqlplus scripts, alas, again without the absolute path, so you have to add $ORACLE_HOME/bin to the PATH as well. By the way, catcon.pl was introduced – among others, to handle SQL scripts execution in multi-tenant environments.

Normally, a Perl program acts as a wrapper for sqlplus, not the other way around. Strangely, the sole purpose of the catcdb.sql wrapping code is to query the Oracle home from the database, even though a Perl program could do that much more efficiently – by simply reading the environment variable $ENV{ORACLE_HOME}. A shortcoming is that sqlplus won’t signal any errors if the Perl script fails.

So, parse the sqlplus output for Perl fatal errors, such as the one below:

open2: exec of sqlplus /nolog failed at /u00/oracle/orabase/product/18.7.0.0.190716_a/rdbms/admin/catcon.pm line 996.

In particular, the key word to search for is: ‘failed’.

Further, set the following environment variables: CATCDB_SYS_PASSWD, CATCDB_SYSTEM_PASSWD and CATCDB_TEMPTS to avoid prompting. That’s not documented – I figured it out by analyzing catcdb.pl.

Last but not least, catcdb.sql installs Oracle Workspace Manager, which, according to Mike Dietrich, can be safely removed:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -–reverse -n 1 -l /logdir -b uninstall_owm $ORACLE_HOME/rdbms/admin/owmuinst.plb

In summary, do the following when automating the data dictionary creation with scripts:

• supply arguments to catcdb.sql: catcdb.sql log_directory log_file
• PATH=$ORACLE_HOME/perl/bin:$ORACLE_HOME/bin
• unset PERL5LIB
• set CATCDB_SYS_PASSWD, CATCDB_SYSTEM_PASSWD and CATCDB_TEMPTS
• parse catcdb.sql output for ‘failed’
• uninstall Oracle Workspace manager if you don’t use it