oracle最容易忽视特性吕海波美创

OracleOracle正式的名称是：MultitenantArchitecture

Ø一个CDB可以有N个PDB（至少1Ø所有PDB共享同一个SGA，和一Ø每个PDB都是一个相对独立的数RedoØ当CDB是OPEN状态时，它里面的

Ø一个CDB可以有N个PDB（至少1Ø所有PDB共享同一个SGA，和一 Ø每个PDB都是一个相对独立的数RedoØ当CDB是OPEN状态时，它里面的

SQL>colpdb_nameforSQL>selectpdb_id,pdb_name,dbid,con_uid,guid,status,con_idfrom PDB时，就是ClonePDB$SEED为一个新的PDB。 ØØ手动创建，步骤同非 MAXINSTANCES2MAXLOGHISTORYMAXLOGFILESMAXLOGMEMBERSDATAFILE'/export/home/oradb/oradata/CDBV4/system01.dbf'SIZE300MREUSEAUTOEXTENDONNEXT10240KMAXSIZEUNLIMITEDEXTENTMANAGEMENTLOCALSYSAUXDATAFILE'/export/home/oradb/oradata/CDBV4/sysaux01.dbf'SIZE200MREUSEAUTOEXTENDONNEXT10240KMAXSIZEUNLIMITEDSMALLFILEDEFAULTTEMPORARYTABLESPACETEMPTEMPFILE'/export/home/oradb/oradata/CDBV4/temp01.dbf'SIZE20MREUSEAUTOEXTENDONNEXT640KMAXSIZEUNLIMITEDSMALLFILEUNDOTABLESPACE"UNDOTBS1"DATAFILE'/export/home/oradb/oradata/CDBV4/undotbs01.dbf'SIZE100MREUSEAUTOEXTENDONNEXT5120KMAXSIZEUNLIMITEDCHARACTERSETWE8MSWIN1252NATIONALCHARACTERSETAL16UTF16LOGFILEGROUP1('/export/home/oradb/oradata/CDBV4/redo01.log')SIZE100M,GROUP2('/export/home/oradb/oradata/CDBV4/redo02.log')SIZE100M,GROUP3('/export/home/oradb/oradata/CDBV4/redo03.log')SIZE100MUSERSYSIDENTIFIEDBY"Oracle1"USERSYSTEMIDENTIFIEDBY"Oracle1"enablepluggableseedfile_name_convert: 创建新的bash-3.2$sqlplus/asSQL>CREATEPLUGGABLEDATABASEpdbtest1ADMINUSERdba1IDENTIFIEDBYa或SQL>CREATEPLUGGABLEDATABASEpdbtest2FROM 连接到PDB的两种方式Ø先进入cdb$root，再altersessionset bash-3.2$sqlplus/assysdbaSQL>showcon_nameSQL>altersessionsetØconn.../.@TNS_NAME，使用tns_name直接进入指定的Connectedto: pdbtest2=(DESCRIPTION=(CONNECT_DATA=(SERVER=))

PMON听 所有已STATUSoftheLISTENER

Service"pdbtest2"has1Service"pdbtest4"has1instance(s).ThecommandcompletedsuccessfullySQLshowparameterservi Ø Ø视图的变化：CDB_DBA_ALL_SQL>colownerfora15 只有当PDBOPEN时，才能在cdb_*视图中查到相关PDB的对象。 原来的打开数据库方式，只是打开CDB$ROOT和bash-3.2$sqlplus/assysdbaConnectedtoanidleinstance.SQL> FixedSizeVariableSizeRedoBuffersDatabaseopened.

SQL>selectcon_id,name,open_modefromv$pdbs;CON_IDNAME

Ø打开其他PDBalterpluggabledatabasePDB_NAME1[,PDB_NAME2,……]open[READONLY alterpluggabledatabasePDB_NAME1[,PDB_NAME2,……]close[immediate]; Ø另一种打开PDBbash-3.2$sqlplus/assysdbaConnectedto:Sessionaltered. SQL>showSQL>altersystemsetopen_cursors=1000;Systemaltered.SQL>showparameter SQL>showcon_name

SQL>showparameter SQL>createpfile='/export/home/oracle/pdbtest2.ora'fromspfile;Filecreated.bash-3.2$cat SQL>altersystemsetmemory_target=500m;altersystemsetmemory_target=500m*ERRORatlineORA-65040:operationnotallowedfromwithinapluggableSQL>showcon_nameSQL>altersystemsetmemory_target=500m;Systemaltered.

TS#NAME 01113141510212221303231424340415rows 用户分两类：用户和用户。mon用户一旦创建，将在每个PDB中都存在，可以说是全局用户。的内部用户都是mon用户，比如SYS和SYSTEM。DBA自已 ØSQL>createuserc##u2identifiedbya;Usercreated. SQL>createuserc##u3identifiedbya;*ERRORatline rmantarget12format11format'/export/home/oradb/oradata/CDBV3/pdbtest4/system01.dbf'FROMBACKUPSET'/export/home/oracle/pdb/PDBTEST_01qnvmlg_1_1.pdb';setserveroutputonl_resultBOOLEAN;BEGINl_result:=patibility(pdb_descr_file=>'/export/home/oracle/pdb/CDBV3_test1.xml',pdb_name =>'pdbtest1');IFl_resultTHENDBMS_OUTPUT.PUT_LINE('compatible');ELSEDBMS_OUTPUT.PUT_LINE('patible');ENDIF;/ file_name_convert=NONENOCOPYtempfilereuse; alterdatabaseopenreadonly;/altersessionsetcontainer=orcl;In-MemoryOptionShareShareBufferIn-MemoryTXmemoryArea中。在IMCU中的数据。TxJournal中记录 In-MemoryOptionØ设置InMemoryAreaSQL>altersystemsetinmemory_size=100mscope=spfile;Systemaltered.ØSQL>shutdownDatabaseclosed.ORACLEinstanceshutdown.SQL>startup FixedSizeVariableSizeRedoBuffersIn-MemoryAreaDatabaseopened.

Ø加载表到IMSQL>altertablet2inmemory;Tablealtered.Ø取消In-MemeroyAreaSQL>altertablet2noinmemory;Tablealtered.ØSQL>selectcount(*)from SQL>selectcount(*)from nhashnhash|Id||Name||Cost(%CPU)|||Id||Name|Rows|Cost(%CPU)|||0|SELECTSTATEMENT

1|515(1)|00:00:01

|0|SELECT

1

19(0)|00:00:01|1|SORT

1

|1|SORT

1 |2|TABLEACCESSFULL|T2

461K|515(1)|00:00:01

|2|TABLEACCESSINMEMORYFULL|

| 19(0)|00:0 dynamicstatisticsused:dynamicsampling

dynamicstatisticsused:dynamicsampling0recursivecalls0dbblockgets1796consistent1788physical0redo542bytessentviaSQL*Net551bytesreceivedviaSQL*Netfrom2SQL*Netroundtripsto/from0sorts0sorts1rows

0recursivecalls0dbblockgets8consistentgets0physical0redo542bytessentviaSQL*Net551bytesreceivedviaSQL*Netfrom2SQL*Netroundtripsto/from0sorts0sorts1rows测试表T2大小15M，IDnomemory(微秒18707（全表扫描79508（全表扫描Where781（索引扫描IMCU_Addr30IMCU_Addr410IMCU_Addr10Min:Max: 行扫描操作。比如查询条件是ID>20andID<=100的列值，图中第二个IMCU中，ID值的范RedoAllocationLatchLogBufferPublicRedoStrand

Ø状态，开始等待LogFileSync。PublicStrandPublicStrandMessageArea中的消息，通知某个前台进程写Redo完成、LogFileSync结束。

MessageArea(在共享池中RedoRedoRedoAllocationLatch

LogBuffer

PublicStrandPublicStrand

条件：某一个LogBuffer满三分之一。

MessageArea(在共享池中RedoRedoPublicRedoStrand Public

RedoRedo

RedoRedoLGWRintragroupsync”事LogFileSyncbuf_pool_t/HASHWait

File_IDFile_ID：5BufferPin3Wait:4212略Use Wait

2