小丸子学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
[orauser@CIT-BATTON-BJTMP01 datafile]$ lsnrctl status

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

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

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

[orauser@CIT-BATTON-BJTMP01 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

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

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/