1.查看当前容器

  1. SQL> show con_name                                                                                                                    
  3. CON_NAME                                                                                                                              
  4. ------------------------------                                                                                                        
  5. CDB$ROOT

2.创建容器pdb数据库

(1)可能提示’FILE_NAME_CONVERT’错误，需要在sql语句后面添加相关种子数据库和新容器数据库路径。
(2)设置参数db_create_file_dest 也可处理’FILE_NAME_CONVERT’错误。
12cR2中源数据库不在要求是只读，只要开启归档和本地undo 就可以克隆

  2. SQL> create PLUGGABLE DATABASE plug_test ADMIN user admin IDENTIFIED by "12345";                                                      
  3. create PLUGGABLE DATABASE plug_test ADMIN user admin IDENTIFIED by "12345"                                                            
  4.                                                                          *                                                            
  5. ERROR at line 1:                                                                                                                      
  6. ORA-65016: FILE_NAME_CONVERT must be specified  
  9. create PLUGGABLE DATABASE plug_test ADMIN user admin IDENTIFIED by "12345";
  10. FILE_NAME_CONVERT=('/opt/oracle/oradata/ORCLCDB/pdbseed',
  11. '/opt/oracle/oradata/ORCLCDB/plug_test');
  13. SQL> alter   system set db_create_file_dest='/opt/oracle/oradata/ORCLCDB';                                                            
  15. System altered. 
  16. 克隆已有数据库pdb
  17. alter pluggable database pdb1 close immediate;
  18. alter pluggable database pdb1 open read only;
  20. create pluggable database fxpdb3 from pdb1 storage unlimited file_name_convert=('/u01/app/oracle/oradata/orcl/pdb1','/u01/app/oracle/oradata/orcl/fxpdb3');
  21.  create pluggable database pdb2   from  pdb1;
  22. create pluggable database pdb1   from pdb2@pdb1_link;

3.查看pdb运行状态

  1. select * from v$pdbs;

4.更改当前容器

  1. SQL> alter   session set container=plug_test;                                                                                         
  3. Session altered.

5.打开所有容器数据库和特定容器数据库

1.前提条件必须是在cdb环境下

  1. alter PLUGGABLE DATABASE database_name open;
  3. alter PLUGGABLE DATABASE all open;
  4. -----------------------------------------
  5. 打开所有数据库plug_test除外
  6. alter PLUGGABLE DATABASE all except plug_test open;

6.关闭数据库

1.和普通shutdown命令一样，需要注意的是要先切换到相关容器数据库下

  1. SQL> alter   session set container=plug_test;   
  3. shutdown immediate;
  5. -------------------------------------------------------------------
  6. [oracle@instance-ej1hspdt ~]$ sqlplus c##guest/12345@172.16.0.4:1522/\plug_test 
  7. SQL> show con_name                                                                                                                    
  9. CON_NAME                                                                                                                              
  10. ------------------------------                                                                                                        
  11. PLUG_TEST                                                                                                                             
  12. SQL> shutdown
  13. ---------------------------------------------------------------------
  14. alter pluggable database plug_test close;

7.在cdb重启时自动启动pdb容器数据库，取消相关状态

  1. SQL> alter pluggable database plug_test/all/all except plug_test save state;                                                                                   
  3. Pluggable database altered. 
  6.  -------------------------------------------------------------                                                                                                                                     
  7. SQL> alter pluggable database  plug_test/all/all except plug_test   discard state;                                                                              
  9. Pluggable database altered.

8.插拔数据库

  1. 先关闭数据库
  2. alter PLUGGABLE DATABASE plug_test1 CLOSE;
  3. -------------------------------------------
  4. SQL> alter PLUGGABLE DATABASE plug_test1 UNPLUG into '/opt/oracle/plug_test1.xml';
  5. Pluggable database altered.
  6. 删除数据库并不清除数据文件的路径
  7. drop PLUGGABLE DATABASE plug_test1;
  8. 删除数据库并且清理数据库中数据文件(注意操作系统当中文件并没有删除)
  9. drop PLUGGABLE DATABASE plug_test2 INCLUDING DATAFILES;
  11. 添加数据库
  12. create PLUGGABLE database plug_test2 USING '/opt/oracle/plug_test1.xml';
  13. 打开数据库
  14. alter PLUGGABLE DATABASE plug_test2 open;

9.相关视图

  1. select * from cdb_tablespaces;
  2. select * from v$tablespace;
  3. select * from v$containers;
  4. select * from v$pdbs;
  5. select * from cdb_pdbs;

10.备份恢复

  1. 备份
  2. RMAN> backup pluggable database "CDB$ROOT"; 
  3. RMAN> backup pluggable database pdb2,pdb3;
  4. backup pluggable database fxpdb1,fxpdb2 plus archivelog;
  5. 恢复
  6. restore pluggable database fxpdb1;
  7. recover pluggable database fxpdb1;
  8. alter pluggable database fxpdb1 open;
  10. validate database;
  11. validate database root;
  12. validate pluggable database fxpdb1;

11.容器数据库创建用户

  1. create   user   c##test   identified  by  "password' container=all/containerName;

12.查看容器数据的信息

  1. select   *   from v$containers;

13.pdb缺失表空间无法open

思路：先登录cdb切换session到pdb 离线缺失表空间再open;
之后在rman恢复缺失的文件

  1. sqlplus / as sysdba
  2. alter session datafile 12 offline;
  3. alter pluggable database tplug open;
  4. rman target robert/robert@10.1.1.1:1521/tplug 
  5. restore datafile 12;
  6. recover datafile 12;
  7. alter database datafile 12 online;

14.查询pdb对应的表空间文件

  1. SELECT d.con_ID,p.PDB_NAME，d.FILE_ID,d.TABLESPACE_NAME,
  2. d.FILE_NAME
  3. FROM  CDB_PDBS p, CDB_DATA_ FILES d where p.PDB_ID(+) = d.CON_ID
  4. order by d.con_id;

15.查询用户对所有pdb 对应的表

  1. SELECT p.PDB_ID,p.PDB_NAME,t.OWNER,t.TABLE_NAMEFROMCDB_PDBS p,CDB_TABLES t
  2. where p.PDB_ID = t.CON_ID
  3. AND T.OWNER='ORDDATA' ORDER BY t.TABLE_NAME;

16.检查PDB历史

(它是什么时候创建的)PDB是怎么来的(从哪里克隆而来)?是怎么被创建的?是什么时候创建的?这些重要信息都保存在CDB PDB_HISTORY 中，可以执行下面的查询:

  1. SELECT DB_NAME，CON_ID,PDB_NAME，OPERATION,
  2. OPTIMESTAMP,CLONED FROM PDB NAME
  3. FROMCDB PDB_HISTORY
  4. WHERE CON ID >2
  5. ORDER BY CON_ID;

17.pdb 级别alter system 语句

  1. ALTER SYSTEM FLUSH SHARED_POOL
  2. ALTER SYSTEM FLUSH BUFFER_CACHE
  3. ALTER SYSTEM SET USE_STORED_OUTLINES
  4. ALTER SYSTEM CHECKPOINT
  5. ALTER SYSTEM KILL SESSION
  6. ALTER SYSTEM DISCONNECT SESSION
  7. ALTER SYSTEM SET initialization_parameter

18.pdb 管理语句

  1. ALTER PLUGGABLE DATABASE DATAFILE '/u03/oracle/pdb1_01.dbf' ONLINE/offline; 联机、离线数据文件
  2. ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE pdb1_tbss;设置pdb 级别默认表空间
  3. ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE pdb1_temp;设置pdb级别临时表空间
  4. ALTER PLUGGABLE DATABASE SET DEFAULT BIGFILE TABLESPACE;设置pdb 级别默认表空间类型
  5. ALTER PLUGGABLE DATABASE STORAGE(MAXSIZE 2G);  设置pdb级别默认最大表空间大小
  6. ALTER PLUGGABLE DATABASE STORAGE(MAXSIZE UNLIMITED);设置pdb级别默认表空间大小
  7. ALTER PLUGGABLE DATABASE NOLOGGING;   设置pdb级别NOLOGGING 模式
  8. ALTER PLUGGABLE DATABASE ENABLE FORCE LOGGING;  设置pdb级别强制NOLOGGING 模式
  9. ALTER PLUGGABLE DATABASE DEFAULT EDITION = PDB1E3; 设置pdb级别 EDITION 
  10. ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO salespdb.example.com;修改pdb数据库名，此操作完成后需要重新打开pdb