还剩7页未读,继续阅读
文本内容:
监控Oracle数据库的常用shell脚本一.脚本来监控Oracle数据库
1.检查实例的可用性
2.检查监听器的可用性
3.检查alert日志文件中的错误信息
4.在存放log文件的地方满以前清空旧的log文件
5.分析table和index以获得更好的性能
6.检查表空间的使用情况
7.找出无效的对象
8.监控用户和事务二.DBA需要的Unix基本知识基本的UNIX命令,以下是一些常用的Unix命令ps--显示进程grep--搜索文件中的某种文本模式__ilx--读取或者发送__ilcat--连接文件或者显示它们cut--选择显示的列awk--模式匹配语言df--显示剩余的磁盘空间以下是DBA如何使用这些命令的一些例子
1.显示服务器上的可用实例$ps–ef|grep__onoracle220861002:32:240:04ora___on_PPRD10oracle52152__72008:10:19pts/40:00grep__on
2.显示服务器上的可用监听器$ps-ef|greplistenergrep-vgrepgrep命令应该加上-i参数,即grep-ilistener,该参数的作用是忽略大小写,因为有些时候listener是大写的,这时就会看不到结果$ps–ef|grep-ilisteneroracle965510__r120:01/data/app/oracle/
9.
2.0/bin/tnslsnrLISTENER-inheritoracle226101002:45:020:02/data/app/oracle/
10.
2.0/bin/tnslsnrLISTENER-inheritoracle52682__72008:13:02pts/40:00grep-ilistener
3.查看Oracle存档目录的文件系统使用情况$df-k|grep/data/dev/md/dsk/d50104977675886105421531735786%/data
4.统计alter.log文件中的行数$catalert_PPRD
10.log|wc-l13124$morealert_PPRD
10.log|wc-l
131245.列出alert.log文件中的全部Oracle错误信息$grepORA-*alert.logORA-00600:internalerrorcodearguments:[kcrrrfswda.1][][][][][]ORA-00600:internalerrorcodearguments:
[1881][2___0496]
[25857716][]
6.CRONTAB基本一个crontab文件中包含有六个字段分钟0-59小时0-23月中的第几天1-31月份1-12星期几0-6with0=Sunday
7.Unix命令或者Shell脚本要编辑一个crontab文件,输入Crontab-e要查看一个crontab文件,输入Crontab-l04**5/dba/admin/____yze_table.ksh303**36/dba/admin/hotbackup.ksh/dev/null21在上面的例子中,第一行显示了一个分析表的脚本在每个星期5的400am运行第二行显示了一个执行热备份的脚本在每个周三和周六的300a.m.运行三.监控数据库的常用Shell脚本以下提供的8个shell脚本覆盖了DBA每日监控工作的90%,你可能还需要修改UNIX的环境变量
1.检查Oracle实例的可用性oratab文件中列出了服务器上的所有数据库$cat/var/opt/oracle/oratab##ThisfileisusedbyORACLEutilities.Itiscreatedbyroot.sh#andupdatedbytheDatabaseConfigurationAssistantwhencreating#adatabase.#Acolon:isusedasthefieldterminator.Anewlineterminates#theentry.Linesbeginningwithapoundsign#arecomments.##Entriesareoftheform:#$ORACLE_SID:$ORACLE_HOME::##Thefirstandsecondfieldsarethesystemidentifierandhome#directoryofthedatabaserespectively.Thethirdfiledindicates#tothedbstartutilitythatthedatabaseshouldYorshouldnot#Nbebroughtupatsystemboottime.##Multipleentrieswiththesame$ORACLE_SIDarenotallowed.###*:/data/app/oracle/
9.
2.0:NTRNG:/data/app/oracle/
9.
2.0:Y*:/data/app/oracle/
9.
2.0:NPPRD:/data/app/oracle/
10.
2.0:YPPRD10:/data/app/oracle/
10.
2.0:N以下的脚本检查oratab文件中列出的所有数据库,并且找出该数据库的状态启动还是关闭#####################################################################ckinstan__.ksh#####################################################################ORATAB=/var/opt/oracle/oratabecho`date`echoOracleDatabasesStatus`hostname`:/ndb=`egrep-i:Y|:N$ORATAB|cut-d:-f1|grep-v/#|grep-v/*`pslist=`ps-ef|greppmon`foriin$db;doecho$pslist|grepora_pmon_$i/dev/null2$1if$;thenechoOracleInstan__-$i:DownelseechoOracleInstan__-$i:Upfidone使用以下的命令来确认该脚本是可以执行的$chmod744ckinstan__.ksh$ls-lckinstan__.ksh-rwxr--r--1oracledba657__r522:59ckinstan__.ksh以下是实例可用性的报表$shckinstan__.kshWed__y1312:51:20PDT2009OracleDatabasesStatusgambels:OracleInstan__-PPRD:UpOracleInstan__-PPRD10:Up
2.检查Oracle监听器的可用性以下有一个类似的脚本检查Oracle监听器假如监听器停了,该脚本将会重新启动监听器#######################################################################cklsnr.sh########################################################################!/bin/kshTNS_ADMIN=/var/opt/oracle;exportTNS_ADMINORACLE_SID=PPRD10;exportORACLE_SIDORAENV_ASK=NO;exportORAENV_ASKPATH=$PATH:/bin:/usr/local/bin;exportPATH.oraenvDBALIST=tianlesoftware@vip.__.comtianlesoftware@hot__il.com;exportDBALISTcd/var/opt/oraclerm-flsnr.existps-ef|grepPPRD10|grep-vgreplsnr.existif[-slsnr.exist]thenechoelseechoAlert|__ilx-sListenerPPRD10on`hostname`isdown$DBALISTlsnrctlstartPPRD10fi
3.检查Alert日志ORA-XXXXX######################################################################ckalertlog.sh#######################################################################!/bin/kshEDITOR=vi;exportEDITORORACLE_SID=PPRD10;exportORACLE_SIDORACLE_BASE=/data/app/oracle;exportORACLE_BASEORACLE_HOME=$ORACLE_BASE/
10.
2.0;exportORACLE_HOMELD_LIBRARY_PATH=$ORACLE_HOME/lib;exportLD_LIBRARY_PATHTNS_ADMIN=/var/opt/oracle;exportTNS_ADMINNLS_LANG=american;exportNLS_LANGNLS_DATE_FOR__T=MonDDYYYYHH24:MI:SS;exportNLS_DATE_FOR__TORATAB=/var/opt/oracle/oratab;exportORATABPATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/__in:/__in:/usr/openwin/bin:/opt/bin:.;exportPATHDBALIST=tianlesoftware@vip.__.comtianlesoftware@hot__il.com;exportDBALISTcd$ORACLE_BASE/admin/PPRD10/bdumpif[-falert_PPRD
10.log]thenmvalert_PPRD
10.logalert_work.logtouchalert_PPRD
10.logcatalert_work.logalert_PPRD
10.histgrepORA-alert_work.logalert.errfiif[`catalert.err|wc-l`-gt0]then__ilx-sPPRD10ORACLEALERTERRORS$DBALISTalert.errfirm-falert.errrm-falert_work.log
4.清除旧的归档文件以下的脚本将会在log文件达到90%容量的时候清空旧的归档文件$df-k|greparchFilesystemkbytesused__ailcapacityMountedon/dev/vx/dsk/proddg/archive71123968302102484059423243%/u08/archive#########################################################################clean_arch.ksh##########################################################################!/bin/kshdf-k|greparchdfk.resultarchive_filesystem=`awk-F{print$6}dfk.result`archive_capacity=`awk-F{print$5}dfk.result`if[$archive_capacity90%]thenechoFilesystem${archive_filesystem}is${archive_capacity}filled#tryoneofthefollowingoptiondependonyourneedfind$archive_filesystem-typef-mtime+2-execrm-r{};tarr__nfi
5.分析表和索引以得到更好的性能以下我将展示假如传送参数到一个脚本中######################################################################____yze_table.sh#######################################################################!/bin/ksh#inputparameter:1:passWord#2:SIDif$#1thenechoPleaseenteroracleuserpasswordasthefirstparameter!exit0fiif$#2thenechoPleaseenterinstan__nameasthesecondparameter!exit0fi要传入参数以执行该脚本,输入$____yze_table.sh__nageroradb1脚本的第一部分产生了一个____yze.sql文件,里面包含了分析表用的语句脚本的第二部分分析全部的表###################################################################____yze_table.sh###################################################################sqlplus-s/assysdbasetheadingoffsetfeedoffsetpagesize200setlinesize100spool____yze_table.sqlselect____YZETABLE||owner||.||segment_name||ESTI__TESTATISTICSSAMPLE10PER__NT;fromdba_segmentswheresegment_type=TABLEandownernotinSYSSYSTEM;spooloffexitEOFsqlplus-s/assysdba@./____yze_table.sqlexitEOF以下是____yze.sql的一个例子$cat____yze.sql____YZETABLEHIRWIN.JANUSAGE_SUM__RYESTI__TESTATISTICSSAMPLE10PER__NT;____YZETABLEHIRWIN.JANUSER_PROFILEESTI__TESTATISTICSSAMPLE10PER__NT;____YZETABLEAPPSSYS.HIST_SYSTEM_ACTIVITYESTI__TESTATISTICSSAMPLE10PER__NT;____YZETABLEHTOMEH.QUEST_IM_VERSIONESTI__TESTATISTICSSAMPLE10PER__NT;____YZETABLEJSTENZEL.HIST_SYS_ACT_0615ESTI__TESTATISTICSSAMPLE10PER__NT;
6.检查表空间的使用以下的脚本检测表空间的使用假如表空间只剩下10%,它将会发送一个警告e__il#######################################################################ck_tbsp.sh########################################################################!/bin/kshEDITOR=vi;exportEDITORORACLE_SID=PPRD10;exportORACLE_SIDORACLE_BASE=/data/app/oracle;exportORACLE_BASEORACLE_HOME=$ORACLE_BASE/
10.
2.0;exportORACLE_HOMELD_LIBRARY_PATH=$ORACLE_HOME/lib;exportLD_LIBRARY_PATHTNS_ADMIN=/var/opt/oracle;exportTNS_ADMINNLS_LANG=american;exportNLS_LANGNLS_DATE_FOR__T=MonDDYYYYHH24:MI:SS;exportNLS_DATE_FOR__TORATAB=/var/opt/oracle/oratab;exportORATABPATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/__in:/__in:/usr/openwin/bin:/opt/bin:.;exportPATHDBALIST=tianlesoftware@vip.__.comtianlesoftware@hot__il.com;exportDBALISTsqlplus-s/assysdbasetfeedoffsetlinesize100setpagesize200columnUSEDMBfor__ta10columnFREEMBfor__ta10columnTOTALMBfor__ta10columnPER_FREEfor__ta10spooltablespa__.alertSELECTF.TABLESPA___NAMETO_CHART.TOTAL_SPA__-F.FREE_SPA__999999USEDMBTO_CHARF.FREE_SPA__999999FREEMBTO_CHART.TOTAL_SPA__999999TOTALMBTO_CHARROUNDF.FREE_SPA__/T.TOTAL_SPA__*100999||%PER_FREEFROMSELECTTABLESPA___NAMEROUNDSUMBLOCKS*SELECTVALUE/1024FROMV/$PARAMETERWHERENAME=db_block_size/1024FREE_SPA__FROMDBA_FREE_SPA__GROUPBYTABLESPA___NAMEFSELECTTABLESPA___NAMEROUNDSUMBYTES/1048576TOTAL_SPA__FROMDBA_DATA_FILESGROUPBYTABLESPA___NAMETWHEREF.TABLESPA___NAME=T.TABLESPA___NAMEANDROUNDF.FREE_SPA__/T.TOTAL_SPA__*10080;spooloffexitEOFif[`cattablespa__.alert|wc-l`-gt0]thencattablespa__.alerttablespa__.tmp__ilx-sTABLESPA__ALERTforPPRD10$DBALISTtablespa__.tmpfi警告e__il输出的例子如下TABLESPA___NAMEUSEDMBFREEMBTOTALMBPER_FREE----------------------------------------------------------------------SYSTEM51940192044%MILLDATA559441100044%SYSAUX33160994065%MILLREPORTS14625440064%
7.查找出无效的数据库对象以下查找出无效的数据库对象#######################################################################invalid_o__ect_alert.sh######################################################################!/bin/kshEDITOR=vi;exportEDITORORACLE_SID=PPRD10;exportORACLE_SIDORACLE_BASE=/data/app/oracle;exportORACLE_BASEORACLE_HOME=$ORACLE_BASE/
10.
2.0;exportORACLE_HOMELD_LIBRARY_PATH=$ORACLE_HOME/lib;exportLD_LIBRARY_PATHTNS_ADMIN=/var/opt/oracle;exportTNS_ADMINNLS_LANG=american;exportNLS_LANGNLS_DATE_FOR__T=MonDDYYYYHH24:MI:SS;exportNLS_DATE_FOR__TORATAB=/var/opt/oracle/oratab;exportORATABPATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/__in:/__in:/usr/openwin/bin:/opt/bin:.;exportPATHDBALIST=tianlesoftware@vip.__.comtianlesoftware@hot__il.com;exportDBALISTsqlplus-s/assysdbasetfeedoffsetheadingoffcolumnOWNERfor__ta10columnO__ECT_NAMEfor__ta35columnO__ECT_TYPEfor__ta10columnSTATUSfor__ta10spoolinvalid_o__ect.alertSELECTOWNERO__ECT_NAMEO__ECT_TYPESTATUSFROMDBA_O__ECTSWHERESTATUS=INVALIDORDERBYOWNERO__ECT_TYPEO__ECT_NAME;spooloffexitEOFif[`catinvalid_o__ect.alert|wc-l`-gt0]then__ilx-sINVALIDO__ECTSforPPRD10$DBALISTinvalid_o__ect.alertfi$moreinvalid_o__ect.alertPUBLICALL_WM_LOCKED_TABLESSYNONYMINVALIDPUBLICALL_WM_VERSIONED_TABLESSYNONYMINVALIDPUBLICDBA_WM_VERSIONED_TABLESSYNONYMINVALIDPUBLICSDO_CART_TEXTSYNONYMINVALIDPUBLICSDO_GEOMETRYSYNONYMINVALIDPUBLICSDO_REGAGGRSYNONYMINVALIDPUBLICSDO_REGAGGRSETSYNONYMINVALIDPUBLICSDO_REGIONSYNONYMINVALIDPUBLICSDO_REGIONSETSYNONYMINVALIDPUBLICUSER_WM_LOCKED_TABLESSYNONYMINVALIDPUBLICUSER_WM_VERSIONED_TABLESSYNONYMINVALIDPUBLICWM_COMPRESS_BATCH_SIZESSYNONYMINVALID
8.监视用户和事务死锁等以下的脚本在死锁发生的时候发送一个警告e-__il#####################################################################deadlock_alert.sh######################################################################!/bin/kshEDITOR=vi;exportEDITORORACLE_SID=PPRD10;exportORACLE_SIDORACLE_BASE=/data/app/oracle;exportORACLE_BASEORACLE_HOME=$ORACLE_BASE/
10.
2.0;exportORACLE_HOMELD_LIBRARY_PATH=$ORACLE_HOME/lib;exportLD_LIBRARY_PATHTNS_ADMIN=/var/opt/oracle;exportTNS_ADMINNLS_LANG=american;exportNLS_LANGNLS_DATE_FOR__T=MonDDYYYYHH24:MI:SS;exportNLS_DATE_FOR__TORATAB=/var/opt/oracle/oratab;exportORATABPATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/__in:/__in:/usr/openwin/bin:/opt/bin:.;exportPATHDBALIST=tianlesoftware@vip.__.comtianlesoftware@hot__il.com;exportDBALISTsqlplus-s/assysdbasetfeedoffsetheadingoffspooldeadlock.alertSELECTSIDDECODEBLOCK0NOYESBLOCKERDECODEREQUEST0NOYESWAITERFROMV/$LOCKWHEREREQUEST0ORBLOCK0ORDERBYblockDESC;spooloffexitEOFif[`catdeadlock.alert|wc-l`-gt0]then__ilx-sDEADLOCKALERTforPPRD10$DBALISTdeadlock.alertfi四.结论020407-17**1-5/dba/scripts/ckinstan__.sh/dev/null21020407-17**1-5/dba/scripts/cklsnr.sh/dev/null21020407-17**1-5/dba/scripts/ckalertlog.sh/dev/null2130***0-6/dba/scripts/clean_arch.sh/dev/null21*5**13/dba/scripts/____yze_table.sh/dev/null21*5**0-6/dba/scripts/ck_tbsp.sh/dev/null21*5**0-6/dba/scripts/invalid_o__ect_alert.sh/dev/null21020407-17**1-5/dba/scripts/deadlock_alert.sh/dev/null21通过以上的脚本,可大大减轻你的工作你可以使用这些是来做更重要的工作,例如性能调整文章整理八零后少林方丈http://___.9in.orghttp://___.9in.org脚本数量8个。