Oracle 12c CDB 和PDB 数据库的启动与关闭 说明

在Oracle 12c中,分CDB 和PDB,他们的启动和关闭操作整理如下。

1Container 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 rightsreserved.

Connectedto:

OracleDatabase 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

Withthe Partitioning,OLAP,Advanced Analytics and Real Application Testing options

sql>show con_name

CON_NAME

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

CDB$ROOT

sql>

sql>select con_id,dbid,guid,name,open_mode from v$pdbs;

CON_IDDBID GUIDNAMEOPEN_MODE

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

2 4088301206 F7C1E3C96BBF0585E0430A01A8C05459PDB$SEED READ ONLY

3426143573 F7C209EB1DFC0854E0430A01A8C0B787 PDBCNDBA READ WRITE

我们现在连接的是CDB,即root container。

我们关闭CDB:

sql>shutdown immediate

Databaseclosed.

Databasedismounted.

ORACLEinstance shut down.

我们关闭CDB之前PDB 是没有关闭的,所以这个操作也会把PDB 关闭掉。

sql>startup

ORACLEinstance started.

TotalSystem Global Area 1269366784 bytes

FixedSize 2287912 bytes

VariableSize 788530904 bytes

DatabaseBuffers 469762048 bytes

RedoBuffers 8785920 bytes

Databasemounted.

DatabaSEOpened.

sql> select con_id,open_modefrom v$pdbs;

CON_IDDBID GUIDNAMEOPEN_MODE

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

2 4088301206F7C1E3C96BBF0585E0430A01A8C05459 PDB$SEED READ ONLY

3426143573 F7C209EB1DFC0854E0430A01A8C0B787 PDBCNDBA MOUNTED

注意这里的PDB,在CDB 启动之,PDB 是自动启动到mount状态,而不是OPEN。 所以我们还需要手工去open它,当然,也可以通过在CDB中配置触发器来自动open。

2Pluggable Database (PDB)

PDB 的操作可以通过sql*PLUS 来操作,也可以通过ALTER PLUGGABLE DATABASE 命令操作。

2.1使用sql*Plus 命令

因为默认连接的是CDB,所以必须先指定PDB才可以通过sqlplus来启动和关闭PDB,

具体语法和普通实例一样:

STARTUPFORCE;

STARTUPOPEN READ WRITE [RESTRICT];

STARTUPOPEN READ ONLY [RESTRICT];

STARTUPUPGRADE;

SHUTDOWN[IMMEDIATE];

sql>show con_name

CON_NAME

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

CDB$ROOT

sql>selectcon_id,open_mode from v$pdbs;

CON_IDDBID GUIDNAME OPEN_MODE

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

2 4088301206F7C1E3C96BBF0585E0430A01A8C05459 PDB$SEEDREAD ONLY

3426143573 F7C209EB1DFC0854E0430A01A8C0B787 PDBCNDBA MOUNTED

注意这里是mount,表示PDB 是关闭的。

--指定PDB数据库

sql>alter session set container=pdbcndba;

Sessionaltered.

sql>startup

PluggableDatabase opened.

sql>select con_id,open_mode from v$pdbs;

CON_IDDBID GUIDNAME OPEN_MODE

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

3426143573 F7C209EB1DFC0854E0430A01A8C0B787 PDBCNDBA READ WRITE

sql>

启动成功。 当我们切换到PDB之后,就看不到seed PDB的信息了。

2.2使用ALTER PLUGGABLE DATABASE命令

如果在PDB中可以使用如下语法:

ALTERPLUGGABLE DATABASE OPEN READ WRITE [RESTRICTED] [FORCE];

ALTERPLUGGABLE DATABASE OPEN READ ONLY [RESTRICTED] [FORCE];

ALTERPLUGGABLE DATABASE OPEN UPGRADE [RESTRICTED];

ALTERPLUGGABLE DATABASE CLOSE [IMMEDIATE];

如果是在CDB中,可以使用如下语法:

ALTERPLUGGABLE DATABASE <pdd-name-clause> OPEN READ WRITE [RESTRICTED][FORCE];

ALTERPLUGGABLE DATABASE <pdd-name-clause> OPEN READ ONLY [RESTRICTED] [FORCE];

ALTERPLUGGABLE DATABASE <pdd-name-clause> OPEN UPGRADE [RESTRICTED];

ALTERPLUGGABLE DATABASE <pdd-name-clause> CLOSE [IMMEDIATE];

<pdd-name-clause>表示的是多个PDB,如果有多个,用逗号分开。 也可以使用ALL或者ALL EXCEPT关键字来替代。

ALL:表示所有的PDBS。

ALLEXCEPT 表示需要排除的PDBS。

如:

ALTERPLUGGABLE DATABASE pdb1,pdb2 OPEN READ ONLY FORCE;

ALTERPLUGGABLE DATABASE pdb1,pdb2 CLOSE IMMEDIATE;

ALTERPLUGGABLE DATABASE ALL OPEN;

ALTERPLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

ALTERPLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;

ALTERPLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE IMMEDIATE;

示例:

sql>alter session set container=CDB$ROOT;

Sessionaltered.

sql>select con_id,open_mode from v$pdbs;

CON_IDDBID GUIDNAME OPEN_MODE

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

2 4088301206F7C1E3C96BBF0585E0430A01A8C05459 PDB$SEEDREAD ONLY

3426143573 F7C209EB1DFC0854E0430A01A8C0B787 PDBCNDBA READ WRITE

sql>ALTER PLUGGABLE DATABASE ALL CLOSE ;

Pluggabledatabase altered.

sql>select con_id,open_mode from v$pdbs;

CON_IDDBID GUIDNAME OPEN_MODE

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

2 4088301206F7C1E3C96BBF0585E0430A01A8C05459 PDB$SEEDREAD ONLY

3 426143573 F7C209EB1DFC0854E0430A01A8C0B787PDBCNDBA MOUNTED

sql>alter session set container=pdbcndba;

Sessionaltered.

sql>ALTER PLUGGABLE DATABASE OPEN;

Pluggabledatabase altered.

sql> select con_id,open_modefrom v$pdbs;

CON_IDDBID GUIDNAME OPEN_MODE

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

3 426143573 F7C209EB1DFC0854E0430A01A8C0B787PDBCNDBA READ WRITE

3设置Pluggable Database (PDB) 的自动startup

默认情况下,在CDB 启动的时候,all 的PDB 都是mount状态,也没有默认的机制,在CDB启动时自动启动PDB。

但这里可以通过触发器来实现PDB的自动open:

CREATEOR REPLACE TRIGGER open_pdbs

AFTER STARTUP ON DATABASE

BEGIN

EXECUTE IMMEDIATE 'ALTERPLUGGABLE DATABASE ALL OPEN';

END open_pdbs;

/

示例:

sql>show con_name

CON_NAME

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

PDBCNDBA

sql>alter session set container=CDB$ROOT;

Sessionaltered.

sql>CREATE OR REPLACE TRIGGER open_pdbs

2AFTER STARTUP ON DATABASE

3BEGIN

4EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';

5 ENDopen_pdbs;

6 /

Triggercreated.

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

相关文章

数据库版本:11.2.0.4 RAC(1)问题现象从EM里面可以看到,在23号早上8:45~8:55时,数据库等待会话暴增...
(一)问题背景最近在对一个大约200万行数据的表查看执行计划时,发现存在异常,理论上应该返回100多万...
(一)删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操作时,会在RM...
(1)DRA介绍 数据恢复顾问(Data Recovery Advise)是一个诊断和修复数据库的工具,DRA能够修复数据文...
RMAN(Recovery Manager)是Oracle恢复管理器的简称,是集数据库备份(backup)、修复(restore)和恢复...
(1)备份对象 可以使用RMAN进行的备份对象如下: --整个数据库:备份所有的数据文件和控制文件; --数...