年4月19日Oracle數(shù)據(jù)庫維護手冊資料內(nèi)容僅供參考，如有不當(dāng)或者侵權(quán)，請聯(lián)系本人改正或者刪除。數(shù)據(jù)庫維護手冊

目錄1 前言 42 ORACLE維護方法 42.1 數(shù)據(jù)庫啟動 42.2 數(shù)據(jù)庫停止 52.3 監(jiān)聽器的啟停 52.4 用戶管理 62.5 數(shù)據(jù)庫參數(shù)文件 62.6 數(shù)據(jù)庫概況的查詢 72.7 常見對象的創(chuàng)立和使用 82.8 失效數(shù)據(jù)庫對象的檢測和編譯 92.9 數(shù)據(jù)備份的技巧 102.10 數(shù)據(jù)庫命中率的監(jiān)測 112.11 最消耗資源進程的檢測 112.12 鎖的監(jiān)測及處理 122.13 SQL語句執(zhí)行技巧 132.14 表空間的管理 142.15 數(shù)據(jù)文件I/O的統(tǒng)計優(yōu)化 152.16 錯誤號的跟蹤出理 153 日常問題處理流程 163.1 ORACLE數(shù)據(jù)庫日常檢查 163.2 會話連接日志的清理 163.3 數(shù)據(jù)庫hang住時的停止和方法 163.4 擴表空間方法 173.5 死鎖會話的殺死 173.6 未決兩階段事務(wù)的回滾 184 相關(guān)通用工具介紹 184.1 EM工具 18

前言本文主要是簡單介紹和總結(jié)日常Oracle數(shù)據(jù)在運行維護方面的一些方法和技巧。經(jīng)過該手冊以便于相關(guān)技術(shù)人員的日常運維工作。ORACLEDB維護方法數(shù)據(jù)庫啟動1、以oracle用戶用戶登錄, sqlplus‘/assysdba’sqlplus>startup2、以oracle用戶或ora816用戶登錄 sqlplus‘/assysdba’ sqlplus>startupnomunt sqlplus>alterdatabasemount; sqlplus>alterdatabaseopen;3、第一種啟動方式是最常見的手工啟動方式,第二種啟動方式是數(shù)據(jù)庫有故障時常見的逐步啟動方式,能夠觀察故障點。因為startupnomount語句只讀取spfile(serverparameterfile數(shù)據(jù)庫參數(shù)文件),啟動instance,啟動SGA和后臺進程;alterdatabasemount語句打開控制文件,確認(rèn)數(shù)據(jù)文件和聯(lián)機日志文件的位置,但此時不對數(shù)據(jù)文件和日志文件進行校驗檢查;alterdatabaseopen打開包括Redolog文件在內(nèi)的所有數(shù)據(jù)庫文件,這種方式下可訪問數(shù)據(jù)庫中的數(shù)據(jù)。Startup完成功能是上述三條語句的之和。數(shù)據(jù)庫停止1、 sqlplus‘/assysdba’ sqlplus>shutdownnormal正常方式關(guān)閉數(shù)據(jù)庫。2、shutdownimmediate立即方式關(guān)閉數(shù)據(jù)庫,在SQLPLUS中執(zhí)行shutdownimmediate,數(shù)據(jù)庫并不立即關(guān)閉,而是在所有事務(wù)執(zhí)行完畢并提交工作后才關(guān)閉,因此可能會等待,因此在關(guān)閉數(shù)據(jù)前要停掉連接到數(shù)據(jù)庫的所有應(yīng)用程序。建議日常維護工作中的關(guān)閉數(shù)據(jù)庫,采用此方式。 3、shutdownabort直接關(guān)閉數(shù)據(jù)庫,正在訪問數(shù)據(jù)庫的會話會被突然終止。如果數(shù)據(jù)庫中有大量操作正在執(zhí)行,這時執(zhí)行shutdownabort后,因日志回滾、前滾(RollBack/RollForward),下次重新啟動數(shù)據(jù)庫需要教長時間。當(dāng)用shutdownimmediate不能關(guān)閉數(shù)據(jù)庫時,shutdownabort能夠立即完成數(shù)據(jù)庫的關(guān)閉操作。監(jiān)聽器的啟停1、監(jiān)聽器的啟動,以oracle用戶用戶登錄 lsnrctlstart[$ORACLE_SID]2、監(jiān)聽器的停止,以oracle用戶用戶登錄 lsnrctlstop[$ORACLE_SID]3、監(jiān)聽器狀態(tài)的查看,以oracle用戶用戶登錄 lsnrctlstatus[$ORACLE_SID]4、檢測服務(wù)名是否有效,在操作系統(tǒng)下運行 tnspingSERVICE_NAMENUMBERS SERVICE_NAME為你建立的服務(wù)名,NUMBERS為你要試PING數(shù)據(jù)庫服務(wù)的次數(shù)用戶管理 1、用SQL語句查看系統(tǒng)中已有用戶情況 select*fromdba_users; 2、增加新的用戶,并授予連接和資源權(quán)限,只有授予連接權(quán)限才可登錄,授予資源權(quán)限才可建表和修改 createuserUSERNAMEprofiledefaultidentifiedbyPASSWORDdefaulttablespaceDEFALUT_TABLESPACE_NAMEtemporarytablespaceTEMP_TABLESPACE_NAME;grantconnecttoUSERNAME;grantresourcetoUSERNAME; 3、修改用戶口令 alteruserUSERNAMEidentifiedbyNEWPASSWORD; 4、刪除用戶 dropuserUSERNAME; 5、限制某個已有用戶會話的連接數(shù)(經(jīng)過創(chuàng)立profile的方式)createprofilePROFILENAMElimitsessions_per_userCONNECT_NUMBERS; alteruserUSERNAMEprofilePROFILENAME;數(shù)據(jù)庫參數(shù)文件 1、參數(shù)文件位置$ORACLE_HOME/dbs/spfileINSTANCE_NAME.ora,參數(shù)主要分為動態(tài)參數(shù)和非動態(tài)參數(shù),動態(tài)參數(shù)修改夠直接生效,非動態(tài)參數(shù)需要重新啟動數(shù)據(jù)庫才能生效。2、修改參數(shù)的方法altersystemsetPARAMETER_NAME=VAlUEscope=both(system/spfile) 3、幾個重要的參數(shù) A、db_block_size數(shù)據(jù)庫塊大小,數(shù)據(jù)庫創(chuàng)立時決定,創(chuàng)立后不能修改。 B、db_block_buffers數(shù)據(jù)高速緩沖區(qū)大小為此值與db_block_buffes的乘積,該區(qū)越大越好。 C、share_pool_size程序高速緩沖區(qū)和數(shù)據(jù)字典緩沖區(qū)的大小,主要用于存儲執(zhí)行過的sql語句,減少重復(fù)分析,提高運行速度。該區(qū)也越大越好,但建議數(shù)據(jù)高速緩沖區(qū)加上程序高速緩沖區(qū)和數(shù)據(jù)字典緩沖區(qū)的大小(即SGA)一般不超過系統(tǒng)物理內(nèi)存的50%。 D、sort_area_size每個會話用于排序操作的內(nèi)存大小,建議為默認(rèn)值的兩倍到１M之間。 E、process能同時訪問數(shù)據(jù)庫的最大進程數(shù),根據(jù)連接數(shù)的多少,來設(shè)定,一般為300以上。 F、db_io_slaves后臺寫進程數(shù)對I/O比較繁忙的數(shù)據(jù)庫,能夠?qū)⑵湓O(shè)為多于1個。 (以上參數(shù)以9i為參考,在10g里個別參數(shù)名有所變化)數(shù)據(jù)庫概況的查詢1、表空間的情況 selecttablespace_name,stautsfromdba_tablespaces;2、數(shù)據(jù)文件的情況 SELECTFILE_NAME,FILE_ID,TABLESPACE_NAMEFROMDBA_DATA_FILES;3、段的情況SELECTSEGMENT_NAME,,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUSFROMDBA_ROLLBACK_SEGS;4、表、存儲過程的擁有者名字和創(chuàng)立日期selectowner,object_name,object_type,createdfromall_objects whereobject_type=’TABLE’OROBJECT_TYPE=’PROCEDURE' 5、其它常見的視圖和表 v$lock鎖的情況 v$session會話的情況 v$process進程的情況 v$parameter數(shù)據(jù)庫參數(shù) v$rollname回滾段的情況 v$sqlarea共享池中的SQL語句 v$waitstat等待的會話 dba_jobs數(shù)據(jù)里定義的作業(yè) dba_jobs_running數(shù)據(jù)庫里正在運行的作業(yè) dba_views數(shù)據(jù)庫中所有視圖的文本 dba_indexs數(shù)據(jù)庫中所有索引的描述 dba_free_space數(shù)據(jù)庫中所有表空間自由分區(qū) dba_db_links數(shù)據(jù)庫中所有數(shù)據(jù)庫鏈接常見對象的創(chuàng)立和使用 1、表的創(chuàng)立(在表空間zy上創(chuàng)立表dhhm為例) createtabledhhm(hmvarchar2(11),yhmcvarchar2(60))tablespacezy; 2、索引的創(chuàng)立(將DHHM表上hm字段建立索引hmsy存儲在表空間zy上為例) createindexhmsyondhhm(hm)tablespacezy; 3、已有存儲過程、函數(shù)、包源代碼的查找 selectname,textfromuser_sourcewherename=NAME;數(shù)據(jù)庫鏈接的創(chuàng)立和使用createpublicdatabaselinkDBLINKNAME.worldconnecttoUSERNAMEidentdiedbyPASWORDusingSERVICENAME;select*fromTABLE_NAME@DBLINKNAME;5、同義詞的創(chuàng)立和使用(以dyh用戶能夠查詢并修改yyxt用戶的dhhm表為例)cratesynonyndyh.dhhmforyyxt.dhhm;grantselectonyyxt.dhhmtodyh;grantupdateonyyxt.dhhmtodyh;失效數(shù)據(jù)庫對象的檢測和編譯 1、失效存儲過程的查找和編譯 select'alterprocedure'||object_name||'compile;'fromuser_objectswherestatus='INVALID'andobject_type='PROCEDURE’然后復(fù)制粘貼并執(zhí)行第一條語句的輸出結(jié)果 2、失效的包的查找和編譯 select'alterpackage'||object_name||'compile;'fromuser_objectswherestatus='INVALID'andobject_type='PACKAGE’然后復(fù)制粘貼并執(zhí)行第一條語句的輸出結(jié)果數(shù)據(jù)備份的技巧一、EXP/IMP1、利用管道將數(shù)據(jù)庫備份壓縮到磁帶中去,以備份dhhm,yhzl表為例(磁帶機名IBM為/dev/rmt0,HP為/dev/rtm/m0)/usr/sbin/mknodpipe_ofp/usr/sbin/mknodpipe_ifpddof=/dev/rmt0if=pipe_ifbs=1024&compress<pipe_of>pipe_if&expUSERNAME/PASSWORDtables=(dhhm,yhzl)file=pipe_ofrmpipe_ofrmpipe_if 2、利用管道將備份磁帶解壓后倒入到數(shù)據(jù)庫中去/usr/sbin/mknodpipe_ofp/usr/sbin/mknodpipe_ifpddif=/dev/rmt0of=pipe_ofbs=1024&uncompress<pipe_of>pipe_if&impUSERNAME/PASSWORDtables=(dhhm,yhzl)file=pipe_ifrmpipe_ofrmpipe_if3、數(shù)據(jù)庫備份的分割,解決有時文件系統(tǒng)限制文件大小不能超過2G的問題(此處只分割為三個文件,當(dāng)然能夠根據(jù)實際情況繼續(xù)增加)expUSERNAME/PASSWORDfile=(FILE1.dmp,FILE2.dmp,FILE3.dmp)log=dump.logfilesize=1gtables=為了提高備份速度,可將DIRECT參數(shù)設(shè)為Y(但當(dāng)該參數(shù)設(shè)置為Y時,備份時不做一些有效性檢查,應(yīng)慎用),并將buffer開大,如expUSERNAME/PASSWORDdirect=ybuffer=819tables=(dhhm,yhzl)file=1.dmp只備份建表和建索引語句expUSERNAME/PASSWORDrows=ntables=(dhhm,yhzl)file=1.dmp有時為了提高倒入速度,應(yīng)該將數(shù)據(jù)和索引的倒入倒出分開,而不是一起進行二、RMAN備份恢復(fù)1、非歸檔模式下數(shù)據(jù)庫必須在mount狀態(tài)下進行備份。$rmantarget/進入rman全庫備份RMAN>shutdownimmediate;關(guān)閉數(shù)據(jù)庫RMAN>startupmount;RMAN>backupdatabaseformat‘/路徑/DAT_%d_%T_%s’;RMAN>alterdatabaseopen;恢復(fù)數(shù)據(jù)庫為OPEN狀態(tài)RMAN>listbackupsetofdatabase;備份記錄RMAN>listbackup;恢復(fù)RMAN>shutdownimmediate;RMAN>startupnomount;RMAN>restorecontrolfilefrom‘控制文件備份’;RMAN>alterdatabasemount;RMAN>restoredatabase;RMAN>recoverdatabase;RMAN>alterdatabaseopenresetlogs;打開數(shù)據(jù)庫并重做日志歸檔模式切換歸檔模式SQL>altersystemsetlog_archive_dest_1='location=歸檔路徑'scope=both;首先需要指定歸檔路徑SQL>startupmount;SQL>alterdatabasearchivelog;非歸檔模式noarchivelogSQL>alterdatabaseopen;SQL>altersystemswitchlogfile;/*切換日志*/RMAN>backupdatabaseformat'/數(shù)據(jù)備份路徑/full_%d_%T_%s'plusarchivelogformat'/日志備份路徑/arch_%d_%T_%s'deleteallinput;數(shù)據(jù)庫命中率的監(jiān)測 1、監(jiān)控SGA中數(shù)據(jù)高速緩沖區(qū)的命中率selecta.value+b.value"logical_reads",c.value"phys_reads",(100*((a.value+b.value)-c.value)/(a.value+b.value))"SGADATABUFFERHITRATIO"fromv$sysstata,v$sysstatb,v$sysstatc wherea.statistic#=38andb.statistic#=39andc.statistic#=40;2、監(jiān)控SGA中字典緩沖區(qū)的命中率select100*(sum(pins-reloads))/sum(pins)"DICTIONARYBUFFERHITRATIO"fromv$librarycache;3、監(jiān)控SGA中共享緩存區(qū)的命中率,select100*((sum(pins-reloads))/sum(pins))"SQLSHAREAREAHITRATIO"fromv$librarycache; 4、整個SGA使用率的檢查 selectname,sgasize/1024/1024"totalsgaM",bytes/1024/1024"freesgaM",round(bytes/sgasize*100,2)"freeratio%"from(selectsum(bytes)sgasizefromsys.v_$sgastat)s,sys.v_$sgastatfwhere='freememory';最消耗資源進程的檢測1、查看某個會話正在執(zhí)行的SQL語句及該會話是哪臺臺機器發(fā)出selectb.username,mand,b.termainal,b.osuserb.machinea.sql_textfromv$sqltexta,v$sessionbwherea.address=b.sql_addressandb.sid=SESSION_IDSQLPLUS下找出最消耗資源的10個進程的SQL指令集!ps-eaf|grepora|grep-v\/sh|grep-vora_|awk'{print$4,$1,$2}'|sort-r|head–10(HP平臺)!ps-eaf-opcpu,pid,user,tty,comm|grepora|grep-v\/sh|grep-vora_|sort-r|head–10(IBM平臺)promptEnterTheUNIXPID:acceptPIDSELECTa.username,a.terminal,gram,b.sql_textFROMv$sessiona,v$sqlareab,v$processcWHERE(c.spid='&PID'ORcess='&PID')ANDa.paddr=c.addrANDa.sql_address=b.address;3、利用數(shù)據(jù)自帶統(tǒng)計工具檢測其性能和瓶頸A、啟動統(tǒng)計程序:svrmgrl>svrmgrl>@$ORACLE_HOME/admin/rdbms/utlbstat.sql B、停止統(tǒng)計程序 svrmgrl>svrmgrl>@$ORACLE_HOME/admin/rdbms/utlestat.sql C、一般在系統(tǒng)忙時開始統(tǒng)計程序,半小時后停止統(tǒng)計程序,不能執(zhí)行過長,否則會影響系統(tǒng)性能;停止統(tǒng)計程序后,會在當(dāng)前目錄產(chǎn)生名為report.txt的文本文件,應(yīng)對其做分析。鎖的監(jiān)測及處理1、鎖的監(jiān)測及鎖住哪些表(注意絕大部分鎖都不是死鎖) selectid1,count(*)fromv$lockgroupbyid2havingcount(*)>1; selectobject_name,object_typefromdba_objectswhereobject_id=id1; 這里v$lock中的id1和all_objects中的object_id是相對應(yīng)的。2、查看哪些會話被鎖并殺死會話selectid1,count(*)fromv$lockgroupbyid2havingcount(*)>1;selectsid,serial#fromv$sessionwhereid1=被鎖的表ID;altersystemkillsession'sid,serial#';(注意sid,serial#都為數(shù)字)死數(shù)據(jù)庫僵死進程(假設(shè)此進程的數(shù)據(jù)庫會話號為X)selectspidfromv$processa,v$sessionbwherea.addr=b.paddrandb.sid=x;找出此數(shù)據(jù)庫會話的操作系統(tǒng)進程spid,然后在操作系統(tǒng)提示符號下殺死此進程killspidSQL語句執(zhí)行技巧 1、打開和關(guān)閉顯示SQL語句的執(zhí)行方案(如使用哪個索引等) setautotraceon; setautotroaceoff; 2、打開和關(guān)閉顯示SQL語句的執(zhí)行時間 settimeon; settimeoff; settimingon; settimingoff; 3、盡量使用unionall而不使用union,因為union操作時要排序并移走重復(fù)記錄,而unionall不執(zhí)行上述操作,因此速度要塊很多倍。 4、避免在SQL里使用PL/SQL功能調(diào)用,以提高速度 5、查詢時避免使用like‘%string’,以免全表掃描,而like‘string%’則使用了對應(yīng)字段的索引。 6、定期重建索引,提高查詢速度 alterindexINDEX_NAMErebuild;保存SQL輸出到文本文件(以輸出到ls.txt為例)setserverouton spoolls.txtSQL語句Spooloff。表空間的管理1、查看所有表空間大小和使用率selecta.tablespace_name,sum(a.bytes)/1024/1024"totalsize(M)",round(10000*(sum(a.bytes)/1024/1024-sum(b.bytes)/1024/1024))/1000"usedsize(M)",round(10000*(sum(a.bytes)/1024/1024-sum(b.bytes)/1024/1024)/(sum(a.bytes)/1024/1024))/100"percent%"fromdba_data_filesa,(selecttablespace_name,file_id,sum(bytes)bytesfromdba_free_spacegroupbytablespace_name,file_id)bwherea.tablespace_name=b.tablespace_nameanda.file_id=b.file_idgroupbya.tablespace_name;2、使用文件系統(tǒng)的表空間擴展alterdatabaseTABLESPACE_NAMEadddatafileFILE_NAMEsizeSIZE;3、用裸設(shè)備表空間的擴展alterdatabaseTABLESPACE_NAMEadddatafileRAW_DEVICE_NAMEsizeSIZE;這里需要注意的是裸設(shè)備要提前建立后,且這里使用的是裸設(shè)備的字符文件(類型為C),SIZE值要比其實際大小要稍微小一些。4、集群中,在某一臺主機上共享卷組上增加文件系統(tǒng)和裸設(shè)備后,一定要同步到集群中另外的主機,否則當(dāng)數(shù)據(jù)庫在另外的主機中啟動時,會找不著相應(yīng)的設(shè)備號,導(dǎo)致數(shù)據(jù)庫不能夠被打開。5、查找表空間的剩余空間塊數(shù)以及最大空閑塊的大小,如果碎片過多或最大空閑塊過小,需要手工合并表空間碎片selecttablespace_name,count(*)"freeblocknumber",trunc(sum(bytes)/1024/1024)"totalfreesize(M)",trunc(max(bytes)/1024/1024)"maxblock(M)"fromdba_free_spacegroupbytablespace_name;6、手工合并某表空間碎片altertablespaceTABLESPACE_NAMEcoalesce;數(shù)據(jù)文件I/O的統(tǒng)計優(yōu)化col文件名formata35;select 文件名,fs.phyrds讀次數(shù),fs.phywrts寫次數(shù), (fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds))讀時間, (fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts))寫時間fromv$datafiledf,v$filestatfswheredf.file#=fs.file#orderby;錯誤號的跟蹤出理 1、在操作系統(tǒng)級顯示其錯誤詳細(xì)信息和?？捶Q辦法以報ora_4031錯誤為例) $>oerrora4031 2、有時為了進一步跟蹤其錯誤,讓其產(chǎn)生更詳細(xì)的告警日志在用戶報警文件里,需要在數(shù)據(jù)庫的初始化文件里加下面一句配置,并重新啟動數(shù)據(jù)庫event="4031tracenameerrorstacklevel3"這里注意的是,跟蹤完畢后,應(yīng)將此句話屏蔽掉,以免影響數(shù)據(jù)庫性能。 日常問題處理流程ORACLE數(shù)據(jù)庫日常檢查1、登錄到主機上利用sqlplus檢查是否可用;2、數(shù)據(jù)庫告警日志的檢查$ORACLE_HOME/admin/INSTANCE_NAME/bdump/alert_INSTANCE_NAME.log此文件中記載數(shù)據(jù)庫的啟動、停止、系統(tǒng)級修改、告警信息、日志切換,是日常維護中的重要依據(jù)。會話連接日志的清理會話連接日志$ORACLE_HOME/network/log/listener.log,此文件中記載著每個連接登錄時的ＩＰ地址和時間等情況,可作為監(jiān)測的依