分析兩個主機和Oracle數據庫巡檢腳本,值得收藏
作者:波波說運維
分享一個之前我用來巡檢主機和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.sh
3. 查看巡檢內容


注意:ORAWatcher.sh腳本中數據庫的默認連接串是:system/system,如果system密碼不是這個,可以這樣執行:./ORAWatcher.sh system/password
責任編輯:趙寧寧
來源:
今日頭條