通过种子创建

(

1 数据文件最大20G；

2 使用公共临时表空间最多10G；

3 创建pdb时同时新建一个默认表空间test（可选）；

4 以后pdb里的directory的创建到'/u01/oradata/cdb/pdb2/'下（可选）；

5 通过种子创建，数据文件的路径需要convert一下（如果设置了pdb_file_name_convert或db_file_name_convert，就可以不写FILE_NAME_CONVERT）；

)

6 其中admin user和file_name_convert是必须的，admin user后面可以跟roles来赋权；

(pdb可以指定自己的临时表空间，但是undo表空间只能用cdb的，也就是参数文件指定的)

SQL> create pluggable database pdb2 admin user ls identified by oracle roles=(dba)   2  storage (maxsize 20g max_shared_temp_size 10g)   3  default tablespace test   4  datafile '/u01/oradata/cdb/pdb2/test01.dbf' size 10m autoextend on   5  path_prefix = '/u01/oradata/cdb/pdb2/'   6  file_name_convert = ('pdbseed', 'pdb2'); Pluggable database created.

SQL> select * from dba_pdbs where PDB_NAME = 'PDB2';     PDB_ID PDB_NAME         DBID    CON_UID GUID                             STATUS    CREATION_SCN        VSN LOGGING   FOR FOR     CON_ID ---------- ---------- ---------- ---------- -------------------------------- --------- ------------ ---------- --------- --- --- ----------          4 PDB2        465958082  465958082 0ECFEA9579430AACE0530A010101360B  NEW            2139478  202375680 LOGGING   NO  NO           4

还需要挂载才能用

SQL> alter pluggable database pdb2 open; Pluggable database altered.

SQL> select * from dba_pdbs where PDB_NAME = 'PDB2';     PDB_ID PDB_NAME         DBID    CON_UID GUID                             STATUS    CREATION_SCN        VSN LOGGING   FOR FOR     CON_ID ---------- ---------- ---------- ---------- -------------------------------- --------- ------------ ---------- --------- --- --- ----------          4 PDB2        465958082  465958082 0ECFEA9579430AACE0530A010101360B  NORMAL         2139478  202375680 LOGGING   NO  NO           4

连接

SQL> conn ls/oracle@pdb2 Connected.

[oracle@db10 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PDB2 =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.10)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = pdb2)     )   )

[oracle@db10 ~]$ lsnrctl stat LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 11-FEB-2015 20:39:08 Copyright (c) 1991, 2014, Oracle.  All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date                11-FEB-2015 20:17:32 Uptime                    0 days 0 hr. 21 min. 36 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Log File         /u01/app/oracle/diag/tnslsnr/db10/listener/alert/log.xml Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db10)(PORT=1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=db10)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/cdb/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "cdb" has 1 instance(s).   Instance "cdb", status READY, has 1 handler(s) for this service... Service "cdbXDB" has 1 instance(s).   Instance "cdb", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s).   Instance "cdb", status READY, has 1 handler(s) for this service... Service "pdb2" has 1 instance(s).

  Instance "cdb", status READY, has 1 handler(s) for this service...

The command completed successfully

其它创建方式

复制cdb中的pdb

SQL> create pluggable database pdb2 from pdb1   2  file_name_convert = ('pdb1', 'pdb2'); Pluggable database created.

SQL> alter pluggable database pdb2 open; Pluggable database altered.

复制pdb同时去掉业务数据

SQL> create pluggable database pdb2 from pdb1 file_name_convert=('pdb1','pdb2')  no data; Pluggable database created. SQL> alter pluggable database pdb2 open; Pluggable database altered. SQL> conn ls@pdb2 Enter password: Connected. SQL> select count(*) from t;   COUNT(*) ----------          0

复制远程的pdb

1 创建dblink，可以是到远程cdb，也可以是到要复制的pdb

SQL> CREATE DATABASE LINK pdb1   2     CONNECT TO system IDENTIFIED BY oracle   3     USING 'pdb1';

2 复制

SQL> create pluggable database pdba from pdb1@pdb1 file_name_convert=('pdb1','pdba'); Pluggable database created.

SQL> alter pluggable database pdba open; Pluggable database altered.

复制其它数据库

如果源库的undo表空间和临时表空间和cdb不一样，新的pdb的临时表空间是用自己的，但是undo表空间是用cdb的参数文件指定的。

1 创建到源库的dblink

SQL> CREATE DATABASE LINK orcl   2     CONNECT TO system IDENTIFIED BY oracle   3     USING 'orcl'; Database link created.

2 将源库复制为pdb

SQL> create pluggable database pdb1   2    from NON$CDB@orcl   3    file_name_convert=('orcl','pdb1'); Pluggable database created.

3 打开

在第一次打开前一定要先执行

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

否则直接打开会报错

SQL> alter pluggable database pdb1 open; Warning: PDB altered with errors.

复制一个已经unplug的pdb

为了测试，先把现有的pdb unplug了

SQL> alter pluggable database pdb2 close; Pluggable database altered. SQL> alter pluggable database pdb2 unplug into '/u01/oradata/pdb2.xml'; Pluggable database altered. SQL> select pdb_name, status from dba_pdbs where pdb_name = 'PDB2'; PDB_NAME   STATUS ---------- --------- PDB2       UNPLUGGED SQL> drop pluggable database pdb2 keep datafiles; Pluggable database dropped. 如果过程中xml文件丢了，可以用dbms_pdb包来重建

SQL> begin   2   dbms_pdb.recover(pdb_descr_file=>'/u01/oradata/pdb2_recover.xml', pdb_name=>'pdb2',   3    filenames=>'/u01/oradata/pdb2/sysaux01.dbf,/u01/oradata/pdb2/system01.dbf,/u01/oradata/pdb2/users01.dbf');   4  end;   5  / PL/SQL procedure successfully completed. 复制 SQL> create pluggable database pdb2 using '/u01/oradata/pdb2.xml' FILE_NAME_CONVERT=('pdb2','new_pdb2'); Pluggable database created. 更简单的方法是不用复制，直接插上用

SQL> create pluggable database pdb2 using '/u01/oradata/pdb2.xml' nocopy; Pluggable database created.

也不是必须先unplug才能得到xml文件。在迁移pdb之前可以先预生成一个描述文件，然后在目标库上测试一下兼容性。

SQL> begin   2    dbms_pdb.describe(   3    pdb_descr_file => '/u01/oradata/pre_unplug_pdb2.xml',   4    pdb_name => 'pdb2');   5  end;   6  / PL/SQL procedure successfully completed.

SQL> !ls /u01/oradata/pre*xml /u01/oradata/pre_unplug_pdb2.xml

在目标cdb上验证兼容性

SQL> set serveroutput on SQL> declare   2      compatible constant varchar2(3) := case   3                                          dbms_pdb.check_plug_compatibility(pdb_descr_file => ' /u01/oradata/pre_unplug_pdb2.xml',   4                                                                            pdb_name       => ' pdbtest')   5                                             when true then   6                                              'yes'   7                                             else   8                                              'no'   9                                         end; 10  begin 11      dbms_output.put_line(compatible); 12  end; 13  / yes PL/SQL procedure successfully completed.

如果打印结果为no，可以查询PDB_PLUG_IN_VIOLATIONS视图查看原因

关于path_prefix

如果create pluggable database后面加了path_prefix，再创建directory的时候就可以只写文件夹名，文件夹就自动建到path_prefix下了。

SQL> create directory xxx as  'xxx'; Directory created. SQL> select directory_path from dba_directories where directory_name = 'XXX'; DIRECTORY_PATH ---------------------------------------------------------------------------------------------------- /u01/oradata/pdb2/xxx

查看当前连接的pdb

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') name FROM DUAL; NAME -------------------------------------------------- CDB$ROOT

SQL> conn ls @pdb1 Enter password: Connected.

SQL> select sys_context ('userenv', 'con_name') name from dual; NAME -------------------------------------------------- PDB1

或者更简单的

SQL> show con_id CON_ID ------------------------------ 3 SQL> show con_name CON_NAME ------------------------------ PDB1

还有一些列函数可以方便转换

CON_NAME_TO_ID() CON_DBID_TO_ID() CON_UID_TO_ID() CON_GUID_TO_ID()

批量执行SQL

set serverout on declare     c1       integer;     rowcount integer;     sql_txt varchar2(32767) := 'begin                                   delete test.t;

                                  insert into test.t select * from test.t;

                                  commit;                                 end;';     type t_pdbs is table of varchar2(128) index by pls_integer;     pdbs t_pdbs; begin     c1 := dbms_sql.open_cursor;     for i in pdbs.first .. pdbs.last loop         dbms_output.put_line('pdb: ' || pdbs(i));         dbms_sql.parse(c => c1,                        statement => sql_txt,                        language_flag => dbms_sql.native,                         schema => 'test',

                       container => pdbs(i));         rowcount := dbms_sql.execute(c => c1);     end loop;     dbms_sql.close_cursor(c => c1); end; /

查看所有container里的数据

当一个common user在多个container里有相同表和视图时，可以用containers()来一次查看所有数据。

SQL> conn c##ls/oracle Connected. SQL> create table dbf as select file_name from dba_data_files; Table created. SQL> conn c##ls @pdb1 Enter password: Connected. SQL> create table dbf as select file_name from dba_data_files; Table created. SQL> conn c##ls/oracle Connected. SQL> select * from dbf; FILE_NAME -------------------------------------------------------------------------------- /u01/oradata/cdb/system01.dbf /u01/oradata/cdb/sysaux01.dbf /u01/oradata/cdb/undotbs01.dbf /u01/oradata/cdb/users01.dbf SQL> select * from  containers(dbf); FILE_NAME                                                                            CON_ID -------------------------------------------------------------------------------- ---------- /u01/oradata/cdb/pdb1/system01.dbf                                                        3 /u01/oradata/cdb/pdb1/sysaux01.dbf                                                        3 /u01/oradata/cdb/pdb1/test.dbf                                                            3 /u01/oradata/cdb/system01.dbf                                                             1 /u01/oradata/cdb/sysaux01.dbf                                                             1 /u01/oradata/cdb/undotbs01.dbf                                                            1 /u01/oradata/cdb/users01.dbf                                                              1 7 rows selected.

SQL> select * from containers(dbf) where  con_id = 3; FILE_NAME                                                                            CON_ID -------------------------------------------------------------------------------- ---------- /u01/oradata/cdb/pdb1/system01.dbf                                                        3 /u01/oradata/cdb/pdb1/sysaux01.dbf                                                        3 /u01/oradata/cdb/pdb1/test.dbf                                                            3

删除

SQL> alter pluggable database pdb2 close; Pluggable database altered.

SQL> drop pluggable database pdb2 including datafiles; Pluggable database dropped.