数据库培训(杨宝秋).ppt

ORACLE数据库基础,杨宝秋,议程,体系结构,3,议程,ORACLE简介,1,体系结构,3,关于Oracle,是一家世界500强的公司,2010财富杂志的排名为366位有两个商标,一个是“Oracle”，另一个是“甲骨文”老板是劳伦斯埃里森Oracle是一个关系型的数据库BobMiner、EdOates、Scott名字为中报局投资的项目代码,看两张图片,左起EdOates、BruceScott、BobMiner、LarryEllison,Oracle版本历史,1979年的夏季，RSI发布了Oracle第二版1983年3月，RSI发布了Oracle第三版1984年10月，Oracle发布了第4版产品1985年，Oracle发布了5.0版1988年，ORACLE发布了第6版,引入了行级锁1992年6月，Oracle发布了第7版1997年6月，Oracle第八版发布1998年9月，Oracle公司正式发布Oracle8i2001年6月，Oracle发布了Oracle9i，引入了RAC。2003年9月，Oracle发布了Oracle10g2007年7月11日，Oracle发布了Oracle11g,TasksofanDBA,Evaluatethedatabaseserverhardware.InstalltheOraclesoftware.Planthedatabase.CreateandopenthedatabaseBackupthedatabase.Implementthedatabasedesign.Recoverfromdatabasefailure.Monitordatabaseperformance.,议程,软件的安装与建库,体系结构,3,2,安装流程,结束,环境准备,配置,打补丁,安装Oracle软件,建库,准备工作,准备一台机器准备Oracle安装介质+补丁,SystemRequirements,Hardware:512MBofphysicalrandomaccessmemory(RAM)1GBofswapspace(ortwicethesizeofRAM)400MBofdiskspaceinthetemporarydirectory(/tmporTemp)1.5GBofdiskspacefortheOraclesoftware1.5GBofdiskspaceforthepreconfigureddatabaseOperatingsystem:Seeinstallationguide,SettingEnvironmentVariables,ORACLE_BASE:ThebaseoftheOracledirectorystructureforOFAORACLE_HOME:ThedirectorycontainingtheOraclesoftwareORACLE_SID:Theinitialinstancename(ORCLbydefault)NLS_LANG:Thelanguage,territoryandclientcharactersetsettings,PreinstallationChecks,OracleUniversalInstaller,InventoryandUNIXGroupName,orainstRoot.sh,#sh/u01/app/oracle/oraInventory/orainstRoot.shCreatingtheOracleinventorypointerfile(/etc/oraInst.loc)Changinggroupnameof/u01/app/oracle/oraInventorytooinstall.,FileLocations,InstallType,PrerequisiteChecks,StarterDatabase,PasswordsandSummary,Installation,root.sh,CreatinganOracleDatabase,DatabaseConfigurationAssistant,CreatingaDatabase,DatabaseIdentification,ManagementOptions,PasswordsandStorage,FileLocationsandBackupRecovery,FileLocationVariables,ContentandInitializationParameters,DatabaseStorage,CreationOptionsandCreate,议程,体系结构,3,Oracle体系架构,Instance,SGA,RedoLogBuffer,SharedPool,DataDictionaryCache,LibraryCache,DBWR,SMON,PMON,CKPT,LGWR,Others,Userprocess,Serverprocess,PGA,Controlfiles,Datafiles,Database,DatabaseBufferCache,RedoLogfiles,JavaPool,LargePool,Parameterfile,ArchivedLogfiles,ControlFiles,ContainphysicaldatabasestructureinformationMultiplexedtoprotectagainstlossRequiredtostarttheinstance,Controlfiles,RedoLogFiles,RecordchangestothedatabaseMultiplextoprotectagainstloss,Redologbuffer,LogwriterLGWR,Group1,Group2,Group3,TablespacesandDatafiles,TablespacesconsistofoneormoredatafilesDatafilesbelongtoonlyonetablespace,Segments,Extents,andBlocks,Segmentsexistwithinatablespace.Segmentsaremadeofacollectionofextents.Extentsareacollectionofdatablocks.DatablocksaremappedtoOSblocks.,Segment,Extents,Datablocks,OSblocks,OracleMemoryStructures,JavaPool,DatabaseBufferCache,RedoLogBuffer,SharedPool,LargePool,SGA,StreamsPool,ServerProcess1,PGA,ServerProcess2,PGA,Back-groundProcess,PGA,OracleProcesses,SystemMonitorSMON,DatabaseWriterDBWn,CheckpointCKPT,LogWriterLGWR,ProcessMonitorPMON,ArchiverARCn,ServerProcess,ServerProcess,ServerProcess,ServerProcess,SystemGlobalAreaSGA,BackgroundProcesses,ConnectingtoanInstance,User,Server,Server,User,Client,User,Server,Oracledatabase,Server,Applicationserver,Browser,议程,体系结构,3,数据库接口-SQL,4,WhatIsSQL*Plus?,Command-linetoolUsedinteractivelyorinbatchmode,$sqlplus/assysdbaSQL*Plus:Release.0-ProductiononThuNov2515:13:362010Copyright(c)1982,2007,Oracle.AllRightsReserved.SQLselect*fromdual;D-X,WhatIsSQL?,SQLprovidesstatementsforavarietyoftasks,including:QueryingdataInserting,updating,anddeletingrowsinatableCreating,replacing,altering,anddroppingobjectsControllingaccesstothedatabaseanditsobjectsSQLunifiesalloftheprecedingtasksinoneconsistentlanguage.,使用SQL,ThereareseveraltoolsforinterfacingwiththedatabaseusingSQL:OracleSQL*PlusPl/sqldeveloper,Toad等图形化软件Pro*c和Oci程序设计,SQLProcessingPhases,Close,Open,Fetch,Bind,Parse,Execute,SQLProcessing:Parse,Parsephase:SearchesforthestatementinthesharedpoolCheckssyntaxCheckssemanticsandprivilegesMergesviewdefinitionsandsubqueriesDeterminesexecutionplanMinimizeparsingasmuchaspossible:Parsecallsareexpensive.AvoidreparsingParseonce,executemanytimes,LIBRARYCACHELATCHES,OracleOptimizer,TheoptimizercreatesanexecutionplanforeverySQLstatementby:EvaluatingexpressionsandconditionsUsingobjectandsystemstatisticsDecidinghowtoaccessthedataDecidinghowtojointablesDecidingwhichpathismostefficientComparingthecostforexecutionofdifferentplansDeterminingtheleast-costplan,TopDatabasePerformanceIssues,BadconnectionmanagementBadSQLNonstandardinitializationparametersI/OissuesLongfull-tablescansIn-disksortsHighamountsofrecursiveSQLSchemaerrorsandoptimizerproblems,议程,体系结构,3,5,常用维护操作,启/停数据库,StartingUpaDatabase,OPEN,MOUNT,NOMOUNT,SHUTDOWN,Allfilesopenedasdescribedbythecontrolfileforthisinstance,Controlfileopenedforthisinstance,Instancestarted,STARTUP,SHUTDOWN,ShuttingDowntheDatabase,Shutdownmode:A=ABORTI=IMMEDIATET=TRANSACTIONALN=NORMAL,ANoNoNoNo,TNoNoYesYes,INoNoNoYes,ShutdownModeAllownewconnectionsWaituntilcurrentsessionsendWaituntilcurrenttransactionsendForceacheckpointandclosefiles,NNoYesYesYes,InitializationParameterFiles,AlertLog,Thread1advancedtologsequence48897(LGWRswitch)Currentlog#1seq#48897mem#0:/dev/zhkfvg01/rredo1_11.dbfTueSep100:24:332009Thread1advancedtologsequence48898(LGWRswitch)Currentlog#5seq#48898mem#0:/dev/zhkfvg01/rredo1_51.dbfTueSep100:27:032009Thread1advancedtologsequence48899(LGWRswitch)Currentlog#4seq#48899mem#0:/dev/zhkfvg01/rredo1_41.dbfTueSep100:32:212009Thread1advancedtologsequence48900(LGWRswitch)Currentlog#2seq#48900mem#0:/dev/zhkfvg01/rredo1_21.dbfTueSep100:39:262009Thread1advancedtologsequence48901(LGWRswitch)Currentlog#3seq#48901mem#0:/dev/zhkfvg01/rredo1_31.dbfTueSep100:43:102009,Tablespacemanagement,SpaceManagementinTablespaces,Locallymanagedtablespace:Freeextentsaremanagedinthetablespace.Bitmapisusedtorecordfreeextents.Eachbitcorrespondstoablockorgroupofblocks.Bitvalueindicatesfreeorused.,CreatingTablespaces,CREATETABLESPACEuserdataDATAFILE/u01/oradata/userdata01.dbfSIZE5M;,Atablespaceiscreatedusingthecommand:CREATETABLESPACE,CREATETABLESPACEuserdataDATAFILE/u01/oradata/userdata01.dbfSIZE500MEXTENTMANAGEMENTLOCALUNIFORMSIZE128K;,CREATETABLESPACEuser_dataDATAFILE/u01/oradata/userdata01.dbfSIZE200MAUTOEXTENDONNEXT10MMAXSIZE500M;,AddingDataFilestoaTablespace,IncreasesthespaceallocatedtoatablespacebyaddingadditionaldatafilesADDDATAFILEclauseisusedtoaddadatafileExample:,ALTERTABLESPACEuser_dataADDDATAFILE/u01/oradata/userdata03.dbfSIZE200M;,DroppingTablespaces,Youcannotdropatablespaceifit:IstheSYSTEMtablespaceHasactivesegmentsINCLUDINGCONTENTSdropsthesegments.INCLUDINGCONTENTSANDDATAFILESdeletesdatafiles.CASCADECONSTRAINTSdropsallreferentialintegrityconstraints.,DROPTABLESPACEuserdataINCLUDINGCONTENTSANDDATAFILES;,表空间统计,SELECTUPPER(D.TABLESPACE_NAME)ts_name,D.TOT_GROOTTE_MBts_size,D.TOT_GROOTTE_MB-nvl(F.TOTAL_BYTES,0)used_space,TO_CHAR(ROUND(D.TOT_GROOTTE_MB-nvl(F.TOTAL_BYTES,0)/D.TOT_GROOTTE_MB*100,2),990.99)used_pct,nvl(F.TOTAL_BYTES,0)free_space,nvl(F.MAX_BYTES,0)max_blockFROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTESFROMSYS.DBA_FREE_SPACEGROUPBYTABLESPACE_NAME)F,(SELECTDD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MBFROMSYS.DBA_DATA_FILESDDGROUPBYDD.TABLESPACE_NAME)DWHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+)ORDERBY4DESC,结果：,Usermanagement,CreatingaNewUser:,Settheinitialpassword:,CREATEUSERhrIDENTIFIEDBYhr123DEFAULTTABLESPACEdataTEMPORARYTABLESPACEtempQUOTA15MONdataQUOTA10MONusersPASSWORDEXPIRE;,DroppingaUser,DROPUSERaaron;,DROPUSERaaronCASCADE;,UsetheCASCADEclausetodropallobjectsintheschemaiftheschemacontainsobjects.,Grantprivilege,Grantconnecttohr;,SomePredefinedRoles:,Undomanagement,UndoData,Undodatais:Acopyoforiginal,premodification,dataCapturedforeverytransactionthatchangesdataRetainedatleastuntilthetransactionisendedUsedtosupport:RollbackoperationsRead-consistentandflashbackqueriesRecoveryfromfailedtransactions,User,StoringUndoInformation,Undoinformationisstoredinundosegments,whichareinturnstoredinanundotablespace.Undotablespaces:AreonlyusedforundosegmentsHavespecialrecoveryconsiderationsMayonlybeassociatedwithasingleinstance,andaninstancecanonlyhaveoneactiveundotablespaceatatime,MonitoringUndo,DBA,Undousuallyrequireslittlemanagement.Areastomonitorinclude:Undotablespacefreespace“Snapshottooold”errors,AdministeringUndo,Administrationofundoshouldincludepreventing:UndotablespacespaceerrorsSizetheundotablespaceproperlyEnsurelargetransactionscommitperiodically“Snapshottooold”errorsConfigureanappropriateundoretentionintervalSizetheundotablespaceproperlyConsiderguaranteeingundoretention,DBA,UNDO_MANAGEMENT=AUTOUNDO_TABLESPACE=UNDOTBS1,ConfiguringUndoRetention,DBA,UNDO_RETENTION=0,Undoretentionspecifies(inseconds)theamountofalreadycommittedundoinformationtoretain.Defaultvalueis0(automatic).Maximumvalueis232seconds(morethan187years).Asettingof0indicatesautomaticundoretentionmode.,Ora-01555,1:40,A查到第4000记录,报ora-01555,回滚段中4000万记录extent被重用,1:35,用户Bupdate了T1表中的第4000万行的这条记录用户B接着执行了commit,1:30,A刚查到2500万记录,UPDATEhr.employeesSETsalary=salary+100WHEREemployee_id=101;1rowupdated.,1:00,select*fromT1;,用户B,Time,用户A,假定表T1有5000万条记录,全表扫需要1小时,MonitoringandResolvingLockConflicts,Locks,PreventmultiplesessionsfromchangingthesamedataatthesametimeAutomaticallyobtainedatthelowestpossiblelevelforagivenstatement,Transaction1,SQLUPDATEhr.employees2SETsalary=salary*1.13WHEREemployee_id=100;,SQLUPDATEhr.employees2SETsalary=salary+1003WHEREemployee_id=100;,Transaction2,LockingMechanism,HighlevelofdataconcurrencyRow-levellocksforinserts,updates,anddeletesNolocksrequiredforqueriesAutomaticqueuemanagementLockshelduntiltransactionends(withcommitorrollbackoperation),Transaction1,SQLUPDATEhr.employees2SETsalary=salary*1.13WHEREemployee_id=101;,SQLUPDATEhr.employees2SETsalary=salary+1003WHEREemployee_id=100;,Transaction2,EnqueueMechanism,Theenqueuemechanismkeepstrackof:SessionswaitingforlocksTherequestedlockmodeTheorderinwhichsessionsrequestedthelock,LockConflicts,Deadlocks,查看锁sql,SELECTC.SID,C.SERIAL#,A.OWNER,A.OBJECT_NAME,C.PROGRAM,B.ORACLE_USERNAME,B.OS_USER_NAME,B.LOCKED_MODE,C.MACHINE,C.STATUS,C.SERVER,C.PADDRFROMDBA_OBJECTSA,V$LOCKED_OBJECTB,V$SESSIONCWHEREA.OBJECT_ID=B.OBJECT_IDANDB.SESSION_ID=C.SID-ANDORDERBYow