Oracle 12C及以上版本 CDB/PDB常用管理命令
1. 查看PDB信息
SQL> show pdbs --查看所有pdb
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 SBDB1 READ WRITE NO
SQL> col name for a20
SQL> select name,dbid,open_mode from v$pdbs; --v$pdbs为PDB信息视图
SQL> select name,dbid,open_mode from v$pdbs;
NAME DBID OPEN_MODE
-------------------- ---------- ----------
PDB$SEED 943290432 READ ONLY
PDB1 1666453508 READ WRITE
PDB2 1345465961 READ WRITE
SBDB1 3044255387 READ WRITE
2.切换容器
SQL> alter session set container=sbdb1 --切换到PDB sbdb1
SQL> alter session set container=CDB$ROOT --切换到CDB容器
查看当前属于哪个容器
SQL> show con_name;/show pdbs; --用show查看当前属于哪个容器
SQl> select sys_context('USERENV','CON_NAME') from dual; --使用sys_context查看属于哪个容
3. 启动PDB
SQL> alter pluggable database sbdb1 open; --开启指定PDB
SQL> alter pluggable database all open; --开启所有PDB
SQL> alter session set container=sbdb1; --切换到PDB进去开启数据库
SQL> startup
4.关闭PDB
SQL> alter pluggable database sbdb1 close; --关闭指定的PDB
SQL> alter pluggable database all close; --关闭所有PDB
SQL> alter session set container=sbdb1; --切换到PDB进去关闭数据库
SQL> shutdown immediate
5.创建PDB
创建或克隆前要指定文件映射的位置(需要CBD下sysdba权限)
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata';
创建一个新的PDB(需要CBD下sysdba权限)
SQL> create pluggable database sbdb2 admin user sbdb2_user identified by oracle;
SQL> alter pluggable database sbdb2 open; --将PDB sbdb2 打开
6.克隆PDB
(需要CBD下sysdba权限)
SQL> create pluggable database sbdb3 from sbdb1; --sbdb1必须是打开的,才可以被克隆
SQL> alter pluggable database sbdb3 open; --然后打开这个pdb
7. 删除PDB
(需要CBD下sysdba权限)
SQL> alter pluggable database sbdb3 close; --关闭之后才能删除
SQL> drop pluggable database sbdb3 including datafiles; --删除PDB sbdb3
8.设置CDB中PDB自动启动
方式一:设置触发器
CREATE TRIGGER open_all_pdbs
AFTER STARTUP
ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/
方式二:使用save state参数(12cR2及以上版本)
通过SAVE STATE保存CDB重启前PDB的状态。这样重启CDB之后,会自动打开PDB到之前的状态。
SQL> ALTER PLUGGABLE DATABASE sbdb1 SAVE STATE;
SQL> ALTER PLUGGABLE DATABASE all SAVE STATE;
取消保存CDB重启前PDB的状态
SQL> ALTER PLUGGABLE DATABASE sbdb1 DISCARD STATE;
SQL> ALTER PLUGGABLE DATABASE all DISCARD STATE;