Oracle 12c 新特性之 Multitenant Architecture (四)

按照上篇流程创建好CDB后，就可以往里面添加PDB了，可以使用如下几种方式：

a)   以SEED为模板创建PDB

b)   以一个已经存在的PDB为模板，创建新的PDB

c)   导入一个unplugged PDB

d)   使用一个non-CDB来创建一个PDB

   当然最简单的方式还是用DBCA，点击几下 next 就可以了，这里我们使用SQL来创建，其实也不麻烦。切记一个CDB环境最多只包含253个PDB哦，而且包含SEED在内。不管是以SEED或者已有PDB为模板创建PDB，其实都是将源库的数据文件拷贝到新位置的过程。

   对于创建PDB的语句，有几点注意的地方，如果指定了STORAGE子句，MAXSIZE指定一个PDB所有表空间能占用多少存储空间，可以用UNLIMITED指定无限制，MAX_SHARED_TEMP_SIZE指定该PDB可以使用多少共享临时表空间，也可以设置成UNLIMITED代表不限制。

a)   以SEED为模板创建PDB

SQL> l
  1  CREATE PLUGGABLE DATABASE newpdb2 ADMIN USER pdbuser1 IDENTIFIED BY pdbpass1
  2    STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  3    DEFAULT TABLESPACE dtbs
  4       DATAFILE '/u01/oracle/oradata/newcdb/newpdb2/dtbs01.dbf' SIZE 250M AUTOEXTEND ON
  5    PATH_PREFIX = '/u01/oracle/oradata/newcdb/newpdb2/'
  6    FILE_NAME_CONVERT = ('/u01/oracle/oradata/newcdb/pdbseed/',
  7*                          '/u01/oracle/oradata/newcdb/newpdb2/')
SQL> /
Pluggable database created.
SQL> show pdbs;
    CON_ID CON_NAME                           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
          2 PDB$SEED                          READ ONLY  NO
          3 NEWPDB1                           READ WRITE NO
          4 NEWPDB2                           MOUNTED
SQL> alter pluggable database newpdb2 open;
Pluggable database altered.
SQL> show pdbs;
    CON_ID CON_NAME                           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
          2 PDB$SEED                          READ ONLY  NO
          3 NEWPDB1                           READ WRITE NO
          4 NEWPDB2                           READ WRITE NO

b)   以一个已经存在的PDB为模板，创建新的PDB

SQL> alter pluggable database newpdb2 close;
Pluggable database altered.
SQL> alter pluggable database newpdb2 open read only;
Pluggable database altered.
SQL> CREATE PLUGGABLE DATABASE newpdb3 FROM newpdb2
  2    PATH_PREFIX = '/u01/oracle/oradata/newcdb/newpdb3/'
  3    FILE_NAME_CONVERT = ('/u01/oracle/oradata/newcdb/newpdb2/',
  4                         '/u01/oracle/oradata/newcdb/newpdb3/');
Pluggable database created.
SQL> alter pluggable database newpdb3 open;
Pluggable database altered.
SQL> show pdbs;
    CON_ID CON_NAME                      OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
    2 PDB$SEED                          READ ONLY  NO
    3 NEWPDB1                           READ WRITE NO
    4 NEWPDB2                           READ ONLY  NO
    5 NEWPDB3                           READ WRITE NO

c)   导入一个unplugged PDB

首先创建一个unplugged PDB，然后以一个新的PDB导入系统

SQL> alter pluggable database newpdb2 close;
Pluggable database altered.
SQL> alter pluggable database newpdb2 unplug into '/tmp/newpdb2.xml';
Pluggable database altered.
SQL> show pdbs;
    CON_ID CON_NAME                    OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
  2 PDB$SEED                          READ ONLY  NO
  3 NEWPDB1                           READ WRITE NO
  4 NEWPDB2                           MOUNTED
SQL> drop pluggable database newpdb2 keep datafiles;
Pluggable database dropped.
SQL> show pdbs;
    CON_ID CON_NAME                    OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
  2 PDB$SEED                          READ ONLY  NO
  3 NEWPDB1                           READ WRITE NO
SQL> create pluggable database newpdb3 using '/tmp/newpdb2.xml'
  2  source_file_name_convert=('/u01/oracle/oradata/newcdb/newpdb2','/u01/oracle/oradata/newcdb/newpdb2')
  3  copy
  4  file_name_convert('/u01/oracle/oradata/newcdb/newpdb2','/u01/oracle/oradata/newcdb/newpdb3')
  5  storage (maxsize unlimited max_shared_temp_size unlimited)
  6  /
file_name_convert('/u01/oracle/oradata/newcdb/newpdb2','/u01/oracle/oradata/newcdb/newpdb3')
                 *
ERROR at line 4:
ORA-02000: missing = keyword
SQL> l
  1  create pluggable database newpdb3 using '/tmp/newpdb2.xml'
  2  source_file_name_convert=('/u01/oracle/oradata/newcdb/newpdb2','/u01/oracle/oradata/newcdb/newpdb2')
  3  copy
  4  file_name_convert('/u01/oracle/oradata/newcdb/newpdb2','/u01/oracle/oradata/newcdb/newpdb3')
  5* storage (maxsize unlimited max_shared_temp_size unlimited)
SQL> define _editor=vi
SQL> ed
Wrote file afiedt.buf
  1  create pluggable database newpdb3 using '/tmp/newpdb2.xml'
  2 source_file_name_convert=('/u01/oracle/oradata/newcdb/newpdb2',
'/u01/oracle/oradata/newcdb/newpdb2')
  3  copy
  4 file_name_convert=('/u01/oracle/oradata/newcdb/newpdb2',
'/u01/oracle/oradata/newcdb/newpdb3')
  5* storage (maxsize unlimited max_shared_temp_size unlimited)
SQL> /
Pluggable database created.
SQL> alter pluggable database newpdb3 open;
Pluggable database altered.
SQL> show pdbs;
    CON_ID CON_NAME                    OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
  2 PDB$SEED                          READ ONLY  NO
  3 NEWPDB1                           READ WRITE NO
  4 NEWPDB3                           READ WRITE NO