Oracle数据库维护手册6688.docx

数据库维护手册册目 录1前言442ORACCLE维护方方法42.1数据库库启动42.2数据库库停止52.3监听器器的启停52.4用户管管理62.5数据库库参数文件62.6数据库库概况的查询询72.7常用对对象的创建和和使用82.8失效数据库库对象的检测测和编译92.9数据备备份的技巧102.10数据据库命中率的的监测112.111最消耗资源源进程的检测测112.12锁的的监测及处理理122.13SQQL语句执行行技巧132.14表空空间的管理142.15数据据文件I/OO的统计优化化152.16错误误号的跟踪出出理153日常问题题处理流程163.1ORAACLE数据据库日常检查查163.2会话连连接日志的清清理163.3数据库库hang住时时的停止和方方法163.4扩表空空间方法173.5死锁会会话的杀死173.6未决两两阶段事务的的回滚184相关通用用工具介绍184.1EM工工具181 前言本文主要是简单单介绍和总结结日常Oraacle数据据在运行维护护方面的一些些方法和技巧巧。通过该手手册以便于相相关技术人员员的日常运维维工作。2 ORACLE DB维护方方法2.1 数据库启动1、以oraccle用户用用户登录，sqlpluus /as ssysdbaasqlpluss> staartup2、以oraccle用户或或ora8116用户登录录sqlpluus /as ssysdbaasqlpluus >sttartupp nomuunt sqlpluus >allter ddatabaase moount；sqlpluus >allter ddatabaase oppen；3、第一种启动动方式是最常常用的手工启启动方式，第第二种启动方方式是数据库库有故障时常常用的逐步启启动方式，可可以观察故障障点。因为sstartuup nommount 语句只读取取spfille（serrver pparameeter ffile 数数据库参数文文件），启动动instaance，启启动SGA和和后台进程；alterr dataabase mountt语句打开控控制文件，确确认数据文件件和联机日志志文件的位置置，但此时不不对数据文件件和日志文件件进行校验检检查；altter daatabasse opeen打开包括括Redo log文件件在内的所有有数据库文件件，这种方式式下可访问数数据库中的数数据。Staartup完完成功能是上上述三条语句句的之和。2.2 数据库停止1、sqlpplus /as ssysdbaasqlpluus >shhutdowwn norrmal正常方式关闭数数据库。2、shutddown iimmediiate立即方式关闭数数据库,在 SQLPLLUS中执行行shutddown iimmediiate，数数据库并不立立即关闭，而而是在所有事事务执行完毕毕并提交工作作后才关闭，所所以可能会等等待，因此在在关闭数据前前要停掉连接接到数据库的的所有应用程程序。建议日日常维护工作作中的关闭数数据库，采用用此方式。3、shuttdown abortt直接关闭数据库库，正在访问问数据库的会会话会被突然然终止。如果果数据库中有有大量操作正正在执行，这这时执行shhutdowwn aboort后，因因日志回滚、前前滚（Rolll Bacck/Roll Forwaard），下下次重新启动动数据库需要要教长时间。当当用shuttdown immeddiate不不能关闭数据据库时，shhutdowwn aboort可以立立即完成数据据库的关闭操操作。2.3 监听器的启停1、监听器的启启动，以orracle用用户用户登录录lsnrcttl staart $ORACLLE_SIDD2、监听器的停停止，以orracle用用户用户登录录lsnrcttl stoop $OORACLEE_SID3、监听器状态态的查看，以以oraclle用户用户户登录lsnrcttl staatus $ORRACLE_SID4、检测服务名名是否有效，在在操作系统下下运行tnspiing SEERVICEE_NAMEE NUMMBERSSERVICCE_NAMME为你建立立的服务名，NNUMBERRS为你要试试PING数数据库服务的的次数2.4 用户管理1、用SQLL语句查看系系统中已有用用户情况 sellect * fromm dba_userss;2、增加新的的用户，并授授予连接和资资源权限，只只有授予连接接权限才可登登录，授予资资源权限才可可建表和修改改creatte useer USEERNAMEE proffile ddefaullt identtifiedd by PPASSWOORDdefaultt tabllespacce DEFFALUT_TABLEESPACEE_NAMEE temporaary taablesppace TTEMP_TTABLESSPACE_NAME ;grant cconnecct to 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;alterr userr USERRNAME profiile PRROFILEENAME;2.5 数据库参数文件件1、参数文件件位置$ORRACLE_HOME/dbs/sspfileeINSTAANCE_NNAME.oora,参数数主要分为动动态参数和非非动态参数，动动态参数修改改够直接生效效，非动态参参数需要重新新启动数据库库才能生效。 2、 修修改参数的方方法 alter systeem sett PARAAMETERR_NAMEE = VAAlUE sscope=both(systeem/spffile)3、几个重要要的参数A、db_bblock_size 数据库块大大小，数据库库创建时决定定，创建后不不能修改。B、db_bblock_buffeers数据据高速缓冲区区大小为此值值与db_bblock_buffees的乘积，该该区越大越好好。 C、sharre_poool_sizze 程序高高速缓冲区和和数据字典缓缓冲区的大小小，主要用于于存储执行过过的sql语语句，减少重重复分析，提提高运行速度度。该区也越越大越好，但但建议数据高高速缓冲区加加上程序高速速缓冲区和数数据字典缓冲冲区的大小（即即SGA）一般不超过过系统物理内内存的50%。D、sortt_areaa_sizee每个会话话用于排序操操作的内存大大小，建议为为默认值的两两倍到M之间。E、proccess能能同时访问数数据库的最大大进程数，根根据连接数的的多少，来设设定，一般为为300以上上。F、db_iio_slaaves 后后台写进程数数 对I/OO比较繁忙的的数据库，可可以将其设为为多于1个。（以上参数以99i为参考，在在10g里个别别参数名有所所变化）2.6 数据库概况的查查询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 FROMM DBA_ROLLBBACK_SSEGS;4、表、存储过过程的拥有者者名字和创建建日期select ownerr,objeect_naame,obbject_type,creatted frrom alll_objjectswhere objeect_tyype=TABLEEOR OBBJECT_TYPE=PROCEEDURE''5、其他常用用的视图和表表v$locck 锁的情况况v$sesssion 会话的情情况v$proocess 进程的情况况v$parrameteer 数据库库参数v$rolllnamee 回滚段段的情况v$sqllarea 共享池中中的SQL语语句v$waiitstatt 等待的会会话dba_jjobs 数据里定义义的作业dba_jjobs_rrunninng 数据库库里正在运行行的作业dba_vviews 数据库中所所有视图的文文本dba_iindexss 数据库中中所有索引的的描述dba_ffree_sspace 数据库中所所有表空间自自由分区dba_ddb_linnks 数据据库中所有数数据库链接2.7 常用对象的创建建和使用1、表的创建建(在表空间间zy上创建建表dhhmm为例)creatte tabble dhhhm (hm varrchar22(11) ,yhmc vaarcharr2(60) tabblespaace zyy;2、索引的创创建(将DHHHM表上hhm字段建立立索引hmssy存储在表表空间zy上上为例)creatte inddex hmmsy onn dhhmm(hm) tableespacee zy;3、已有存储储过程、函数数、包源代码码的查找selecct namme,texxt froom useer_souurce wwhere name=NAME;4、 数据库链接的创创建和使用create publiic dattabasee linkkDBLIINKNAMME.worrld coonnectt to UUSERNAAME iddentdiied byy PASWWORD uusing SERVIICENAMME;select * froom TABBLE_NAAMEDBBLINKNNAME;5、同义词的创创建和使用(以dyh用用户可以查询询并修改yyyxt用户的的dhhm表表为例)crate ssynonyyn dyyh.dhhm ffor yyyxt.dhhm;grant sselectt on yyxxt.dhhhm to dyh;grant uupdatee on yyyxt.ddhhm tto dyhh;2.8 失效数据库对象象的检测和编编译1、失效存储储过程的查找找和编译selectt 'altter prroceduure'|objecct_namme|' compiile;' from user_objeccts whhere sstatuss = 'IINVALIID' annd objject_ttype=''PROCEEDURE 然后复制粘贴并并执行第一条条语句的输出出结果2、失效的包包的查找和编编译selecct 'allter ppackagge'|oobjectt_namee|' ccompille;' ffrom uuser_oobjectts wheere sttatus = 'INNVALIDD' andd objeect_tyype= ''PACKAAGE然后复制粘贴并并执行第一条条语句的输出出结果2.9 数据备份的技巧巧一、EXP/IIMP1、利用管道将将数据库备份份压缩到磁带带中去,以备备份dhhmm,yhzll 表为例（磁带机名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_oof > ppipe_iif &exp USEERNAMEE/PASSSWORD tablees=(dhhhm,yhhzl) ffile=ppipe_oofrm pipee_ofrm pipee_if2、利用管道道将备份磁带带解压后倒入入到数据库中中去/usr/sbbin/mkknod ppipe_oof p/usr/sbbin/mkknod ppipe_iif pdd if=/dev/rrmt0 oof=pippe_of bs=10024 &uncomprress << pipee_of >> pipee_if &&imp USEERNAMEE/PASSSWORD tablees=(dhhhm,yhhzl) ffile=ppipe_iifrm pipee_ofrm pipee_if3、数据库备份份的分割,解解决有时文件件系统限制文文件大小不能能超过2G的问题（此此处只分割为为三个文件，当当然可以根据据实际情况继继续增加）exp USEERNAMEE/PASSSWORD file=(FILE11.dmp,FILE22.dmp,FILE33.dmp) llog=duump.loog fillesizee=1g tablles=(ddhhm,yyhzl)1、 为了提高备份速速度,可将DDIRECTT参数设为YY（但当该参参数设置为YY时，备份时时不做一些有有效性检查，应应慎用）,并并将bufffer开大，如如exp USEERNAMEE/PASSSWORD direect=y buffeer=81992000 tabless=(dhhhm,yhzzl) fiile=1.dmp2、 只备份建表和建建索引语句exp USEERNAMEE/PASSSWORD rows=n tabbles=(dhhm,yhzl) filee=1.dmmp5、 有时为了提高倒倒入速度，应应该将数据和和索引的倒入入倒出分开，而而不是一起进进行二、RMAN备备份恢复1、非归档模式式下数据库必必须在mouunt状态下下进行备份。$rman ttargett/ 进入rman全库备份RMAN>shhutdowwn immmediatte；关闭数数据库RMAN>sttartupp mounnt；RMAN>baackup databbase fformatt /路径/DAAT_%d_%T_%ss；RMAN>allter ddatabaase oppen；恢复数据库库为OPENN状态RMAN>liist baackupsset off dataabase; 备份记录录RMAN>liist baackup;恢复RMAN>shhutdowwn immmediatte；RMAN>sttartupp nomounnt；RMAN>reestoree conttrolfiile frrom 控制文件备备份;RMAN>allter ddatabaase moount;RMAN>reestoree dataabase;RMAN>reecoverr dataabase;RMAN>allter ddatabaase oppen reesetloogs; 打打开数据库并并重做日志2、 归档模式切换归档模式SQL> allter ssystemm set log_aarchivve_desst_1=''locattion=归归档路径' scopee=bothh; 首先先需要指定归归档路径SQL > sstartuup mouunt; SQL > aalter databbase aarchivvelog; 非归档模模式noarrchiveelogSQL > aalter databbase oopen;SQL > aalter systeem swiitch llogfille; /*切换日日志*/RMAN>baackup databbase formaat '/数数据备份路径径/fulll_%d_%T_%s'' pluss archhiveloog forrmat ''/日志备份份路径/arrch_%dd_%T_%s' deelete all iinput;2.10 数据库命中率的的监测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 wwhere a.staatistiic# = 38 annd b.sstatisstic# = 39 andd 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使用率的的检查selecct namme,sgaasize/1024/1024 "totaal sgaa M",bbytes/1024/1024 "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'2.11 最消耗资源进程程的检测1、查看某个会会话正在执行行的SQL语语句及该会话话是哪台台机机器发出select b.useernamee,b.coommandd,b.teermainnal,b.osuseer b.mmachinne a.ssql_teext frrom v$sqlteext a,v$sesssion b where aa.addrress=bb.sql_addreess annd b.ssid=SEESSIONN_ID2、 SQLPLUSS下找出最消消耗资源的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,coomm | grep ora | grepp -v /sh | grepp -v oora_ | sortt -r | headd 10（IIBM平台）prompt Enterr The UNIX PID :accept PIDSELECT a.ussernamme, a.termiinal, a.proogram, 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_adddress = b.aaddresss；3、利用数据自自带统计工具具检测其性能能和瓶颈A、启动统计程程序：svrmgrll>svrmgrll>$ORRACLE_HOME/adminn/rdbmms/utllbstatt.sqlB、停止统统计程序svrmgrrl>svrmgrll>$ORRACLE_HOME/adminn/rdbmms/utllestatt.sqlC、一般在在系统忙时开开始统计程序序，半小时后后停止统计程程序，不能执执行过长，否否则会影响系系统性能；停停止统计程序序后，会在当当前目录产生生名为repport.ttxt的文本本文件，应对对其做分析。2.12 锁的监测及处理理1、锁的监测及及锁住哪些表表（注意绝大大部分锁都不不是死锁）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 counnt(*)>>1;select sid,sseriall# froom v$ssessioon wheere idd1=被锁的的表ID；alter ssystemm killl sesssion ''sid,seerial#'(注意意sid,sseriall#都为数字字)3、 死数据库僵死进进程（假设此此进程的数据据库会话号为为X）select spid from v$proocess a ,v$sessiion bwhere aa.addrr=b.paaddr aand b.sid=xx;找出此数据库会会话的操作系系统进程sppid ,然然后在操作系系统提示符号号下杀死此进进程 kill spid 2.13 SQL语句执行行技巧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、查询时避避免使用 llike %striing,以免全表表扫描，而llike strinng%则使用了对对应字段的索索引。6、定期重建建索引，提高高查询速度alterr indeex INDDEX_NAAME reebuildd ;6、 保存SQL输出出到文本文件件(以输出到到ls.txxt 为例)set serrverouut onspool ls.txxtSQL 语句Spool ooff。2.14 表空间的管理1、查看所有表表空间大小和和使用率select a.tabblespaace_naame,suum(a.bbytes)/10244/10244 "tottal siize(M)",round(110000*(summ(a.byytes)/1024/1024-sum(bb.bytees)/10024/10024)/1000 "uused ssize (M)",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 tabblespaace_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_DEVIICE_NAAME siize SIIZE;这里需要注意的的是裸设备要要提前建立后后，且这里使使用的是裸设设备的字符文文件（类型为为C）,SIIZE值要比比其实际大小小要稍微小一一些。4、集群中，在在某一台主机机上共享卷组组上增加文件件系统和裸设设备后，一定定要同步到集群中另另外的主机，否否则当数据库库在另外的主主机中启动时时，会找不着着相应的设备备号，导致数数据库不能够够被打开。5、查找表空间间的剩余空间间块数以及最最大空闲块的的大小，如果果碎片过多或或最大空闲块块过小，需要要手工合并表表空间碎片select tableespacee_namee,counnt(*)"free bblock 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;2.15 数据文件I/O的统计优优化 col 文件名名 formmat a335；select df.namme 文件名名,fs.pphyrdss 读次数,fs.phhywrtss 写次数,(fs.reeadtimm/decoode(fss.phyrrds,0,-1,fss.phyrrds) 读时间,(fs.wrritetiim/deccode(ffs.phyywrts,0,-1,fs.phhywrtss) 写时时间from vv$dataafile df,v$filesstat ffswhere ddf.fille#=fss.filee# orderr by ddf.namme；2.16 错误号的跟踪出出理1、在操作系系统级显示其其错误详细信信息和常用解解决办法以报报ora_44031错误误为例）$>oerrr oraa 403112、有时为了了进一步跟踪踪其错误，让让其产生更详详细的告警日日志在用户报报警文件里，需需要在数据库库的初始化文文件里加下面面一句配置，并并重新启动数数据库event = "40331 traace naame errrorsttack llevel 3"这里注意的是，跟跟踪完毕后，应应将此句话屏屏蔽掉，以免免影响数据库库性能。3 日常问题处理流流程3.1 ORACLE数数据库日常检检查1、登录到主机机上利用sqqlpluss 检查是否否可用； 2、数据库告警警日志的检查查$ORACLEE_HOMEE/admiin/INSSTANCEE_NAMEE/bdummp/aleert_INNSTANCCE_NAMME.logg此文件中记载数数据库的启动动、停止、系系统级修改、告告警信息、日日志切换，是是日常维护中中的重要依据据。3.2 会话连接日志的的清理会话连接日志$ORACLLE_HOMME/nettwork/log/llistenner.loog,此文件件中记载着每每个连接登录录时的地地址和时间等等情况，可作作为监测的依依据，因为此此文件不断增增大，需要定定期清空，清清空方式为在在$ORACCLE_HOOME/neetworkk/log目目录下运行操操作系统命令令>listenner.loog。 3.3 数据库hangg住时的停止止和方法在某个帐务数据据库发生故障障时，在将其其对应的服务务和后台帐务务应用停完后后，需要数据据库重新启动动，具体停止止方法如下： $lssnrctll stopp$kill 9 pss ef|grrep LOOCAL=NNO|greep v greep|awkk prinnt $2$sqlpluus /as ssydbasqlpluss >altter syystem switcch loggfile;sqlpluss >shuutdownn immeediatee启动方法为：$sqlpluus /as ssydbasqlpluss >staartup$lsnrcttl staart3.4 扩表空间方法方法一：利用图图形化得OEEM工具来扩扩，选择存储储管理的表空空间；方法二：利用脚脚本：alter ddatabaase TAABLESPPACE_NNAME aadd daatafille RAWW_DEVIICE_NAAME siize SIIZE;(注注：三个大写写字符串是需需要变化的具具体值)这里需要注意的的是裸设备要要提前建立，且且这里使用的的是裸设备的的字符文件（类类型为c）,SIZE值值要比其实际际大小要稍微微小一些，如如设备大小为为15M，语句句里就不能使使用15M，只要要比15M小100K左右就可可以。3.5 死锁会话的杀死死 如果发现一一会话SIDD为100，具具体操作如下下：(1)、sellect ssid,seerial#,proggram ffrom vv$sesssion wwhere sid=1100；查到其 serrial#号号，如这里为为1199；（2）、SELLECT a.siid,a.sseriall#,a.uusernaame, aa.termminal, a.prrogramm,b.sppid FROM v$s essioon a, v$prrocesss bWHERE a.siid = 1100 AND a.paaddr = b.adddr;b.spid为为操作系统进进程号，如这这里为255555（3） altter syystem kill sessiion '1100,11199'kill 掉OORACLEE回话（4） killl 9 255555kill掉操作作系统进程4 相关通用工具介介绍4.1 Enterprrise MManageer （EMM）EM是用于Orracle数数据库管理的的一个功能强强大的工具，并且可以通过IE访问连接，使用EM可以完成大部分的数据库管理工作。1. 作为 ooraclee 用户登录录到操作系统统上。执行以以下命令来启启动 dbcconsolle 进程：emctl sstart dbconnsoleEM启动后可以以通过登录以以下URL来来连接：https:/locaalhostts:55000/em2. 通过执行行以下命令来来检查 dbbconsoole 进程程的状态： emcttl staatus ddbconssole3. 停止 ddbconssole 进进程：emctl sstop dbcconsolle 第 26 页