共計 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 行業資訊頻道，感謝各位的閱讀！
正文完
