在ORACLE 12C中引入了CDB和PDB的概念,实现了ORACLE数据库的可插拔,在一个CDB数据库中,有多个PDB,而每一个PDB又可以理解为一个独立的传统ORACLE 数据库,那为了能够通过一个sql查询获得整个CDB数据库的信息,ORALCE 引入了CDB_*开头的视图,该视图就是在传统的DBA_*视图基础之上增加了CON_ID,用来区分不同的PDB,从而实现了一个简单sql查询在有足够权限的情况下,可以查询所有PDB中信息
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

确定是否是CDB

SQL> SELECT CDB FROM V$DATABASE;
CDB
---
YES

YES表示该数据库是CDB,如果是NO表示是NO-CDB(普通数据库)

CDB中各容器信息

SQL> COLUMN NAME FORMAT A8
SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
NAME         CON_ID       DBID    CON_UID GUID
-------- ---------- ---------- ---------- --------------------------------
CDB$ROOT          1 1922813718          1 DB85A3D39F8E7703E0431CAAE80A8C44
PDB$SEED          2 4048821679 4048821679 D49B80694E4A449BE0430100007F906F
PDB1              3 3313918585 3313918585 D49BF37938FB4C10E0430100007F6CBD
PDB2              4 3872456618 3872456618 D49BFA33332F4C3EE0430100007FA059
SQL> select CON_NAME_TO_ID('PDB1') FROM DUAL;
CON_NAME_TO_ID('PDB1')
----------------------
                     3
SQL> SELECT CON_DBID_TO_ID(3313918585) FROM DUAL;
CON_DBID_TO_ID(3313918585)
--------------------------
                         3
SQL> SELECT CON_UID_TO_ID(3313918585) FROM DUAL;
CON_UID_TO_ID(3313918585)
-------------------------
                        3

PDB部分信息

SQL> COLUMN PDB_NAME FORMAT A15
SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
    PDB_ID PDB_NAME        STATUS
---------- --------------- -------------
         2 PDB$SEED        NORMAL
         3 PDB1            NORMAL
         4 PDB2            NORMAL
SQL> COLUMN NAME FORMAT A15
SQL> COLUMN RESTRICTED FORMAT A10
SQL> COLUMN OPEN_TIME FORMAT A30
SQL> SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
NAME            OPEN_MODE  RESTRICTED OPEN_TIME
--------------- ---------- ---------- ------------------------------
PDB$SEED        READ ONLY  NO         12-MAY-13 08.51.53.177 AM
PDB1            READ WRITE NO         12-MAY-13 09.35.48.646 AM
PDB2            MOUNTED               12-MAY-13 08.56.59.859 AM

CDB中查询对象信息

SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN OWNER FORMAT A15
SQL> COLUMN TABLE_NAME FORMAT A30
SQL> SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME
  2  FROM DBA_PDBS p, CDB_TABLES t
  3  WHERE p.PDB_ID > 2
  4  AND T.TABLE_NAME='COL$'
  5  AND p.PDB_ID = t.CON_ID
  6  ORDER BY p.PDB_ID;
    PDB_ID PDB_NAME        OWNER           TABLE_NAME
---------- --------------- --------------- ------------------------------
         3 PDB1            SYS             COL$
         4 PDB2            SYS             COL$

查询在CDB中的PDB数据/临时文件信息

SQL> COLUMN PDB_ID FORMAT 999
SQL> COLUMN PDB_NAME FORMAT A8
SQL> COLUMN FILE_ID FORMAT 9999
SQL> COLUMN TABLESPACE_NAME FORMAT A10
SQL> COLUMN FILE_NAME FORMAT A45
SQL> SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
  2  FROM DBA_PDBS p, CDB_DATA_FILES d
  3  WHERE p.PDB_ID = d.CON_ID
  4  ORDER BY p.PDB_ID;
PDB_ID PDB_NAME FILE_ID TABLESPACE FILE_NAME
------ -------- ------- ---------- ---------------------------------------------
     2 PDB$SEED       5 SYSTEM     +DATA/pdb$seed_system01.dbf
     2 PDB$SEED       7 SYSAUX     +DATA/pdb$seed_sysaux01.dbf
     3 PDB1           9 SYSAUX     +DATA/pdb1_pdb$seed_sysaux01.dbf
     3 PDB1          10 USERS      +DATA/cdb/pdb1_users01.dbf
     3 PDB1           8 SYSTEM     +DATA/pdb1_pdb$seed_system01.dbf
     4 PDB2          13 USERS      +DATA/cdb/pdb2_users01.dbf
     4 PDB2          12 SYSAUX     +DATA/pdb2_pdb$seed_sysaux01.dbf
     4 PDB2          11 SYSTEM     +DATA/pdb2_pdb$seed_system01.dbf
8 rows selected.
SQL> COLUMN CON_ID FORMAT 999
SQL> COLUMN FILE_ID FORMAT 9999
SQL> COLUMN TABLESPACE_NAME FORMAT A15
SQL> COLUMN FILE_NAME FORMAT A45
SQL> SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
  2  FROM CDB_TEMP_FILES
  3  ORDER BY CON_ID;
CON_ID FILE_ID TABLESPACE_NAME FILE_NAME
------ ------- --------------- ---------------------------------------------
     1       1 TEMP            +DATA/cdb/temp01.dbf
     2       2 TEMP            +DATA/pdbseed_temp01.dbf
     3       3 TEMP            +DATA/pdb1_temp01.dbf
     4       4 TEMP            +DATA/pdb2_temp01.dbf

查询PDB的service信息

SQL> COLUMN NETWORK_NAME FORMAT A30
SQL> COLUMN PDB FORMAT A15
SQL> COLUMN CON_ID FORMAT 999
SQL> SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES
  2  WHERE PDB IS NOT NULL AND
  3  CON_ID > 2
  4  ORDER BY PDB;
PDB             NETWORK_NAME                   CON_ID
--------------- ------------------------------ ------
PDB1            pdb1                                3
PDB2            pdb2                                4

PDB中可以修改参数

SELECT NAME FROM V$SYSTEM_PARAMETER
WHERE ISPDB_MODIFIABLE = 'TRUE'
ORDER BY NAME;

查看PDB历史信息

SQL> COLUMN DB_NAME FORMAT A10
SQL> COLUMN CON_ID FORMAT 999
SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN OPERATION FORMAT A16
SQL> COLUMN OP_TIMESTAMP FORMAT A10
SQL> COLUMN CLONED_FROM_PDB_NAME FORMAT A15
SQL> SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
  2  FROM CDB_PDB_HISTORY
  3  WHERE CON_ID > 2
  4  ORDER BY CON_ID;
DB_NAME    CON_ID PDB_NAME        OPERATION        OP_TIMESTA CLONED_FROM_PDB
---------- ------ --------------- ---------------- ---------- ---------------
SEEDDATA        3 PDB$SEED        UNPLUG           29-APR-13
CDB             3 PDB1            CREATE           01-FEB-13  PDB$SEED
CDB             3 PDB$SEED        PLUG             01-FEB-13  PDB$SEED
SEEDDATA        4 PDB$SEED        UNPLUG           29-APR-13
CDB             4 PDB2            CREATE           01-FEB-13  PDB$SEED
CDB             4 PDB$SEED        PLUG             01-FEB-13  PDB$SEED
6 rows selected.

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              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

启动关闭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中进行也可以在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                     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可以通过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 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

创建用户默认的是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                                  1
C##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 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会继承进去;如果在pdb中修改会覆盖pdb从cdb中继承的参数含义