0、查看db
10多年專(zhuān)注成都網(wǎng)站制作，成都定制網(wǎng)站，個(gè)人網(wǎng)站制作服務(wù)，為大家分享網(wǎng)站制作知識(shí)、方案，網(wǎng)站設(shè)計(jì)流程、步驟,成功服務(wù)上千家企業(yè)。為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁(yè)設(shè)計(jì)及定制高端網(wǎng)站建設(shè)服務(wù),專(zhuān)注于成都定制網(wǎng)站,高端網(wǎng)頁(yè)制作,對(duì)木屋等多個(gè)行業(yè)，擁有豐富的網(wǎng)站運(yùn)維經(jīng)驗(yàn)。
show con_name
show pdbs
1、cdb切換至pdb
alter session set container=pdb;
2、pdb切換至cdb
alter session set container=cdb$root;
3、pdb更改字符集（cdb的字符集必須為al32utf8）
alter system enable restricted session; （恢復(fù)操作為alter system disable...）
alter database character set internal_use zhs16gbk;
注：使用低版本的client在不同字符集的pdb之間切換會(huì)報(bào)錯(cuò)：ora-24964
4、實(shí)例啟動(dòng)后自動(dòng)打開(kāi)所有pdb
CREATE OR REPLACE TRIGGER open_pdbs AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
5、pdb克隆
create pluggable database pdb2 from pdb1 file_name_convert=('pdb1path','pdb2path');
Oracle 12C引入了CDB與PDB的新特性，在ORACLE 12C數(shù)據(jù)庫(kù)引入的多租用戶(hù)環(huán)境（Multitenant Environment）中，允許一個(gè)數(shù)據(jù)庫(kù)容器（CDB）承載多個(gè)可插拔數(shù)據(jù)庫(kù)（PDB）。CDB全稱(chēng)為Container Database，中文翻譯為數(shù)據(jù)庫(kù)容器，PDB全稱(chēng)為Pluggable Database，即可插拔數(shù)據(jù)庫(kù)。在ORACLE 12C之前，實(shí)例與數(shù)據(jù)庫(kù)是一對(duì)一或多對(duì)一關(guān)系（RAC）：即一個(gè)實(shí)例只能與一個(gè)數(shù)據(jù)庫(kù)相關(guān)聯(lián)，數(shù)據(jù)庫(kù)可以被多個(gè)實(shí)例所加載。而實(shí)例與數(shù)據(jù)庫(kù)不可能是一對(duì)多的關(guān)系。當(dāng)進(jìn)入ORACLE 12C后，實(shí)例與數(shù)據(jù)庫(kù)可以是一對(duì)多的關(guān)系。下面是官方文檔關(guān)于CDB與PDB的關(guān)系圖。
clip_image002
其實(shí)大家如果對(duì)SQL SERVER比較熟悉的話(huà)，這種CDB與PDB是不是感覺(jué)和SQL SERVER的單實(shí)例多數(shù)據(jù)庫(kù)架構(gòu)是一回事呢。像PDB$SEED可以看成是master、msdb等系統(tǒng)數(shù)據(jù)庫(kù)，PDBS可以看成用戶(hù)創(chuàng)建的數(shù)據(jù)庫(kù)。而可插拔的概念與SQL SERVER中的用戶(hù)數(shù)據(jù)庫(kù)的分離、附加其實(shí)就是那么一回事?？磥?lái)ORACLE也“抄襲”了一把SQL SERVER的概念，只是改頭換面的包裝了一番。
CDB組件（Components of a CDB）
一個(gè)CDB數(shù)據(jù)庫(kù)容器包含了下面一些組件：
ROOT組件
ROOT又叫CDB$ROOT, 存儲(chǔ)著ORACLE提供的元數(shù)據(jù)和Common User,元數(shù)據(jù)的一個(gè)例子是ORACLE提供的PL/SQL包的源代碼，Common User 是指在每個(gè)容器中都存在的用戶(hù)。
SEED組件
Seed又叫PDB$SEED,這個(gè)是你創(chuàng)建PDBS數(shù)據(jù)庫(kù)的模板，你不能在Seed中添加或修改一個(gè)對(duì)象。一個(gè)CDB中有且只能有一個(gè)Seed. 這個(gè)感念，個(gè)人感覺(jué)非常類(lèi)似SQL SERVER中的model數(shù)據(jù)庫(kù)。
PDBS
CDB中可以有一個(gè)或多個(gè)PDBS，PDBS向后兼容，可以像以前在數(shù)據(jù)庫(kù)中那樣操作PDBS，這里指大多數(shù)常規(guī)操作。
這些組件中的每一個(gè)都可以被稱(chēng)為一個(gè)容器。因此，ROOT(根)是一個(gè)容器，Seed(種子)是一個(gè)容器，每個(gè)PDB是一個(gè)容器。每個(gè)容器在CDB中都有一個(gè)獨(dú)一無(wú)二的的ID和名稱(chēng)。
1）連接到CDB數(shù)據(jù)庫(kù)
連接到CDB數(shù)據(jù)庫(kù)容器非常簡(jiǎn)單，跟以前連接數(shù)據(jù)庫(kù)是一樣的
復(fù)制代碼
[oracle@get-orasvr02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Oct 20 23:41:36 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL
[oracle@get-orasvr02 ~]$ sqlplus sys/password as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Oct 20 23:43:17 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL
復(fù)制代碼
2）查看數(shù)據(jù)庫(kù)是否為CDB
復(fù)制代碼
SQL select name, decode(cdb, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option" , open_mode, con_id from v$database;
NAME Multitenant Option OPEN_MODE CON_ID
--------- ----------------------------- -------------------- ----------
EPPS Multitenant Option enabled READ WRITE 0
復(fù)制代碼
YES表示該數(shù)據(jù)庫(kù)是CDB,如果是NO表示是NO-CDB(普通數(shù)據(jù)庫(kù))
3）查看當(dāng)前容器（Container）
3.1
復(fù)制代碼
SQL show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL
3.2
SQL select sys_context('userenv', 'con_name') "Container DB" from dual;
Container DB
----------------------------------------------------
CDB$ROOT
SQL
復(fù)制代碼
4）查看CDB容器中的PDBS信息
查看CDB中有多少個(gè)pluggable database
復(fù)制代碼
SQL select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
---------- ---------- -------------------------------- ------------------------------ ----------
2 4071321146 E89E8DA2866E3157E043DE07A8C09238 PDB$SEED READ ONLY
3 1930201447 E89E9418B882350CE043DE07A8C092B6 PDBEPPS MOUNTED
SQL
復(fù)制代碼
5）啟動(dòng)PDB數(shù)據(jù)庫(kù)
方式1：
復(fù)制代碼
SQL alter pluggable database PDBEPPS open;
Pluggable database altered.
SQL select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
---------- ---------- -------------------------------- ------------------------------ ----------
2 4071321146 E89E8DA2866E3157E043DE07A8C09238 PDB$SEED READ ONLY
3 1930201447 E89E9418B882350CE043DE07A8C092B6 PDBEPPS READ WRITE
復(fù)制代碼
方式2：
復(fù)制代碼
SQL alter session set container=PDBEPPS;
Session altered.
SQL startup
Pluggable Database opened.
SQL
復(fù)制代碼
6）關(guān)閉PDB數(shù)據(jù)庫(kù)
SQL alter pluggable database PDBEPPS close;
Pluggable database altered.
SQL select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
---------- ---------- -------------------------------- ------------------------------ ----------
2 4071321146 E89E8DA2866E3157E043DE07A8C09238 PDB$SEED READ ONLY
3 1930201447 E89E9418B882350CE043DE07A8C092B6 PDBEPPS MOUNTED
SQL
7）在容器間切換
SQL alter session set container=PDBEPPS;
Session altered.
SQL show con_name;
CON_NAME
------------------------------
PDBEPPS
SQL
SQL alter session set container=CDB$ROOT;
Session altered.
SQL show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL
1 連接到CDB
和普通實(shí)例一樣的連接。 指定ORACLE_SID 以后可以使用OS認(rèn)證，也可以使用密碼進(jìn)行連接。
[oracle@Ora12c /]$ echo $ORACLE_SID
cndba
[oracle@Ora12c /]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production onMon Apr 28 11:33:43 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options
SQL conn system/oracle
Connected.
SQL
--查看CDB中可用的service：
SQL COLUMN name FORMAT A30
SQL SELECT name,pdb
2 FROM v$services
3 ORDER BY name;
NAME PDB
------------------------------------------------------------
SYS$BACKGROUND CDB$ROOT
SYS$USERS CDB$ROOT
cndba CDB$ROOT
cndbaXDB CDB$ROOT
pcndba2 PCNDBA2
pdbcndba PDBCNDBA
6 rows selected.
--通過(guò)lsnrctl 也可以判斷：
[oracle@Ora12c /]$ lsnrctl service
LSNRCTL for Linux: Version 12.1.0.1.0 -Production on 28-APR-2014 11:35:31
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "cndba" has 1instance(s).
Instance "cndba", status READY, has 1 handler(s) for thisservice...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "cndbaXDB" has 1instance(s).
Instance "cndba", status READY, has 1 handler(s) for thisservice...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER machine: Ora12c, pid: 10085
(ADDRESS=(PROTOCOL=tcp)(HOST=Ora12c)(PORT=14696))
Service "pcndba2" has 1instance(s).
Instance "cndba", status READY, has 1 handler(s) for thisservice...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "pdbcndba" has 1instance(s).
Instance "cndba", status READY, has 1 handler(s) for thisservice...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[oracle@Ora12c /]$
通過(guò)這些service，就可以遠(yuǎn)程連接CDB。
--EZCONNECT
C:\Users\Davesqlplussystem/oracle@192.168.1.10:1521/cndba
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 30 11:36:48 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
連接到:
Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options
SQL
--通過(guò)TNSNAMES.ORA連接：
在tnsnames.ora 中配置如下：
cndba =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cndba)
)
)
--連接：
C:\Users\Davesqlplussystem/oracle@cndba
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 30 11:40:01 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
連接到:
Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options
SQL
2 在不同Container database中切換
在12c的架構(gòu)中，因?yàn)橛蠧DB和 PDB的存在，所以會(huì)有很多不同的container，所以在連接到不同的container時(shí)，就需要進(jìn)行切換。
--查看當(dāng)前的container：
SQL show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL SELECT SYS_CONTEXT('USERENV','CON_NAME') FROM dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT
SQL set lin 140
SQL select con_id, dbid,guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
---------- ------------------------------------------ ---------- ----------
2 4088301206 F7C1E3C96BBF0585E0430A01A8C05459 PDB$SEED READ ONLY
3 426143573F7C209EB1DFC0854E0430A01A8C0B787 PDBCNDBA READ WRITE
4 1231796139 F812DE1B6A8F363AE0430A01A8C0C759 PCNDBA2 READ WRITE
--切換container：
SQL alter session setcontainer=pcndba2;
Session altered.
SQL show con_name
CON_NAME
------------------------------
PCNDBA2
3 連接到PDB
--EZCONNECT:
C:\Users\Davesqlplussystem/oracle@192.168.1.10:1521/pcndba2
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 30 11:54:30 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
連接到:
Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options
SQL
--TNSNAMES.ora
在tnsnames.ora 中添加如下內(nèi)容：
pcndba =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pcndba2)
)
)
C:\Users\Davesqlplussystem/oracle@pcndba
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 30 11:55:50 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
連接到:
Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options
SQL
啟動(dòng)根容器：
[oracle@eric ~]$ export ORACLE_SID=cup
[oracle@eric ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 21 16:00:06 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL startup -----不會(huì)直接啟動(dòng)所有可插拔數(shù)據(jù)庫(kù)，如需啟動(dòng)所有可插拔數(shù)據(jù)庫(kù)，執(zhí)行命令：alter pluggable database all open
ORACLE instance started.
Total System Global Area 767557632 bytes
Fixed Size 2929112 bytes
Variable Size 574623272 bytes
Database Buffers 184549376 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
停止根容器：
SQL shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
查看是否創(chuàng)建了CDB，如果有顯示名字:
SQL select name,cdb from v$database;
NAME CDB
--------- ---
CUP YES
SQL show parameter service;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string cup
查看容器名字，其中有3個(gè)容器：根容器、種子容器和自己創(chuàng)建的容器:
SQL select con_id,name from v$containers;
CON_ID NAME
---------- ------------------------------
1 CDB$ROOT ---根容器
2 PDB$SEED ---種子容器，只可讀
3 TEA ---自己創(chuàng)建的可插拔數(shù)據(jù)庫(kù)
SQL select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/oracle/app/oradata/CUP/datafile/o1_mf_system_bch07kvz_.dbf
/oracle/app/oradata/CUP/datafile/o1_mf_sysaux_bch020oo_.dbf
/oracle/app/oradata/CUP/datafile/o1_mf_undotbs1_bch0d2on_.dbf
/oracle/app/oradata/CUP/datafile/o1_mf_users_bch0d15n_.dbf
SQL select file_name from cdb_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/oracle/app/oradata/CUP/datafile/o1_mf_system_bch07kvz_.dbf
/oracle/app/oradata/CUP/datafile/o1_mf_sysaux_bch020oo_.dbf
/oracle/app/oradata/CUP/datafile/o1_mf_undotbs1_bch0d2on_.dbf
/oracle/app/oradata/CUP/datafile/o1_mf_users_bch0d15n_.dbf
創(chuàng)建公共用戶(hù)：
SQL create user c##eric identified by gao;
User created.
SQL conn c##eric/gao
ERROR:
ORA-01045: user C##ERIC lacks CREATE SESSION privilege; logon denied ----沒(méi)有權(quán)限，我們可以單獨(dú)給其賦予權(quán)限，也可以給其指定角色。
Warning: You are no longer connected to ORACLE.
SQL conn / as sysdba
Connected.
SQL grant dba to c##eric container=all; ---給其DBA角色，角色范圍覆蓋所有的容器
Grant succeeded.
SQL conn c##eric/gao
Connected. ---連接成功
切換容器：
首先啟動(dòng)自己創(chuàng)建的容器數(shù)據(jù)庫(kù)：
SQL alter pluggable database tea open; ---首先啟動(dòng)PDB數(shù)據(jù)庫(kù)
SQL alter session set container = tea;
Session altered.
SQL show con_name ---查看當(dāng)前所在的容器名
CON_NAME
------------------------------
TEA
關(guān)閉可插拔數(shù)據(jù)庫(kù)：
SQL alter pluggable database tea close immediate;
Pluggable database altered.
重慶分公司
重慶分公司
