惜分飞oracle,惜分飞 - 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788...

ORACLE 12C中提出来CDB和PDB的概念,对于ORACLE的数据库来说,确实是一个新东西,他们可以分别理解为容器和插件(PDB插入在CDB中),CDB的管理和传统数据库区别不大,本篇文章对PDB的部分操作进行了简单说明(创建PDB,OPEN PDB,DROP PDB,Plug PDB,Unplugging PDB)

CREATE PDB

SQL> SELECT NAME,CDB FROM V$DATABASE;

NAME CDB

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

XIFENFEI YES

SQL> select pdb_id,pdb_name,dbid,STATUS,CREATION_SCN from dba_pdbs;

PDB_ID PDB_NAME DBID STATUS CREATION_SCN

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

2 PDB$SEED 4044122081 NORMAL 1661281

3 FF 1565322182 NORMAL 1720654

SQL> CREATE PLUGGABLE DATABASE xff_db ADMIN USER xff IDENTIFIED BY xifenfei

2 STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)

3 DEFAULT TABLESPACE xifenfei

4 DATAFILE '/u01/app/oracle/oradata/xifenfei/xff/xifenfei01.dbf' SIZE 25M AUTOEXTEND ON

5 PATH_PREFIX = '/u01/app/oracle/oradata/xifenfei/xff/'

6 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/xifenfei/pdbseed/',

7 '/u01/app/oracle/oradata/xifenfei/xff/');

Pluggable database created.

SQL> select pdb_id,pdb_name,dbid,STATUS,CREATION_SCN from dba_pdbs;

PDB_ID PDB_NAME DBID STATUS CREATION_SCN

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

2 PDB$SEED 4044122081 NORMAL 1661281

3 FF 1565322182 NORMAL 1720654

4 XFF_DB 2272981748 NEW 1771028

OPEN PDB

SQL> alter pluggable database xff_db open;

Pluggable database altered.

SQL> select pdb_id,pdb_name,dbid,STATUS,CREATION_SCN from dba_pdbs;

PDB_ID PDB_NAME DBID STATUS CREATION_SCN

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

2 PDB$SEED 4044122081 NORMAL 1661281

3 FF 1565322182 NORMAL 1720654

4 XFF_DB 2272981748 NORMAL 1771028

SQL> alter pluggable database all close immediate;

Pluggable database altered.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

CON_ID DBID NAME OPEN_MODE

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

2 4044122081 PDB$SEED READ ONLY

3 1565322182 FF MOUNTED

4 2272981748 XFF_DB MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

CON_ID DBID NAME OPEN_MODE

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

2 4044122081 PDB$SEED READ ONLY

3 1565322182 FF READ WRITE

4 2272981748 XFF_DB READ WRITE

Unplugging a PDB from a CDB

SQL> alter pluggable database FF close immediate;

Pluggable database altered.

SQL> alter pluggable database ff UNPLUG into '/tmp/ff.xml';

Pluggable database altered.

DROP PDB

SQL> DROP PLUGGABLE DATABASE xff_db INCLUDING DATAFILES;

Pluggable database dropped.

SQL> DROP PLUGGABLE DATABASE ff INCLUDING DATAFILES;

Pluggable database dropped.

Plug Unplugged PDB into CDB

SQL> SELECT NAME,CDB FROM V$DATABASE;

NAME CDB

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

XFF_L YES

SQL> create pluggable database ff using '/tmp/ff.xml'

2 copy file_name_convert=('/u01/app/oracle/oradata/xifenfei/FF/','/u01/app/oracle/oradata/xff_l/xff');

Pluggable database created.

SQL> exec DBMS_PDB.SYNC_PDB();

PL/SQL procedure successfully completed.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

CON_ID DBID NAME OPEN_MODE

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

2 4043918109 PDB$SEED READ ONLY

3 2346805300 LX1 MOUNTED

4 2385557792 LX2 MOUNTED

5 1565384817 FF MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

CON_ID DBID NAME OPEN_MODE

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

2 4043918109 PDB$SEED READ ONLY

3 2346805300 LX1 READ WRITE

4 2385557792 LX2 READ WRITE

5 1565384817 FF READ WRITE

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/xff_l/system01.dbf

/u01/app/oracle/oradata/xff_l/pdbseed/system01.dbf

/u01/app/oracle/oradata/xff_l/sysaux01.dbf

/u01/app/oracle/oradata/xff_l/pdbseed/sysaux01.dbf

/u01/app/oracle/oradata/xff_l/undotbs01.dbf

/u01/app/oracle/oradata/xff_l/users01.dbf

/u01/app/oracle/oradata/xff_l/LX1/system01.dbf

/u01/app/oracle/oradata/xff_l/LX1/sysaux01.dbf

/u01/app/oracle/oradata/xff_l/LX1/LX1_users01.dbf

/u01/app/oracle/oradata/xff_l/LX2/system01.dbf

/u01/app/oracle/oradata/xff_l/LX2/sysaux01.dbf

/u01/app/oracle/oradata/xff_l/LX2/LX2_users01.dbf

/u01/app/oracle/oradata/xff_l/xffsystem01.dbf

/u01/app/oracle/oradata/xff_l/xffsysaux01.dbf

/u01/app/oracle/oradata/xff_l/xffSAMPLE_SCHEMA_users01.dbf

/u01/app/oracle/oradata/xff_l/xffexample01.dbf

16 rows selected.

补充说明:本部分内容比较多,本篇blog,只是对其中的很小一部分进行了测试,确实证明可以对pdb实现在不同的cdb中实现迁移,对于该项操作,可以在dbca和gc中实现相同操作.由于12c数据库尚未正式发布,该部分功能只是出于个人测试目的.