Oracle多租戶特性的常用操作
本文描述了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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 20C 多租戶_新特性Oracle
- Oracle 18c新特性：多租戶艦隊 CDB FleetOracle
- Oracle 18c新特性詳解-多租戶專題Oracle
- 多租戶
- Oracle 12c多租戶特性詳解：PDB 的建立、克隆與維護Oracle
- Oracle 12c多租戶特性詳解：PDB 的出與入 InAndOutOracleNaN
- Oracle 12c多租戶特性詳解：PDB 的備份與恢復Oracle
- Part II 配置和管理多租戶環境概述-Oracle多租戶管理員指南Oracle
- Oracle多租戶架構優勢分析Oracle架構
- OceanBase學習之路13|體驗多租戶特性
- 【多租戶技術】
- 【Dataguard】Oracle多租戶環境對Dataguard的影響Oracle
- Oracle多租戶管理員指南-CDB共性概念03Oracle
- 12C多租戶關於CDB、PDB的常用SQL語句SQL
- 3.3.2 多租戶環境的工具
- 詳解ABP框架的多租戶框架
- Part III PDB建立概述-Oracle多租戶管理員指南Oracle
- oracle 12c 多租戶體系結構概念Oracle
- Oracle Database 12cR2多租戶權威指南OracleDatabase
- Oracle 12c 多租戶 CDB 與 PDB 備份Oracle
- 如何理解多租戶架構？架構
- HBase多租戶-Namespace Quota管理namespace
- 3.3.1 多租戶環境的任務
- 多租戶系統的建立目標
- Oracle 20C 多租戶_體系結構介紹Oracle
- Oracle 12c系列(一)｜多租戶容器資料庫Oracle資料庫
- oracle12之 多租戶容器資料庫架構Oracle資料庫架構
- 多租戶商城系統解說
- 基於 SQL 解析的 JPA 多租戶方案SQL
- Oracle多租戶管理員指南-體系結構介紹01Oracle
- Part II 建立和配置CDB-Oracle多租戶管理員指南Oracle
- Part I CDB中服務的概述04-Oracle多租戶管理員指南Oracle
- Oracle 12c多租戶的一些日常簡單維護Oracle
- Oracle 12c多租戶特性詳解：全域性使用者與本地使用者的原理與維護Oracle
- 2 多租戶體系結構概述
- 實現saas多租戶方案比較
- 一種Django多租戶解決方案Django
- 多租戶軟體開發架構架構