12 DBCA创建pdb的两种方法

最新推荐文章于 2024-01-10 15:16:38 发布

cuibaodun6837

最新推荐文章于 2024-01-10 15:16:38 发布

阅读量1k

点赞数

文章标签：数据库运维

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

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

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

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