ORACLE 12c之CDB与PDB

ORACLE 12C之CDB与PDB
北京时间,2013年6月26日,Oracle Database 12c版本正式发布,首先发布的版本号是12.1.0.1.0
Oracle Pluggable Database:Oracle PDB体系结构由一个容器数据库(CDB)和多个可组装式数据库(PDB)构成,PDB包含独立的系统表空间
和SYSAUX表空间等,但是所有PDB共享CDB的控制文件、日志文件和UNDO表空间。CDB可以包含零到多个PDBs。

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

查看是否是CDB结构的数据库,如果CDB列为NO,则说明不是CDB结构,创建PDB时,会报出ORA-65090: operation only allowed in a container database

SQL> select name ,cdb,open_mode from v$database;
NAME CDB OPEN_MODE
--------- --- --------------------
TRSEN YES READ WRITE
SQL> select pdb_id,pdb_name,dbid,status from dba_pdbs;
PDB_ID PDB_NAME DBID STATUS
---------- ------------------------- ---------- ---------
3 TRSEN_PDB1 632776444 NORMAL
2 PDB$SEED 2575298548 NORMAL
SQL> CREATE PLUGGABLE DATABASE trsen_pdb2 ADMIN USER trsen IDENTIFIED BY trsen
2 DEFAULT TABLESPACE tbs_trsen DATAFILE '/u02/app/trsen/oradata/trsen/trsen.dbf' SIZE 1024M AUTOEXTEND ON MAXSIZE UNLIMITED
3 FILE_NAME_CONVERT=('/u02/app/trsen/oradata/trsen/pdbseed','/u02/app/trsen/oradata/trsen/trsen_pdb2')
4 PATH_PREFIX='/u02/app/trsen/oradata/trsen/trsen_pdb2';
Pluggable database created.

SQL> select pdb_id,pdb_name,dbid,status from dba_pdbs;
PDB_ID PDB_NAME DBID STATUS
---------- ------------------------- ---------- ---------
3 TRSEN_PDB1 632776444 NORMAL
2 PDB$SEED 2575298548 NORMAL
4 TRSEN_PDB2 2479479521 NEW

目录结构

[trsen@vm010148 trsen]$ ls -l
总计 3036444
-rw-r----- 1 trsen oinstall 17973248 08-04 14:54 control01.ctl
drwxr-x--- 2 trsen oinstall 4096 08-04 14:19 pdbseed
-rw-r----- 1 trsen oinstall 52429312 08-04 14:54 redo01.log
-rw-r----- 1 trsen oinstall 52429312 08-04 14:33 redo02.log
-rw-r----- 1 trsen oinstall 52429312 08-04 14:34 redo03.log
-rw-r----- 1 trsen oinstall 671096832 08-04 14:53 sysaux01.dbf
-rw-r----- 1 trsen oinstall 828383232 08-04 14:50 system01.dbf
-rw-r----- 1 trsen oinstall 206577664 08-04 14:52 temp01.dbf
-rw-r----- 1 trsen oinstall 1073750016 08-04 14:52 trsen.dbf
drwxr-x--- 2 trsen oinstall 4096 08-04 14:29 trsen_pdb1==>在创建pdb库时,会在指定的文件目录下生成一个文件
drwxr-x--- 2 trsen oinstall 4096 08-04 14:52 trsen_pdb2
-rw-r----- 1 trsen oinstall 351281152 08-04 14:52 undotbs01.dbf
-rw-r----- 1 trsen oinstall 5251072 08-04 14:39 users01.dbf
[trsen@vm010148 trsen]$ cd pdbseed/
[trsen@vm010148 pdbseed]$ ls -l
总计 820088
-rw-r----- 1 trsen oinstall 104865792 08-04 14:24 pdbseed_temp012014-08-04_02-19-18-PM.dbf
-rw-r----- 1 trsen oinstall 576724992 08-04 14:34 sysaux01.dbf
-rw-r----- 1 trsen oinstall 262152192 08-04 14:34 system01.dbf
[trsen@vm010148 pdbseed]$ cd ../trsen_pdb1/
[trsen@vm010148 trsen_pdb1]$ ls -l
总计 2151332
-rw-r----- 1 trsen oinstall 1304174592 08-04 14:39 example01.dbf
-rw-r----- 1 trsen oinstall 5251072 08-04 14:39 SAMPLE_SCHEMA_users01.dbf
-rw-r----- 1 trsen oinstall 618668032 08-04 14:42 sysaux01.dbf
-rw-r----- 1 trsen oinstall 272637952 08-04 14:44 system01.dbf
-rw-r----- 1 trsen oinstall 206577664 08-04 14:29 trsen_pdb1_temp012014-08-04_02-29-05-PM.dbf
[trsen@vm010148 trsen]$ cd trsen_pdb2/
[trsen@vm010148 trsen_pdb2]$ ls -l
总计 820088
-rw-r----- 1 trsen oinstall 20979712 08-04 14:52 pdbseed_temp012014-08-04_02-19-18-PM.dbf
-rw-r----- 1 trsen oinstall 576724992 08-04 14:52 sysaux01.dbf
-rw-r----- 1 trsen oinstall 262152192 08-04 14:52 system01.dbf

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

切换到pdb的trsen_pdb1下

SQL> alter session set container=trsen_pdb1;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
TRSEN_PDB1

SQL> alter pluggable database trsen_pdb1 close immediate;
Pluggable database altered.

SQL> col pdb_name for a25;
SQL> select pdb_id,pdb_name,dbid,status from dba_pdbs;
PDB_ID PDB_NAME DBID STATUS
---------- ------------------------- ---------- ---------
3 TRSEN_PDB1 632776444 NORMAL
2 PDB$SEED 2575298548 NORMAL
4 TRSEN_PDB2 2479479521 NORMAL

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 2575298548 PDB$SEED READ ONLY
3 632776444 TRSEN_PDB1 MOUNTED
4 2479479521 TRSEN_PDB2 READ WRITE

SQL> alter session set container=trsen_pdb2;
Session altered.

在自己的pdb下也可以关闭pdb

SQL> alter pluggable database trsen_pdb2 close immediate;
Pluggable database altered.

在trsen_pdb2下通过视图v$pdbs只能查到自己所对的pdb

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
4 2479479521 TRSEN_PDB2 MOUNTED

将之前的之前close的pdb给open起来,如下操作,也可以在cdb$root下操作

SQL> alter session set container=trsen_pdb1;
Session altered.

SQL> alter pluggable database trsen_pdb1 open;
Pluggable database altered.

SQL> select con_id,dbid,name,open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
3 632776444 TRSEN_PDB1 READ WRITE

查看各个pdb的启动的状态及时间


SQL> SELECT NAME, CON_ID, DBID,open_mode,to_char(open_time,'yyyy-mm-dd hh24:mi:ss') open_time FROM V$CONTAINERS ORDER BY CON_ID;
NAME CON_ID DBID OPEN_MODE OPEN_TIME
-------------------- ---------- ---------- ---------- -------------------
CDB$ROOT 1 3688071478 READ WRITE 2014-08-04 14:27:38
PDB$SEED 2 2575298548 READ ONLY 2014-08-04 14:34:45
TRSEN_PDB1 3 632776444 READ WRITE 2014-08-04 16:51:39
TRSEN_PDB2 4 2479479521 MOUNTED 2014-08-04 16:40:39

SQL> alter pluggable database trsen_pdb2 open;
Pluggable database altered.

SQL> SELECT NAME, CON_ID, DBID,open_mode,to_char(open_time,'yyyy-mm-dd hh24:mi:ss') open_time FROM V$CONTAINERS ORDER BY CON_ID;
NAME CON_ID DBID OPEN_MODE OPEN_TIME
-------------------- ---------- ---------- ---------- -------------------
CDB$ROOT 1 3688071478 READ WRITE 2014-08-04 14:27:38
PDB$SEED 2 2575298548 READ ONLY 2014-08-04 14:34:45
TRSEN_PDB1 3 632776444 READ WRITE 2014-08-04 16:51:39
TRSEN_PDB2 4 2479479521 READ WRITE 2014-08-04 17:17:41

配置tns来访问pdb库的用户数据


SQL> alter session set container=trsen_pdb1;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
TRSEN_PDB1
SQL> conn scott/scott@trsen_pdb1==>tnsname名字与pdb名字相同
Connected.
SQL> select tname from tab;

TNAME
--------------------------------
BONUS
DEPT
EMP
SALGRADE

在不同的pdb下只能查到对应的自己库里的用户数据,正常情况下不能查到其他pdb的数据库,简单一点,可以利用dba_users/pdb_users这个视图,可验证这个问题