Oracle 12c新特性 – 多租户

1.多租户概念

ORACLE 12C多租户环境,允许一个数据库容器(CDB)承载多个可插拔数据库(PDB)。CDB全称为Container Database,数据库容器,PDB全称为Pluggable Database,即可插拔数据库。CDB和PDB的关系如下:

1.1 多租户组件

  • ROOT组件Root又为容器库,名称为CDB$ROOT,包含了元数据和公用用户,例如Oracle提供的PL/SQL包的源代码,公用用户是每个容器中都可以使用的数据库用户。
  • SEED组件Seed又叫PDB$SEED,这个是创建PDBS数据库的模板,不能在Seed中添加或修改一个对象。一个CDB中有且只能有一个Seed。
  • PDBSCDB中可以有一个或多个PDBS,PDBS向后兼容,可以像以前在数据库中那样操作PDBS,这里指大多数常规操作。

2.容器库

2.1 查看PDB

当前的CDB,只有一个PDB$SEED模板,还没有任何可插拔库

SQL> select con_id,dbid,name,open_mode from v$pdbs;
    CON_ID       DBID NAME       OPEN_MODE
---------- ---------- ---------- ----------
         2 3749717834 PDB$SEED   READ ONLY

2.2 如何判断当前在CDB还是PDB

可以通过如下命令确认

SQL> show con_name con_id
CON_NAME
------------------------------
CDB$ROOT

CON_ID
--------
1

也可以通过如下命令确认

SQL> select sys_context('userenv', 'con_name') "Container DB" from dual;

Container DB
--------------------------------------------------------------------------------
CDB$ROOT

2.3 如何切换CDB或PDB

可以通过alter session命令进行切换,如下:

SQL> alter session set container = PDB$SEED;
Session altered.

SQL> SHOW CON_NAME
CON_NAME
------------------------------
PDB$SEED

SQL> alter session set container = CDB$ROOT;
Session altered.

SQL> show CON_NAME
CON_NAME
------------------------------
CDB$ROOT

2.4 设置内存

在多租户环境下,只需要对CDB分配内存即可,对PDBs来讲,内存资源是动态分配与管理。

SQL> alter system set sga_max_size=6g scope=spfile;
System altered.
SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 1552M
sga_target                           big integer 1552M
unified_audit_sga_queue_size         integer     1048576
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 6442450944 bytes
Fixed Size                  2938792 bytes
Variable Size            5603592280 bytes
Database Buffers          822083584 bytes
Redo Buffers               13836288 bytes
Database mounted.
Database opened.

PDB的SGA是共享的,无法单独修改PDB的SGA内存参数

SQL> ALTER SESSION SET CONTAINER = PDB1;
Session altered.
SQL> alter system set sga_target=800M scope=spfile;
alter system set sga_target=800M scope=spfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

2.5 数据文件

在还未有可插拔库的情况下,数据文件的情况如下:

SQL> select con_id, name from v$datafile order by con_id;

    CON_ID NAME
---------- ------------------------------------------------------------
         1 /data/oracle/oradata/t12c/system01.dbf
         1 /data/oracle/oradata/t12c/sysaux01.dbf
         1 /data/oracle/oradata/t12c/undotbs01.dbf
         1 /data/oracle/oradata/t12c/users01.dbf
         2 /data/oracle/oradata/t12c/pdbseed/system01.dbf
         2 /data/oracle/oradata/t12c/pdbseed/sysaux01.dbf

3. 创建PDB数据库

SQL> CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb_admin IDENTIFIED BY oracle
       FILE_NAME_CONVERT = ('/data/oracle/oradata/t12c/pdbseed/',
                            '/data/oracle/oradata/t12c/pdb1/');
Pluggable database created.
SQL> exec dbms_pdb.sync_pdb();
PL/SQL procedure successfully completed.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

4. 用户管理

在多租户环境下,公用用户可以在 CDB 和 PDB中同时存在,能够连接 ROOT 和 PDB 进行操作。而本地用户则只在特定的 PDB 中存在,也只能在特定的 PDB 中执行操作;在 PDB 中不能创建公用用户,而在 CDB 中(CDB$ROOT 中)同样不能创建本地用户。

4.1 公用用户

当前存在的公用用户有如下这些:

SQL> show con_name con_id
CON_NAME
------------------------------
CDB$ROOT
CON_ID
------------------------------
1
SQL> column username format a25
SQL> select username, account_status, common from dba_users;
USERNAME                  ACCOUNT_STATUS                   COM
------------------------- -------------------------------- ---
ORACLE_OCM                EXPIRED & LOCKED                 YES
OJVMSYS                   EXPIRED & LOCKED                 YES
SYSKM                     EXPIRED & LOCKED                 YES
XS$NULL                   EXPIRED & LOCKED                 YES
GSMCATUSER                EXPIRED & LOCKED                 YES
MDDATA                    EXPIRED & LOCKED                 YES
SYSBACKUP                 EXPIRED & LOCKED                 YES
DIP                       EXPIRED & LOCKED                 YES
SYSDG                     EXPIRED & LOCKED                 YES
APEX_PUBLIC_USER          EXPIRED & LOCKED                 YES
SPATIAL_CSW_ADMIN_USR     EXPIRED & LOCKED                 YES
SPATIAL_WFS_ADMIN_USR     EXPIRED & LOCKED                 YES
GSMUSER                   EXPIRED & LOCKED                 YES
AUDSYS                    EXPIRED & LOCKED                 YES
FLOWS_FILES               EXPIRED & LOCKED                 YES
DVF                       EXPIRED & LOCKED                 YES
MDSYS                     EXPIRED & LOCKED                 YES
ORDSYS                    EXPIRED & LOCKED                 YES
DBSNMP                    EXPIRED & LOCKED                 YES
WMSYS                     EXPIRED & LOCKED                 YES
APEX_040200               EXPIRED & LOCKED                 YES
APPQOSSYS                 EXPIRED & LOCKED                 YES
GSMADMIN_INTERNAL         EXPIRED & LOCKED                 YES
ORDDATA                   EXPIRED & LOCKED                 YES
CTXSYS                    EXPIRED & LOCKED                 YES
ANONYMOUS                 EXPIRED & LOCKED                 YES
XDB                       EXPIRED & LOCKED                 YES
ORDPLUGINS                EXPIRED & LOCKED                 YES
DVSYS                     EXPIRED & LOCKED                 YES
SI_INFORMTN_SCHEMA        EXPIRED & LOCKED                 YES
OLAPSYS                   EXPIRED & LOCKED                 YES
LBACSYS                   EXPIRED & LOCKED                 YES
OUTLN                     EXPIRED & LOCKED                 YES
SYSTEM                    OPEN                             YES
SYS                       OPEN                             YES
35 rows selected.

其中common=YES为公用用户,common=NO为PDB Local用户

4.2 创建公用用户

创建公用用户,需要使用C##或者c##作为该用户名的开头。如果不是以c##开头,则会报错。

另外创建公用用户,必须要在CDB$ROOT容器库中,如果在可插拔库中,也会报错

SQL> create user pei identified by oracle;
create user pei identified by oracle
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

正确的创建方式:

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
Session altered.
SQL> create user c##pei identified by oracle;
User created.

4.3 创建本地用户

本地用户是在可插拔库里创建

SQL> ALTER SESSION SET CONTAINER = PDB1;
Session altered.
SQL> select username, account_status, common from dba_users where common = 'NO';
USERNAME                  ACCOUNT_STATUS                   COM
------------------------- -------------------------------- ---
PDB_ADMIN                 OPEN                             NO
SQL> create user pei identified by oracle;
User created.
SQL> select username, account_status, common from dba_users where common = 'NO';
USERNAME                  ACCOUNT_STATUS                   COM
------------------------- -------------------------------- ---
PEI                       OPEN                             NO
PDB_ADMIN                 OPEN                             NO

5. 启动关闭PDB数据库

5.1 打开单个pdb

SQL> alter pluggable database pdb1 open;

5.2 打开所有pdb

SQL> alter pluggable database all open;

当使用这个命令打开所有可插拔数据库时,必须要在容器库内

SQL> alter pluggable database all open;
alter pluggable database all open
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
SQL> alter session set CONTAINER = CDB$ROOT;
Session altered.
SQL> alter pluggable database all open;
Pluggable database altered.

5.3 关闭单个pdb

SQL> alter pluggable database pdb1 close immediate;

5.4 关闭所有pdb

SQL> alter pluggable database all close immediate;

6. 插拔数据库

为什么可以插拔数据库在不同的的CDB间迁移?那是因为PDB包含了所有信息。

PDB包含的信息有如下:

1. 物理文件

2. 库对象数据字典(元数据)

3. 表、索引、物化视图、约束等自包含信息

4. 没有交叉数据

6.1 拔数据库

SQL> show pdbs


    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> alter pluggable database PDB1 close immediate;
Pluggable database altered.


SQL> show pdbs


    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED

SQL> alter pluggable database PDB1 unplug into '/home/oracle/pdb1_description.xml' ;
Pluggable database altered.


SQL> drop pluggable database PDB1 keep datafiles;
Pluggable database dropped.


SQL> show pdbs


    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO


$ ls -l /data/oracle/oradata/t12c/pdb1
total 839696
-rw-r-----. 1 oracle oinstall 597696512 Jan 10 20:43 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 262152192 Jan 10 20:43 system01.dbf

6.2 插数据库

检查是否可插入

begin
  if not dbms_pdb.check_plug_compatibility(pdb_descr_file =>
          ' /home/oracle/pdb1_description.xml ')
  then
    raise_application_error(-20000, 'PDB is not compatible to plug in');
  end if;
end;
/

插入数据库

SQL> create pluggable database pdb1
     using '/home/oracle/pdb1_description.xml' nocopy;
Pluggable database created.


SQL> alter pluggable database pdb1 open;
Pluggable database altered.


SQL> show pdbs


    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

7.客户端连接

监听信息如下:

$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 11-JAN-2017 15:08:45

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test1)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                09-JAN-2017 15:56:41
Uptime                    1 days 23 hr. 12 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /data/oracle/diag/tnslsnr/test1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test1)(PORT=1621)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "pdb1" has 1 instance(s).
  Instance "t12c", status READY, has 1 handler(s) for this service...
Service "t12c" has 1 instance(s).
  Instance "t12c", status READY, has 1 handler(s) for this service...
Service "t12cXDB" has 1 instance(s).
  Instance "t12c", status READY, has 1 handler(s) for this service...
The command completed successfully

从监听上的信息来看,pdb1是作为t12c实例的一个服务的,所以配置一个tns名如下:

PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test1)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )

使用sqlplus连接如下:

$ sqlplus "pei/oracle@PDB1"
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 15:15:09 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Wed Jan 11 2017 15:12:49 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from dual;
D
-
X

这里就没办法使用SID了,因为如果使用SID,会连到t12c的CDB,导致无法连接上PDB的本地用户,如下:
tns以SID连接t12c

T12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test1)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = t12c)
    )
  )

连接pei用户会报错,因为连接到CDB,CDB没有pei这个用户

$ sqlplus "pei/oracle@IT12C"
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 15:12:52 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: 

关于紫砂壶

感悟技术人生
此条目发表在Oracle基础知识分类目录,贴了标签。将固定链接加入收藏夹。