Creating PDBs with SQL*Plus

（一）Techniques for Creating a PDB

创建新pdb会复制源库所有临时表空间，包括非默认表空间

Users in the PDB who used the default temporary tablespace of the source non-CDB or PDB use the default temporary tablespace of the cloned PDB. Users who used non-default temporary tablespaces in the non-CDB or PDB continue to use the same local temporary tablespaces in the cloned PDB.

• Creating a PDB Using the Seed

You can use the CREATE PLUGGABLE DATABASE statement to create a new PDB by using the files of the CDB seed or an application PDB from the files of an application seed or the CDB seed.

The statement copies these files to a new location and associates them with the new PDB. 

如果当前容器为root则使用PDB$SEED创建PDB，如果当前容器为APPLICATION ROOT则使用application seed创建application pdb, 如果此时application没有seed则使用PDB$SEED.

使用此方式创建PDB需要指定PDB local admin user, 此用户会自动创建并授予pdb_dba role.

注:如果seed使用的OMF，而创建pdb不想使用omf，可以使用file_name_convert进行转化，但只转化目录会报错，需要把所有数据文件均转化：

CREATE PLUGGABLE DATABASE tcymob1 ADMIN USER pdb_mgr IDENTIFIED BY oracle

file_name_convert=(

'/U01/app/oracle/oradata/new12c/NEW12C/4F502D1B1D9DAAE0E0537D857F0A4C6D/datafile/o1_mf_sysaux_dkbv7d16_.dbf','/U01/app/oracle/oradata/new12c/NEW12C/tcymob1/sysaux01.dbf',

'/U01/app/oracle/oradata/new12c/NEW12C/4F502D1B1D9DAAE0E0537D857F0A4C6D/datafile/o1_mf_system_dkbv79rp_.dbf','/U01/app/oracle/oradata/new12c/NEW12C/tcymob1/system01.dbf',

'/U01/app/oracle/oradata/new12c/NEW12C/4F502D1B1D9DAAE0E0537D857F0A4C6D/datafile/o1_mf_temp_dkbv7dv8_.tmp','/U01/app/oracle/oradata/new12c/NEW12C/tcymob1/temp01.dbf',

'/U01/app/oracle/oradata/new12c/NEW12C/4F502D1B1D9DAAE0E0537D857F0A4C6D/datafile/o1_mf_users_dkbv7f2q_.dbf','/U01/app/oracle/oradata/new12c/NEW12C/tcymob1/users01.dbf'

);

（三）Creating a PDB by Cloning an Existing PDB or Non-CDB

The source is the existing PDB or non-CDB that is copied. The target PDB is the clone of the source. The source can be a PDB in the local CDB, a PDB in a remote CDB, or a non-CDB. The CREATE PLUGGABLE DATABASE statement copies the files associated with the source to a new location and associates the files with the target PDB.

如果源PDB在远程CDB中，需要在本地的CDB中创建dblink,连接到远程CDB或远程CDB要复制的PDB中。如果源为non-CDB，同样需要使用dblink

3.1 Cloning a Local PDB

After cloning a local PDB, the source and target PDBs are in the same CDB.

1. The source PDB cannot be closed.
2. If the CDB is not in local undo mode, then the source PDB must be in open read-only mode.
3. If the CDB is not in ARCHIVELOG mode, then the source PDB must be in open read-only mode.
4. If the database character set of the CDB is AL32UTF8, then the character set and national character set of the application container can be different from the CDB. However, all application PDBs in an application container must have same character set and national character set, matching that of the application container.
5. If an error is returned during PDB creation, you can check a PDB's state by querying the CDB_PDBS or DBA_PDBS view, and you can learn more about PDB creation errors by checking the alert log. An unusable PDB can only be dropped.

SQL> show con_name;   

SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;

--nologging:pdb创建的表空间默认为Nologging

--no data: 只复制元信息没有实际数据

SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1

  PATH_PREFIX = '/disk2/oracle/pdb2/'

  FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/')

  STORAGE (MAXSIZE 2G)

  SERVICE_NAME_CONVERT = ('salesrep_ca','salesrep_or','orders_ca','orders_or')

  NOLOGGING

NO DATA;

3.2 Cloning a Remote PDB or Non-CDB

1. The source PDB cannot be closed.
2. When cloning a PDB, if the remote CDB is not in local undo mode, then the source PDB must be in open read-only mode.
3. If the remote CDB or non-CDB is not in ARCHIVELOG mode, then the source PDB or non-CDB must be in open read-only mode.
4. A database link must enable a connection from the CDB to which the PDB is being cloned to the source PDB’s CDB. The database link can connect to either the root of the CDB, to an application PDB if the source is an application PDB, or to the PDB. 

Db_link是从目标cdb$root连接到源cdb$root或源pdb或源application pdbs

1. The user that the database link connects with must have the CREATE PLUGGABLE DATABASE system privilege.
2. If the database link connects to the root in the source PDB’s CDB, then the user that the database link connects with must be a common user.
3. The source and target platforms must meet these requirements:

They must have the same endianness.

The database options installed on the source platform must be the same as, or a subset of, the database options installed on the target platform.

1. If the character set of the CDB to which the PDB is being cloned is not AL32UTF8, then the source and target must have compatible character sets and national character sets. To be compatible, the character sets and national character sets must meet all of the requirements specified in Oracle Database Globalization Support Guide.

If the character set of the CDB to which the PDB is being cloned is AL32UTF8, then this requirement does not apply.

1. If you create a PDB by cloning a non-CDB, then you must meet the following requirements:

The CDB and the non-CDB must be running Oracle Database 12c Release 1 (12.1.0.2) or later.

The data block size of the cloned PDB must match the CDB in which the clone PDB will reside.

1. If you are creating an application PDB, then the application name and version of the source PDB must match the application name and version of the application container that will contain the target application PDB.
2. If you are creating an application PDB, then the application PDB must have the same character set and national character set as the application container.
3. If the database character set of the CDB is AL32UTF8, then the character set and national character set of the application container can different from the CDB. However, all application PDBs in an application container must have same character set and national character set, matching that of the application container.

If you created the PDB from a non-CDB, then run the ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script. This script must be run before the PDB can be opened for the first time.

To run the noncdb_to_pdb.sql script, complete the following steps:

Access the PDB.

The current user must have SYSDBA administrative privilege, and the privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS SYSDBA at connect time.

Run the noncdb_to_pdb.sql script:

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

The script opens the PDB, performs changes, and closes the PDB when the changes are complete.

Open the new PDB in read/write mode.

SQL> show con_name;   

SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1@pdb1_link;

--clone a remote non-CDB

SQL> CREATE PLUGGABLE DATABASE pdb2 FROM mydb@mydb_link;

--When the source is a non-CDB, you can substitute NON$CDB for the name of the non-CDB. For example, the following statement is equivalent to the previous example:

SQL> CREATE PLUGGABLE DATABASE pdb2 FROM NON$CDB@mydb_link;

3.3 CLONE PDB USING SNAPSHOT COPY

没看懂，用时再说

When you use the SNAPSHOT COPY clause, all of the data files of the source PDB must be stored in the same storage type.

When you use the SNAPSHOT COPY clause to create a clone of a source PDB and the CLONEDB initialization parameter is set to FALSE, the underlying file system for the source PDB's files must support storage snapshots. Such file systems include Oracle Automatic Storage Management Cluster File System (Oracle ACFS) and Direct NFS Client storage. Exadata supports snapshot copy functionality on ASM configured with sparse ASM grid disks.

When you use the SNAPSHOT COPY clause to create a clone of a source PDB and the CLONEDB initialization parameter is set to TRUE, the underlying file system for the source PDB's files can be any local file system, network file system (NFS), or clustered file system that has Direct NFS enabled and supports sparse files. However, when the CLONEDB initialization parameter is set to TRUE, the source PDB must remain in open read-only mode as long as any clones exist.

Direct NFS Client enables an Oracle database to access network attached storage (NAS) devices directly, rather than using the operating system kernel NFS client. If the PDB's files are stored on Direct NFS Client storage, then the following additional requirements must be met:

1. The source PDB's files must be located on an NFS volume.
2. Storage credentials must be stored in a Transparent Data Encryption keystore.
3. The storage user must have the privileges required to create and destroy snapshots on the volume that hosts the source PDB's files.
4. Credentials must be stored in the keystore using an ADMINISTER KEY MANAGEMENT ADD SECRET SQL statement. The following example configures an Oracle Database secret in a software keystore:

ADMINISTER KEY MANAGEMENT  

  ADD SECRET 'secret' FOR CLIENT 'client_name'

  USING TAG 'storage_user'

  IDENTIFIED BY  keystore_password  WITH BACKUP;

Run this statement to add a separate entry for each storage server in the configuration. In the previous example, the following values must be specified:

secret is the storage password.

client_name is the storage server. On a Linux or UNIX platform, it is the name entered in /etc/hosts or the IP address of the storage server.

tag is the username passed to the storage server.

keystore_password is the password for the keystore.

See Oracle Database Advanced Security Guide for more information about managing keystores and secrets.

PDB clones created using the SNAPSHOT COPY clause cannot be unplugged. They can only be dropped.

Storage clones are named and tagged using the destination PDB's GUID. You can query the CLONETAG column of DBA_PDB_HISTORY view to view clone tags for storage clones.

3.4 Refreshable Clone PDB

新PDB会从旧PDB refresh changes, 可以使用ALTER PLUGGABLE DATABASE切换refresh mode, 可以在manual/auto间相互切换，但如果切换为none就不能再切回来了(A PDB that is not refreshable cannot be changed into a refreshable PDB)

Auto refresh mode下同样可以使用manual(alter pluggable database sql)来刷新

Note：

1. A refreshable PDB and its source must be in different CDBs. Therefore, a refreshable PDB must be created at a CDB that is remote from the source CDB, and a database link must be specified during the creation.
2. A refreshable PDB can be opened only in open read-only mode. Also, a refreshable PDB must be closed when a refresh is performed. If it is not closed when automatic refresh is attempted, then the refresh is deferred until the next scheduled refresh. If it is not closed when a user attempts to perform manual refresh, then an error is reported.
3. When you create a refreshable PDB, you can set the REMOTE_RECOVERY_FILE_DEST initialization parameter in the PDB. This initialization parameter specifies a directory from which to read archive log files during refresh operations if the source PDB is not available over its database link.
4. If new data files are created in the source PDB, then the PDB_FILE_NAME_CONVERT initialization parameter must be set in the CDB to convert the data file paths from the source PDB to the clone PDB.
5. If you are creating a refreshable PDB, then the source PDB must be in ARCHIVELOG mode and local undo mode.

（五）Creating a PDB by Relocating It

This technique moves a PDB from one CDB to another.

To use this technique, you run the CREATE PLUGGABLE DATABASE statement and include the FROM clause and the RELOCATE clause.

The CREATE PLUGGABLEDATABASE statement also moves the files associated with the PDB to a new location.

Relocate实现迁移PDB 零停机过程：

1. 在relocate过程中，source pdb不受影响正常提供服务，在reloated后source pdb仍为read write并提供服务, 这个过程实现了hot clone.此时新pdb处于mount，此时应该是不会apply redo log来同步的
2. 新pdb open时，源pdb会被quiesces(不能DML,DDL,只能查询)，并传输日志给新pdb，新pdb应用到最新
3. 最终新pdb open(同时回滚源pdb quiesces时中未完成的事务), source pdb自动drop，数据文件会被自动删除

In addition, the PDB being relocated can be opened in read/write mode and fully functional during the relocation process. 

This technique automatically quiesces the PDB in its old location while transporting and applying redo data to the PDB in its new location.

Finally, when the PDB is ready, this technique brings it online in its new location.

While the PDB is being relocated, current DML and DDL operations will pause while they are redirected to the PDB’s new location. Queries continue to run with no pause.

Relocate availability: AVAILABILITY MAX and AVAILABILITY NORMAL.

如果配置了availability max则会自动设置原来监听来分配连接到新监听上，如果源cdb和目标cdb使用相同listener则使用availablility normal即可

When the AVAILABILITY MAX clause is specified, the relocate operation configures the listener to relocate connections to the new location. You have the following options for redirecting connections from the PDB’s old location to the PDB’s new location:

1. If your system is using Oracle Internet Directory (Oracle’s LDAP directory service), then connection information can be updated in a central location rather than in multiple client configuration files.
2. If you use the same listener for the PDB in its old and new locations, then new connections are automatically routed to the PDB’s new location when relocation is complete.
3. If the PDBs use different listeners, and you employ cross-registration of their respective listeners through configuration of the local_listener and remote_listener parameters, then relocation is seamless无缝 because the availability and location of the PDB’s services are automatically registered with the listeners.

Note: In an Oracle Clusterware environment, when relocating a PDB between different CDBs (not CDB instances of the same database), the non-default service resource must be recreated using SRVCTL.

When the source and target CDBs for the relocate operation do not share a common listener network, use the AVAILABILITY MAX clause to automatically configure the original listener to forward connections to the new listener that is handling the relocated PDB’s connections. This configuration is intended to be transient while the Oracle Internet Directory (OID) is updated or the client connections are modified. A “tombstone” PDB remains in the source CDB to protect the PDB’s namespace and preserve the listener forwarding configuration until the OID server or the client connections are updated. After that is completed, the source PDB can be dropped with a DROP PLUGGABLE DATABASE statement.

If the source CDB and target CDB for the relocation operation share a common listener network, forwarding client connections is not necessary as it is handled implicitly by the common listener network. In this case, use the AVAILABILITY NORMAL clause for the relocate operation. After the PDB is relocated to the target CDB and opened, the PDB on the source CDB is dropped.

If a local listener redirects to a Single Client Access Name (SCAN) listener in an Oracle RAC configuration, then this listener may need to further redirect the client connection request to another cluster node. Multiple redirects are not supported by Oracle Net listeners by default. Because any SCAN listener can route the connection request to any node, set the ALLOW_MULTIPLE_REDIRECTS_listener_name parameter to the listener_name of every SCAN listener, and set it in every listener.ora file in the cluster. For example, if the SCAN listeners are named listener_scan1, listener_scan2, and listener_scan3, then the listener.ora file on every destination host should have the following settings:

ALLOW_MULTIPLE_REDIRECTS_LISTENER_SCAN1=YES

ALLOW_MULTIPLE_REDIRECTS_LISTENER_SCAN2=YES

ALLOW_MULTIPLE_REDIRECTS_LISTENER_SCAN3=YES

Caution: Do not set the ALLOW_MULTIPLE_REDIRECTS_listener_name parameter for node listeners because it may allow infinite redirection loops in certain network configurations.

Relocating a PDB

Note:

1. The CDB that contains the PDB before relocation must be in local undo mode.
2. If the CDB to which the PDB is being relocated is not in ARCHIVELOG mode, then the PDB must be in open read-only mode during the operation.
3. The following prerequisites apply to the database link:

A database link must enable a connection from the CDB to which the PDB is being relocated to the PDB’s current CDB. The database link must connect to the root of the CDB. If the PDB is an application PDB, then the database link must connect to its application root.

The user that the database link connects with in the PDB’s current CDB must have either the CREATE PLUGGABLE DATABASE system privilege or the SYSOPER administrative privilege.

If the database link connects to the CDB root in the PDB’s current CDB, then the user that the database link connects with must be a common user.

1. The platforms of the PDB’s current CDB and the CDB to which it is being relocated must meet these requirements:

They must have the same endianness.

The database options installed on the source platform must be the same as, or a subset of, the database options installed on the target platform.

1. If the character set of the CDB to which the PDB is being relocated is not AL32UTF8, then the PDB’s current CDB and the CDB to which it is being relocated must have compatible character sets and national character sets. To be compatible, the character sets and national character sets must meet all of the requirements specified in Oracle Database Globalization Support Guide.

If the character set of the CDB to which the PDB is being relocated is AL32UTF8, then this requirement does not apply.

1. If you are creating an application PDB, then the application name and version of the PDB being relocated must match the application name and version of the application container that will contain the application PDB after it is relocated.

SQL> show con_name;   

SQL> CREATE PUBLIC DATABASE LINK mycdb CONNECT TO c##myadmin IDENTIFIED BY password USING 'MYCDB';

SQL> CREATE PLUGGABLE DATABASE pdb1 FROM pdb1@mycdb RELOCATE AVAILABILITY MAX;

实测：

下面我在本地创建orcl2的数据库，开启归档，创建pdborcl2作为源库

创建两个监听：

LISTENER =

 (DESCRIPTION =

   (ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521))

 )

LISTENER1 =

 (DESCRIPTION =

   (ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1522))

 )

通过local_listenr配置orcl使用1521， orcl2使用1522

alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))';

alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1522))';

lsnrctl start listener

lsnrctl start listener1

在orcl2创建用户(实测用system权限不够)：

create user c##admin identified by oracle container=all;

grant create pluggable database to c##admin container=all;

grant create session, connect, resource, cdb_dba, sysoper to c##admin container=all;

mkdir -p /u01/app/oracle/oradata/orcl/pdb6

show con_name;   

create database link link_orcl2 connect to c##admin identified by oracle using    

    '(DESCRIPTION =

             (ADDRESS_LIST =

                (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1522)))

                (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl2))

      )';

CREATE PLUGGABLE DATABASE pdb6 FROM pdborcl2@link_orcl2 RELOCATE   

  PATH_PREFIX = '/u01/app/oracle/pdb6/'  

  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl2/pdborcl2/', '/u01/app/oracle/oradata/orcl/pdb6/')

  STORAGE (MAXSIZE 2G)

  TEMPFILE REUSE

  DEFAULT TABLESPACE sales;

不太清楚使用AVAILABILITY选项不能创建，ORA-65348: unable to create pluggable database, 去掉能建，网上没信息，可能是bug

上面这个过程直到open pdb6, pdborcl2是可以正常操作的，执行结束后pdborcl2为read write, pdb6为mount

select pdb_name, status from dba_pdbs;   --target pdb为relocating status

在source pdb操作：

create table t1(id number);

insert into t1 values(111111);

create table t2(id number);

insert into t2 values(222222);   ---未commit

open pdb6:

Alter pluggable database pdb6 open;      ----结果pdborcl2直接删除, pdb6为read write

alter session set container=pdb6;

select * from t1;   ---有数据

select * from t2;  ----没数据

select NAME from dba_services;   --pdb6

show parameter listener

NAME             TYPE       VALUE

------------------------------------ ---------- ------------------------------

local_listener       string     (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1522))

remote_listener     string

可见service名添加，只是动态注册到了source pdb的监听，所以还是建议使用自定义服务，然后创建时使用SERVICE_NAME_CONVERT = ('pdbtest1','pdbtest3')

（六）Creating a PDB by Plugging an Unplugged PDB into a CDB

This technique uses the XML metadata file that describes the PDB and the files associated with the PDB to plug it into the CDB. These files can be separate, or they can be included in a single, compressed .pdb archive file.

The USING clause of the CREATE PLUGGABLE DATABASE statement specifies the XML metadata file or the .pdb archive file.

Note: Automatic downgrade of a PDB is not supported. Therefore, you cannot plug in a PDB if the source CDB is a higher Oracle Database release than the target CDB.

1. Plugging In an Unplugged PDB

Note:

1. Either the XML file that describes the PDB or the .pdb archive file must exist in a location that is accessible to the CDB.
2. If the PDB's XML file is unusable or cannot be located, then you can use the DBMS_PDB.RECOVER procedure to generate an XML file using the PDB's data files.
3. If an XML file is specified in the USING clause (not a .pdb file), then the files associated with the PDB (such as the data files and wallet file) must exist in a location that is accessible to the CDB.
4. The source and target CDB platforms must meet the following requirements:

They must have the same endianness.

The database options installed on the source platform must be the same as, or a subset of, the database options installed on the target platform.

1. If the character set of the CDB into which the PDB is being plugged is not AL32UTF8, then the CDB that contained the unplugged PDB and the target CDB must have compatible character sets and national character sets. To be compatible, the character sets and national character sets must meet all of the requirements specified in Oracle Database Globalization Support Guide.

If the character set of the CDB into which the PDB is being plugged is AL32UTF8, then this requirement does not apply.

1. If you are creating an application PDB, then the application name and version of the unplugged PDB must match the application name and version of the application container into which the application PDB is being plugged.
2. If you are creating an application PDB, then the application PDB must have the same character set and national character set as the application container.
3. If the database character set of the CDB is AL32UTF8, then the character set and national character set of the application container can be different from the CDB. However, all application PDBs in an application container must have same character set and national character set, matching that of the application container
4. You can use the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether these requirements are met.

过程：

1. (Optional) Run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether the unplugged PDB is compatible with the CDB.

1. If the PDB is not yet unplugged, then run the DBMS_PDB.DESCRIBE procedure to produce an XML file that describes the PDB.

If the PDB is already unplugged, then proceed to Step 2.b.

For example, to generate an XML file named salespdb.xml in the /disk1/oracle directory, run the following procedure:

BEGIN

  DBMS_PDB.DESCRIBE(

    pdb_descr_file => '/disk1/oracle/salespdb.xml',

    pdb_name       => 'SALESPDB');

END;

/

If the PDB is in a remote CDB, then you can include @database_link_name in the pdb_name parameter, where database_link_name is the name of a valid database link to the remote CDB or to the PDB. For example, if the database link name to the remote CDB is rcdb, then set the pdb_name value to SALESPDB@rcdb.

1. Run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function.

When you run the function, set the following parameters:

- pdb_descr_file - Set this parameter to the full path to the XML file.

- pdb_name - Specify the name of the new PDB. If this parameter is omitted, then the PDB name in the XML file is used.

For example, to determine whether a PDB described by the /disk1/usr/salespdb.xml file is compatible with the current CDB, run the following PL/SQL block:

SET SERVEROUTPUT ON

DECLARE

  compatible CONSTANT VARCHAR2(3) :=

    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

           pdb_descr_file => '/disk1/usr/salespdb.xml',

           pdb_name       => 'SALESPDB')

    WHEN TRUE THEN 'YES'

    ELSE 'NO'

END;

BEGIN

  DBMS_OUTPUT.PUT_LINE(compatible);

END;

/

If the output is YES, then the PDB is compatible, and you can continue with the next step.

If the output is NO, then the PDB is not compatible, and you can check the PDB_PLUG_IN_VIOLATIONS view to see why it is not compatible.

Note: You can specify a .pdb archive file in the pdb_descr_file parameter.

2. If the PDB is not unplugged, then unplug it.

SQL> show con_name;   

-- The files are in the correct location. Therefore, NOCOPY is included. 

不需要复制数据文件到新建的pdb目录

SQL> CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml'

  NOCOPY TEMPFILE REUSE;

--The new PDB is based on the same unplugged PDB that was used to create an existing PDB in the CDB. Therefore, the AS CLONE clause is required. The AS CLONE clause ensures that the new PDB has unique identifiers. 同一CDB中使用相同unplugged PDB来plug in

SQL> CREATE PLUGGABLE DATABASE salespdb AS CLONE USING '/disk1/usr/salespdb.xml'

  NOCOPY TEMPFILE REUSE;

SOURCE_FILE_NAME_CONVERT:xml文件位置不转化，先对xml文件位置转为实际文件位置

FILE_NAME_CONVERT: 将原文件位置转为新位置

SQL> CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml'

  SOURCE_FILE_NAME_CONVERT = ('/disk1/oracle/sales/', '/disk2/oracle/sales/')

  MOVE

  FILE_NAME_CONVERT = ('/disk2/oracle/sales/', '/disk3/oracle/sales/')

  STORAGE (MAXSIZE 2G);

--The unplugged PDB is in a .pdb archive file named sales.pdb. The archive file includes the XML metadata file and the PDB’s files (such as the data files and wallet file) in compressed form, and these files are extracted to the current directory of the .pdb archive file when the CREATE PLUGGABLE DATABASE statement is run.

SQL> CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/sales.pdb'

  STORAGE (MAXSIZE 2G);

（七）Creating a PDB as a Proxy PDB

Proxy pdb就象个符号链接，可以使操作远程pdb就象在本地操作一样

代理pdb并不是将全部的数据文件同步到本地，而只有一些系统表空间

This technique creates a proxy PDB by referencing a PDB in a different CDB, which is referred to as the referenced PDB. You can use a proxy PDB when you want a local context for a remote PDB. In addition, when application containers in different CDBs have the same application, their application roots can be kept synchronized with a proxy PDB.

You must specify a database link to the current location of the referenced PDB in the FROM clause of the CREATE PLUGGABLE DATABASE statement. The database link must be created in the root of the CDB that will contain the proxy PDB, and the database link connects either to the root of remote CDB or to the remote referenced PDB.

Excluding ALTER PLUGGABLE DATABASE and ALTER DATABASE statements, when the proxy PDB is the current container, all SQL statements submitted for execution in the proxy PDB are sent to the referenced PDB and executed in the referenced PDB. The results of the remote execution are returned to the proxy PDB.

When the proxy PDB is the current container and ALTER PLUGGABLE DATABASE and ALTER DATABASE statements are executed, these statements only affect the proxy PDB. They are not sent to the referenced PDB for execution. Similarly, when the current container is the root to which the proxy PDB belongs, ALTER PLUGGABLE DATABASE statements only affect the proxy PDB.

However, after the proxy PDB is created, the database link specified during creation is no longer used by the proxy PDB. Instead, the proxy PDB communicates directly with the referenced PDB.

This direct communication requires the port number and host name of the listener of the CDB that contains the referenced PDB.

Listener port number: 1521

If the referenced PDB’s listener does not use the default port number, then you must use the PORT clause to specify the listener’s port number. You can specify the port number when you create the proxy PDB, or you can alter the proxy PDB to change the port number.

Listener host name: The host name of the CDB that contains the referenced PDB

If the referenced PDB’s listener does not use the default host name, then you must use the HOST clause to specify the listener’s host name. You can specify the host name when you create the proxy PDB, or you can alter the proxy PDB to change the host name.

Only password authentication is supported for sessions in a proxy PDB.

Creating a Proxy PDB

Note:

1. The CDB that contains the referenced PDB must be in local undo mode.
2. The CDB that contains the referenced PDB must be in ARCHIVELOG mode
3. The referenced PDB must be in open read/write mode when the proxy PDB is created. The open mode of the referenced PDB can be changed after the proxy PDB is created.
4. If the database link connects to the root in a remote CDB that contains the referenced PDB, then the user that the database link connects with must be a common user.
5. If the database link connects to the referenced PDB, then the user that the database link connects with in the referenced PDB must have the CREATE PLUGGABLE DATABASE system privilege.
6. If you are creating a proxy PDB in an application container, then the following prerequisites apply:

The referenced PDB must be an application root or an application PDB in an application container.

The application name and version of the proxy PDB’s application container must match the application name and version of the referenced PDB.

When the proxy PDB is being created in an application container, a database link must enable a connection from the root of the application container in which the proxy PDB is being created to the location of the referenced PDB. The database link can connect to either the root of the remote application container or to the remote application PDB

If the database link connects to the root in a remote application container that contains the referenced PDB, then the user that the database link connects with must be an application common user.

If the database link connects to the referenced application PDB, then the user that the database link connects with in the referenced application PDB must have the CREATE PLUGGABLE DATABASE system privilege.

Note:You can create a proxy PDB in a CDB root that is based on a referenced PDB in an application container.

（八）Creating a PDB Using a Non-CDB

Both the CDB and the non-CDB must be running Oracle Database 12c Release 1 (12.1.0.2) or later. If your current non-CDB uses an Oracle Database release before Oracle Database 12c Release 1 (12.1.0.2), then you must upgrade the non-CDB to Oracle Database 12c Release 1 (12.1.0.2) to use this technique. See Oracle Database Upgrade Guide for information about upgrading.

You can accomplish this task in the following ways:

1. Creating a PDB by cloning a non-CDB

This method is the simplest way to create a PDB using a non-CDB, but it requires copying the files of the non-CDB to a new location.

1. Use the DBMS_PDB package to generate an XML metadata file.

The XML metadata file describes the database files of the non-CDB so that you can plug it into a CDB.

This method requires more steps than creating a PDB by cloning a non-CDB, but it enables you to create a PDB using a non-CDB without moving the non-CDB files in some situations.

1. Use Oracle Data Pump export/import.

If the Oracle Database release of the non-CDB is Oracle Database 11g Release 2 (11.2.0.3) or later, then you can use full transportable export/import to move the data. When transporting a non-CDB from an Oracle Database 11g Release 2 (11.2.0.3) or later Oracle Database 11gdatabase to Oracle Database 12c, the VERSION Data Pump export parameter must be set to 12.0.0.0.0 or higher.

If the Oracle Database release of the non-CDB is before Oracle Database 11g Release 2 (11.2.0.3), then you can use transportable tablespaces to move the data, or you can perform a full database export/import.

1. Use GoldenGate replication.

1. Using the DBMS_PDB Package on a Non-CDB

To move a non-CDB into a PDB using the DBMS_PDB package:

1. Create the CDB if it does not exist.
2. Ensure that the non-CDB is in a transactionally-consistent state and place it in read-only mode.
3. Connect to the non-CDB, and run the DBMS_PDB.DESCRIBE procedure to construct an XML file that describes the non-CDB.The current user must have SYSDBA administrative privilege. The user must exercise the privilege using AS SYSDBA at connect time.

BEGIN

  DBMS_PDB.DESCRIBE(

    pdb_descr_file => '/disk1/oracle/ncdb.xml');

END;

/

1. After the procedure completes successfully, you can use the XML file and the non-CDB's database files to plug the non-CDB into a CDB.

SET SERVEROUTPUT ON

DECLARE

  compatible CONSTANT VARCHAR2(3) :=

    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

           pdb_descr_file => '/disk1/oracle/ncdb.xml',

           pdb_name       => 'NCDB')

    WHEN TRUE THEN 'YES'

    ELSE 'NO'

END;

BEGIN

  DBMS_OUTPUT.PUT_LINE(compatible);

END;

/

If the output is YES, then the non-CDB is compatible, and you can continue with the next step. If the output is NO, then the non-CDB is not compatible, and you can check the PDB_PLUG_IN_VIOLATIONS view to see why it is not compatible. All violations must be corrected before you continue.

1. Shut down the non-CDB.
2. Plug in the non-CDB.
3. Run the ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script. This script must be run before the PDB can be opened for the first time.

To run the noncdb_to_pdb.sql script, complete the following steps:

Access the PDB.

The current user must have SYSDBA administrative privilege, and the privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS SYSDBA at connect time.

Run the noncdb_to_pdb.sql script:

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

The script opens the PDB, performs changes, and closes the PDB when the changes are complete.

1. Open the new PDB in read/write mode.
2. Back up the PDB.