Oracle數(shù)據(jù)庫(kù)日常檢查手冊(cè)

10Oracle10g數(shù)據(jù)庫(kù)日常維護(hù)手冊(cè)

密級(jí)：保密編號(hào)：日期：編寫：核對(duì)：更：目錄\l“_TOC_250058“檢查數(shù)據(jù)庫(kù)根本狀況 4\l“_TOC_250057“檢查數(shù)據(jù)庫(kù)版本 4\l“_TOC_250056“檢查ORACLE實(shí)例狀態(tài) 4\l“_TOC_250055“檢查ORACLE效勞進(jìn)程 5\l“_TOC_250054“檢查ORACLE監(jiān)聽(tīng)狀態(tài) 5\l“_TOC_250053“檢查系統(tǒng)和ORACLE日志文件 6\l“_TOC_250052“檢查操作系統(tǒng)日志文件 6\l“_TOC_250051“檢查ORACLE日志文件 6\l“_TOC_250050“檢查ORACLE核心轉(zhuǎn)儲(chǔ)名目 7\l“_TOC_250049“檢查ROOT用戶和ORACLE用戶的EMAIL 7\l“_TOC_250048“檢查ORACLE對(duì)象狀態(tài) 7\l“_TOC_250047“檢查ORACLE掌握文件狀態(tài) 8\l“_TOC_250046“檢查ORACLE在線日志狀態(tài) 8\l“_TOC_250045“檢查ORACLE表空間的狀態(tài) 8\l“_TOC_250044“檢查ORACLE全部數(shù)據(jù)文件狀態(tài) 9\l“_TOC_250043“檢查無(wú)效對(duì)象 10\l“_TOC_250042“檢查全部回滾段狀態(tài) 10\l“_TOC_250041“檢查ORACLE相關(guān)資源的使用狀況 10\l“_TOC_250040“檢查ORACLE初始化文件中相關(guān)參數(shù)值 11\l“_TOC_250039“檢查數(shù)據(jù)庫(kù)連接狀況 11\l“_TOC_250038“檢查系統(tǒng)磁盤空間 12\l“_TOC_250037“檢查表空間使用狀況 13\l“_TOC_250036“檢查一些擴(kuò)展特別的對(duì)象 13\l“_TOC_250035“檢查SYSTEM表空間內(nèi)的內(nèi)容 14\l“_TOC_250034“檢查對(duì)象的下一擴(kuò)展與表空間的最大擴(kuò)展值 14\l“_TOC_250033“檢查ORACLE數(shù)據(jù)庫(kù)備份結(jié)果 15\l“_TOC_250032“檢查數(shù)據(jù)庫(kù)備份日志信息 15\l“_TOC_250031“檢查BACKUP卷中文件產(chǎn)生的時(shí)間 15\l“_TOC_250030“檢查ORACLE用戶的EMAIL 15\l“_TOC_250029“檢查ORACLE數(shù)據(jù)庫(kù)性能 15\l“_TOC_250028“檢查數(shù)據(jù)庫(kù)的等待大事 15\l“_TOC_250027“DISKREAD最高的SQL語(yǔ)句的獵取 16\l“_TOC_250026“查找前十條性能差的SQL 16\l“_TOC_250025“等待時(shí)間最多的5個(gè)系統(tǒng)等待大事的獵取 16\l“_TOC_250024“檢查PID對(duì)應(yīng)的SQL語(yǔ)句 16\l“_TOC_250023“檢查運(yùn)行很久的SQL 16\l“_TOC_250022“檢查消耗CPU最高的進(jìn)程 17\l“_TOC_250021“檢查碎片程度高的表 17\l“_TOC_250020“檢查表空間的I/O比例 17\l“_TOC_250019“檢查文件系統(tǒng)的I/O比例 17\l“_TOC_250018“檢查死鎖及處理 18\l“_TOC_250017“檢查數(shù)據(jù)庫(kù)CPU、I/O、內(nèi)存性能 18\l“_TOC_250016“查看是否有僵死進(jìn)程 19\l“_TOC_250015“檢查行鏈接/遷移 19\l“_TOC_250014“定期做統(tǒng)計(jì)分析 20\l“_TOC_250013“檢查緩沖區(qū)命中率 20\l“_TOC_250012“檢查共享池命中率 20\l“_TOC_250011“檢查排序區(qū) 21\l“_TOC_250010“檢查日志緩沖區(qū) 21\l“_TOC_250009“檢查數(shù)據(jù)庫(kù)安全性 21\l“_TOC_250008“檢查系統(tǒng)安全日志信息 21\l“_TOC_250007“檢查用戶修改密碼 22\l“_TOC_250006“其他檢查 22\l“_TOC_250005“檢查當(dāng)前CRONTAB任務(wù)是否正常 22\l“_TOC_250004“ORACLEJOB是否有失敗 22\l“_TOC_250003“監(jiān)控?cái)?shù)據(jù)量的增長(zhǎng)狀況 23\l“_TOC_250002“檢查失效的索引 23\l“_TOC_250001“檢查不起作用的約束 24\l“_TOC_250000“檢查無(wú)效的TRIGGER 24巡檢內(nèi)容檢查數(shù)據(jù)庫(kù)根本狀況在本節(jié)中主要對(duì)數(shù)據(jù)庫(kù)的根本狀況進(jìn)展檢查，其中包含：檢查Oracle實(shí)例狀態(tài)，檢查Oracle效勞進(jìn)程，檢查Oracle監(jiān)聽(tīng)進(jìn)程，共三個(gè)局部。檢查數(shù)據(jù)庫(kù)版本SQL>selectSQL>select*fromv$version;BANNEROracleDatabase10gEnterpriseEditionRelease.0-64biPL/SQLRelease.0-ProductionCORE .0 ProductionTNSforLinux:Version.0-ProductionNLSRTLVersion.0-ProductionOracle實(shí)例狀態(tài)SQL>SQL>selectinstance_name,host_name,startup_time,status,database_statusfromv$instance;INSTANCE_NAME HOST_NAME STARTUP_TIME DATABASE_STATUSCKDBAS142009-5-79:3OPENACTIVE表示OracleOPE；表示Oracle當(dāng)前數(shù)據(jù)庫(kù)的狀態(tài)，必需為“ACTIVE”。NAMELOG_MODEOPEN_MODECKDBARCHIVELOGREADWRITESQL>selectname,log_mode,open_modefromv$database;其中“LOG_MODE”表示SQL>selectname,log_mode,open_modefromv$database;Oracle效勞進(jìn)程oracle296010May07?00:01:02ora_pmon_CKDBoracle296210May07?00:00:22ora_psp0_CKDBoracle296410May07?00:00:00ora_mman_CKDBoracle296610May07?00:03:20ora_dbw0_CKDBoracle296810May07?00:04:29ora_lgwr_CKDBoracle297010May07?00:10:31ora_ckpt_CKDBoracle297210May07?00:03:45ora_smon_CKDBoracle297410May07?00:00:00ora_reco_CKDBoracle297610May07?00:01:24ora_cjq0_CKDBoracle297810May07?00:06:17ora_mmon_CKDBoracle298010May07?00:07:26ora_mmnl_CKDBoracle298210May07?00:00:00ora_d000_CKDBoracle298410May07?00:00:00ora_s000_CKDBoracle299410May07?00:00:28ora_arc0_CKDBoracle299610May07?00:00:29ora_arc1_CKDBoracle300010May07?00:00:00ora_qmnc_CKDBoracle362510May07?00:01:40ora_q000_CKDBoracle3159410Jul20?00:00:00ora_q003_CKDBoracle238021005:09?00:00:33ora_j000_CKDB19$ps-ef|grepora_|grep$ps-ef|grepora_|grep-vgrep&&ps-ef|grepora_|grep-vgrep|wc-l.Oracle寫數(shù)據(jù)文件的進(jìn)程，輸出顯示為：“ora_dbw0_CKDB”.Oracle寫日志文件的進(jìn)程，輸出顯示為：“ora_lgwr_CKDB”.Oracle監(jiān)聽(tīng)實(shí)例狀態(tài)的進(jìn)程，輸出顯示為：“ora_smon_CKDB”.Oracle監(jiān)聽(tīng)客戶端連接進(jìn)程狀態(tài)的進(jìn)程，輸出顯示為：“ora_pmon_CKDB”.Oracle進(jìn)展歸檔的進(jìn)程，輸出顯示為：“ora_arc0_CKDB”.Oracle進(jìn)展檢查點(diǎn)的進(jìn)程，輸出顯示為：“ora_ckpt_CKDB”.Oracle進(jìn)展恢復(fù)的進(jìn)程，輸出顯示為：“ora_reco_CKDB”O(jiān)racle監(jiān)聽(tīng)狀態(tài)/home/oracle>lsnrctl/home/oracle>lsnrctlstatusLSNRCTLforLinux:Version.0-Productionon23-JUL-200914:11:53Copyright(c)1991,2005,Oracle. Allrightsreserved.Connectingto(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUSoftheLISTENERAliasLISTENERVersionVersionStartDateUptimeTraceLevelSecuritySNMPListenerParameterFileListenerLogFileTNSLSNRforLinux:Version.0-Production07-MAY-200909:35:5277days4hr.36min.0secoffON:LocalOSAuthenticationOFF/data/oracle/product/10.2.0/network/admin/listener.ora/data/oracle/product/10.2.0/network/log/listener.logListeningEndpointsSummary...(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AS14)(PORT=1521)))ServicesSummary...Service“CKDB“has1instance(s).Instance“CKDB“,statusREADY,has1handler(s)forthisservice...Service“CKDBXDB“has1instance(s).Instance“CKDB“,statusREADY,has1handler(s)forthisservice...Service“CKDB_XPT“has1instance(s).Instance“CKDB“,statusREADY,has1handler(s)forthisservice...Thecommandcompletedsuccessfully“ServicesSummary”項(xiàng)表示Oracle的監(jiān)聽(tīng)進(jìn)程正在監(jiān)聽(tīng)哪些數(shù)據(jù)庫(kù)實(shí)例，輸出顯示中至少應(yīng)當(dāng)有“CKDB”這一項(xiàng)。[oracle@AS14[oracle@AS14~]$ ps-ef|greplsn|grep-vgreporacle 2954 1 0May07? 00:01:17/data/oracle/product/10.2.0/bin/tnslsnrLISTENER–inherit檢查系統(tǒng)和oracle日志文件在本節(jié)主要檢查相關(guān)的日志文件，包含：檢查操作系統(tǒng)的日志文件，檢查Oracle日志文件，檢查Oracle核心轉(zhuǎn)儲(chǔ)名目，檢查Root用戶和Oracle用戶的email，總共四個(gè)局部。檢查操作系統(tǒng)日志文件##cat/var/log/messages|grepfailed查看是否有與Oracle用戶相關(guān)的出錯(cuò)信息。oracle日志文件[oracle@AS14[oracle@AS14~]$cat/data/oracle/admin/CKDB/bdump/alert_CKDB.log|grepora-[oracle@AS14~]$cat/data/oracle/admin/CKDB/bdump/alert_CKDB.log|greperr[oracle@AS14~]$cat/data/oracle/admin/CKDB/bdump/alert_CKDB.log|grepfailOracle(alert_SID.log)假設(shè)由于檢查點(diǎn)(checkpoint)操作沒(méi)有執(zhí)行完成造成不能切換，會(huì)記錄不能切換的緣由；對(duì)間不夠、消滅壞塊、數(shù)據(jù)庫(kù)內(nèi)部錯(cuò)誤(ORA－600)等。定期檢查日志文件，依據(jù)日志中發(fā)現(xiàn)的問(wèn)題準(zhǔn)時(shí)進(jìn)展處理：?jiǎn)栴}啟動(dòng)參數(shù)不對(duì)由于檢查點(diǎn)操作或歸檔操作沒(méi)有完成造成重做日志不能切換有人未經(jīng)授權(quán)刪除了表空間消滅壞塊表空間不夠消滅ORA-600

處理檢查初始化參數(shù)文件假設(shè)常常發(fā)生這樣的狀況，可以考慮增加重做日志文件組；想方法提高檢查點(diǎn)或歸檔操作的效率；檢查數(shù)據(jù)庫(kù)的安全問(wèn)題，是否密碼太簡(jiǎn)潔；如有必要，撤消某些用戶的系統(tǒng)權(quán)限檢查是否是硬件問(wèn)題(如磁盤本生有壞塊)，假設(shè)不是，檢查是那個(gè)數(shù)據(jù)庫(kù)對(duì)象消滅了壞塊，對(duì)這個(gè)對(duì)象進(jìn)展重建增加數(shù)據(jù)文件到相應(yīng)的表空間依據(jù)日志文件的內(nèi)容查看相應(yīng)的TRC文件，假設(shè)Oraclebug，要準(zhǔn)時(shí)打上相應(yīng)的補(bǔ)丁Listener日志：$ORACLE_HOME/network/log檢查Oracle核心轉(zhuǎn)儲(chǔ)名目$ls$ls$ORACLE_BASE/admin/CKDB/cdump/*.trc|wc-l$ls$ORACLE_BASE/admin/CKDB/udump/*.trc|wc–l假設(shè)上面命令的結(jié)果每天都在增長(zhǎng)，則說(shuō)明Oracle進(jìn)程常常發(fā)生核心轉(zhuǎn)儲(chǔ)。這說(shuō)明某些用戶進(jìn)程或者數(shù)據(jù)庫(kù)后臺(tái)進(jìn)程由于無(wú)法處理的緣由而特別退出后臺(tái)進(jìn)程的核心轉(zhuǎn)儲(chǔ)會(huì)導(dǎo)致數(shù)據(jù)庫(kù)特別終止。RootOracleemail#tail#tail–n200/var/mail/root#tail–n200/var/mail/oracle查看有無(wú)與Oracle用戶相關(guān)的出錯(cuò)信息。Oracle對(duì)象狀態(tài)在本節(jié)主要檢查相關(guān)OracleOracleOracle在線日志狀態(tài)，檢查Oracle表空間的狀態(tài)，檢查Oracle全部數(shù)據(jù)文件狀態(tài)，檢查Oracle全部表、索引、存儲(chǔ)過(guò)程、觸發(fā)器、包等對(duì)象的狀態(tài)，檢查Oracle全部回滾段的狀態(tài)，總共六個(gè)局部。檢查Oracle掌握文件狀態(tài)SQL>SQL>selectstatus,namefromv$controlfile;STATUS NAME/data/oradata/CKDB/control01.ctl/data/oradata/CKDB/control02.ctl/data/oradata/CKDB/control03.ctl3條以上〔3條〕的記錄，“STATUS”應(yīng)當(dāng)為空。狀態(tài)為空表示掌握文件狀態(tài)正常。檢查Oracle在線日志狀態(tài)3ONLINE/data/oradata/CKDB/redo03.log2ONLINE/data/oradata/CKDB/redo02.log1ONLINE/data/oradata/CKDB/redo01.log4ONLINE/data/oradata/CKDB/redo04.log5ONLINE/data/oradata/CKDB/redo05.log6ONLINE/data/oradata/CKDB/redo06.logSQL>selectgroup#,status,type,memberfromv$logfile;GROUP#STATUS TYPEMEMBER6rowsselected輸出結(jié)果應(yīng)當(dāng)有3〔包含3條SQL>selectgroup#,status,type,memberfromv$logfile;GROUP#STATUS TYPEMEMBER6rowsselected檢查Oracle表空間的狀態(tài)SQL>SQL>selecttablespace_name,statusfromdba_tablespaces;TABLESPACE_NAMESTATUSSYSTEMUNDOTBS1SYSAUXTEMPUSERSSJ1ADM_INDEXHOME_DATAONLINEONLINEONLINEONLINEONLINEONLINEONLINEONLINEHOME_INDEXHOME_INDEXPHOTO_DATAPHOTO_INDEX。。。。ONLINEONLINEONLINE輸出結(jié)果中STATUS應(yīng)當(dāng)都為ONLINE。檢查Oracle全部數(shù)據(jù)文件狀態(tài)SQL>selectname,statusfromv$datafile;NAME STATUS/data/oradata/CKDB/system01.dbf SYSTEM/data/oradata/CKDB/undotbs01.dbf ONLINE/data/oradata/CKDB/sysaux01.dbf ONLINE/data/oradata/CKDB/users01.dbf ONLINE/data/oradata/CKDB/sj.dbf ONLINE/data/oradata/CKDB/HOME_DATA1.dbf ONLINE/data/oradata/CKDB/HOME_INDEX1.dbf ONLINE/data/oradata/CKDB/PHOTO_DATA1.dbf ONLINE/data/oradata/CKDB/PHOTO_INDEX1.dbf ONLINE/data/oradata/CKDB/BLOG_DATA1.dbf ONLINE/data/oradata/CKDB/BLOG_INDEX1.dbf ONLINE/data/oradata/CKDB/AUDIO_DATA1.dbf ONLINE/data/oradata/CKDB/AUDIO_INDEX1.dbf ONLINE/data/oradata/CKDB/VIDEO_DATA1.dbf ONLINE/data/oradata/CKDB/VIDEO_INDEX1.dbf ONLINE/data/oradata/CKDB/SYS_DATA1.dbf ONLINE/data/oradata/CKDB/SYS_INDEX1.dbf ONLINE/data/oradata/CKDB/ADM_DATA1.dbf ONLINE/data/oradata/CKDB/ADM_INDEX1.dbf ONLINE/data/oradata/CKDB/perfstat.dbf ONLINESQL>selectfile_name,statusSQL>selectfile_name,statusfromdba_data_files;FILE_NAMESTATUS/data/oradata/CKDB/users01.dbf/data/oradata/CKDB/sysaux01.dbf/data/oradata/CKDB/undotbs01.dbf/data/oradata/CKDB/system01.dbf/data/oradata/CKDB/sj.dbf/data/oradata/CKDB/perfstat.dbf/data/oradata/CKDB/HOME_DATA1.dbfAVAILABLEAVAILABLE/data/oradata/CKDB/HOME_INDEX1.dbf/data/oradata/CKDB/HOME_INDEX1.dbf/data/oradata/CKDB/PHOTO_DATA1.dbfAVAILABLE檢查無(wú)效對(duì)象sql>select owner,object_name,object_type from dba_objects where status!=”VALID” andsql>select owner,object_name,object_type from dba_objects where status!=”VALID” andowner!=”SYS”andowner!=”SYSTEM”;norowsselected個(gè)對(duì)象，或者：SELECTowner,SELECTowner,object_name,object_typeFROMdba_objectsWHEREstatus=”INALID”;SQL>SQL>selectsegment_name,statusfromdba_rollback_segs;SEGMENT_NAMESYSTEM_SYSSMU1$_SYSSMU2$_SYSSMU3$_SYSSMU4$_SYSSMU5$_SYSSMU6$_SYSSMU7$_SYSSMU8$_SYSSMU9$_SYSSMU10$ONLINEONLINEONLINEONLINEONLINEONLINEONLINEONLINEONLINEONLINEONLINE11rowsselected輸出結(jié)果中全部回滾段的“STATUS”應(yīng)當(dāng)為“ONLINE”。Oracle相關(guān)資源的使用狀況在本節(jié)主要檢查Oracle相關(guān)資源的使用狀況，包含：檢查Oracle初始化文件中相關(guān)的參數(shù)值，檢查數(shù)據(jù)庫(kù)連接狀況，檢查系統(tǒng)磁盤空間，檢查Oracle各個(gè)表空間使用狀況，檢查一些擴(kuò)展特別的對(duì)象，檢查system表空間內(nèi)的內(nèi)容，檢查對(duì)象的下一擴(kuò)展與表空間的最大擴(kuò)展值，總共七個(gè)局部。檢查Oracle初始化文件中相關(guān)參數(shù)值processes162500500sessions168555555enqueue_locknqueue_resources1112660UNLIMITEDges_procs000ges_ress00UNLIMITEDges_locks00UNLIMITEDges_cache_ress00UNLIMITEDges_reg_msgs00UNLIMITEDges_big_msgs00UNLIMITEDges_rsv_msgs000gcs_resources000gcs_shadows000dml_locks762440UNLIMITEDtemporary_table_locks26UNLIMITEDUNLIMITEDtransactions13610UNLIMITEDbranches0610UNLIMITEDcmtcallbk3610UNLIMITEDsort_segment_locks5UNLIMITEDUNLIMITEDmax_rollback_segments1161065535max_shared_servers1UNLIMITEDUNLIMITEDparallel_max_servers16803600SQL>selectresource_name,max_utilization,initial_allocation,limit_valuefromv$resource_limit;RESOURCE_NAMEMAX_UTILIZATIONINITIAL_ALLOCATLIMIT_VALUERESOURCE_NAMEMAX_UTILIZATIONINITIAL_ALLOCATLIMIT_VALUE22rowsselected假設(shè)LIMIT_VALU-MAX_UTILIZATION<=5，則說(shuō)明與RESOURCE_NAME相關(guān)的Oracle初始化參數(shù)需要調(diào)整。SQL>selectresource_name,max_utilization,initial_allocation,limit_valuefromv$resource_limit;RESOURCE_NAMEMAX_UTILIZATIONINITIAL_ALLOCATLIMIT_VALUERESOURCE_NAMEMAX_UTILIZATIONINITIAL_ALLOCATLIMIT_VALUE22rowsselected$ORACLE_BASE/admin/CKDB/pfile/initORCL.ora來(lái)修改。檢查數(shù)據(jù)庫(kù)連接狀況SQL>SQL>selectcount(*)fromv$session;COUNT(*)292913oracle@xz15saledb(PMON)xz15saledbACTIVE23oracle@xz15saledb(DBW0)xz15saledbACTIVE33oracle@xz15saledb(DBW1)xz15saledbACTIVE43oracle@xz15saledb(LGWR)xz15saledbACTIVE53oracle@xz15saledb(CKPT)xz15saledbACTIVE63oracle@xz15saledb(SMON)xz15saledbACTIVE73oracle@xz15saledb(RECO)xz15saledbACTIVE81oracle@xz15saledb(CJQ0)xz15saledbACTIVE93oracle@xz15saledb(ARC0)xz15saledbACTIVE103oracle@xz15saledb(ARC1)xz15saledbACTIVE1111319ZKAccPrtInv_svr@xz15tuxedo2 (TNSV1-V3)xz15tuxedo2selectsid,serial#,username,program,machine,statusfromv$session;SID SERIAL# USERNAME PROGRAM MACHINESTATUSINACTIVEselectsid,serial#,username,program,machine,statusfromv$session;SID SERIAL# USERNAME PROGRAM MACHINESTATUSINACTIVE13 48876ZGupload@xz15saleap (TNS V1-V3)xz15saleapINACTIVE17 20405 ZK AccCreateRpt@xz15tuxedo1 (TNS V1-V3) xz15tuxedo1INACTIVE20 12895 ZK OweScanSvr@xz15billdb (TNS V1-V3) xz15billdbINACTIVESERIAL# 會(huì)話的序列號(hào)，和SID一起用來(lái)唯一標(biāo)識(shí)一個(gè)會(huì)話；USERNAME 建立該會(huì)話的用戶名；PROGRAM 這個(gè)會(huì)話是用什么工具連接到數(shù)據(jù)庫(kù)的；STATUS 當(dāng)前這個(gè)會(huì)話的狀態(tài)，ACTIVE表示會(huì)話正在執(zhí)行某些任務(wù)，INACTIVE表示當(dāng)前會(huì)話沒(méi)有執(zhí)行任何操作；altersystemkillsession”SID,SERIAL#”;假設(shè)建立了過(guò)多的連接，會(huì)消耗數(shù)據(jù)庫(kù)的資源，同時(shí)，對(duì)一些“掛死”的連接可能需要手工進(jìn)展清理。假設(shè)DBA要手工斷開(kāi)某個(gè)會(huì)話，則執(zhí)行〔一般不建議使用這種方式去殺掉數(shù)sessionsid查到操作系統(tǒng)的spid使用psef|grepspidno的方式確認(rèn)spid不是ORACLEaltersystemkillsession”SID,SERIAL#”;SID110(USERNAME列為空)Oracle的后臺(tái)進(jìn)程，不要對(duì)這些會(huì)話進(jìn)展任何操作。檢查系統(tǒng)磁盤空間[oracle@AS14[oracle@AS14~]$df-hFilesystemFilesystem/dev/sda5/dev/sda1/dev/sda2noneSize UsedAvailUse%Mountedon9.7G479M49G3.9G16M19G5.4G42%/438M28G1014M01014M4%/boot41%/data0%/dev/shm檢查表空間使用狀況OPERATION_DATA180054730WAPWEB_DATA1003636OPERATION_INDEX50018637SYSTEM102451550SYSAUX102453452SALE8_TEMP1006262SJ150034870PERFSTAT……….50035671HOME_DATA1007777SYS_INDEX100100100VIDEO_INDEX100100100VIDEO_DATA100100100BLOG_DATA100100100SQL>selectf.tablespace_name,a.total,f.free,round((f.free/a.total)*100)“%Free“from(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group bytablespace_name)a,(selecttablespace_name,round(sum(bytes/(1024*1024)))freeSQL>selectf.tablespace_name,a.total,f.free,round((f.free/a.total)*100)“%Free“from(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group bytablespace_name)a,(selecttablespace_name,round(sum(bytes/(1024*1024)))freefromdba_free_spacegroupbytablespace_name)fWHEREa.tablespace_name=f.tablespace_name(+)orderby“%Free“;TABLESPACE_NAMETOTALFREE%Free39rowsselected檢查一些擴(kuò)展特別的對(duì)象sql>selectsql>selectSegment_Name,Segment_Type,TableSpace_Name,(Extents/Max_extents)*100PercentFromFromsys.DBA_SegmentsWhereMax_Extents!=0and(Extents/Max_extents)*100>=95orderByPercent;norowsselected改它的存儲(chǔ)構(gòu)造參數(shù)。檢查system表空間內(nèi)的內(nèi)容selectselectdistinct(owner)fromdba_tableswheretablespace_name=”SYSTEM”andowner!=”SYS”andowner!=”SYSTEM”unionselectdistinct(owner)fromdba_indexeswheretablespace_name=”SYSTEM”andowner!=”SYS”andowner!=”SYSTEM”;norowsselected假設(shè)記錄返回，則說(shuō)明systemsystemsys用戶的對(duì)象。應(yīng)當(dāng)進(jìn)一步檢查這些對(duì)象是否與我們應(yīng)用相關(guān)。假設(shè)相關(guān)請(qǐng)把這些對(duì)象移到非System表空間，同時(shí)應(yīng)當(dāng)檢查這些對(duì)象屬主的缺省表空間值。檢查對(duì)象的下一擴(kuò)展與表空間的最大擴(kuò)展值sql>selectsql>selecta.table_name,a.next_extent,a.tablespace_namefromall_tablesa,(selecttablespace_name,max(bytes)asbig_chunkfromdba_free_spacegroupbytablespace_name)fwheref.tablespace_name=a.tablespace_nameanda.next_extent>f.big_chunkunionselecta.index_name,a.next_extent,a.tablespace_namefromall_indexesa,(selecttablespace_name,max(bytes)asbig_chunkfromdba_free_spacegroupbytablespace_name)fwheref.tablespace_name=a.tablespace_nameanda.next_extent>f.big_chunk;norowsselected整相應(yīng)表空間的存儲(chǔ)參數(shù)。Oracle數(shù)據(jù)庫(kù)備份結(jié)果在本節(jié)主要檢查Oraclebackup卷中文件產(chǎn)生的時(shí)間，檢查oracle用戶的email，總共三個(gè)局部。檢查數(shù)據(jù)庫(kù)備份日志信息#cat/backup/hotbackup/hotbackup-09-7-22.log|grep–ierror假設(shè)：備份的臨時(shí)名目為/backup/hotbakup2009722#cat/backup/hotbackup/hotbackup-09-7-22.log|grep–ierror備份腳本的日志文件為hotbackup-月份-日期-年份.log存在“ERROR:”，則說(shuō)明備份沒(méi)有成功，存在問(wèn)題需要檢查。backup卷中文件產(chǎn)生的時(shí)間#ls#ls–lt/backup/hotbackupbackup卷是備份的臨時(shí)名目，查看輸出結(jié)果中文件的日期，都應(yīng)當(dāng)是在當(dāng)天凌晨由熱備份腳本產(chǎn)生的。假設(shè)時(shí)間不對(duì)則說(shuō)明熱備份腳本沒(méi)執(zhí)行成功。oracleemail#tail#tail–n300/var/mail/oracleOraclecron去執(zhí)行的。cronEmail通知OracleOracleemail中今日凌晨局部有無(wú)ORA-，Error，F(xiàn)ailed等出錯(cuò)信息，假設(shè)有則說(shuō)明備份不正常。Oracle數(shù)據(jù)庫(kù)性能在本節(jié)主要檢查Oracle數(shù)據(jù)庫(kù)性能狀況，包含：檢查數(shù)據(jù)庫(kù)的等待大事，檢查死鎖及處理，檢查cpu、I/O、內(nèi)存性能，查看是否有僵死進(jìn)程，檢查行鏈接/遷移，定期做統(tǒng)計(jì)分檢查數(shù)據(jù)庫(kù)的等待大事setsets80setlines120coleventfora40selectselectsid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAITfromv$session_waitwhereeventnotlike”SQL%”andeventnotlike”rdbms%”;假設(shè)數(shù)據(jù)庫(kù)長(zhǎng)時(shí)間持續(xù)消滅大量像latchfree，enqueue，bufferbusywaits，dbfilesequentialread，dbfilescatteredread等等待大事時(shí)，需要對(duì)其進(jìn)展分析，可能存在問(wèn)題的語(yǔ)句。DiskReadSQL語(yǔ)句的獵取SQL>SELECT SQL_TEXT FROMSQL>SELECT SQL_TEXT FROMDISK_READS)WHEREROWNUM<=5desc;(SELECT* FROM V$SQLAREA ORDER BY查找前十條性能差的sqlSELECTSELECT*FROM(SELECTPARSING_USER_IDEXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXTFROMV$SQLAREAORDERBYDISK_READSDESC)WHEREROWNUM<10;等待時(shí)間最多的5個(gè)系統(tǒng)等待大事的獵取SELECTSELECT*FROM(SELECT*FROMV$SYSTEM_EVENTWHEREEVENTNOTLIKE”SQL%”O(jiān)RDERBYTOTAL_WAITSDESC)WHEREROWNUM<=5;PIDSQL語(yǔ)句selectselect/*+ORDERED*/sql_textFROMv$sqltextawhere(a.hash_value,a.address)IN(selectDECODE(sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr,sql_address)FROMv$sessionbWHEREb.paddr=(selectaddrfromv$processcWHEREc.spid=”PID”))orderbypieceASC;top(linux)topas(AIX)查看占用CPU較多的oracle的PIDSQLCOLUMNCOLUMNUSERNAMEFORMATA12COLUMNOPNAMEFORMATA16COLUMNPROGRESSFORMATA8SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100/ TOTALWORK,0)|| ”%” ASPROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQLWHEREWHERETIME_REMAINING<>0ANDSQL_ADDRESS=ADDRESSANDSQL_HASH_VALUE=HASH_VALUE;CPU最高的進(jìn)程SETSETLINE240SETVERIFYOFFCOLUMNSIDFORMAT999COLUMNPIDFORMAT999COLUMNS_#FORMAT999COLUMNUSERNAMEFORMATA9HEADING“ORAUSER“COLUMNPROGRAMFORMATA29COLUMNSQL FORMATA60COLUMNOSNAMEFORMATA9HEADING“OSUSER“SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSEROSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAMPROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT,1,80))SQLFROMV$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDRANDS.SQL_ADDRESS=A.ADDRESS(+)ANDP.SPIDLIKE”%&1%”;檢查碎片程度高的表SQL>SQL>SELECTsegment_nametable_name,COUNT(*)extentsFROMdba_segmentsWHEREownerNOTIN(”SYS”,”SYSTEM”)GROUPBYsegment_nameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMdba_segmentsGROUPBYsegment_name);I/O比例SQL>SELECTSQL>SELECTDF.TABLESPACE_NAMENAME,DF.FILE_NAME“FILE“,F.PHYRDSPYR,F.PHYBLKRDPBR,F.PHYWRTSPYW,F.PHYBLKWRTPBWFROMV$FILESTATF,DBA_DATA_FILESDFWHEREF.FILE#=DF.FILE_IDORDERBYDF.TABLESPACE_NAME;檢查文件系統(tǒng)的I/O比例SQL>SELECTSQL>SELECTSUBSTR(A.FILE#,1,2)“#“,SUBSTR(A.NAME,1,30)“NAME“,A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTSFROM V$DATAFILEA, V$FILESTATBWHEREA.FILE#=B.FILE#;檢查死鎖及處理colcolsidfor999999colusernamefora10colschemanamefora10colosuserfora16colmachinefora16colterminalfora20colownerfora10colobject_namefora30colobject_typefora10selectsid,serial#,username,SCHEMANAME,osuser,MACHINE,terminal,PROGRAM,owner,object_name,object_type,o.object_idfromdba_objectso,v$locked_objectl,v$sessionswhereo.object_id=l.object_idands.sid=l.session_id;oraclekill掉該session：alteraltersystemkillsession”&sid,&serial#”;#>kill-9pid操作系統(tǒng)級(jí)kill#>kill-9pidcpu、I/O、內(nèi)存性能記錄數(shù)據(jù)庫(kù)的cpu使用、IO、內(nèi)存等使用狀況，使用vmstat,iostat,sar,top等命令進(jìn)展信息收集并檢查這些信息，推斷資源使用狀況。[root@sale8~]#[root@sale8~]#toptop-10:29:35up73days,19:54, 1user, loadaverage:0.37,0.38,0.29Tasks:353total, 2running,351sleeping, 0stopped, 0zombieCpu(s):Mem:Swap:1.2%us,0.1%sy, 0.0%ni,98.8%id, 0.0%wa, 0.0%hi, 0.0%si16404472ktotal,12887428kused,3517044kfree,60796kbuffers8385920ktotal,665576kused,7720344kfree,10358384kcachedPIDUSERPR NI VIRT RES SHRS%CPU%MEMTIME+ COMMAND30495oracle1508329m866m861mR105.47:53.90oracle32501oracle1508328m1.7g1.7gS210.61:58.38oracle32503oracle1508329m1.6g1.6gS210.22:06.62oracle。。。。留意上面的藍(lán)色字體局部，此局部?jī)?nèi)容表示系統(tǒng)剩余的cpu，當(dāng)其平均值下降至10%以下的時(shí)視為CPU使用率特別，需記錄下該數(shù)值，并將狀態(tài)記為特別。內(nèi)存使用狀況：#free-mtotalusedfreesharedbufferscachedMem: 2026195867076 1556-/+buffers/cache:3261700Swap: 599292590010%時(shí)視為特別。#iostat-k#iostat-k13Linux2.6.9-22.ELsmp(AS14)07/29/2009avg-cpu:%user0.16%nice0.00%sys%iowait0.05 0.36%idle99.43Device:tpskB_read/skB_wrtn/skB_readkB_wrtnsda3.3313.1650.2594483478360665804avg-cpu:%user0.00%nice0.00%sys%iowait0.00 0.00%idle100.00Device:sdatps0.00kB_read/s0.00kB_wrtn/s0.00kB_read0kB_wrtn0如上所示，藍(lán)色字體局部表示磁盤讀寫狀況，紅色字體局部為cpuIO等待狀況。#uptime12:08:37#uptime12:08:37up162days,23:33,15users, loadaverage:0.01,0.15,0.10如上所示，藍(lán)體字局部表示系統(tǒng)負(fù)載，后面的32.5的時(shí)候就說(shuō)明系統(tǒng)在超負(fù)荷運(yùn)轉(zhuǎn)了，并將此值記錄到巡檢表，視為特別。查看是否有僵死進(jìn)程selectselectspidfromv$processwhereaddrnotin(selectpaddrfromv$session);有些僵尸進(jìn)程有堵塞其他業(yè)務(wù)的正常運(yùn)行，定期殺掉僵尸進(jìn)程。檢查行鏈接/遷移Sql>select table_name,num_rows,chain_cnt From dba_tables Where owner=”CTAIS2”Sql>select table_name,num_rows,chain_cnt From dba_tables Where owner=”CTAIS2”Andchain_cnt<>0;注：含有l(wèi)ongraw列的表有行鏈接是正常的,找到遷移行保存到chained_rows表中,如沒(méi)有該表執(zhí)行../rdbms/admin/utlchain.sqlSql>analyzetabletablenamelistchainedrows;可通過(guò)表chained_rows中table_name,head_rowid看出哪些行是遷移行如:Sql>createtableaaasselecta.*fromsb_zsxxa,chained_rowsbwherea.rowid=b.head_rowidandb.table_name=”SB_ZSXX”;sql>deletefromsb_zsxxwhererowidin(selecthead_rowidfromchained_rowswheretable_name=”SB_ZSXX”);sql>insertintosb_zsxxselect*fromchained_rowwheretable_name=”SB_ZSXX”;定期做統(tǒng)計(jì)分析對(duì)于承受OracleCost-Based-Optimizer的系統(tǒng)，需要定期對(duì)數(shù)據(jù)對(duì)象的統(tǒng)計(jì)信息進(jìn)展采集更，使優(yōu)化器可以依據(jù)預(yù)備的信息作出正確的explainplan。在以下?tīng)顩r更需要進(jìn)展統(tǒng)計(jì)信息的更：1、應(yīng)用發(fā)生變化2、大規(guī)模數(shù)據(jù)遷移、歷史數(shù)據(jù)遷出、其他數(shù)據(jù)的導(dǎo)入等3、數(shù)據(jù)量發(fā)生變化查看表或索引的統(tǒng)計(jì)信息是否需更，如：Sql>Select table_name,num_rows,last_analyzed From user_tables where table_name=”DJ_NSRXX”sql>selectcount(*)fromDJ_NSRXX如num_rowscount(*)假設(shè)行數(shù)相差很多,則該表需要更統(tǒng)計(jì)信息，建議一周做一次統(tǒng)計(jì)信息收集，如：Sql>execsys.dbms_stats.gather_schema_stats(ownname=>”CTAIS2”,cascade=>TRUE,degree=>4);檢查緩沖區(qū)命中率SQL>SQL>SELECTa.VALUE+b.VALUElogical_reads,c.VALUEphys_reads,round(100*(1-c.value/(a.value+b.value)),4)hit_ratioFROMv$sysstata,v$sysstatb,v$sysstatcWHEREa.NAME=”dbblockgets”ANDb.NAME=”consistentgets”ANDc.NAME=”physicalreads”;LOGICAL_READSPHYS_READS HIT_RATIO12736457057119143094.410490%則需加大數(shù)據(jù)庫(kù)參數(shù)db_cache_size。檢查共享池命中率SQL>SQL>selectsum(pinhits)/sum(pins)*100fromv$librarycache;SUM(PINHITS)/SUM(PINS)*100SUM(PINHITS)/SUM(PINS)*10099.5294474716798如低于95%，則需要調(diào)整應(yīng)用程序使用綁定變量，或者調(diào)整數(shù)據(jù)庫(kù)參數(shù)sharedpool的大小。檢查排序區(qū)SQL>SQL>selectname,valuefromv$sysstatwherenamelike”%sort%”;NAMEsorts(memory)sorts(disk)sorts(rows)613553482264742084假設(shè)disk/(memoty+row)的比例過(guò)高，則需要調(diào)整sort_area_size(workarea_size_policy=false)pga_aggregate_target(workarea_size_policy=true)。檢查日志緩沖區(qū)SQL>SQL>selectname,valuefromv$sysstatwherenamein(”redoentries”,”redobufferallocationretries”);NAMEredoentriesredobufferallocationretries27663705880redobufferallocationretries/redoentries1%，則需要增大log_buffer。檢查數(shù)據(jù)庫(kù)安全性在本節(jié)主要檢查Oracle數(shù)據(jù)庫(kù)的安全性，包含：檢查系統(tǒng)安全信息，定期修改密碼，總共兩個(gè)局部。檢查系統(tǒng)安全日志信息[root@rac2~]#grep[root@rac2~]#grep-iaccepted/var/log/secureJan 808:44:43rac2sshd[29559]:Acceptedpasswordforrootfrom::ffff:port1119ssh2……[root@rac2[root@rac2~]#grep-iinval/var/log/secure&&grep-ifailed/var/log/secureJan 910:30:44rac2sshd[3071]:Invaliduserydbuserfrom::ffff:Jan 910:30:56rac2sshd[3071]:Failedpas