Oracle 12c:Container Database and Pluggable Database
在Oracle database 12c发行版中,引入了多租户的概念。而该概念的实现则是通过:数据库容器与可插拔数据库实现的。
在过去,一个数据库实例只能访问一个数据库(这是标准的运行状态:单实例模式)。
可能发生的情况是多个实例访问一个数据库,这就是Real Application Cluster的架构。
但从12c开始,一个实例是可能对应多个数据库的,这就是数据库的多租户架构(Multitenant Architecture)。
在一个多租户架构中只有一个多租户容器数据库(Container DB,CDB),多个用户可以在该容器库中创建多个可插拔数据库(Pluggable DB,PDB)。
对于访问数据库的客户端而言,PDB最终以普通的(非CDB)的形式展现,与12c之前的数据库访问与操纵没有区别。
(12C之前的数据库都是非CDB的。)
判断当前的数据库是否为CDB:
1 2 3 4 5 6 7 |
SQL> select name,cdb,open_mode,con_id from v$database; NAME CDB OPEN_MODE CON_ID --------- --- -------------------- ---------- ATLAS YES READ WRITE 0 SQL> |
v$database.cdb,标注了数据库实例的多租属性,如果为YES则表示该实例为容器库,如果为NO则表示该实例不是容器库。
需要注意的是:
一个库是否是CDB是在它创建时指定的,不能够将一个非CDB的库转换为CDB,也不能将一个CDB的库转为非CDB。
创建数据库时指定CDB有两种方法:
1.SQL语句建库
和过去建库的方式一样,通过“create database …”可以创建一个数据库。如果希望它为CDB的库,则需要在“create database …”的最后跟上选项“enable pluggable database”即可。否则,如果没有跟上“enable …”则创建的是非CDB的库。
2.DBCA图像化工具建库:
每一个CDB都会包含一个Root容器。
一个CDB旗下的所有PDB都属于该ROOT容器。
它包含了Oracle官方提供的元数据,以及公共用户。公共用户是每个容器中的PDB库的使用者。
ROOT容器的名称为:CDB$ROOT。
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> select sys_context('userenv','con_name') "Container DB" from dual; Container DB -------------------------------------------------------------------------------- CDB$ROOT SQL> |
每个CDB夜都会有一个种子PDB。
种子PDB是CDB用于创建新的PDB的模板,该模板由系统提供。
用户不能添加或者修改种子PDB中的对象。
种子PDB的名称为:PDB$SEED。
1 2 3 4 5 6 7 8 9 10 |
SQL> col name for a8 SQL> set linesize 300 SQL> run 1* select con_id,dbid,guid,name,open_mode from v$pdbs CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- -------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY SQL> |
PDB通常是为了支持特定的应用而被特定的用户专门创建的,它包含支持特定业务的特定特性及其结构数据与代码。
如下:
如上图所示,该CDB中包含了两个PDB:
一个种子PDB以及两个PDB。
两个独立的PDB分别服务于不同的业务系统。
在数据库端可以看到不同的PDB具有自己的管理账户,而对CDB的管理也是如此。
它们均有特定的用户实施数据库端的管理与运维任务。
可以将过去的(12c之前的)多个数据库整合到单个计算机上的单个数据库中。
这个过程称为:数据库整合,Database Consolidate。
创建一个PDB库。
通过SQL语句“create pluggable database …”可以创建一个PDB的数据库。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
SQL> select file_id,file_name,tablespace_name from dba_data_files order by 1; FILE_ID FILE_NAME TABLESPACE ---------- ------------------------------------------------------- ---------- 1 /oradata/ATLAS/datafile/o1_mf_system_b5cyjwrl_.dbf SYSTEM 3 /oradata/ATLAS/datafile/o1_mf_sysaux_b5cykjk4_.dbf SYSAUX 5 /oradata/ATLAS/datafile/o1_mf_undotbs1_b5cykxrb_.dbf UNDOTBS1 6 /oradata/ATLAS/datafile/o1_mf_users_b5cym96v_.dbf USERS SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- -------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY SQL> SQL> create pluggable database db_logic admin user db_admin identified by oracle; Pluggable database created. SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- -------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY 3 143360529 06E0B924516C1CA4E053827CA8C0A438 DB_LOGIC MOUNTED SQL> select file_id,file_name,tablespace_name from dba_data_files order by 1; FILE_ID FILE_NAME TABLESPACE ---------- ------------------------------------------------------- ---------- 1 /oradata/ATLAS/datafile/o1_mf_system_b5cyjwrl_.dbf SYSTEM 3 /oradata/ATLAS/datafile/o1_mf_sysaux_b5cykjk4_.dbf SYSAUX 5 /oradata/ATLAS/datafile/o1_mf_undotbs1_b5cykxrb_.dbf UNDOTBS1 6 /oradata/ATLAS/datafile/o1_mf_users_b5cym96v_.dbf USERS SQL> |
查看ALERT日志:
1 2 3 4 5 |
[root@ora12c trace]# pwd /u01/app/oracle/diag/rdbms/atlas/atlas/trace [root@ora12c trace]# ls -l | grep --color alert -rw-r----- 1 oracle oinstall 136096 Nov 2 20:54 alert_atlas.log [root@ora12c trace]# |
命令:tail -f alert_atlas.log
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Sun Nov 02 20:53:57 2014 create pluggable database db_logic admin user db_admin identified by * Sun Nov 02 20:54:01 2014 Opatch XML is skipped for PDB PDB$SEED (conid=2) APEX_040200.WWV_FLOW_SHARED_QRY_SQL_STMTS (SQL_STATEMENT) - CLOB populated Sun Nov 02 20:54:22 2014 **************************************************************** Pluggable Database DB_LOGIC with pdb id - 3 is created as UNUSABLE. If any errors are encountered before the pdb is marked as NEW, then the pdb must be dropped **************************************************************** Database Characterset for DB_LOGIC is ZHS16GBK Deleting old file#2 from file$ Deleting old file#4 from file$ Adding new file#7 to file$(old file#2) Adding new file#8 to file$(old file#4) Successfully created internal service db_logic at open ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local **************************************************************** Post plug operations are now complete. Pluggable database DB_LOGIC with pdb id - 3 is now marked as NEW. **************************************************************** Completed: create pluggable database db_logic admin user db_admin identified by * |
通过克隆创建一个PDB。
通过克隆的方式创建PDB,克隆的源,可以选择当前CDB中的另一个PDB,也可以通过DB Link选择另一个CDB中的某一个PDB。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- -------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY 3 143360529 06E0B924516C1CA4E053827CA8C0A438 DB_LOGIC READ WRITE SQL> create pluggable database allah from db_logic; Pluggable database created. SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- -------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY 3 143360529 06E0B924516C1CA4E053827CA8C0A438 DB_LOGIC READ WRITE 4 3559514509 06E0B924516E1CA4E053827CA8C0A438 ALLAH MOUNTED SQL> |
Alert日志:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
create pluggable database allah from db_logic Sun Nov 02 21:16:30 2014 Opatch XML is skipped for PDB DB_LOGIC (conid=3) APEX_040200.WWV_FLOW_SHARED_QRY_SQL_STMTS (SQL_STATEMENT) - CLOB populated Sun Nov 02 21:16:57 2014 **************************************************************** Pluggable Database ALLAH with pdb id - 4 is created as UNUSABLE. If any errors are encountered before the pdb is marked as NEW, then the pdb must be dropped **************************************************************** Database Characterset for ALLAH is ZHS16GBK Deleting old file#7 from file$ Deleting old file#8 from file$ Adding new file#9 to file$(old file#7) Adding new file#10 to file$(old file#8) Successfully created internal service allah at open ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local **************************************************************** Post plug operations are now complete. Pluggable database ALLAH with pdb id - 4 is now marked as NEW. **************************************************************** Completed: create pluggable database allah from db_logic |
注意:
通过克隆方式创建PDB,需要源PDB处于OPEN的模式。
否则你会报出下列错误:
1 2 3 4 5 6 7 8 |
SQL> create pluggable database allah from db_logic; create pluggable database allah from db_logic * ERROR at line 1: ORA-65036: pluggable database DB_LOGIC not open in required mode SQL> |
Alert Log:
1 2 3 4 5 6 |
create pluggable database allah from db_logic Sun Nov 02 21:07:40 2014 ************************************************************** Undo Create of Pluggable Database ALLAH with pdb id - 4. ************************************************************** ORA-65036 signalled during: create pluggable database allah from db_logic... |
启动一个PDB:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- -------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY 3 143360529 06E0B924516C1CA4E053827CA8C0A438 DB_LOGIC MOUNTED SQL> alter pluggable database db_logic open; Pluggable database altered. SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- -------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY 3 143360529 06E0B924516C1CA4E053827CA8C0A438 DB_LOGIC READ WRITE SQL> |
Alert日志:
1 2 3 4 5 6 7 8 9 |
Sun Nov 02 21:13:31 2014 alter pluggable database db_logic open Sun Nov 02 21:13:31 2014 Pluggable database DB_LOGIC dictionary check beginning Pluggable Database DB_LOGIC Dictionary check complete Database Characterset for DB_LOGIC is ZHS16GBK Opening pdb DB_LOGIC (3) with no Resource Manager plan active Pluggable database DB_LOGIC opened read write Completed: alter pluggable database db_logic open |
既然是PDB是可插拔的数据库。
那么接下来演示关于PDB的拔出与插入。
拔出一个PDB库:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 |
SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- -------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY 3 143360529 06E0B924516C1CA4E053827CA8C0A438 DB_LOGIC READ WRITE 4 3559514509 06E0B924516E1CA4E053827CA8C0A438 ALLAH MOUNTED SQL> alter pluggable database db_logic close; Pluggable database altered. SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- -------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY 3 143360529 06E0B924516C1CA4E053827CA8C0A438 DB_LOGIC MOUNTED 4 3559514509 06E0B924516E1CA4E053827CA8C0A438 ALLAH MOUNTED SQL> SQL> alter pluggable database db_logic unplug into '/home/oracle/db_logic.xml'; Pluggable database altered. SQL> col pdb_name for a8 SQL> select pdb_name,status from cdb_pdbs; PDB_NAME STATUS -------- --------- PDB$SEED NORMAL DB_LOGIC UNPLUGGED ALLAH NEW SQL> SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- -------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY 3 143360529 06E0B924516C1CA4E053827CA8C0A438 DB_LOGIC MOUNTED 4 3559514509 06E0B924516E1CA4E053827CA8C0A438 ALLAH MOUNTED SQL> SQL> select con_id,name from v$datafile order by con_id; CON_ID NAME ---------- ------------------------------------------------------------------------------------- 1 /oradata/ATLAS/datafile/o1_mf_system_b5cyjwrl_.dbf 1 /oradata/ATLAS/datafile/o1_mf_undotbs1_b5cykxrb_.dbf 1 /oradata/ATLAS/datafile/o1_mf_users_b5cym96v_.dbf 1 /oradata/ATLAS/datafile/o1_mf_sysaux_b5cykjk4_.dbf 2 /oradata/ATLAS/06DDE7A616EE107BE053827CA8C0CC0F/datafile/o1_mf_sysaux_b5cykscz_.dbf 2 /oradata/ATLAS/06DDE7A616EE107BE053827CA8C0CC0F/datafile/o1_mf_system_b5cyk7cv_.dbf 3 /oradata/ATLAS/06E0B924516C1CA4E053827CA8C0A438/datafile/o1_mf_system_b5dbcb48_.dbf 3 /oradata/ATLAS/06E0B924516C1CA4E053827CA8C0A438/datafile/o1_mf_sysaux_b5dbcb4w_.dbf 4 /oradata/ATLAS/06E0B924516E1CA4E053827CA8C0A438/datafile/o1_mf_sysaux_b5dcogy5_.dbf 4 /oradata/ATLAS/06E0B924516E1CA4E053827CA8C0A438/datafile/o1_mf_system_b5dcogxz_.dbf 10 rows selected. SQL> SQL> !ls -ltr /home/oracle total 8 -rw-r--r-- 1 oracle oinstall 4781 Nov 2 21:40 db_logic.xml SQL> !cat /home/oracle/db_logic.xml | wc -l 149 SQL> !cat /home/oracle/db_logic.xml <?xml version="1.0" encoding="UTF-8"?> <PDB> <xmlversion>1</xmlversion> <pdbname>DB_LOGIC</pdbname> <cid>3</cid> <byteorder>1</byteorder> <vsn>202375680</vsn> <vsns> <vsnnum>12.1.0.2.0</vsnnum> <cdbcompt>12.1.0.2.0</cdbcompt> <pdbcompt>12.1.0.2.0</pdbcompt> <vsnlibnum>0.0.0.0.22</vsnlibnum> <vsnsql>22</vsnsql> <vsnbsv>8.0.0.0.0</vsnbsv> </vsns> <dbid>143360529</dbid> <ncdb2pdb>0</ncdb2pdb> <cdbid>1218980312</cdbid> <guid>06E0B924516C1CA4E053827CA8C0A438</guid> <uscnbas>1481020</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>/oradata/ATLAS/06E0B924516C1CA4E053827CA8C0A438/datafile/o1_mf_system_b5dbcb48_.dbf</path> <afn>7</afn> <rfn>1</rfn> <createscnbas>1477297</createscnbas> <createscnwrp>0</createscnwrp> <status>1</status> <fileblocks>33280</fileblocks> <blocksize>8192</blocksize> <vsn>202375680</vsn> <fdbid>143360529</fdbid> <fcpsw>0</fcpsw> <fcpsb>1481016</fcpsb> <frlsw>0</frlsw> <frlsb>1</frlsb> <frlt>862594328</frlt> </file> </tablespace> <tablespace> <name>SYSAUX</name> <type>0</type> <tsn>1</tsn> <status>1</status> <issft>0</issft> <file> <path>/oradata/ATLAS/06E0B924516C1CA4E053827CA8C0A438/datafile/o1_mf_sysaux_b5dbcb4w_.dbf</path> <afn>8</afn> <rfn>4</rfn> <createscnbas>1477300</createscnbas> <createscnwrp>0</createscnwrp> <status>1</status> <fileblocks>77440</fileblocks> <blocksize>8192</blocksize> <vsn>202375680</vsn> <fdbid>143360529</fdbid> <fcpsw>0</fcpsw> <fcpsb>1481016</fcpsb> <frlsw>0</frlsw> <frlsb>1</frlsb> <frlt>862594328</frlt> </file> </tablespace> <tablespace> <name>TEMP</name> <type>1</type> <tsn>2</tsn> <status>1</status> <issft>0</issft> <bmunitsize>128</bmunitsize> <file> <path>/oradata/ATLAS/06E0B924516C1CA4E053827CA8C0A438/datafile/o1_mf_temp_b5dbcb4w_.dbf</path> <afn>3</afn> <rfn>1</rfn> <createscnbas>1477298</createscnbas> <createscnwrp>0</createscnwrp> <status>0</status> <fileblocks>2560</fileblocks> <blocksize>8192</blocksize> <vsn>202375680</vsn> <autoext>1</autoext> <maxsize>4194302</maxsize> <incsize>80</incsize> </file> </tablespace> <optional> <ncdb2pdb>0</ncdb2pdb> <csid>852</csid> <ncsid>2000</ncsid> <options> <option>APS=12.1.0.2.0</option> <option>CATALOG=12.1.0.2.0</option> <option>CATJAVA=12.1.0.2.0</option> <option>CATPROC=12.1.0.2.0</option> <option>CONTEXT=12.1.0.2.0</option> <option>DV=12.1.0.2.0</option> <option>JAVAVM=12.1.0.2.0</option> <option>OLS=12.1.0.2.0</option> <option>ORDIM=12.1.0.2.0</option> <option>OWM=12.1.0.2.0</option> <option>SDO=12.1.0.2.0</option> <option>XDB=12.1.0.2.0</option> <option>XML=12.1.0.2.0</option> <option>XOQ=12.1.0.2.0</option> </options> <olsoid>0</olsoid> <dv>0</dv> <APEX>4.2.5.00.08:1</APEX> <parameters> <parameter>processes=300</parameter> <parameter>memory_target=1140850688</parameter> <parameter>db_block_size=8192</parameter> <parameter>compatible='12.1.0.2.0'</parameter> <parameter>open_cursors=300</parameter> <parameter>enable_pluggable_database=TRUE</parameter> <spfile>*.db_securefile='PREFERRED'</spfile> </parameters> <tzvers> <tzver>primary version:18</tzver> <tzver>secondary version:0</tzver> </tzvers> <walletkey>0</walletkey> <hasclob>1</hasclob> <awr> <loadprofile>CPU Usage Per Sec=0.000000</loadprofile> <loadprofile>DB Block Changes Per Sec=0.000000</loadprofile> <loadprofile>Database Time Per Sec=0.000000</loadprofile> <loadprofile>Executions Per Sec=0.000000</loadprofile> <loadprofile>Hard Parse Count Per Sec=0.000000</loadprofile> <loadprofile>Logical Reads Per Sec=0.000000</loadprofile> <loadprofile>Logons Per Sec=0.000000</loadprofile> <loadprofile>Physical Reads Per Sec=0.000000</loadprofile> <loadprofile>Physical Writes Per Sec=0.000000</loadprofile> <loadprofile>Redo Generated Per Sec=0.000000</loadprofile> <loadprofile>Total Parse Count Per Sec=0.000000</loadprofile> <loadprofile>User Calls Per Sec=0.000000</loadprofile> <loadprofile>User Rollbacks Per Sec=0.000000</loadprofile> <loadprofile>User Transaction Per Sec=0.000000</loadprofile> </awr> <hardvsnchk>0</hardvsnchk> </optional> </PDB> SQL> SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- -------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY 3 143360529 06E0B924516C1CA4E053827CA8C0A438 DB_LOGIC MOUNTED 4 3559514509 06E0B924516E1CA4E053827CA8C0A438 ALLAH MOUNTED SQL> drop pluggable database db_logic; Pluggable database dropped. SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- -------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY 4 3559514509 06E0B924516E1CA4E053827CA8C0A438 ALLAH MOUNTED SQL> |
Alert日志:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Sun Nov 02 21:37:01 2014 alter pluggable database db_logic close Sun Nov 02 21:37:02 2014 ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local Pluggable database DB_LOGIC closed Completed: alter pluggable database db_logic close —————————————————————————————————————————— alter pluggable database db_logic unplug into '/home/oracle/db_logic.xml' Sun Nov 02 21:39:51 2014 Database Characterset for DB_LOGIC is ZHS16GBK Accessing dba_registry_sqlpatch hit error: 942 Opatch XML is skipped for PDB DB_LOGIC (conid=3) APEX_040200.WWV_FLOW_SHARED_QRY_SQL_STMTS (SQL_STATEMENT) - CLOB populated ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local Database Characterset for DB_LOGIC is ZHS16GBK Accessing dba_registry_sqlpatch hit error: 942 Opatch XML is skipped for PDB DB_LOGIC (conid=3) APEX_040200.WWV_FLOW_SHARED_QRY_SQL_STMTS (SQL_STATEMENT) - CLOB populated ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local Completed: alter pluggable database db_logic unplug into '/home/oracle/db_logic.xml' —————————————————————————————————————————— Sun Nov 02 21:51:10 2014 drop pluggable database db_logic Sun Nov 02 21:51:10 2014 Deleted Oracle managed file /oradata/ATLAS/06E0B924516C1CA4E053827CA8C0A438/datafile/o1_mf_temp_b5dbcb4w_.dbf Completed: drop pluggable database db_logic |
通过插入创建一个PDB。
通过上图可以看到,通过插入的方式增加一个PDB是基于PDB的数据文件组以及一个包含元数据的XML文件的。
eg.将上文中Unplug的PDB(db_logic)重新加入CDB
通过上文的操作,可以看到:
XML文件位于:/home/oracle/db_logic.xml
PDB有关的数据文件位于:/oradata/ATLAS/06E0B924516C1CA4E053827CA8C0A438/(DB_LOGIC的CON_ID为3,ID:3对应的数据文件均位于该路径下;同时,在XML文件中,也可以找到相应的记录)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[oracle@ora12c ~]$ cd /oradata/ATLAS/06E0B924516C1CA4E053827CA8C0A438/ [oracle@ora12c 06E0B924516C1CA4E053827CA8C0A438]$ ll total 4 drwxr-x--- 2 oracle oinstall 4096 Nov 2 21:51 datafile [oracle@ora12c 06E0B924516C1CA4E053827CA8C0A438]$ du -sh * 866M datafile [oracle@ora12c 06E0B924516C1CA4E053827CA8C0A438]$ cd datafile/ [oracle@ora12c datafile]$ ls -ltr total 886652 -rw-r----- 1 oracle oinstall 272637952 Nov 2 21:39 o1_mf_system_b5dbcb48_.dbf -rw-r----- 1 oracle oinstall 634396672 Nov 2 21:39 o1_mf_sysaux_b5dbcb4w_.dbf [oracle@ora12c datafile]$ du -sh * 606M o1_mf_sysaux_b5dbcb4w_.dbf 261M o1_mf_system_b5dbcb48_.dbf [oracle@ora12c datafile]$ [oracle@ora12c datafile]$ cd /home/oracle/ [oracle@ora12c ~]$ ls -ltr total 8 -rw-r--r-- 1 oracle oinstall 4781 Nov 2 21:40 db_logic.xml [oracle@ora12c ~]$ du -sh * 8.0K db_logic.xml [oracle@ora12c ~]$ |
插入PDB:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
SQL> show user USER is "SYS" SQL> col name for a8 SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- -------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY 4 3559514509 06E0B924516E1CA4E053827CA8C0A438 ALLAH MOUNTED SQL> SQL> create pluggable database db_logic_1 using '/home/oracle/db_logic.xml' nocopy; Pluggable database created. SQL> SQL> col name for a10 SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- ---------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY 3 143360529 06E0B924516C1CA4E053827CA8C0A438 DB_LOGIC_1 MOUNTED 4 3559514509 06E0B924516E1CA4E053827CA8C0A438 ALLAH MOUNTED SQL> SQL> select dbid,name,open_mode from v$pdbs; DBID NAME OPEN_MODE ---------- ---------- ---------- 3094230870 PDB$SEED READ ONLY 143360529 DB_LOGIC_1 MOUNTED 3559514509 ALLAH MOUNTED SQL> |
Alert日志:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Sun Nov 02 21:59:20 2014 create pluggable database db_logic_1 using '/home/oracle/db_logic.xml' nocopy Sun Nov 02 21:59:20 2014 **************************************************************** Pluggable Database DB_LOGIC_1 with pdb id - 3 is created as UNUSABLE. If any errors are encountered before the pdb is marked as NEW, then the pdb must be dropped **************************************************************** Database Characterset for DB_LOGIC_1 is ZHS16GBK Deleting old file#7 from file$ Deleting old file#8 from file$ Adding new file#11 to file$(old file#7) Adding new file#12 to file$(old file#8) Successfully created internal service db_logic_1 at open ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local **************************************************************** Post plug operations are now complete. Pluggable database DB_LOGIC_1 with pdb id - 3 is now marked as NEW. **************************************************************** Completed: create pluggable database db_logic_1 using '/home/oracle/db_logic.xml' nocopy |
CDB与PDB的切换:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- ---------- ---------- 2 3094230870 06DDE7A616EE107BE053827CA8C0CC0F PDB$SEED READ ONLY 3 143360529 06E0B924516C1CA4E053827CA8C0A438 DB_LOGIC_1 MOUNTED 4 3559514509 06E0B924516E1CA4E053827CA8C0A438 ALLAH MOUNTED SQL> alter session set container=db_logic_1; Session altered. SQL> show con_name CON_NAME ------------------------------ DB_LOGIC_1 SQL> SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- ---------- ---------- 3 143360529 06E0B924516C1CA4E053827CA8C0A438 DB_LOGIC_1 MOUNTED SQL> startup Pluggable Database opened. SQL> select con_id,dbid,guid,name,open_mode from v$pdbs; CON_ID DBID GUID NAME OPEN_MODE ---------- ---------- -------------------------------- ---------- ---------- 3 143360529 06E0B924516C1CA4E053827CA8C0A438 DB_LOGIC_1 READ WRITE SQL> SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ---------- ---------- DB_LOGIC_1 READ WRITE SQL> |