ORACLE 12C PDB 維護基礎知識介紹_oracle

先說基本用法：
先按11G之前進行
conn / as sysdba;
create user test identifed by test;

ORA-65096: 公用使用者名稱或角色名稱無效.

查官方文檔得知“試圖建立一個通用使用者，必需要用C##或者c##開頭”，這時候心裡會有疑問，什麼是common user？不管先建成功了再說
create C##user test identifed by test;
建立成功

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;
這時再用create user test identifed by test;建立使用者就可以了。

CDB和PDB是ORACLE 12C一個很亮的新特性,由於他們的引入導致傳統的ORACLE資料庫管理理念不少發生了改變,這裡列舉了部分最基本的cdb和pdb管理方式
cdb和pdb關係圖

ORACLE 12C版本

SQL> select * from v$version; BANNER                                        CON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production       0PL/SQL Release 12.1.0.1.0 - Production                          0CORE  12.1.0.1.0   Production                            0TNS for Linux: Version 12.1.0.1.0 - Production                      0NLSRTL Version 12.1.0.1.0 - Production                          0

啟動關閉pdb

SQL> startupORACLE instance started. Total System Global Area 597098496 bytesFixed Size         2291072 bytesVariable Size       272632448 bytesDatabase Buffers     314572800 bytesRedo Buffers        7602176 bytesDatabase 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> startupPluggable 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中進行也可以在pdb中進行,如果是cdb中進行,需要PLUGGABLE關鍵字,如果是pdb中直接和普通資料庫一樣

登入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           LISTENERVersion          TNSLSNR for Linux: Version 12.1.0.1.0 - ProductionStart Date        11-MAY-2013 18:30:54Uptime          0 days 13 hr. 36 min. 8 secTrace Level        offSecurity         ON: Local OS AuthenticationSNMP           OFFListener Parameter File  /u01/app/grid/product/12.1/network/admin/listener.oraListener Log File     /u01/app/grid/diag/tnslsnr/xifenfei/listener/alert/log.xmlListening 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 aliasAttempting 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 ProductionWith the Partitioning, Automatic Storage Management, OLAP, Advanced Analyticsand 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 ProductionWith the Partitioning, Automatic Storage Management, OLAP, Advanced Analyticsand Real Application Testing options SQL> alter session set container=pdb1; Session altered. SQL> show con_name; CON_NAME------------------------------PDB1

pdb可以通過alter session container進入也可以直接通過tns方式登入

建立使用者

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 6509665096, 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    103C##XFF       3    104 SQL> alter session set container=pdb1; Session altered. SQL> show con_name CON_NAME------------------------------PDB1SQL> 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

建立使用者預設的是container=all,在cdb中只能建立全域使用者(c##開頭),會在cdb和所有的pdb中建立該使用者(但是pdb中的全域使用者需要另外授權才能夠在pdb中訪問)。在pdb中只能建立的使用者為本機使用者

使用者授權

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                 1C##XFF                 3

使用者授權預設情況下是只會給當前container,在cdb中也可以指定container=all,對所有open的pdb且存在該使用者都進行授權

修改參數

SQL> alter system set open_cursors=500 container=all; System altered. SQL> conn sys/xifenfei@pdb1 as sysdbaConnected.SQL> show parameter open_cursors; NAME                 TYPE    VALUE------------------------------------ ----------- ------------------------------open_cursors             integer   500SQL> 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 fixedtables 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   100SQL> conn / as sysdbaConnected.SQL> show parameter open_cursors; NAME                 TYPE    VALUE------------------------------------ ----------- ------------------------------open_cursors             integer   500

這裡可以看到在cdb中修改,pdb會繼承進去;如果在pdb中修改會覆蓋pdb從cdb中繼承的參數含義