ORACLE 12C PDB 維護(hù)基礎(chǔ)知識(shí)介紹
更新時(shí)間：2016年09月16日 21:50:37 作者：惜分飛
CDB和PDB是ORACLE 12C一個(gè)很亮的新特性,由于他們的引入導(dǎo)致傳統(tǒng)的ORACLE數(shù)據(jù)庫(kù)管理理念不少發(fā)生了改變,這里列舉了部分最基本的cdb和pdb管理方式
先說(shuō)基本用法：
先按11G之前進(jìn)行
conn / as sysdba;
create user test identifed by test;
ORA-65096: 公用用戶名或角色名無(wú)效.
查官方文檔得知“試圖創(chuàng)建一個(gè)通用用戶，必需要用C##或者c##開頭”，這時(shí)候心里會(huì)有疑問(wèn)，什么是common user？不管先建成功了再說(shuō)
create C##user test identifed by test;
創(chuàng)建成功
SQL>show con_name;
CON_NAME
------------------------------
CDB$ROOT
selectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4066409480 PDB$SEED READ ONLY
3 2270995695 PDBORCL MOUNTED
SQL>alter session set container=PDBORCL;
這時(shí)再用create user test identifed by test;建立用戶就可以了。
CDB和PDB是ORACLE 12C一個(gè)很亮的新特性,由于他們的引入導(dǎo)致傳統(tǒng)的ORACLE數(shù)據(jù)庫(kù)管理理念不少發(fā)生了改變,這里列舉了部分最基本的cdb和pdb管理方式
cdb和pdb關(guān)系圖
ORACLE 12C版本
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 PL/SQL Release 12.1.0.1.0 - Production 0 CORE 12.1.0.1.0 Production 0 TNS for Linux: Version 12.1.0.1.0 - Production 0 NLSRTL Version 12.1.0.1.0 - Production 0
啟動(dòng)關(guān)閉pdb
SQL> startup
ORACLE instance started.
Total System Global Area 597098496 bytes
Fixed Size 2291072 bytes
Variable Size 272632448 bytes
Database Buffers 314572800 bytes
Redo Buffers 7602176 bytes
Database mounted.
Database opened.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 MOUNTED
4 3872456618 PDB2 MOUNTED
SQL> alter PLUGGABLE database pdb1 open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 READ WRITE
4 3872456618 PDB2 MOUNTED
SQL> alter PLUGGABLE database pdb1 close;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 MOUNTED
4 3872456618 PDB2 MOUNTED
SQL> alter PLUGGABLE database all open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 READ WRITE
4 3872456618 PDB2 READ WRITE
SQL> alter PLUGGABLE database all close;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 MOUNTED
4 3872456618 PDB2 MOUNTED
SQL> alter session set container=pdb1;
Session altered.
SQL> startup
Pluggable Database opened.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
3 3313918585 PDB1 READ WRITE
pdb的管理可以在cdb中進(jìn)行也可以在pdb中進(jìn)行,如果是cdb中進(jìn)行,需要PLUGGABLE關(guān)鍵字,如果是pdb中直接和普通數(shù)據(jù)庫(kù)一樣
登錄pdb
[oracle@xifenfei ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 12-MAY-2013 08:07:02 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production Start Date 11-MAY-2013 18:30:54 Uptime 0 days 13 hr. 36 min. 8 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/grid/product/12.1/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/xifenfei/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=xifenfei)(PORT=5500)) (Security=(my_wallet_directory=/u01/oracle/12.1/db_1/admin/cdb/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM", status READY, has 1 handler(s) for this service... Service "cdb" has 1 instance(s). Instance "cdb", status READY, has 1 handler(s) for this service... Service "cdbXDB" has 1 instance(s). Instance "cdb", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "cdb", status READY, has 1 handler(s) for this service... Service "pdb2" has 1 instance(s). Instance "cdb", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@xifenfei ~]$ tnsping pdb1 TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 12-MAY-2013 08:07:09 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1))) OK (20 msec) [oracle@xifenfei ~]$ sqlplus sys/xifenfei@pdb1 as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Sun May 12 08:08:02 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> show con_name; CON_NAME ------------------------------ PDB1 [oracle@xifenfei ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Sun May 12 08:09:14 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> alter session set container=pdb1; Session altered. SQL> show con_name; CON_NAME ------------------------------ PDB1
pdb可以通過(guò)alter session container進(jìn)入也可以直接通過(guò)tns方式登錄
創(chuàng)建用戶
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 READ WRITE
4 3872456618 PDB2 MOUNTED
SQL> create user xff identified by xifenfei;
create user xff identified by xifenfei
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> !oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause: An attempt was made to create a common user or role with a name
// that wass not valid for common users or roles. In addition to
// the usual rules for user and role names, common user and role
// names must start with C## or c## and consist only of ASCII
// characters.
// *Action: Specify a valid common user or role name.
//
SQL> create user c##xff identified by xifenfei;
User created.
SQL> SELECT USERNAME,CON_ID,USER_ID FROM CDB_USERS WHERE USERNAME='C##XFF';
USERNAME CON_ID USER_ID
---------- ---------- ----------
C##XFF 1 103
C##XFF 3 104
SQL> alter session set container=pdb1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> create user xff identified by xifenfei;
User created.
SQL> create user c##abc identified by xifenfei;
create user c##abc identified by xifenfei
*
ERROR at line 1:
ORA-65094: invalid local user or role name
創(chuàng)建用戶默認(rèn)的是container=all,在cdb中只能創(chuàng)建全局用戶(c##開頭),會(huì)在cdb和所有的pdb中創(chuàng)建該用戶(但是pdb中的全局用戶需要另外授權(quán)才能夠在pdb中訪問(wèn))。在pdb中只能創(chuàng)建的用戶為本地用戶
用戶授權(quán)
SQL> grant connect to c##xff; Grant succeeded. SQL> select GRANTEE,con_id from cdb_ROLE_PRIVS where GRANTED_ROLE='CONNECT' AND GRANTEE='C##XFF'; GRANTEE CON_ID ------------------------------ ---------- C##XFF 1 SQL> grant resource to c##xff container=all; Grant succeeded. SQL> select GRANTEE,con_id from cdb_ROLE_PRIVS where GRANTED_ROLE='RESOURCE' AND GRANTEE='C##XFF'; GRANTEE CON_ID ------------------------------ ---------- C##XFF 1 C##XFF 3
用戶授權(quán)默認(rèn)情況下是只會(huì)給當(dāng)前container,在cdb中也可以指定container=all,對(duì)所有open的pdb且存在該用戶都進(jìn)行授權(quán)
修改參數(shù)
SQL> alter system set open_cursors=500 container=all; System altered. SQL> conn sys/xifenfei@pdb1 as sysdba Connected. SQL> show parameter open_cursors; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ open_cursors integer 500 SQL> alter system set open_cursors=100; alter system set open_cursors=100 * ERROR at line 1: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only SQL> alter database open; Database altered. SQL> alter system set open_cursors=100; System altered. SQL> show parameter open_cursors; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ open_cursors integer 100 SQL> conn / as sysdba Connected. SQL> show parameter open_cursors; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ open_cursors integer 500
這里可以看到在cdb中修改,pdb會(huì)繼承進(jìn)去;如果在pdb中修改會(huì)覆蓋pdb從cdb中繼承的參數(shù)含義
相關(guān)文章
![[Oracle] Data Guard CPU/PSU補(bǔ)丁安裝詳細(xì)教程](http://img.jbzj.com/images/xgimg/bcimg0.png)
[Oracle] Data Guard CPU/PSU補(bǔ)丁安裝詳細(xì)教程
以下是對(duì)Data Guard CPU/PSU補(bǔ)丁安裝的方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友可以參考下2013-07-07
詳解如何刪除Oracle數(shù)據(jù)庫(kù)臨時(shí)表空間
因生產(chǎn)環(huán)境磁盤空間不足,影響了業(yè)務(wù),短時(shí)間內(nèi)無(wú)法擴(kuò)容磁盤,經(jīng)過(guò)排查發(fā)現(xiàn),可以釋放temp臨時(shí)表空間來(lái)臨時(shí)釋放部分空間,本文記錄了如何釋放臨時(shí)表空間的詳細(xì)操作步驟,需要的朋友可以參考下2024-03-03
Oracle Linux 6.8安裝 mysql 5.7.17的詳細(xì)教程
這篇文章主要介紹了Oracle Linux 6.8安裝 mysql 5.7.17的詳細(xì)教程,需要的朋友可以參考下2017-06-06
window10 安裝Oracle19C 和SQL Developer 的圖文教程
這篇文章主要介紹了window10 安裝Oracle19C 和SQL Developer 教程（圖文詳解）,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-03-03
在Oracle中別名也可以在列名和表名中進(jìn)行,進(jìn)行別名處理是為了給列或表一個(gè)臨時(shí)項(xiàng),下面這篇文章主要給大家介紹了關(guān)于Oracle別名使用的一些要點(diǎn)小結(jié),需要的朋友可以參考下2022-04-04
這篇文章主要介紹了Oracle判斷表、列、主鍵是否存在的方法，下面是小編之家小編給大家?guī)?lái)的一些判斷方法和大家分享下2018-04-04
處理Oracle監(jiān)聽程序當(dāng)前無(wú)法識(shí)別連接描述符中請(qǐng)求的服務(wù)異常(ORA-12514)
這篇文章介紹了處理Oracle監(jiān)聽程序當(dāng)前無(wú)法識(shí)別連接描述符中請(qǐng)求的服務(wù)異常(ORA-12514)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-12-12
Oracle數(shù)據(jù)庫(kù)中基本的查詢優(yōu)化與子查詢優(yōu)化講解
這篇文章主要介紹了Oracle數(shù)據(jù)庫(kù)中基本的查詢優(yōu)化與子查詢優(yōu)化講解,舉了實(shí)例來(lái)分析子查詢對(duì)性能的影響,需要的朋友可以參考下2016-01-01
本文通過(guò)實(shí)例代碼給大家介紹了Oracle中Like與Instr模糊查詢性能對(duì)比，需要的朋友參考下吧2017-05-05
這篇文章主要為大家詳細(xì)介紹了oracle11g卸載完整圖文教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-09-09