分析两个主机和Oracle数据库巡检脚本，值得收藏
mhr18 2024-09-20 20:57 29 浏览 0 评论
概述
分享一个之前我用来巡检主机和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
虽然后面都是用工具巡检了，但是这个脚本还是有可借鉴之处的。后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~
相关推荐
- B站收藏视频失效?mybili 收藏夹备份神器完整部署指南
-
本内容来源于@什么值得买APP，观点仅代表作者本人｜作者：羊刀仙很多B站用户都有过类似经历：自己精心收藏的视频突然“消失”，点开一看不是“已被删除”，就是“因UP主设置不可见”。而B站并不会主动通知...
- 中间件推荐初始化配置
-
Redis推荐初始化配置bind0.0.0.0protected-modeyesport6379tcp-backlog511timeout300tcp-keepalive300...
- Redis中缓存穿透问题与解决方法
-
缓存穿透问题概述在Redis作为缓存使用时，缓存穿透是常见问题。正常查询流程是先从Redis缓存获取数据，若有则直接使用；若没有则去数据库查询，查到后存入缓存。但当请求的数据在缓存和数据库中都...
- 后端开发必看!Redis 哨兵机制如何保障系统高可用?
-
你是否曾在项目中遇到过Redis主服务器突然宕机，导致整个业务系统出现数据读取异常、响应延迟甚至服务中断的情况？面对这样的突发状况，作为互联网大厂的后端开发人员，如何快速恢复服务、保障系统的高可用...
- Redis合集-大Key处理建议
-
以下是Redis大Key问题的全流程解决方案，涵盖检测、处理、优化及预防策略，结合代码示例和最佳实践：一、大Key的定义与风险1.大Key判定标准数据类型大Key阈值风险场景S...
- 深入解析跳跃表:Redis里的"老六"数据结构，专治各种不服
-
大家好，我是你们的码农段子手，今天要给大家讲一个Redis世界里最会"跳科目三"的数据结构——跳跃表（SkipList）。这货表面上是个青铜，实际上是个王者，连红黑树见了都要喊声大哥。...
- Redis 中 AOF 持久化技术原理全解析，看完你就懂了!
-
你在使用Redis的过程中，有没有担心过数据丢失的问题？尤其是在服务器突然宕机、意外断电等情况发生时，那些还没来得及持久化的数据，是不是让你夜不能寐？别担心，Redis的AOF持久化技术就是...
- Redis合集-必备的几款运维工具
-
Redis在应用Redis时，经常会面临的运维工作，包括Redis的运行状态监控，数据迁移，主从集群、切片集群的部署和运维。接下来，从这三个方面，介绍一些工具。先来学习下监控Redis实时...
- 别再纠结线程池大小 + 线程数量了，没有固定公式的!
-
我们在百度上能很轻易地搜索到以下线程池设置大小的理论：在一台服务器上我们按照以下设置CPU密集型的程序-核心数+1I/O密集型的程序-核心数*2你不会真的按照这个理论来设置线程池的...
- 网络编程—IO多路复用详解
-
假如你想了解IO多路复用，那本文或许可以帮助你本文的最大目的就是想要把select、epoll在执行过程中干了什么叙述出来，所以具体的代码不会涉及，毕竟不同语言的接口有所区别。基础知识IO多路复用涉及...
- 5分钟学会C/C++多线程编程进程和线程
-
前言对线程有基本的理解简单的C++面向过程编程能力创造单个简单的线程。创造单个带参数的线程。如何等待线程结束。创造多个线程，并使用互斥量来防止资源抢占。会使用之后，直接跳到“汇总”，复制模板来用就行...
- 尽情阅读，技术进阶，详解mmap的原理
-
1.一句话概括mmapmmap的作用，在应用这一层，是让你把文件的某一段，当作内存一样来访问。将文件映射到物理内存，将进程虚拟空间映射到那块内存。这样，进程不仅能像访问内存一样读写文件，多个进程...
- C++11多线程知识点总结
-
一、多线程的基本概念1、进程与线程的区别和联系进程：进程是一个动态的过程，是一个活动的实体。简单来说，一个应用程序的运行就可以被看做是一个进程；线程：是运行中的实际的任务执行者。可以说，进程中包含了多...
- 微服务高可用的2个关键技巧，你一定用得上
-
概述上一篇文章讲了一个朋友公司使用SpringCloud架构遇到问题的一个真实案例，虽然不是什么大的技术问题，但如果对一些东西理解的不深刻，还真会犯一些错误。这篇文章我们来聊聊在微服务架构中，到底如...
- Java线程间如何共享与传递数据
-
1、背景在日常SpringBoot应用或者Java应用开发中，使用多线程编程有很多好处，比如可以同时处理多个任务，提高程序的并发性；可以充分利用计算机的多核处理器，使得程序能够更好地利用计算机的资源，...
欢迎 你 发表评论:
- 一周热门
- 最近发表
- 标签列表
-
- oracle位图索引 (63)
- oracle批量插入数据 (62)
- oracle事务隔离级别 (53)
- oracle 空为0 (50)
- oracle主从同步 (55)
- oracle 乐观锁 (51)
- redis 命令 (78)
- php redis (88)
- redis 存储 (66)
- redis 锁 (69)
- 启动 redis (66)
- redis 时间 (56)
- redis 删除 (67)
- redis内存 (57)
- redis并发 (52)
- redis 主从 (69)
- redis 订阅 (51)
- redis 登录 (54)
- redis 面试 (58)
- 阿里 redis (59)
- redis 搭建 (53)
- redis的缓存 (55)
- lua redis (58)
- redis 连接池 (61)
- redis 限流 (51)