oracle 12c pdb与cdb

来源：互联网发布：丹尼斯约翰逊数据编辑：程序博客网时间：2024/06/02 19:37

一、概念

CDB的组件：

Root：即CDB$ROOT，保存了元数据及通用用户。如PL/SQL包的源码就是元数据。通用用户是指每一个容器都知道的数据库用户。

The root,namedCDB$ROOT, stores Oracle-supplied metadata andcommon users. An example ofmetadata is the source code for Oracle-suppliedPL/SQL packages. A common useris a database user known in every Container. ACDB has exactly one root.

Seed：即PDB$SEED，它是创建PDB的模板，我们不能添加或修改Seed中的对象，并且一个CDB只能有一个Seed。

The seed, namedPDB$SEED, is a templatethat you can use to create new PDBs. You cannot add objectsto or modifyobjects in the seed. A CDB has exactly one seed.

PDBs：一个PDB对于用户或应用程序来说就像一个普通的数据库。

A PDB appears tousers and applications asif it were a non-CDB.

common user与local user：

一个CDB支持多个common user。Commonuser 在root与PDB中具有相同的标识，并且可以登录root与任何其拥有权限的PDB。Commonuser能够执行的操作取决于对common user的授权。另外，一些管理操作，如创建PDB，拔出PDB等需要由commonuser来执行。一个local user只存在于一个PDB中。

A CDB supportscommon users. A common useris a user that has the same identity in the root andin every existing andfuture PDB. A common user can log in to the root and anyPDB in which it hasprivileges. The operations that a common user can performdepend on theprivileges granted to the common user. Some administrative tasks,such ascreating a PDB or unplugging a PDB, must be performed by a common user.A CDBalso supports local users. A local user is a user that exists in exactlyone PDB.

二、CDB与PDB操作

1、连接CDB

与11g及之前版本登录方式一致，默认连接至cdb

[oracle@bing ~]$ sqlplus / as sysdba;

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 17 22:34:48 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bitProduction

With the Partitioning, OLAP, Advanced Analytics and Real ApplicationTesting options

SQL>

通过监听查看servername

[oracle@bing ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-DEC-201622:47:18

Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bing)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date 17-DEC-2016 21:42:20

Uptime 0days 1 hr. 4 min. 58 sec

Trace Level off

Security ON:Local OS Authentication

SNMP OFF

Listener Parameter File /u01/oracle/product/12.1.0/db_1/network/admin/listener.ora

Listener Log File /u01/oracle/diag/tnslsnr/bing/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bing)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=bing)(PORT=5500))(Security=(my_wallet_directory=/u01/oracle/admin/bing/xdb_wallet))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "bing" has 1 instance(s).

Instance "bing",status READY, has 1 handler(s) for this service...

Service "bingXDB" has 1 instance(s).

Instance "bing",status READY, has 1 handler(s) for this service...

Service "pdbbing" has 1 instance(s).

Instance "bing",status READY, has 1 handler(s) for this service...

The command completed successfully

通过servername 连接

[oracle@bing ~]$ sqlplus system/system@192.168.184.200:1521/bing;

2、在cdb中查看当前container

SQL> show con_name

CON_NAME

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

CDB$ROOT

3、查看pdb

在cdb中查看该实例拥有哪些pdb

SQL> set linesize 500

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

CON_ID DBID NAME OPEN_MODE

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

2 1289148512 PDB$SEED READ ONLY

3 1949305541 PDBBING READ WRITE

4、连接pdb

sqlplus system/system@192.168.184.200/pdbbing;

5、pdb间切换

在cdb中切换到pdb

SQL> alter session set container=pdbbing;

Session altered.

切换回cdb

SQL> alter session set container=cdb$root;

Session altered.

6、CDB及PDB的启动与关闭

sqlplus / as sysdba 默认登录CDB

start ---启动

shutdown immediate --关闭，关闭回将pdb一并关闭。

默认情况下，启动后pdb为mount状态，需要手动open

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

CON_ID DBID NAME OPEN_MODE

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

2 1289148512 PDB$SEED READ ONLY

3 1949305541 PDBBING MOUNTED

SQL> alter session set container=pdbbing; ---切换至需要启动的pdb

Session altered.

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> startup ----启动alterdatabase open;

Database altered.

SQL> select status from v$instance;

STATUS

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

OPEN

SQL> select con_id,dbid,name,open_mode from v$pdbs;---在当前pdb下只能看到自己，而cdb下可以看到所有pdb。

CON_ID DBID NAME OPEN_MODE

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

3 1949305541 PDBBING READ WRITE

7、alter pluggable database命令维护pdb

在cdb中维护：

SQL> alter pluggable database pdbbing close;--关闭pdb名为pdbbing的数据库

Pluggable database altered.

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

CON_ID DBID NAME OPEN_MODE

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

2 1289148512 PDB$SEED READ ONLY

3 1949305541 PDBBING MOUNTED ----可以发现pdb状态关闭后为mount而不是shutdown

SQL> alter pluggable database all open; ---将改cdb中的所有pdb启动

Pluggable database altered.

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

CON_ID DBID NAME OPEN_MODE

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

2 1289148512 PDB$SEED READ ONLY

3 1949305541 PDBBING READ WRITE

SQL> alter pluggable database all close immediate; ---立即关闭cdb中所有pdb

Pluggable database altered.

在pdb中维护

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

CON_ID DBID NAME OPEN_MODE

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

2 1289148512 PDB$SEED READ ONLY

3 1949305541 PDBBING READ WRITE

SQL> alter session set container=pdb$seed; ---切换到pdb$seed数据库尝试关闭，该pdb为系统自建

Session altered.

SQL> alter pluggable database close; ---无法关闭该pdb，提示不能删除或变更

alter pluggable database close

ERROR at line 1:

ORA-65017: seed pluggable database may not be dropped or altered

SQL> alter session set container=pdbbing; --切换到我创建的pdb中

SQL> alter pluggable database open; --启动该pdb

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE

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

PDBBING READ WRITE

SQL> alter pluggable database close immediate; --关闭该pdb

Pluggable database altered.

8、设置自动启动pdbs

在cdb$root下执行

CREATE OR REPLACE TRIGGER openpdb

AFTER STARTUP ON DATABASE

BEGIN

EXECUTE IMMEDIATE 'ALTERPLUGGABLE DATABASE ALL OPEN';

END open_pdbs;

0 0