12c新推出的pdb是一种全新的数据库管理模式，有别于传统的数据库。今天的实验室是使用dbca图形界面创建一个pdb，再用dbca的静默方式创建一个pdb。
pdb是可插拔数据库，当然也就离不开cdb。c就是container,容器。
环境变量如下
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1
export ORACLE_SID=CDB
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
在这里如果有报错Error in Process:/u01/app/oracle/product/12.1.0/db_1/perl/bin/perl
可参见我的另一篇博客http://blog.itpub.net/29047826/viewspace-1434056/
通过SQL PLUS连接根库
[oracle@snow ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 8 16:11:18 2015
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
查看pdb状态，已经是open(read write)
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
图形安装后包含了根库CDB$ROOT，con_id为1，包含种子库PDB$SEED，con_id为2，还包含本次创建的可插拔库pdb1，con_id为3
SQL> select con_id,name from v$containers;
CON_ID NAME
---------- ------------------------------
1 CDB$ROOT
2 PDB$SEED
3 PDB1
查看cdb_data_files数据字典可以看到cdb的数据文件和pdb的数据文件
SQL> col file_name for a60
SQL> set line 120
SQL> select con_id,file_name from cdb_data_files order by 1;
CON_ID FILE_NAME
---------- ------------------------------------------------------------
1 /u01/app/oracle/oradata/CDB/system01.dbf
1 /u01/app/oracle/oradata/CDB/users01.dbf
1 /u01/app/oracle/oradata/CDB/undotbs01.dbf
1 /u01/app/oracle/oradata/CDB/sysaux01.dbf
2 /u01/app/oracle/oradata/CDB/pdbseed/sysaux01.dbf
2 /u01/app/oracle/oradata/CDB/pdbseed/system01.dbf
3 /u01/app/oracle/oradata/CDB/pdb1/sysaux01.dbf
3 /u01/app/oracle/oradata/CDB/pdb1/pdb1_users01.dbf
3 /u01/app/oracle/oradata/CDB/pdb1/system01.dbf
如果切换到pdb，就只能看到属于自己的数据文件
SQL> alter session set container=pdb1;
SQL> col name for a60
SQL> select con_id,name from v$datafile;
CON_ID NAME
---------- ------------------------------------------------------------
0 /u01/app/oracle/oradata/CDB/undotbs01.dbf
3 /u01/app/oracle/oradata/CDB/pdb1/system01.dbf
3 /u01/app/oracle/oradata/CDB/pdb1/sysaux01.dbf
3 /u01/app/oracle/oradata/CDB/pdb1/pdb1_users01.dbf
由于我的虚拟机空间较小，先用dbca删除刚才创建的cdb和pdb再执行下面的脚本。注意dbca删除数据库后并不清理磁盘上的物理文件，需要手动删除比如 rm -rf $ORACLE_BASE/oradata/CDB，这个命令只是一个事例请勿模仿。
下面这条dbca - silent … 语句是一条完整的语句，不换行。 里面的关键字是createAsContainerDatabase true,如果没有该关键字创建出来的就是一个普通的数据库，而不是我们此次需要的CDB。
该命令执行后，输出Copying database files ..1% complete
[oracle@snow ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname CDB -sid CDB -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration LOCAL -createAsContainerDatabase true -sysPassword oracle -systemPassword oracle
Copying database files
1% complete
3% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
46% complete
47% complete
52% complete
57% complete
58% complete
59% complete
62% complete
Completing Database Creation
66% complete
70% complete
74% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/CDB/CDB.log" for further details.
到此为止CDB创建完成，通过下面的sql语句查看其内容。
[oracle@snow ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 8 16:51:36 2015
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 pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL>
SQL> select con_id,name from v$containers;
CON_ID NAME
---------- ------------------------------
1 CDB$ROOT
2 PDB$SEED
SQL> col file_name for a60
SQL> select con_id,file_name from cdb_data_files order by 1;
CON_ID FILE_NAME
---------- ------------------------------------------------------------
1 /u01/app/oracle/oradata/CDB/system01.dbf
1 /u01/app/oracle/oradata/CDB/users01.dbf
1 /u01/app/oracle/oradata/CDB/undotbs01.dbf
1 /u01/app/oracle/oradata/CDB/sysaux01.dbf
2 /u01/app/oracle/oradata/CDB/pdbseed/sysaux01.dbf
2 /u01/app/oracle/oradata/CDB/pdbseed/system01.dbf
下面通过种子容器创建pdb
[oracle@snow ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 8 16:59:03 2015
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> create pluggable database pdb1 admin user pdb1adm identified by oracle file_name_convert=('/u01/app/oracle/oradata/CDB/pdbseed','/u01/app/oracle/oradata/CDB/pdb1');
Pluggable database created.
此时pdb的状态是mounted
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
下面的额查询语句只有跟库的信息，无法显示pdb的信息
SQL> select con_id,file_name from cdb_data_files order by 1;
CON_ID FILE_NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/CDB/system01.dbf
1 /u01/app/oracle/oradata/CDB/sysaux01.dbf
1 /u01/app/oracle/oradata/CDB/undotbs01.dbf
1 /u01/app/oracle/oradata/CDB/users01.dbf
2 /u01/app/oracle/oradata/CDB/pdbseed/sysaux01.dbf
2 /u01/app/oracle/oradata/CDB/pdbseed/system01.dbf
通过下面的命令将所有的pdb库open
SQL> alter pluggable database all open;
Pluggable database altered.
此时pdb的状态有mount变成了read write
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
再次执行下面的查询就可以看到pdb的数据文件了
SQL> select con_id,file_name from cdb_data_files order by 1;
CON_ID FILE_NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/CDB/system01.dbf
1 /u01/app/oracle/oradata/CDB/users01.dbf
1 /u01/app/oracle/oradata/CDB/undotbs01.dbf
1 /u01/app/oracle/oradata/CDB/sysaux01.dbf
2 /u01/app/oracle/oradata/CDB/pdbseed/system01.dbf
2 /u01/app/oracle/oradata/CDB/pdbseed/sysaux01.dbf
3 /u01/app/oracle/oradata/CDB/pdb1/system01.dbf
3 /u01/app/oracle/oradata/CDB/pdb1/sysaux01.dbf
本次通过dbca图形化和静默方式创建cdb+pdb的方法演示完毕。
706
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
