Oracle12c CDB和PDB数据库的启动与关闭说明
Oracle 12c中,增加了可插拔数据库的概念,即PDB,允许一个数据库容器（CDB）承载多个可插拔数据库（PDB）。
CDB全称为Container Database,中文翻译为数据库容器,PDB全称为Pluggable Database,即可插拔数据库。
在Oracle 12c之前,实例与数据库是一对一或多对一关系（RAC）：即一个实例只能与一个数据库相关联,数据库可以被多个实例所加载。
而实例与数据库不可能是一对多的关系。当进入ORACLE 12C后,实例与数据库可以是一对多的关系。
下面是官方文档关于CDB与PDB的关系图。
1.Container Database (CDB)
对于CDB,启动和关闭与之前传统的方式一样,具体语法如下：
STARTUP[NOMOUNT | MOUNT | RESTRICT | UPGRADE | FORCE | READ ONLY]
SHUTDOWN[IMMEDIATE | ABORT]
要注意,在12c数据库创建完成后,默认情况下使用sqlplus / as sysdba 登录连接的是CDB。
[oracle@Ora12c~]$ sqlplus / as sysdba
SQL*Plus:Release 12.1.0.1.0 Production on Thu Apr 24 17:50:34 2014
Copyright(c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>show con_name
CON_NAME
------------
CDB$ROOT
SQL>select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
--------- ---------- ---------------------------------------------- ---------------- ----------------
2 3204963903 63A7E5E19484F30BE05556737F8D97CF PDB$SEED READ ONLY
3 4133618892 63A7FCD3636CFC8DE05556737F8D97CF ORCLPDB READ WRITE
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
我们现在连接的是CDB,即root container。
我们关闭CDB：
SQL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
我们关闭CDB之前PDB 是没有关闭的,所以这个操作也会把PDB 关闭掉。
SQL> startup
ORACLE instance started.
Total System Global Area 834666496 bytes
Fixed Size 8798264 bytes
Variable Size 633343944 bytes
Database Buffers 184549376 bytes
Redo Buffers 7974912 bytes
Database mounted.
Database opened.
SQL> select con_id, dbid, guid, name , open_modefrom v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
--------- ---------- ---------------------------------------------- ---------------- ----------------
2 3204963903 63A7E5E19484F30BE05556737F8D97CF PDB$SEED READ ONLY
3 4133618892 63A7FCD3636CFC8DE05556737F8D97CF ORCLPDB MOUNTED
注意这里的PDB,在CDB 启动之,PDB 是自动启动到mount状态,而不是OPEN。 所以我们还需要手工去open它,当然,也可以通过在CDB中配置触发器来自动open。
PDB 的操作可以通过SQL*PLUS 来操作,也可以通过ALTER PLUGGABLE DATABASE 命令操作。
2.1 使用SQL*Plus 命令
因为默认连接的是CDB,所以必须先指定PDB才可以通过sqlplus来启动和关闭PDB,
具体语法和普通实例一样：
STARTUP FORCE;
STARTUP OPEN READ WRITE [RESTRICT];
STARTUP OPEN READ ONLY [RESTRICT];
STARTUP UPGRADE;
SHUTDOWN[IMMEDIATE];
SQL>show con_name
CON_NAME
-------------
CDB$ROOT
SQL>select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
--------- ---------- ---------------------------------------------- ---------------- ----------------
2 3204963903 63A7E5E19484F30BE05556737F8D97CF PDB$SEED READ ONLY
3 4133618892 63A7FCD3636CFC8DE05556737F8D97CF ORCLPDB MOUNTED
注意这里是mount,表示PDB 是关闭的。
--指定PDB 数据库：
SQL>alter session set container=ORCLPDB;
Session altered.
SQL>startup
Pluggable Database opened.
SQL>select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
--------- ---------- ---------------------------------------------- ---------------- ----------------
3 4133618892 63A7FCD3636CFC8DE05556737F8D97CF ORCLPDB READ WRITE
启动成功。 注意:当我们切换到PDB之后,就看不到seed PDB的信息了。
2.2 使用ALTER PLUGGABLE DATABASE命令
如果在PDB中可以使用如下语法：
ALTER PLUGGABLE DATABASE OPEN READ WRITE [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE CLOSE [IMMEDIATE];
如果是在CDB中,可以使用如下语法：
ALTER PLUGGABLE DATABASE <pdd-name-clause> OPEN READ WRITE [RESTRICTED][FORCE];
ALTER PLUGGABLE DATABASE <pdd-name-clause> OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE <pdd-name-clause> OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE <pdd-name-clause> CLOSE [IMMEDIATE];
<pdd-name-clause>表示的是多个PDB,如果有多个,用逗号分开。 也可以使用ALL或者ALL EXCEPT关键字来替代。
ALL：表示所有的PDBS。
ALL EXCEPT 表示需要排除的PDBS。
如：
ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE IMMEDIATE;
示例：
SQL>alter session set container=CDB$ROOT;
Session altered.
SQL>select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
--------- ---------- ---------------------------------------------- ---------------- ----------------
2 3204963903 63A7E5E19484F30BE05556737F8D97CF PDB$SEED READ ONLY
3 4133618892 63A7FCD3636CFC8DE05556737F8D97CF ORCLPDB READ WRITE
SQL>ALTER PLUGGABLE DATABASE ALL CLOSE ;
Pluggable database altered.
SQL>select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
--------- ---------- ---------------------------------------------- ---------------- ----------------
2 3204963903 63A7E5E19484F30BE05556737F8D97CF PDB$SEED READ ONLY
3 4133618892 63A7FCD3636CFC8DE05556737F8D97CF ORCLPDB MOUNTED
SQL>alter session set container=ORCLPDB;
Session altered.
SQL>ALTER PLUGGABLE DATABASE OPEN;
Pluggable database altered.
SQL> select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
--------- ---------- ---------------------------------------------- ---------------- ----------------
3 4133618892 63A7FCD3636CFC8DE05556737F8D97CF ORCLPDB READ WRITE
3 设置Pluggable Database (PDB) 的自动startup
默认情况下,在CDB 启动的时候,all 的PDB都是mount状态.要在CDB启动时自动启动PDB,这里可以通过触发器来实现PDB的自动open：
CREATE OR REPLACE TRIGGER open_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/
SQL> alter session set container=CDB$ROOT;
Oracle 12 数据库启动/关闭
由于系统重启,Oracle没有设置自动启动,需要手动启动数据库
1.确保$ORACLE_HOME与$ORACLE_SID设置成了正确
安装Oracle 12c之后，以下3个脚本文件默认会在/usr/local/bin目录下，
dbhome oraenv coraenv
运行dbhome可以知道ORACLE_HOME的目录。运行oraenv可以设置ORACLE_SID以及ORACLE_HOME的值。
如果没有正确设置ORACLE_SID的值，那么第2步启动database就会失败。具体见下一步。
2.启动数据库
[oracle@xiaogaokui bin]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Thu May 10 10:27:50 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 834666496 bytes
Fixed Size 8798264 bytes
Variable Size 633343944 bytes
Database Buffers 184549376 bytes
Redo Buffers 7974912 bytes
Database mounted.
Database opened.
3.启动监听服务
检查监听服务
方式一:
oracle listener默认监听1521端口
[oracle@xiaogaokui ~]$ netstat -nap | grep 1521
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp6 0 0 :::1521 :::* LISTEN 1779/tnslsnr
tcp6 0 0 fe80::5673:7f1c:8:63533 fe80::5673:7f1c:88:1521 ESTABLISHED 3691/ora_lreg_orcl
tcp6 0 0 fe80::5673:7f1c:88:1521 fe80::5673:7f1c:8:63533 ESTABLISHED 1779/tnslsnr
方式二:
# lsnrctl status
启动监听服务
# $ORACLE_HOME/bin/lsnrctl start
检查database services是否成功注册到listener上,有时要等一小会（< 1 minute）才会注册成功。
# $ORACLE_HOME/bin/lsnrctl services
如果启动listener失败，得到了错误消息“TNS-00525: Insufficient privilege for operation”，那说明之前有人曾用root启动过listener。这时需要删除目录/var/tmp/.oracle/里面的文件，然后再重新启动listener。
4.有时如果database services不能成功注册到listener上，可以尝试如下命令，
SQL> connect / as sysdba
Connected.
SQL> alter system register;
System altered.
SQL> alter system set local_listener='';
System altered.
报错情况:
如果之前没有正确设置ORACLE_SID，则会得到如下错误，
# sqlplus /nolog
# connect / as sysdba;
ERROR:
ORA-12162: TNS:net service name is incorrectly specified