一、前言

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

二、注意事项与报告部分截图

一定注意阅读git上的README.md说明，避免 system 用户被锁定。
oracle自动巡检脚本生成html报告

三、README.md

1、需要使用oracle用户执行
2、使用说明
1)、多实例下运行此脚本：
声明实例名；执行时跟上此实例对应的 system 密码

1$ export ORACLE_SID=orcl
2$ chmod +x orawatch.sh
3$ ./orawatch.sh system/yourpassword

或者是将此实例对应的 system 密码填写到脚本中，随后执行

1$ vi orawatch.sh
2sqlstr="system/system"
3$ chmod +x orawatch.sh
4$ ./orawatch.sh

2)、请注意一定要将对应实例名的对应system密码填写至脚本如下位置，或是执行时跟上对应实例的system密码，否则将造成 system 用户因密码错误而被锁定

system用户解锁语句：

1SQL> alter user system account unlock;
2alter 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)、数据库当前分配的数据表空间使用率信息

四、脚本内容

1#!/bin/bash
2# script_name: orawatch.sh
3# Author: Danrtsey.Shun
4# Email:mydefiniteaim@126.com
5# usage:
6# chmod +x orawatch.sh
7# export ORACLE_SID=orcl
8# ./orawatch.sh system/yourpassword
9ipaddress=`ip a|grep "global"|awk '{print $2}' |awk -F/ '{print $1}'`
10file_output=${ipaddress}'os_oracle_summary.html'
11td_str=''
12th_str=''
13sqlstr=$1
14test $1
15if [ $? = 1 ]; then
16 echo
17 echo "Info...You did not enter a value for sqlstr."
18 echo "Info...Using default value = system/system"
19 sqlstr="system/system"
20fi
21export NLS_LANG='american_america.AL32UTF8'
22#yum -y install bc sysstat net-tools
23create_html_css(){
24  echo -e "<html>
25<head>
26<style type="text/css">
27    body        {font:12px Courier New,Helvetica,sansserif; color:black; background:White;}
28    table,tr,td {font:12px Courier New,Helvetica,sansserif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} 
29    th          {font:bold 12px Courier New,Helvetica,sansserif; color:White; background:#0033FF; padding:0px 0px 0px 0px;} 
30    h1          {font:bold 12pt Courier New,Helvetica,sansserif; color:Black; padding:0px 0px 0px 0px;} 
31</style>
32</head>
33<body>"
34}
35create_html_head(){
36echo -e "<h1>$1</h1>"
37}
38create_table_head1(){
39  echo -e "<table width="68%" border="1" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">"
40}
41create_table_head2(){
42  echo -e "<table width="100%" border="1" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">"
43}
44create_td(){
45    td_str=`echo $1 | awk 'BEGIN{FS="|"}''{i=1; while(i<=NF) {print "<td>"$i"</td>";i++}}'`
46}
47create_th(){
48    th_str=`echo $1|awk 'BEGIN{FS="|"}''{i=1; while(i<=NF) {print "<th>"$i"</th>";i++}}'`
49}
50create_tr1(){
51  create_td "$1"
52  echo -e "<tr>
53    $td_str
54  </tr>" >> $file_output
55}
56create_tr2(){
57  create_th "$1"
58  echo -e "<tr>
59    $th_str
60  </tr>" >> $file_output
61}
62create_tr3(){
63  echo -e "<tr><td>
64  <pre style=\"font-family:Courier New; word-wrap: break-word; white-space: pre-wrap; white-space: -moz-pre-wrap\" >
65  `cat $1`
66  </pre></td></tr>" >> $file_output
67}
68create_table_end(){
69  echo -e "</table>"
70}
71create_html_end(){
72  echo -e "</body></html>"
73}
74NAME_VAL_LEN=12
75name_val () {
76   printf "%+*s | %s\n" "${NAME_VAL_LEN}" "$1" "$2"
77}
78get_netinfo(){
79   echo "interface | status | ipadds     |      mtu    |  Speed     |     Duplex" >>/tmp/tmpnet_h1_`date +%y%m%d`.txt
80   for ipstr in `ifconfig -a|grep ": flags"|awk  '{print $1}'|sed 's/.$//'`
81   do
82      ipadds=`ifconfig ${ipstr}|grep -w inet|awk '{print $2}'`
83      mtu=`ifconfig ${ipstr}|grep mtu|awk '{print $NF}'`
84      speed=`ethtool ${ipstr}|grep Speed|awk -F: '{print $2}'`
85      duplex=`ethtool ${ipstr}|grep Duplex|awk -F: '{print $2}'`
86      echo "${ipstr}"  "up" "${ipadds}" "${mtu}" "${speed}" "${duplex}"\
87      |awk '{print $1,"|", $2,"|", $3,"|", $4,"|", $5,"|", $6}'  >>/tmp/tmpnet1_`date +%y%m%d`.txt
88   done
89}
90ora_base_info(){
91  echo "######################## 1.数据库版本"
92  echo "select ' ' as \"--1.Database Version\" from dual;" >ora_sql.sql
93  sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_base_`date +%y%m%d`.txt
94  echo "Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)='Oracle';" >ora_sql.sql
95  sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_base_`date +%y%m%d`.txt
96}
97ora_archive_info(){
98  echo "######################## 2.归档状态"
99  echo "select ' ' as \"--2.DB Archive Mode\" from dual;" >ora_sql.sql
100  sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_archive_`date +%y%m%d`.txt
101  echo "select archiver from v\$instance;" >ora_sql.sql
102  sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_archive_`date +%y%m%d`.txt
103  sed -i '33!d' /tmp/tmpora_archive_`date +%y%m%d`.txt
104  archive_string=`cat /tmp/tmpora_archive_\`date +%y%m%d\`.txt`
105  if [ $archive_string = STARTED ];then
106    echo "set linesize 333;
107    col FILE_TYPE for a13;
108    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';
109    show parameter log_archive;
110    col NAME for a40;
111    col 已使用空间 for a13;
112    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.sql
113    sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_archive_`date +%y%m%d`.txt
114    for i in `seq 2`; do sed -i '$d' /tmp/tmpora_archive_`date +%y%m%d`.txt ; done
115  fi
116}
117ora_mem_info(){
118  echo "######################## 3.1 内存参数memory"
119  echo "select ' ' as \"--3.1.DB memory\" from dual;" >ora_sql.sql
120  sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_mem_`date +%y%m%d`.txt
121  echo "set line 2500;
122  show parameter memory;" >ora_sql.sql
123  sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_mem_`date +%y%m%d`.txt
124}
125ora_sga_info(){
126  echo "######################## 3.2 内存参数sga"
127  echo "select ' ' as \"--3.2.DB sga\" from dual;" >ora_sql.sql
128  sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_sga_`date +%y%m%d`.txt
129  echo "set line 2500;
130  show parameter sga;" >ora_sql.sql
131  sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_sga_`date +%y%m%d`.txt
132}
133ora_pga_info(){
134  echo "######################## 3.3 内存参数pga"
135  echo "select ' ' as \"--3.3.DB pga\" from dual;" >ora_sql.sql
136  sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_pga_`date +%y%m%d`.txt
137  echo "set line 2500;
138  show parameter pga;" >ora_sql.sql
139  sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_pga_`date +%y%m%d`.txt
140}
141ora_dbfile_info(){
142  echo "######################## 4.表空间是否自动扩展"
143  echo "select ' ' as \"--4.DB dbfile\" from dual;" >ora_sql.sql
144  sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_dbfile_`date +%y%m%d`.txt
145  echo "set lines 2500;
146  col TABLESPACE_NAME for a15;
147  col FILE_NAME for a60;
148  select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, maxbytes/1024/1024 as max_m,increment_by/1024/1024 as incre_m  from dba_data_files;" >ora_sql.sql
149  sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_dbfile_`date +%y%m%d`.txt
150}
151ora_dbfile_useage_info(){
152  echo "######################## 5.表空间使用率"
153  echo "select ' ' as \"--5.DB dbfile useage\" from dual;" >ora_sql.sql
154  sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt
155  echo "set line 2500;
156  col 表空间名 for a14;
157  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_SPACE   where 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.sql
158  sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt
159}
160
161create_html(){
162  rm -rf $file_output
163  touch $file_output
164  create_html_css >> $file_output
165
166  create_html_head "0 Network Info Summary" >> $file_output
167  create_table_head1 >> $file_output
168  get_netinfo
169  while read line
170  do
171    create_tr2 "$line" 
172  done < /tmp/tmpnet_h1_`date +%y%m%d`.txt
173  while read line
174  do
175    create_tr1 "$line" 
176  done < /tmp/tmpnet1_`date +%y%m%d`.txt
177  create_table_end >> $file_output
178
179  create_html_head "1 Version of Database" >> $file_output
180  create_table_head1 >> $file_output
181  ora_base_info
182  sed -i '27,33!d' /tmp/tmpora_base_`date +%y%m%d`.txt
183  sed -i '2,3d' /tmp/tmpora_base_`date +%y%m%d`.txt
184  create_tr3 "/tmp/tmpora_base_`date +%y%m%d`.txt"
185  create_table_end >> $file_output
186
187  create_html_head "2 Status of archive_log" >> $file_output
188  create_table_head1 >> $file_output
189  ora_archive_info
190  sed -i '2,11d' /tmp/tmpora_archive_`date +%y%m%d`.txt
191  create_tr3 "/tmp/tmpora_archive_`date +%y%m%d`.txt"
192  create_table_end >> $file_output
193
194  create_html_head "3.1 memory Config of Database" >> $file_output
195  create_table_head1 >> $file_output
196  ora_mem_info
197  sed -i '1,30d' /tmp/tmpora_mem_`date +%y%m%d`.txt
198  for i in `seq 2`; do sed -i '$d' /tmp/tmpora_mem_`date +%y%m%d`.txt ; done
199  create_tr3 "/tmp/tmpora_mem_`date +%y%m%d`.txt"
200  create_table_end >> $file_output
201
202  create_html_head "3.2 sga Config of Database" >> $file_output
203  create_table_head1 >> $file_output
204  ora_sga_info
205  sed -i '1,30d' /tmp/tmpora_sga_`date +%y%m%d`.txt
206  for i in `seq 2`; do sed -i '$d' /tmp/tmpora_sga_`date +%y%m%d`.txt ; done
207  create_tr3 "/tmp/tmpora_sga_`date +%y%m%d`.txt"
208  create_table_end >> $file_output
209
210  create_html_head "3.3 pga Config of Database" >> $file_output
211  create_table_head1 >> $file_output
212  ora_pga_info
213  sed -i '1,30d' /tmp/tmpora_pga_`date +%y%m%d`.txt
214  for i in `seq 2`; do sed -i '$d' /tmp/tmpora_pga_`date +%y%m%d`.txt ; done
215  create_tr3 "/tmp/tmpora_pga_`date +%y%m%d`.txt"
216  create_table_end >> $file_output
217
218  create_html_head "4 dbfile autoextensible of Database" >> $file_output
219  create_table_head1 >> $file_output
220  ora_dbfile_info
221  sed -i '1,30d' /tmp/tmpora_dbfile_`date +%y%m%d`.txt
222  for i in `seq 2`; do sed -i '$d' /tmp/tmpora_dbfile_`date +%y%m%d`.txt ; done
223  create_tr3 "/tmp/tmpora_dbfile_`date +%y%m%d`.txt"
224  create_table_end >> $file_output
225
226  create_html_head "5 dbfile usage of Database" >> $file_output
227  create_table_head1 >> $file_output
228  ora_dbfile_useage_info
229  sed -i '1,30d' /tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt
230  for i in `seq 2`; do sed -i '$d' /tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt ; done
231  create_tr3 "/tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt"
232  create_table_end >> $file_output
233
234  create_html_end >> $file_output
235  sed -i 's/BORDER=1/width="68%" border="1" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse"/g' $file_output
236  rm -rf /tmp/tmp*_`date +%y%m%d`.txt
237  rm -rf ora_sql.sql
238}
239PLATFORM=`uname`
240if [ ${PLATFORM} = "HP-UX" ] ; then
241    echo "This script does not support HP-UX platform for the time being"
242exit 1
243elif [ ${PLATFORM} = "SunOS" ] ; then
244    echo "This script does not support SunOS platform for the time being"
245exit 1
246elif [ ${PLATFORM} = "AIX" ] ; then
247    echo "This script does not support AIX platform for the time being"
248exit 1
249elif [ ${PLATFORM} = "Linux" ] ; then
250  create_html
251fi

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

一、前言

二、注意事项与报告部分截图

三、README.md

四、脚本内容

评论区

相关推荐

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

一、 前言

二、注意事项与报告部分截图

三、README.md

四、脚本内容

评论区

相关推荐

一、前言