oracle 12c引入了多租户环境，允许一个数据库容器CDB承载多个可插拔数据库PDB，CDB全称为Container Database翻译为数据库容器，PDB全称为Pluggable Database翻译为可插拔数据库，在oracle 12c之前实例与数据库是一对一或者多对一的关系，而在oracle 12c之后实例与数据库可以实现一对多的关系，也就是一个CDB，多个PDB，这个其实如果大家有接触过mysql、sqlserver这类数据库，对于这个概念就很好理解了，摘要下官方文档关于CDB和PDB的关系图：
CDB组成：
root组件：也就是CDB$ROOT,存储着ORACLE提供的元数据库和common user，元数据比如oracle提供的pl/sql包的源代码，common user是指每个容器中都存在的用户
SEED组件：Seed又叫做PDB$SEED，这个是创建PDBS数据库的模板，不能在Seed中添加或者修改对应，这个基本类似于sql server的module数据库
PDBS组件：具体的PDB数据库
ROOT、Seed、PDB关系可以这么简单描述：ROOT(根)是一个容器，Seed(种子)是一个容器，每个PDB是一个容器。每个容器在CDB中都有一个独一无二的的ID和名称。
查看数据库是否为CDB
SQL> select name,cdb from v$database;
NAME CDB
——— —
ORA12C YES
YES表示该数据库是CDB,NO表示是NO_CDB（普通数据库）
查看对应的PDB名称：
SQL> show con_name;
CON_NAME
——————————
CDB$ROOT
SQL> select sys_context(‘userenv’,’con_name’) “Container DB” from dual;
Container DB
——————————————————————————–
CDB$ROOT
SQL> select con_id,dbid,name,open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
———- ———- —————————— ———-
2 3962890709 PDB$SEED READ ONLY
3 2723517908 PDB1 MOUNTED
4 369175303 PDB2 MOUNTED
启动和关闭对应的PDBS
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> select con_id,dbid,name,open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
———- ———- —————————— ———-
2 3962890709 PDB$SEED READ ONLY
3 2723517908 PDB1 READ WRITE
4 369175303 PDB2 MOUNTED
SQL> alter pluggable database pdb1 close;
Pluggable database altered.
SQL> select con_id,dbid,name,open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
———- ———- —————————— ———-
2 3962890709 PDB$SEED READ ONLY
3 2723517908 PDB1 MOUNTED
4 369175303 PDB2 MOUNTED
切换到对应的PDBS
SQL> alter session set container=pdb1;
Session altered.
SQL> alter database open;
Database altered.
SQL> select con_id,dbid,name,open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
———- ———- —————————— ———-
3 2723517908 PDB1 READ WRITE
查看cdb、pdb模式下数据文件、日志文件、undo tablespace、控制文件等变化：
切换到cdb$root下：
SQL>conn / as sysdba
SQL> select con_id,name from v$datafile order by con_id,name;
CON_ID NAME
———- —————————————————————————————————-
1 /oracle12c/app/oracle/oradata/ora12c/sysaux01.dbf
1 /oracle12c/app/oracle/oradata/ora12c/system01.dbf
1 /oracle12c/app/oracle/oradata/ora12c/undotbs01.dbf
1 /oracle12c/app/oracle/oradata/ora12c/users01.dbf
2 /oracle12c/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf
2 /oracle12c/app/oracle/oradata/ora12c/pdbseed/system01.dbf
3 /oracle12c/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf
3 /oracle12c/app/oracle/oradata/ora12c/pdb1/sysaux01.dbf
3 /oracle12c/app/oracle/oradata/ora12c/pdb1/system01.dbf
4 /oracle12c/app/oracle/oradata/ora12c/pdb2/pdb2_users01.dbf
4 /oracle12c/app/oracle/oradata/ora12c/pdb2/sysaux01.dbf
4 /oracle12c/app/oracle/oradata/ora12c/pdb2/system01.dbf
12 rows selected.
SQL> select con_id,name from v$controlfile order by con_id,name;
CON_ID NAME
———- —————————————————————————————————-
0 /oracle12c/app/oracle/fast_recovery_area/ora12c/control02.ctl
0 /oracle12c/app/oracle/oradata/ora12c/control01.ctl
SQL> select con_id,member from v$logfile;
CON_ID MEMBER
———- —————————————————————————————————-
0 /oracle12c/app/oracle/oradata/ora12c/redo03.log
0 /oracle12c/app/oracle/oradata/ora12c/redo02.log
0 /oracle12c/app/oracle/oradata/ora12c/redo01.log
SQL> select con_id,name from v$tempfile;
CON_ID NAME
———- —————————————————————————————————-
1 /oracle12c/app/oracle/oradata/ora12c/temp01.dbf
2 /oracle12c/app/oracle/oradata/ora12c/pdbseed/pdbseed_temp012016-07-17_09-08-17-AM.dbf
3 /oracle12c/app/oracle/oradata/ora12c/pdb1/temp012016-07-17_09-08-17-AM.dbf
4 /oracle12c/app/oracle/oradata/ora12c/pdb2/temp012016-07-17_09-08-17-AM.dbf
这里可以看出CDB、PDB模式下，每个PDB都有自己的数据文件、临时文件，所有的PDB共享相同的日志文件、控制文件
[oracle@oracle12c ~]$ ls -l /oracle12c/app/oracle/product/12.1.0/dbhome_1/dbs
total 17576
-rw-rw—-. 1 oracle dba 1544 Jul 20 03:36 hc_ora12c.dat
-rw-r–r–. 1 oracle dba 2992 Feb 3 2012 init.ora
-rw-r—–. 1 oracle dba 24 Jun 25 13:12 lkORA12C
-rw-r—–. 1 oracle dba 7680 Jul 17 09:12 orapwora12c
-rw-r—–. 1 oracle dba 17973248 Jul 17 09:26 snapcf_ora12c.f
-rw-r—–. 1 oracle dba 3584 Jul 20 08:54 spfileora12c.ora
[oracle@oracle12c ~]$ ls -l /oracle12c/app/oracle/product/12.1.0/dbhome_1/network/admin/
total 20
-rw-r—–. 1 oracle dba 343 Jul 7 03:00 listener.ora
drwxr-xr-x. 2 oracle dba 4096 Jun 25 11:56 samples
-rw-r–r–. 1 oracle dba 373 Oct 31 2013 shrept.lst
-rw-r—–. 1 oracle dba 208 Jul 7 03:00 sqlnet.ora
-rw-r—–. 1 oracle dba 420 Jul 17 09:12 tnsnames.ora
[oracle@oracle12c ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 20-JUL-2016 08:59:47
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 20-JUL-2016 08:59:34
Uptime 0 days 0 hr. 0 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle12c/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle12c/app/oracle/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/oracle12c/app/oracle/product/12.1.0/dbhome_1/admin/ora12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary…
Service “ora12c” has 1 instance(s).
Instance “ora12c”, status READY, has 1 handler(s) for this service…
Service “ora12cXDB” has 1 instance(s).
Instance “ora12c”, status READY, has 1 handler(s) for this service…
Service “pdb1” has 1 instance(s).
Instance “ora12c”, status READY, has 1 handler(s) for this service…
Service “pdb2” has 1 instance(s).
Instance “ora12c”, status READY, has 1 handler(s) for this service…
The command completed successfully
SQL> select name,con_Id from v$active_services order by 2;
NAME CON_ID
—————————————————————- ———-
ora12cXDB 1
SYS$BACKGROUND 1
SYS$USERS 1
ora12c 1
pdb1 3
pdb2 4
6 rows selected.
同样也只有一个spfile文件、控制文件快照、密码文件，监听程序会将所有的PDB对应的services注册到监听程序中，可以连接到cdb$root，也可以连接到指定的pdb
可以用os认证、ezconnect方式连接到cdb$root环境下：
SQL> conn / as sysdba
Connected.
SQL> show con_name;
CON_NAME
——————————
CDB$ROOT
SQL> conn sys/oracle@192.168.212.139:1521/ora12c as sysdba
Connected.
SQL> show con_name;
CON_NAME
——————————
CDB$ROOT
用ezconnect连接PDBS
SQL> conn sys/oracle@192.168.212.139:1521/pdb1 as sysdba
Connected.
SQL> show con_name;
CON_NAME
——————————
PDB1
SQL> conn sys/oracle@192.168.212.139:1521/pdb2 as sysdba
Connected.
SQL> show con_name;
CON_NAME
——————————
PDB2
创建pdb
SQL>conn / as sysdba
SQL> create pluggable database pdb3 admin user pdb_admin identified by password roles=(dba) file_name_convert=(‘/oracle12c/app/oracle/oradata/ora12c/pdbseed’,’/oracle12c/app/oracle/oradata/ora12c/pdb3′);
Pluggable database created.
SQL> select * from cdb_pdbs;
PDB_ID PDB_NAME DBID CON_UID GUID STATUS CREATION_SCN VSN LOGGING FOR FOR CON_ID
———- ———- ———- ———- ——————————– ——— ———— ———- ——— — — ———-
3 PDB1 2723517908 2723517908 37CB98E04474679CE0538BD4A8C07711 NORMAL 1741469 202375680 LOGGING NO NO 3
2 PDB$SEED 3962890709 3962890709 37CB7CA2D31D63D9E0538BD4A8C03C51 NORMAL 1594409 202375680 LOGGING NO NO 2
4 PDB2 369175303 369175303 37CB997769736856E0538BD4A8C08647 NORMAL 1742072 202375680 LOGGING NO NO 4
5 PDB3 2895601090 2895601090 3807F68447B4320DE0538BD4A8C05719 NEW 2000447 202375680 LOGGING NO NO 5
SQL> !lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 20-JUL-2016 09:20:58
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 20-JUL-2016 08:59:34
Uptime 0 days 0 hr. 21 min. 24 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle12c/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle12c/app/oracle/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my_wallet_directory=/oracle12c/app/oracle/product/12.1.0/dbhome_1/admin/ora12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary…
Service “ora12c” has 1 instance(s).
Instance “ora12c”, status READY, has 1 handler(s) for this service…
Service “ora12cXDB” has 1 instance(s).
Instance “ora12c”, status READY, has 1 handler(s) for this service…
Service “pdb1” has 1 instance(s).
Instance “ora12c”, status READY, has 1 handler(s) for this service…
Service “pdb2” has 1 instance(s).
Instance “ora12c”, status READY, has 1 handler(s) for this service…
Service “pdb3” has 1 instance(s).
Instance “ora12c”, status READY, has 1 handler(s) for this service…
The command completed successfully
SQL> select name,con_id from v$active_services;
NAME CON_ID
—————————————————————- ———-
pdb3 5
pdb2 4
pdb1 3
ora12cXDB 1
ora12c 1
SYS$BACKGROUND 1
SYS$USERS 1
7 rows selected.
创建新的pdb后，监听程序也会自动将这个pdb的services注册到监听程序中
需要注意的是在数据库重新启动后PDB默认是mounted状态，需要手动将PDB open，也可以直接写database级别的trigger，在数据库每次启动后将所有的pdb open
SQL> conn / as sysdba
Connected.
SQL> startup force;
ORACLE instance started.
Total System Global Area 1644167168 bytes
Fixed Size 2925024 bytes
Variable Size 1040191008 bytes
Database Buffers 587202560 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$pdbs;
OPEN_MODE
———-
READ ONLY
MOUNTED
MOUNTED
MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
修改pdb的名称
SQL> conn sys/oracle@192.168.212.139:1521/pdb3 as sysdba
Connected.
SQL> alter pluggable database pdb3 rename global_name to pdb_his;
alter pluggable database pdb3 rename global_name to pdb_his
*
ERROR at line 1:
ORA-65045: pluggable database not in a restricted mode
这里修改pdb的名称需要将对应的pdb open restricted模式下
SQL> alter pluggable database pdb3 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb3 open restricted;
Pluggable database altered.
SQL> alter pluggable database pdb3 rename global_name to pdb_his;
Pluggable database altered.
SQL> alter pluggable database close immediate;
Pluggable database altered.
SQL> alter pluggable database open;
Pluggable database altered.
创建用户：
SQL> conn / as sysdba
Connected.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB2 READ WRITE
PDB_HIS READ WRITE
在root中创建common user，用户必须以c##开头，在root中创建的用户会级联复制到所有的pdbs中，除了seed pdb
SQL> create user c##xiaoyu identified by xiaoyu container=all;
User created.
SQL> select username,common,con_id from cdb_users where username=’C##XIAOYU’;
USERNAME COM CON_ID
——————————————————————————————————————————– — ———-
C##XIAOYU YES 1
C##XIAOYU YES 5
C##XIAOYU YES 3
C##XIAOYU YES 4
common user登陆到cdb$root和pdbs中
SQL> conn c##xiaoyu/xiaoyu;
ERROR:
ORA-01045: user C##XIAOYU lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn c##xiaoyu/xiaoyu@192.168.212.139:1521/pdb1
ERROR:
ORA-01045: user C##XIAOYU lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
登陆cdb$root给common user授权
SQL> conn / as sysdba
Connected.
SQL> grant dba to c##xiaoyu;
Grant succeeded.
SQL> conn c##xiaoyu/xiaoyu
Connected.
common user在cdb$root中权限默认并不级联到pdb中，需要在pdb中重新授权
SQL> conn c##xiaoyu/xiaoyu@192.168.212.139:1521/pdb1
ERROR:
ORA-01045: user C##XIAOYU lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn sys/oracle@192.168.212.139:1521/pdb1 as sysdba
Connected.
SQL> grant dba to c##xiaoyu;
Grant succeeded.
SQL> conn c##xiaoyu/xiaoyu@192.168.212.139:1521/pdb1
Connected.
当然还是跟授权的SQL语句有关，比如在授权时添加container=all，该全选则会级联到所有的pdbs中
SQL> conn / as sysdba
Connected.
SQL> grant dba to c##xiaoyu container=all;
Grant succeeded.
SQL> conn c##xiaoyu/xiaoyu@192.168.212.139:1521/pdb2
Connected.
删除pdb
SQL> drop pluggable database pdb_his including datafiles;
drop pluggable database pdb_his including datafiles
*
ERROR at line 1:
ORA-65025: Pluggable database PDB_HIS is not closed on all instances.
SQL> alter pluggable database pdb_his close immediate;
Pluggable database altered.
SQL> drop pluggable database pdb_his including datafiles;
Pluggable database dropped.
这里简单先介绍下cdb、pdb的概念和常规管理，后续会陆续推出一些cdb、pdb的简单应用的文章！
转载请注明：我是IT » oracle 12c的cdb、pdb简介
