Oracle 12c-Multitenant Database

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.

cdb

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.

db_consolidation

 

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.

12c_consolidation

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

[[email protected] ~]$ sqlplus hr/[email protected]//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.

Leave a Reply