1.前言
CDB與PDB是Oracle 12C引入的新特性,在ORACLE 12C資料庫引入的多租使用者環境(Multitenant Environment)中,允許一個數據庫容器(CDB)承載多個可插拔資料庫(PDB)。CDB全稱為ContainerDatabase,中文翻譯為資料庫容器,PDB全稱為Pluggable Database,即可插拔資料庫。
在ORACLE 12C之前,例項與資料庫是一對一或多對一關係(RAC):即一個例項只能與一個數據庫相關聯,資料庫可以被多個例項所載入。而例項與資料庫不可能是一對多的關係。
當進入ORACLE 12C後,例項與資料庫可以是一對多的關係。
關於CDB與PDB的關係圖
12c中 oracle引入了容器資料庫 CDB(container database),和可插拔資料庫 PDB(pluggable database)。oracle 將CDB看成一個容器,用來存放資料庫。
在CDB中可以有多個PDB,其中存在一個root根容器(PDB$ROOT)、一個種子容器(PDB$SEED)和多個PDBS。
所有的PDB共用一個硬體系統資源、sga和pga、redo、臨時段、控制檔案、引數檔案、還原段(還可對每個PDB單獨指定)。
PDB$ROOT:根容器用來做所有容器的跟,用來對每個PDB進行統一管理,sqlplus / as sysdba連線進來預設是連線的根容器,需要切換到其他的PDB容器才可以對單獨的PDB操作。其中有 system資料檔案、sysaux資料檔案、(undo資料檔案、temp資料檔案、redo、控制檔案)。一般不存放生產資料檔案
PDB$SEED:種子容器作為插入PDB的模板而存在,每個CDB都有一個種子容器,且不可對其中物件進行修改。其中有 system資料檔案、sysaux資料檔案、其他資料檔案。
PDB:新插入容器,該容器用來存放資料庫。其中有 system資料檔案、sysaux資料檔案、其他資料檔案。12c中可以插入多個容器進行統一管理,來減少BDA的工作量。其中的資料庫可以插入或拔出。
使用者:12c中PDB$ROOT中的普通目錄可以通過許可權分配來訪問一個或多個指定的PDB容器,最大許可權使用者是sysdba。其中PDB也可單獨建立普通使用者來管理該容器的資料庫。
PDB資源管理:12c中將多個數據庫執行在一個硬體資源上,CDB效能上得到優化。在CDB中為每個PDB確定使用CPU最低份額,CDB會按照一個PDB份額/分配的總份額數*100%,來保證PDB最低份額數。
容器管理
連線資料庫
[[email protected] ~]$ sqlplus / as sysdba
查詢該容器是CDB 還是非CDB
SQL> select name,cdb,open_mode,con_id from v$database;
NAME CDB OPEN_MODE CON_ID
--------- --- -------------------- ----------
ORCL YES READ WRITE 0
檢視當前容器
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
檢視所建立的PDB
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
建立一個新PDB
SQL> create pluggable database pdb1 admin user pdb1 identified by pdb1 file_name_convert=('/u01/app/oracle/oradata/orcl/pdbseed/','/u01/app/oracle/oradata/orcl/pdb1/');
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
4 PDB1 MOUNTED
啟動一個建立好的PDB
SQL> alter pluggable database pdb1 open;
Warning: PDB altered with errors.
SQL> select message,time from pdb_plug_in_violations;
MESSAGE
--------------------------------------------------------------------------------
TIME
---------------------------------------------------------------------------
Sync PDB failed with ORA-959 during 'CREATE USER c##dm_ca IDENTIFIED BY * PROFIL
E DEFAULT DEFAULT TABLESPACE casdb ACCOUNT UNLOCK container = all'
26-DEC-18 11.26.27.070262 AM
SQL> alter session set container=pdb1;
Session altered.
SQL> create tablespace users datafile '/u01/app/oracle/oradata/orcl/pdb1/users01.dbf' size 800M;
Tablespace created.
SQL> alter pluggable database pdb1 close;
Pluggable database altered.
SQL> alter pluggable database pdb1 open;
Warning: PDB altered with errors. #還是會報錯,不知是何原因了,檢視沒有報錯資訊
SQL> select message,time from pdb_plug_in_violations;
no rows selected
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB1 READ WRITE YES
也可以通過sqlplus使用傳統的startup和shutdown命令來啟動和關閉PDB
SQL> alter session set container=pdb1;
Session altered.
SQL>
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB1 MOUNTED
SQL> startup
Warning: PDB altered with errors.
Pluggable Database opened.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB1 READ WRITE YES
SQL> shutdown immediate;
Pluggable Database closed.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB1 MOUNTED
SQL> alter session set container=CDB$ROOT;
Session altered.
配置監聽檔案
監聽檔案目錄在$ORACLE_HOME/network/admin
[[email protected] admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = master)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
PDBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = master)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDBORCL)
)
)
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDB1)
)
)
listener.ora 檔案內容
[[email protected] admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = ORCL)
)
(SID_DESC=
(GLOBAL_DBNAME = PDB1)
(SID_NAME = PDB1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = master)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
測試一下
[[email protected] admin]$ tnsping pdb1
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 26-DEC-2018 11:55:36
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDB1)))
OK (0 msec)
連線pdb1
方法一:
[[email protected] admin]$ sqlplus sys/admin@pdb1 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 26 14:23:22 2018
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> startup
Warning: PDB altered with errors.
Pluggable Database opened.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB1 READ WRITE YES
SQL>
方法二:
[[email protected] ~]$ sqlplus pdb1/[email protected]
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 26 15:27:35 2018
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>
oracle12C中在啟動資料庫的時候,PDB並不會隨著CDB而啟動。不過我們可以通過建立一個觸發器讓PDB能夠隨CDB啟動。如下:
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT
SQL> CREATE OR REPLACE TRIGGER open_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/
Trigger created.
重新啟動資料庫:
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 26 14:32:11 2018
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 pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL READ WRITE NO
4 PDB1 READ WRITE YES
確實已經隨著資料庫啟動,pdb1也啟動起來。
但是,RESTRICTED 為YES
我重新建立一個pdb2
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> create pluggable database pdb2 admin user pdb2 identified by pdb2 file_name_convert=('/u01/app/oracle/oradata/orcl/pdbseed/','/u01/app/oracle/oradata/orcl/pdb2/');
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL READ WRITE NO
4 PDB1 READ WRITE YES
5 PDB2 MOUNTED
SQL> alter pluggable database pdb2 open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL READ WRITE NO
4 PDB1 READ WRITE YES
5 PDB2 READ WRITE NO
pdb2完全正確。
pdb1解決如下:
SQL> select message from pdb_plug_in_violations where name='PDB1';
MESSAGE
--------------------------------------------------------------------------------
Sync PDB failed with ORA-959 during 'CREATE USER c##dm_ca IDENTIFIED BY * PROFIL
E DEFAULT DEFAULT TABLESPACE casdb ACCOUNT UNLOCK container = all'
Sync PDB failed with ORA-1918 during 'drop user c##dm_ca cascade'
SQL> alter session set container=PDB1;
Session altered.
SQL> select name from v$tablespace;
NAME
------------------------------
UNDOTBS1
SYSTEM
SYSAUX
TEMP
USERS
SQL> create tablespace casdb datafile '/u01/app/oracle/oradata/orcl/pdb1/casdb01.dbf' size 800M;
Tablespace created.
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database pdb1 close;
Pluggable database altered.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL READ WRITE NO
4 PDB1 READ WRITE NO
5 PDB2 READ WRITE NO
SQL>
|