Oracle数据库维护手册

1、 第 PAGE 22 页 数据库维护护手册目 录TOC o 1-3 h z u HYPERLINK l _Toc127763156 1前言言 PAGEREF _Toc127763156 h 4 HYPERLINK l _Toc127763157 2ORRACLEE维护方法法 PAGEREF _Toc127763157 h 4 HYPERLINK l _Toc127763158 2.1数数据库启动动 PAGEREF _Toc127763158 h 4 HYPERLINK l _Toc127763159 2.2数数据库停止止 PAGEREF _Toc127763159 h 5 HYPERLINK

2、l _Toc127763160 2.3监监听器的启启停 PAGEREF _Toc127763160 h 5 HYPERLINK l _Toc127763161 2.4用用户管理 PAGEREF _Toc127763161 h 6 HYPERLINK l _Toc127763162 2.5数数据库参数数文件 PAGEREF _Toc127763162 h 6 HYPERLINK l _Toc127763163 2.6数数据库概况况的查询 PAGEREF _Toc127763163 h 7 HYPERLINK l _Toc127763164 2.7常常用对象的的创建和使使用 PAGEREF _To

3、c127763164 h 8 HYPERLINK l _Toc12277633165 2.8失效数据据库对象的的检测和编编译 PAGEREF _Toc127763165 h 9 HYPERLINK l _Toc127763166 2.9数数据备份的的技巧 PAGEREF _Toc127763166 h 10 HYPERLINK l _Toc127763167 2.10数据库命命中率的监监测 PAGEREF _Toc127763167 h 11 HYPERLINK l _Toc127763168 2.11最消耗资资源进程的的检测 PAGEREF _Toc127763168 h 11 HYPERL

4、INK l _Toc127763169 2.12锁的监测测及处理 PAGEREF _Toc127763169 h 12 HYPERLINK l _Toc127763170 2.13SQL语句句执行技巧巧 PAGEREF _Toc127763170 h 13 HYPERLINK l _Toc127763171 2.14表空间的的管理 PAGEREF _Toc127763171 h 14 HYPERLINK l _Toc127763172 2.15数据文件件I/O的统统计优化 PAGEREF _Toc127763172 h 15 HYPERLINK l _Toc127763173 2.16错误号的

5、的跟踪出理理 PAGEREF _Toc127763173 h 15 HYPERLINK l _Toc127763174 3日常常问题处理理流程 PAGEREF _Tocc12777631774 hh 16 HYPERLINK l _Toc127763175 3.1OORACLLE数据库库日常检查查 PAGEREF _Toc127763175 h 16 HYPERLINK l _Toc127763176 3.2会会话连接日日志的清理理 PAGEREF _Toc127763176 h 16 HYPERLINK l _Toc127763177 3.3数数据库haang住时时的停止和和方法 PAGER

6、EF _Toc127763177 h 16 HYPERLINK l _Toc127763178 3.4扩扩表空间方方法 PAGEREF _Toc127763178 h 17 HYPERLINK l _Toc127763179 3.5死死锁会话的的杀死 PAGEREF _Toc127763179 h 17 HYPERLINK l _Toc127763180 3.6未未决两阶段段事务的回回滚 PAGEREF _Toc127763180 h 18 HYPERLINK l _Toc127763181 4相关关通用工具具介绍 PAGEREF _Toc127763181 h 18 HYPERLINK l

7、_Toc127763182 4.1EEM工具 PAGEREF _Toc127763182 h 18前言本文主要是是简单介绍绍和总结日日常Oraacle数数据在运行行维护方面面的一些方方法和技巧巧。通过该该手册以便便于相关技技术人员的的日常运维维工作。ORACLLE DBB维护方法法数据库启动动1、以orraclee用户用户户登录，sqlpplus /as sysddbasqlpllus starrtup2、以orraclee用户或oora8116用户登登录sqlpplus /as sysddbasqlpplus staartupp nommunt sqlpplus altter ddatabb

8、ase mounnt；sqlpplus altter ddatabbase openn；3、第一种种启动方式式是最常用用的手工启启动方式，第第二种启动动方式是数数据库有故故障时常用用的逐步启启动方式，可可以观察故故障点。因因为staartupp nommountt 语句只只读取sppfilee（serrver paraameteer fiile 数数据库参数数文件），启启动insstancce，启动动SGA和和后台进程程；altter ddatabbase mounnt语句打打开控制文文件，确认认数据文件件和联机日日志文件的的位置，但但此时不对对数据文件件和日志文文件进行校校验检查；altee

9、r daatabaase oopen打打开包括RRedo log文文件在内的的所有数据据库文件，这这种方式下下可访问数数据库中的的数据。SStarttup完成成功能是上上述三条语语句的之和和。数据库停止止1、sqqlpluus /as sysddbasqlpplus shutddown normmal正常方式关关闭数据库库。2、shuutdowwn immmediiate立即方式关关闭数据库库,在 SSQLPLLUS中执执行shuutdowwn immmediiate，数数据库并不不立即关闭闭，而是在在所有事务务执行完毕毕并提交工工作后才关关闭，所以以可能会等等待，因此此在关闭数数据前要停停掉

10、连接到到数据库的的所有应用用程序。建建议日常维维护工作中中的关闭数数据库，采采用此方式式。3、shhutdoown aabortt直接关闭数数据库，正正在访问数数据库的会会话会被突突然终止。如果数据据库中有大大量操作正正在执行，这这时执行sshutddown aborrt后，因因日志回滚滚、前滚（RRoll Backk/Rolll Forrwardd），下次次重新启动动数据库需需要教长时时间。当用用shuttdownn immmediaate不能能关闭数据据库时，sshutddown aborrt可以立立即完成数数据库的关关闭操作。监听器的启启停1、监听器器的启动，以以oraccle用户户用户

11、登录录lsnrrctl starrt $ORACCLE_SSID2、监听器器的停止，以以oraccle用户户用户登录录lsnrrctl stopp $OORACLLE_SIID3、监听器器状态的查查看，以ooraclle用户用用户登录lsnrrctl stattus $OORACLLE_SIID4、检测服服务名是否否有效，在在操作系统统下运行tnsspingg SERRVICEE_NAMME NNUMBEERSSERVVICE_NAMEE为你建立立的服务名名，NUMMBERSS为你要试试PINGG数据库服服务的次数数用户管理1、用SSQL语句句查看系统统中已有用用户情况 sselecct *

12、fromm dbaa_useers;2、增加加新的用户户，并授予予连接和资资源权限，只只有授予连连接权限才才可登录，授授予资源权权限才可建建表和修改改creeate userr USEERNAMME prrofille deefaullt idenntifiied bby PAASSWOORDdefauult ttableespacce DEEFALUUT_TAABLESSPACEE_NAMME tempooraryy tabblesppace TEMPP_TABBLESPPACE_NAMEE ;grantt connnectt to USERRNAMEE;grantt ressourcce

13、too USEERNAMME;3、修改改用户口令令altter uuser USERRNAMEE ideentiffied by NNEWPAASSWOORD;4、删除除用户droop usser UUSERNNAME;5、限制制某个已有有用户会话话的连接数数(通过创建建proffile的的方式)creatte prrofille PRROFILLENAMME liimit sesssionss_perr_useer CONNEECT_NNUMBEERS;altter uuser USERRNAMEE proofilee PROOFILEENAMEE;数据库参数数文件1、参数数文件位置置$OR

14、AACLE_HOMEE/dbss/spffileIINSTAANCE_NAMEE.oraa,参数主主要分为动动态参数和和非动态参参数，动态态参数修改改够直接生生效，非动动态参数需需要重新启启动数据库库才能生效效。 2、 修改参参数的方法法 alterr sysstem set PARAAMETEER_NAAME = VAllUE sscopee=botth(syystemm/spffile)3、几个个重要的参参数A、dbb_bloock_ssize 数据库块块大小，数数据库创建建时决定，创创建后不能能修改。B、dbb_bloock_bbuffeers数数据高速缓缓冲区大小小为此值与与db_bb

15、lockk_bufffes的的乘积，该该区越大越越好。 C、shhare_pooll_sizze 程序序高速缓冲冲区和数据据字典缓冲冲区的大小小，主要用用于存储执执行过的ssql语句句，减少重重复分析，提提高运行速速度。该区区也越大越越好，但建建议数据高高速缓冲区区加上程序序高速缓冲冲区和数据据字典缓冲冲区的大小小（即SGGA）一般不超超过系统物物理内存的的50%。D、soort_aarea_sizee每个会会话用于排排序操作的的内存大小小，建议为为默认值的的两倍到M之间。E、prrocesss能同同时访问数数据库的最最大进程数数，根据连连接数的多多少，来设设定，一般般为3000以上。F、db

16、b_io_slavves 后后台写进程程数 对II/O比较较繁忙的数数据库，可可以将其设设为多于11个。（以上参数数以9i为参考考，在100g里个别别参数名有有所变化）数据库概况况的查询1、表空间间的情况seleect ttableespacce_naame,sstautts frrom ddba_ttableespacces;2、数据文文件的情况况SELEECT FFILE_NAMEE,FILLE_IDD,TABBLESPPACE_NAMEE FROOM DBBA_DAATA_FFILESS;3、段的情情况SELECCT SEGGMENTT_NAMME,TTABLEESPACCE_NAAME

17、,SSEGMEENT_IID,FIILE_IID,STTATUSS FROOM DBBA_ROOLLBAACK_SSEGS;4、表、存存储过程的的拥有者名名字和创建建日期selecct owwner,objeect_nname,objeect_ttype,creaated fromm alll_objjectsswherre oobjecct_tyype=TABLLEOR OOBJECCT_TYYPE=PROCCEDURRE5、其他他常用的视视图和表v$llock 锁的情况况v$ssessiion 会话的情情况v$pproceess 进进程的情况况v$pparammeterr 数据库库参数v$

18、rrollnname 回滚段段的情况v$ssqlarrea 共享池中中的SQLL语句v$wwaitsstat 等待的会会话dbaa_jobbs 数数据里定义义的作业dbaa_jobbs_ruunninng 数据据库里正在在运行的作作业dbaa_vieews 数数据库中所所有视图的的文本dbaa_inddexs 数据库中中所有索引引的描述dbaa_freee_sppace 数据库中中所有表空空间自由分分区dbaa_db_linkks 数据据库中所有有数据库链链接常用对象的的创建和使使用1、表的的创建(在在表空间zzy上创建建表dhhhm为例)creeate tablle dhhhm (hm vv

19、archhar2(11) ,yhmc varcchar22(60) taablesspacee zy;2、索引引的创建(将DHHHM表上hhm字段建建立索引hhmsy存存储在表空空间zy上上为例)creeate indeex hmmsy oon dhhhm(hhm) ttableespacce zyy;3、已有有存储过程程、函数、包源代码码的查找sellect namee,texxt frrom uuser_sourrce wwheree namme=NAAME;数据库链接接的创建和和使用creatte puublicc dattabasse liinkDDBLINNKNAMME.woorld

20、 connnect to UUSERNNAME iidenttdiedd by PASWWORD usinng SEERVICCENAMME;selecct * fromm TABBLE_NNAMEDBLIINKNAAME;5、同义词词的创建和和使用(以以dyh用用户可以查查询并修改改yyxtt用户的ddhhm表表为例)cratee synoonyn dyhh.dhhmm forr yyyxt.dhhmm;grantt sellect on yyyxt.ddhhm to ddyh;grantt upddate on yyyxt.dhhmm to dyh;失效数据库库对象的检检测和编译译1、失效

21、效存储过程程的查找和和编译seleect alteer prroceddure|obbjectt_namme| commpilee; ffrom userr_objjectss wheere sstatuus = INVVALIDD annd obbjectt_typpe=PROCCEDURRE 然后复制粘粘贴并执行行第一条语语句的输出出结果2、失效效的包的查查找和编译译sellect altter ppackaage|objject_namee| comppile; frrom uuser_objeects wherre sttatuss = INVAALID andd objject_ty

22、pee= PACCKAGEE然后复制粘粘贴并执行行第一条语语句的输出出结果数据备份的的技巧一、EXPP/IMPP1、利用管管道将数据据库备份压压缩到磁带带中去,以以备份dhhhm,yyhzl 表为例（磁带机名名IBM为为/devv/rmtt0，HPP为/deev/rttm/m00）/usr/sbinn/mknnod ppipe_of pp/usr/sbinn/mknnod ppipe_if ppdd off=/deev/rmmt0 iif=piipe_iif bss=10224 &comprress pipee_if &exp UUSERNNAME/PASSSWORDD tabbles=(dh

23、hhm,yhhzl) filee=pippe_offrm piipe_oofrm piipe_iif2、利用用管道将备备份磁带解解压后倒入入到数据库库中去/usr/sbinn/mknnod ppipe_of pp/usr/sbinn/mknnod ppipe_if ppdd iff=/deev/rmmt0 oof=piipe_oof bss=10224 &uncommpresss piipe_iif &imp UUSERNNAME/PASSSWORDD tabbles=(dhhhm,yhhzl) filee=pippe_iffrm piipe_oofrm piipe_iif3、数据库库备份的分

24、分割,解决决有时文件件系统限制制文件大小小不能超过过2G的问题题（此处只只分割为三三个文件，当当然可以根根据实际情情况继续增增加）exp UUSERNNAME/PASSSWORDD filee=(FIILE1.dmp,FILEE2.dmmp,FIILE3.dmp) logg=dummp.loog fiilesiize=11g tabbles=(dhhhm,yhhzl)为了提高备备份速度,可将DIIRECTT参数设为为Y（但当当该参数设设置为Y时时，备份时时不做一些些有效性检检查，应慎慎用）,并并将bufffer开开大，如exp UUSERNNAME/PASSSWORDD diirectt=y

25、bbuffeer=811920000 tablles=(dhhmm,yhzzl) ffile=1.dmmp只备份建表表和建索引引语句exp UUSERNNAME/PASSSWORDD rowws=n tablles=(dhhmm,yhzzl) ffile=1.dmmp有时为了提提高倒入速速度，应该该将数据和和索引的倒倒入倒出分分开，而不不是一起进进行二、RMAAN备份恢恢复1、非归档档模式下数数据库必须须在mouunt状态态下进行备备份。$rmann tarrget/ 进入rmaan全库备份RMANshuttdownn immmediaate；关关闭数据库库RMANstarrtup mounn

26、t；RMANbackkup ddatabbase formmat /路径/DAT_%d_%T_%ss；RMANalteer daatabaase oopen；恢复数据据库为OPPEN状态态RMANlistt bacckupsset oof daatabaase; 备份记录录RMANlistt bacckup;恢复RMANshuttdownn immmediaate；RMANstarrtup nomouunt；RMANresttore conttrolffile fromm 控制文件件备份;RMANalteer daatabaase mmountt;RMANresttore dataabasee

27、;RMANrecoover dataabasee;RMANalteer daatabaase oopen reseetloggs; 打打开数据库库并重做日日志归档模式切换归档模模式SQL alteer syystemm sett logg_arcchivee_desst_1=loccatioon=归档档路径 scoppe=booth; 首先需需要指定归归档路径SQL staartupp mouunt; SQL altter ddatabbase archhivellog; 非归档模模式noaarchiiveloogSQL altter ddatabbase openn;SQL alteer s

28、yystemm swiitch logffile; /*切换换日志*/RMANbackkup dataabasee formmat /数据备备份路径/fulll_%d_%T_%s pplus archhivellog fformaat /日志备份份路径/aarch_%d_%T_%ss deeletee alll inpput;数据库命中中率的监测测1、 监监控 SGGA中数据据高速缓冲冲区的命中中率 sselecct a.valuue + b.vaalue loggicall_reaads, c.vvaluee phhys_rreadss,(100 * (a.vaalue+b.vaalue)-

29、c.vvaluee) / (a.vvaluee+b.vvaluee) SGAA DATTA BUUFFERR HITT RATTIO ffrom v$syysstaat a, v$ssyssttat bb, v$syssstat c wheere aa.staatisttic# = 388 andd b.sstatiisticc# = 39 aand cc.staatisttic# = 400; 2、监控 SGA 中字典缓缓冲区的命命中率selecct 1000*(ssum(ppins-relooads)/suum(piins) DDICTIIONARRY BUUFFERR HITT RATT

30、IO fromm v$llibraarycaache;3、监控 SGA 中共享缓缓存区的命命中率，selecct 1100*(summ(pinns-reeloadds)/sum(pinss) SQQL SHHARE AREAA HITT RATTIO fromm v$llibraarycaache;4、整个个SGA使使用率的检检查sellect namee,sgaasizee/10224/10024 totaal sgga M,byttes/11024/10244 freee sgga M,rouund(bbytess/sgaasizee*1000, 2) frree rratioo% ffr

31、om (seleect ssum(bbytess) sggasizze frrom ssys.vv_$sggastaat) ss, syys.v_$sgaastatt f wheree e = freee memmory;最消耗资源源进程的检检测1、查看某某个会话正正在执行的的SQL语语句及该会会话是哪台台台机器发发出selecct b.ussernaame,mmandd,b.ttermaainall,b.oosuseer b.machhine a.sqql_teext ffrom v$sqqltexxt a,v$seessioon b wheree a.aaddreess=bb.sqll_a

32、dddresss andd b.ssid=SSESSIION_IIDSQLPLLUS下找找出最消耗耗资源的110个进程程的SQLL指令集!ps -eaf | grrep oora | greep -vv /ssh | grepp -v ora_ | aawk priint $4,$11,$2 | sortt -r | heead 10 （HHP平台）!ps -eaf -o ppcpu,pid,userr,ttyy,commm | grepp oraa | ggrep -v /sh | grrep -v orra_ | sorrt -rr | hhead 10（IIBM平台台）promppt E

33、nnter The UNIXX PIDD :acceppt PIIDSELECCT aa.useernamme, aa.terrminaal, ogramm, b.sql_texttFROM vv$sesssionn a, v$sqqlareea b, v$pproceess ccWHEREE ( c.sspid = &PID OR a.prrocesss = &PIID )AND aa.padddr = c.aaddrAND aa.sqll_adddresss = bb.adddresss；3、利用数数据自带统统计工具检检测其性能能和瓶颈A、启动统统计程序：svrmggrlsvrmggrl$

34、ORAACLE_HOMEE/admmin/rrdbmss/utllbstaat.sqqlB、停停止统计程程序svrmmgrlsvrmggrl$ORAACLE_HOMEE/admmin/rrdbmss/utllestaat.sqqlC、一一般在系统统忙时开始始统计程序序，半小时时后停止统统计程序，不不能执行过过长，否则则会影响系系统性能；停止统计计程序后，会会在当前目目录产生名名为repport.txt的的文本文件件，应对其其做分析。锁的监测及及处理1、锁的监监测及锁住住哪些表（注注意绝大部部分锁都不不是死锁）sellect id1,counnt(*) frrom vv$locck grroup

35、 by iid2 hhavinng coount(*)11;sellect objeect_nname,objeect_ttype fromm dbaa_objjectss wheere oobjecct_idd=id11;这里v$lockk中的idd1和alll_obbjectts中的oobjecct_idd 是相对对应的。2、查看哪哪些会话被被锁并杀死死会话selecct idd1,coount(*) fromm v$llock grouup byy id22 havving counnt(*)1;selecct siid,seeriall# frrom vv$sesssionn whee

36、re iid1=被被锁的表IID；alterr sysstem killl sesssionn siid,seeriall#;(注意siid,seeriall#都为数数字)死数据库僵僵死进程（假假设此进程程的数据库库会话号为为X）selecct sppid ffrom v$prrocesss a ,v$ssessiion bbwheree a.adddr=b.padddr and b.siid=x;找出此数据据库会话的的操作系统统进程sppid ,然后在操操作系统提提示符号下下杀死此进进程 kkill spidd SQL语句句执行技巧巧1、打开开和关闭显显示SQLL语句的执执行方案（如如使用哪

37、个个索引等）sett auttotraace oon;sett auttotrooace off;2、打开开和关闭显显示SQLL语句的执执行时间sett timme onn;sett timme offf;sett timming on;sett timming off;3、尽量量使用 uunionn alll 而不使使用uniion ,因为unnion操操作时要排排序并移走走重复记录录，而unnion all不不执行上述述操作，所所以速度要要块很多倍倍。4、避免免在SQLL里使用PPL/SQQL功能调调用，以提提高速度5、查询询时避免使使用 liike %strring,以免全全表扫描，而而

38、likee striing%则使用了了对应字段段的索引。6、定期期重建索引引，提高查查询速度altter iindexx INDDEX_NNAME rebuuild ;保存SQLL输出到文文本文件(以输出到到ls.ttxt 为为例)set sserveeroutt onspoool lss.txttSQL 语语句Spooll offf。表空间的管管理1、查看所所有表空间间大小和使使用率selecct a.tabllespaace_nname,sum(a.byytes)/10224/10024 totaal siize(MM),roundd(100000*(suum(a.bytees)/1102

39、4/10244-summ(b.bbytess)/10024/11024)/1000 useed siize (M),roundd(100000*(sum(a.byytes)/10224/10024-ssum(bb.byttes)/10244/10224)/(sum(a.byytes)/10224/10024)/1000 peercennt %from dba_dataa_filles aa,(seelectt tabblesppace_namee,fille_idd,sum(bbytess) byytes from dba_freee_spaace ggroupp by tabllespaac

40、e_nname,filee_id ) b wheree a.ttableespacce_naame=bb.tabblesppace_nameeand aa.fille_idd=b.ffile_id ggroupp by a.taablesspacee_namme;2、使用文文件系统的的表空间扩扩展 altter ddatabbase TABLLESPAACE_NNAME add dataafilee FILLE_NAAME ssize SIZEE;3、用裸设设备表空间间的扩展alterr dattabasse TAABLESSPACEE_NAMME addd daatafiile RRAW_D

41、DEVICCE_NAAME ssize SIZEE;这里需要注注意的是裸裸设备要提提前建立后后，且这里里使用的是是裸设备的的字符文件件（类型为为C）,SSIZE值值要比其实实际大小要要稍微小一一些。4、集群中中，在某一一台主机上上共享卷组组上增加文文件系统和和裸设备后后，一定要要同步到集群群中另外的的主机，否否则当数据据库在另外外的主机中中启动时，会会找不着相相应的设备备号，导致致数据库不不能够被打打开。5、查找表表空间的剩剩余空间块块数以及最最大空闲块块的大小，如如果碎片过过多或最大大空闲块过过小，需要要手工合并并表空间碎碎片selecct taablesspacee_namme,cooun

42、t(*)freee bloock nnumbeer,ttruncc(summ(byttes)/10244/10224)totaal frree ssize(M),trunnc(maax(byytes)/10224/10024) max blocck(M) frrom ddba_ffree_spacce grroup by ttableespacce_naame;6、手工合合并某表空空间碎片alterr tabblesppace TABLLESPAACE_NNAME coallescee; 数据文件件I/O的的统计优化化 col 文文件名 fformaat a335；selecct df.nna

43、me 文件名,fs.pphyrdds 读次次数,fss.phyywrtss 写次数数,(fs.readdtim/decoode(ffs.phhyrdss,0,-1,fss.phyyrds) 读时时间,(fs.writtetimm/deccode(fs.pphywrrts,00,-1,fs.pphywrrts) 写时间间from v$daatafiile ddf,v$fileestatt fswheree df.filee#=fss.fille# ordeer byy e；错误号的跟跟踪出理1、在操操作系统级级显示其错错误详细信信息和常用用解决办法法以报orra_40031错误误为例）$ooer

44、r ora 403112、有时时为了进一一步跟踪其其错误，让让其产生更更详细的告告警日志在在用户报警警文件里，需需要在数据据库的初始始化文件里里加下面一一句配置，并并重新启动动数据库eventt = 40311 traace nname erroorstaack llevell 3这里注意的的是，跟踪踪完毕后，应应将此句话话屏蔽掉，以以免影响数数据库性能能。日常问题处处理流程ORACLLE数据库库日常检查查1、登录到到主机上利利用sqllpluss 检查是是否可用； 2、数据库库告警日志志的检查$ORACCLE_HHOME/admiin/INNSTANNCE_NNAME/bdummp/allert_INSTTANCEE_NAMME.loog此文件中记记载数据库库的启动、停止、系系统级修改改、告警信信息、日志志切换，是是日常维护护中的重要要依据。会话连接日日志的清理理会话连接日日志$ORRACLEE_HOMME/neetworrk/loog/liistenner.llog,此此文件中记记载着每个个连接登