Oracle 12c cdb & pdb

ORACLE 12c引入了CDB和PDB的概念,使传统的数据库管理概念发生了不少的改变。
下面对最基本的CDB和PDB的管理进行简单的介绍和实验演示。

  1. 数据库的启停
[oracle@ora12c ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.1.0 Production on Tue Dec 31 18:36:40 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  480182272 bytes
Fixed Size                  2289784 bytes
Variable Size             402657160 bytes
Database Buffers           71303168 bytes
Redo Buffers                3932160 bytes
Database mounted.
Database opened.
SQL> select con_id,dbid,name,open_mode from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4072677634 PDB$SEED                       READ ONLY
         3 3892962747 BLUE1                          MOUNTED
         4 3599752208 BLUE2                          MOUNTED

SQL> alter PLUGGABLE database BLUE1 open;

Pluggable database altered.

SQL> select con_id,dbid,name,open_mode from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4072677634 PDB$SEED                       READ ONLY
         3 3892962747 BLUE1                          READ WRITE
         4 3599752208 BLUE2                          MOUNTED
SQL> alter PLUGGABLE database BLUE1 close;

Pluggable database altered.

SQL> select con_id,dbid,open_mode from v$pdbs;

    CON_ID       DBID OPEN_MODE
---------- ---------- ----------
         2 4072677634 READ ONLY
         3 3892962747 MOUNTED
         4 3599752208 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 4072677634 PDB$SEED                       READ ONLY
         3 3892962747 BLUE1                          READ WRITE
         4 3599752208 BLUE2                          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 4072677634 PDB$SEED                       READ ONLY
         3 3892962747 BLUE1                          MOUNTED
         4 3599752208 BLUE2                          MOUNTED

SQL> alter session set container=BLUE2;

Session altered.

SQL> startup
Pluggable Database opened.
SQL> select con_id,dbid,name,open_mode from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         4 3599752208 BLUE2                          READ WRITE

PDB的管理可以在CDB中进行,也可以在PDB中进行,如果在CDB中进行,需要指定pluggable关键字,如果是在pdb中,那么和普通的数据库一样.
  1. PDB的登录
tnsname登录
[oracle@ora12c admin]$ sqlplus sys/bluenight@blue2 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Dec 31 19:23:35 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, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
BLUE2

container登录
[oracle@ora12c admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.1.0 Production on Tue Dec 31 19:27:21 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, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set container=blue2;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
BLUE2

3.user的创建

SQL> desc v$pdbs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CON_ID                                             NUMBER
 DBID                                               NUMBER
 CON_UID                                            NUMBER
 GUID                                               RAW(16)
 NAME                                               VARCHAR2(30)
 OPEN_MODE                                          VARCHAR2(10)
 RESTRICTED                                         VARCHAR2(3)
 OPEN_TIME                                          TIMESTAMP(3)
 CREATE_SCN                                         NUMBER
 TOTAL_SIZE                                         NUMBER

SQL> select con_id,dbid,name,open_mode from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         4 3599752208 BLUE2                          READ WRITE

SQL> create user blue identified by blue;

User created.

SQL> desc cdb_users;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(128)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(4000)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(128)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(128)
 EXTERNAL_NAME                                      VARCHAR2(4000)
 PASSWORD_VERSIONS                                  VARCHAR2(12)
 EDITIONS_ENABLED                                   VARCHAR2(1)
 AUTHENTICATION_TYPE                                VARCHAR2(8)
 PROXY_ONLY_CONNECT                                 VARCHAR2(1)
 COMMON                                             VARCHAR2(3)
 LAST_LOGIN                                         TIMESTAMP(9) WITH TIME ZONE
 ORACLE_MAINTAINED                                  VARCHAR2(1)
 CON_ID                                             NUMBER

SQL> select USERNAME,CON_ID,USER_ID from cdb_users;


USERNAME                 CON_ID    USER_ID
-------------------- ---------- ----------
BLUE                          4        103


SQL> alter session set container=blue1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
BLUE1


operation.
//

SQL> select con_id,dbid,name from v$pdbs;

    CON_ID       DBID NAME
---------- ---------- ------------------------------
         3 3892962747 BLUE1


进入ROOT组件

SQL> alter session set container=cdb$root;

Session altered.

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 4072677634 PDB$SEED                       READ ONLY
         3 3892962747 BLUE1                          READ WRITE
         4 3599752208 BLUE2                          READ WRITE


SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=blue1;

Session altered.

SQL> create user blue identified by blue;

User created.


SQL> alter session set container=cdb$root;

Session altered.

SQL> create user blue identified by blue;
create user blue identified by blue
            *
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> 
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> create user C##blue identified by blue;

User created.


SQL> SELECT USERNAME,CON_ID,USER_ID FROM CDB_USERS WHERE USERNAME like '%BLUE%';

USERNAME                 CON_ID    USER_ID
-------------------- ---------- ----------
C##BLUE                       1        102
BLUE                          4        103
C##BLUE                       4        104
BLUE                          3        103
C##BLUE                       3        104


创建用户默认的是container=all,在cdb中只能创建全局用户(c##开头),会在cdb和所有的pdb中创建该用户,但是pdb中的全局用户需要额外的授权才能够在pdb中访问,在pdb中只能创建本地用户。

4.参数的修改

参数在CDB中修改,PDB会继承修改;参数在PDB中修改,会覆盖CDB中继承的参数含义。
SQL> select con_id,dbid,name,open_mode from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4072677634 PDB$SEED                       READ ONLY
         3 3892962747 BLUE1                          MOUNTED
         4 3599752208 BLUE2                          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 4072677634 PDB$SEED                       READ ONLY
         3 3892962747 BLUE1                          READ WRITE
         4 3599752208 BLUE2                          READ WRITE
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     100
SQL> 
SQL> 
SQL> alter session set container=BLUE1;

Session altered.

SQL> show parameter con_name
SQL> show con_name

CON_NAME
------------------------------
BLUE1
SQL> show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     100

SQL> alter system set open_cursors=500 container=all;

System altered.

SQL> alter session set container=BLUE1;

Session altered.

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     500

SQL> alter session set container=blue2;

Session altered.

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     500

在cdb中修改参数,每个pdb都会继承.
SQL> show con_name

CON_NAME
------------------------------
BLUE2
SQL> alter system set open_cursors=200;

System altered.

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     200
SQL> 
SQL> 
SQL> conn / as sysdba
Connected.
SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     500
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

在pdb中修改参数会覆盖在cdb中修改的参数

5.PDB的创建

[oracle@ora12c ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.1.0 Production on Tue Mar 25 20:00:46 2014

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, OLAP, Advanced Analytics and Real Application Testing options

SQL> col CREATION_SCN a20
SQL> col PDB_NAME for a20
SQL> select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs;

    PDB_ID PDB_NAME                   DBID STATUS        CREATION_SCN
---------- -------------------- ---------- ------------- ------------
         3 BLUE1                3892962747 NORMAL             1918305
         2 PDB$SEED             4072677634 NORMAL             1720782
         4 BLUE2                3599752208 NORMAL             1919344

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/blue12c/users01.dbf
/u01/app/oracle/oradata/blue12c/undotbs01.dbf
/u01/app/oracle/oradata/blue12c/sysaux01.dbf
/u01/app/oracle/oradata/blue12c/system01.dbf

[oracle@ora12c blue12c]$ ls -l
total 2161556
drwxr-x--- 2 oracle oinstall      4096 Oct 28 21:45 blue1
drwxr-x--- 2 oracle oinstall      4096 Oct 28 21:48 blue2
-rw-r----- 1 oracle oinstall  17973248 Mar 25 20:03 control01.ctl
-rw-r----- 1 oracle oinstall  17973248 Mar 25 20:03 control02.ctl
drwxr-x--- 2 oracle oinstall      4096 Oct 28 21:17 pdbseed
-rw-r----- 1 oracle oinstall  52429312 Mar 25 20:03 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Mar 25 19:45 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Mar 25 19:45 redo03.log
-rw-r----- 1 oracle oinstall 849354752 Mar 25 20:03 sysaux01.dbf
-rw-r----- 1 oracle oinstall 828383232 Mar 25 20:03 system01.dbf
-rw-r----- 1 oracle oinstall  92282880 Mar 25 19:57 temp01.dbf
-rw-r----- 1 oracle oinstall 246423552 Mar 25 20:02 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Mar 25 19:45 users01.dbf
[oracle@ora12c blue12c]$ pwd
/u01/app/oracle/oradata/blue12c

创建PDB,名字为BLUE3
SQL> CREATE PLUGGABLE DATABASE BLUE3 ADMIN USER BLUE3 IDENTIFIED BY blue
  2  STORAGE (MAXSIZE 1G MAX_SHARED_TEMP_SIZE 100M)
  3  DEFAULT TABLESPACE TEST
  4  DATAFILE '/u01/app/oracle/oradata/blue12c/blue3/blue03.dbf' SIZE 50M AUTOEXTEND ON
  5  PATH_PREFIX = '/u01/app/oracle/oradata/blue12c/blue3/'
  6  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/blue12c/pdbseed/',
  7  '/u01/app/oracle/oradata/blue12c/blue3/');

Pluggable database created.

SQL> select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs;

    PDB_ID PDB_NAME                   DBID STATUS        CREATION_SCN
---------- -------------------- ---------- ------------- ------------
         3 BLUE1                3892962747 NORMAL             1918305
         2 PDB$SEED             4072677634 NORMAL             1720782
         4 BLUE2                3599752208 NORMAL             1919344
         5 BLUE3                3651593771 NEW                2219689

打开PDB 
SQL> alter pluggable database BLUE3 open;

Pluggable database altered.

SQL> select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs;

    PDB_ID PDB_NAME                   DBID STATUS        CREATION_SCN
---------- -------------------- ---------- ------------- ------------
         3 BLUE1                3892962747 NORMAL             1918305
         2 PDB$SEED             4072677634 NORMAL             1720782
         4 BLUE2                3599752208 NORMAL             1919344
         5 BLUE3                3651593771 NORMAL             2219689
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select con_id,dbid,open_mode from v$pdbs;

    CON_ID       DBID OPEN_MODE
---------- ---------- ----------
         2 4072677634 READ ONLY
         3 3892962747 READ WRITE
         4 3599752208 READ WRITE
         5 3651593771 READ WRITE

6.PDB的删除

SQL> alter pluggable database blue3 close immediate;

Pluggable database altered.

SQL> alter pluggable database blue3 unplug into '/u01/app/oracle/blue3.xml';

Pluggable database altered.

SQL> DROP PLUGGABLE DATABASE BLUE3 INCLUDING DATAFILES;

Pluggable database dropped.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4072677634 PDB$SEED                       READ ONLY
         3 3892962747 BLUE1                          READ WRITE
         4 3599752208 BLUE2                          READ WRITE

SQL> select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs;

    PDB_ID PDB_NAME                   DBID STATUS        CREATION_SCN
---------- -------------------- ---------- ------------- ------------
         3 BLUE1                3892962747 NORMAL             1918305
         2 PDB$SEED             4072677634 NORMAL             1720782
         4 BLUE2                3599752208 NORMAL             1919344

update:
如果pdb没有从cdb中继承,那么pdb的初始化参数是不保存在spfile中的,pdb的参数是存储在cdb的数据字典表中的。

SQL> alter session set container=pdbrac;

Session altered.

SQL> show parameter ddl_lock_timeout

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
SQL> select name,value,con_id from v$system_parameter where name='ddl_lock_timeout';

NAME                 VALUE                    CON_ID
-------------------- -------------------- ----------
ddl_lock_timeout     0                             3

SQL> select name,open_mode,con_id from v$pdbs;

NAME                 OPEN_MODE      CON_ID
-------------------- ---------- ----------
PDBRAC               READ WRITE          3

SQL> select a.name,value$,con_id from pdb_spfile$ a join v$pdbs b on (a.pdb_uid=b.con_uid);

no rows selected

在cdb中查询

SQL> select a.name,value$,con_id from pdb_spfile$ a join v$pdbs b on (a.pdb_uid=b.con_uid);
NAME                           VALUE$                             CON_ID
------------------------------ ------------------------------ ----------
inmemory_size                  104857600                               3
ddl_lock_timeout               30                                      3

当pdb数据库open的时候PDB_SPFILE$,这个表可以用来查询到pdb的一些信息
SQL> desc pdb_spfile$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DB_UNIQ_NAME                              NOT NULL VARCHAR2(30)
 PDB_UID                                   NOT NULL NUMBER
 SID                                       NOT NULL VARCHAR2(80)
 NAME                                      NOT NULL VARCHAR2(80)
 VALUE$                                             VARCHAR2(4000)
 COMMENT$                                           VARCHAR2(255)
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             VARCHAR2(128)
此条目发表在Infrastructure分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s