共计 14377 个字符，预计需要花费 36 分钟才能阅读完成。
如何分析两个主机和 Oracle 数据库巡检脚本，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。
分享一个之前我用来巡检主机和 Oracle 的脚本，不过输出结果不是很美观，内容还是可以的 …
主机巡检脚本：OSWatcher.sh
ps: 这里的第 10 项，普通用户检查 /var/log/messages 很可能没有读权限，如有需要巡检此系统日志，建议切换 root 用户执行此脚本。
#!/bin/bash echo ###################################################################### # 设置命令的路径，防止命令找不到路径 PATH=$PATH:/usr/sbin/ export PATH echo the PATH is:$PATH ###################################################################### PLATFORM=`/bin/uname` # ###################################################################### # Create log subdirectories if they don t exist ###################################################################### if [ ! -d archive ]; then mkdir archive fi case $PLATFORM in Linux) DF= df -h MEMINFO= free -m MPSTAT= mpstat 1 3 TOP= eval top -b -n 1 | head -50 VMSTAT= vmstat 1 3 IOSTAT= iostat -d -x -k 1 5 PSELF= ps -elf BOOTLOG= tail -500 /var/log/boot.log SYSLOG= dmesg MESSAGE= tail -500 /var/log/messages ;; esac hostn=`hostname` hour=`date + %m.%d.%y.%H00.dat ` echo `date` Collect archive/${hostn}_$hour ###################################################################### # Test for discovery of os utilities. Notify if not found. ###################################################################### echo echo Starting Data Collection... echo case $PLATFORM in Linux) $DF /dev/null 2 1 if [ $? = 0 ]; then echo DF found on your system. echo --1.DF========================== archive/${hostn}_$hour $DF archive/${hostn}_$hour MEMFOUND=1 else echo Warning... DF not found on your system. MEMFOUND=0 fi $MEMINFO /dev/null 2 1 if [ $? = 0 ]; then echo meminfo found on your system. echo --2.MEMINFO========================== archive/${hostn}_$hour $MEMINFO archive/${hostn}_$hour MEMFOUND=1 else echo Warning... /proc/meminfo not found on your system. MEMFOUND=0 fi $MPSTAT /dev/null 2 1 if [ $? = 0 ]; then echo MPSTAT found on your system. echo --3.MPSTAT========================== archive/${hostn}_$hour $MPSTAT archive/${hostn}_$hour MEMFOUND=1 else echo Warning... MPSTAT not found on your system. MEMFOUND=0 fi $TOP /dev/null 2 1 if [ $? = 0 ]; then echo TOP found on your system. echo --4.TOP========================== archive/${hostn}_$hour $TOP archive/${hostn}_$hour MEMFOUND=1 else echo Warning... TOP not found on your system. MEMFOUND=0 fi $VMSTAT /dev/null 2 1 if [ $? = 0 ]; then echo VMSTAT found on your system. echo --5.VMSTAT========================== archive/${hostn}_$hour $VMSTAT archive/${hostn}_$hour MEMFOUND=1 else echo Warning... VMSTAT not found on your system. MEMFOUND=0 fi $IOSTAT /dev/null 2 1 if [ $? = 0 ]; then echo IOSTAT found on your system. echo --6.IOSTAT========================== archive/${hostn}_$hour $IOSTAT archive/${hostn}_$hour MEMFOUND=1 else echo Warning... IOSTAT not found on your system. MEMFOUND=0 fi $PSELF /dev/null 2 1 if [ $? = 0 ]; then echo PSELF found on your system. echo --7.PSELF========================== archive/${hostn}_$hour $PSELF archive/${hostn}_$hour MEMFOUND=1 else echo Warning... PSELF not found on your system. MEMFOUND=0 fi $BOOTLOG /dev/null 2 1 if [ $? = 0 ]; then echo BOOTLOG found on your system. echo --8.BOOTLOG========================== archive/${hostn}_$hour $BOOTLOG archive/${hostn}_$hour MEMFOUND=1 else echo Warning... BOOTLOG not found on your system. MEMFOUND=0 fi $SYSLOG /dev/null 2 1 if [ $? = 0 ]; then echo SYSLOG found on your system. echo --9.SYSLOG========================== archive/${hostn}_$hour $SYSLOG archive/${hostn}_$hour MEMFOUND=1 else echo Warning... SYSLOG not found on your system. MEMFOUND=0 fi $MESSAGE /dev/null 2 1 if [ $? = 0 ]; then echo MESSAGE found on your system. echo --10.MESSAGE========================== archive/${hostn}_$hour $MESSAGE archive/${hostn}_$hour MEMFOUND=1 else echo Warning... MESSAGE not found on your system. MEMFOUND=0 fi ;; esac echo echo Discovery completed. echo Collection completed. echo The Collected result saved in ./archive/${hostn}_$hour. echo Oracle 巡检脚本：ORAWatcher.sh
这个是用来巡检 Oracle 数据库的
#!/usr/bin/ksh echo echo ORAWatcher Version：1.0.1 echo ###################################################################### # 数据库连接设置 ###################################################################### sqlstr=$1 test $1 if [ $? = 1 ]; then echo echo Info...You did not enter a value for sqlstr. echo Info...Using default value = system/system sqlstr= system/system fi ###################################################################### # Create log subdirectories if they don t exist ###################################################################### if [ ! -d archive ]; then mkdir archive fi echo $sqlstr echo Starting Data Collection... echo ###################################################################### hostn=`hostname` hour=`date + %m.%d.%y.%H00.dat ` echo `date` collect... archive/${hostn}_oracle_$hour ###################################################################### echo ######################## 1. 数据库版本 echo select as \ --1.Database Version\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)= Oracle my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 2. 控制文件 echo select as \ --2.Control files\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo select name from v\$controlfile; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 3.pfile、spfile echo select as \ --3.Parameter files\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo show parameter pfile; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 4. 字符集 echo select as \ --4.DB Character\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo col PARAMETER for a20 col value for a20 select * from v\$nls_parameters where parameter= NLS_CHARACTERSET my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 5. 归档状态 echo select as \ --5.DB Archive Mode\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo set linesize 333 show parameter log_archive my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 6. 参数设置 echo select as \ --6.Parameter Config\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo set linesize 500 set pages 2000 show parameter; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 7. 回滚段存储位置 echo select as \ --7.Undo Info\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo set linesize 500 set pages 2000 SELECT SEGMENT_NAME, TABLESPACE_NAME, STATUS FROM DBA_ROLLBACK_SEGS; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 8.redolog echo select as \ --8.Redolog Files\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo set linesize 200 set pages 2000 col MEMBER for a50 select a.member,a.group#,b.thread#,b.bytes,b.members,b.status from v\$logfile a,v\$log b where a.group#=b.group#; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 9. 查看表空间大小及利用率 echo select as \ --9.Tablespace Usage\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo set linesize 200 set pages 2000 col TABLESPACENAME for a30 select substr(a.TABLESPACE_NAME,1,30) TablespaceName, sum(a.bytes/1024/1024) as \ Totle_size(M)\ , sum(nvl(b.free_space1/1024/1024,0)) as \ Free_space(M)\ , sum(a.bytes/1024/1024)-sum(nvl(b.free_space1/1024/1024,0)) as \ Used_space(M)\ , round((sum(a.bytes/1024/1024)-sum(nvl(b.free_space1/1024/1024,0))) *100/sum(a.bytes/1024/1024),2) as \ Used_percent%\ from dba_data_files a, (select sum(nvl(bytes,0)) free_space1,file_id from dba_free_space group by file_id) b where a.file_id = b.file_id(+) group by a.TABLESPACE_NAME order by \ Used_percent%\ my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 10. 数据文件 echo select as \ --10.DB Files Info\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo set linesize 300 set pagesize 500 col file_name format a80 col TABLESPACE_NAME for a30 select tablespace_name,file_id,status,bytes/1024/1024 FileSizeM,file_name from dba_data_files order by tablespace_name; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 11. 查看数据文件的扩展方式 echo select as \ --11.DB Files Extend\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo set linesize 300 set pagesize 500 col FILE_NAME for a60 col TABLESPACE_NAME for a30 select file_id,file_name,tablespace_name,autoextensible from dba_data_files order by file_id; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 12. 查看表空间的扩展方式 echo select as \ --12.TBS Extend\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo set linesize 120 select TABLESPACE_NAME, BLOCK_SIZE, EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT from dba_tablespaces; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 13. 临时表空间 echo select as \ --13.DB Temp TBS\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo select FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES/1024/1024 \ BYTES(M)\ , USER_BYTES/1024/1024 \ USER_BYTES(M)\ , status from dba_temp_files; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 14. 用户默认表空间 echo select as \ --14.User Default TBS\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo set lines 200 set pages 2000 col username for a20 col default_tablespace for a30 col temporary_tablespace for a30 select username, default_tablespace, temporary_tablespace from dba_users; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 15. 数据库缓冲区高速缓存命中率 echo select as \ --15.DB Cache Hit\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo select 1-(phy.value/(cur.value+con.value)) from v\$sysstat cur, v\$sysstat con, v\$sysstat phy where cur.name = db block gets and con.name = consistent gets and phy.name = physical reads my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 16. 重写不等待比率 echo select as \ --16.Redo nowaits\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo select (req.value*5000)/entries.value from v\$sysstat req,v\$sysstat entries where req.name = redo log space requests and entries.name = redo entires my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 17. 库高速缓存命中率 echo select as \ --17.Library Cache Hit\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo select namespace,gethitratio from v\$librarycache; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 18. 数据字典高速缓存 Getmisses 对 gets 的目标比例 echo select as \ --18.DB Dic cache\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo select sum(getmisses)/sum(gets) from v\$rowcache; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 19. 用户对像（表、索引、大小） echo select as \ --19.User objects\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo col OWNER for a30 col SEGMENT_NAME for a33 col PARTITION_NAME for a22 col SEGMENT_TYPE for a11 col TABLESPACE_NAME for a30 set lines 333 set pages 5000 select OWNER ,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,bytes/1024/1024 as table_size_M from Dba_Segments where SEGMENT_TYPE= TABLE order by OWNER; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 20. 检查是否有失效的索引 echo select as \ --20.Check invalid Ind\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo set lines 333 set pages 50 select index_name, owner, status, tablespace_name from dba_indexes where owner not in(SYS , SYSTEM) and status != VALID and tablespace_name is not null union all select index_name, index_owner owner, status, tablespace_name from dba_ind_partitions where index_owner not in (SYS , SYSTEM) and status USABLE and tablespace_name is not null; select as a from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 21. 检查数据库会话连接占用率 echo select as \ --21.Check DB Sessions\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo col TOT_SESSIONS for a15 select cur_sessions, tot_sessions, a.cur_sessions/b.tot_sessions*100 \ sessions used%\ from (select count(*) cur_sessions from v\$session) a, (select value tot_sessions from v\$parameter where name = sessions) b; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 22. 检查数据库会话连接历史最高值 echo select as \ --22.Highwater of Session\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo select HIGHWATER from dba_high_water_mark_statistics where name = SESSIONS my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo ######################## 23. 检查数据库 Job 状态 echo select as \ --23.Check Status of Job\ from dual; my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour echo set linesize 333 col what for a30 col NEXT_DATE for a30 col INTERVAL for a30 select job, what, next_date, INTERVAL, BROKEN from dba_jobs where BROKEN != N my_sql.sql sqlplus $sqlstr my_sql.sql archive/${hostn}_oracle_$hour ###################################################################### echo Collection completed. echo The Collected result saved in ./archive/${hostn}_oracle_$hour. echo 脚本使用
1. 创建目录并授权
# mkdir -p /home/oracle/scripts # chown -R oracle.oinstall /home/oracle/scripts/ # chmod -R 755 /home/oracle/scripts/2. 两脚本都用 oracle 用户执行
切换到 oracle 用户，执行两个脚本：
$./OSWatcher.sh $./ORAWatcher.sh3. 查看巡检内容
注意：ORAWatcher.sh 脚本中数据库的默认连接串是：system/system，如果 system 密码不是这个，可以这样执行：./ORAWatcher.sh system/password
看完上述内容，你们掌握如何分析两个主机和 Oracle 数据库巡检脚本的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！
正文完
