• Oracle 12c中的cdb、pdb概念介绍
  • 发布于 2个月前
  • 154 热度
    0 评论
racle 12c引入了多租户环境,允许一个数据库容器CDB承载多个可插拔数据库PDB,CDB全称为Container Database翻译为数据库容器,PDB全称为Pluggable Database翻译为可插拔数据库,在oracle 12c之前实例与数据库是一对一或者多对一的关系,而在oracle 12c之后实例与数据库可以实现一对多的关系,也就是一个CDB,多个PDB,这个其实如果大家有接触过mysql、sqlserver这类数据库,对于这个概念就很好理解了,摘要下官方文档关于CDB和PDB的关系图:

CDB组成:
1.root组件:也就是CDB$ROOT,存储着ORACLE提供的元数据库和common user,元数据比如oracle提供的pl/sql包的源代码,2.common user是指每个容器中都存在的用户
3.SEED组件:Seed又叫做PDB$SEED,这个是创建PDBS数据库的模板,不能在Seed中添加或者修改对应,这个基本类似于sql server的module数据库
4.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的简单应用的文章!

用户评论