Oracle Database 12c创建Pluggable Database的两种方式及拔出、插入、删除Pluggable Database
来源:互联网 发布:淘宝可以删差评吗 编辑:程序博客网 时间:2024/05/16 02:30
dbca建库后的目录结构(包括一个CDB,10个PDB):
[root@db-12c tree-1.6.0]# tree /opt/oracle/oradata
/opt/oracle/oradata
`-- cdb
|-- control01.ctl
|-- pdb1
| |-- pdb1_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb10
| |-- pdb10_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb2
| |-- pdb2_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb3
| |-- pdb3_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb4
| |-- pdb4_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb5
| |-- pdb5_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb6
| |-- pdb6_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb7
| |-- pdb7_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb8
| |-- pdb8_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb9
| |-- pdb9_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdbseed
| |-- pdbseed_temp01.dbf
| |-- sysaux01.dbf
| `-- system01.dbf
|-- redo01.log
|-- redo02.log
|-- redo03.log
|-- sysaux01.dbf
|-- system01.dbf
|-- temp01.dbf
|-- undotbs01.dbf
`-- users01.dbf
*****************************从seed创建PDB mypdb1:**************************************************
SQL>CREATE PLUGGABLE DATABASE mypdb1admin user lexidentifiedby oracle
2 STORAGE (MAXSIZE2G MAX_SHARED_TEMP_SIZE100M)
3 DEFAULT TABLESPACE lex datafile'/opt/oracle/oradata/cdb/mypdb1/lex.dbf'size 100M
4 PATH_PREFIX ='/opt/oracle/oradata/cdb/mypdb1/'
5 FILE_NAME_CONVERT = ('/opt/oracle/oradata/cdb/pdbseed/','/opt/oracle/oradata/cdb/mypdb1/');
Pluggable databasecreated.
[root@db-12c tree-1.6.0]# tree /opt/oracle/oradata
/opt/oracle/oradata
`-- cdb
|-- control01.ctl
|-- mypdb1
| |-- lex.dbf
| |-- pdbseed_temp01.dbf
| |-- sysaux01.dbf
| `-- system01.dbf
|-- pdb1
| |-- pdb1_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb10
| |-- pdb10_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb2
| |-- pdb2_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb3
| |-- pdb3_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb4
| |-- pdb4_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb5
| |-- pdb5_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb6
| |-- pdb6_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb7
| |-- pdb7_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb8
| |-- pdb8_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb9
| |-- pdb9_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdbseed
| |-- pdbseed_temp01.dbf
| |-- sysaux01.dbf
| `-- system01.dbf
|-- redo01.log
|-- redo02.log
|-- redo03.log
|-- sysaux01.dbf
|-- system01.dbf
|-- temp01.dbf
|-- undotbs01.dbf
`-- users01.dbf
14 directories,56 files
*****************************从mypdb1克隆mypdb2:**************************************************
mkdir -p /opt/oracle/oradata/cdb/mypdb2
关闭mypdb1:
SQL>alter pluggable database mypdb1close immediate;
Pluggable databasealtered.
启动mypdb1到只读模式:
SQL>alter pluggable database mypdb1open readonly;
Pluggable databasealtered.
SQL>CREATE PLUGGABLE DATABASE mypdb2FROM mypdb1
2 FILE_NAME_CONVERT = ('/opt/oracle/oradata/cdb/mypdb1/','/opt/oracle/oradata/cdb/mypdb2/')
3 PATH_PREFIX ='/opt/oracle/oradata/cdb/mypdb2/';
Pluggable databasecreated.
SQL>alter pluggable database mypdb1close immediate;
Pluggable databasealtered.
SQL>alter pluggable database mypdb2open;
Pluggable databasealtered.
SQL>alter pluggable database mypdb1open;
Pluggable databasealtered.
SQL>select pdb_id,pdb_name,dbid,STATUS,CREATION_SCNfrom dba_pdbs;
PDB_ID PDB_NAME DBID STATUS CREATION_SCN
------------------------------ ---------- ------------- ------------
3 PDB1 3329419267 NORMAL 1998421
2 PDB$SEED 4064317774 NORMAL 1720760
4 PDB2 3887928480 NORMAL 1999367
5 PDB3 3937800481 NORMAL 2000203
6 PDB4 3832418168 NORMAL 2001078
7 PDB5 3882200487 NORMAL 2001887
8 PDB6 3732932822 NORMAL 2002739
9 PDB7 3782796556 NORMAL 2003949
10 PDB8 3678118471 NORMAL 2004808
11 PDB9 2843194490 NORMAL 2005714
12 PDB10 3756068798 NORMAL 2006533
PDB_ID PDB_NAME DBID STATUS CREATION_SCN
------------------------------ ---------- ------------- ------------
14 MYPDB2 4286232239 NORMAL 2184097
13 MYPDB1 3993145774 NORMAL 2155249
13 rows selected.
*****************************UNPLUG A PDBFROM CDB:************************************
关闭mypdb2:
SQL>alter pluggable database mypdb2close immediate;
Pluggable databasealtered.
SQL>alter pluggable database mypdb2 unpluginto '/opt/oracle/oradata/cdb/mypdb2/mypdb2.xml';
Pluggable databasealtered.
The details about themetadata describing mypdb2 are storedin the XMLfile/opt/oracle/oradata/cdb/mypdb2/mypdb2.xml
mypdb2.xml内容:
<?xml version="1.0" encoding="UTF-8"?><PDB> <pdbname>MYPDB2</pdbname> <cid>14</cid> <byteorder>1</byteorder> <vsn>202375168</vsn> <dbid>4286232239</dbid> <cdbid>1938322612</cdbid> <guid>E26728EA9B9B0277E043E201A8C0DF9F</guid> <uscnbas>2186735</uscnbas> <uscnwrp>0</uscnwrp> <rdba>4194824</rdba> <tablespace> <name>SYSTEM</name> <type>0</type> <tsn>0</tsn> <status>1</status> <issft>0</issft> <file> <path>/opt/oracle/oradata/cdb/mypdb2/system01.dbf</path> <afn>49</afn> <rfn>1</rfn> <createscnbas>2184097</createscnbas> <createscnwrp>0</createscnwrp> <status>1</status> <fileblocks>34560</fileblocks> <blocksize>8192</blocksize> <vsn>202375168</vsn> <fdbid>4286232239</fdbid> <fcpsw>0</fcpsw> <fcpsb>2186692</fcpsb> <frlsw>0</frlsw> <frlsb>1720082</frlsb> <frlt>821719092</frlt> </file> </tablespace> <tablespace> <name>SYSAUX</name> <type>0</type> <tsn>1</tsn> <status>1</status> <issft>0</issft> <file> <path>/opt/oracle/oradata/cdb/mypdb2/sysaux01.dbf</path> <afn>50</afn> <rfn>4</rfn> <createscnbas>2184100</createscnbas> <createscnwrp>0</createscnwrp> <status>1</status> <fileblocks>87040</fileblocks> <blocksize>8192</blocksize> <vsn>202375168</vsn> <fdbid>4286232239</fdbid> <fcpsw>0</fcpsw> <fcpsb>2186692</fcpsb> <frlsw>0</frlsw> <frlsb>1720082</frlsb> <frlt>821719092</frlt> </file> </tablespace> <tablespace> <name>TEMP</name> <type>1</type> <tsn>2</tsn> <status>1</status> <issft>0</issft> <bmunitsize>128</bmunitsize> <file> <path>/opt/oracle/oradata/cdb/mypdb2/pdbseed_temp01.dbf</path> <afn>14</afn> <rfn>1</rfn> <createscnbas>2184098</createscnbas> <createscnwrp>0</createscnwrp> <status>0</status> <fileblocks>2560</fileblocks> <blocksize>8192</blocksize> <vsn>202375168</vsn> <autoext>1</autoext> <maxsize>4194302</maxsize> <incsize>80</incsize> </file> </tablespace> <tablespace> <name>LEX</name> <type>0</type> <tsn>3</tsn> <status>1</status> <issft>0</issft> <file> <path>/opt/oracle/oradata/cdb/mypdb2/lex.dbf</path> <afn>51</afn> <rfn>42</rfn> <createscnbas>2184103</createscnbas> <createscnwrp>0</createscnwrp> <status>1</status> <fileblocks>12800</fileblocks> <blocksize>8192</blocksize> <vsn>202375168</vsn> <fdbid>4286232239</fdbid> <fcpsw>0</fcpsw> <fcpsb>2186692</fcpsb> <frlsw>0</frlsw> <frlsb>1720082</frlsb> <frlt>821719092</frlt> </file> </tablespace> <optional> <csid>873</csid> <ncsid>871</ncsid> <options> <option>APS=12.1.0.1.0</option> <option>CATALOG=12.1.0.1.0</option> <option>CATJAVA=12.1.0.1.0</option> <option>CATPROC=12.1.0.1.0</option> <option>CONTEXT=12.1.0.1.0</option> <option>DV=12.1.0.1.0</option> <option>JAVAVM=12.1.0.1.0</option> <option>OLS=12.1.0.1.0</option> <option>ORDIM=12.1.0.1.0</option> <option>OWM=12.1.0.1.0</option> <option>SDO=12.1.0.1.0</option> <option>XDB=12.1.0.1.0</option> <option>XML=12.1.0.1.0</option> <option>XOQ=12.1.0.1.0</option> </options> <olsoid>0</olsoid> <dv>0</dv> <ncdb2pdb>0</ncdb2pdb> <APEX>4.2.0.00.27:1</APEX> <parameters> <parameter>processes=300</parameter> <parameter>sga_target=1426063360</parameter> <parameter>db_block_size=8192</parameter> <parameter>compatible=12.1.0.0.0</parameter> <parameter>open_cursors=300</parameter> <parameter>pga_aggregate_target=471859200</parameter> <parameter>enable_pluggable_database=TRUE</parameter> </parameters> <tzvers> <tzver>primary version:18</tzver> <tzver>secondary version:0</tzver> </tzvers> <walletkey>0</walletkey> </optional></PDB>
SQL>select pdb_id,pdb_name,dbid,STATUS,CREATION_SCNfrom dba_pdbs;
PDB_ID PDB_NAME DBID STATUS CREATION_SCN
------------------------------ ---------- ------------- ------------
3 PDB1 3329419267 NORMAL 1998421
2 PDB$SEED 4064317774 NORMAL 1720760
4 PDB2 3887928480 NORMAL 1999367
5 PDB3 3937800481 NORMAL 2000203
6 PDB4 3832418168 NORMAL 2001078
7 PDB5 3882200487 NORMAL 2001887
8 PDB6 3732932822 NORMAL 2002739
9 PDB7 3782796556 NORMAL 2003949
10 PDB8 3678118471 NORMAL 2004808
11 PDB9 2843194490 NORMAL 2005714
12 PDB10 3756068798 NORMAL 2006533
PDB_ID PDB_NAME DBID STATUS CREATION_SCN
------------------------------ ---------- ------------- ------------
14 MYPDB2 4286232239 UNPLUGGED 2184097
13 MYPDB1 3993145774 NORMAL 2155249
13 rows selected.
SQL>drop pluggable database mypdb2;
Pluggable databasedropped.
SQL>select pdb_id,pdb_name,dbid,STATUS,CREATION_SCNfrom dba_pdbs;
PDB_ID PDB_NAME DBID STATUS CREATION_SCN
------------------------------ ---------- ------------- ------------
3 PDB1 3329419267 NORMAL 1998421
2 PDB$SEED 4064317774 NORMAL 1720760
4 PDB2 3887928480 NORMAL 1999367
5 PDB3 3937800481 NORMAL 2000203
6 PDB4 3832418168 NORMAL 2001078
7 PDB5 3882200487 NORMAL 2001887
8 PDB6 3732932822 NORMAL 2002739
9 PDB7 3782796556 NORMAL 2003949
10 PDB8 3678118471 NORMAL 2004808
11 PDB9 2843194490 NORMAL 2005714
12 PDB10 3756068798 NORMAL 2006533
PDB_ID PDB_NAME DBID STATUS CREATION_SCN
------------------------------ ---------- ------------- ------------
13 MYPDB1 3993145774 NORMAL 2155249
12 rows selected.
*****************************PLUG A PLUGGALBE DATABASETO A CDB**************************
SQL>create pluggable database mypdb2using '/opt/oracle/oradata/cdb/mypdb2/mypdb2.xml'
2 nocopy;
Pluggable databasecreated.
SQL>select pdb_id,pdb_name,dbid,STATUS,CREATION_SCNfrom dba_pdbs;
PDB_ID PDB_NAME DBID STATUS CREATION_SCN
------------------------------ ---------- ------------- ------------
3 PDB1 3329419267 NORMAL 1998421
2 PDB$SEED 4064317774 NORMAL 1720760
4 PDB2 3887928480 NORMAL 1999367
5 PDB3 3937800481 NORMAL 2000203
6 PDB4 3832418168 NORMAL 2001078
7 PDB5 3882200487 NORMAL 2001887
8 PDB6 3732932822 NORMAL 2002739
9 PDB7 3782796556 NORMAL 2003949
10 PDB8 3678118471 NORMAL 2004808
11 PDB9 2843194490 NORMAL 2005714
12 PDB10 3756068798 NORMAL 2006533
PDB_ID PDB_NAME DBID STATUS CREATION_SCN
------------------------------ ---------- ------------- ------------
13 MYPDB1 3993145774 NORMAL 2155249
14 MYPDB2 4286232239NEW 2187230
13 rows selected.
SQL>alter pluggable database mypdb2open;
Pluggable databasealtered.
SQL>select pdb_id,pdb_name,dbid,STATUS,CREATION_SCNfrom dba_pdbs;
PDB_ID PDB_NAME DBID STATUS CREATION_SCN
------------------------------ ---------- ------------- ------------
3 PDB1 3329419267 NORMAL 1998421
2 PDB$SEED 4064317774 NORMAL 1720760
4 PDB2 3887928480 NORMAL 1999367
5 PDB3 3937800481 NORMAL 2000203
6 PDB4 3832418168 NORMAL 2001078
7 PDB5 3882200487 NORMAL 2001887
8 PDB6 3732932822 NORMAL 2002739
9 PDB7 3782796556 NORMAL 2003949
10 PDB8 3678118471 NORMAL 2004808
11 PDB9 2843194490 NORMAL 2005714
12 PDB10 3756068798 NORMAL 2006533
PDB_ID PDB_NAME DBID STATUS CREATION_SCN
------------------------------ ---------- ------------- ------------
13 MYPDB1 3993145774 NORMAL 2155249
14 MYPDB2 4286232239 NORMAL 2187230
13 rows selected.
*****************************彻底删除PDB:******************************
SQL>alter pluggable database mypdb2close immediate;
Pluggable databasealtered.
SQL>DROP PLUGGABLE DATABASE mypdb2 INCLUDING DATAFILES;
Pluggable databasedropped.
[root@db-12c mypdb2]# tree/opt/oracle/oradata
/opt/oracle/oradata
`-- cdb
|-- control01.ctl
|-- mypdb1
| |-- lex.dbf
| |-- pdbseed_temp01.dbf
| |-- sysaux01.dbf
| `-- system01.dbf
|-- mypdb2
| `-- mypdb2.xml
|-- pdb1
| |-- pdb1_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb10
| |-- pdb10_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb2
| |-- pdb2_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb3
| |-- pdb3_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb4
| |-- pdb4_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb5
| |-- pdb5_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb6
| |-- pdb6_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb7
| |-- pdb7_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb8
| |-- pdb8_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdb9
| |-- pdb9_users01.dbf
| |-- sysaux01.dbf
| |-- system01.dbf
| `-- temp01.dbf
|-- pdbseed
| |-- pdbseed_temp01.dbf
| |-- sysaux01.dbf
| `-- system01.dbf
|-- redo01.log
|-- redo02.log
|-- redo03.log
|-- sysaux01.dbf
|-- system01.dbf
|-- temp01.dbf
|-- undotbs01.dbf
`-- users01.dbf
14 directories,57 files
- Oracle Database 12c创建Pluggable Database的两种方式及拔出、插入、删除Pluggable Database
- Oracle 12c Pluggable Database Architecture
- Oracle 12c 新特性-Pluggable Database
- Oracle 12c – Rename Pluggable Database
- ORACLE 12C ——03 PDB(Pluggable Database)的创建
- 12c新特性,Pluggable Database
- Oracle 12C Auto Start PDB Pluggable Database
- Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c
- 【12c新特性】12c中如何自动启动PDB Pluggable Database
- Oracle创建Database Link的两种方式
- oracle12c新特点之可插拔数据库(Pluggable Database,PDB)
- Oracle创建、删除DATABASE LINK
- 创建oracle数据库连接(database link)的两种方法
- 创建oracle数据库连接(database link)的两种方法
- Oracle Database 12c 如何创建数据库
- Oracle Database 12c 创建scott账户
- Oracle Database 12c cdb/pdb用户的创建
- Oracle Database 12c 数据库简单实例的创建过程
- cvRepeat、cvFlip、cvSlip等函数使用
- java时间格式大全
- 小米2013校园招聘笔试题-最大连续子序列乘积
- (spring+hibernate)java.lang.NoSuchMethodError: org.objectweb.asm.ClassVisitor.visit(IILjava/lang/Str
- 个人重构机房收费系统DataGridView显示用户信息
- Oracle Database 12c创建Pluggable Database的两种方式及拔出、插入、删除Pluggable Database
- 大数的四则运算
- Knockout.js随手记(7)
- 10392 - Factoring Large Numbers
- debugfs, tmpfs, proc, sysfs 介绍
- android 中 系统日期时间的获取
- 学生信息管理系统问题(一)
- http://acm.hdu.edu.cn/showproblem.php?pid=1789
- Linux┊理解devfs、sysfs、udev