PDB的创建和删除

创建PDB的几种方式：

注：Proxy PDB is a pdb that references a different PDB. In this case, the proxy PDB is local, whereas the referenced PDB is a separate CDB. All statements that you issue in the Proxy PDB execute in the remote PDB.(代理PDB是参考其他PDB的，在这种情况下，代理PDB是本地的，被参考的PDB是一个单独的PDB，在代理PDB发出的任何语句都会在远程PDB上执行。）

Each PDB has a globally unique identifier (GUID). The PDB GUID is primarily used to generate names for directories that store the PDB’s files, including both Oracle Managed Files directories and non-Oracle Managed Files directories.
每个PDB都有一个全局唯一标识符（GUID）。 PDB GUID主要用于为存储PDB文件的目录生成名称，包括Oracle Managed Files目录和非Oracle Managed Files目录。(GUID在cdb_pdbs视图能查到）

PDB的创建

1. 利用pdbseed创建pdb:主要是把PDBSEED中的数据文件复制到CREATE PLUGGABLE DATABASE语句中定义的目标目录中。可以使用位置子句创建，也可利用OMF创建。
   使用位置子句的意思就是：在创建的语句中直接指定目标目录，包括file_name_convert和create_file_dest。
   如果觉得在语句中指定路径有些麻烦，可以提前定义好相关OMF参数。
   DB_CREATE_FILE_DEST。注意再次创建新的pdb时需要重新定义该参数。
   PDB_FILE_NAME_CONVERT。12c中引入的参数。
   但是如果DB_CREATE_FILE_DEST和PDB_FILE_NAME_CONVERT同时定义的话，以DB_CREATE_FILE_DEST为准（全局定义）。
   示例：

---file_name_convert创建方式
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
SQL> create pluggable database pdb2 admin user pdb2admin identified by "pdb2admin" roles=(connect) file_name_convert=('/oracle/app/oracle/oradata/ZTEST/pdbseed','/oracle/app/oracle/oradata/ZTEST/pdb2');

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
         4 PDB2                           MOUNTED

SQL> alter pluggable database pdb2 open;

Pluggable database altered.
SQL> alter session set container=PDB2;---这样创建的PDB默认只创建了SYSTEM,SYSAUX,TEMP,UNDO表空间
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP

SQL> select * from dba_role_privs where grantee='PDB2ADMIN';---PDB2ADMIN权限也不是太多

GRANTEE              GRANTED_ROLE         ADM DEL DEF COM INH
-------------------- -------------------- --- --- --- --- ---
PDB2ADMIN            PDB_DBA              YES NO  YES NO  NO

SQL> select * from dba_sys_privs where grantee='PDB_DBA';

GRANTEE              PRIVILEGE                                ADM COM INH
-------------------- ---------------------------------------- --- --- ---
PDB_DBA              CREATE PLUGGABLE DATABASE                NO  NO  NO
PDB_DBA              CREATE SESSION                           NO  NO  NO
---create_file_dest子句创建，需先创建目录
SQL> !mkdir /oracle/app/oracle/oradata/ZTEST/pdb3

SQL> create pluggable database pdb3 admin user pdb3admin identified by pdb3admin roles=(connect) create_file_dest='/oracle/app/oracle/oradata/ZTEST/pdb3';

SQL> select con_id,name,open_mode from v$pdbs where name='PDB3';

    CON_ID NAME                 OPEN_MODE
---------- -------------------- ----------
         5 PDB3                 MOUNTED

SQL> select con_id,pdb_name,status,guid from cdb_pdbs where pdb_name='PDB3';

    CON_ID PDB_NAME             STATUS     GUID
---------- -------------------- ---------- --------------------------------
         5 PDB3                 NEW        AD21F9808D4E63C7E0535F4C60858CED


---如果不使用子句，则可以利用OMF参数提前定义好。
------DB_CREATE_FILE_DEST。注意再次创建新的pdb时需要重新定义该参数。
SQL> !mkdir /oracle/app/oracle/oradata/ZTEST/pdb4

SQL> show parameter db_create_file_dest;

SQL> alter system set db_create_file_dest='/oracle/app/oracle/oradata/ZTEST/pdb4';

System altered.

------PDB_FILE_NAME_CONVERT
SQL> !mkdir /oracle/app/oracle/oradata/ZTEST/pdb5

SQL> show parameter pdb_file_name_convert

SQL> alter system set pdb_file_name_convert='/oracle/app/oracle/oradata/ZTEST/pdbseed','/oracle/app/oracle/oradata/ZTEST/pdb5';

System altered.

2. 利用现有的pdb创建pdb

---with data
SQL> create pluggable database pdb4 from pdb file_name_convert=('/oracle/app/oracle/oradata/ZTEST/pdb','/oracle/app/oracle/oradata/ZTEST/pdb4');

Pluggable database created.

SQL> !mkdir /oracle/app/oracle/oradata/ZTEST/pdb7

SQL> create pluggable database pdb7 from pdb create_file_dest='/oracle/app/oracle/oradata/ZTEST/pdb7';

Pluggable database created.

SQL> alter pluggable database pdb7 open;

Pluggable database altered.

---with no data（12.1版本需要设置源pdb只读）
SQL> create pluggable database pdb10 from pdb no data file_name_convert=('/oracle/app/oracle/oradata/ZTEST/pdb','/oracle/app/oracle/oradata/ZTEST/pdb10');

Pluggable database created.

CDB/PDB的操作

1. 对指定pdb开关用逗号隔开，或使用all对所有pdb操作。

SQL> alter pluggable database PDB8,PDB9,PDB10 open;

Pluggable database altered.

SQL> alter pluggable database PDB8,PDB9,PDB10 close;

Pluggable database altered.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter pluggable database all close;

Pluggable database altered.

2. 对PDB的访问依赖于CDB，必须启动CDB之后，才能够对PDB进行操作。当CDB打开访问时，PDB 处于mount状态，需要进一步的操作打开PDB。

SQL> startup
ORACLE instance started.

Total System Global Area 1711274152 bytes
Fixed Size                  9136296 bytes
Variable Size             738197504 bytes
Database Buffers          956301312 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SQL> show pdbs---CDB startup后，除了seed CDB外其他cdb都为mount状态

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
         4 PDB2                           MOUNTED
         5 PDB3                           MOUNTED
         6 PDB4                           MOUNTED

---对指定pdb shutdown immediate后，查询其状态为mount
SQL> alter session set container=pdb11;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        13 PDB11                          READ WRITE NO
SQL> shutdown immediate
Pluggable Database closed.

SQL> select NAME,open_mode from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB11                MOUNTED

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        13 PDB11                          MOUNTED

PDB的删除

1. 连同数据文件一块删除，including datafiles

SQL> drop pluggable database pdb11 including datafiles;

Pluggable database dropped.

[oracle@zcloud ZTEST]$ pwd---pdb11目录还在，下面没内容
/oracle/app/oracle/oradata/ZTEST
[oracle@zcloud ZTEST]$ ls pdb11

2. 保留数据文件，keep datafiles，drop时不加参数默认为keep datafiles
   首先要拔出pdb，拔出PDB的时候指定xml位置，xml中记录了PDB的信息，例如数据文件目录、DBID、PDB参数等。被拔出的PDB只能被删除，不能做其它操作，例如mount、打开等。
   删除后可以重新插入，如果要插入到其他的CDB，则需要指定目录位置。PDB_PLUG_IN_VIOLATIONs视图可用来查看插入是否存在报错。

SQL> alter pluggable database PDB9 unplug into '/home/oracle/pdb9.xml';---拔出

Pluggable database altered.

SQL> drop pluggable database pdb9;---删除，keep datafiles

Pluggable database dropped.

[oracle@zcloud ZTEST]$ ls pdb9
sysaux01.dbf  system01.dbf  undotbs01.dbf  users01.dbf

SQL> create pluggable database pdb9 using '/home/oracle/pdb9.xml' nocopy;---重新插入

Pluggable database created.