Oracle多租戶特性的常用操作

abraham_dba_2013發表於2014-11-25
本文描述了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的資料檔案。
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.
五、PDBs的安全管理
1、使用者的建立
每個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.

六、如何刪除PDBs
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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章