按照上篇流程创建好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 MOUNTEDSQL> 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 MOUNTEDSQL> 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 NOSQL> 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 = keywordSQL> 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=viSQL> edWrote 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