容器数据库(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
转载于:https://blog.51cto.com/13323775/2066364