Multitenant Environment (CDB & PDB) on Oracle 12c

查看是否为 CDB 数据库

SQL> select name, cdb, open_mode, con_id from v$database;

NAME      CDB OPEN_MODE                CON_ID
--------- --- -------------------- ----------
ORCL      YES READ WRITE                    0

1> 查看当前容器（Container）

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

2> 查看 CDB 容器中的 PDBs 信息（在 CDB 模式下）

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        READ WRITE NO

SQL> select con_id,dbid,guid,name,open_mode from v$pdbs;

    CON_ID    DBID          GUID                         NAME       OPEN_MODE
---------- ---------- --------------------------------------------------------
     2     3012372808 BABBD7C6CEFE76EEE055E3D5AC69F4C3  PDB$SEED    READ ONLY
     3     1862374014 BABBF75EF6217B0EE055E3D5AC69F4C3  PDBORCL     READ WRITE

3> 容器会话切换

SQL> alter session set container=PDBORCL;  --切换至 PDB 容器 PDBORCL

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
PDBORCL

SQL> alter session set container=CDB$ROOT;  --切换至 CDB 容器 CDB$ROOT

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

4> 启动 / 关闭 PDB

SQL> alter pluggable database PDB_NAME open;  --开启指定 PDB

SQL> alter pluggable database all open;  --开启所有 PDBs

SQL> alter session set container=PDB_NAME;  --切换到 PDB 后开启数据库
SQL> startup

SQL> alter pluggable database PDB_NAME close;  --关闭指定的PDB

SQL> alter pluggable database all close;  --关闭所有PDBs

SQL> alter session set container=PDB_NAME;  --切换到 PDB 后关闭数据库
SQL> shutdown immediate

5> 创建 / 克隆 PDB

创建或克隆前要指定文件映射的位置（需要 CBD 下 sysdba 权限）

SQL> alter system set db_create_file_dest='/oradata/app/oracle/orcl/pdborcl2';

创建一个新的 PDB（需要 CBD 下 sysdba 权限）

SQL> create pluggable database pdborcl2 admin user admin identified by oracle;  --创建 PDB pdborcl2
SQL> alter pluggable database pdborcl2 open;   --打开 PDB pdborcl2

克隆 PDB（需要 CBD 下 sysdba 权限）

SQL> create pluggable database pdborcl2 from orcl1;  --由 orcl1 克隆 pdborcl2
SQL> alter pluggable database pdborcl2 open;  --打开 pdborcl2

删除 PDB（需要 CBD 下 sysdba 权限）

SQL> alter pluggable database pdborcl2 close;  --关闭 PDB pdborcl2
SQL> drop pluggable database pdborcl2 including datafiles;  --删除PDB pdborcl2

设置 CDB 启动 PDB 自动启动（需要 CBD 下 sysdba 权限）

CREATE OR REPLACE TRIGGER open_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/

Multitenant Environment (CDB & PDB) on Oracle 12c

Related commands