Oracle数据库日常管理方案.docx

1. Oracle数据库平常管理方案OraCIe数据库的运维解决，更重要的是做好数据库的管理工作，做好数据库的检查与备份工作，然后需要熟悉相关的逻辑结构故障解决方法、物理结构故障解决方法，应对数据库损坏的情况，在数据库发生损坏而无法修复的时候，能运用数据库管理工作中所做的数据备份及时恢复系统、数据，恢复正常生产工作。1.1 .数据库管理Oracle数据库的管理重要是要做好平常的检查管理工作，并检查好数据库的备份情况,在发生紧急情况时能及时不仅切换到备用系统上进行工作，并要能在数据库数据出现异常的时候，能及时恢复数据。因此，做好平常的检查与管理工作特别重要，重要工作涉及： 数据库的性能监控检查 数据库状态巡检检查 数据库备份情况与可恢复性检查1.1.1.数据库性能监控管理通过对数据库性能监控的管理工作，可以有效的防范风险事件的发生，并能从长时间的运营数据中，总结出数据库性能的状态，为系统性能优化、隐患事件排查提供更好的依据。1.1.1.1.查找前十条性能差的SqlSE1.ECT*FROM(selectPARSlNG_USERD,EXECUTIONS,SORTS,COMMANDJYPE,DISK_READS,sqI_textFROMvSsqlareaorderBYdisk_readsDESC)whereROWNUM<10;1.1.1.2,查询有enqueue等待的事件SE1.ECTb.SID,b.serial#,b.username,machine,event,wait_time,CHR(BITAND(pl,-16777216)/16777215)CHR(BITAND(p1,16711680)/65535)"EnqueueType"FROMv$session_waita,v$sessionbWHEREa.eventNOT1.IKE'SQ1.*N%'ANDa.eventNOT1.IKE,rdbms%,ANDa.SID=b.SIDANDb.SID>8ANDa.event='enqueue'ORDERBYusername;1.1.13.如何拟定哪个表空间读写频繁selectname,phyrds,phywrts,readtim,writetimfromv$filestata,v$dbfilebwherea.file#=b.file#orderbyreadtimdesc;SE1.ECTs.sid,kglpnmod"Mode”,kglpnreq"Req”,SPID“OSProcessFROMv$session_waitw,x$kglpnp,v$sessionS,vSprocessoWHEREp.kglpnuse=s.saddrANDkglpnhdl=w.plrawandw.eventliket%librarycachepin%,ands.paddr=o.addr;1.1.1.4.查询全表扫描的表SQ1.>colnamefora30SQ1.>selectname,valuefromv$SySSUnwherenamein('tablescans(shorttables)','tablescans(longtables)');NAMEVA1.UE18602tablescans(shorttables)111tablescans(longtables)SQ1.>selectcount(target),targetfromv$SeSSion_1OngOPSwhereopname='TableScan'groupbytarget;COUNT(TARGET)TARGET84ECM,APP1.RPT1.OG159ECM_DCTM_OTHR.DMR_CONTENT_S9ECM_DCTM_OTHR.DM_SYSOBJECT_R2ECM_DCTM_OTHR.DM_SYSOBJECT_S1.1.1.5. 查出全表扫描的表Selectsql_textfromv$sqltextt,v$sql_planpWheret.hash_value=p.hash_valueAndp.opcration=,TAB1.EACCESS,Andp.option=TU1.1.,Orderbyp.hash-value,t.piece;1.1.1.6. 查找FastFullindex扫描的SqI语句可以这样:Selectsql_textfromv$sqltextt,v$Sq1.PIanpWheret.hash_value=p.hash_valueAndp.opcration='INDEX'Andp.option=TU1.1.SCAN,Orderbyp.hash-value,t.piece;1.1.1.7. 查询硬语法分析的次数SQ1.>selectname,valuefromv$SySStalwherenamelike'parsecount%'NAMEVA1.UEparsecount(total)16103parsecount(hard)343parsecount(failures)5该项显示buffercache大小是否合适。公式：!-(physicalreads-physicalreadsdirect-physicalreadsdirect(lob)/sessionlogicalreads)执行：selectl-(a.value-b.value-c.value)d.value)fromv$sysstata,v$SySStatb,v$SySStatc,v$SySStatdwherea.name=,physicalreads'andb.name=,physicalreadsdirect'andc.name=,physicalreadsdirect(Iob)'andd.name='sessionlogicalreads'1.1.1.8. 显示buffer命中率公式：1-(physicalreads/(dbblockgets+consistentgets)执行：select1-(sum(decode(name,physicalreads',value,0)(sum(decode(name,dbblockgetsvalue,0)÷sum(decode(name,'consistentgets*,value,0),'bufferhitratio"fromv$sysstat;Softparseratio：这项将显示系统是否有太多硬解析。该值将会与原始记录数据对比以保证精确。例如，软解析率仅为02则表达硬解析率太高。但是，假如总解析量(ParSeCOUnttotal)偏低，这项值可以被忽略。公式：1-(parsecount(hard)/parsecount(total)执行:selectl-(a.valueb.value)fromv$SySStaIa,v$SySStatbWherea.name='parsecount(hard)'andb.name='parsecount(total)1;In-memorysortratio：该项显示内存中完毕的排序所占比例。最抱负状态下，在O1.TP系统中，大部分排序不仅小并且可以完全在内存里完毕排序。公式：sorts(memory)/(sorts(memory)÷sorts(disk)执行：selecta.value(b.value+c.value)fromv$SySStata,v$sysstatb,v$SySStaIcwherea.name=,sorts(memory)'andb.name='sorts(memory)*andc.name=,sorts(disk),;Parsetoexecuteratio：在生产环境，最抱负状态是条SqI语句一次解析多数运营。公式：1-(parsecount/executecount)执行：selectl-(a.valueb.value)fromvSsysstata,vSsysstatbwherea.name=,parsecount(total),andb.name=,executecount,;ParseCPUtototalCPUratio：该项显示总的CPU花费在执行及解析上的比率。假如这项比率较低，说明系统执行了太多的解析。公式：1-(parsetimecpu/CPUusedbythissession)执行：select1-(a.valueb.value)fromvSsysstata,vSsysstatbwherea.name=,parsetimecpu,andb.name='CPUusedbythissession,;ParsetimeCPUtoparsetimeelapsed：通常，该项显示锁竞争比率。这项比率计算是否时间花费在解析分派给CPU进行周期运算（即生产工作）。解析时间花费不在CPU周期运算通常表达由于锁竞争导致了时间花费。公式：parsetimecpu/parsetimeelapsed执行:selecta.value/b.valuefromvSsysstata,v$sysstatbwherea.name=,parsetimecpu,andb.name=,parsetimeelapsed,;从VSSYSSTAT获取负载间档(1.OadPrOfne)数据负载间档是监控系统吞吐量和负载变化的重要部分，该部分提供如下每秒和每个事务的记录信息：IOgonSCUmUIatiVe,parsecount(total),parsecount(hard),executes,physicalreads,physicalwrites,blockchanges,andredosize.被格式化的数据可检查rates'是否过高，或用于对比其它基线数据设立为辨认systemprofile在期间如何变化。例如，计算每个事务中blockchanges可用如下公式：dbblockchanges/(usercommits+userrollbacks)执行:selecta.value(b.value+c.value)fromv$sysstata,vSsysstatb,v$SySStatcwherea.name=,dbblockchanges'andb.name=,uscrcommits,andc.name=,userrollbacks,;其它计算记录以衡量负载方式，如下：Blockschangedforeachread：这项显示出blockchanges在blockreads中的比例。它将指出是否系统.重:要用于只读访问或是重要进行诸多数据操作(如：insertsUpdatesZdeletes)公式：dbblockchanges/sessionlogicalreads执行:selecta.value/b.valuefromvSsysstata,vSsysstatbwherea.name=,dbblockchanges,andb.name=,sessionlogicalreads'Rowsforeachsort：数据排序情况公式：sorts(rows)/(sorts(memory)+sorts(disk)执行：selecta.value(b.value+c.value)fromv$sysstata,vSsysstatb,v$sysstatCwherea.name=,sorts(rows),andb.name=,sorts(memory)'andc.name='sorts(disk)*;1.1.1.9. 查看某表的约束条件selectconstraint_name,constraint_type,search_condition,r_constraint_namefromUSerConstraintswheretable_name=upper(,<fetable-name');selectc.constraint_name,c.constraint_type,cc.column_namefromuser-constraintsc,user_cons_columnsccwherec.owner=upper('&table_owner')andc.table_name=upper('&table_name')andc.owner=cc.ownerandc.constraint_name=cc.constraint_nameorderbycc.position;1.1.1.10. 查看表的信息Select*fromUSejlables;1.1.1.11. 查看表空间的名称及大小selectt.tablespace_name,round(sum(bytes(1024*1024),0)ts_sizefromdba_tablespacest,dba_data_filesdwheret.tablespace_name=d.(ablespace_namegroupbyt.tablespace_name;selectt.tablespace_name,round(sum(bytes(1024*1024),0)ts_sizefromdba-tablespacest,dba_data_filesdwheret.tablespace_name=d.tablespace_namegroupbyt.tablespace_name;CWM1.ITE20DRSYS20ODM20Perfstat99system400TAB1.ESPACE_NAMETS.SIZE1.1.1.12. 查看回滚段名称及大小selectsegment-name,tablespace_name,r.status,(initial_extent/1024)InitialExtent,(next-extent/1024)NextExtent,max_extents,v.curextCurExtentFromdba_rollback_segsr,v$rolIstatvWherer.segment_id=v.usn(+)orderbysegment_name;1.1.1.13. 移动一个表的多个分区BEGINFORxIN(SE1.ECTpartition_nameFROMuser_tab_partitionsWHEREtable_name='BIG_TAB1.E2')1.OOPEXECUTEIMMEDIATEtaltertablebigtable2movepartitiontx.partitionname;END1.OOP;END;1.1.1.14. 查看1.OCKSE1.ECT/*+ORDEREDUSE_HASH(H,R)*/H.SIDHO1.D-SlD,R.SIDWArr_SID,decode(H.type,"MR",“MediaRecovery,WJRedoThread,"UN","UserName”，1X","Transaction”,叮M",“DM1.”，U1.","P1./SQ1.User1.ock”,“DX","DistributedXaction,j“CF","ControlFile”,"IS"，"InstanceState”,'FS","FileSet”,"IR","InstanceRecovery”,ST”,“DiskSpaceTransaction'IS","TemPSegment,“IV","1.ibraryCacheInvalidation,“1.S”,"1.OgStartorSwitch”，RW","RowWait”,“SQ"，"SequenceNumber”,叮E",“ExtendTable,'TTJTempTable,H.lype)type,decode(H.lmode,0,"None",Nuir,2,Row-S(SS)'',3,Row-X(SX),4,"Share”，5,SRow-X(SSX),6,"Exclusive”，to_char(H.1mode)hold,deco<le(r.request,0,"None",l,Nuir,2,Row-S(SS)n,3,"Row-X(SX)'',4,"Share”，5,SRow-X(SSX)”,6,"Exclusive”，to_char(R.request)request,R.ID1,R.ID2,R.CTIMEFROMV1.OCKH,V1.OCKRWHEREH.B1.OCK=1ANDR.B1.OCK=0andH.TYPE<>,MR,)ANDR.TYPEo"MR”ANDH.ID1=R.IDIANDH.ID2=R.ID21.1.1.15. 找ORAC1.E字符集select*fromsys.props$wherename=,N1.S-CHARACTERSET,;1.1.1.16. 查看ORAC1.E运营的OS平台SQ1.>runbegindbms_output.put_line(dbms_utility.port_string);end;1.1.1.17. 查看空间具体使用情况CREATEORREP1.ACEPROCEDUREshow_space(p_segnameINVARCHAR2,p_ownerINVARCHAR2DEFAU1.TUSER,p_typeINVARCHAR2DEFAU1.TTAB1.E；p_partitionINVARCHAR2DEFAU1.TNU1.1.)ASl_total_blocksNUMBER;l_total_bytesNUMBER;l_unused_blocksNUMBER;l_unused_bytesNUMBER;IJastusedextfileidNUMBER;1.lastusedextblockidNUMBER;l_last_used_blockNUMBER;PROCEDUREp(pjabelINVARCHAR2,p_numINNUMBER)ISBEGINDBMS_OUTPUT.put_line(RPAD(pjabel,40,7)p_num);END;BEGINDBMS_SPACE.unused_space(segment_owner=>p_owner,segmenl_name=>p_segname,segment_type=>P-type,partition_name=>p-partition,total_blocks=>l_total_blocks,total_bytes=>l_total_bytes,unused_blocks=>l_unused_blocks,unused-bytes=>l_unused_bytes,last_used_extent_file_id=>IJastusedextfileid,last_used_extent_block_id=>Ijastusedextblockilast_used_block=>l_last_uscd_block)；p(TotalBlocks',l_total_blocks);p(TotalBytes,l_total_bytes);p('UnusedBlocks',l_unused_blocks);P(,UnusedBytes',l_unused_bytes);p('1.astUsedExtFiIeId',1.lastusedextfileid);p('1.astUsedExtBlockld,Uastusedextblockid);p('1.astUsedBlock',l_last_used_block);END;1.1.1.18. 显示缓冲区的相关SQ1.SE1.ECTlch,file#,dbablk,CASEWHENobj=THEN'rbscompatsegment'E1.SE(SE1.ECTMAX(,(,object_type|'),owner,.,object_name)DECODE(COUNT(*),1,",'maybe!')FROMdba.objectsWHEREdata_object_id=x.obj)ENDwhatFROM(SE1.ECTtch,file#,dbablk,objFROMx$bhWHEREstate<>OORDERBYtchDESC)XWHEREROWNUM<=5;1.1.1.19. 获取生成的根据文献名selectc.value7'd.instance_name1.oraJa.spid'.trc'tracefromv$processa,v$SeSSiOnb,VSparameterc,vSi11stancedwherea.addr=b.paddrandb.audsid=userenv('sessionid')andc.name='user_dump_dest'在v$session_longops视图中，sofar字段表达已经扫描的块数，totalwork表达总得需要扫描的块数，所以我们即可以对正在运营的长查询进行监控，比如在索引创建时，查看索引创建的进度，也可以查看系统中以往的长查询。colopnameIbrmata32coltarget_descformata32colperworkformatal2setlines131selectsid,OPNAME,TARGET_DESC,sofar,TOTA1.WORK,trunc(sofartotalwork*100,2)ll'%'asperworkfromv$session_longopswheresofar!=totalwork;setlines121setpages999colopnameformata29coltargetformata29coltarget_descformatal2colperworkformatal2colremainformat99colstart_timeformata21colsofarformat99999999coltotalworkformat99999999colsql_textformatallcolbufgetsformat99999999selectopname,target,to_char(start_time,'yy-mm-dd:hh24:mi:ssr)start_time,elapsed_secondselapsed,executionsexecs,buffer_gets/decode(executions,0,1,executions)bufgets,module,sql_textfromv$SeSSiOnOngOPSsi,v$SqIareasawheresl.sql_hash_value=sa.hash_valueandupper(substr(module,1,4)<>,RMAN'andsubstr(opname,1,4)<>,RMAN,andmodule<>'SQ1.*Plus'andsl.start-time>trunc(sysdate)orderbyStarUime;1.1.1.20. IO事件监控1 .监控事例的等待selectevent,sum(decode(wait_Time,0,0,1)"Prev",sum(decode(wait_Time,0,1,0),Curr'count(*)"Tot"fromv$session_Waitgroupbyeventorderby4;2 .回滚段的争用情况selectname,waits,gets,waits/gets"Ratio11fromvSrollstata,v$rolInamebwherea.usn=b.usn;3 .监控表空间的I/O比例selectdf.tablespace_namename,df.file_name"file",f.phyrdspyr,Ephyblkrdpbr,f.phywrtspyw,EphyblkwrtpbwfromvSfilestatf,dba_data_filesdfwheref.file#=df.file_idorderbydf.tablespace_name;4 .监控文献系统的I/O比例selectsubstr(a.file#,1,2)substr(a.name,1,30)"Name",a.statusya.bytes,b.phyrds,b.phywrtsfromv$datafilea,v$filestatbwherea.file#=b.file#;1.1.1.21. 在某个用户下找所有的索引selectuser_indexes.table_name,user_indexes.index_name,uniqueness,column-namefromuser_ind_columns,user_indexeswhereuser_ind_columns.index_name=user_indexes.index_nameanduser_ind_columns.table_name=user_indexes.table_nameorderbyuser_indexes.table_type,user_indexes.table_name,user_indexes.index_name,column_position;1.1.1.22. 监控SGA的命中率selecta.value+b.valuenIogica1.reads",c.value"phys_reads",round(100*(a.value+b.value)-c.value)/(a.value+b.value)"BUFFERHITRATIO"fromv$SySStata,v$sysstatb,vSsysstatcwherea.statistic#=38andb.statistic#=39andc.statistic#=40;1.1.1.23. 监控SGA中字典缓冲区的命中率selectparameter,gets,Getmisses,getmisses(gets+getmisses)*100',missratio",(1-(sum(getmisses)/(sum(gets)+sum(getmisses)*100"Hitratio"fromv$rowcachewheregets+getmisses<>0groupbyparameter,gets,getmisses;监控SGA中共享缓存区的命中率，应当小于1%selectsum(pins)"TotalPins",sum(reloads)"TotalReloads",sum(reloads)sum(pins)*100Iibcachefromv$librarycache;selectsum(pinhits-reloads)sum(pins)"hitradio",sum(reloads)sum(pins)"reloadpercent,rfromv$librarycache;显示所有数据库对象的类别和大小selectcount(name)num_instances,type,sum(source_size)source_size,sum(parsed_size)parsed_size,sum(code_size)code_size,sum(error_size)eor-size,sum(source_size)+sum(parsed-size)+sum(code-size)+sum(error-size)size_requiredfromdba_object_sizegroupbytypeorderby2;监控SGA中重做日记缓存区的命中率，应当小于1%SE1.ECTname,gets,misses,immediate_gets,immediate-misses,Decode(gets,0,0,missesgets*100)ratio1,Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100)ratio2FROMv$latchWHEREnameIN('redoallocation','redocopy');监控内存和硬盘的排序比率，最佳使它小于.10,增长sort_area_size:SE1.ECTname,valueFROMv$sysstatWHEREnameIN('sorts(memory)','sorts(disk),);监控当前数据库谁在运营什么SQ1.语句SE1.ECTosuser,username,sql_textfromvSsessiona,v$sqltextbwherea.sq1.address=b.addressorderbyaddress,piece;监控字典缓冲区select(sum(pins-reloads)/sum(pins)"libcache"fromv$librarycache;select(sum(gets-getmisses-usage-fixed)/sum(gets)"rowcache"fromv$rowcache;selectsum(pins)"executions",sum(reloads)"cachemisseswhileexecuting"fromvSlibrarycache;后者除以前者,此比率小于1%,接近0%为好。selectsum(gets)"dictionarygets",sum(gelmisses)"dictionarycachegetmisses11fromv$rowcache;1.1.1.24. 监控MTSselectbusy(busy+idle)"sharedserversbusy"fromv$dispatcher;此值大于05时，参数需加大。selectsum(wait)sum(totalq)"dispatcherwaits"fromv$queuewherelype='dispatcher'selectcount(*)fromv$dispatcher;selectservers_highwaterfromv$mts;servers_highwater接近mts_max_servers时，参数需加大1.1.1.25. 碎片限度selecttablespace_name,count(tablespace_name)fromdba_free_spacegroupbytablespace_namehavingcount(tablespace_name)>10;altertablespacenamecoalesce;altertablenamedeallocateunused;createorreplaceviewts_biocks_vasselecttablespace_name,block_id,bytes,blocks/freespace'segmeni_namefromdba_free_spaceunionallselecttablespace_name,block_id,bytes,blocks,segment_namefromdba_extents;select*fromts_blocks_v;selecttablespace_name,sum(bytes),max(bytes),count(block_id)fromdba_free_spacegroupbytablespace_name;1.1.1.26. 查看碎片限度高的表SE1.ECTsegment_nametable_name,COUNT(*)extentsFROMdba_segmentsWHEREownerNOTIN('SYS','SYSTEM')GROUPBYsegment_nameHAVINGCOUNT(*)=(SE1.ECTMAX(COUNT(*)FROMdba_segmentsGROUPBYsegment_name);1.1.1.27. 表、索引的存储情况检查selectsegment-name,sum(bytes),count(*)ext_quanfromdba_extentswheretablespace_name='&tablespace_name'andSegmenUyPe=TAB1.Egroupbytablespace_name,segment_name;selectsegment-name,count(*)fromdba_extentswhereSegmenUyPe='INDEX'andowner='&owner'groupbysegment_name;1.1.1.28. 找使用CPU多的用户sessioncpuusedbythissession：查找各回话连接的CPU使用率selecta.sid,spid,status,substr(a.program,1,40)prog,a.terminal,osuser,value60100valuefromv$sessiona,v$ProCeSSb,vSsesstatcwherec.statistic#=12andc.sid=a.sidanda.paddr=b.addrorderbyvaluedesc;1.1.1.29. 寻找CPU使用过量的sessionSE1.ECTsql_textFROMvSsqltextaWHERE(a.hash_value,a.address)IN(SE1.ECTdecode(sql_hash_value,0,prev_hash_value,sql_hash_value),decode(sql_hash_value,0,prev_sql_addr,sql_address)FROMvSsessionbWHEREb.paddr=(SE1.ECTaddrFROMVSprocesscWHEREc.spid='<fepid,)ORDERBYpieceASC;1.1.2.数据库巡检管理1.1.2.1. 平常检测1.1.2.1.1. 检测系统卷状态df-k可以看到系统各个卷的使用情