oracle自動(dòng)巡檢腳本生成html報(bào)告的方法
更新時(shí)間：2020年12月23日 10:35:07 作者：DomDanrtsey
這篇文章主要介紹了oracle自動(dòng)巡檢腳本生成html報(bào)告的方法，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
一、 前言
1、由于每月月底都需要對(duì)一些oracle數(shù)據(jù)庫(kù)環(huán)境進(jìn)行一些簡(jiǎn)單的巡檢，而通過(guò)運(yùn)行一條條語(yǔ)句，并依依去截圖保存到word文檔中效率低下，所以這里我就將手工巡檢過(guò)程編寫(xiě)成shell腳本來(lái)提高巡檢效率，同時(shí)也免去了截圖照片圖片不清晰的問(wèn)題。
2、腳本簡(jiǎn)單容易二次編輯，本文僅提供簡(jiǎn)單巡檢的事項(xiàng)，如數(shù)據(jù)表空間是否自動(dòng)擴(kuò)展、是否開(kāi)啟歸檔等，大家根據(jù)實(shí)際需要編輯修改，增加符合自己公司需求的巡檢報(bào)告。
3、項(xiàng)目已經(jīng)上傳到我的github上
項(xiàng)目地址：orawatch.git
二、注意事項(xiàng)與報(bào)告部分截圖
一定注意閱讀git上的README.md說(shuō)明，避免 system 用戶被鎖定。
三、README.md
1、需要使用oracle用戶執(zhí)行
2、使用說(shuō)明
1)、多實(shí)例下運(yùn)行此腳本：
聲明實(shí)例名；執(zhí)行時(shí)跟上此實(shí)例對(duì)應(yīng)的 system 密碼
$ export ORACLE_SID=orcl $ chmod +x orawatch.sh $ ./orawatch.sh system/yourpassword
或者是將此實(shí)例對(duì)應(yīng)的 system 密碼填寫(xiě)到腳本中，隨后執(zhí)行
$ vi orawatch.sh sqlstr="system/system" $ chmod +x orawatch.sh $ ./orawatch.sh
2)、請(qǐng)注意一定要將對(duì)應(yīng)實(shí)例名的對(duì)應(yīng)system密碼填寫(xiě)至腳本如下位置，或是執(zhí)行時(shí)跟上對(duì)應(yīng)實(shí)例的system密碼，否則將造成 system 用戶因密碼錯(cuò)誤而被鎖定
system用戶解鎖語(yǔ)句：
SQL> alter user system account unlock; alter user system identified by yourpassword;
3、執(zhí)行完巡檢之后，將在腳本所在的路徑下生成html巡檢結(jié)果報(bào)告，如下
192.168.35.244os_oracle_summary.html
4、巡檢項(xiàng)信息如下（其他統(tǒng)計(jì)項(xiàng)可根據(jù)實(shí)際需要自行添加）
0)、巡檢ip信息
1)、數(shù)據(jù)庫(kù)版本
2)、是否開(kāi)啟歸檔，及歸檔磁盤占用率與路徑信息
3)、數(shù)據(jù)庫(kù)memory/sga/pga信息
4)、數(shù)據(jù)表空間是否自動(dòng)擴(kuò)展
5)、數(shù)據(jù)庫(kù)當(dāng)前分配的數(shù)據(jù)表空間使用率信息
四、腳本內(nèi)容
#!/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/yourpassword
ipaddress=`ip a|grep "global"|awk '{print $2}' |awk -F/ '{print $1}'`
file_output=${ipaddress}'os_oracle_summary.html'
td_str=''
th_str=''
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
export NLS_LANG='american_america.AL32UTF8'
#yum -y install bc sysstat net-tools
create_html_css(){
echo -e "<html>
<head>
<style type="text/css">
body {font:12px Courier New,Helvetica,sansserif; color:black; background:White;}
table,tr,td {font:12px Courier New,Helvetica,sansserif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}
th {font:bold 12px Courier New,Helvetica,sansserif; color:White; background:#0033FF; padding:0px 0px 0px 0px;}
h1 {font:bold 12pt Courier New,Helvetica,sansserif; color:Black; padding:0px 0px 0px 0px;}
</style>
</head>
<body>"
}
create_html_head(){
echo -e "<h1>$1</h1>"
}
create_table_head1(){
echo -e "<table width="68%" border="1" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">"
}
create_table_head2(){
echo -e "<table width="100%" border="1" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse">"
}
create_td(){
td_str=`echo $1 | awk 'BEGIN{FS="|"}''{i=1; while(i<=NF) {print "<td>"$i"</td>";i++}}'`
}
create_th(){
th_str=`echo $1|awk 'BEGIN{FS="|"}''{i=1; while(i<=NF) {print "<th>"$i"</th>";i++}}'`
}
create_tr1(){
create_td "$1"
echo -e "<tr>
$td_str
</tr>" >> $file_output
}
create_tr2(){
create_th "$1"
echo -e "<tr>
$th_str
</tr>" >> $file_output
}
create_tr3(){
echo -e "<tr><td>
<pre style=\"font-family:Courier New; word-wrap: break-word; white-space: pre-wrap; white-space: -moz-pre-wrap\" >
`cat $1`
</pre></td></tr>" >> $file_output
}
create_table_end(){
echo -e "</table>"
}
create_html_end(){
echo -e "</body></html>"
}
NAME_VAL_LEN=12
name_val () {
printf "%+*s | %s\n" "${NAME_VAL_LEN}" "$1" "$2"
}
get_netinfo(){
echo "interface | status | ipadds | mtu | Speed | Duplex" >>/tmp/tmpnet_h1_`date +%y%m%d`.txt
for ipstr in `ifconfig -a|grep ": flags"|awk '{print $1}'|sed 's/.$//'`
do
ipadds=`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`.txt
done
}
ora_base_info(){
echo "######################## 1.數(shù)據(jù)庫(kù)版本"
echo "select ' ' as \"--1.Database Version\" from dual;" >ora_sql.sql
sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_base_`date +%y%m%d`.txt
echo "Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)='Oracle';" >ora_sql.sql
sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_base_`date +%y%m%d`.txt
}
ora_archive_info(){
echo "######################## 2.歸檔狀態(tài)"
echo "select ' ' as \"--2.DB Archive Mode\" from dual;" >ora_sql.sql
sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_archive_`date +%y%m%d`.txt
echo "select archiver from v\$instance;" >ora_sql.sql
sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_archive_`date +%y%m%d`.txt
sed -i '33!d' /tmp/tmpora_archive_`date +%y%m%d`.txt
archive_string=`cat /tmp/tmpora_archive_\`date +%y%m%d\`.txt`
if [ $archive_string = STARTED ];then
echo "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.sql
sqlplus $sqlstr <ora_sql.sql>>/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 ; done
fi
}
ora_mem_info(){
echo "######################## 3.1 內(nèi)存參數(shù)memory"
echo "select ' ' as \"--3.1.DB memory\" from dual;" >ora_sql.sql
sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_mem_`date +%y%m%d`.txt
echo "set line 2500;
show parameter memory;" >ora_sql.sql
sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_mem_`date +%y%m%d`.txt
}
ora_sga_info(){
echo "######################## 3.2 內(nèi)存參數(shù)sga"
echo "select ' ' as \"--3.2.DB sga\" from dual;" >ora_sql.sql
sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_sga_`date +%y%m%d`.txt
echo "set line 2500;
show parameter sga;" >ora_sql.sql
sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_sga_`date +%y%m%d`.txt
}
ora_pga_info(){
echo "######################## 3.3 內(nèi)存參數(shù)pga"
echo "select ' ' as \"--3.3.DB pga\" from dual;" >ora_sql.sql
sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_pga_`date +%y%m%d`.txt
echo "set line 2500;
show parameter pga;" >ora_sql.sql
sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_pga_`date +%y%m%d`.txt
}
ora_dbfile_info(){
echo "######################## 4.表空間是否自動(dòng)擴(kuò)展"
echo "select ' ' as \"--4.DB dbfile\" from dual;" >ora_sql.sql
sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_dbfile_`date +%y%m%d`.txt
echo "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_m from dba_data_files;" >ora_sql.sql
sqlplus $sqlstr <ora_sql.sql>>/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.sql
sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt
echo "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_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
sqlplus $sqlstr <ora_sql.sql>>/tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt
}
create_html(){
rm -rf $file_output
touch $file_output
create_html_css >> $file_output
create_html_head "0 Network Info Summary" >> $file_output
create_table_head1 >> $file_output
get_netinfo
while read line
do
create_tr2 "$line"
done < /tmp/tmpnet_h1_`date +%y%m%d`.txt
while read line
do
create_tr1 "$line"
done < /tmp/tmpnet1_`date +%y%m%d`.txt
create_table_end >> $file_output
create_html_head "1 Version of Database" >> $file_output
create_table_head1 >> $file_output
ora_base_info
sed -i '27,33!d' /tmp/tmpora_base_`date +%y%m%d`.txt
sed -i '2,3d' /tmp/tmpora_base_`date +%y%m%d`.txt
create_tr3 "/tmp/tmpora_base_`date +%y%m%d`.txt"
create_table_end >> $file_output
create_html_head "2 Status of archive_log" >> $file_output
create_table_head1 >> $file_output
ora_archive_info
sed -i '2,11d' /tmp/tmpora_archive_`date +%y%m%d`.txt
create_tr3 "/tmp/tmpora_archive_`date +%y%m%d`.txt"
create_table_end >> $file_output
create_html_head "3.1 memory Config of Database" >> $file_output
create_table_head1 >> $file_output
ora_mem_info
sed -i '1,30d' /tmp/tmpora_mem_`date +%y%m%d`.txt
for i in `seq 2`; do sed -i '$d' /tmp/tmpora_mem_`date +%y%m%d`.txt ; done
create_tr3 "/tmp/tmpora_mem_`date +%y%m%d`.txt"
create_table_end >> $file_output
create_html_head "3.2 sga Config of Database" >> $file_output
create_table_head1 >> $file_output
ora_sga_info
sed -i '1,30d' /tmp/tmpora_sga_`date +%y%m%d`.txt
for i in `seq 2`; do sed -i '$d' /tmp/tmpora_sga_`date +%y%m%d`.txt ; done
create_tr3 "/tmp/tmpora_sga_`date +%y%m%d`.txt"
create_table_end >> $file_output
create_html_head "3.3 pga Config of Database" >> $file_output
create_table_head1 >> $file_output
ora_pga_info
sed -i '1,30d' /tmp/tmpora_pga_`date +%y%m%d`.txt
for i in `seq 2`; do sed -i '$d' /tmp/tmpora_pga_`date +%y%m%d`.txt ; done
create_tr3 "/tmp/tmpora_pga_`date +%y%m%d`.txt"
create_table_end >> $file_output
create_html_head "4 dbfile autoextensible of Database" >> $file_output
create_table_head1 >> $file_output
ora_dbfile_info
sed -i '1,30d' /tmp/tmpora_dbfile_`date +%y%m%d`.txt
for i in `seq 2`; do sed -i '$d' /tmp/tmpora_dbfile_`date +%y%m%d`.txt ; done
create_tr3 "/tmp/tmpora_dbfile_`date +%y%m%d`.txt"
create_table_end >> $file_output
create_html_head "5 dbfile usage of Database" >> $file_output
create_table_head1 >> $file_output
ora_dbfile_useage_info
sed -i '1,30d' /tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt
for i in `seq 2`; do sed -i '$d' /tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt ; done
create_tr3 "/tmp/tmpora_dbfile_useage_`date +%y%m%d`.txt"
create_table_end >> $file_output
create_html_end >> $file_output
sed -i 's/BORDER=1/width="68%" border="1" bordercolor="#000000" cellspacing="0px" style="border-collapse:collapse"/g' $file_output
rm -rf /tmp/tmp*_`date +%y%m%d`.txt
rm -rf ora_sql.sql
}
PLATFORM=`uname`
if [ ${PLATFORM} = "HP-UX" ] ; then
echo "This script does not support HP-UX platform for the time being"
exit 1
elif [ ${PLATFORM} = "SunOS" ] ; then
echo "This script does not support SunOS platform for the time being"
exit 1
elif [ ${PLATFORM} = "AIX" ] ; then
echo "This script does not support AIX platform for the time being"
exit 1
elif [ ${PLATFORM} = "Linux" ] ; then
create_html
fi
到此這篇關(guān)于oracle自動(dòng)巡檢腳本生成html報(bào)告的文章就介紹到這了,更多相關(guān)oracle自動(dòng)巡檢腳本內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
DBA 在Linux下安裝Oracle Database11g數(shù)據(jù)庫(kù)圖文教程
正在學(xué)習(xí)Oracle DBA的知識(shí)，所以安裝oracle 11個(gè)的數(shù)據(jù)庫(kù)用以做測(cè)試，如Clone， RMAN, Stream等2014-08-08
Oracle ASM數(shù)據(jù)庫(kù)故障數(shù)據(jù)恢復(fù)解決方案
這篇文章主要介紹了Oracle ASM數(shù)據(jù)庫(kù)故障數(shù)據(jù)恢復(fù)解決方案,需要的朋友可以參考下2017-04-04
小編為大家整理了 Oracle 常用的函數(shù)，大家可以參考一下，希望對(duì)大家有所幫助。2016-05-05
delete archivelog all無(wú)法清除歸檔日志解決方法
最近在因歸檔日志暴增，使用delete archivelog all貌似無(wú)法清除所有的歸檔日志,究竟是什么原因呢？本文將為您解答，需要的朋友可以參考下2012-12-12
案例：一個(gè)應(yīng)用表中的一個(gè)字段是主鍵，向表中插入數(shù)據(jù)時(shí)，先把數(shù)據(jù)放在臨時(shí)表中（沒(méi)有主鍵）然后再插入應(yīng)用表。2013-04-04
Oracle數(shù)據(jù)庫(kù)添加/刪除/修改表字段超詳細(xì)指南
這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫(kù)添加/刪除/修改表字段的超詳細(xì)指南, 本文主要是關(guān)于Oracle數(shù)據(jù)庫(kù)表中字段的增加、刪除、修改和重命名的操作,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-01-01
ora-00119和ora-00132問(wèn)題的解決方法
這篇文章主要為大家詳細(xì)介紹了ora-00119和ora-00132問(wèn)題的解決方法，感興趣的小伙伴們可以參考一下2016-07-07
使用Oracle數(shù)據(jù)庫(kù)登錄時(shí)被告知用戶被鎖怎么解決
這篇文章主要介紹了使用Oracle數(shù)據(jù)庫(kù)登錄時(shí)被告知用戶被鎖怎么解決的相關(guān)資料,需要的朋友可以參考下2016-07-07
序列(SEQUENCE)是序列號(hào)生成器，可以為表中的行自動(dòng)生成序列號(hào)，產(chǎn)生一組等間隔的數(shù)值(類型為數(shù)字)。不占用磁盤空間，占用內(nèi)存,這篇文章主要介紹了Oracle中序列(Sequence)詳解,需要的朋友可以參考下2023-09-09
Oracle出現(xiàn)超出打開(kāi)游標(biāo)最大數(shù)的兩種解決方法
當(dāng)Oracle數(shù)據(jù)庫(kù)中打開(kāi)的游標(biāo)數(shù)超過(guò)了數(shù)據(jù)庫(kù)的最大游標(biāo)數(shù)限制時(shí),就會(huì)出現(xiàn)“超出打開(kāi)游標(biāo)最大數(shù)”的錯(cuò)誤,本文給大家介紹了幾種常見(jiàn)的解決方法,需要的朋友可以參考下2024-02-02