oracle 12c数据库启动(包含CDB和PDB)以及常见异常处理

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