ORACLE 12C之CDB与PDB_oracle12c cdb和pdb-优快云博客

本文链接：https://blog.youkuaiyun.com/jingemperor/article/details/120137676

ORACLE 12C之CDB与PDB

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;