容器数据库(CDB,multitenant container database)和可热插拔数据库(pluggable databases,PDB)。
DSC0000.jpg (45.67 KB, 下载次数: 0)
2018-9-5 13:33 上传
官方文档: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
DSC0001.jpg (47.21 KB, 下载次数: 0)
2018-9-5 13:33 上传
选择高级模式
DSC0002.jpg (62.88 KB, 下载次数: 0)
2018-9-5 13:33 上传
选择使用的模板
general purpose or transaction processing 事务处理
custom database 自定义
data warehouse 数据中心
DSC0003.jpg (63.54 KB, 下载次数: 0)
2018-9-5 13:33 上传
CDB
DSC0004.jpg (63.03 KB, 下载次数: 0)
2018-9-5 13:33 上传
EM
DSC0005.jpg (59.13 KB, 下载次数: 0)
2018-9-5 13:33 上传
设置数据库管理员密码
DSC0006.jpg (64.11 KB, 下载次数: 0)
2018-9-5 13:33 上传
设置监听(这里没设置,用netca设置的)
DSC0007.jpg (62.65 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC0008.jpg (80.15 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC0009.jpg (70.55 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00010.jpg (61.51 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00011.jpg (69.98 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00012.jpg (64.91 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00013.jpg (77.18 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00014.jpg (76.01 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00015.jpg (63.06 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00016.jpg (78.42 KB, 下载次数: 0)
2018-9-5 13:33 上传
创建监听
netca
DSC00017.jpg (87.41 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00018.jpg (80.07 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00019.jpg (72.7 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00020.jpg (77.95 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00021.jpg (78.69 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00022.jpg (72.94 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00023.jpg (73.68 KB, 下载次数: 0)
2018-9-5 13:33 上传
本地
DSC00024.jpg (86.86 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00025.jpg (81.45 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00026.jpg (75 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00027.jpg (72.75 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00028.jpg (78.34 KB, 下载次数: 0)
2018-9-5 13:33 上传
这里不测试连接,到finish结束
创建PDB
[oracle@oracle ~]$ ORACLE_SID=CDB
[oracle@oracle ~]$ sqlplus / as sysdba
DSC00029.jpg (29.06 KB, 下载次数: 0)
2018-9-5 13:33 上传
查看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>
create pluggable database orcl admin user sde> *
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified
查看报错信息
[oracle@oracle ~]$ oerr ora 65016
DSC00030.jpg (44.74 KB, 下载次数: 0)
2018-9-5 13:33 上传
处理方法一、
指定file_name_convert
SQL> create pluggable database orcl admin user sde>
/data/oracle/app/oradata/CDB/orcl/是PDB数据存放路径,需要手动创建
[oracle@oracle oradata]$ mkdir -pv /data/oracle/app/oradata/CDB/orcl/
DSC00031.jpg (19.76 KB, 下载次数: 0)
2018-9-5 13:33 上传
处理方法二、
使用参数db_create_file_dest
SQL>>
System>
SQL> create pluggable database vms3devdb admin user c5web> 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;
DSC00032.jpg (33.68 KB, 下载次数: 0)
2018-9-5 13:33 上传
查看监听状态
DSC00033.jpg (91.57 KB, 下载次数: 0)
2018-9-5 13:33 上传
使用PDB
启动PDB
#启动单个PDB
SQL>>
Pluggable database> #查看状态
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
PDB$SEED READ ONLY
ORCL READ WRITE
VMS3DEVDB MOUNTED
#启动所有PDB
SQL> >
Pluggable database> SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
PDB$SEED READ ONLY
ORCL READ WRITE
VMS3DEVDB READ WRITE
关闭PDB
#关闭单个PDB
SQL>>
Pluggable database> SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
PDB$SEED READ ONLY
ORCL MOUNTED
VMS3DEVDB READ WRITE
SQL> >
Pluggable database> #关闭所有的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> >
Pluggable database>
#查看所有PDB
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE ------------------------------ ----------
PDB$SEED READ ONLY
ORCL MOUNTED
VMS3DEVDB READ WRITE
#开启orcl的RES
SQL> >
Pluggable database>
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>>
Session>
# 修改orcl名称为orcl_test
SQL>>
Pluggable database>
#查看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> Warning: You are no longer connected to ORACLE.
这是因为没有创建本地监听
创建本地监听
[oracle@oracle ~]$ netca
DSC00034.jpg (85.2 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00035.jpg (78.29 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00036.jpg (75.13 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00037.jpg (74.01 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00038.jpg (80.15 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00039.jpg (77.31 KB, 下载次数: 0)
2018-9-5 13:33 上传
DSC00040.jpg (75.97 KB, 下载次数: 0)
2018-9-5 13:33 上传
另外个也是这样创建
连接PDB
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus:>Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition>
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'>
Tablespace created.
SQL> create user 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>>
Pluggable database>
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