LATEST TOPICS

Oracle 12c: Create Container Database with SQL*Plus

Introduction

In my last article, I had discussed about creating a container database in non-interactive with DBCA. However, we have seen there is a limitation with DBCA where it installs all the database components irrespective of the fact whether all of those components would be used or not.

I had mentioned that we can overcome this limitation of DBCA by creating the container database using CREATE DATABASE command through SQL*Plus. In today’s article, I will discuss about creating a container database using the CREATE DATABASE statement.

Prerequisites

We need to meet certain prerequisites to be able to create a CDB using CREATE DATABASE statement from SQL*Plus. Since we are creating the CDB from SQL*Plus, we need to make sure that we have already created the database parameter file (init${ORACLE_SID}.ora) as well as created all the required directory structures for the container database and properly sourced the database environment.

Sourcing database environment

Before we can create a CDB using CREATE DATABASE command, we need to set the database environments such as $ORACLE_SID, $ORACLE_HOME, etc for the container database to be created.

For example: Considering, I want to create a new CDB with name orlcdb01 under ORACLE_HOME /app/oracle/product/12.0.2; we would source the database environment as follows

#/---- set ORACLE_SID for the CDB to be created -----/
#/----
[oracle@mylab-02 ~]$ export ORACLE_SID=orlcdb01

#/---- set ORACLE_HOME for the CDB to be created -----/
#/----
[oracle@mylab-02 ~]$ export ORACLE_HOME=/app/oracle/product/12.0.20

#/---- set PATH for the CDB to be created -----/
#/----
[oracle@mylab-02 ~]$ export PATH=$ORACLE_HOME/bin:$PATH

#/---- validate database environment -----/
#/----
[oracle@mylab-02 ~]$ env | grep ORACLE
ORACLE_SID=orlcdb01
ORACLE_BASE=/app/oracle
ORACLE_HOME=/app/oracle/product/12.0.2

Preparing the database parameter file

While preparing the database parameter file for the container database, one important parameter to be set is ENABLE_PLUGGABLE_DATABASE; which controls whether a database is a container database or not. We need to set this parameter to TRUE indicating our database is a container database.

If we fail to set this parameter to TRUE, container database creation would fail with following errors.

ORA-65093: multitenant container database not set up properly

Here is what Oracle says about this error

[oracle@mylab-02 scripts]$ oerr ora 65093
65093, 00000, "multitenant container database not set up properly"
// *Cause:  An attempt was made to open a multitenant container database without
//          the correct parameter set for a multitenant container database in
//          the initialization parameter file.
// *Action: Set the 'enable_pluggable_database=true' parameter for the
//          multitenant container database in the initialization parameter file
//          and restart the database.
//

Example:
Here is, how our database parameter file would like for the CDB with name orlcdb01 (I have created the parameter file with minimal set of parameters. We can set additional parameters based on our requirements)

[oracle@mylab-02 ~]$ cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora
sga_target=600M
PGA_AGGREGATE_TARGET=100M
db_name=orlcdb01
db_unique_name=orlcdb01
#/---- parameter to enable container ----/
#/----
enable_pluggable_database=true
#/---- control file location must pre-exist ---/
#/----
control_files='/data/oracle/orlcdb01/control_01.ctl','/data/oracle/orlcdb01/control_02.ctl'

Create required directory structures

Since we are creating the CDB using CREATE DATABASE command, it would not create the datafile directories during the database creating process. We need to create the directories beforehand for the container database creation to be successful.

At minimum we need to create the directory structures for storing datafiles from root (CDB$ROOT) and seed (PDB$SEED) containers

#/---- directory for storing root's (CDB$ROOT) datafile ----/
#/----
[oracle@mylab-02 ~]$ mkdir -p /data/oracle/orlcdb01/

#/---- directory for storing seed's (PDB$SEED) datafile ----/
#/----
[oracle@mylab-02 ~]$ mkdir -p /data/oracle/orlcdb01/pdbseed

#/---- validate directory structures ----/
#/----
[oracle@mylab-02 ~]$ ls -ld /data/oracle/orlcdb01/
drwxr-xr-x 3 oracle dba 4096 Sep 12 21:42 /data/oracle/orlcdb01/
[oracle@mylab-02 ~]$ ls -ld /data/oracle/orlcdb01/pdbseed
drwxr-xr-x 2 oracle dba 4096 Sep 12 21:42 /data/oracle/orlcdb01/pdbseed
[oracle@mylab-02 ~]$

Create CDB using CREATE DATABASE command

At this moment, we have completed the prerequisites for creating the CDB using CREATE DATABASE command. Creating a CDB using CREATE DATABASE command is almost similar to creating a non-CDB. However, there are few additional clauses in the CREATE DATABASE statement which must be set for creating a CDB.

I am skipping the generic clauses of CREATE DATABASE statement and just focusing on the clauses relevant to the container database. We have following additional clauses in CREATE DATABASE statement which are specific to CDB

CREATE DATABASE cdb_name
...
... (skipped_generic_clauses)
...
-/---- Clause to indicate creation of CDB ----/
ENABLE PLUGGABLE DATABASE
	-/---- Clause to specify properties of seed database ----/
	SEED
		-/---- clause to specify how to generate datafiles for seed ----/
		FILE_NAME_CONVERT=('root_datafile_patern_1','seed_datafile_pattern_1','root_datafile_patern_2','seed_datafile_pattern_2',..)
		-/---- clause to specify additional properties for seed's SYSTEM datafile ----/
		SYSTEM DATAFILE datafile_properties
		-/---- clause to specify additional properties for seed's SYSAUX datafile ----/
		SYSAUX DATAFILE datafile_properties
		-/---- clause to specify default tablespace for seed database ----/
		USER_DATA TABLESPACE tablespace_name
		DATAFILE datafile_name datafile_properties

Where:-

Clause Name Description
ENABLE PLUGGABLE DATABASE This clause under the CREATE DATABASE statement indicates whether the database to be created is a CDB or not. We must specify this clause to be able to create a container database (CDB)
SEED This is a sub clause under the ENABLE PLUGGABLE DATABASE clause. This clause is used to specify properties for the seed (PDB$SEED) database. It has a set of sub-clauses which are described in the next tabs
FILE_NAME_CONVERT This sub clause of SEED clause determines how the seed’s datafile would be generated from the root’s datafile. ROOT (CDB$ROOT) is the parent container in a CDB and the CREATE DATABASE statement creates the datafiles for root which are then used to generate the skeleton (template) datafiles for the seed. We use the FILE_NAME_CONVERT clause of SEED to specify how we would like to
convert the root’s datafile for seed. This clause can be used as FILE_NAME_CONVERT=(‘root_datafile_pattern_1′,’seed_datafile_pattern_1′,’root_datafile_patern_2′,’seed_datafile_pattern_2’,….)
where seed_datafile_pattern_1 would replace the root_datafile_patern_1 and so on, while generating the datafiles for seed database.
SYSTEM DATAFILE The seed’s SYSTEM datafile inherits the properties of root’s SYSTEM datafile
by default. We can use this sub clause of SEED to specify different attribute (like size, autoextend, etc) for seed’s
SYSTEM datafile
SYSAUX DATAFILE The seed’s SYSAUX datafile inherits the properties of root’s SYSTEM datafile
by default. We can use this sub clause of SEED to specify different attribute (like size, autoextend, etc) for seed’s
SYSAUX datafile
USER_DATA TABLESPACE This sub clause of SEED can be used to define a dedicated tablespace for storing
user data in seed database. When a PDB is created using SEED, the resulting PDB would include this tablespace
and its datafiles. Tablespace created using this clause is not used by the root (CDB$ROOT). If this clause is
not specified, the seed’s default tablespace would be inherited from the root container (which is the USERS tablespace in general)

Note: We have another alternative for generating the seed’s datafile without using the FILE_NAME_CONVERT clause. We can set the database parameter PDB_FILE_NAME_CONVERT in the parameter file to specify how would we like to generate the datafiles for seed database.

Example:
In the following example, I am creating a CDB with name orlcdb01. To meet the prerequisites, I have already sourced the database environment, created the parameter file with the mandatory parameters (specifically enable_pluggable_database=TRUE) and created the required directory structures.

Further, I have saved the contents of CREATE DATABASE commands in file /app/oracle/scripts/create_db_orlcdb01.sql. To create the CDB, I will just bring my database in NOMOUNT state and execute the CREATE DATABASE script as shown below:

----/ startup the database in NOMOUNT mode /----
----/
[oracle@mylab-02 admin]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Sat Sep 12 23:28:41 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  629145600 bytes
Fixed Size                  2927528 bytes
Variable Size             184550488 bytes
Database Buffers          436207616 bytes
Redo Buffers                5459968 bytes
SQL> set echo on
SQL> set timing on
----/
----/ Calling the script containing CREATE DATABASE command /-----
----/
SQL> @/app/oracle/scripts/create_db_orlcdb01.sql
SQL> ----
SQL> ----// CREATE DATABASE STATEMENT //----
SQL> CREATE DATABASE orlcdb01
  2  ----
  3  ----// SYS password //----
  4    USER SYS IDENTIFIED BY oracle
  5  ----
  6  ----// SYSTEM password //----
  7    USER SYSTEM IDENTIFIED BY oracle
  8  ----
  9  ----// Online redo log groups //----
 10    LOGFILE GROUP 1 ('/data/oracle/orlcdb01/redo_01.log') SIZE 100M BLOCKSIZE 512,
 11            GROUP 2 ('/data/oracle/orlcdb01/redo_02.log') SIZE 100M BLOCKSIZE 512,
 12            GROUP 3 ('/data/oracle/orlcdb01/redo_03.log') SIZE 100M BLOCKSIZE 512
 13  ----
 14  ----// maximum number of archived redo log files for automatic media recovery //----
 15    MAXLOGHISTORY 1
 16  ----
 17  ----// Max number of logfile groups that can be created ever //----
 18    MAXLOGFILES 16
 19  ----
 20  ----// Max number of logfiles that can be created for each log groups //----
 21    MAXLOGMEMBERS 3
 22  ----
 23  ----// Max number of datafiles that can be created in this database //----
 24    MAXDATAFILES 1024
 25  ----
 26  ----// Database characterset //----
 27    CHARACTER SET AL32UTF8
 28  ----
 29  ----// database national characterset //----
 30    NATIONAL CHARACTER SET AL16UTF16
 31  ----
 32  ----// database extent managemet //----
 33    EXTENT MANAGEMENT LOCAL
 34  ----
 35  ----// root system datafile and it's properties  //----
 36    DATAFILE '/data/oracle/orlcdb01/system01.dbf'
 37      SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
 38  ----
 39  ----// root sysaux datafile and it's properties //----
 40    SYSAUX DATAFILE '/data/oracle/orlcdb01/sysaux01.dbf'
 41      SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
 42  ----
 43  ----// root's default tablespace and properties //----
 44    DEFAULT TABLESPACE USERS
 45       DATAFILE '/data/oracle/orlcdb01/users01.dbf'
 46       SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
 47  ----
 48  ----// default temp tablespace and it's properties //----
 49    DEFAULT TEMPORARY TABLESPACE temp
 50       TEMPFILE '/data/oracle/orlcdb01/temp01.dbf'
 51       SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
 52  ----
 53  ----// undo tablespace and it's properties //----
 54    UNDO TABLESPACE undotbs
 55       DATAFILE '/data/oracle/orlcdb01/undotbs01.dbf'
 56       SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
 57  ----
 58  ----// create database as container (CDB) //----
 59    ENABLE PLUGGABLE DATABASE
 60  ----
 61  ----// SEED database properties //----
 62      SEED
 63  ----
 64  ----// how to generate seed's datafiles //----
 65      FILE_NAME_CONVERT = ('/data/oracle/orlcdb01/','/data/oracle/orlcdb01/pdbseed/')
 66  ----
 67  ----// seed's SYSTEM and SYSAUX datafile properties //----
 68      SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
 69      SYSAUX DATAFILES SIZE 100M
 70  ----
 71  ----// seed's default tablespace and it's properties //----
 72  ----// commented this clause. SEED will use root's default tablespace //----
 73  ----  USER_DATA TABLESPACE SEED_USER
 74  ----       DATAFILE '/data/oracle/orlcdb01/pdbseed/seed_users_01.dbf'
 75  ---    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
 76  ----
 77  ----// End of CREATE DATABASE //----
 78  ----
 78  ;

Database created.

Elapsed: 00:00:38.82
SQL>

The container database orlcdb01 is created at this point based on the CREATE DATABASE command inputs. Lets validate the creation of our container database

----/
----/ validate the database is created as CDB /----
----/
SQL> select name,open_mode,database_role,cdb from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    CDB
--------- -------------------- ---------------- ---
ORLCDB01  READ WRITE           PRIMARY          YES

----/
----/ validate the seed database is created /----
----/
SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY

----/
----/ CHeck if the NLS parameters are set as per the response file /----
----/
SQL> select * from v$nls_parameters where  parameter like '%CHARACTERSET%';
 
PARAMETER                           VALUE               CON_ID
----------------------------------- --------------- ----------
NLS_CHARACTERSET                    AL32UTF8                 0
NLS_NCHAR_CHARACTERSET              AL16UTF16                0
          

----/
----/ validate the datafiles are created in the desired locations /----
----/
SQL> select c.name container,d.name
  2  from v$containers c, v$datafile d
  3  where c.con_id=d.con_id
  4  order by 1;

CONTAINER                      NAME
------------------------------ ------------------------------------------------------------
CDB$ROOT                       /data/oracle/orlcdb01/users01.dbf
CDB$ROOT                       /data/oracle/orlcdb01/undotbs01.dbf
CDB$ROOT                       /data/oracle/orlcdb01/sysaux01.dbf
CDB$ROOT                       /data/oracle/orlcdb01/system01.dbf
PDB$SEED                       /data/oracle/orlcdb01/pdbseed/users01.dbf
PDB$SEED                       /data/oracle/orlcdb01/pdbseed/system01.dbf
PDB$SEED                       /data/oracle/orlcdb01/pdbseed/sysaux01.dbf

7 rows selected.

----/
----/ End of validation /----
----/

At this point our container database is created. However, the database dictionaries are not yet populated and we must populate them. Let’s do it..

Populating dictionary views in CDB

We are aware of the fact that we need to run the Oracle supplied dictionary scripts like catalog.sql, catproc.sql, pupbld.sql, etcetera for populating the necessary dictionary views when we create a Oracle database manually using CREATE DATABASE statement.

However, for a container database the story is bit different. Since we have multiple containers (databases) such as root (CDB$ROOT) and seed (PDB$SEED), we need to run these scripts against each of these containers. To simplify the creation of dictionary views in a CDB, Oracle provides a script called catcdb.sql located under $ORACLE_HOME/rdbms/admin directory.

There is one problem in running the catcdb.sql script for populating dictionary views. When we run the catcdb.sql script, it creates all the database components like the way DBCA does i.e. it will create database components (spatial, Oracle Text, XDK, etc) irrespective of the fact whether we would use those components or not.

Fortunately, we have another alternative here. We can utilize the wrapper script catcon.pl located under $ORACLE_HOME/rdbms/admin to run the Oracle supplied scripts (catalog.sql, catproc.sql, etc) against our container database. In fact the catcdb.sql script utilizes this wrapper script (catcon.pl) for populating dictionary view in all of the CDB containers.

We are not interested in executing catcdb.sql as that would populate all the unwanted database components whereas we are keen to populate only the mandatory components. We will make use of catcon.pl wrapper script for executing the mandatory dictionary scripts catalog.sql, catproc.sql and pupbld.sql against our CDB containers.

Here is the syntax for running the catcon.pl script

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl  
[-u username[/password]] [-U username[/password]] [-d directory] 
[-l directory] [{-c|-C} container] [-p parallelism] [-e] [-s] 
[-E { ON | errorlogging-table-other-than-SPERRORLOG } ] [-I] [-g] [-f] 
-b log_file_name_base -- { SQL_script [arguments] | --x'SQL_statement' }

For a detailed explanation of the catcon.pl script, you can refer the official documentation here.

Lets populate the CDB dictionary views by running catalog.sql, catproc.sql and pupbld.sql against each of the containers using the wrapper script (catcon.pl) as shown below

#/----
#/---- running catalog.sql script against CDB containers ---/
#/----
[oracle@mylab-02 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -d $ORACLE_HOME/rdbms/admin -b catalog_output -e -l /home/oracle catalog.sql
catcon: ALL catcon-related output will be written to /home/oracle/catalog_output_catcon_4857.lst
catcon: See /home/oracle/catalog_output*.log files for output generated by scripts
catcon: See /home/oracle/catalog_output_*.lst files for spool files, if any
Enter Password:
catcon.pl: completed successfully
[oracle@mylab-02 ~]$

#/----
#/---- running catproc.sql script against CDB containers ---/
#/----
[oracle@mylab-02 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -d $ORACLE_HOME/rdbms/admin -b catproc_output -e -l /home/oracle catproc.sql
catcon: ALL catcon-related output will be written to /home/oracle/catproc_output_catcon_5155.lst
catcon: See /home/oracle/catproc_output*.log files for output generated by scripts
catcon: See /home/oracle/catproc_output_*.lst files for spool files, if any
Enter Password:
catcon.pl: completed successfully
[oracle@mylab-02 ~]$

#/----
#/---- running pupbld.sql script against CDB containers ---/
#/----
[oracle@mylab-02 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u system -d $ORACLE_HOME/sqlplus/admin -b pupbld_output -e -l /home/oracle pupbld.sql
catcon: ALL catcon-related output will be written to /home/oracle/pupbld_output_catcon_5939.lst
catcon: See /home/oracle/pupbld_output*.log files for output generated by scripts
catcon: See /home/oracle/pupbld_output_*.lst files for spool files, if any
Enter Password:
catcon.pl: completed successfully
[oracle@mylab-02 ~]$

Lets validate the components of our container database orlcdb01.

----/
----/ Query the status of database components /----
----/
SQL> select name,comp_name,version,status from v$database,dba_registry;

NAME         COMP_NAME                                     VERSION      STATUS
------------ --------------------------------------------- ------------ ----------
ORLCDB01     Oracle XML Database                           12.1.0.2.0   VALID
             Oracle Database Catalog Views                 12.1.0.2.0   INVALID
             Oracle Database Packages and Types            12.1.0.2.0   INVALID


----/			 
----/ run utlrp.sql to recompile all the INVALID objects /----			 
----/
SQL> @?/rdbms/admin/utlrp.sql

----/
----/ check the status of the database components again /----
----/
SQL> select name,comp_name,version,status from v$database,dba_registry;

NAME         COMP_NAME                                     VERSION      STATUS
------------ --------------------------------------------- ------------ ----------
ORLCDB01     Oracle XML Database                           12.1.0.2.0   VALID
             Oracle Database Catalog Views                 12.1.0.2.0   VALID
             Oracle Database Packages and Types            12.1.0.2.0   VALID

At this point our container database is created with minimal database components. We can now create additional PDBs within our container database as shown below

----/
----/ Creating PDB mypdb_01 using seed database (PDB$SEED) /----
----/
SQL> CREATE PLUGGABLE DATABASE mypdb_01 ADMIN USER pdbadmin IDENTIFIED BY oracle
  2  FILE_NAME_CONVERT=('/data/oracle/orlcdb01/pdbseed/','/data/oracle/orlcdb01/mypdb_01/')
  3  ;

Pluggable database created.

----/
----/ Open the PDB for READ-WRITE /----
----/
SQL> alter pluggable database MYPDB_01 open;

Pluggable database altered.


----/
----/ Validate the PDB /----
----/
SQL> select name,open_mode from v$pdbs;

NAME                                                         OPEN_MODE
------------------------------------------------------------ ----------
PDB$SEED                                                     READ ONLY
MYPDB_01                                                     READ WRITE


----/
----/ validate list of datafiles from each of the containers /----
----/


CONTAINER                      NAME
------------------------------ ------------------------------------------------------------
CDB$ROOT                       /data/oracle/orlcdb01/system01.dbf
CDB$ROOT                       /data/oracle/orlcdb01/undotbs01.dbf
CDB$ROOT                       /data/oracle/orlcdb01/users01.dbf
CDB$ROOT                       /data/oracle/orlcdb01/sysaux01.dbf
MYPDB_01                       /data/oracle/orlcdb01/mypdb_01/users01.dbf
MYPDB_01                       /data/oracle/orlcdb01/mypdb_01/sysaux01.dbf
MYPDB_01                       /data/oracle/orlcdb01/mypdb_01/system01.dbf
PDB$SEED                       /data/oracle/orlcdb01/pdbseed/users01.dbf
PDB$SEED                       /data/oracle/orlcdb01/pdbseed/sysaux01.dbf
PDB$SEED                       /data/oracle/orlcdb01/pdbseed/system01.dbf

Conclusion

We have explored, how we can make use of CREATE DATABASE command to create a container database with minimal database components. We have also come across with the wrapper script catcon.pl which can be utilized to execute Oracle supplied scripts against each of the containers in a CDB and how it can be used as a replacement for catcdb.sql script.

Hope you enjoyed reading this post as much as I enjoyed exploring and writing!

4 Comments
  1. Shaukat Ali
  2. Dennis
    • Abu Fazal Abbas
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed