YonyouSoftwareCorporationORACLE数据库基础知识用友软件股份有限公司冯建民2014年5月20日体系结构1初始化参数文件2控制文件3表空间和数据文件5REDOLOG文件4YonyouSoftwareCorporationPasswordfile1、体系结构InstanceSGARedoLogBufferSharedPoolDataDictionaryCacheLibraryCacheDBWRSMONPMONCKPTLGWROthersUserprocessServerprocessPGAControlfilesDatafilesDatabaseDatabaseBufferCacheRedoLogfilesJavaPoolLargePoolParameterfileArchivedLogfilesYonyouSoftwareCorporationOracle实例一个Oracle实例:意味着访问和存取一个Oracledatabase可以打开一个而且只有一个数据库由内存和后台进程组成BackgroundprocessstructuresMemorystructuresInstanceSGARedoLogBufferSharedPoolDataDictionaryCacheLibraryCacheDBWRSMONPMONCKPTLGWROthersDatabaseBufferCacheJavaPoolLargePoolOracle数据库一个Oracle数据库:是一个被看作是一个完整单元的数据集合由三种文件类型组成PasswordfileParameterfileArchivedLogfilesControlfilesDatafilesRedoLogfilesOracleDatabaseYonyouSoftwareCorporation物理结构物理结构包含三种文件类型:控制文件数据文件重做日志文件ControlfilesDatafiles(includesDataDictionary)HeaderOnlineRedoLogfilesYonyouSoftwareCorporation内存结构Oracle的内存结构由两个内存区域构成如下所示:系统全局区SystemGlobalArea(SGA):在事例启动时分配,而且是数据库事例最基本的组成部件程序全局区ProgramGlobalArea(PGA):在后台进程启动时分配YonyouSoftwareCorporation系统全局区SGASGA由几个内存结构组成:SharedPoolDatabaseBufferCacheRedoLogBufferOtherstructures(forexample,lockandlatchmanagement,statisticaldata)还有两个额外的内存结构也可以配置在SGA中:LargePoolJavaPoolYonyouSoftwareCorporationProgramGlobalArea为每一个用户连接到数据库预先分配的内存在进程创建时分配在进程终止时释放进程独占方式使用UserprocessPGAServerprocessYonyouSoftwareCorporation后台进程MaintainsandenforcesrelationshipsbetweenphysicalandmemorystructuresMandatorybackgroundprocesses:DBWnPMONCKPTLGWRSMONOptionalbackgroundprocesses:ARCnLMDnRECOCJQ0LMONSnnnDnnnPnnnLCKnQMNnYonyouSoftwareCorporation建立连接和创建会话连接到一个Oracle实例:建立一个用户连接创建一个会话创建会话Databaseuser用户进程Serverprocess建立连接OracleServerYonyouSoftwareCorporationYonyouSoftwareCorporationspfiledb01.ora2、初始化参数文件CONNECT/ASSYSDBASTARTUPOracleInstanceSGARedoLogBufferSharedPoolDataDictionaryCacheLibraryCacheDBW0SMONPMONCKPTLGWROthersDatabaseBufferCacheJavaPoolLargePoolYonyouSoftwareCorporationInitializationParameterFilesEntriesarespecifictotheinstancebeingstartedTwotypesofparameters:公开:Havinganentryinthefile隐含:Noentrywithinthefile,butassumingtheOracledefaultvaluesMultipleinitializationparameterfilescanexistChangestoentriesinthefiletakeeffectbasedonthetypeofinitializationparameterfileusedStaticparameterfile,PFILEPersistentparameterfile,SPFILEYonyouSoftwareCorporationCreatinganSPFILECreatedfromaPFILEfilewhereSPFILE-NAME:SPFILEtobecreatedPFILE-NAME:PFILEcreatingtheSPFILECanbeexecutedbeforeorafterinstancestartupCREATESPFILE=‘$ORACLE_HOME/dbs/spfileDBA01.ora’FROMPFILE=‘$ORACLE_HOME/dbs/initDBA01.ora’;YonyouSoftwareCorporationSTARTUPCommandBehaviorOrderofPrecedencespfileSID.oraDefaultSPFILEinitSID.oraDefaultPFILESpecifiedPFILEcanoverrideprecedencePFILEcanindicatetouseSPFILESTARTUPPFILE=$ORACLE_HOME/dbs/initDBA1.oraSPFILE=/database/startup/spfileDBA1.oraYonyouSoftwareCorporation3、控制文件一个简单的二进制文件定义数据库的当前运行的物理状态维护数据库的完整性条件:AtMOUNTstateduringdatabasestartupTooperatethedatabaseLinkedtoasingledatabase如果丢失，需要恢复SizedinitiallybyCREATEDATABASEControlfilesDatabaseYonyouSoftwareCorporationMultiplexingtheControlFileCONTROL_FILES=$HOME/ORADATA/u01/ctrl01.ctl,$HOME/ORADATA/u02/ctrl02.ctlDisk1(u01)Disk2(u02)ctrl02.ctlctrl01.ctlYonyouSoftwareCorporationMultiplexingtheControlFileWhenUsingSPFILE1.AltertheSPFILE:2.Shutdownthedatabase:3.Createadditionalcontrolfiles:4.Startthedatabase:ALTERSYSTEMSETcontrol_files='$HOME/ORADATA/u01/ctrl01.ctl','$HOME/ORADATA/u02/ctrl02.ctl'SCOPE=SPFILE;cp$HOME/ORADATA/u01/ctrl01.ctl$HOME/ORADATA/u02/ctrl02.ctlstartupshutdownimmediateYonyouSoftwareCorporationMultiplexingtheControlFileWhenUsingPFILE1.Shutdownthedatabase:2.Createadditionalcontrolfiles:3.AddcontrolfilenamestoPFILE:4.Startthedatabase:shutdownimmediateCONTROL_FILES=(/DISK1/control01.ctl,/DISK3/control02.ctl)cp$HOME/ORADATA/u01/ctrl01.ctl$HOME/ORADATA/u02/ctrl02.ctlstartupYonyouSoftwareCorporation获得控制文件的信息可以通过查询以下视图来返回控制文件的状态和路径信息.V$CONTROLFILE:ListsthenameandstatusofallcontrolfilesassociatedwiththeinstanceV$PARAMETER:ListsstatusandlocationofallparametersV$CONTROLFILE_RECORD_SECTION:ProvidesinformationaboutthecontrolfilerecordsectionsSHOWPARAMETERCONTROL_FILES:Liststhename,status,andlocationofthecontrolfilesYonyouSoftwareCorporation4、RedoLogFilesRedolog文件特点:记录所有数据的变化提供一个恢复机制能够按Group来进行组织至少两组RedoLogfilesYonyouSoftwareCorporationStructureofRedoLogFilesGroup2Group3Group1MemberDisk1Disk2MemberMemberMemberMemberMemberYonyouSoftwareCorporationAddingOnlineRedoLogFileGroupsGroup1Group2Group3ALTERDATABASEADDLOGFILEGROUP3('$HOME/ORADATA/u01/log3a.rdo','$HOME/ORADATA/u02/log3b.rdo')SIZE1M;log3b.rdolog3a.rdolog1a.rdolog1b.rdolog2a.rdolog2b.rdoYonyouSoftwareCorporationAddingOnlineRedoLogFileMembersALTERDATABASEADDLOGFILEMEMBER'$HOME/ORADATA/u04/log1c.rdo'TOGROUP1,'$HOME/ORADATA/u04/log2c.rdo'TOGROUP2,'$HOME/ORADATA/u04/log3c.rdo'TOGROUP3;Group1Group2Group3log1a.rdolog1b.rdolog2a.rdolog2b.rdolog1c.rdolog2c.rdolog3a.rdolog3b.rdolog3c.rdoYonyouSoftwareCorporation5、表空间和数据文件Oracle在表空间里存储逻辑信息数据，在数据文件里存储物理信息数据表空间:只能同时属于一个数据库可以由一个或多个数据文件组成可以被更进一步的划分为细小的逻辑存储单元数据文件:只能同时属于一个表空间和同一个数据库它是用户对象数据的容器DatabaseTablespaceDatafilesYonyouSoftwareCorporation表空间的类型SYSTEM表空间CreatedwiththedatabaseContainsthedatadictionaryContainstheSYSTEMundosegment非SYSTEM表空间SeparatesegmentsEasesspaceadministrationControlsamountofspaceallocatedtoauserYonyouSoftwareCorporationCreatingTablespacesAtablespaceiscreatedusingthecommand:CREATETABLESPACECREATETABLESPACEuserdataDATAFILE'/u01/oradata/userdata01.dbf'SIZE100MAUTOEXTENDONNEXT5MMAXSIZE200M;YonyouSoftwareCorporation增加数据文件到表空间IncreasesthespaceallocatedtoatablespacebyaddingadditionaldatafilesADDDATAFILEclauseisusedtoaddadatafileExample:ALTERTABLESPACEuser_dataADDDATAFILE'/u01/oradata/userdata03.dbf'SIZE200M;YonyouSoftwareCorporation在数据库中移动数据文件的方法ALTERTABLESPACETablespacemustbeofflineTargetdatafilesmustexistStepstorenameadatafile:Takethetablespaceoffline.UseanOScommandtomoveorcopythefiles.ExecutetheALTERTABLESPACERENAMEDATAFILEcommand.Bringthetablespaceonline.UseanOScommandtodeletethefileifnecessary.ALTERTABLESPACEuserdataRENAMEDATAFILE'/u01/oradata/userdata01.dbf'TO'/u02/oradata/userdata01.dbf';YonyouSoftwareCorporationStartingUpaDatabaseOPENOPENMOUNTNOMOUNTSHUTDOWNAllfilesopenedasdescribedbythecontrolfileforthisinstanceControlfileopenedforthisinstanceInstancestartedSTARTUPSHUTDOWNYonyouSoftwareCorporation
