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”

上一篇:利用Python 脚本生成 .h5 文件 代码


下一篇:如何利用数据优化运营?