What are the Oracle 12c Pluggable databases? How does a pluggable database work?
Answer: A pluggable database is a new construct whereby you can encapsulate a sub-set of Oracle data tables and indexes along with its associated metadata from the data dui dictionary. You start by creating a "root" instance database, called a container database (CDB). This container is used to hold many pluggable database "tenants" (PDB's), in a type of architectural separation.
The most important part of this separation is the "split" data dictionary, whereby everything needed for a PDB is self-contained in this sub-set of the metadata.
Unlike a traditional database where only one database can exist per instance, the Oracle 12c pluggable databases allow multiple databases within an instance. Oracle pluggable databases ease the movement into database consolidation because the data dictionary information (
obj$, tab$ and
source$) are independent of any container database. All PDB's within the container share a common LGWR process.
This container can then be populated with sub-sets of a schema and then be can be un-plugged from one instance and re-added to another instance quickly. Because the table definitions, index definitions, constraints and data all reside within the PDB, they can be easily copied and moved between instances and servers. Oracle has termed "multi-tenancy" to describe the process of creating a CDB that contains many "tenant" PDB's.
The pluggable databases provide significant reduction in overhead on systems (like Exadata) where we'd like to host multiple databases, but can't consolidate them due to conflicts that prevent them running under the same instance (public synonyms / database links, schema name collision, etc). There are several benefits to pluggable databases:
1 - Easy fast cloning: Simply copy the PDB very quickly
2 - Easy upgrades: Just copy/move the PDB to a container running a higher release of Oracle
There is a new pluggable database administrator role in 12c called the CDB Administrator role.
Connecting to the root of a container database is the same as that of any previous database instance. On the database server you can use OS Authentication.
[oracle@vmdb12c ~]$ sqlplus / as sysdba
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
The V$SERVICES views can be used to display available services from the database.
SQL> select name,pdb from v$services order by 2;
NAME PDB
——————– ———-
zwcXDB CDB$ROOT
SYS$BACKGROUND CDB$ROOT
SYS$USERS CDB$ROOT
zwc CDB$ROOT
zhongwc1 ZHONGWC1
zhongwc2 ZHONGWC2
6 rows selected.
The lsnrctl utility allows you to display the available services from the command line.
[oracle@vmdb12c ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 – Production on 06-SEP-2013 22:10:41
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmdb12c)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 – Production
Start Date 06-SEP-2013 19:32:46
Uptime 0 days 2 hr. 37 min. 54 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/vmdb12c/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmdb12c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service "zhongwc1" has 1 instance(s).
Instance "zwc", status READY, has 1 handler(s) for this service…
Service "zhongwc2" has 1 instance(s).
Instance "zwc", status READY, has 1 handler(s) for this service…
Service "zwc" has 1 instance(s).
Instance "zwc", status READY, has 1 handler(s) for this service…
Service "zwcXDB" has 1 instance(s).
Instance "zwc", status READY, has 1 handler(s) for this service…
The command completed successfully
[oracle@vmdb12c ~]$ lsnrctl service
LSNRCTL for Linux: Version 12.1.0.1.0 – Production on 06-SEP-2013 22:10:45
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmdb12c)(PORT=1521)))
Services Summary…
Service "zhongwc1" has 1 instance(s).
Instance "zwc", status READY, has 1 handler(s) for this service…
Handler(s):
"DEDICATED" established:5 refused:0 state:ready
LOCAL SERVER
Service "zhongwc2" has 1 instance(s).
Instance "zwc", status READY, has 1 handler(s) for this service…
Handler(s):
"DEDICATED" established:5 refused:0 state:ready
LOCAL SERVER
Service "zwc" has 1 instance(s).
Instance "zwc", status READY, has 1 handler(s) for this service…
Handler(s):
"DEDICATED" established:5 refused:0 state:ready
LOCAL SERVER
Service "zwcXDB" has 1 instance(s).
Instance "zwc", status READY, has 1 handler(s) for this service…
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: vmdb12c, pid: 4294>
(ADDRESS=(PROTOCOL=tcp)(HOST=vmdb12c)(PORT=34716))
The command completed successfully
Connections using services are unchanged from previous versions.
[oracle@vmdb12c ~]$ sqlplus system/oracle@192.168.1.14:1521/zhongwc1
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
3 ZHONGWC1 READ WRITE NO
SQL> show con_name
CON_NAME
——————————
ZHONGWC1
[oracle@vmdb12c ~]$ sqlplus system/oracle@192.162.200.10:1521/zhongwc2
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
4 ZHONGWC2 READ WRITE NO
SQL> show con_name
CON_NAME
——————————
ZHONGWC2
[oracle@vmdb12c ~]$ sqlplus system/oracle@zhongwc1
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
3 ZHONGWC1 READ WRITE NO
SQL> show con_name
CON_NAME
——————————
ZHONGWC1
[oracle@vmdb12c ~]$ sqlplus system/oracle@zhongwc2
SQL> show con_name
CON_NAME
——————————
ZHONGWC2
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
4 ZHONGWC2 READ WRITE NO
tnsnames.ora
ZWC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmdb12c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zwc)
)
)
zhongwc1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmdb12c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zhongwc1)
)
)
zhongwc2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmdb12c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zhongwc2)
)
)
LISTENER_ZWC =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmdb12c)(PORT = 1521))
When logged in to the CDB as an appropriately privileged user, the ALTER SESSION command can be used to switch between containers within the container database.
[oracle@vmdb12c ~]$ sqlplus / as sysdba
SQL> alter session set container=zhongwc1;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
3 ZHONGWC1 READ WRITE NO
SQL> alter session set container=zhongwc2;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
4 ZHONGWC2 READ WRITE NO
SQL> alter session set container=cdb$root;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ZHONGWC1 READ WRITE NO
4 ZHONGWC2 READ WRITE NO
SQL> select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
——————————————————————————–
CDB$ROOT
SQL> alter session set container=zhongwc1;
Session altered.
SQL> select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
——————————————————————————–
ZHONGWC1
SQL> alter session set container=zhongwc2;
Session altered.
SQL> select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
——————————————————————————–
ZHONGWC2
Connect to the container database as usual and use the V$INSTANCE view to see where I’m at.
[oracle@oel6 dbhome_1]$ sqlplus / as sysdba
SQL> select instance_name, version, status, con_id from v$instance;
INSTANCE_NAME VERSION STATUS CON_ID
----------------- ----------------- ------------ ----------
ora12cb 12.1.0.1.0 OPEN 0
Lets move into a PDB. I have quite a few PDBs created, lets just use PDB1.
SQL> alter session set container=PDB1;
Session altered.
Did I actually move containers? How can I tell? Instead of selecting INSTANCE_NAME from V$INSTANCE; we have two new SHOW commands that will provide us the information we are looking for. These command are quite simple (SHOW CON_ID & SHOW CON_NAME).
SQL> show con_id
CON_ID
------------------------------
3
SQL> show con_name
CON_NAME
------------------------------
PDB1
Now that we are confident that we are in PDB1, lets take a look at the V$INSTANCE view again. What you will notice is that V$INSTANCE provides us with information relative to the container database (CDB). It only makes sense that the V$INSTANCE would return CDB information. So, how do we identify items related to the pluggable database (PDB) without the SHOW commands?
Lets take a look at a view that is similar to V$INSTANCE. Enter the V$PDBS view!
SQL> desc v$pdbs;
Name Null? Type
----------------------- -------- ----------------------------
CON_ID NUMBER
DBID NUMBER
CON_UID NUMBER
GUID RAW(16)
NAME VARCHAR2(30)
OPEN_MODE VARCHAR2(10)
RESTRICTED VARCHAR2(3)
OPEN_TIME TIMESTAMP(3)
CREATE_SCN NUMBER
TOTAL_SIZE NUMBER
This view provides similar information as V$INSTANCE does; yet is only specific to PDBs. If we wanted to find out the container_id, name, open_mode and size of the PDB we are currently connected to, we can use this query:
SQL> select con_id, name, open_mode, total_size from v$pdbs;
CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE 393216000
What is interesting to point out, is that since we used an ALTER SESSION statement to switch to PDB1, the V$PDBS view only lists the PDB we are currently working in. If we want to get a complete list of PDBs within the container database, we need to go back to the root container database and run the same SQL statement.
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select con_id, name, open_mode, total_size from v$pdbs;
CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY 283115520
3 PDB1 READ WRITE 393216000
4 PDBTEST MOUNTED 0
5 PDB2 READ ONLY 393216000
****************************************************************************
Common Users & SYSDBA with #Oracle 12c Multitenancy
A 12c multitenant database introduces the new concept of local users and common users. This article shows simple use cases why DBAs may want to create common users – in contrast to the common users that are created automatically, like SYS, SYSTEM, MDSYS etc.
A typical requirement is to have a superuser other than SYS, but with the same power. Like the common user C##_SYS in the picture below.
Or suppose we have many pluggable databases (PDBs) and different superusers responsible for different PDBs like C##_ADMIN1 and C##_ADMIN2:
Common Users in Oracle 12cLet’s implement it as above. Initially, my demo environment looks like this:
SQL> select name,open_mode,con_id from v$pdbs;
NAME OPEN_MODE CON_ID
------------------------------ ---------- ----------
PDB$SEED READ ONLY 2
PDB1 READ WRITE 3
PDB2 READ WRITE 4
PDB3 READ WRITE 5
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
At first, I create C##_SYS and grant SYSDBA as a common privilege to that new user:
SQL> create user c##_sys identified by oracle container=all;
User created.
SQL> grant sysdba to c##_sys container=all;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
C##_SYS TRUE FALSE FALSE FALSE FALSE FALSE 0
C##_SYS can now do anything to any PDB:
SQL> connect c##_sys/oracle@edd2r6p0/pdb1 as sysdba
Connected.
SQL> shutdown immediate
Pluggable Database closed.
SQL> connect / as sysdba
Connected.
SQL> select name,open_mode,con_id from v$pdbs;
NAME OPEN_MODE CON_ID
------------------------------ ---------- ----------
PDB$SEED READ ONLY 2
PDB1 MOUNTED 3
PDB2 READ WRITE 4
PDB3 READ WRITE 5
SQL> connect c##_sys/oracle@edd2r6p0/pdb1 as sysdba
Connected.
SQL> startup
Pluggable Database opened.
SQL> connect / as sysdba
Connected.
SQL> select name,open_mode,con_id from v$pdbs;
NAME OPEN_MODE CON_ID
------------------------------ ---------- ----------
PDB$SEED READ ONLY 2
PDB1 READ WRITE 3
PDB2 READ WRITE 4
PDB3 READ WRITE 5
Notice that there is a subtle difference in granting SYSDBA of the container database (CDB) as local or common privilege:
SQL> revoke sysdba from c##_sys container=all;
Revoke succeeded.
SQL> grant sysdba to c##_sys;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
C##_SYS TRUE FALSE FALSE FALSE FALSE FALSE 1
SQL> connect c##_sys/oracle@edd2r6p0/pdb1 as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
C##_SYS has SYSDBA of the CDB “only”, therefore the error. Although:
SQL> connect c##_sys/oracle as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=pdb1;
Session altered.
SQL> shutdown immediate
Pluggable Database closed.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> startup
Pluggable Database opened.
SQL> connect c##_sys/oracle as sysdba
Connected.
SQL> select name,open_mode,con_id from v$pdbs;
NAME OPEN_MODE CON_ID
------------------------------ ---------- ----------
PDB$SEED READ ONLY 2
PDB1 READ WRITE 3
PDB2 READ WRITE 4
PDB3 READ WRITE 5
However, the proper way is probably granting it as a common privilege:
SQL> revoke sysdba from c##_sys;
Revoke succeeded.
SQL> grant sysdba to c##_sys container=all;
Grant succeeded.
Now to the implementation of C##_ADMIN1 and C##_ADMIN2. The point is here, that SYSDBA can be granted as a local privilege for certain PDBs only, to the effect that different superusers can be responsible for different groups of PDBs:
SQL> create user c##_admin1 identified by oracle container=all;
User created.
SQL> alter session set container=pdb1;
Session altered.
SQL> grant sysdba to c##_admin1 container=current;
Grant succeeded.
SQL> connect / as sysdba
Connected.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
C##_SYS TRUE FALSE FALSE FALSE FALSE FALSE 0
C##_ADMIN1 TRUE FALSE FALSE FALSE FALSE FALSE 3
6 rows selected.
For now, C##_ADMIN1 can only connect to PDB1:
SQL> connect c##_admin1/oracle@edd2r6p0/pdb1 as sysdba
Connected.
SQL> select count(*) from session_privs;
COUNT(*)
----------
233
SQL> connect c##_admin1/oracle@edd2r6p0/pdb2 as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
The completed implementation of the picture above:
SQL> connect / as sysdba
Connected.
SQL> alter session set container=pdb2;
Session altered.
SQL> grant sysdba to c##_admin1 container=current;
Grant succeeded.
SQL> connect / as sysdba
Connected.
SQL> create user c##_admin2 identified by oracle;
User created.
SQL> alter session set container=pdb3;
Session altered.
SQL> grant sysdba to c##_admin2 container=current;
Grant succeeded.
SQL> connect / as sysdba
Connected.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
C##_SYS TRUE FALSE FALSE FALSE FALSE FALSE 0
C##_ADMIN1 TRUE FALSE FALSE FALSE FALSE FALSE 3
C##_ADMIN1 TRUE FALSE FALSE FALSE FALSE FALSE 4
C##_ADMIN2 TRUE FALSE FALSE FALSE FALSE FALSE 5
8 rows selected.
