Oracle数据库维护手册

1、 第 PAGE 17 页 数据库维护手册册目 录TOC o 1-3 h z u HYPERLINK l _Toc 1前言 PAGEREF _Toc h 4 HYPERLINK l _Toc 2ORACLEE维护方法 PAGEREF _Toc h 4 HYPERLINK l _Toc 2.1数据库启启动 PAGEREF _Toc h 4 HYPERLINK l _Toc 2.2数据库停停止 PAGEREF _Toc h 5 HYPERLINK l _Toc 2.3监听器的的启停 PAGEREF _Toc h 5 HYPERLINK l _Toc 2.4用户管理理 PAGEREF _Toc h 6

2、 HYPERLINK l _Toc 2.5数据库参参数文件 PAGEREF _Toc h 6 HYPERLINK l _Toc 2.6数据库概概况的查询 PAGEREF _Toc h 77 HYPERLINK l _Toc 2.7常用对象象的创建和使使用 PAGEREF _Toc h 8 HYPERLINK l _Toc 2.8失效数据据库对象的检检测和编译 PAGEREF _Toc h 99 HYPERLINK l _Toc 2.9数据备份份的技巧 PAGEREF _Toc h 100 HYPERLINK l _Toc 2.10数据库库命中率的监监测 PAGEREF _Toc h 11 HY

3、PERLINK l _Toc 2.11最消耗耗资源进程的的检测 PAGEREF _Toc h 11 HYPERLINK l _Toc 2.12锁的监监测及处理 PAGEREF _Toc h 112 HYPERLINK l _Toc 2.13SQLL语句执行技技巧 PAGEREF _Toc h 13 HYPERLINK l _Toc 2.14表空间间的管理 PAGEREF _Toc h 144 HYPERLINK l _Toc 2.15数据文文件I/O的统计计优化 PAGEREF _Toc h 15 HYPERLINK l _Toc 2.16错误号号的跟踪出理理 PAGEREF _Toc h 1

4、5 HYPERLINK l _Toc 3日常问题处理理流程 PAGEREFF _Tocc h 116 HYPERLINK l _Toc 3.1ORACCLE数据库库日常检查 PAGEREF _Toc h 116 HYPERLINK l _Toc 3.2会话连接接日志的清理理 PAGEREF _Toc h 16 HYPERLINK l _Toc 3.3数据库hhang住时时的停止和方方法 PAGEREF _Toc h 16 HYPERLINK l _Toc 3.4扩表空间间方法 PAGEREF _Toc h 17 HYPERLINK l _Toc 3.5死锁会话话的杀死 PAGEREF _Toc

5、 h 177 HYPERLINK l _Toc 3.6未决两阶阶段事务的回回滚 PAGEREF _Toc h18 HYPERLINK l _Toc 4相关通用工具具介绍 PAGEREF _Toc h 18 HYPERLINK l _Toc 4.1EM工具具 PAGEREF _Toc h 18前言本文主要是简单单介绍和总结结日常Oraacle数据据在运行维护护方面的一些些方法和技巧巧。通过该手手册以便于相相关技术人员员的日常运维维工作。ORACLE DB维护方方法数据库启动1、以oraccle用户用用户登录，sqlpluus /as ssysdbaasqlpluss staartup2、以ora

6、ccle用户或或ora8116用户登录录sqlpluus /as ssysdbaasqlpluus sttartupp nomuunt sqlpluus allter ddatabaase moount；sqlpluus allter ddatabaase oppen；3、第一种启动动方式是最常常用的手工启启动方式，第第二种启动方方式是数据库库有故障时常常用的逐步启启动方式，可可以观察故障障点。因为sstartuup nommount 语句只读取取spfille（serrver pparameeter ffile 数数据库参数文文件），启动动instaance，启启动SGA和和后台进程；alt

7、err dataabase mountt语句打开控控制文件，确确认数据文件件和联机日志志文件的位置置，但此时不不对数据文件件和日志文件件进行校验检检查；altter daatabasse opeen打开包括括Redo log文件件在内的所有有数据库文件件，这种方式式下可访问数数据库中的数数据。Staartup完完成功能是上上述三条语句句的之和。数据库停止1、sqlpplus /as ssysdbaasqlpluus shhutdowwn norrmal正常方式关闭数数据库。2、shutddown iimmediiate立即方式关闭数数据库,在 SQLPLLUS中执行行shutddown iim

8、mediiate，数数据库并不立立即关闭，而而是在所有事事务执行完毕毕并提交工作作后才关闭，所所以可能会等等待，因此在在关闭数据前前要停掉连接接到数据库的的所有应用程程序。建议日日常维护工作作中的关闭数数据库，采用用此方式。3、shuttdown abortt直接关闭数据库库，正在访问问数据库的会会话会被突然然终止。如果果数据库中有有大量操作正正在执行，这这时执行shhutdowwn aboort后，因因日志回滚、前滚（Rooll Baack/Roll Forwaard），下下次重新启动动数据库需要要教长时间。当用shuutdownn immeediatee不能关闭数数据库时，sshutdoo

9、wn abbort可以以立即完成数数据库的关闭闭操作。监听器的启停1、监听器的启启动，以orracle用用户用户登录录lsnrcttl staart $ORACLLE_SIDD2、监听器的停停止，以orracle用用户用户登录录lsnrcttl stoop $OORACLEE_SID3、监听器状态态的查看，以以oraclle用户用户户登录lsnrcttl staatus $ORRACLE_SID4、检测服务名名是否有效，在在操作系统下下运行tnspiing SEERVICEE_NAMEE NUMMBERSSERVICCE_NAMME为你建立立的服务名，NNUMBERRS为你要试试PING数数据

10、库服务的的次数用户管理1、用SQLL语句查看系系统中已有用用户情况 sellect * fromm dba_userss;2、增加新的的用户，并授授予连接和资资源权限，只只有授予连接接权限才可登登录，授予资资源权限才可可建表和修改改create user USERNNAME pprofille deffaultiidentiified by PAASSWORRDdefaultt tabllespacce DEFFALUT_TABLEESPACEE_NAMEE temporaary taablesppace TTEMP_TTABLESSPACE_NAME ;grant cconnecct to

11、USERNNAME;grant rresourrce too USERRNAME;3、修改用户户口令alterr userr USERRNAME identtifiedd by NNEWPASSSWORDD;4、删除用户户drop user USERNNAME;5、限制某个个已有用户会会话的连接数数(通过创建pprofille的方式)create profiile PRROFILEENAME limitt sesssions_per_uuser CONNECTT_NUMBBERS;alter uuser UUSERNAAME prrofilee PROFFILENAAME;数据库参数文件件1、

12、参数文件件位置$ORRACLE_HOME/dbs/sspfileeINSTAANCE_NNAME.oora,参数数主要分为动动态参数和非非动态参数，动动态参数修改改够直接生效效，非动态参参数需要重新新启动数据库库才能生效。 2、 修修改参数的方方法alter ssystemm set PARAMMETER_NAME = VAllUE sccope=bboth(ssystemm/spfiile)3、几个重要要的参数A、db_bblock_size 数据库块大大小，数据库库创建时决定定，创建后不不能修改。B、db_bblock_buffeers数据据高速缓冲区区大小为此值值与db_bblock_b

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

14、进程数数 对I/OO比较繁忙的的数据库，可可以将其设为为多于1个。（以上参数以99i为参考，在在10g里个别别参数名有所所变化）数据库概况的查查询1、表空间的情情况selectt tabllespacce_namme,staauts ffrom ddba_taablesppaces;2、数据文件的的情况SELECTT FILEE_NAMEE,FILEE_ID,TTABLESSPACE_NAME FROM DBA_DDATA_FFILES;3、段的情况SELECT SEGMEENT_NAAME,TTABLESSPACE_NAME,SEGMEENT_IDD,FILEE_ID,SSTATUSS FR

15、OMM DBA_ROLLBBACK_SSEGS;4、表、存储过过程的拥有者者名字和创建建日期select ownerr,objeect_naame,obbject_type,creatted frrom alll_objjectswhere objeect_tyype=TABLEEOR OBBJECT_TYPE=PROCEEDURE5、其他常用用的视图和表表v$locck 锁的情况况v$sesssion 会话的情情况v$proocess 进程的情况况v$parrameteer 数据库库参数v$rolllnamee 回滚段段的情况v$sqllarea 共享池中中的SQL语语句v$waiitsta

16、tt 等待的会会话dba_jjobs 数据里定义义的作业dba_jjobs_rrunninng 数据库库里正在运行行的作业dba_vviews 数据库中所所有视图的文文本dba_iindexss 数据库中中所有索引的的描述dba_ffree_sspace 数据库中所所有表空间自自由分区dba_ddb_linnks 数据据库中所有数数据库链接常用对象的创建建和使用1、表的创建建(在表空间间zy上创建建表dhhmm为例)creatte tabble dhhhm (hm varrchar22(11) ,yhmc vaarcharr2(60) tabblespaace zyy;2、索引的创创建(将DH

17、HHM表上hhm字段建立立索引hmssy存储在表表空间zy上上为例)create indexx hmsyy on dhhhm(hmm) tabblespaace zyy;3、已有存储过过程、函数、包源代码的的查找selecct namme,texxt froom useer_souurce wwhere name=NAME;数据库链接的创创建和使用create publiic dattabasee linkkDBLIINKNAMME.worrld coonnectt to UUSERNAAME iddentdiied byy PASWWORD uusing SERVIICENAMME;sele

18、ct * froom TABBLE_NAAMEDBBLINKNNAME;5、同义词的创创建和使用(以dyh用用户可以查询询并修改yyyxt用户的的dhhm表表为例)cratesyynonynn dyhh.dhhm ffor yyyxt.dhhm;grant sselectt on yyxxt.dhhhm to dyh;grant uupdatee on yyyxt.ddhhm tto dyhh;失效数据库对象象的检测和编编译1、失效存储储过程的查找找和编译select alteer proocedurre|oobjectt_namee| ccompille; ffrom uuser_oobje

19、ctts wheere sttatus = INNVALIDD andd objeect_tyype=PROCEEDURE 然后复制粘贴并并执行第一条条语句的输出出结果2、失效的包包的查找和编编译select alteer pacckage|objject_nname| commpile; froom useer_objjects wheree stattus = INVAALID and oobjectt_typee= PACKKAGE然后复制粘贴并并执行第一条条语句的输出出结果数据备份的技巧巧一、EXP/IIMP1、利用管道将将数据库备份份压缩到磁带带中去,以备备份dhhmm,yhzll

20、表为例（磁带机名IBBM为/deev/rmtt0，HP为为/dev/rtm/mm0）/usr/sbbin/mkknod ppipe_oof p/usr/sbbin/mkknod ppipe_iif pdd of=/dev/rrmt0 iif=pippe_if bs=10024 &compresss ppipe_iif &expUSERRNAME/PASSWWORD ttabless=(dhhhm,yhzzl) fiile=piipe_offrm pipee_ofrm pipee_if2、利用管道道将备份磁带带解压后倒入入到数据库中中去/usr/sbbin/mkknod ppipe_oof p/

21、usr/sbbin/mkknod ppipe_iif pdd if=/dev/rrmt0 oof=pippe_of bs=10024 &uncomprress pipee_if &imp USEERNAMEE/PASSSWORD tablees=(dhhhm,yhhzl) ffile=ppipe_iifrm pipee_ofrm pipee_if3、数据库备份份的分割,解解决有时文件件系统限制文文件大小不能能超过2G的问题（此此处只分割为为三个文件，当当然可以根据据实际情况继继续增加）exp USEERNAMEE/PASSSWORD file=(FILE11.dmp,FILE22.dmp,FI

22、LE33.dmp) llog=duump.loog fillesizee=1g tablles=(ddhhm,yyhzl)为了提高备份速速度,可将DDIRECTT参数设为YY（但当该参参数设置为YY时，备份时时不做一些有有效性检查，应应慎用）,并并将bufffer开大，如如expUSERRNAME/PASSWWORD direcct=y bbufferr= tabless=(dhhhm,yhzzl) fiile=1.dmp只备份建表和建建索引语句expUSERRNAME/PASSWWORD rrows=nn tablles=(ddhhm,yyhzl) file=1.dmpp有时为了提高倒倒入速

23、度，应应该将数据和和索引的倒入入倒出分开，而而不是一起进进行二、RMAN备备份恢复1、非归档模式式下数据库必必须在mouunt状态下下进行备份。$rman ttargett/ 进入rman全库备份RMANshhutdowwn immmediatte；关闭数数据库RMANsttartupp mounnt；RMANbaackup databbase fformatt /路径/DAAT_%d_%T_%ss；RMANallter ddatabaase oppen；恢复数据库库为OPENN状态RMANliist baackupsset off dataabase; 备份记录录RMANliist baac

24、kup;恢复RMANshhutdowwn immmediatte；RMANsttartupp nomounnt；RMANreestoree conttrolfiile frrom控制文件备备份;RMANallter ddatabaase moount;RMANreestoree dataabase;RMANreecoverr dataabase;RMANallter ddatabaase oppen reesetloogs; 打打开数据库并并重做日志归档模式切换归档模式SQL allter ssystemm set log_aarchivve_desst_1=locattion=归归档路径 s

25、copee=bothh; 首先先需要指定归归档路径SQL sstartuup mouunt; SQL aalter databbase aarchivvelog; 非归档模模式noarrchiveelogSQL aalter databbase oopen;SQL allter ssystemm swittch loogfilee;/*切换换日志*/RMANbaackupddatabaaseforrmat /数据备份份路径/fuull_%dd_%T_%s pllus arrchiveelog fformatt /日志志备份路径/arch_%d_%TT_%s delette alll inpuu

26、t;数据库命中率的的监测1、 监控 SGA中数数据高速缓冲冲区的命中率率 sellect aa.valuue + bb.valuue loogicall_readds, cc.valuue phhys_reeads,(100 * (a.vvalue+b.vallue)-cc.valuue) / (a.vaalue+bb.valuue) SGA DDATA BBUFFERR HIT RATIOO froom v$ssysstaat a, v$syssstat b, v$syssttat c where a.staatistiic# = 38 annd b.sstatisstic# = 39 an

27、dd c.sttatisttic# = 40; 2、监控 SGGA 中字典典缓冲区的命命中率select 100*(sum(ppins-rreloadds)/ssum(piins) DICCTIONAARY BUUFFER HIT RRATIO fromm v$liibraryycachee;3、监控 SGGA 中共享享缓存区的命命中率，select 100*(summ(pinss-relooads)/sum(pins) SQL SSHARE AREA HIT RRATIO fromm v$liibraryycachee;4、整个SGGA使用率的的检查select name,sgasiize/

28、10024/10024 ttotal sga MM,byttes/10024/10024 free sga MM,rouund(byytes/ssgasizze*1000, 2) freee ratiio% ffrom (selectt sum(bytess) sgaasize from sys.vv_$sgaastat) s, ssys.v_$sgasstat ff where f.namme = free memorry;最消耗资源进程程的检测1、查看某个会会话正在执行行的SQL语语句及该会话话是哪台台机机器发出selectbb.userrname,mmand,b.terrmainaal,

29、b.oosuserr b.maachinee a.sqql_texxt froom v$ssqltexxt a,vv$sesssion bb where aa.addrress=bb.sql_addreess annd b.ssid=SEESSIONN_IDSQLPLUSS下找出最消消耗资源的110个进程的的SQL指令令集!ps -eaaf | ggrep oora | grep -v /sh | grep -v orra_ | awk prinnt $4,$1,$22 | sort -r | head 10 （HHP平台）!ps -eaaf -o pcpu,pid,uuser,ttty,co

30、omm | grep ora | grepp -v /sh | grepp -v oora_ | sortt -r | headd 10（IIBM平台）prompt Enterr The UNIX PID :accept PIDSELECT a.ussernamme, a.termiinal, ogram, b.sqql_texxtFROM v$seessionn a, vv$sqlaarea bb, v$pprocesss cWHERE ( c.spid = &PPID OOR a.pprocesss = &PID )AND a.paaddr = c.adddrAND a.sqql_addd

31、ress = b.aaddresss；3、利用数据自自带统计工具具检测其性能能和瓶颈A、启动统计程程序：svrmgrllsvrmgrll$ORRACLE_HOME/adminn/rdbmms/utllbstatt.sqlB、停止统统计程序svrmgrrlsvrmgrll$ORRACLE_HOME/adminn/rdbmms/utllestatt.sqlC、一般在在系统忙时开开始统计程序序，半小时后后停止统计程程序，不能执执行过长，否否则会影响系系统性能；停停止统计程序序后，会在当当前目录产生生名为repport.ttxt的文本本文件，应对对其做分析。锁的监测及处理理1、锁的监测及及锁住哪些表表

32、（注意绝大大部分锁都不不是死锁）selecct id11,counnt(*) fromm v$loock grroup bby id22 haviing coount(*)1;selecct objject_nname,oobjectt_typee fromm dba_objeccts whhere oobjectt_id=iid1;这里v$loock中的iid1和alll_objjects中中的objeect_idd 是相对应应的。2、查看哪些会会话被锁并杀杀死会话select id1,ccount(*) ffrom vv$lockk grouup by id2 hhavingg counn

33、t(*)1;select sid,sseriall# froom v$ssessioon wheere idd1=被锁的的表ID；alter ssystemm killl sesssion sid,seerial#;(注意意sid,sseriall#都为数字字)死数据库僵死进进程（假设此此进程的数据据库会话号为为X）select spid from v$proocess a ,v$sessiion bwhere aa.addrr=b.paaddr aand b.sid=xx;找出此数据库会会话的操作系系统进程sppid ,然然后在操作系系统提示符号号下杀死此进进程 kill spid SQL语

34、句执行行技巧1、打开和关关闭显示SQQL语句的执执行方案（如如使用哪个索索引等）set aautotrrace oon;set aautotrroace off;2、打开和关关闭显示SQQL语句的执执行时间set ttime oon;set ttime ooff;set ttimingg on;set ttimingg off;3、尽量使用用 unioon alll 而不使用用unionn ,因为uunion操操作时要排序序并移走重复复记录，而uunion all不执执行上述操作作，所以速度度要块很多倍倍。4、避免在SSQL里使用用PL/SQQL功能调用用，以提高速速度5、查询时避避免使用 l

35、like %striing,以免全表表扫描，而llike strinng%则使用了对对应字段的索索引。6、定期重建建索引，提高高查询速度alterr indeex INDDEX_NAAME reebuildd ;保存SQL输出出到文本文件件(以输出到到ls.txxt 为例)set serrverouut onspool ls.txxtSQL 语句Spool ooff。表空间的管理1、查看所有表表空间大小和和使用率select a.tabblespaace_naame,suum(a.bbytes)/10244/10244 tottal siize(M),round(110000*(summ(a.

36、byytes)/1024/1024-sum(bb.bytees)/10024/10024)/1000ussed siize (MM),round(110000*(sum(a.byttes)/11024/11024-ssum(b.bytess)/10224/10224)/(sum(a.bytess)/10224/10224)/1100 ppercennt %from dbba_datta_filles a,(seleect taablesppace_nname,ffile_iid,sum(byttes) bbytes from dbba_freee_spaace grroup bby tabbl

37、espaace_naame,fiile_idd ) b where aa.tabllespacce_namme=b.ttablesspace_nameand a.ffile_iid=b.ffile_iid grooup byy a.taablesppace_nname;2、使用文件系系统的表空间间扩展 alterr dataabase TABLEESPACEE_NAMEE add dataffile FFILE_NNAME ssize SSIZE;3、用裸设备表表空间的扩展展alter ddatabaase TAABLESPPACE_NNAME aadd daatafille RAWW_DEV

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

39、numbeer,trrunc(ssum(byytes)/1024/1024)total free size(M),ttrunc(max(bbytes)/10244/10244) max bllock(MM) frrom dbba_freee_spaace grroup bby tabblespaace_naame;6、手工合并某某表空间碎片片alter ttablesspace TABLEESPACEE_NAMEE coallesce;数据文件I/OO的统计优化化col 文件名名 formmat a335；select e 文件名,fs.phhyrds 读次数,ffs.phyywrts 写次数

40、,(fs.reaadtim/decodde(fs.phyrdds,0,-1,fs.phyrdds) 读读时间,(fs.wriitetimm/decoode(fss.phywwrts,00,-1,ffs.phyywrts) 写时间间from v$dataffile ddf,v$ffilesttat fsswhere ddf.fille#=fss.filee#ordeer by df.naame；错误号的跟踪出出理1、在操作系系统级显示其其错误详细信信息和常用解解决办法以报报ora_44031错误误为例）$oerrr oraa 403112、有时为了了进一步跟踪踪其错误，让让其产生更详详细的告警日

41、日志在用户报报警文件里，需需要在数据库库的初始化文文件里加下面面一句配置，并并重新启动数数据库event = 40331 traace naame errrorsttack llevel 3这里注意的是，跟跟踪完毕后，应应将此句话屏屏蔽掉，以免免影响数据库库性能。日常问题处理流流程ORACLE数数据库日常检检查1、登录到主机机上利用sqqlpluss 检查是否否可用； 2、数据库告警警日志的检查查$ORACLEE_HOMEE/admiin/INSSTANCEE_NAMEE/bdummp/aleert_INNSTANCCE_NAMME.logg此文件中记载数数据库的启动动、停止、系系统级修改、告警信息、日志切换，是是日常维护中中的重要依据据。会话连接日志的的清理会话连接日志$ORACLLE_HOMME/nettwork/log/llistenner.loog,此文件件中记载着每每个连接登录录时的地地址和时间等等情况，可作作为监测的依依据，因为此此文件不断增增大，需要定定