Oracle has come up with new feature called multitenant database. Using this feature we can have many pluggable databases plugged into single container database.
This article explains pluggable databases architecture and how to create them.
I am not covering administration part. Viewers can check the documentation mentioned in reference section to check administrative part for managing pluggable databases.
Multitenant Architecture
One of the high-profile new features of Oracle 12c Enterprise Edition(EE) is the multitenant option – multitenant container database (CDB). This option allows the creation of many pluggable databases (PDBs) inside a CDB. The PDBs share resources provided by the CDB, such as memory, background processes, UNDO, REDO, and control files. This enables more databases to run on a single platform than was possible with the former Oracle 11gR2 architecture.
You can consider all databases till 11g as non-container databases or non-CDB. In oracle 12c we can still create non-CDB databases using normal “create database” command and administration wise there is no difference in managing those 12c databases compared to 11g database.
Oracle Multitenant architecture contains following:
One root container database
This looks like a normal database, but the purpose is different. This stores Oracle-supplied metadata and common users. It has all component that a normal database has – controlfile, online redo logs, SYSTEM and SYSAUX tablespace, Pfile and SPfile, archive logs and flasback logs etc. You can also create additional users, tablespace in this database.
One seed pluggable database
This is a seed databases which gets created when we create container database. This is nothing but set of datafiles in different location. This seed database can be used to create new pluggable databases.
Zero or more user-created PDBs
We can create multiple pluggable databases in single container databases. There are different methods of creating pluggable databases. Using above seed database to create pluggable database is just 1 method. We can also clone existing pluggable database from same container database or different container database. We can also make other non-CDB oracle 12c databases as pluggable database in our container database. You can refer to doc mentioned in reference section for more details on this.
Following figure show multitenant architecture:
Container database is same as normal database having all required component same as normal database.
Pluggable database has its own SYSTEM, SYSAUX and data tablespace. All other components like UNDO tablespace, online redo logs, controlfile, archive logs etc are common for container database and pluggable database.
When compared to RAC, I feel this architecture is exactly opposite. In RAC we have multiple instances and 1 database. In multitenant architecture we have single instance and multiple databases.
Creating Multitenant Container database
Creating container database is similar to creating non-container database but with few changes. Following changes should be made if we want to create container database
1) Define enable_pluggable_database parameter to true.
By default this parameter is false. Meaning that by default database will be created as non-multitenant database. If you want to create multitenant database, you have to make this parameter true.
Failing to make this parameter true will give you following error if you try to create multitenant database
ORA-65093: multitenant container database not set up properly
2) Change in “Create database” command
So if you are going to create multitenant database using “create database” command, you need to add couple of clause to this command.
Following clause needs to be added
- ENABLE PLUGGABLE DATABASE
- SEED
Following steps can be used to create multitenant database
Step 1) Create pfile and spfile
You can use following parameters as generic to create test database. You can alter the memory parameters and include/remove many parameters as required. But these are good enough to create test instance.
cat initdeocdb.ora db_name='deocdb' sga_max_size=4G shared_pool_size=1200M shared_pool_reserved_size=300M java_pool_size = 200M pga_aggregate_target=1G processes = 300 audit_trail ='db' db_block_size=8192 db_domain='' db_recovery_file_dest='/u01/oradata/databases/flashback' db_recovery_file_dest_size=50G open_cursors=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDO_T1' control_files = (/ctl-01/databases/deocdb/control.ctl, /ctl-02/databases/deocdb/control.ctl) compatible ='12.0.0' audit_file_dest = /dumps-01/databases/deocdb/adump core_dump_dest = /dumps-02/databases/deocdb/cdump diagnostic_dest = /dumps-01 sec_case_sensitive_logon = FALSE utl_file_dir = /dumps-01/databases/deocdb/output enable_pluggable_database=true create spfile from pfile;
***Its important to set java_pool_size in pfile/spfile. If you do not set java_pool_size, we will see following errors in alert log while creating container database
ORA-04031: unable to allocate 4096 bytes of shared memory (“java pool”,”/51835a0f_ReflectionFactoryGet”,”JOXLE^5cb1fb41″,”:SGAClass”)
Step 2) Create database command
I used following create database command to create container database. Highlighted lines are additional clauses for creating container database.
startup nomount;
CREATE DATABASE deocdb
USER SYS IDENTIFIED BY welcome
USER SYSTEM IDENTIFIED BY welcome
LOGFILE GROUP 1 ('/redo-01-a/databases/deocdb/redo-t01-g01-m1.log',
'/redo-03-a/databases/deocdb/redo-t01-g01-m2.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/redo-02-a/databases/deocdb/redo-t01-g02-m1.log',
'/redo-04-a/databases/deocdb/redo-t01-g02-m2.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/redo-01-a/databases/deocdb/redo-t01-g03-m1.log',
'/redo-03-a/databases/deocdb/redo-t01-g03-m2.log') SIZE 100M BLOCKSIZE 512,
GROUP 4 ('/redo-02-a/databases/deocdb/redo-t01-g04-m1.log',
'/redo-04-a/databases/deocdb/redo-t01-g04-m2.log') SIZE 100M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF8
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/oradata/databases/deocdb/system01.dbf'
SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/oradata/databases/deocdb/sysaux01.dbf'
SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE admin
DATAFILE '/u01/oradata/databases/deocdb/admin-01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '/u01/oradata/databases/deocdb/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undo_t1
DATAFILE '/u01/oradata/databases/deocdb/undo_t1-01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT = ('/u01/oradata/databases/deocdb/',
'/u01/oradata/databases/pdbseed/')
SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILES SIZE 100M
USER_DATA TABLESPACE users
DATAFILE '/u01/oradata/databases/pdbseed/users-01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Step 3) run catcdb.sql
DO NOT RUN catlog.sql and catproc.sql
If you are creating container database, you need to run only catcdb.sql.
This script takes care of running all other dependent scripts internally.
If you run catlog.sql and catproc.sql, this script will fail later at many stages with following errors
catcon: ALL catcon-related output will be written to dbmsxdbt_catcon_2527.lst catcon: See dbmsxdbt*.log files for output generated by scripts catcon: See dbmsxdbt_*.lst files for spool files, if any catconInit: database is not open on the default instance Unexpected error encountered in catconInit; exiting
In case of normal database creation, we usually run catlog.sql and catproc.sql as mentioned in http://docs.oracle.com/database/121/ADMIN/create.htm#ADMIN11082
But that’s for normal database creation and process is same in 12c. But for creating container database, we should be running only catcdb.sql
catcdb.sql will ask for 3 inputs – new sys password, new system password and temp tablespace
SQL> @?/rdbms/admin/catcdb.sql Session altered. Enter new password for SYS: Enter new password for SYSTEM: Enter temporary tablespace name: TEMP
Once you provide this, it will create root container database with whatever database name you give. Container name for root container DB will be CDB$ROOT.
It will also create seed pluggable container database. Container name will be seed$pdb
Step 4) Run utlrp.sql to compile invalid objects.
There will be few invalid objects in database. You can run utlrp.sql to compile the same
Validation
You can run few commands to validate if you database is container databases and what all containers it has.
We have a new column in v$database called CDB. If your database is container database it will show as YES
SYS.DEOCDB.PRIMARY>select name, cdb from v$database; NAME CDB --------- --- DEOCDB YES 1 row selected.
You can also check different containers in your database using following
SYS.DEOCDB.PRIMARY>select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers; CON_ID NAME OPEN_MODE TOTAL_SIZE ---------- ------------------------------ ---------- ---------- 1 CDB$ROOT READ WRITE 0 2 PDB$SEED READ ONLY 1599078400
V$CONTAINER is the new view available in 12c. When this is run from root container, it shows all containers CDB as well as PDB. But if you run same command from any PDB, it will show that container.
SYS.DEOCDB.PRIMARY>alter session set container=PDB$SEED; Session altered. SYS.DEOCDB.PRIMARY>select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers; CON_ID NAME OPEN_MODE TOTAL_SIZE ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY 1599078400
Creating Pluggable database
Following command can be used to create pluggable database from seed pluggable database PDB$SEED
SYS.DEOCDB.PRIMARY>create pluggable database deopdb1 admin user deodba identified by welcome FILE_NAME_CONVERT=('/u01/oradata/databases/pdbseed/','/u01/oradata/databases/deopdb1/');
Pluggable database created.
Above command is in its basic form and all parameter/clauses mentioned are mandatory. You need to provide FILE_NAME_CONVERT so that files from seed database will be copied to location you provided for your pluggable database. There are many parameters to this command and you can explore them in documentation link provided in reference.
Initially when you create pluggable database it will be in MOUNTED state. This is because pluggable database use same memory structure and control file as root container database. So memory structure and control file is already allocated.
SYS.DEOCDB.PRIMARY>select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers; CON_ID NAME OPEN_MODE TOTAL_SIZE ---------- ------------------------------ ---------- ---------- 1 CDB$ROOT READ WRITE 0 2 PDB$SEED READ ONLY 1599078400 3 DEOPDB1 MOUNTED 0
You need to just open the database.
You can open pluggable database from root container when you logged in using following command
SYS.DEOCDB.PRIMARY>show con_name; NAME_COL_PLUS_PDB_CONTAINER ------------------------------------- CDB$ROOT SYS.DEOCDB.PRIMARY>alter pluggable database deopdb1 open; Pluggable Database opened.
Or you can change container name and simply start
SYS.DEOCDB.PRIMARY>alter session set container=DEOPDB1; Session altered. SYS.DEOCDB.PRIMARY>show con_name; NAME_COL_PLUS_PDB_CONTAINER ------------------------------------- DEOPDB1 SYS.DEOCDB.PRIMARY>startup Pluggable Database opened.
You can shutdown pluggable database using similar commands
if you are in root container, you can use following commands
SYS.DEOCDB.PRIMARY>show con_name; NAME_COL_PLUS_PDB_CONTAINER ------------------------------------- CDB$ROOT SYS.DEOCDB.PRIMARY>alter pluggable database deopdb1 close; Pluggable database altered.
Else you can change container and shutdown the database.
SYS.DEOCDB.PRIMARY>alter session set container=DEOPDB1; Session altered. SYS.DEOCDB.PRIMARY>show con_name; NAME_COL_PLUS_PDB_CONTAINER -------------------------------------- DEOPDB1 SYS.DEOCDB.PRIMARY> SYS.DEOCDB.PRIMARY> SYS.DEOCDB.PRIMARY>shut immediate; Pluggable Database closed.
Reference
http://docs.oracle.com/database/121/ADMIN/cdb_intro.htm#ADMIN13508
http://docs.oracle.com/database/121/ADMIN/cdb_plug.htm#ADMIN13556
http://docs.oracle.com/database/121/ADMIN/cdb_create.htm#ADMIN13521
https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CNCPT89234
Hope this helps !!
Persistent Storage Solutions 