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 CUST.sh -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:
#!/bin/sh
OLD_UMASK=`umask`
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/12.1.0.1/dbhome_1/dbs
mkdir -p /u01/fra
mkdir -p /u01/fra/CUST
mkdir -p u01/oradata/CUST
mkdir -p u01/oradata/CUST/pdbseed
umask ${OLD_UMASK}
PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB
ORACLE_SID=CUST; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
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/12.1.0.1/dbhome_1/bin/sqlplus /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/12.1.0.1/dbhome_1/bin/orapwd file=/u01/app/oracle/product/12.1.0.1/dbhome_1/dbs/orapwCUST force=y format=12 @/u01/app/oracle/admin/CUST/scripts/CreateDB.sql @/u01/app/oracle/admin/CUST/scripts/CreateDBFiles.sql @/u01/app/oracle/admin/CUST/scripts/CreateDBCatalog.sql @/u01/app/oracle/admin/CUST/scripts/JServer.sql @/u01/app/oracle/admin/CUST/scripts/context.sql @/u01/app/oracle/admin/CUST/scripts/ordinst.sql @/u01/app/oracle/admin/CUST/scripts/interMedia.sql @/u01/app/oracle/admin/CUST/scripts/cwmlite.sql @/u01/app/oracle/admin/CUST/scripts/spatial.sql @/u01/app/oracle/admin/CUST/scripts/labelSecurity.sql @/u01/app/oracle/admin/CUST/scripts/apex.sql @/u01/app/oracle/admin/CUST/scripts/datavault.sql @/u01/app/oracle/admin/CUST/scripts/CreateClustDBViews.sql @/u01/app/oracle/admin/CUST/scripts/lockAccount.sql @/u01/app/oracle/admin/CUST/scripts/postDBCreation.sql @/u01/app/oracle/admin/CUST/scripts/PDBCreation.sql @/u01/app/oracle/admin/CUST/scripts/plug_PDB1.sql @/u01/app/oracle/admin/CUST/scripts/postPDBCreation_PDB1.sql
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>
where
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
(required).
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
db_block_size=8192
open_cursors=300
db_domain=""
db_name="CUST"
control_files=("u01/oradata/CUST/control01.ctl", "/u01/fra/CUST/control02.ctl")
db_recovery_file_dest="/u01/fra"
db_recovery_file_dest_size=4815m
compatible=12.1.0.0.0
diagnostic_dest=/u01/app/oracle
enable_pluggable_database=true
processes=300
sga_target=900m
audit_file_dest="/u01/app/oracle/admin/CUST/adump"
audit_trail=db
remote_login_passwordfile=EXCLUSIVE
dispatchers="(PROTOCOL=TCP) (SERVICE=CUSTXDB)"
pga_aggregate_target=300m
undo_tablespace=UNDOTBS1
[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):
CREATE DATABASE "CUST"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE '/u01/oradata/CUST/system01.dbf' SIZE 700M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/oradata/CUST/sysaux01.dbf' SIZE 550M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/CUST/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/oradata/CUST/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
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
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"
enable pluggable database
seed file_name_convert=(
'/u01/oradata/CUST/system01.dbf','/u01/oradata/CUST/pdbseed/system01.dbf',
'/u01/oradata/CUST/sysaux01.dbf','/u01/oradata/CUST/pdbseed/sysaux01.dbf',
'/u01/oradata/CUST/temp01.dbf','/u01/oradata/CUST/pdbseed/temp01.dbf',
'/u01/oradata/CUST/undotbs01.dbf','/u01/oradata/CUST/pdbseed/undotbs01.dbf'
);
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.
Scripts
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 @/u01/app/oracle/admin/CUST/scripts/CreateDB.sql @/u01/app/oracle/admin/CUST/scripts/CreateDBFiles.sql @/u01/app/oracle/admin/CUST/scripts/CreateDBCatalog.sql @/u01/app/oracle/admin/CUST/scripts/JServer.sql @/u01/app/oracle/admin/CUST/scripts/context.sql @/u01/app/oracle/admin/CUST/scripts/ordinst.sql @/u01/app/oracle/admin/CUST/scripts/interMedia.sql @/u01/app/oracle/admin/CUST/scripts/cwmlite.sql @/u01/app/oracle/admin/CUST/scripts/spatial.sql @/u01/app/oracle/admin/CUST/scripts/labelSecurity.sql @/u01/app/oracle/admin/CUST/scripts/apex.sql @/u01/app/oracle/admin/CUST/scripts/datavault.sql @/u01/app/oracle/admin/CUST/scripts/CreateClustDBViews.sql @/u01/app/oracle/admin/CUST/scripts/lockAccount.sql @/u01/app/oracle/admin/CUST/scripts/postDBCreation.sql @/u01/app/oracle/admin/CUST/scripts/PDBCreation.sql @/u01/app/oracle/admin/CUST/scripts/plug_PDB1.sql @/u01/app/oracle/admin/CUST/scripts/postPDBCreation_PDB1.sql
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:
SET VERIFY OFF 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/catcon.pl -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/catcon.pl -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/catcon.pl -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/catcon.pl -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/catcon.pl -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/catcon.pl -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/catcon.pl -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 catcon.pl script is used.
perl /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catcon.pl
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 } ]
[-g]
-b log-file-name-base
--
{ sqlplus-script [arguments] | --x<SQL-statement> } ...
Optional:
-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
host
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
<log-file-name-base>_<script_name_without_extension>_[<container_name_if_any>].<default_extension>
-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
Mandatory:
-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
NOTES:
- 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 catcon.pl ... x.sql '--pJohn' '--PEnter Password for John:' ...
The script seems to be documented in a few places in the Admin Guide, for example here:
http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_admin.htm#ADMIN14074
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/catcon.pl \ > -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;
CREATE PLUGGABLE DATABASE PDB1 ADMIN USER PDBADMIN IDENTIFIED BY "sys" ROLES=(CONNECT) file_name_convert=('u01/oradata/CUST/pdbseed',
'u01/oradata/CUST/PDB1');
alter pluggable database PDB1 open;
alter system register;
alter session set container =PDB1;
CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE 'u01/oradata/CUST/PDB1/PDB1_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER DATABASE DEFAULT TABLESPACE "USERS";
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.
Martins Blog 
Responses
Hi.
Just a thought… Even if you are not planning on using APEX in your organization, the APEX code contains a number of really useful APIs that your PL/SQL developers can make good use of, so they don’t have to reinvent the wheel.
You can install APEX without enabling it, but still have access to the APIs…
Cheers
Tim…
PS. Why would you not want APEX? :)
Hi Tim,
good points, thanks!
So far I lived by the mantra not to add components I don’t plan on using but might want to revise APEX when I have the time to do so. I also figured that a migration from 11.2.0.3 to 12.1.0.1 with APEX installed took a loooooong time (and some more) to complete.
And thanks for passing by!
Martin