[原创]DB12cR2系列001_CDB/PDB操作
容器数据库CDB和可插拔数据库PDB的一些常用操作
启动/停止CDB
跟之前11g启动停止实例操作一样
[oracle@databasevm ~]$ export ORACLE_SID=ORCLCDB;sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jul 19 22:32:34 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> shu immediate; ###### 停止实例
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup ###### 启动实例和打开数据库
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 8793304 bytes
Variable Size 520094504 bytes
Database Buffers 1073741824 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,cdb,open_mode from v$database; ###### 查看当前db是否cdb/pdb和状态
NAME CDB OPEN_MODE
--------- --- --------------------
ORCLCDB YES READ WRITE
SQL>
SQL> show pdbs ###### 查看当前pdb状态
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
SQL>
启动/停止PDB
参考如下：
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 MOUNTED
SQL>
SQL> alter pluggable database ORCLPDB1 open read only;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ ONLY NO
SQL>
SQL> alter pluggable database orclpdb1 close immediate;
Pluggable database altered.
SQL> alter pluggable database orclpdb1 open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
SQL>
或者切换到pdb，跟11g一样启动停止
SQL> alter session set container=orclpdb1;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCLPDB1 READ WRITE NO
SQL> shu immediate;
Pluggable Database closed.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCLPDB1 MOUNTED
SQL> conn / as sysdba
Connected.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 MOUNTED
SQL>
建议使用alter pluggable database XXXX open 类似的操作。
另外，如果很多PDB的时候，可以批量的操作：
全部PDB停止/启动
SQL> alter pluggable database all close;
Pluggable database altered.
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 ORCLPDB1 READ WRITE NO
SQL>
指定几个pdb一起操作
SQL> alter pluggable database ORCLPDB1,PDB$SEED open read only;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ ONLY NO
SQL>
** 只有一个种子PDB和ORCLPDB1，而种子PDB只有open read only状态
制定某个数据库之外其他库一起操作
SQL> alter pluggable database all close;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 MOUNTED
SQL> alter pluggable database PDB$SEED close;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 ORCLPDB1 MOUNTED
SQL> alter pluggable database all except PDB$SEED open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 ORCLPDB1 READ WRITE NO
SQL>
番外篇：自动启动pdb
启动全部pdb
SQL> alter pluggable database all open;
可以通过添加Trigger的形式来定制化startup时自动将PDB OPEN
使用SYS用户创建如下触发器即可： CREATE TRIGGER open_all_pdbs AFTER STARTUP ON DATABASE BEGIN EXECUTE IMMEDIATE ‘alter pluggable database all open’; END open_all_pdbs; /
即可实现
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> show pdbs ### cdb实例停止后pdb也是关闭的
ERROR:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SP2-1545: This feature requires Database availability.
SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 8793304 bytes
Variable Size 520094504 bytes
Database Buffers 1073741824 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
SQL>
~~ 2017/07/17 bigdata_lyn ~~