Creating a 12c Container Database from scripts

Tags: oracle

If you are curious how to create a CDB without the help of dbca then the “generate scripts” option is exactly the right approach! I am a great fan of creating databases with the required options only-the default template (General Purpose) is dangerous as it creates a database with options you may not be licensed for and additionally opens security risk.

The best^H^H^Heasiest way to understand how a Container Database (CDB from now on) is created is to let dbca create the scripts. The process is the same as with an interactive installation except that at the very end you do NOT create the database but tick the box to generate the scripts.

The resulting scripts will be created in $ORACLE_BASE/admin/${ORACLE_SID}/scripts. Change directory to this location and you will be surprised about the sheer number of files ending in *.sql

[oracle@server1 scripts]$ ls -l
total 84
-rw-r-----. 1 oracle oinstall  374 Jul  4 16:05 apex.sql
-rw-r-----. 1 oracle oinstall  970 Jul  4 16:05 context.sql
-rw-r-----. 1 oracle oinstall  534 Jul  4 16:05 CreateClustDBViews.sql
-rw-r-----. 1 oracle oinstall 1805 Jul  4 16:05 CreateDBCatalog.sql
-rw-r-----. 1 oracle oinstall  388 Jul  4 16:05 CreateDBFiles.sql
-rw-r-----. 1 oracle oinstall 1461 Jul  4 16:05 CreateDB.sql
-rwxr-xr-x. 1 oracle oinstall  757 Jul  4 16:05
-rwxr-xr-x. 1 oracle oinstall 1216 Jul  4 16:05 CUST.sql
-rw-r-----. 1 oracle oinstall  341 Jul  4 16:05 cwmlite.sql
-rw-r-----. 1 oracle oinstall  353 Jul  4 16:05 datavault.sql
-rw-r-----. 1 oracle oinstall 2132 Jul  4 16:05 init.ora
-rw-r-----. 1 oracle oinstall  330 Jul  4 16:05 interMedia.sql
-rw-r-----. 1 oracle oinstall 1127 Jul  4 16:05 JServer.sql
-rw-r-----. 1 oracle oinstall  332 Jul  4 16:05 labelSecurity.sql
-rw-r-----. 1 oracle oinstall 1151 Jul  4 16:05 lockAccount.sql
-rw-r-----. 1 oracle oinstall  348 Jul  4 16:05 ordinst.sql
-rw-r-----. 1 oracle oinstall   91 Jul  4 16:05 PDBCreation.sql
-rw-r-----. 1 oracle oinstall  790 Jul  4 16:05 plug_PDB1.sql
-rw-r-----. 1 oracle oinstall  829 Jul  4 16:05 postDBCreation.sql
-rw-r-----. 1 oracle oinstall  175 Jul  4 16:05 postPDBCreation_PDB1.sql
-rw-r-----. 1 oracle oinstall  323 Jul  4 16:05 spatial.sql
[oracle@server1 scripts]$

Although you weren’t given a choice when picking database options in dbca, you can run scripts selectively. For me that means skipping quite a lot!

Let’s review the main script, ${ORACLE_SID}.sh:


umask 0027
mkdir -p /u01/app/oracle/admin/CUST/adump
mkdir -p /u01/app/oracle/admin/CUST/dpdump
mkdir -p /u01/app/oracle/admin/CUST/pfile
mkdir -p /u01/app/oracle/audit
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/CUST
mkdir -p /u01/app/oracle/product/
mkdir -p /u01/fra
mkdir -p /u01/fra/CUST
mkdir -p u01/oradata/CUST
mkdir -p u01/oradata/CUST/pdbseed
umask ${OLD_UMASK}
echo You should Add this entry in the /etc/oratab: CUST:/u01/app/oracle/product/12.1.0/dbhome_1:Y
/u01/app/oracle/product/ /nolog @/u01/app/oracle/admin/CUST/scripts/CUST.sql

Nothing too exciting new in here, except the part where it creates the base directory for the PDB seed database. Also did you notice how it sets the PERL5LIB? This will become important in a minute.

The main script responsible for the creation of the database is ${ORACLE_SID}.sql, as it always has been. It doesn’t really do anything but calls scripts to create the various components. This is a nice reference to show which component requires what. Here’s the script’s contents for your reference:

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/ file=/u01/app/oracle/product/ force=y format=12

The call to orapwd to create the password file is interesting: it forces the 12c format but no further option. The orapwd utility has been greatly enhanced in 12c compared to 11.2 due to the fact that we now have lots more options for separation of duties!

[oracle@server1 scripts]$ orapwd
Usage: orapwd file=<fname> entries=<users> force=<y/n> asm=<y/n>
       dbuniquename=<dbname> format=<legacy/12> sysbackup=<y/n> sysdg=<y/n>
       syskm=<y/n> delete=<y/n> input_file=<input-fname>

Usage: orapwd describe file=<fname>

    file - name of password file (required),
    password - password for SYS will be prompted
               if not specified at command line.
               Ignored, if input_file is specified,
    entries - maximum number of distinct DBA (optional),
    force - whether to overwrite existing file (optional),
    asm - indicates that the password to be stored in
          Automatic Storage Management (ASM) disk group
          is an ASM password. (optional).
    dbuniquename - unique database name used to identify database
                   password files residing in ASM diskgroup only.
                   Ignored when asm option is specified (optional),
    format - use format=12 for new 12c features like SYSBACKUP, SYSDG and
             SYSKM support, longer identifiers, etc.
             If not specified, format=12 is default (optional),
    delete - drops a password file. Must specify 'asm',
             'dbuniquename' or 'file'. If 'file' is specified,
             the file must be located on an ASM diskgroup (optional),
    sysbackup - create SYSBACKUP entry (optional and requires the
                12 format). Ignored, if input_file is specified,
    sysdg - create SYSDG entry (optional and requires the 12 format),
            Ignored, if input_file is specified,
    syskm - create SYSKM entry (optional and requires the 12 format),
            Ignored, if input_file is specified,
    input_file - name of input password file, from where old user
                 entries will be migrated (optional),
    describe - describes the properties of specified password file

  There must be no spaces around the equal-to (=) character.
[oracle@server1 scripts]$

I changed the $ORACLE_SID.sql file to remove some of the options I don’t like such as APEX, InterMedia, Spatial, …

Remember not to remove XMLDB as it is now required.

The init.ora provided and stripped of comments is shown here:

[oracle@server1 scripts]$ grep '^\w' init.ora
control_files=("u01/oradata/CUST/control01.ctl", "/u01/fra/CUST/control02.ctl")
[oracle@server1 scripts]$


You obviously need a 12.1 compatible parameter for the creation of a PDB (enable_pluggable_database), but thankfully the other parameters are known from 11.2.

Create the database!  

OK I have to admit all the previous text was designed to lead up to the really interesting bit-the create database statement and the creation of the catalog! (drums please)

So here we go (formatted for better readability):

DATAFILE '/u01/oradata/CUST/system01.dbf' SIZE 700M REUSE
SYSAUX DATAFILE '/u01/oradata/CUST/sysaux01.dbf' SIZE 550M REUSE
  GROUP 1 ('u01/oradata/CUST/redo01.log') SIZE 50M,
  GROUP 2 ('u01/oradata/CUST/redo02.log') SIZE 50M,
  GROUP 3 ('u01/oradata/CUST/redo03.log') SIZE 50M
enable pluggable database
seed file_name_convert=(

The first part of the create database statement is still fairly standard, but the lower part beginning with the “enable pluggable database” statement is new. In fact, the enable pluggable database statement together with the initialisation parameter allows you to create the CDB. Now you need to know that every CDB comes with a “seed” PDB which needs to be placed.

After requesting the creation of a CDB, you need to specify where you are placing the PDB data files. This is easiest when you are using Oracle Managed Files (OMF) either implicitly with ASM or on a file system. Otherwise you need to use a file_name_convert clause as you see in the example.


The number of scripts dbca wants to run is rather large, see here for the output of the main SQL script:

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/dbhome_1/bin/orapwd file=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwCUST force=y format=12

I personally copy the file to a safe location and remove what I don’t want (InterMedia, Apex, Spatial, …). The scripts are quite cleverly made as you will see. Take the CreateDBCatalog.sql script as an example:

connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/CUST/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/dbhome_1/rdbms/admin/ -n 1 -l /u01/app/oracle/admin/CUST/scripts -b catalog /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catalog.sql;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/ -n 1 -l /u01/app/oracle/admin/CUST/scripts -b catblock /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catblock.sql;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/ -n 1 -l /u01/app/oracle/admin/CUST/scripts -b catproc /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catproc.sql;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/ -n 1 -l /u01/app/oracle/admin/CUST/scripts -b catoctk /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catoctk.sql;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/ -n 1 -l /u01/app/oracle/admin/CUST/scripts -b owminst /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/owminst.plb;
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/ -n 1 -l /u01/app/oracle/admin/CUST/scripts -b pupbld -u SYSTEM/&&systemPassword /u01/app/oracle/product/12.1.0/dbhome_1/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /u01/app/oracle/admin/CUST/scripts/sqlPlusHelp.log append
host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/ -n 1 -l /u01/app/oracle/admin/CUST/scripts -b hlpbld -u SYSTEM/&&systemPassword -a 1  /u01/app/oracle/product/12.1.0/dbhome_1/sqlplus/admin/help/hlpbld.sql 1helpus.sql;
spool off

Were you curious why the PERL5LIB was set in the main shell script? Here is your answer! Instead of running the scripts (?/rdbms/admin/catalog.sql et al) directly as in a non-CDB the script is used.

perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/

  Usage: catcon  [-u username[/password]] [-U username[/password]]
                 [-d directory] [-l directory]
                 [{-c|-C} container] [-p degree-of-parallelism]
                 [-e] [-s]
                 [-E { ON | errorlogging-table-other-than-SPERRORLOG } ]
                 -b log-file-name-base
                 { sqlplus-script [arguments] | --x<SQL-statement> } ...

     -u username (optional /password; otherwise prompts for password)
        used to connect to the database to run user-supplied scripts or
        SQL statements
        defaults to "/ as sysdba"
     -U username (optional /password; otherwise prompts for password)
        used to connect to the database to perform internal tasks
        defaults to "/ as sysdba"
     -d directory containing the file to be run
     -l directory to use for spool log files
     -c container(s) in which to run sqlplus scripts, i.e. skip all
        Containers not named here; for example,
          -c 'PDB1 PDB2',
     -C container(s) in which NOT to run sqlplus scripts, i.e. skip all
        Containers named here; for example,
          -C 'CDB PDB3'

       NOTE: -c and -C are mutually exclusive

     -p expected number of concurrent invocations of this script on a given

       NOTE: this parameter rarely needs to be specified

     -e sets echo on while running sqlplus scripts
     -s output of running every script will be spooled into a file whose name
        will be
     -E sets errorlogging on; if ON is specified, default error logging table
        will be used, otherwise, specified error logging table (which must
        have been created in every Container) will be used
     -g turns on production of debugging info while running this script

     -b base name (e.g. catcon_test) for log and spool file names

     sqlplus-script - sqlplus script to run OR
     SQL-statement  - a statement to execute

     - if --x<SQL-statement> is the first non-option string, it needs to be
       preceeded with -- to avoid confusing module parsing options into
       assuming that '-' is an option which that module is not expecting and
       about which it will complain
     - command line parameters to SQL scripts can be introduced using --p
       interactive (or secret) parameters to SQL scripts can be introduced
       using --P

     For example,
       perl ... x.sql '--pJohn' '--PEnter Password for John:' ...


The script seems to be documented in a few places in the Admin Guide, for example here:  

But it’s cool, isn’t it? So to pick the catcon-driven installation of the catalog, here is the translation into plain English:

host perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/ \
>  -n 1 -l /u01/app/oracle/admin/CUST/scripts -b catalog \
> /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catalog.sql;


Now it appears as if the -n flag didn’t have any obvious meaning, but -l indicates the log file directory, -b is the base name for the log file base name and finally the script to be executed, catalog.sql!

The remaining scripts follow the same logic.

Another interesting script is

host mkdir -p u01/oradata/CUST/PDB1;
alter pluggable database PDB1 open;
alter system register;
alter session set container =PDB1;

So there you go-the creation of the CDB in scripts explained. Now if you want, create the CDB with all your required options followed by the creation of a dbca-template to save yourself some time next you need a database quickly. Remember that a dbca-template is a cold backup of the database and it’s always faster to clone from a template than it is running scripts.

本文翻译:使用脚本创建 12c Container Database