本文描述了Oracle多租户特性环境下关于CDBs和PDBs的常见操作。
CDBs表示容器数据库(Container Database)、PDBs表示可插拔数据库(Pluggable Database)。
目录
一、如何连接到CDB和PDB环境要求:
二、如何从种子PDB创建一个新的PDB
三、CDBs和PDBs管理--启动、关闭、重命名
四、CDBs和PDBs的存储管理--数据表空间和临时表空间
五、PDBs的安全管理
六、如何删除PDBs
Oracle 12C数据库环境及已经创建了一个PDB和CDB。
同时本文的相关环境变量如下:
TNS Listener port: 1521
Container databases:
SID: ora12c
Pluggable databases (in ora12c):
pdb12c1
pdb12c2
一、如何连接到CDB和PDB
1、登录到容器库ORA12C环境
[oracle@ol6 ~]$ sqlplus sys/oracle@localhost:1521/ora12c as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 24 17:43:34 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show con_id
CON_ID
------------------------------
1
2、登录到OS最高管理员
SQL> connect / as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show con_id
CON_ID
------------------------------
1
3、查看数据库中可用的服务
SQL> select name,con_id from v$active_services order by 1;
NAME CON_ID
---------------------------------------------------------------- ----------
SYS$BACKGROUND 1
SYS$USERS 1
ora12c 1
ora12cXDB 1
pdb12c1 5
pdb12c2 4
6 rows selected.
4、连接到插拔数据库PDB12C1
SQL> connect sys/oracle@localhost:1521/pdb12c1 as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
PDB12C1
SQL> show con_id
CON_ID
------------------------------
5
二、如何从种子PDB创建一个新的PDB
这一部分是如何从种子PDB创建一个新的PDB。
1、创建PDB
[oracle@ol6 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 24 17:53:21 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create pluggable database pdb12c3 admin user pdb12c3_admin identified by oracle roles=(DBA);
Pluggable database created.
2、检查PDB状态
SQL> set linesize 1000
SQL> select pdb_name,status from cdb_pdbs;
PDB_NAME STATUS
------------- ---------
PDB12C3 NEW
PDB$SEED NORMAL
PDB12C2 NORMAL
PDB12C1 NORMAL
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------ ----------
PDB$SEED READ ONLY
PDB12C3 MOUNTED
PDB12C2 READ WRITE
PDB12C1 READ WRITE
SQL> select name,con_id from v$active_services order by 1;
NAME CON_ID
----------------- ----------
SYS$BACKGROUND 1
SYS$USERS 1
ora12c 1
ora12cXDB 1
pdb12c1 5
pdb12c2 4
pdb12c3 3
7 rows selected.
3、查看新PDB的数据文件目录
SQL> select name from v$datafile where con_id=3;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA12C/0898C60BC4372029E053DE00A8C08956/datafile/o1_mf_system_b7601w86_.dbf
/u01/app/oracle/oradata/ORA12C/0898C60BC4372029E053DE00A8C08956/datafile/o1_mf_sysaux_b7601w8b_.dbf
三、CDBs和PDBs管理--启动、关闭、重命名
下面主要介绍如何关闭与启动CDBs和PDBs。
1、管理CDB容器数据库
[oracle@ol6 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 25 10:23:33 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
首先会将所有的PDBs,然后卸载控制文件,最后关闭数据库实例。
SQL> startup
ORACLE instance started.
Total System Global Area 3321888768 bytes
Fixed Size 2929840 bytes
Variable Size 1694501712 bytes
Database Buffers 1073741824 bytes
Redo Buffers 13844480 bytes
In-Memory Area 536870912 bytes
Database mounted.
Database opened.
启动过程中,首先启动实例,然后加载控制文件,最后打开root容器。启动的用户需要拥有SYSDBA或者SYSBACKUP权限。这个启动过程不会启动PDB,可以设置成自动启动PDB。
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB12C3 MOUNTED
PDB12C2 MOUNTED
PDB12C1 MOUNTED
2、管理PDB插拔数据库
SQL> alter pluggable database pdb12c1 open;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB12C3 MOUNTED
PDB12C2 MOUNTED
PDB12C1 READ WRITE
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB12C3 READ WRITE
PDB12C2 READ WRITE
PDB12C1 READ WRITE
SQL> alter pluggable database pdb12c1 close immediate;
Pluggable database altered
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB12C3 READ WRITE
PDB12C2 READ WRITE
PDB12C1 MOUNTED
SQL> alter pluggable database all close immediate;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB12C3 MOUNTED
PDB12C2 MOUNTED
PDB12C1 MOUNTED
可以通过创建触发器,实现CDB启动后PDB自动启动。触发器内容如下:
create or replace trigger Sys.After_Startup after startup on database
begin
execute immediate 'alter pluggable database all open';
end After_Startup;
/
shutdown immediate
startup
select name, open_mode from v$pdbs;
3、重命名PDB。
将PDB置于Restricted模式状态。
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB12C3 READ WRITE NO
4 PDB12C2 READ WRITE NO
5 PDB12C1 READ WRITE NO
SQL> alter pluggable database pdb12c1 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb12c1 open restricted;
Pluggable database altered.
SQL> select name,restricted from v$pdbs;
NAME RES
------------------------------ ---
PDB$SEED NO
PDB12C3 NO
PDB12C2 NO
PDB12C1 YES
SQL> alter pluggable database pdb12c1 rename global_name to pdb12c1_his;
alter pluggable database pdb12c1 rename global_name to pdb12c1_his
*
ERROR at line 1:
ORA-65046: operation not allowed from outside a pluggable database
SQL> alter session set container=pdb12c1;
Session altered.
SQL> alter pluggable database pdb12c1 rename global_name to pdb12c1_his;
Pluggable database altered.
SQL> alter pluggable database close immediate;
Pluggable database altered.
SQL> alter pluggable database open;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB12C1_HIS READ WRITE
这样pdb12c1成功地重命名为pdb12c1_his。
四、CDBs和PDBs的存储管理--数据表空间和临时表空间
每个CDB的数据分别存储于各自的数据文件中。可以创建PDB的数据文件,也可以创建CDB的数据文件。五、PDBs的安全管理
1、创建CDB数据文件
SQL> conn / as sysdba
Connected.
SQL> create tablespace cdata;
Tablespace created.
SQL> create temporary tablespace temp_root;
Tablespace created.
SQL> select tablespace_name,con_id from cdb_tablespaces where con_id=1;
TABLESPACE_NAME CON_ID
------------------------------ ----------
SYSTEM 1
SYSAUX 1
UNDOTBS1 1
TEMP 1
USERS 1
CDATA 1
TEMP_ROOT 1
7 rows selected.
2、创建PDB数据文件
SQL> create tablespace ldata;
Tablespace created.
SQL> create temporary tablespace temp_pdb12c2;
Tablespace created.
SQL> select tablespace_name ,con_id from cdb_tablespaces;
TABLESPACE_NAME CON_ID
------------------------------ ----------
SYSTEM 4
SYSAUX 4
TEMP 4
USERS 4
LDATA 4
TEMP_PDB12C2 4
6 rows selected.
1、用户的创建六、如何删除PDBs
每个CDB都有Common 和Local用户。对于这些用户,必须被赋予相应的权限才能被连接。Common用户在Root下创建并且自动被复制到每个PDB中。Common用户可以连接到任何一个PDB。Common用户的名称必须由c##开头。
Local用户是每个PDB各个创建的。Local用户只能连接到各个的PDB。Local用户对于同一个容器中的其它PDB不可见的。
[oracle@ol6 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 25 13:34:20 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create user c##1 identified by oracle container=all;
User created.
SQL> show con_id
CON_ID
------------------------------
1
SQL> set linesize 1000
SQL> select username,common,con_id from cdb_users where username like 'C##%';
USERNAME COM CON_ID
---------- --- ----------
C##1 YES 3
C##1 YES 5
C##1 YES 1
C##1 YES 4
SQL> conn c##1/oracle@localhost:1521/pdb12c2;
ERROR:
ORA-01045: user C##1 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn c##1/oracle@localhost:1521/pdb12c3;
ERROR:
ORA-01045: user C##1 lacks CREATE SESSION privilege; logon denied
SQL> connect system/oracle@localhost:1521/pdb12c1_his
Connected.
SQL> create user hr identified by oracle;
User created.
SQL> select username,common,con_id from cdb_users where username='HR';
USERNAME COM CON_ID
---------- --- ----------
HR NO 5
SQL> connect hr/oracle@localhost:1521/pdb12c2;
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect hr/oracle@localhost:1521/pdb12c1_his;
ERROR:
ORA-01045: user HR lacks CREATE SESSION privilege; logon denied
2、角色的创建
角色分为两种,一种是Common角色,一种是Local角色。Common角色是可以公共的。Local角色只能由单个PDB使用。
SQL> connect / as sysdba
Connected.
SQL> create role c##r1 container=all;
Role created.
SQL> set linesize 1000
SQL> select role,common,con_id from cdb_roles where role='C##R1';
ROLE COM CON_ID
----------- --- ----------
C##R1 YES 1
C##R1 YES 4
C##R1 YES 5
C##R1 YES 3
SQL> connect system/oracle@localhost:1521/pdb12c2
Connected.
SQL> create role hr_manager;
Role created.
SQL> select role,common,con_id from cdb_roles where role='HR_MANAGER';
ROLE COM CON_ID
----------------------------------------------------- --- ----------
HR_MANAGER NO 4
SQL> create role c##r2 container=all;
create role c##r2 container=all
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT
3、权限分配
[oracle@ol6 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 25 14:09:19 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> grant create session to c##1 container=all;
Grant succeeded.
SQL> conn c##1/oracle@localhost:1521/pdb12c2
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
SQL> conn c##1/oracle@localhost:1521/pdb12c3
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
SQL> connect system/oracle@localhost:1521/pdb12c1_his
Connected.
SQL> grant create session to hr container=all;
grant create session to hr container=all
*
ERROR at line 1:
ORA-65030: one may not grant a Common Privilege to a Local User or Role
SQL> grant create session to hr;
Grant succeeded.
SQL> conn hr/oracle@localhost:1521/pdb12c1_his
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
SQL> conn hr/oracle@localhost:1521/pdb12c2
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect / as sysdba
Connected.
SQL> alter pluggable database all close immediate;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB12C3 MOUNTED
PDB12C2 MOUNTED
PDB12C1_HIS MOUNTED
SQL> drop pluggable database pdb12c2 including datafiles;
Pluggable database dropped.
SQL> select name from v$pdbs;
NAME
------------------------------
PDB$SEED
PDB12C3
PDB12C1_HIS
SQL>
SQL> drop user c##1;
User dropped.
SQL> drop role c##r1;
Role dropped.
SQL> drop tablespace cdata including contents;
Tablespace dropped.
SQL> drop tablespace temp_root including contents;
SQL> exit
Tablespace dropped.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ol6 ~]$
Reference:
1、 Performing Basic Tasks in Oracle Multitenant
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29439655/viewspace-1345956/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29439655/viewspace-1345956/