oracle_multitenant

Overview
Using SQL
Using DBCA
Automation

Overview

Multitenancy is a new concept in Oracle 12c. The basis of it is that, you can separate the application data from the system data. Up to and including 11g, all the data in the database was residing in the same dimension as the system data or any other data for that matter. Data related to different application, were in the same tablespaces or even managed by the same user. Well, from 12c, all this can go under the table, now you can have many databases PDBs (pluggable databases), inside one big database, the root container. There are even more possibilities like application containers and so on, but we will see that later.

With the multi-tenancy, we have a:

root container - Where the system data resides. Also you can choose if you want the UNDO tablespace in the root to be used for all PDBs, or each PDB to have its own undo, SYSTEM and SYSAUX tablespace, making it completely independent from the root container.
Application Container - Container which consist of 1 or many PDBs which host a specific application. We are able to assign versions and name to such container, furthermore we can initiate installation and upgrade modes.

Using SQL

Firstly, we need to create our container. From 12c, we can create a database to be either as it always has been, a normal database, OR we can create it to allow other databases as well. So it will be a container, let's see how we can do both:

Creation

Actually, the creation of a normal database isn't that hard, you can just type:

Create simple database

SQL> Create database;

Simply that, will create us a normal database, well after that you will have to run the catproc and the catalog scripts, but you get the gist.

Create container DB

To create a container, database we NEED to allow it to have PLUGGABLE databases, as follows:

Create CDB

SQL> CREATE DATABASE CDB1 ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT = ('/loc1/data','/loc2/data');

That will create us a container database called CDB1 and a SEED, after that you will have to run script:

@$ORACLE_HOME/rdbms/admin/catcdb.sql

But other than that, all will be ok. So we have a container, big deal, what then. Well, now we can create PLUGGABLE databases as follows:

Create Pluggable DB (PDB)

As I said before, pluggable databases, are totally separate databases from one another. Imagine it having many databases (PDBs) within one big database (CDB). These PDBs, can have separate user totally individual from each other. So let's see how it is done:

Create PDB

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb2/');

You can use either:

FILE_NAME_CONVERT, or
CREATE_FILE_DEST, or
PDB_FILE_NAME_CONVERT

Either in the session level or on the CREATE statement, up to you, the result will be the same.

Once created, you can list all PDBs in a certain CDB as follows:

List PDBs

COLUMN pdb_name FORMAT A20

SELECT pdb_name, status
FROM   dba_pdbs
ORDER BY pdb_name;

PDB_NAME	     STATUS
-------------------- -------------
PDB$SEED	     NORMAL
PDB1		     NORMAL
PDB2		     NEW
PDB3		     NEW

COLUMN name FORMAT A20

SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
PDB1			       MOUNTED
PDB2			       MOUNTED
PDB3			       MOUNTED

Remember, 1 PDB can be in only 1 ROOT$CDB, but 1 CDB can have many PDBs. Furthermore, from 12.2, we have also applicational containers, which are containers within the CDB, but we will speak about them in a second.

Using DBCA

We can use DBCA to create and delete CDB and PDBs as follows:

Examples

# Local container (cdb1).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb1 \
 -pdbAdminPassword SysPassword1 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u02/oradata/" \
 -redoLogFileSize 50 \
 -initParams encrypt_new_tablespaces=DDL \
 -emConfiguration NONE \
 -ignorePreReqs

# Remote container (cdb3) with PDB (pdb5).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb3 -sid cdb3 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb5 \
 -pdbAdminPassword SysPassword1 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u02/oradata/" \
 -redoLogFileSize 50 \
 -initParams encrypt_new_tablespaces=DDL \
 -emConfiguration NONE \
 -ignorePreReqs

# Non-CDB instance (db12c).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname db12c -sid db12c -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase false \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u02/oradata/" \
 -redoLogFileSize 50 \
 -initParams encrypt_new_tablespaces=DDL \
 -emConfiguration NONE \
 -ignorePreReqs

# Delete the instances.
#dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1
dbca -silent -deleteDatabase -sourceDB cdb3 -sysDBAUserName sys -sysDBAPassword SysPassword1
dbca -silent -deleteDatabase -sourceDB db12c -sysDBAUserName sys -sysDBAPassword SysPassword1

#Configure OMF
export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

ALTER SYSTEM SET db_create_file_dest = '/u02/oradata';

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE pdb5 OPEN;
ALTER PLUGGABLE DATABASE pdb5 SAVE STATE;

EXIT;
EOF

Automation

Using the awesome power, vested to me by Vagrant and Bash, I summon the automation of:

Virtual Machine
Oracle 19c database
1 CDB
1 PDB

All that, twice:

Automation

$allow_shell = <<SCRIPT
sed -i 's/PasswordAuthentication no/PasswordAuthentication yes/g' /etc/ssh/sshd_config    
systemctl restart sshd.service
SCRIPT

$edit_hosts_file = <<SCRIPT
cat > /etc/hosts <<EOF
127.0.0.1 localhost
192.168.0.l03 lpar1.example.com 
192.168.0.l04 lpar2.example.com 
EOF
SCRIPT

$install_oracle_preinstall = <<SCRIPT
curl -o oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
SCRIPT

$install_oracle_19c = <<SCRIPT
cd /vagrant
yum -y localinstall oracle-database-ee-19c-1.0-1.x86_64.rpm

SCRIPT

$install_19c_db = <<SCRIPT
runuser -l oracle -c '/opt/oracle/product/19c/dbhome_1/bin/dbca -silent -createDatabase -responseFile  /vagrant/simple_db_cdb.rsp'
SCRIPT

$install_vnc_server = <<SCRIPT
yum -y install vnc-server
yum -y install tigervnc-server
yum -y install xterm
yum -y install net-tools
yum -y install unzip
yum -y install curl
SCRIPT


Vagrant.configure("2") do |config|
 (1..2).each do |i|
  config.vm.provision :shell, inline: "setenforce 0", run: "always"
  config.vm.define "lpar#{i}" do |lpar|
    lpar.vm.box = "centos/7"
    lpar.vm.hostname = "lpar#{i}.example.com"
    lpar.vm.synced_folder "./lpar_binaries", "/vagrant", type: "virtualbox"
	lpar.vm.network "public_network", ip: "192.168.0.10#{2+i}"
	lpar.vm.network "private_network", ip: "10.100.0.10#{2+i}"
	lpar.vm.provision "shell", inline: $install_vnc_server, privileged: true
	lpar.vm.provision "shell", inline: $allow_shell, privileged: true
	lpar.vm.provision "shell", inline: $edit_hosts_file, privileged: true
	lpar.vm.provision "shell", inline: $install_oracle_preinstall, privileged: true
	lpar.vm.provision "shell", inline: $install_oracle_19c, privileged: true
	lpar.vm.provision "shell", inline: $install_19c_db, privileged: true
    lpar.vm.provider :virtualbox do |v|
      v.customize ["modifyvm", :id, "--memory", 2560]
      v.customize ["modifyvm", :id, "--name", "lpar#{2+i}"]
	  v.customize ["storageattach", :id, 
                "--storagectl", "IDE", 
                "--port", "0", "--device", "1", 
                "--type", "dvddrive", 
                "--medium", "emptydrive"]    
     end
    end
  end
end

Just make sure, you have the Oracle 19c RPM in folder called: “lpar_binaries” in your Vagrant main folder and the response file below:

Response file

responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=orcl.example.com
sid=orcl
databaseConfigType=SI
policyManaged=false
createServerPool=false
force=false
createAsContainerDatabase=true
numberOfPDBs=1
pdbName=pdb1
useLocalUndoForPDBs=true
pdbAdminPassword=Oracle123
templateName=/opt/oracle/product/19c/dbhome_1/assistants/dbca/templates/General_Purpose.dbc
sysPassword=Oracle123
systemPassword= Oracle123
emExpressPort=0
runCVUChecks=FALSE
dvConfiguration=false
olsConfiguration=false
datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/
datafileDestination={ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/
recoveryAreaDestination={ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}
storageType=FS
characterSet=AL32UTF8
registerWithDirService=false
variables=ORACLE_BASE_HOME=/opt/oracle/product/19c/dbhome_1,DB_UNIQUE_NAME=orcl,ORACLE_BASE=/opt/oracle,PDB_NAME=,DB_NAME=orcl,ORACLE_HOME=/opt/oracle/product/19c/dbhome_1,SID=orcl
initParams=undo_tablespace=UNDOTBS1,db_block_size=8KB,dispatchers=(PROTOCOL=TCP) (SERVICE={SID}XDB),diagnostic_dest={ORACLE_BASE},remote_login_passwordfile=EXCLUSIVE,db_create_file_dest={ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=300,memory_target=936MB,db_recovery_file_dest_size=12732MB,open_cursors=300,db_domain=example.com,compatible=19.0.0,db_name=orcl,db_recovery_file_dest={ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME},audit_trail=db
sampleSchema=false
memoryPercentage=40
databaseType=MULTIPURPOSE
automaticMemoryManagement=true
totalMemory=0

Of course feel free to add / remove or modify any parameter which you wish. Just bare in mind that some are mandatory :)

Table of Contents

Overview

Using SQL

Creation

Create container DB

Create Pluggable DB (PDB)

Using DBCA

Automation