oracle多租户运维,oracle12c多租户使用测试

创建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.

参考：