Oracle Database 12C 学习之多租户（连载三）

我们先切换回根容器：

SYS@ora12c> alter session set container=CDB$ROOT;

Session altered.




接下来我们来创建用户及角色：

在12c中，如果我们还是按照11g以及以前版本中的命令来创建用户和角色，那是要报错的。因为12c中新引入了common_user_prefix参数，也就是说，你创建用户或者角色时，需要指定名称前缀，默认为c##。对于os认证的用户，也有相关的约束，参数为os_authent_prefix。

我们可以将该参数做一下处理。

SYS@ora12c> alter system set common_user_prefix='' scope=spfile;

System altered.

SYS@ora12c> startup force;

ORACLE instance started.

Total System Global Area 838860800 bytes

Fixed Size  2929936 bytes

Variable Size  687868656 bytes

Database Buffers  142606336 bytes

Redo Buffers  5455872 bytes

Database mounted.

Database opened.

SYS@ora12c> show parameter common_user_prefix;

NAME  TYPE  VALUE

------------------------------------ ----------- ------------------------------

common_user_prefix  string



SYS@ora12c> create user test_u1 identified by test container=current;

create user test_u1 identified by test container=current

*

ERROR at line 1:

ORA-65049: creation of local user or role is not allowed in CDB$ROOT

--在根容器中，不能创建本地用户。

SYS@ora12c> create user test_u1 identified by test container=all;

User created.

切换到种子容器试试：

SYS@ora12c> alter session set container=PDB$SEED;

Session altered.

SYS@ora12c> create user test_u3 identified by test container=current;

create user test_u3 identified by test container=current

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database or pluggable database open for read-only access

SYS@ora12c> alter session set container=ora12c_pdb1;

Session altered.

SYS@ora12c> create user test_u3 identified by test container=current;

create user test_u3 identified by test container=current

*

ERROR at line 1:

ORA-01109: database not open

SYS@ora12c> startup;

Pluggable Database opened.

SYS@ora12c> create user test_u3 identified by test container=current;

User created.

SYS@ora12c> create user test_u3 identified by test container=all;

create user test_u3 identified by test container=all

*

ERROR at line 1:

ORA-65050: Common DDLs only allowed in CDB$ROOT

也就是说，在根容器中创建用户，container默认为all；在某一可插拔数据库中创建用户，container默认为current；种子容器中不允许创建用户。

角色也是如此。

CDB的管理操作，我们不再多说，想了解更多细节的话，可以查阅官方文档管理员手册的第40、41两章。

接下来，我们来看看PDB的创建与管理。

oracle提供了多种创建或者克隆PDB的工具，比如说create pluggable database语句，比如说DBCA，再比如说EMCC（Enterprise Manager Cloud Control）。关于如何使用EMCC图形化的方式创建PDB，可以参考管理员手册的第39章。我们这里着重关注如何使用命令行来创建或者复制PDB。

通过create pluggable database语句，可以使用如下资源创建PDB:

1，种子容器；

2，已经存在的PDB（本地和远程的均可）；

3，非CDB数据库；

4，被拔出的PDB。

如果使用DBCA，则可以使用如下资源创建PDB:

1，种子容器；

2，RMAN备份；

3，已被拔出的PDB。

先看如何使用create pluggable database语句来创建PDB：

使用克隆种子容器的方式：

SYS@ora12c> show pdbs;

CON_ID CON_NAME  OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

 3 ORA12C_PDB1  READ WRITE YES

SYS@ora12c> alter session set container=CDB$ROOT;

Session altered.

SYS@ora12c> show pdbs;

CON_ID CON_NAME  OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

 2 PDB$SEED  READ ONLY NO

 3 ORA12C_PDB1  READ WRITE YES

--注意，此时种子容器必须处于read only状态。

SYS@ora12c> create pluggable database oracdb_pdb2

admin user pdb2_admin identified by admin

file_name_convert = ('/u01/oracle/oradata/ora12c/pdbseed',

'/u01/oracle/oradata/ora12c/cdb/pdb2');

Pluggable database created.

--需要先确定种子容器的数据文件存放路径，

--以及创建新的可插拔数据库的数据文件存放目录。

--如果使用OMF，则不需要设置file_name_convert参数。

SYS@ora12c> show pdbs;

CON_ID CON_NAME  OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

 2 PDB$SEED  READ ONLY NO

 3 ORA12C_PDB1  READ WRITE YES

 4 ORACDB_PDB2  MOUNTED

SYS@ora12c> alter pluggable database ORACDB_PDB2 open;

Pluggable database altered.

SYS@ora12c> show pdbs;

CON_ID CON_NAME  OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

 2 PDB$SEED  READ ONLY NO

 3 ORA12C_PDB1  READ WRITE YES

 4 ORACDB_PDB2  READ WRITE NO

SYS@ora12c> conn pdb2_admin/admin@ORACDB_PDB2;

ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

Warning: You are no longer connected to ORACLE.

@>

此时需要配置下tns了。

vi $ORACLE_HOME/network/admin/tnsnames.ora

添加如下内容：

ORACDB_PDB2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ora12)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = oracdb_pdb2)

)

)

再看一下监听的状态：

[oracle@ora12 admin]$ lsnrctl services;

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-DEC-2015 10:14:04

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora12)(PORT=1521)))

Services Summary...

Service "ora12c" has 1 instance(s).

Instance "ora12c", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:1 refused:0 state:ready

LOCAL SERVER

Service "ora12cXDB" has 1 instance(s).

Instance "ora12c", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: ora12.oracle.com, pid: 4585>

(ADDRESS=(PROTOCOL=tcp)(HOST=ora12)(PORT=25789))

Service "ora12c_pdb1" has 1 instance(s).

Instance "ora12c", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:1 refused:0 state:ready

LOCAL SERVER

Service "oracdb_pdb2" has 1 instance(s).

Instance "ora12c", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:1 refused:0 state:ready

LOCAL SERVER

The command completed successfully

然后再测试一下：

SYS@ora12g> conn pdb2_admin/admin@ORACDB_PDB2;

Connected.

ok，好使了。

本文来自云栖社区合作伙伴“DBGEEK”