Oracle巡檢腳本其1

#!/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ù)庫版本"
 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

參考https://www.jb51.net/article/202734.htm

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末折晦，一起剝皮案震驚了整個(gè)濱河市褥蚯，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌，老刑警劉巖咆耿，帶你破解...
沈念sama閱讀 212,718評(píng)論 6贊 492
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件杆煞，死亡現(xiàn)場(chǎng)離奇詭異娩嚼，居然都是意外死亡蘑险，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 90,683評(píng)論 3贊 385
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門待锈，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人嘴高，你說我怎么就攤上這事竿音。” “怎么了拴驮？”我有些...
開封第一講書人閱讀 158,207評(píng)論 0贊 348
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵春瞬，是天一觀的道長(zhǎng)。經(jīng)常有香客問我套啤，道長(zhǎng)宽气，這世上最難降的妖魔是什么随常？我笑而不...
開封第一講書人閱讀 56,755評(píng)論 1贊 284
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮萄涯，結(jié)果婚禮上绪氛，老公的妹妹穿的比我還像新娘。我一直安慰自己涝影，他們只是感情好枣察，可當(dāng)我...
茶點(diǎn)故事閱讀 65,862評(píng)論 6贊 386
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著燃逻，像睡著了一般序目。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上伯襟，一...
開封第一講書人閱讀 50,050評(píng)論 1贊 291
城市分裂傳說
那天猿涨，我揣著相機(jī)與錄音，去河邊找鬼姆怪。笑死叛赚，一個(gè)胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的片效。我是一名探鬼主播红伦，決...
沈念sama閱讀 39,136評(píng)論 3贊 410
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長(zhǎng)吁一口氣：“原來是場(chǎng)噩夢(mèng)啊……” “哼淀衣！你這毒婦竟也來了昙读？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 37,882評(píng)論 0贊 268
萬榮殺人案實(shí)錄
序言：老撾萬榮一對(duì)情侶失蹤膨桥，失蹤者是張志新（化名）和其女友劉穎蛮浑，沒想到半個(gè)月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體只嚣，經(jīng)...
沈念sama閱讀 44,330評(píng)論 1贊 303
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡沮稚，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 36,651評(píng)論 2贊 327
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了册舞。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片蕴掏。...
茶點(diǎn)故事閱讀 38,789評(píng)論 1贊 341
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖调鲸，靈堂內(nèi)的尸體忽然破棺而出盛杰，到底是詐尸還是另有隱情，我是刑警寧澤藐石，帶...
沈念sama閱讀 34,477評(píng)論 4贊 333
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布即供，位于F島的核電站，受9級(jí)特大地震影響于微，放射性物質(zhì)發(fā)生泄漏逗嫡。R本人自食惡果不足惜青自，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 40,135評(píng)論 3贊 317
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望驱证。院中可真熱鬧延窜，春花似錦、人聲如沸雷滚。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,864評(píng)論 0贊 21
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽祈远。三九已至呆万，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間车份，已是汗流浹背谋减。一陣腳步聲響...
開封第一講書人閱讀 32,099評(píng)論 1贊 267
情欲美人皮
我被黑心中介騙來泰國(guó)打工，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留扫沼，地道東北人出爹。一個(gè)月前我還...
沈念sama閱讀 46,598評(píng)論 2贊 362
代替公主和親
正文我出身青樓，卻偏偏與公主長(zhǎng)得像缎除，于是被迫代替她去往敵國(guó)和親严就。傳聞我的和親對(duì)象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 43,697評(píng)論 2贊 351

Oracle巡檢腳本其1

推薦閱讀更多精彩內(nèi)容