Creating a multitenant container database using scripts

Creating a database in Oracle 12c.

Whereas dbca is available and the best tool to create a 12c database, it is also possible to create it using scripts.  The 12c multitenant option introduces some enhancements to the CREATE DATABASE syntax, as well as some new keywords related to container database creation.

This page explores the basic process of creating of a 12c multitenant container database, and a pluggable database, using scripts and explains the syntax.

Creation of the multitenant database using sql.

The database creation involves these steps broadly

1)    Configure storage locations for the new database

2)    Configure a temporary init.ora file with basic parameters

3)    Create the database using the create database ddl

4)    Execute post-database scripts, by using a workaround for the missing catcdb.sql script. (The missing script is now apparently fixed in the April release).

5)    Creating any pluggable databases needed.

Step # 1 – Configure storage locations and a sysdba password

The multi tenant container database will consist at least 2 (and eventually possible more) containers, the root and seed containers.

Each container will have at least the system, sysaux tablespaces and its necessary to set up storage locations for these containers.

The root container will contain at least the system, sysaux, 2 redo log groups, the CDB-WIDE undo tablespace and the CDB-WIDE temp tablespace.

The seed container can be used as a template for creating a pluggable database using the CREATE PLUGGABLE DATABASE command and could contain the commonly needed tablespaces like a default temp tablespace, a user data  tablespace, apart from the mandatory SYSTEM and SYSAUX

Other containers created with plug-in operations or the CREATE PLUGGABLE DATABASE command will need a place where their files go too.

The folder organization followed in this example is shown below, with a folder created per container.

The password can be configured using the orapwd tool as before.

Step # 2 – Configure a temp init.ora file

Oracle Database 12c introduces a new parameter ENABLE_PLUGGABLE_DATABASE which is the most important one for creating a multi-tenant container database. Below is a sample init.ora file configured with that parameter, which can bring up an instance in nomount mode prior to the CREATE DATABASE.

DB_NAME=CDB2

ENABLE_PLUGGABLE_DATABASE=TRUE

CONTROL_FILES='/u01/app/oracle/oradata/cdb2/root/control1.ctl',

              '/u01/app/oracle/oradata/cdb2/root/control2.ctl'

SGA_MAX_SIZE=2G

SGA_TARGET=1G

PGA_AGGREGATE_TARGET=600M

Step # 3 – Execute the CREATE DATABASE command

Start up the instance in NOMOUNT mode from SqPlus using the pfile init.ora configured in Step 2 and execute the CREATE DATABASE command.

The CREATE Database command has been enhanced with some multitenant related options. Below is a sample CREATE DATABASE command with a brief description of the options. (After the CREATE DATABASE, there is create SPFILE statement also )

At this stage normally its necessary to run catcdb.sql which creates the data dictionary, packages, db components needed to complete the database creation. 

However, the 12c r1 delivery for Unix does not contain this script – the workaround is to run a perl script, mentioned in Step #4.

Step # 4 – Run the dictionary creation scripts using catcon.pl

There is a perl script called catcon.pl, installed in the $ORACLE_HOME/rdbms/lib folder which can be invoked to execute the scripts like catalog.sql, catproc.sql, and pupbld.sql in the root and seed containers. It needs to be invoked from the perl interpreter binary called perl, and requires a couple of parameters to customize its working. This script accepts various parameters but the main ones are described here with reference to the example below:

Note:

Before executing perl, it is required to first set this environment variable.

$> export PERL5LIB=$ORACLE_HOME/rdbms/admin

$ > perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle -b catalog $ORACLE_HOME/rdbms/admin/catalog.sql

Parameter	Purpose
$ORACLE_HOME/rdbms/admin/catcon.pl	Location of the Oracle-provided perl script
-l /home/oracle	Specifies the base folder where the logs should be written
-b catalog	Specifies the base  name for the log files, they will be name-prefixed with this specified name
$ORACLE_HOME/rdbms/admin/catalog.sql	This is the name of the Oracle dictionary/component installer script to be run.

Below is a sample script which will call the 3 basic catalog creation scripts and complete the database creation. It can be run from Unix command line and should be run as the oracle home owner.

export PERL5LIB=$ORACLE_HOME/rdbms/admin

perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle -b catalog $ORACLE_HOME/rdbms/admin/catalog.sql

perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle -b catproc $ORACLE_HOME/rdbms/admin/catproc.sql

perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle -b pupbld $ORACLE_HOME/sqlplus/admin/pupbld.sql

The logs will be created in the /home/oracle folder and named catalog0.log, catalog1.log, etc. Any errors are logged in detail and failures are reported to the screen.

Step # 5 – Creating any pluggable databases needed

The pluggable database can be created inside a multitenant container database with the CREATE PLUGGABLE DATABASE statement. There are a number ways to create a PDB, we explore the creation from CDB in this example. Below is a sample CREATE PLUGGABLE DATABASE.

create pluggable database PDB1

 admin user pdb1_dba identified by oracle

 roles=(dba)

 file_name_convert=('/u01/app/oracle/oradata/cdb2/seed','/u01/app/oracle/oradata/cdb2/pdb1')

 storage (maxsize 2g max_shared_temp_size 500m)

Notes:

• The newly created database name is PDB1.
• It will be created using the seed data base as a template.
• The FILE_NAME_CONVERT parameter maps the seed datafile locations to corresponding ones for the PDB.
• The statement identifies an administrative user (pdb1_dba) with password oracle.
• The administrator is granted the DBA role which allows him to fully administer the PDB.
• There is a max disk usage restriction of 10GB and a max shared temp usage restriction of 1 GB.
• No resource plans or shares are configured either at the CDB or PDB level.
• The PDB needs to be opened at least once in read write mode for integrating it with the CDB, Upon opening, it goes from NEW to NORMAL status

Screenshot showing creation of a pluggable database using the seed as a template:

Conclusion

CDB and pluggable db creation is possible with scripts, as well as with dbca, but as compared to a NONCDB, CDB administration requires a more careful planning and organization of resources like storage,memory,cpu,etc. because multiple databases could be be running off it, due to consolidation. 

It is necessary in a multi tenant environment to ensure accurate connections to the proper services, by configuring the listeners and listening ports.

Thought should also be given to resource management, and Oracle Database 12c supports a two-tier resource plan based on resource shares to distribute the the resources first among the PDBs as a fraction/share of the total available, and within the CDB, among its resource consumer groups.