CDB/PDB常见管理

一.打开和关闭CDB/PDB

CDB的打开和关闭和以前的非CDB环境一致,可以打开带nomount,mount,open等.

下面着重讲解PDB的打开和关闭

1.PDB可以打开到mount,migrate,read only,read write,注意PDB不能打开到nomount

可以对单独的PDB执行打开和关闭

alter pluggable database brent open;

alter pluggable database brent open read only;

alter pluggable database brent open upgrade restricted;

alter pluggable database brent  close;

alter pluggable database brent close immediate;

还可以对所有的PDB操作:

 

alter pluggable database all open;

alter pluggable database all close immediate;

还可以排除某些PDB,其它PDB全部打开/关闭

 

alter pluggable database all except brent open;

alter pluggable database all except testpdb1,testpdb2 close;

alter pluggable database all except testpdb1,testpdb2 close;

但是注意,上的操作都是在CDB中执行,如果是在某个PDB中,那么就只能起停自己的PDB,不能操作其它PDB.

否则报错:

 

SQL> alter pluggable database testpdb2 close;

alter pluggable database testpdb2 close

*

ERROR at line 1:

ORA-65118: operation affecting a pluggable database cannot be performed from

another pluggable database

2.为了兼容以前的语法,还可以使用startup命令.

如果你在CDB中,使用如下语法:

 

SQL> startup pluggable database all;

Pluggable Database opened.

SQL> show pdbs

    CON_ID CON_NAME           OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

     2 PDB$SEED           READ ONLY  NO

     3 BRENT              READ WRITE NO

     4 TESTPDB1           READ WRITE NO

     5 TESTPDB2           READ WRITE NO

     7 TESTPDB3           READ WRITE NO

如果你处在某个PDB中,则可以直接进行startup和shutdown

 

SQL> alter session set container=testpdb1;

Session altered.

SQL> shutdown

Pluggable Database closed.

SQL> startup

Pluggable Database opened

建议如果是管理员,还是使用alter pluggable database命令.

 

3.保存PDB的打开状态

在12.1.01版本中,如果重启了CDB,你还需要手工重启所有的PDB,在12.1.01之后,可以通过save state命令,保存当前的PDB状态,这样即使CDB重启之后,PDB会自动恢复到原来的状态.

下面是具体的例子,首先查询当前的PDB状态:

 

SQL> show pdbs;

    CON_ID CON_NAME           OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

     2 PDB$SEED           READ ONLY  NO

     3 BRENT              READ WRITE NO

     4 TESTPDB1           READ WRITE NO

     5 TESTPDB2           READ WRITE NO

     7 TESTPDB3           MOUNTED

使用SQL> alter pluggable database all save state;

命令来保存所有的PDB状态,如果你想保存单个的上面写单个PDB名称即可,注意上面有一个是mount,其它是read write:

 

SQL> alter pluggable database all save state;

Pluggable database altered.

如果是集群环境,则需要在每个实例上执行上面的sql,或者添加instances关键字:

 

SQL> alter pluggable database testpdb2 save state instances=all;

Pluggable database altered.

下面重启CDB,再查看PDB状态:

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  952106368 bytes

Fixed Size          9141632 bytes

Variable Size         486539264 bytes

Database Buffers      448790528 bytes

Redo Buffers            7634944 bytes

Database mounted.

Database opened.

SQL> show pdbs;

    CON_ID CON_NAME           OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

     2 PDB$SEED           READ ONLY  NO

     3 BRENT              READ WRITE NO

     4 TESTPDB1           READ WRITE NO

     5 TESTPDB2           READ WRITE NO

     7 TESTPDB3           MOUNTED

可以看到PDB状态和重启CDB之前一直.

可以通过视图DBA_PDB_SAVED_STATES查看PDB的保存状态:

 

SQL> select con_name,state from DBA_PDB_SAVED_STATES;

CON_NAME               STATE

------------------------------ --------------

BRENT                  OPEN

TESTPDB1               OPEN

TESTPDB2               OPEN

上面没有TESTPDB3的保存状态,则默认为mount

还可以使用discard state命令,清除保存状态alter pluggable database testpdb1 discard state;

 

SQL> alter pluggable database testpdb1 discard state;

Pluggable database altered.

SQL> select con_name,state from DBA_PDB_SAVED_STATES;

CON_NAME               STATE

------------------------------ --------------

BRENT                  OPEN

TESTPDB2               OPEN

4.在集群数据库中打开PDB

在集群环境中,PDB可以指定在某些实例上打开

 

SQL> alter pluggable database testpdb3 open instances=('ora19c1');

Pluggable database altered.

 

alter pluggable database testpdb3 open instances=all;

alter pluggable database testpdb3 open instances=('ora19c1','ora19c2');

alter pluggable database testpdb3 open instances=al except ('ora19c3');

还可以使用relocate命令,他的意思是关闭当前实例的PDB,在其他实例上打开,和集群的relocate资源含义一致.

 

alter pluggable database testpdb3 close relocate to ora19c2;

alter pluggable database testpdb3 close relocate; 

二.查看PDB的状态和操作历史

查看v$pdbs可以查看pdb的当前状态,快捷的可以使用show pdbs命令:

 

SQL> show pdbs

    CON_ID CON_NAME           OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

     2 PDB$SEED           READ ONLY  NO

     3 BRENT              READ WRITE NO

     4 TESTPDB1           READ WRITE NO

     5 TESTPDB2           READ WRITE NO

     7 TESTPDB3           READ WRITE NO

可以通过查看cdb_pdb_history查看PDB的操作历史:

 

  1* select pdb_name,op_timestamp,operation from cdb_pdb_history

SQL> /

​

PDB_NAME               OP_TIMEST OPERATION

------------------------------ --------- ----------------

BRENT                  21-JUN-19 CREATE

TESTPDB1               21-JUN-19 CREATE

TESTPDB2               23-JUN-19 CREATE

TESTPDB3               23-JUN-19 CREATE

TESTPDB5               23-JUN-19 CLONE

TESTPDB4               13-SEP-19 CREATE

TESTPDB5               13-SEP-19 DROP

TESTPDB4               13-SEP-19 UNPLUG

TESTPDB4               13-SEP-19 DROP

TESTPDB3               13-SEP-19 UNPLUG

TESTPDB3               13-SEP-19 DROP

TESTPDB4               13-SEP-19 PLUG

TESTPDB4               13-SEP-19 UNPLUG

TESTPDB4               13-SEP-19 DROP

TESTPDB3               13-SEP-19 PLUG

TESTPDB2               23-JUN-19 CREATE

BRENT                  21-JUN-19 CREATE

TESTPDB1               21-JUN-19 CREATE

TESTPDB4               13-SEP-19 CREATE

TESTPDB4               13-SEP-19 UNPLUG

TESTPDB4               13-SEP-19 PLUG

TESTPDB4               13-SEP-19 UNPLUG

TESTPDB3               13-SEP-19 PLUG

​