oracle12c多租户使用测试

容器数据库（CDB,multitenant container database）和可热插拔数据库（pluggable databases，PDB）。 官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/introduction-to-the-multitenant-architecture.html#GUID-1CB3617F-8B18-441D-96B3-3E7767A5ED1D

创建CDB

[oracle@oracle ~]$ dbca 选择高级模式 选择使用的模板 general purpose or transaction processing 事务处理 custom database 自定义 data warehouse 数据中心 CDB EM 设置数据库管理员密码 设置监听（这里没设置，用netca设置的）

创建监听

netca

本地

这里不测试连接，到finish结束

创建PDB

[oracle@oracle ~]$ ORACLE_SID=CDB

[oracle@oracle ~]$ sqlplus / as sysdba

查看CDB名和ID

SQL> show con_name

CON_NAME

CDB$ROOT SQL> show con_id

CON_ID

1

连接CDB，查看CDB名和ID

SQL> conn sys/ycig1234@CDB as sysdba; Connected. SQL> show con_name

CON_NAME

CDB$ROOT SQL> show con_id

CON_ID

1

查看可用服务

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

NAME CON_ID

---

CDB 1 CDBXDB 1 SYS$BACKGROUND 1 SYS$USERS 1

创建PDB

SQL> conn / as sysdba Connected. SQL> create pluggable database orcl admin user sde identified by sde roles=(DBA); create pluggable database orcl admin user sde identified by sde roles=(DBA) * ERROR at line 1: ORA-65016: FILE_NAME_CONVERT must be specified 查看报错信息 [oracle@oracle ~]$ oerr ora 65016 处理方法一、 指定file_name_convert

SQL> create pluggable database orcl admin user sde identified by sde file_name_convert=('/data/oracle/app/oradata/CDB/pdbseed/','/data/oracle/app/oradata/CDB/orcl/');

/data/oracle/app/oradata/CDB/orcl/是PDB数据存放路径，需要手动创建 [oracle@oracle oradata]$ mkdir -pv /data/oracle/app/oradata/CDB/orcl/ 处理方法二、 使用参数db_create_file_dest SQL> alter system set db_create_file_dest='/data/oracle/app/oradata/';

System altered. SQL> create pluggable database vms3devdb admin user c5web identified by c5web roles=(DBA);

Pluggable database created.

查看服务

SQL> select con_id,name,open_mode,restricted from v$pdbs order by 1;
SQL>  select name,con_id from v$active_services order by 1;

查看监听状态

使用PDB

启动PDB

#启动单个PDB SQL> alter pluggable database orcl open;

Pluggable database altered. #查看状态 SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE

---

PDB$SEED READ ONLY ORCL READ WRITE VMS3DEVDB MOUNTED #启动所有PDB

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE

---

PDB$SEED READ ONLY ORCL READ WRITE VMS3DEVDB READ WRITE

关闭PDB

#关闭单个PDB SQL> alter pluggable database orcl close immediate;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE

---

PDB$SEED READ ONLY ORCL MOUNTED VMS3DEVDB READ WRITE

SQL> alter pluggable database all close immediate;

Pluggable database altered. #关闭所有的PDB SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE

---

PDB$SEED READ ONLY ORCL MOUNTED VMS3DEVDB MOUNTED

创建触发器启动PDB

SQL> CREATE OR REPLACE TRIGGER open_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/

PDB重命名

#查看所有PDB

SQL> show pdbs

CON_ID CON_NAME			  OPEN MODE  RESTRICTED

---

 2 PDB$SEED			  READ ONLY  NO
 3 ORCL 			  READ WRITE NO
 4 VMS3DEVDB			  READ WRITE NO
    #关闭orcl

SQL> alter pluggable database orcl close immediate;

Pluggable database altered.
#查看所有PDB

SQL> select name,open_mode from v$pdbs;

   NAME			       OPEN_MODE
   ------------------------------ ----------
  PDB$SEED		       READ ONLY
     ORCL			       MOUNTED
  VMS3DEVDB		       READ WRITE

#开启orcl的RES SQL> alter pluggable database orcl open restricted;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
 2 PDB$SEED			  READ ONLY  NO
 3 ORCL 			  READ WRITE YES
 4 VMS3DEVDB			  READ WRITE NO
#使用orcl

SQL> alter session set container=orcl;

Session altered.
# 修改orcl名称为orcl_test

SQL> alter pluggable database orcl rename global_name to orcl_test;

Pluggable database altered.
#查看PDB

SQL> show pdbs

CON_ID CON_NAME			  OPEN MODE  RESTRICTED

---

 3 ORCL_TEST			  READ WRITE YES

SQL> conn / as sysdba

 Connected.

SQL> show pdbs

CON_ID CON_NAME			  OPEN MODE  RESTRICTED

---

 2 PDB$SEED			  READ ONLY  NO
 3 ORCL_TEST			  READ WRITE YES
 4 VMS3DEVDB			  READ WRITE NO

连接PDB

SQL> conn sys/ycig1234@127.0.0.1:1521/orcl as sysdba ERROR: ORA-12154: TNS:could not resolve the connect identifier specified

Warning: You are no longer connected to ORACLE. 这是因为没有创建本地监听

创建本地监听 [oracle@oracle ~]$ netca 另外个也是这样创建

连接PDB

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jan 30 01:03:03 2018
 Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application     Testing options

SQL> show pdbs;

CON_ID CON_NAME			  OPEN MODE  RESTRICTED

---

 2 PDB$SEED			  READ ONLY  NO
 3 ORCL_TEST			  READ WRITE NO
 4 VMS3DEVDB			  READ WRITE NO

SQL> conn sys/ycig1234@orcl_test as sysdba

 Connected.

存储管理

SQL> conn / as sysdba

	Connected.

CDB存储管理

SQL> create tablespace CDBdata;

Tablespace created.

PDB存储管理

SQL> conn sys/ycig1234@orcl_test as sysdba

Connected.

SQL> create tablespace TEST datafile '/data/oracle/app/oradata/CDB/datafile/TEST1.dbf' size 1g autoextend on;

Tablespace created.		

SQL> create user test identified by test default tablespace TEST;

User created.		

管理方法和单实例一样

删除PDB

SQL> show pdbs

CON_ID CON_NAME			  OPEN MODE  RESTRICTED

---

 2 PDB$SEED			  READ ONLY  NO
 3 ORCL 			  READ WRITE NO
 4 VMS3DEVDB			  READ WRITE NO

SQL> alter pluggable database all close immediate;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME			  OPEN MODE  RESTRICTED

---

 2 PDB$SEED			  READ ONLY  NO
 3 ORCL 			  MOUNTED
 4 VMS3DEVDB			  MOUNTED

SQL> drop pluggable database ORCL including datafiles;

Pluggable database dropped.

参考：

Oracle多租户特性的常用操作: http://blog.itpub.net/29439655/viewspace-1345956/ 官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/introduction-to-the-multitenant-architecture.html#GUID-1CB3617F-8B18-441D-96B3-3E7767A5ED1D