LATEST TOPICS

Oracle 12c: Create Container Database silently (non-interactive mode)

Introduction

In today’s article I will discuss and explore the creation of a multi-tenant Oracle 12c database. With the introduction of multi-tenant Oracle database architecture, the installation method has changed significantly when compared to the previous releases of Oracle database. We need to provide certain additional inputs while we create a container database.

We are all familiar with DBCA (Database Configuration Assistant), which enables us to create/configure/delete/modify Oracle databases. In today’s article we would see how to create a multi-tenant Oracle 12c database with DBCA. However, I will limit my discussion only to the silent installation method of DBCA. Further, we will also explore process involved in manual method of creating a multi-tenant database and the things to be taken care during the manual process.

Terminologies used in this article

  • Container Database: CDB
  • Pluggable Database: PDB
  • Database Configuration Assistant: DBCA

Create CDB in Non-Interactive mode with DBCA

DBCA is the primary tool provided by Oracle to configure Oracle databases. DBCA is primarily a GUI tool where it asks for series of interactive inputs and based on those inputs it creates/modify/configure/delete a Oracle database. However, since most of the DBAs operate remotely on Oracle Databases (servers), this GUI option is not always suitable due to slow response time. Further, GUI tools have their own limitations when integrated with deployment scripts.

To overcome these limitations/restrictions, Oracle also provides an option to run the DBCA tool in silent (non-interactive) mode, where we can bypass the GUI. However, to be able to run DBCA in silent mode, we need to provide all the required inputs in advance which DBCA expects during the run time.

We can do this by means of a response file, where we store all the required DBCA input variables and their respective values. Oracle provides a template of this response file which located in $ORACLE_HOME/assistants/dbca directory.

To create a CDB, we need to create the response file with following parameters. Please read the in-line comments to find out more details about these parameters

#/*********
#/********* DBCA Response file for creating container database ***********
#/*********
#/*********
#/***************** General Input section ********************************
#/*********
[GENERAL]
#-----------------------------------------------------------------------------
# Name          : RESPONSEFILE_VERSION
# Description   : Version of the database to create
# Mandatory     : Yes
#-----------------------------------------------------------------------------
RESPONSEFILE_VERSION = "12.1.0"
#-----------------------------------------------------------------------------
# Name          : OPERATION_TYPE
# Description   : Type of DBCA operation
# Mandatory     : Yes
#-----------------------------------------------------------------------------
OPERATION_TYPE = "createDatabase"
#/*********
#/***************** End of General Input section *****************************
#/*********
#/*********
#/***************** Create Database Input Section ****************************
#/*********
[CREATEDATABASE]
#-----------------------------------------------------------------------------
# Name          : GDBNAME
# Description   : Global database name of the database
# Mandatory     : Yes
#-----------------------------------------------------------------------------
GDBNAME = "mycdb"
#-----------------------------------------------------------------------------
# Name          : DATABASECONFTYPE
# Description   : database conf type as Single Instance, Real Application Cluster 
#				  or Real Application Cluster One Nodes database
# Default value : SI
# Mandatory     : No
#-----------------------------------------------------------------------------
DATABASECONFTYPE  = "SI"
#-----------------------------------------------------------------------------
# Name          : SID
# Description   : System identifier (SID) of the database
# Default value :  specified in GDBNAME
# Mandatory     : No
#-----------------------------------------------------------------------------
SID = "mycdb"
#-----------------------------------------------------------------------------
# Name          : CREATEASCONTAINERDATABASE
# Description   : flag to create database as container database
# Default value : false
# Mandatory     : No
#-----------------------------------------------------------------------------
CREATEASCONTAINERDATABASE = TRUE
#-----------------------------------------------------------------------------
# Name          : NUMBEROFPDBS
# Description   : Specify the number of pdb to be created
# Default value : 0
# Mandatory     : No
#-----------------------------------------------------------------------------
NUMBEROFPDBS = 1
#-----------------------------------------------------------------------------
# Name          : PDBNAME
# Description   : Specify the pdbname/pdbanme prefix if one or more pdb need to be created
# Default value : None
# Mandatory     : No
#-----------------------------------------------------------------------------
PDBNAME= "MYPDB_1"
#-----------------------------------------------------------------------------
# Name          : PDBADMINPASSWORD
# Description   : PDB Administrator user password
# Default value : None
# Mandatory     : No
#-----------------------------------------------------------------------------
PDBADMINPASSWORD= "oracle"
#-----------------------------------------------------------------------------
# Name          : TEMPLATENAME
# Description   : Name of the DBCA template
# Mandatory     : Yes
#-----------------------------------------------------------------------------
TEMPLATENAME = "General_Purpose.dbc"
#-----------------------------------------------------------------------------
# Name          : SYSPASSWORD
# Description   : Password for SYS user
# Mandatory     : Yes
#-----------------------------------------------------------------------------
SYSPASSWORD = "oracle"
#-----------------------------------------------------------------------------
# Name          : SYSTEMPASSWORD
# Description   : Password for SYSTEM user
# Mandatory     : Yes
#-----------------------------------------------------------------------------
SYSTEMPASSWORD = "oracle"
#-----------------------------------------------------------------------------
# Name          : STORAGETYPE
# Description   : Specifies the storage on which the database is to be created
# Default value : FS
# Mandatory     : No
#-----------------------------------------------------------------------------
STORAGETYPE = "ASM"
#-----------------------------------------------------------------------------
# Name          : DISKGROUPNAME
# Description   : Specifies the disk group name for the storage
# Default value : DATA
# Mandatory     : No
#-----------------------------------------------------------------------------
DISKGROUPNAME = "DATA"
#-----------------------------------------------------------------------------
# Name          : RECOVERYGROUPNAME
# Description   : Specifies the disk group name for the recovery area
# Default value : RECOVERY
# Mandatory     : No
#-----------------------------------------------------------------------------
RECOVERYGROUPNAME = "FRA"
#-----------------------------------------------------------------------------
# Name          : CHARACTERSET
# Description   : Character set of the database
# Default value : "US7ASCII"
# Mandatory     : NO
#-----------------------------------------------------------------------------
CHARACTERSET = "AL32UTF8"
#-----------------------------------------------------------------------------
# Name          : NATIONALCHARACTERSET
# Description   : National Character set of the database
# Default value : "AL16UTF16"
# Mandatory     : No
#-----------------------------------------------------------------------------
NATIONALCHARACTERSET = "AL16UTF16"
#-----------------------------------------------------------------------------
# Name          : AUTOMATICMEMORYMANAGEMENT
# Description   : flag to indicate Automatic Memory Management is used
# Default value : TRUE
# Mandatory     : NO
#-----------------------------------------------------------------------------
AUTOMATICMEMORYMANAGEMENT = TRUE
#-----------------------------------------------------------------------------
# Name          : TOTALMEMORY
# Description   : total memory in MB to allocate to Oracle
# Default value :
# Mandatory     : NO
#-----------------------------------------------------------------------------
TOTALMEMORY = "700"
#/*********
#/*************** End of Create Database Section *****************************
#/*********

We have mentioned OPERATION_TYPE = “createDatabase” in the response file to instruct DBCA to create a Oracle database based on the other input parameters provided in the response file.

If you observed the response file, you might have noticed few parameters which were not there prior to 12c. Let me quickly provide an overview of these parameters

  • CREATEASCONTAINERDATABASE: This parameter in DBCA response file determines whether to create the database as a CDB or Non-CDB. It can have two possible values TRUE/FALSE. By default the value is FALSE, which means DBCA will create the database as a non CDB database. We need to set the value of this parameter to TRUE if we wish to create a CDB.
  • NUMBEROFPDBS: This parameter determines the number of PDBs to be created within the CDB. The default for this parameter is 0, in which case DBCA will not create any PDB within the respective CDB. We can have a maximum of 255 PDBs within a PDB and hence the value ranges from 0-255. This parameter comes into effect when CREATEASCONTAINERDATABASE is set to true.
  • PDBNAME: This parameter determines the name of the PDB in case of NUMBEROFPDBS is selected as 1. If the NUMBEROFPDBS are more than 1, then this value determines the prefix of all the PDBs within the CDB and DBCA will append a number to each of the PDB prefix to distinguish among PDBs.
  • PDBADMINPASSWORD: This parameter determines the password for the PDB ADMIN user.

Note: All though all these parameters are mentioned as non-mandatory, there is a dependency among these parameters which determines whether the other parameter is mandatory or not. For instance, if we choose NUMBEROFPDBS more than 0, then PDBNAME and PDBADMINPASSWORD are mandatory parameters to be set.

Further, with respect to the datafile storage; since I am using ASM, I had to set STORAGETYPE and subsequently DISKGROUPNAME and RECOVERYGROUPNAME parameters in the response file. In case of local file system, these parameters needs to be ignored and the following parameters need to be set.

#/************
#/************ Storage parameters for Local file system **********
#/************
#-----------------------------------------------------------------------------
# Name          : DATAFILEDESTINATION
# Description   : Location of the data file's
# Default value : $ORACLE_BASE/oradata
# Mandatory     : No
#-----------------------------------------------------------------------------
DATAFILEDESTINATION = {path_for_local_fs}
#-----------------------------------------------------------------------------
# Name          : RECOVERYAREADESTINATION
# Description   : Location of the data file's
# Default value : $ORACLE_BASE/flash_recovery_area
# Mandatory     : No
#-----------------------------------------------------------------------------
RECOVERYAREADESTINATION= {path_for_local_fs}
#/*********
#/********* End Of local file system storage parameters ***********
#/*********

Additionally, if we are creating the database as a RAC database, we need to set few additional parameters as listed below

#/************
#/************ RAC specific parameters **********
#/************
#-----------------------------------------------------------------------------
# Name          : DATABASECONFTYPE
# Description   : database conf type as Single Instance, Real Application Cluster or Real Application Cluster One Nodes database
# Default value : SI
# Mandatory     : No
#-----------------------------------------------------------------------------
DATABASECONFTYPE=RAC
#-----------------------------------------------------------------------------
# Name          : NODELIST
# Description   : Comma-separated list of cluster nodes
# Default value : None
# Mandatory     : No (Yes for RAC database-centric database )
#-----------------------------------------------------------------------------
NODELIST= {list_of_cluster_nodes}
#/*********
#/********* End of RAC specific parameters **********
#/*********

Once we have the response file ready for DBCA, we can execute DBCA in silent mode to create the CDB as follows. I have saved the response parameters in /data/response/create_cdb_dbca.rsp file

#/***********
#/*********** Running DBCA in silent mode using response file **********
#/***********
[oracle@mylab response]$ $ORACLE_HOME/bin/dbca -silent -responseFile /data/response/create_cdb_dbca.rsp
Registering database with Oracle Restart
4% complete
Copying database files
5% complete
6% complete
12% complete
17% complete
22% complete
27% complete
30% complete
Creating and starting Oracle instance
32% complete
35% complete
36% complete
37% complete
41% complete
44% complete
45% complete
48% complete
Completing Database Creation
50% complete
53% complete
55% complete
63% complete
71% complete
74% complete
Creating Pluggable Databases
79% complete
100% complete
Look at the log file "/app/oracle/cfgtoollogs/dbca/mycdb/mycdb.log" for further details.
[oracle@mylab response]$

We can check logs under $ORACLE_BASE/cfgtoollogs/dbca/ for any issues related to the DBCA execution.

Now, lets validate if the CDB is created as per our input from the response file.

----------
---------- Check if the database is created as CDB ---------
----------
SQL> select name,open_mode,cdb from v$database;

NAME                           OPEN_MODE            CDB
------------------------------ -------------------- ---
MYCDB                          READ WRITE           YES

----------
---------- Check if the PDB (mypdb_1) is created or not -------
----------
SQL> select CON_ID,DBID,NAME,OPEN_MODE from v$containers;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         1 2231909403 CDB$ROOT                       READ WRITE
         2  457461496 PDB$SEED                       READ ONLY
         3  261421620 MYPDB_1                        READ WRITE

		 
----------
---------- 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
		 

----------
---------- Check if database parameters are set as per response file input -------
----------
SQL> select name,value from v$parameter where name in ('memory_target','db_create_file_dest','db_recovery_file_dest');

NAME                                VALUE
----------------------------------- ---------------
memory_target                       734003200
db_create_file_dest                 +DATA
db_recovery_file_dest               +FRA

----------
---------- Check database components installed by DBCA -------------
----------
SQL> select name,COMP_NAME,VERSION,STATUS from v$database,dba_registry;

NAME            COMP_NAME                                VERSION         STATUS
--------------- ---------------------------------------- --------------- ---------------
MYCDB           Oracle Database Vault                    12.1.0.2.0      VALID
                Oracle Application Express               4.2.5.00.08     VALID
                Oracle Label Security                    12.1.0.2.0      VALID
                Spatial                                  12.1.0.2.0      VALID
                Oracle Multimedia                        12.1.0.2.0      VALID
                Oracle Text                              12.1.0.2.0      VALID
                Oracle Workspace Manager                 12.1.0.2.0      VALID
                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
                JServer JAVA Virtual Machine             12.1.0.2.0      VALID
                Oracle XDK                               12.1.0.2.0      VALID
                Oracle Database Java Packages            12.1.0.2.0      VALID
                OLAP Analytic Workspace                  12.1.0.2.0      VALID
                Oracle OLAP API                          12.1.0.2.0      VALID
                Oracle Real Application Clusters         12.1.0.2.0      OPTION OFF

16 rows selected.

----------
---------- End of Validation ---------------------
----------

As we can see, the container database (mycdb) is being created as expected according to the input from the response file. This silent mode of creating database is very useful when there are limitations around accessing GUI or when there is a need to script the database creation as part of database deployment projects.

Whatever method we choose to run DBCA, there is always a disadvantage in using DBCA to create a Oracle database and that is; it installs all the database components (as seen in the validation query) irrespective of the fact whether we would be using those components or not.

To overcome this limitation of DBCA, we have another method of creating a CDB by using CREATE DATABASE command from SQL*Plus command line utility.

We will explore that option in the next article..

No Responses
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed