小丸子学Oracle 12c系列之——Oracle Pluggable Database

好久没有研究Oracle了，最近觉得有必要研究下Oracle 12c的新特性，下面主要是记录我在学习Oracle 12c新特性之pdb的相关内容。

1.Oracle Pluggable Database

1.1 根据seed创建pdb

SQL> select pdb_id,pdb_name,con_id from dba_pdbs;

PDB_ID PDB_NAME CON_ID

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

2 PDB$SEED 2

三种创建场景：

SQL> create pluggable database pdb01 admin user pdbadmin01 identified by oracle

default tablespace pdb_tbs

datafile '/u01/oracle/oradata/ASIAINFO/datafile/pdb_tbs01.dbf' size 100M;

SQL> create pluggable database pdb02 admin user pdbadmin02 identified by oracle

default tablespace pdb_tbs2 roles=(dba);

SQL> CREATE PLUGGABLE DATABASE pdb03 ADMIN USER pdbadmin03 IDENTIFIED BY oracle

STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)

DEFAULT TABLESPACE pdb_tbs3

DATAFILE '/u01/oracle/oradata/pdb03/pdb03.dbf' SIZE 250M AUTOEXTEND ON

PATH_PREFIX = '/u01/oracle/oradata/pdb03/'

FILE_NAME_CONVERT = ('/u01/oracle/oradata/ASIAINFO/datafile/', '/u01/oracle/oradata/pdb03/');

CREATE PLUGGABLE DATABASE pdb03 ADMIN USER pdbadmin03 IDENTIFIED BY oracle

ERROR at line 1:

ORA-01276: Cannot add file /u01/oracle/oradata/pdb03/o1_mf_system_cqsjkz37_.dbf. File has an Oracle Managed Files file name.

解决方法：

SQL> CREATE PLUGGABLE DATABASE pdb03 ADMIN USER pdbadmin03 IDENTIFIED BY oracle

STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)

DEFAULT TABLESPACE pdb_tbs3

DATAFILE '/u01/oracle/oradata/pdb03/pdb03.dbf' SIZE 250M AUTOEXTEND ON

PATH_PREFIX = '/u01/oracle/oradata/pdb03/'

FILE_NAME_CONVERT = ('/u01/oracle/oradata/ASIAINFO/datafile/o1_mf_system_cqsjkz37_.dbf', '/u01/oracle/oradata/pdb03/system01.dbf',

'/u01/oracle/oradata/ASIAINFO/datafile/o1_mf_sysaux_cqsjkz1r_.dbf', '/u01/oracle/oradata/pdb03/sysaux01.dbf',

'/u01/oracle/oradata/ASIAINFO/datafile/pdbseed_temp012016-07-06_04-33-42-PM.dbf','/u01/oracle/oradata/pdb03/pdbseed_temp01.dbf');

Pluggable database created.

SQL> select pdb_id,pdb_name,con_id,status from dba_pdbs;

PDB_ID PDB_NAME CON_ID STATUS

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

3 PDB01 3 NEW

2 PDB$SEED 2 NORMAL

1.2 克隆一个pdb

SQL> create pluggable database pdb02 from pdb01;

create pluggable database pdb02 from pdb01

ERROR at line 1:

ORA-65036: pluggable database PDB01 not open in required mode

解决方法：

SQL> alter pluggable database pdb01 open;

Warning: PDB altered with errors.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ WRITE NO

3 PDB01 READ WRITE YES

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> alter session set container=PDB01;

Session altered.

SQL> show con_name

CON_NAME

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

PDB01

SQL> create pluggable database pdb02 from pdb01;

create pluggable database pdb02 from pdb01

ERROR at line 1:

ORA-65040: operation not allowed from within a pluggable database

解决方法：

SQL> alter session set container=cdb$root;

Session altered.

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> create pluggable database pdb02 from pdb01;

Pluggable database created.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ WRITE NO

3 PDB01 READ WRITE YES

4 PDB02 MOUNTED

1.3 删除pdb

SQL> drop pluggable database PDB02 keep datafiles;

drop pluggable database PDB02 keep datafiles

ERROR at line 1:

ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged

解决方法：

SQL> alter pluggable database PDB02 unplug into '/u01/oracle/pdb01.xml';

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ WRITE NO

3 PDB01 READ WRITE YES

4 PDB02 MOUNTED

SQL> drop pluggable database PDB02 keep datafiles;

Pluggable database dropped.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ WRITE NO

3 PDB01 READ WRITE YES

1.4 启停pdb

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ WRITE NO

3 PDB01 READ WRITE YES

4 PDB02 READ WRITE YES

5 PDB03 MOUNTED

SQL> alter pluggable database PDB03 open ;

Warning: PDB altered with errors.

解决方法：

SQL> select name,cause ,type,message,status from pdb_plug_in_violations;

NAME CAUSE TYPE MESSAGE STATUS

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

PDB03 Sync Failure ERROR Sync PDB failed with ORA-959 during 'CREATE USER c##scott PENDING

IDENTIFIED BY * DEFAULT TABLESPACE users

QUOTA 20M ON users

TEMPORARY TABLESPACE temp container = all'

SQL> alter session set container=pdb03;

Session altered.

SQL> create tablespace users datafile '/u01/oracle/oradata/pdb03/user01.dbf' size 10M;

Tablespace created.

SQL> alter pluggable database pdb03 close;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

5 PDB03 MOUNTED

SQL> alter pluggable database pdb03 open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

5 PDB03 READ WRITE NO

1.5 连接pdb

[[email protected] datafile]$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.1.0 - Production on 13-JUL-2016 17:09:55

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.1.0.1.0 - Production

Start Date 12-JUL-2016 16:03:13

Uptime 1 days 1 hr. 6 min. 43 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Log File /u01/oracle/diag/tnslsnr/CIT-BATTON-BJTMP01/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CIT-BATTON-BJTMP01)(PORT=1521)))

Services Summary...

Service "asiainfo" has 1 instance(s).

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

Service "asiainfoXDB" has 1 instance(s).

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

Service "pdb01" has 1 instance(s).

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

Service "pdb02" has 1 instance(s).

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

Service "pdb03" has 1 instance(s).

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

The command completed successfully

[[email protected] datafile]$ sqlplus sys/oracle@`hostname`:1521/pdb03 as sysdba

SQL*Plus: Release 11.1.0.1.0 Production on Wed Jul 13 17:14:55 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 11.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

5 PDB03 READ WRITE NO

SQL> show con_name

CON_NAME

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

PDB03

SQL> alter session set container=cdb$root;

Session altered.

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ WRITE NO

3 PDB01 READ WRITE YES

4 PDB02 READ WRITE YES

5 PDB03 READ WRITE NO

1.5 unplug and plug pdb

SQL> alter pluggable database pdb03 unplug into '/u01/oracle/pdb03.xml';

alter pluggable database pdb03 unplug into '/u01/oracle/pdb03.xml'

ERROR at line 1:

ORA-65025: Pluggable database PDB03 is not closed on all instances.

解决方法：

SQL> alter pluggable database pdb03 close;

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ WRITE NO

3 PDB01 READ WRITE YES

4 PDB02 READ WRITE YES

5 PDB03 MOUNTED

SQL> alter pluggable database pdb03 unplug into '/u01/oracle/pdb03.xml';

Pluggable database altered.

SQL> select pdb_name,CON_ID,STATUS from dba_pdbs;

PDB_NAME CON_ID STATUS

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

PDB01 3 NORMAL

PDB$SEED 2 NORMAL

PDB02 4 NORMAL

PDB03 5 UNPLUGGED

SQL> drop pluggable database PDB03 keep datafiles;

Pluggable database dropped.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ WRITE NO

3 PDB01 READ WRITE YES

4 PDB02 READ WRITE YES

SQL> create pluggable database pdb04 using '/u01/oracle/pdb03.xml' move;

create pluggable database pdb04 using '/u01/oracle/pdb03.xml' move

ERROR at line 1:

ORA-65018: FILE_NAME_CONVERT or NOCOPY must be specified

SQL> create pluggable database pdb04 using '/u01/oracle/pdb03.xml' copy;

create pluggable database pdb04 using '/u01/oracle/pdb03.xml' copy

ERROR at line 1:

ORA-65018: FILE_NAME_CONVERT or NOCOPY must be specified

解决方法：

SQL> create pluggable database pdb04 using '/u01/oracle/pdb03.xml' copy

FILE_NAME_CONVERT = ('/u01/oracle/oradata/pdb03/system01.dbf', '/u01/oracle/oradata/pdb04/system01.dbf',

'/u01/oracle/oradata/pdb03/sysaux01.dbf', '/u01/oracle/oradata/pdb04/sysaux01.dbf',

'/u01/oracle/oradata/pdb03/pdbseed_temp01.dbf','/u01/oracle/oradata/pdb04/temp01.dbf',

'/u01/oracle/oradata/pdb03/pdb03.dbf', '/u01/oracle/oradata/pdb04/pdb03.dbf',

'/u01/oracle/oradata/pdb03/user01.dbf', '/u01/oracle/oradata/pdb04/user01.dbf');

Pluggable database created.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ WRITE NO

3 PDB01 READ WRITE YES

4 PDB02 READ WRITE YES

5 PDB04 MOUNTED

至此，pdb的一些常用操作演示到此。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20801486/viewspace-2122005/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20801486/viewspace-2122005/

小丸子学Oracle 12c系列之——Oracle Pluggable Database

继续阅读

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

手动安装Intel network I217-LM网卡的Linux驱动

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

nginx 安装错误信息解决

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

GitHub连夜封杀！这份阿里 10W 字内部 Java 字面试手册到底有多强？

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述