=====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. {{:screen_shot_2021-11-11_at_6.52.49_pm.png?1200|}} 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: 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: 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 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: 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: # 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 < ===== 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: $allow_shell = <