Docstoc

shell

Document Sample
shell Powered By Docstoc
					《基于 Linux 的 Oracle 数据库管理》shell 脚本文件

190 页

  mydu.sh:

  #!/bin/bash
  for dir in ${*:-.}; do
   if [ -e $dir ]; then
     result=$(du -s $dir | cut -f 1)
     let total=$result*1024
     echo -n "Total for $dir = $total bytes"
     if [ $total -ge 1048576 ]; then
       echo " ($((total/1048576)) Mb)"
     elif [ $total -ge 1024 ]; then
       echo " ($((total/1024)) Kb)"
     fi
   fi
  done



194 页

  testcase.sh
  #! /bin/bash
  echo -n "Input a color: "
  read color
  case $color in
  [Bb]lue)
     echo "Your input is bule."
  ;;
  [Rr]*)
     echo "Your input is red."
  ;;
  Green | green)
     echo "Your input is green"
  ;;
  *)
     echo "Input Error!"
  ;;
  esac
195 页

  #! /bin/bash

  case "$1" in

  start)
  sqlplus -s /nolog > /dev/null <<EOF
  conn / as sysdba
  startup
  exit;
  EOF
  echo "Database openned."
  ;;

  stop)
  sqlplus -s /nolog > /dev/null <<EOF
  conn / as sysdba
  shutdown immediate
  exit
  EOF
  echo "Database stopped."
  ;;

  restart)
  sqlplus -s /nolog > /dev/null <<EOF
  conn / as sysdba
  shutdown immediate
  startup
  exit
  EOF
  echo "Database restarted."
  ;;

  status)
  pmonstat=`ps -ef | grep ora_pmon | grep -v grep | wc -l`
  if [ $pmonstat -gt 0 ]
  then
     dbstatus=`sqlplus -s /nolog <<EOF
     conn / as sysdba
     set feedback off heading off pagesize 0
     select status from v\\$instance;
     exit
  EOF`
    echo "Database status:$dbstatus"
  else
    echo "Database not started."
  fi
  ;;

  *)
       echo "Usage: start, stop, status, restart"
  ;;

  esac



198 页


  #!/bin/bash
  declare -i sum=0
  for ((a=1; a<=100; a++))
  do
   sum="$sum"+"$a"
  done
  echo $sum
  exit 0



199 页


  #!/bin/bash
  for (( i=1; i <= 12 ; i++ ))
  do
    for (( j=1 ; j <= 12 ; j++ ))
    do
      echo -ne "$(( j * i ))\t"
    done
    echo
  done

  #! /bin/bash
  for name in $( awk -F":" '{print $1}'< /etc/passwd)
  do
   echo $name
  done

  listdb1:
  #! /bin/bash

  for name in $( awk -F":" '{print $1}'< /etc/passwd }'< /etc/oratab | grep -v "^\#")
  do
    echo $name
  done
  listdb2:
  #! /bin/bash
  for name in $(egrep ":Y|:N" /etc/oratab | grep -v '\^#' | cut -d ":" -f1)
  do
    echo $name
  done



  findlinkfile:
  #! /bin/bash
  filelist=$(ls "$1")
  echo "soft link files in "$1":"
  for file in $filelist
  do
     if [ -h "$1/$file" ]
     then
       echo "$file"
     fi
  done



200 页


  [root@law ~]# cat whileTest.sh
  #!/bin/bash
  var0=0
  LIMIT=10
  while [ "$var0" -lt "$LIMIT" ]
  do
    echo -n "$var0 " # -n 的作用是不换行。
    var0=$(($var0+1))
  done



  #!/bin/bash
  a=1
  LIMIT=10
  while (( a < LIMIT ))
  do
    echo -n "$a "
    ((a+=1))
  done
201 页

  [root@law ~]# cat whileTest2.sh
  #!/bin/bash
  echo
  while [ "$var1" != "end" ]
  do
    echo "Input variable #1 (end to exit) "
    read var1       # read 语句的作用是从键盘接收字符串,传给 var1 变量
    echo "variable #1= $var1 "
    echo
  done
  exit 0



202 页


  [root@law ~]# cat line_word_num
  #! /bin/bash
  if [ ! -e "$1" ]
  then
     echo "The file not exists."
  else
     declare -i line_num=1
     cat "$1" | while read LINE
     do
       char_num=$(echo "$LINE" | wc -w)
       echo "Line $line_num: $char_num"
       line_num=$(($line_num+1))
     done
  fi



203 页

  [root@law ~]# cat breakTest.sh
  #!/bin/bash
  LIMIT=19
  echo "Printing numbers 1 through 20 (but not 3 and 11)"
  a=0
  while [ $a -le "$LIMIT" ]
  do
    a=$(($a+1))
    if [ "$a" -eq 3 ] || [ "$a" -eq 11 ]
    then
       continue
    fi
  echo -n "$a "
  done

  #!/bin/bash
  LIMIT=50
  a=0
  while [ $a -le "$LIMIT" ]
  do
    a=$(($a+1))
    if [ "$a" -gt 20 ]
    then
       break
    fi
  echo -n "$a "
  done




205 页


  [root@law ~]# cat ftptest
  #! /bin/bash
  ftp -n 192.168.0.188 >/dev/null 2>&1 <<EOF
  user oracle "oracle"
  cd /opt/oracle/admin/law/bdump
  binary
  get alert_law.log
  bye
  EOF

207 页


  [oracle@redhat ~]$ cat sqlplus.sh
  #! /bin/bash
  sqlplus /nolog <<EOF
  conn scott/tiger
  select * from dept;
  exit
  EOF
208 页


  [oracle@redhat ~]$ cat sqlplus.sh
  #! /bin/bash
  sqlplus /nolog > sqlresult <<EOF
  conn scott/tiger
  select * from dept;
  exit
  EOF

  [oracle@redhat ~]$ cat sqlplus.sh
  #! /bin/bash
  sqlplus -s /nolog > sqlresult <<EOF
  conn scott/tiger
  select * from dept;
  exit
  EOF




209 页


  [oracle@redhat ~]$ cat sqlplus.sh
  #! /bin/bash
  sqlplus -s /nolog > sqlresult <<EOF
  set heading off feedback off pagesize 0
  conn scott/tiger
  select * from dept;
  exit
  EOF



210 页


  [oracle@redhat ~]$ cat sqlshell1.sh
  #!/bin/bash
  VALUE=`sqlplus -silent /nolog <<END
       conn scott/tiger
       set pagesize 0 feedback off verify off heading off echo off numwidth 4
       select count(*) coun from all_objects;
       exit;
       END`
  if [ "$VALUE" -gt 0 ]; then
     echo "The number of rows is $VALUE."
     exit 0
  else
     echo "There is no row in the table."
  fi



211 页


  [oracle@redhat ~]$ cat sqltest.sh
  #!/bin/bash
  sqlplus -s /nolog > result.log <<EOF
  connect scott/tiger
  set verify off
  col coun new_value coun_emp
  select count(*) coun from emp;
  exit coun_emp
  EOF
  result="$?"
  echo "The number of rows in emp is $result"




212 页


  [oracle@redhat ~]$ cat sqltest.sh
  #!/bin/bash
  sqlplus -s /nolog > result.log <<EOF
  connect scott/tiger
  set verify off
  col coun new_value coun_emp
  select count(*) coun from all_objects;
  exit coun_emp
  EOF
  result="$?"
  echo "The number of rows in emp is $result"




213 页
  [oracle@law ~]$ cat VarToSQL1.sh
  #! /bin/bash
  dno=$1
  sqlplus -s scott/tiger << EOF
  select ename,sal from emp
  where deptno=$dno;
  exit
  EOF



214 页


  [oracle@law ~]$ cat VarToSQL2.sh
  #! /bin/bash
  empname=$1
  sqlplus -s scott/tiger << EOF
  select ename,sal from emp
  where ename='$empname';
  exit
  EOF



  [oracle@law ~]$ cat VarToSQL3.sh
  #! /bin/bash
  sqlplus -s scott/tiger << EOF
  select ename,sal from emp
  where ename='$1';
  exit
  EOF




220 页


  [oracle@law ~]$ cat testPasswd.sh
  #! /bin/bash
  echo -n "Enter password for scott: "
  read passwd
  sqlplus -s /nolog << EOF
  conn scott/$passwd
  select * from dept;
  exit
  EOF
  [oracle@law ~]$ cat testPasswd.sh
  #! /bin/bash
  passwd=`cat passwd.txt`
  sqlplus -s /nolog << EOF
  conn scott/$passwd
  select * from dept;
  exit
  EOF




222 页


  [oracle@redhat bin]$ cat dbstartup
  #! /bin/bash
  lsnrctl start
  sqlplus /nolog <<EOF
  connect / as sysdba
  startup
  EOF



223 页

  [oracle@redhat bin]$ cat dbstop
  #! /bin/bash
  lsnrctl stop
  sqlplus /nolog <<EOF
  connect / as sysdba
  shutdown immediate
  EOF



  [root@redhat init.d]# vi oracle
  #! /bin/bash
  case "$1" in
  start)
  echo "Starting Oracle Listener and Database …"
    echo "----------------------------------------------------------------------" >> /var/log/oracle
    date +"%T %a %D: Starting Oracle Listener and Database …" >> /var/log/oracle
    echo "----------------------------------------------------------------------" >> /var/log/oracle
    su - oracle -c dbstartup >> /var/log/oracle
    echo "Done."
    echo " "
    echo "----------------------------------------------------------------------" >> /var/log/oracle
    date +"%T %a %D: Finished." >> /var/log/oracle
    echo "----------------------------------------------------------------------" >> /var/log/oracle
    touch /var/lock/subsys/oracle
    ;;
  stop)
    echo "Stop Oracle Database and Listener … "
    echo "----------------------------------------------------------------------" >> /var/log/oracle
    date +"%T %a %D: Stop Oracle Listener and Database…" >> /var/log/oracle
    echo "----------------------------------------------------------------------" >> /var/log/oracle
    su - oracle -c dbstop >> /var/log/oracle
    rm –f /var/lock/subsys/oracle
    echo "Done."
    echo ""
    echo "----------------------------------------------------------------------" >> /var/log/oracle
    date +"%T %a %D: Finished." >> /var/log/oracle
    echo "----------------------------------------------------------------------" >> /var/log/oracle
    ;;
  restart)
    echo "Restarting Oracle Listener and Database …"
    echo "----------------------------------------------------------------------" >> /var/log/oracle
    date +"%T %a %D: Restarting Oracle Listener and Database ..." >> /var/log/oracle
    echo "----------------------------------------------------------------------" >> /var/log/oracle
    su - oracle -c dbstop >> /var/log/oracle
    su - oracle -c dbstartup >> /var/log/oracle
    echo "Done."
    echo ""
    echo "----------------------------------------------------------------------" >> /var/log/oracle
    date +"%T %a %D: Finished." >> /var/log/oracle
    echo "----------------------------------------------------------------------" >> /var/log/oracle
    touch /var/lock/subsys/oracle
    ;;
  *)
    echo "Usage: oracle {start|stop|restart}"
    exit 1
  esac




230 页


  [root@law oracle]# cat chklistener_1.sh
    #!/bin/bash
    . /home/oracle/.bash_profile
    declare -i n_lsnr
    n_lsnr=`ps -ef | grep listener | grep -v grep | wc -l`
    if [ $n_lsnr=0 ]; then
       lsnrctl start > /dev/null
       mail law_mail@126.com -s " Listener is down, and restarted. "
   fi



231 页

  chklistener_2.sh:
  #!/bin/bash
  . /home/oracle/.bash_profile
  tempfile=/oracle/admin/$ORACLE_SID/tempfile.lis
  lsnrctl status > $tempfile
  if [ $? != 0 ]
  then
     echo " " >> $tempfile
     echo "=============================================" >> $tempfile
     echo " " >> $tempfile
     date >> $tempfile
     echo "Listener is down, restarting it agin." >> $tempfile
     lsnrctl start >> $tempfile

     if [ $? = 0 ]
     then
        mail law_mail@126.com -s "Listener is restarted." < $tempfile
     else
        mail law_mail@126.com -s "Listener is down, restarting failed." < $tempfile
     fi
   fi
  rm -f $tempfile




232 页


  #! /bin/bash
  ORATAB=/etc/oratab
  echo "`date`"
  echo "Oracle Database(s) Status `hostname`"
  db=`grep -v "^\#" $ORATAB | egrep -i ":Y|:N" | cut -f1 -d ":"`
  pslist="`ps -ef | grep pmon | grep -v grep`"
  for db_name in $db; do
    echo "$pslist" | grep "ora_pmon_$db_name" > /dev/null 2>&1
    if (( $? )); then
       echo "Oracle Instance - $db_name: Down"
     else
       echo "Oracle Instance -$db_name: Up"
    fi
   done



233 页

  #! /bin/bash
  df -k | grep arch > dfk.tmp
  archive_filesystem=`awk -F" " '{ print $6 }' dfk.tmp`
  archive_capacity=`awk -F" " '{ print $5 }' dfk.tmp`
  if [[ $archive_capacity > 90% ]]
  then
     echo "Filesystem ${archive_filesystem} is ${archive_capacity} filled"
     find $archive_filesystem -type f -mtime +2 -exec rm -r {} \;
  fi




234 页

  #!/bin/bash
  declare -i nerror
  nerror=`grep -i "^ORA-" /oracle/admin/law/bdump/alert_law.log | wc -l`
  if [ $nerror -gt 0 ]; then
      grep -i "^ORA-" /oracle/admin/law/bdump/alert_law.log | \
      mail law_mail@126.com -s " There are some erros in alert file! "
  fi




235 页


  #! /bin/bash
  . /home/oracle/.bash_profile
  cd $ORACLE_BASE/admin/$ORACLE_SID/bdump
  if [ -f alert_$ORACLE_SID.log ]
  then
     mv alert_$ORACLE_SID.log alert_temp.log
     touch alert_$ORACLE_SID.log
     cat alert_temp.log >> alert_$ORACLE_SID.hist
     grep ORA- alert_temp.log > alert.err
   fi
   if [ `cat alert.err|wc -l` -gt 0 ]
   then
      mail -s "ORACLE ALERT ERRORS" law_mail@126.com < alert.err
   fi
   rm -f alert.err
   rm -f alert_temp.log




236 页


  #/bin/bash
  RESULT=$(df | grep "^/dev/" | awk '{print $5}' | sort -nr | head -1| tr -d "%")
  if [ "$RESULT" -gt "85" ]; then
      df -h | mail -s "Server Warning: Disk usage nearly full " law_mail@126.com
  fi




238 页


  #/bin/bash
  RESULT=$(df | grep "^/dev/" | awk '{print $5}' | sort -nr | head -1)
  if [[ $RESULT > 85% ]]; then
      df -h | mail -s "Server Warning: Disk usage nearly full " law_mail@126.com
  fi

  #/bin/bash
  limit=85%
  RESULT=$(df | grep "^/dev/" | awk '{print $5}' | sort -nr | head -1)
  if [[ $RESULT > $limit ]]; then
      df -h | mail -s "Server Warning: Disk usage nearly full " law_mail@126.com
  fi

  #! /bin/bash
  limit=85
  tempfile=chk_disk_space.tmp
  alert=n
  for percent in `df | grep -v Filesystem | awk '{ print $5 }' | cut -d'%' -f1`
  do
    if [ $percent -ge $limit ]
    then
       alert=y
       break
     fi
   done

  if [ ! alert = 'n' ]
  then
     df -k > $tempfile
     mail -s "Disk usage above $limit% on `hostname`" law_mail@126.com < $tempfile
     rm -f $tempfile
  fi



244 页


  [oracle@law ~]$ cat chktbs.sh
  #! /bin/bash
  . /home/oracle/.bash_profile
  sqlplus -s system/oracle > tbsfree.alert << EOF
  set feed off
  set verify off
  set heading off
  set pagesize 0
  set linesize 300
  select f.tablespace_name ,f.free_space/d.total_space free_percen
  from (select tablespace_name,sum(bytes) free_space
         from dba_free_space group by tablespace_name) f,
  (select tablespace_name,sum(bytes) total_space
         from dba_data_files group by tablespace_name) d
  where f.tablespace_name=d.tablespace_name
  and f.free_space/d.total_space<0.20
  /
  exit
  EOF

  if [ `cat tbsfree.alert | wc -l` -gt 0 ]
  then
     cat tbsfree.alert | mail law_mail@126.com -s "No enough free space in Orace db! "
  fi
245 页


  [oracle@law ~]$ cat chktbs.sh
  #! /bin/bash
  . /home/oracle/.bash_profile
  sqlplus -s system/oracle > /dev/null << EOF
  set feed off
  set heading off
  set pagesize 0
  set linesize 300
  spool tbsfree.alert
  select f.tablespace_name ,f.free_space/d.total_space free_percen
  from (select tablespace_name,sum(bytes) free_space
         from dba_free_space group by tablespace_name) f,
  (select tablespace_name,sum(bytes) total_space
         from dba_data_files group by tablespace_name) d
  where f.tablespace_name=d.tablespace_name
  and f.free_space/d.total_space<0.20;
  spool off
  exit
  EOF

  if [ `cat tbsfree.alert | wc -l` -gt 0 ]
  then
     cat tbsfree.alert | mail law_mail@126.com -s "No enough free space in Orace db! "
  fi




248 页


  #! /bin/bash
  backup_dir=/oracle/bakup
  log_file=$ORACLE_BASE/admin/$ORACLE_SID/cold_backup_$ORACLE_SID.log
  echo "Beginning cold backup..." > $log_file
  date >> $log_file

  sqlplus -s / as sysdba << EOF > $log_file
  set pagesize 0
  set linesize 1000
   spool file_copy_$ORACLE_SID.sh
   select 'cp ' || name || ' $backup_dir/' from v\$controlfile;
   select 'cp ' || file_name || ' $backup_dir/' from dba_data_files;
   select 'cp ' || member || ' $backup_dir/' from v\$logfile;
   spool off
   shutdown immediate;
   !bash file_copy_$ORACLE_SID.sh
   startup;
   exit;
   EOF
   rm -f file_copy_$ORACLE_SID.sh

   if [ -e $ORACLE_HOME/dbs/init$ORACLE_SID.ora ]
   then
      cp $ORACLE_HOME/dbs/init$ORACLE_SID.ora $backup_dir/
   fi

   if [ -e $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora ]
   then
      cp $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora $backup_dir/
   fi

   if [ -e $ORACLE_HOME/dbs/orapw$ORACLE_SID ]
   then
      cp $ORACLE_HOME/dbs/orapw$ORACLE_SID $backup_dir/
   fi

   echo "Cold backup finished." >> $log_file
   date >> $log_file



250 页

  #! /bin/bash
  backup_dir=/oracle/bakup
  log_file=$ORACLE_BASE/admin/$ORACLE_SID/hot_backup_$ORACLE_SID.log
  echo "Beginning hot backup..." > $log_file
  date >> $log_file

  sqlplus -s / as sysdba << EOF >> $log_file
  alter database backup controlfile to '/$backup_dir/backup_cotrolfile.ctl';
  set pagesize 0
   set linesize 1000
   set feedback off
   column tablespace_name noprin
   column sortorder noprin
   column textout format a120

   spool hot_backup_$ORACLE_SID.sql

   select tablespace_name,'1' sortorder,
        'alter tablespace ' || tablespace_name || ' begin backup;' textout
   from dba_data_files
   union
   select tablespace_name,'2' sortorder,
        'host cp ' || file_name || ' ' || '$backup_dir' textout
   from dba_data_files
   union
   select tablespace_name, '3' sortorder,
       'alter tablespace ' || tablespace_name || ' end backup; ' textout
   from dba_data_files
   order by tablespace_name, sortorder, textout;

   select 'alter system archive log current;' from dual;
   spool off

   @hot_backup_$ORACLE_SID.sql
   exit
   EOF

   rm -f hot_backup_$ORACLE_SID.sql
  echo "Hot backup finished." >> $log_file



256 页


  #! /bin/bash

  if [ $1 ]
  then
     backup_level=$1
  else
     backup_level=0
  fi

   backup_user=sys
   backup_user_pw=oracle

   #catlog_user=rman
   #catlog_user_pw=rman

   log_file=/oracle/admin/$ORACLE_SID/rman_backup.log

   echo "Beginning rman backup..." > $log_file
   date >> $log_file

   rman target $backup_user/$backup_user_pw << EOF > $log_file
   # catlog $catlog_user/$catlog_user_pw@rman
  backup incremental level=$backup_level database;
  quit;
  EOF
  echo "rman backup finished." >> $log_file
  date >> $log_file



257 页


  #! /bin/bash
  log_file=/oracle/admin/$ORACLE_SID/full_exp_$ORACLE_SID.log
  exp_arguments="userid=system/oracle buffer=10485760 owner=$1"
  if [ $2 ]
  then
     exp_arguments="$exp_arguments file=$2"
  else
     exp_arguments="$exp_arguments file="$ORACLE_SID"_full_export.dmp"
  fi

   echo "Beginning full db export..." > $log_file
   echo "Exporting with following arguments: $exp_arguments" >> $log_file
   exp $exp_arguments >> $log_file 2>&1
   echo "Export finished." >> $log_file
   date >> $log_file

   exp_result=$?
 if [ exp_result != 0 ]
 then
   mail -s "Errors in exporting $ORACLE_SID." law_mail@126.com < $log_file
fi

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:20
posted:9/12/2012
language:Unknown
pages:20