Oracle 12c introduced a multitenant architecture to assist in database consolidation efforts. Instead of having multiple database instance running in different VM, this In multitenant architecture, you have three components
Root CDB – This is base container and is named CDB$ROOT.
Seed – This is template which can be used to create PDB’s.It’s named PDB$SEED
PDB – Pluggable Database is used for storing application code and data. We can think of it as pre-12c database which has been plugged into the CDB. They have unique name and unique id. Oracle supports upto 256 PDB. Below diagram shows the architecture.
To explain this concept,let’s take an example. Organization XYZ has two departments Engineering and IT. They are maintaining separate database with similar structure. We have HR.EMP,HR.DEPT in both the databases. Prior to 12c, if we had to consolidate, then we could consider following approaches
- Merge the records from both tables. This is not easy as we will require to ensure no primary-key collision,no table structure mismatch (e.g datatype,extra columns)
- Create two databases on single machine. This approach would require managing two instances and resource requirement for SGA,Processes
- Third option will to be consolidate into separate schema’s. e.g ENG.EMP and IT.EMP.
This approach is not practical as application code will have to be modified to differentiate the schema’s. Another challenge could be when you have hard coded schema name (say third-party application).
Enter 12c , multitenant architecture 🙂 You can now plug these into two separate PDB’s without the need to modify application code or having to consolidate into separate databases running on same machine.
There are also changes to database users. We now have common user and local user.
- Common user are created in CDB$ROOT and can access all PDB. SYS and SYSTEM are default common users. Username has to start with c## or C## and can have ASCII or EDCDIC characters
- Local user are local to a container and can be created only when connected to PDB. Since these are local to PDB, you can use same username in multiple PDB without compromising on security
Let’s try creating a user
SQL> create user amit identified by amit;
create user amit identified by amit
*
ERROR at line 1:
ORA-65096: invalid common user or role name
In 12c multitenant architecture, above syntax fails as by default you are connected to CDB$ROOT and are allowed to only create common user. Local user creation also fails
SQL> create user amit identified by amit container=current;
create user amit identified by amit container=current
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT
When you connect to database using “/ as sysdba”, you connect to CDB$ROOT. You can verify this by using following option
SQL> show con_id
CON_ID
------------------------------
1
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
--One more method
SQL> col connection for a30
SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME') "connection" from dual;
connection
------------------------------
CDB$ROOT
During DB creation using DBCA, I didn’t choose a PDB. Let’s create one PDB manually.
create pluggable database engg admin user amit identified by amit roles=(dba) file_name_convert=('/oracle/oradata','/oracle/oradata1');
engg - PDB name
amit - admin user
file_name_convert - specifies the naming convention for datafile. Can also set CREATE_FILE_DEST for OMF files
SQL> alter session set container=engg;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
ENGG
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl12c/undotbs01.dbf
/oracle/oradata1/orcl12c/pdbseed/system01.dbf
/oracle/oradata1/orcl12c/pdbseed/sysaux01.dbf
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata1/orcl12c/pdbseed/pdbseed_temp012015-07-02_11-50-51-PM.dbf
I queried v$views and not dba_*_files as PDB are in mounted state and not opened in read-write by default
SQL> select file_name from dba_data_files;
select file_name from dba_data_files
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only
Status column in dba_pdbs shows values “NEW” which indicates that PDB has been never created since it has been opened
select PDB_ID,PDB_NAME,CON_UID,STATUS from dba_pdbs
PDB_ID PDB_NAME CON_UID STATUS
---------- -------------------- ---------- ---------
3 ENGG 2147966977 NEW
2 PDB$SEED 2939067613 NORMAL
select con_id,name,open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 ENGG MOUNTED
--Open the PDB using alter pluggable database
SQL> alter pluggable database engg open;
SQL> select con_id,name,open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 ENGG READ WRITE
Checking alert log, we get following messages.
Pluggable database ENGG dictionary check beginning
Pluggable Database ENGG Dictionary check complete
Database Characterset for ENGG is WE8MSWIN1252
2015-07-03 04:49:40.454000 -07:00
Opening pdb ENGG (3) with no Resource Manager plan active
Pluggable database ENGG opened read write
Completed: alter pluggable database engg open
Note that there is single CDB alert log and no separate alert log for PDB. We now create a local user to connect to this database and store our tables
alter session set container=engg; create user hr identified by hr; grant connect,resource to hr;
Let’s try connecting to database. We can use ezconnect connection method
[oracle@oracle11g ~]$ sqlplus hr/hr@//oracle11g:1522/engg SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 3 07:34:58 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. ERROR: ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
Pluggable database has been started with restricted session privilege. When we try to disable restricted session, we get error
SQL> select instance_name,status,logins from v$instance; INSTANCE_NAME STATUS LOGINS ---------------- ------------ ---------- orcl12c OPEN RESTRICTED SQL> alter system disable restricted session; alter system disable restricted session * ERROR at line 1: ORA-65144: ALTER SYSTEM DISABLE RESTRICTED SESSION is not permitted
Above issue is due to bug in 12.1.0.2 which is discussed here
Dropping pluggable database
SQL> alter pluggable database test close; Pluggable database altered. SQL> drop pluggable database test; drop pluggable database test * ERROR at line 1: ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged SQL> drop pluggable database test including datafiles; Pluggable database dropped.
To create a common user, we need to prefix c## or C##. Note that if default tablespace is specified, then it should exist in all the PDB’s
SQL> create user c##amit identified by amit default tablespace users temporary tablespace temp container=all; User created. SQL> grant dba to c##amit; Grant succeeded.