日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

公告:魔扣目錄網為廣大站長提供免費收錄網站服務,提交前請做好本站友鏈:【 網站目錄:http://www.ylptlb.cn 】, 免友鏈快審服務(50元/站),

點擊這里在線咨詢客服
新站提交
  • 網站:51998
  • 待審:31
  • 小程序:12
  • 文章:1030137
  • 會員:747

一、 前言

1、由于每月月底都需要對一些oracle數據庫環境進行一些簡單的巡檢,而通過運行一條條語句,并依依去截圖保存到word文檔中效率低下,所以這里我就將手工巡檢過程編寫成shell腳本來提高巡檢效率,同時也免去了截圖照片圖片不清晰的問題。
2、腳本簡單容易二次編輯,本文僅提供簡單巡檢的事項,如數據表空間是否自動擴展、是否開啟歸檔等,大家根據實際需要編輯修改,增加符合自己公司需求的巡檢報告。
3、項目已經上傳到我的github上
項目地址:orawatch.git

二、注意事項與報告部分截圖

一定注意閱讀git上的README.md說明,避免 system 用戶被鎖定。

oracle自動巡檢腳本生成html報告

 


oracle自動巡檢腳本生成html報告

 

三、README.md

1、需要使用oracle用戶執行
2、使用說明
1)、多實例下運行此腳本:
聲明實例名;執行時跟上此實例對應的 system 密碼

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

或者是將此實例對應的 system 密碼填寫到腳本中,隨后執行

$ vi orawatch.sh
sqlstr="system/system"
$ chmod +x orawatch.sh
$ ./orawatch.sh

2)、請注意一定要將對應實例名的對應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/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 | %sn" "${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.數據庫版本"
  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.歸檔狀態"
  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 內存參數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 內存參數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 內存參數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.表空間是否自動擴展"
  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

分享到:
標簽:oracle
用戶無頭像

網友整理

注冊時間:

網站:5 個   小程序:0 個  文章:12 篇

  • 51998

    網站

  • 12

    小程序

  • 1030137

    文章

  • 747

    會員

趕快注冊賬號,推廣您的網站吧!
最新入駐小程序

數獨大挑戰2018-06-03

數獨一種數學游戲,玩家需要根據9

答題星2018-06-03

您可以通過答題星輕松地創建試卷

全階人生考試2018-06-03

各種考試題,題庫,初中,高中,大學四六

運動步數有氧達人2018-06-03

記錄運動步數,積累氧氣值。還可偷

每日養生app2018-06-03

每日養生,天天健康

體育訓練成績評定2018-06-03

通用課目體育訓練成績評定