oracle自动巡检脚本生成html报告 _oracle

一、前言1、由于每月月底都需要对一些oracle数据库环境进行一些简单的巡检，而通过运行一条条语句，并依依去截图保存到word文档中效率低下，所以这里我就将手工巡检过程编写成shell脚本来提高巡检效率，同时也免去了截图照片图片不清晰的问题。
2、脚本简单容易二次编辑，本文仅提供简单巡检的事项，如数据表空间是否自动扩展、是否开启归档等，大家根据实际需要编辑修改，增加符合自己公司需求的巡检报告。
3、项目已经上传到我的github上
项目地址：orawatch.git
二、注意事项与报告部分截图一定注意阅读git上的README.md说明，避免 system 用户被锁定。

文章插图

文章插图

三、README.md1、需要使用oracle用户执行
2、使用说明
1)、多实例下运行此脚本：
声明实例名；执行时跟上此实例对应的 system 密码
$ export ORACLE_SID=orcl$ chmod +x orawatch.sh$ ./orawatch.sh system/yourpassword或者是将此实例对应的 system 密码填写到脚本中，随后执行
$ vi orawatch.shsqlstr="system/system"$ chmod +x orawatch.sh$ ./orawatch.sh2)、请注意一定要将对应实例名的对应system密码填写至脚本如下位置，或是执行时跟上对应实例的system密码，否则将造成 system 用户因密码错误而被锁定
system用户解锁语句：
SQL> alter user system account unlock;alter user system identified by yourpassword;3、执行完巡检之后，将在脚本所在的路径下生成html巡检结果报告，如下
192.168.35.244os_oracle_summary.html
4、巡检项信息如下（其他统计项可根据实际需要自行添加）
0)、巡检ip信息
1)、数据库版本
2)、是否开启归档，及归档磁盘占用率与路径信息
3)、数据库memory/sga/pga信息
4)、数据表空间是否自动扩展
5)、数据库当前分配的数据表空间使用率信息
四、脚本内容

#!/bin/bash# script_name: orawatch.sh# Author: Danrtsey.Shun# Email:mydefiniteaim@126.com# usage:# chmod +x orawatch.sh# export ORACLE_SID=orcl# ./orawatch.sh system/yourpasswordipaddress=`ip a|grep "global"|awk '{print $2}' |awk -F/ '{print $1}'`file_output=${ipaddress}'os_oracle_summary.html'td_str=''th_str=''sqlstr=$1test $1if [ $? = 1 ]; then echo echo "Info...You did not enter a value for sqlstr." echo "Info...Using default value = https://www.isolves.com/it/sjk/Oracle/2020-12-22/system/system" sqlstr="system/system"fiexport NLS_LANG='american_america.AL32UTF8'#yum -y install bc sysstat net-toolscreate_html_css(){echo -e ""}create_html_head(){echo -e "$1
"}create_table_head1(){echo -e ""}create_table_head2(){echo -e "
"}create_td(){td_str=`echo $1 | awk 'BEGIN{FS="|"}''{i=1; while(i<=NF) {print "";i++}}'`}create_th(){th_str=`echo $1|awk 'BEGIN{FS="|"}''{i=1; while(i<=NF) {print "";i++}}'`}create_tr1(){create_td "$1"echo -e "$td_str" >> $file_output}create_tr2(){create_th "$1"echo -e "$th_str" >> $file_output}create_tr3(){echo -e "" >> $file_output}create_table_end(){echo -e ""$i" "$i"
`cat $1`
"}create_html_end(){echo -e ""}NAME_VAL_LEN=12name_val () {printf "%+*s | %sn" "${NAME_VAL_LEN}" "$1" "$2"}get_netinfo(){echo "interface | status | ipadds|mtu|Speed|Duplex" >>/tmp/tmpnet_h1_`date +%y%m%d`.txtfor ipstr in `ifconfig -a|grep ": flags"|awk'{print $1}'|sed 's/.$//'`doipadds=`ifconfig ${ipstr}|grep -w inet|awk '{print $2}'`mtu=`ifconfig ${ipstr}|grep mtu|awk '{print $NF}'`speed=`ethtool ${ipstr}|grep Speed|awk -F: '{print $2}'`duplex=`ethtool ${ipstr}|grep Duplex|awk -F: '{print $2}'`echo "${ipstr}""up" "${ipadds}" "${mtu}" "${speed}" "${duplex}"|awk '{print $1,"|", $2,"|", $3,"|", $4,"|", $5,"|", $6}'>>/tmp/tmpnet1_`date +%y%m%d`.txtdone}ora_base_info(){echo "######################## 1.数据库版本"echo "select ' ' as "--1.Database Version" from dual;" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_base_`date +%y%m%d`.txtecho "Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)='Oracle';" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_base_`date +%y%m%d`.txt}ora_archive_info(){echo "######################## 2.归档状态"echo "select ' ' as "--2.DB Archive Mode" from dual;" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_archive_`date +%y%m%d`.txtecho "select archiver from v$instance;" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_archive_`date +%y%m%d`.txtsed -i '33!d' /tmp/tmpora_archive_`date +%y%m%d`.txtarchive_string=`cat /tmp/tmpora_archive_`date +%y%m%d`.txt`if [ $archive_string = STARTED ];thenecho "set linesize 333;	col FILE_TYPE for a13;select FILE_TYPE,PERCENT_SPACE_USED as "占用率(%)",PERCENT_SPACE_RECLAIMABLE,NUMBER_OF_FILES,CON_ID from v$flash_recovery_area_usage where FILE_TYPE = 'ARCHIVED LOG';show parameter log_archive;	col NAME for a40;	col 已使用空间 for a13;	select NAME,SPACE_LIMIT/1024/1024 as "最大空间(M)",SPACE_USED/1024/1024 as "已使用空间(M)",SPACE_RECLAIMABLE,NUMBER_OF_FILES,CON_ID from v$recovery_file_dest;" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_archive_`date +%y%m%d`.txt	for i in `seq 2`; do sed -i '$d' /tmp/tmpora_archive_`date +%y%m%d`.txt ; donefi}ora_mem_info(){echo "######################## 3.1 内存参数memory"echo "select ' ' as "--3.1.DB memory" from dual;" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_mem_`date +%y%m%d`.txtecho "set line 2500;show parameter memory;" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_mem_`date +%y%m%d`.txt}ora_sga_info(){echo "######################## 3.2 内存参数sga"echo "select ' ' as "--3.2.DB sga" from dual;" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_sga_`date +%y%m%d`.txtecho "set line 2500;show parameter sga;" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_sga_`date +%y%m%d`.txt}ora_pga_info(){echo "######################## 3.3 内存参数pga"echo "select ' ' as "--3.3.DB pga" from dual;" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_pga_`date +%y%m%d`.txtecho "set line 2500;show parameter pga;" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_pga_`date +%y%m%d`.txt}ora_dbfile_info(){echo "######################## 4.表空间是否自动扩展"echo "select ' ' as "--4.DB dbfile" from dual;" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_dbfile_`date +%y%m%d`.txtecho "set lines 2500;col TABLESPACE_NAME for a15;col FILE_NAME for a60;select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, maxbytes/1024/1024 as max_m,increment_by/1024/1024 as incre_mfrom dba_data_files;" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_dbfile_`date +%y%m%d`.txt}ora_dbfile_useage_info(){echo "######################## 5.表空间使用率"echo "select ' ' as "--5.DB dbfile useage" from dual;" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_dbfile_useage_`date +%y%m%d`.txtecho "set line 2500;col 表空间名 for a14;SELECT UPPER(F.TABLESPACE_NAME) "表空间名",D.TOT_GROOTTE_MB "表空间大小(G)",D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(G)",TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",F.TOTAL_BYTES "空闲空间(G)",F.MAX_BYTES "最大块(G)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024*1024), 2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 * 1024*1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACEwhere tablespace_name<> 'USERS' GROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024*1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD where dd.tablespace_name<> 'USERS' GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 1;" >ora_sql.sqlsqlplus $sqlstr >/tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt}create_html(){rm -rf $file_outputtouch $file_outputcreate_html_css >> $file_outputcreate_html_head "0 Network Info Summary" >> $file_outputcreate_table_head1 >> $file_outputget_netinfowhile read linedocreate_tr2 "$line"done < /tmp/tmpnet_h1_`date +%y%m%d`.txtwhile read linedocreate_tr1 "$line"done < /tmp/tmpnet1_`date +%y%m%d`.txtcreate_table_end >> $file_outputcreate_html_head "1 Version of Database" >> $file_outputcreate_table_head1 >> $file_outputora_base_infosed -i '27,33!d' /tmp/tmpora_base_`date +%y%m%d`.txtsed -i '2,3d' /tmp/tmpora_base_`date +%y%m%d`.txtcreate_tr3 "/tmp/tmpora_base_`date +%y%m%d`.txt"create_table_end >> $file_outputcreate_html_head "2 Status of archive_log" >> $file_outputcreate_table_head1 >> $file_outputora_archive_infosed -i '2,11d' /tmp/tmpora_archive_`date +%y%m%d`.txtcreate_tr3 "/tmp/tmpora_archive_`date +%y%m%d`.txt"create_table_end >> $file_outputcreate_html_head "3.1 memory Config of Database" >> $file_outputcreate_table_head1 >> $file_outputora_mem_infosed -i '1,30d' /tmp/tmpora_mem_`date +%y%m%d`.txtfor i in `seq 2`; do sed -i '$d' /tmp/tmpora_mem_`date +%y%m%d`.txt ; donecreate_tr3 "/tmp/tmpora_mem_`date +%y%m%d`.txt"create_table_end >> $file_outputcreate_html_head "3.2 sga Config of Database" >> $file_outputcreate_table_head1 >> $file_outputora_sga_infosed -i '1,30d' /tmp/tmpora_sga_`date +%y%m%d`.txtfor i in `seq 2`; do sed -i '$d' /tmp/tmpora_sga_`date +%y%m%d`.txt ; donecreate_tr3 "/tmp/tmpora_sga_`date +%y%m%d`.txt"create_table_end >> $file_outputcreate_html_head "3.3 pga Config of Database" >> $file_outputcreate_table_head1 >> $file_outputora_pga_infosed -i '1,30d' /tmp/tmpora_pga_`date +%y%m%d`.txtfor i in `seq 2`; do sed -i '$d' /tmp/tmpora_pga_`date +%y%m%d`.txt ; donecreate_tr3 "/tmp/tmpora_pga_`date +%y%m%d`.txt"create_table_end >> $file_outputcreate_html_head "4 dbfile autoextensible of Database" >> $file_outputcreate_table_head1 >> $file_outputora_dbfile_infosed -i '1,30d' /tmp/tmpora_dbfile_`date +%y%m%d`.txtfor i in `seq 2`; do sed -i '$d' /tmp/tmpora_dbfile_`date +%y%m%d`.txt ; donecreate_tr3 "/tmp/tmpora_dbfile_`date +%y%m%d`.txt"create_table_end >> $file_outputcreate_html_head "5 dbfile usage of Database" >> $file_outputcreate_table_head1 >> $file_outputora_dbfile_useage_infosed -i '1,30d' /tmp/tmpora_dbfile_useage_`date +%y%m%d`.txtfor i in `seq 2`; do sed -i '$d' /tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt ; donecreate_tr3 "/tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt"create_table_end >> $file_outputcreate_html_end >> $file_outputsed -i 's/BORDER=1/width="68%" border="1" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse"/g' $file_outputrm -rf /tmp/tmp*_`date +%y%m%d`.txtrm -rf ora_sql.sql}PLATFORM=`uname`if [ ${PLATFORM} = "HP-UX" ] ; thenecho "This script does not support HP-UX platform for the time being"exit 1elif [ ${PLATFORM} = "SunOS" ] ; thenecho "This script does not support SunOS platform for the time being"exit 1elif [ ${PLATFORM} = "AIX" ] ; thenecho "This script does not support AIX platform for the time being"exit 1elif [ ${PLATFORM} = "linux" ] ; thencreate_htmlfi