Martins Blog

Trying to explain complex things in simple terms

Creating a 12c Container Database from scripts

Posted by Martin Bach on August 21, 2013

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.

About these ads

2 Responses to “Creating a 12c Container Database from scripts”

  1. oraclebase said

    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? :)

    • Martin Bach said

      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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,417 other followers

%d bloggers like this: