Oracle 19c 中CDB和PDB的常见操作介绍

----使用 sqlplus / as sysdba 登录连接到 CDB

[oracle@ocpstudy ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 17 15:35:55 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

----查看有哪些CDB

SQL> show pdbs

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

----启动 PDB

SQL> alter pluggable database pdb open;

Pluggable database altered.

----查看容器当前状态

SQL> col name format A10
SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY
PDB               READ WRITE
PDB 1            MOUNTED

----关闭 PDB

SYS@orcl(CDB$ROOT)> alter pluggable database pdb close;

Pluggable database altered.

----启动或者关闭 all pdbs

SQL> alter pluggable database all open;
SQL> alter pluggable database all close;
或
SQL> alter pluggable database all close immediate;

----排除某些 pdbs 不启动，启动所有未排除的pdbs

SQL>alter pluggable database all except pdb1 open;
SQL>alter pluggable database all except pdb1 close immediate;

----以只读方式打开PDBS

SQL>alter pluggable database pdb,pdb1 open read only force;
SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                                   READ ONLY  NO
         4 PDB 1                                READ ONLY  NO

----以受限模式打开PDB

SQL> alter pluggable database all close;
SQL> alter pluggable database pdb1 open restricted;
SQL> show pdbs

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

----切换容器

--查看当前所在容器
SQL> show con_name

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

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

Session altered.

--查看当前所在容器
SQL> show con_name

CON_NAME
------------------------------
PDB
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB                            READ WRITE NO

--切换到根容器
SQL> alter session set container=cdb$root;
或
SQL> conn / as sysdba

SQL> show con_name

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

–关闭pdb
当已经在pdb里面时，可通过执行shutdown和startup命令关闭和启动容器

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB                            READ WRITE NO
 
 SQL> shutdown
 SQL> startup

默认情况下使用 sqlplus / as sysdba 登录连接的是 CDB。在 CDB 启动之后，PDB 是自动启动到 mount 状态，而不是 OPEN状态。

SQL> conn / as sysdba
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                                   READ WRITE NO
         4 PDB                                   READ WRITE NO
SQL> shutdown immediate
SQL> startup
SQL> show pdbs

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

如果想在CDB启动的时候自动启动PDBS，该怎么实现呢？
方法一：在pdb open 状态下保存状态

SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                                   MOUNTED
         4 PDB 1                                MOUNTED
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                                   READ WRITE NO
         4 PDB                                   READ WRITE NO
SQL> alter pluggable database all save state;

Pluggable database altered.
SQL> shutdown immediate
SQL> startup
SQL> show pdbs

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

方法二：设置触发器

create trigger open_all_pdbs
after startup on database
begin
execute immediate 'alter pluggable database all open';
end open_all_pdbs;
/