oracle 备份,表空间检查shell脚本代码

最新推荐文章于 2021-04-02 18:53:32 发布
最新推荐文章于 2021-04-02 18:53:32 发布
阅读量1.6k 收藏
点赞数
分类专栏： oracle技术 文章标签： oracle shell 脚本 variables database report
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/pengshk/article/details/5492116
版权

#!/bin/ksh
#****************************************************************************
#
# (C) Copyright CPIC. 2008
#
# File name:    check_db_alert.sh
# Project:      Database Alerts Check
#
# Parameter : app_name -- name of db application(=oracle_sid)
#         ipaddress -- ip of the db server
#
# Creation Date:        Aug 19, 2008
# Origin Author:        Shangkun Peng
#
#
# Prerequisites:
# before using this shell script, directory $DAILY_CHECK must be created
# and this shell script should be put into it.
# for each application, environment file $DBENVFILE must be put into it.
# also, variables for auto ftp must be set
#
# Limitations:
# this shell script can be run at most once an hour
# additional job can be added but variable JOB_LIST must be modified
# and relative function must be coded too
#
#****************************************************************************

# record start date and time
echo "------------------------------------------------------------"
echo "database information collect started at:"; date

# check necessary parameter(s)
if [ $# -ne 2 ]
then
  echo "ERROR: parameter(s) not supplied correctly"
  echo "USAGE: check_db_alert.sh app_name ipaddress"
  echo "WHERE:"
  echo "app_name -- name of db application"
  echo "ipaddress -- ip of the db server"
  exit 1
fi

# -----------------------------------------------------------------------------------------------------------
# DEFINE VALUES FOR VARIABLES IN THIS SHELL

# general variables, can not be modified

APP_NAME=$1     # name of db application
IPADDRESS=$2     # ip of db server
DAILY_CHECK=/tmp/db_dailycheck   # directory for information collection of db application
DBENVFILE=$APP_NAME".dbenv"   # name of environment file
DATE=`date +%y%m%d`    # records shell running date
HOUR=`date +%H`     # records shell running hour

# configuration variables, can be modified

RETENTION=30     # days to retain result files
SUFFIX=pre     # suffix for result files before ftp
SUFFIX1=bak     # suffix for result files after ftp
FORMAT=$DATE"."$HOUR"."$SUFFIX   # name format for result files before ftp

# auto ftp variables

FTP_DEST_HOST=10.193.16.86    # ip address of information repository server
FTP_USER=ftpuser    # ftp user name on information repository server
FTP_PWD=1qaz2wsx    # corresponding password for ftp user above
FTP_DEST_DIR=//tbdb_check/db_dailycheck/$DATE  # directory for receiving on information repository server


# job list variables
# include jobs in double quotation marks and separate them with space,
# when add a job, add explanations here and add relative function below
# job1: get the errors of alert_$ORACLE_SID.log within retention
# job2: get the tablespaces which useage>90% and freeage<5G
# job3: get the alert.log,listener.log,sqlnet.log size

JOB_LIST="job1 job2 job3 job4 job5"   # all the jobs need to be done

# -----------------------------------------------------------------------------------------------------------
# check if necessory environment file $DBENVFILE exists
# and the shell has not be run in the same hour already

if [ ! -f $DAILY_CHECK"/"$DBENVFILE ]
then
  echo "ERROR: necessory environment file ("$DBENVFILE") does not exist in "$DAILY_CHECK" directory"
  exit 1
elif [ -f $DAILY_CHECK"/"$APP_NAME.*.$FORMAT* ]
then
  echo "ERROR: this shell script has already been run in this hour, it can NOT be run more than once an hour"
  exit 1
fi

# -----------------------------------------------------------------------------------------------------------
# get into directory for information collection of db application
# and set db environment variables before beginning information collection

cd $DAILY_CHECK
. $DAILY_CHECK"/"$DBENVFILE

# sqlplus varivale

SQLUSER="/ as sysdba"
ALERTFILET=$DAILY_CHECK/alertfilet.txt
sqlplus -S "$SQLUSER" <<EOF
set echo off
set feedback off
set heading off
set pagesize 0
set linesize 100
set trimspool on
spool $ALERTFILET
select a.value||'/alert_'||b.value||'.log' from v/$parameter a,v/$parameter b
where a.name='background_dump_dest' and b.name='instance_name';
spool off
set echo on
set feedback on
set heading on
set pagesize 1000
set linesize 1000
set trimspool off
EOF

ALTERFILE=`head -1 $ALERTFILET`
rm -f $ALERTFILET

# -----------------------------------------------------------------------------------------------------------
# delete outdated files
find $DAILY_CHECK -name $APP_NAME".*."$SUFFIX1 -mtime +$RETENTION -exec rm -f {} /;


# -----------------------------------------------------------------------------------------------------------
# FUNCTION AREA

# function to collect capacity information
# job1: get the errors of alert_$ORACLE_SID.log within retention
# job2: get the tablespaces which useage>90% and freeage<5G
# job3: get the alert.log,listener.log,sqlnet.log size

job1()
{
#job1 variables
today=`date +%a`
username="/ as sysdba"
datefile=./date.txt
alertfile=$ALTERFILE

cd $DAILY_CHECK
touch date.txt
touch alert
echo $IPADDRESS"."$APP_NAME > $APP_NAME"."$FORMAT
echo "/n" >> $APP_NAME"."$FORMAT
echo "alert{SID}.log is processing..."
echo "1.alert.log check" >> $APP_NAME"."$FORMAT
echo '/n/n--------------------------------------------------/n/n' >> $APP_NAME"."$FORMAT
sqlplus -S "$username" << EOF
set echo off
set feedback off
set heading off
set pagesize 0
set linesize 100
set trimspool on
spool $datefile
select to_char(sysdate-1,'yyyy') from dual;
select to_char(sysdate-1,'Mon') from dual;
select to_char(sysdate-1,'dd') from dual;
select initcap(substr(to_char(sysdate-1,'day'),1,3)) from dual;
select to_char(sysdate-3,'yyyy') from dual;
select to_char(sysdate-3,'Mon') from dual;
select to_char(sysdate-3,'dd') from dual;
select initcap(substr(to_char(sysdate-3,'day'),1,3)) from dual;
spool off
set echo on
set feedback on
set heading on
set pagesize 1000
set linesize 1000
set trimspool off
EOF

if [ $today != "Mon" ]; then
year=`sed -n '1p' date.txt`   
month=`sed -n '2p' date.txt`               
day=`sed -n '3p' date.txt`           
weekday=`sed -n '4p' date.txt`               
else
year=`sed -n '5p' date.txt`                   
month=`sed -n '6p' date.txt`                               
day=`sed -n '7p' date.txt`                           
weekday=`sed -n '8p' date.txt`                               
fi

if [ "$day" -lt "10" ]; then
nday=`echo $day | cut -c2`
sed -n "/$weekday $month  $nday [0-9][0-9]:[0-9][0-9]:[0-9][0-9] $year/=" $alertfile > temp
else
sed -n "/$weekday $month $day [0-9][0-9]:[0-9][0-9]:[0-9][0-9] $year/=" $alertfile > temp
fi

if [ -s temp ]; then
beginline=`head -1 temp`                               
tail +"$beginline" $alertfile /
 | sed 's/Mon [A-Z][a-z][a-z] /*sdmei* &/p' | uniq  /
 | sed 's/Tue [A-Z][a-z][a-z] /*sdmei* &/p' | uniq  /
 | sed 's/Wed [A-Z][a-z][a-z] /*sdmei* &/p' | uniq  /
 | sed 's/Thu [A-Z][a-z][a-z] /*sdmei* &/p' | uniq  /
 | sed 's/Fri [A-Z][a-z][a-z] /*sdmei* &/p' | uniq  /
 | sed 's/Sat [A-Z][a-z][a-z] /*sdmei* &/p' | uniq  /
 | sed 's/Sun [A-Z][a-z][a-z] /*sdmei* &/p' | uniq  /
> alert
 else
tail -5000 $alertfile  /
 | sed 's/Mon [A-Z][a-z][a-z] /*sdmei* &/p' | uniq  /
 | sed 's/Tue [A-Z][a-z][a-z] /*sdmei* &/p' | uniq  /
 | sed 's/Wed [A-Z][a-z][a-z] /*sdmei* &/p' | uniq  /
 | sed 's/Thu [A-Z][a-z][a-z] /*sdmei* &/p' | uniq  /
 | sed 's/Fri [A-Z][a-z][a-z] /*sdmei* &/p' | uniq  /
 | sed 's/Sat [A-Z][a-z][a-z] /*sdmei* &/p' | uniq  /
 | sed 's/Sun [A-Z][a-z][a-z] /*sdmei* &/p' | uniq  /
> alert

fi

if [ `cat alert | grep ORA- | wc -l` -gt "0" ]; then
  
point1=1
point2=2
sed -n '/*sdmei*/=' alert > templine
totline=`cat templine | wc -l`
while [ $point1 -lt $totline ]
do
    firstline=`sed -n "${point1}p" templine`
    secondline=`sed -n "${point2}p" templine`
    secondline2=`expr $secondline - 1`
    oraline=`sed -n "${firstline},${secondline2}p" alert | grep ORA- | wc -l`
    if [ $oraline -gt "0" ]; then
        sed -n "${firstline},${secondline2}p" alert | sed 's//*sdmei/*/----------/g' >> $APP_NAME"."$FORMAT
    fi
    point1=`expr $point1 + 1`
    point2=`expr $point2 + 1`

done
rm -f templine

else
    echo "no errors" >> $APP_NAME"."$FORMAT
fi
rm -f alert
rm -f date.txt
rm -f temp

echo '/n/n--------------------------------------------------/n/n' >> $APP_NAME"."$FORMAT
}


job2()
{
echo '2.表空间可用空间信息 ' >> $APP_NAME"."$FORMAT
echo '/n/n--------------------------------------------------/n/n' >> $APP_NAME"."$FORMAT
CHECKSQL=$DAILY_CHECK/checkspace.sql
echo "set linesize 180" > $CHECKSQL
echo "col tablespace_name for a60" >> $CHECKSQL
echo "set pagesize 9999" >> $CHECKSQL
echo "set wrap off" >> $CHECKSQL
echo "select" >> $CHECKSQL
echo "tablespace_name," >>$CHECKSQL
echo "megs_alloc  init_sizeM," >>$CHECKSQL
echo "max  total_sizeM," >>$CHECKSQL
echo "max-megs_used free_sizeM," >>$CHECKSQL
echo "megs_used used_sizeM," >>$CHECKSQL
echo "round((max-megs_used)/(max+0.001) *100,1) free_rate" >>$CHECKSQL
echo "from" >>$CHECKSQL
echo "(select  a.tablespace_name," >>$CHECKSQL
echo "round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc," >>$CHECKSQL
echo "round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free," >>$CHECKSQL
echo "round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used," >>$CHECKSQL
echo "round(maxbytes/1048576,2) Max" >>$CHECKSQL
echo "from  ( select  f.tablespace_name," >>$CHECKSQL
echo "         sum(f.bytes) bytes_alloc," >>$CHECKSQL
echo "         sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes" >>$CHECKSQL
echo "  from dba_data_files f" >>$CHECKSQL
echo "  group by tablespace_name) a," >>$CHECKSQL
echo "( select  f.tablespace_name," >>$CHECKSQL
echo "         sum(f.bytes)  bytes_free" >>$CHECKSQL
echo "  from dba_free_space f" >>$CHECKSQL
echo "  group by tablespace_name) b" >>$CHECKSQL
echo "where a.tablespace_name = b.tablespace_name (+)" >>$CHECKSQL
echo "union all" >>$CHECKSQL
echo "select tablespace_name," >>$CHECKSQL
echo "       round(sum(AllowUseSize_MB),2) megs_alloc," >>$CHECKSQL
echo "       round(sum(FreeSize_MB),2) megs_free," >>$CHECKSQL
echo "       round(sum(UsedSize_MB),2) megs_used," >>$CHECKSQL
echo "       round(sum(MaxSize_MB),2) max" >>$CHECKSQL
echo " from" >>$CHECKSQL
echo "  ( select d.tablespace_name, d.file_name," >>$CHECKSQL
echo "         round(decode(d.autoextensible, 'YES', greatest(d.maxbytes, d.bytes), d.bytes)/1024/1024,2) MaxSize_MB," >>$CHECKSQL
echo "         round(d.bytes/1024/1024,2) TotalSize_MB," >>$CHECKSQL
echo "         nvl(round((d.bytes-p.bytes_cached)/1024/1024,2),0) FreeSize_MB," >>$CHECKSQL
echo "         nvl(round(p.bytes_cached/1024/1024,2),0) UsedSize_MB," >>$CHECKSQL
echo "         nvl(round((decode(d.autoextensible, 'YES', greatest(d.maxbytes, d.bytes), d.bytes) - p.bytes_cached)/1024/1024,2),0) AllowUseSize_MB," >>$CHECKSQL
echo "         --round((user_bytes/bytes)*100,2) UsedPercent," >>$CHECKSQL
echo "         user_blocks * ts.block_size/1024/1024 Fragment_MB" >>$CHECKSQL
echo "    from dba_temp_files d, v/$temp_extent_pool p, dba_tablespaces ts" >>$CHECKSQL
echo "   where d.tablespace_name = p.tablespace_name(+)" >>$CHECKSQL
echo "     and d.file_id = p.file_id(+)" >>$CHECKSQL
echo "     and d.tablespace_name = ts.tablespace_name(+))" >>$CHECKSQL
echo "  group by tablespace_name)" >>$CHECKSQL
echo "order by 6" >>$CHECKSQL
echo "/" >> $CHECKSQL
echo "exit" >>$CHECKSQL
sqlplus "$SQLUSER" @$CHECKSQL >> $APP_NAME"."$FORMAT

rm -f $CHECKSQL
}

job3()
{
echo '/n/n' >> $APP_NAME"."$FORMAT
#3、数据库的alert.log大小检查
echo " alert.log checking processing..."
echo "/n" >> $APP_NAME"."$FORMAT
echo '3. check alert_$ORACLE_SID.log size(M)' >> $APP_NAME"."$FORMAT
echo '--------------------------------------------------/n' >> $APP_NAME"."$FORMAT
if [ -f $ALTERFILE ]
 then
ls -l $ALTERFILE|awk '{print $5}'|awk '{printf("%.2fM/n",$1/1048576);}' >> $APP_NAME"."$FORMAT
fi

echo '/n--------------------------------------------------/n' >> $APP_NAME"."$FORMAT
#4、数据库的listener.log大小检查
echo "listener.log is processing..."
echo 'check listener.log size(M)' >> $APP_NAME"."$FORMAT
echo "/n" >> $APP_NAME"."$FORMAT
if [ -f $ORACLE_HOME/network/log/$ORACLE_SID.log ]
 then
ls -l $ORACLE_HOME/network/log/$ORACLE_SID.log|awk '{print $5}'|awk '{printf("%.2fM/n",$1/1048576);}' >> $APP_NAME"."$FORMAT
echo "/n" >> $APP_NAME"."$FORMAT
fi
if [ -f $ORACLE_HOME/network/log/listener.log ]
 then
ls -l $ORACLE_HOME/network/log/listener.log|awk '{print $5}'|awk '{printf("%.2fM/n",$1/1048576);}' >> $APP_NAME"."$FORMAT
fi

echo '/n--------------------------------------------------/n' >> $APP_NAME"."$FORMAT
#5、数据库sqlnet.log大小检查
echo " sqlnet.log checking processing..."
echo 'check sqlnet.log size(M)' >> $APP_NAME"."$FORMAT
echo "/n" >> $APP_NAME"."$FORMAT
if [ -f $ORACLE_HOME/network/log/sqlnet.log ]
 then
ls -l $ORACLE_HOME/network/log/sqlnet.log|awk '{print $5}'|awk '{printf("%.2fM/n",$1/1048576);}' >> $APP_NAME"."$FORMAT
else
   echo "no sqlnet.log on this database" >> $APP_NAME"."$FORMAT
fi

echo '/n--------------------------------------------------/n' >> $APP_NAME"."$FORMAT

#6.delete outdated trc and core and aud file

echo "delete .trc file processing..."
find $ORACLE_BASE/admin -name "*.trc" -mtime +15 -exec rm -f {} /;
find $ORACLE_BASE/admin -name "*.aud" -mtime +15 -exec rm -f {} /;
find $ORACLE_BASE/admin -name "*.trw" -mtime +15 -exec rm -f {} /;
find $ORACLE_BASE/admin -name "core_*" -mtime +15 -exec rm -rf {} /;
echo 'delete 15 days off .trc file' >> $APP_NAME"."$FORMAT
echo 'delete successfully!!!  ' >> $APP_NAME"."$FORMAT

}


job4()
{
echo '/n----------------------------------------------------------------------------------------------------------------------/n' >> $APP_NAME"."$FORMAT
echo "database check running......"
echo "database check running......" >> $APP_NAME"."$FORMAT
JOB4SQL=$DAILY_CHECK/job4.sql
echo "prompt ----------------------------------------------------------------------------------------------------------------------" >$JOB4SQL
echo "/n/n/nprompt #################### invalid objects ####################" >>$JOB4SQL
echo "set linesize 220 pagesize 9999" >>$JOB4SQL
echo "col owner for a27" >>$JOB4SQL
echo "break on owner skip page on report" >>$JOB4SQL
echo "compute sum label 'sum of owner cnt' of cnt on owner" >>$JOB4SQL
echo "compute sum label 'sum of all cnt' of cnt on report " >>$JOB4SQL
echo "" >>$JOB4SQL
echo "select owner,object_type,status,count(*) cnt from dba_objects" >>$JOB4SQL
echo "where owner not in" >>$JOB4SQL
echo "('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS'," >>$JOB4SQL
echo "'ORACLE_OCM','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','PUBLIC','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM'," >>$JOB4SQL
echo "'TSMSYS','WMSYS','WKSYS','XDB') " >>$JOB4SQL
echo "group by owner,object_type,status order by 1,2,3;" >>$JOB4SQL
echo "" >>$JOB4SQL
echo "prompt ----------------------------------------------------------------------------------------------------------------------" >>$JOB4SQL
echo "/n/n/n prompt #################### appuser statistics ####################" >>$JOB4SQL
echo "--#2 应用账号的统计更新情况" >>$JOB4SQL
echo "select  owner,min(LAST_ANALYZED),max(LAST_ANALYZED) from dba_tables" >>$JOB4SQL
echo "where owner not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','LBACSYS','MDDATA','MDSYS','MGMT_VIEW'," >>$JOB4SQL
echo "'OLAPSYS','ORACLE_OCM','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','PUBLIC','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM'," >>$JOB4SQL
echo "'TSMSYS','WMSYS','WKSYS','XDB')  group by owner;" >>$JOB4SQL
echo "" >>$JOB4SQL
echo "prompt ----------------------------------------------------------------------------------------------------------------------" >>$JOB4SQL
echo "/n/n/n prompt #################### datafile check  #################### /n" >>$JOB4SQL
echo "--#3 数据文件状态" >>$JOB4SQL
echo "SELECT a.file_name,a.status,b.status" >>$JOB4SQL
echo "FROM dba_data_files a,v/$datafile b" >>$JOB4SQL
echo "WHERE a.file_id=b.file# and a.status !='AVAILABLE'" >>$JOB4SQL
echo "UNION" >>$JOB4SQL
echo "SELECT a.file_name,a.status,b.status" >>$JOB4SQL
echo "FROM dba_data_files a,v/$datafile b" >>$JOB4SQL
echo "WHERE a.file_id=b.file# and b.status not in('SYSTEM','ONLINE');" >>$JOB4SQL
echo "" >>$JOB4SQL
echo "" >>$JOB4SQL
echo "prompt ----------------------------------------------------------------------------------------------------------------------" >>$JOB4SQL
echo "/n/n/n prompt #################### corrupt block  ####################" >>$JOB4SQL
echo "--#4 数据库坏块情况" >>$JOB4SQL
echo "SELECT a.file_name,b.file#,b.block#,b.blocks,b.corruption_change#, b.CORRUPTION_TYPE" >>$JOB4SQL
echo "FROM dba_data_files a,v/$database_block_corruption b" >>$JOB4SQL
echo "WHERE a.file_id=b.file#;" >>$JOB4SQL
echo "" >>$JOB4SQL
echo "" >>$JOB4SQL
echo "prompt ----------------------------------------------------------------------------------------------------------------------" >>$JOB4SQL
echo "/n/n/n prompt #################### archived log  ####################" >>$JOB4SQL
echo "--#5 数据库日日志产生量" >>$JOB4SQL
echo "set linesize 220" >>$JOB4SQL
echo "set pagesize 9999" >>$JOB4SQL
echo "break on hour on report" >>$JOB4SQL
echo "compute sum label 'sum of archivelog' of sizeM on report" >>$JOB4SQL
echo "select trunc(a.FIRST_TIME,'HH') hour,a.thread#,count(distinct a.sequence#) cnt_archivedlog," >>$JOB4SQL
echo "sum(round(b.BLOCKS*B.BLOCK_SIZE/1024/1024,2)) sizeM" >>$JOB4SQL
echo "from v/$log_history a,v/$archived_log b" >>$JOB4SQL
echo "where a.FIRST_TIME between trunc(sysdate-1,'DD') and trunc(sysdate,'DD')" >>$JOB4SQL
echo " and  a.thread#=b.thread#" >>$JOB4SQL
echo " and  a.sequence#=b.sequence#" >>$JOB4SQL
echo " and  (b.dest_id=10 or b.dest_id=1)" >>$JOB4SQL
echo "group by trunc(a.FIRST_TIME,'HH'),a.thread#" >>$JOB4SQL
echo "order by 1,2;" >>$JOB4SQL
echo "" >>$JOB4SQL
echo "prompt ----------------------------------------------------------------------------------------------------------------------" >>$JOB4SQL
echo "/n/n/n prompt #################### 2G or 2KWrows table  ####################" >>$JOB4SQL
echo "--#6 超过2G或大于2kw记录的表" >>$JOB4SQL
echo "set linesize 220" >>$JOB4SQL
echo "col owner for a22" >>$JOB4SQL
echo "col table_name for a30" >>$JOB4SQL
echo "select a.owner,a.table_name,a.num_rows,a.last_analyzed,a.PARTITIONED,sum(round(b.bytes/1024/1024,2)) sizeM" >>$JOB4SQL
echo "from dba_tables a,dba_segments b" >>$JOB4SQL
echo "where a.table_name=b.segment_name" >>$JOB4SQL
echo "  and a.owner=b.owner" >>$JOB4SQL
echo "  --and a.PARTITIONED='NO'" >>$JOB4SQL
echo "  and a.num_rows>20000000" >>$JOB4SQL
echo "group by a.owner,a.table_name,a.num_rows,a.last_analyzed,a.PARTITIONED" >>$JOB4SQL
echo "--having sum(round(b.bytes/1024/1024,2))>2*1024*1024*1024" >>$JOB4SQL
echo "order by 1,5;" >>$JOB4SQL
echo "" >>$JOB4SQL

echo "prompt ----------------------------------------------------------------------------------------------------------------------" >>$JOB4SQL
echo "/n/n/n prompt #################### row_chain table  ####################" >>$JOB4SQL
echo "--#7 行迁移行链接表" >>$JOB4SQL
echo "set linesize 220" >>$JOB4SQL
echo "select owner,table_name,num_rows,last_analyzed,chain_cnt from dba_tables where chain_cnt>0;" >>$JOB4SQL
echo "" >>$JOB4SQL
echo "" >>$JOB4SQL
echo "prompt ----------------------------------------------------------------------------------------------------------------------" >>$JOB4SQL
echo "/n/n/n prompt #################### appuser auth  ####################" >>$JOB4SQL
echo "#8 应用账号权限检查" >>$JOB4SQL
echo "break on grantee skip page on report " >>$JOB4SQL
echo "set linesize 180 pagesize 9999" >>$JOB4SQL
echo "col grantee for a23" >>$JOB4SQL
echo "col privilege for a26" >>$JOB4SQL
echo " select grantee,privilege from dba_sys_privs where grantee not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS'," >>$JOB4SQL
echo " 'LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA'," >>$JOB4SQL
echo " 'SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','WKSYS','XDB') and  grantee not in (select role from dba_roles)" >>$JOB4SQL
echo "union" >>$JOB4SQL
echo "select grantee,GRANTED_ROLE privilege from dba_role_privs where grantee not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS'," >>$JOB4SQL
echo "'EXFSYS','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA'," >>$JOB4SQL
echo "'SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','WKSYS','XDB') and  grantee not in (select role from dba_roles)" >>$JOB4SQL
echo "order by 1,2;" >>$JOB4SQL

echo "exit;" >> $JOB4SQL

sqlplus "$SQLUSER" @$JOB4SQL >> $APP_NAME"."$FORMAT

rm -f $JOB4SQL

}

job5()
{
echo '5.表空间可用空间信息 ' >> $APP_NAME"."$FORMAT
echo '/n/n#################### free space < 5% ####################/n/n' >> $APP_NAME"."$FORMAT
CHECKSQL=$DAILY_CHECK/checkspace.sql
echo "set linesize 180" > $CHECKSQL
echo "col tablespace_name for a60" >> $CHECKSQL
echo "set pagesize 9999" >> $CHECKSQL
echo "set wrap off" >> $CHECKSQL
echo "select" >> $CHECKSQL
echo "tablespace_name," >>$CHECKSQL
echo "megs_alloc  init_sizeM," >>$CHECKSQL
echo "max  total_sizeM," >>$CHECKSQL
echo "max-megs_used free_sizeM," >>$CHECKSQL
echo "megs_used used_sizeM," >>$CHECKSQL
echo "round((max-megs_used)/(max+0.001) *100,1) free_rate" >>$CHECKSQL
echo "from" >>$CHECKSQL
echo "(select  a.tablespace_name," >>$CHECKSQL
echo "round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc," >>$CHECKSQL
echo "round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free," >>$CHECKSQL
echo "round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used," >>$CHECKSQL
echo "round(maxbytes/1048576,2) Max" >>$CHECKSQL
echo "from  ( select  f.tablespace_name," >>$CHECKSQL
echo "         sum(f.bytes) bytes_alloc," >>$CHECKSQL
echo "         sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes" >>$CHECKSQL
echo "  from dba_data_files f" >>$CHECKSQL
echo "  group by tablespace_name) a," >>$CHECKSQL
echo "( select  f.tablespace_name," >>$CHECKSQL
echo "         sum(f.bytes)  bytes_free" >>$CHECKSQL
echo "  from dba_free_space f" >>$CHECKSQL
echo "  group by tablespace_name) b" >>$CHECKSQL
echo "where a.tablespace_name = b.tablespace_name (+)" >>$CHECKSQL
echo "union all" >>$CHECKSQL
echo "select tablespace_name," >>$CHECKSQL
echo "       round(sum(AllowUseSize_MB),2) megs_alloc," >>$CHECKSQL
echo "       round(sum(FreeSize_MB),2) megs_free," >>$CHECKSQL
echo "       round(sum(UsedSize_MB),2) megs_used," >>$CHECKSQL
echo "       round(sum(MaxSize_MB),2) max" >>$CHECKSQL
echo " from" >>$CHECKSQL
echo "  ( select d.tablespace_name, d.file_name," >>$CHECKSQL
echo "         round(decode(d.autoextensible, 'YES', greatest(d.maxbytes, d.bytes), d.bytes)/1024/1024,2) MaxSize_MB," >>$CHECKSQL
echo "         round(d.bytes/1024/1024,2) TotalSize_MB," >>$CHECKSQL
echo "         nvl(round((d.bytes-p.bytes_cached)/1024/1024,2),0) FreeSize_MB," >>$CHECKSQL
echo "         nvl(round(p.bytes_cached/1024/1024,2),0) UsedSize_MB," >>$CHECKSQL
echo "         nvl(round((decode(d.autoextensible, 'YES', greatest(d.maxbytes, d.bytes), d.bytes) - p.bytes_cached)/1024/1024,2),0) AllowUseSize_MB," >>$CHECKSQL
echo "         --round((user_bytes/bytes)*100,2) UsedPercent," >>$CHECKSQL
echo "         user_blocks * ts.block_size/1024/1024 Fragment_MB" >>$CHECKSQL
echo "    from dba_temp_files d, v/$temp_extent_pool p, dba_tablespaces ts" >>$CHECKSQL
echo "   where d.tablespace_name = p.tablespace_name(+)" >>$CHECKSQL
echo "     and d.file_id = p.file_id(+)" >>$CHECKSQL
echo "     and d.tablespace_name = ts.tablespace_name(+))" >>$CHECKSQL
echo "  group by tablespace_name)" >>$CHECKSQL
echo "where free_rate<5.0" >>$CHECKSQL
echo "/" >> $CHECKSQL
echo "exit" >>$CHECKSQL
sqlplus "$SQLUSER" @$CHECKSQL >> $APP_NAME"."$FORMAT

rm -f $CHECKSQL
}


# -----------------------------------------------------------------------------------------------------------
# collect database information by functions

for JOB in $JOB_LIST
do
  $JOB
done
 

echo "ended successfully" >> $APP_NAME"."$FORMAT


# -----------------------------------------------------------------------------------------------------------
# send result files by ftp including those failed to be sent before
# and get the newest version of this shell script

cd $DAILY_CHECK
ftp -in <<!
open $FTP_DEST_HOST
user $FTP_USER $FTP_PWD
cd $FTP_DEST_DIR
bin
mput $APP_NAME".*."$SUFFIX
cd ..
get check_db_alert.sh
bye
!
if [ $? != "0" ]
then
 echo "ERROR: send result files by ftp to destination failed"
 exit 1
fi


# -----------------------------------------------------------------------------------------------------------
# rename result file after successful ftp

for FILENAME in $DAILY_CHECK"/"$APP_NAME.*.$SUFFIX
do
  mv $FILENAME $FILENAME"."$SUFFIX1
done

find $DAILY_CHECK -name "*.log" -mtime +15 -exec rm -f {} /;

# -----------------------------------------------------------------------------------------------------------
# record finish date and time

echo 'database information collect finished at:'; date
echo "------------------------------------------------------------"

#crontab -e
#05 08,16 * * * sh /tmp/db_dailycheck/check_db_alert.sh ryxdb 10.203.4.180 >> /tmp/db_dailycheck/check_db_alert.log 2>&1

freshairpeng
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
shell脚本可配置备份oracle数据库.rar
05-12
shell脚本可配置备份oracle数据库，完全可配置，使用方便。
oracle 表空间查看脚本
weixin_33845881的博客
11-15 238
#!/bin/bashecho -e "\n\n*********~~~~~~~~~~~~~~~~~~~~~~~~~~~~DATE:" `date '+%Y/%m/%d %T'` "~~~~~~~~~~~~~~~~~~~~~~~*********" >>/home/oracle/tablespace.logexport PATH=/opt...
Linux下Oracle数据库自动备份Shell脚本
StarLOVELeaf的博客
10-17 1811
1.在服务器创建备份目录，并赋予权限 mkdir -p /backup/P_DATA  #新建shell脚本存放路径 mkdir -p /backup/P_DB_BACK  #新建Oracle数据库备份目录 chown -R oracle:oinstall /backup/P_DATA    #设置目录权限为oinstall用户组的oracle用户（用户oracle与用户组oinstall是在安装...
linux 下RMAN备份shell脚本
热门推荐
乐沙弥的世界
09-28 2万+
RMAN备份对于Oracle数据库的备份与恢复简单易用，成本低廉。对于使用非catalog方式而言，将RMAN脚本嵌入到shell脚本，然后再通过crontab来实现中小型数据库数据库备份无疑是首选。本文提供了一个简单易用的基于linux shell下的RMAN备份脚本供参考。大家可根据自己的需要进行适当调整。 RMAN备份相关方面的知识较多，可以参考：    RMAN 概述及其体系结构    R
rman备份脚本shell版
weixin_34376562的博客
11-15 145
1、数据库全备到硬盘 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 [oracle@centos7scripts]$catrman_backup.sh source/home/oracle/.bash_profile rmantarget...
linux下oracle备份的shell脚本
12-05
linux下oracle备份的shell脚本，带压缩功能，附带操作说明，只需要会cv
Oracle Rman 备份监控shell脚本
最新发布
10-24
此部分为针对Oracle rman 备份的监控脚本，此脚本为shell scripts，通过linux 系统的shell command 进行监控每次备份的状态并发送邮件进行通知。
ORACLE RMAN本地备份shell脚本
06-02
RMAN本地备份shell脚本
linux环境下，oracle自动备份的shell脚本
03-21
linux环境下，oracle自动备份的shell脚本
oracle读取表空间物理文件中数据,shell脚本读取oracle数据库数据写入到文件中
weixin_31621945的博客
04-02 113
1、linux 需要用 sqlplus 客户端去连接oracle 数据库，首先需要确认有没有安装：which sqlplus2、如果没有安装就需要先安装一下(百度)3、配置环境变量：vim /etc/profile4、执行 source /etc/profile5、whereis oracle #查看oracle 客户端安装路径6、进入客户端目录7、编辑配置文件：vim tnsnames.or...
Oracle表空间备份及还原
04-02
NULL 博文链接：https://ajita.iteye.com/blog/1458678
脚本实现查看表空间使用情况
cs20080088的博客
03-26 186
#!/bin/bash source ~/.bash_profile . /home/ora10/system_tablespace sqlplus -S oracle/hao12345@hxy < col ...
实战:oracle新建表空间的shell脚本
yangzhawen
09-25 2158
日常维护中，oracle新建表空间的shell脚本 ！
ORA-04063解决方法
pengshk 专注于oracle ebs dba跟BI
04-16 1万+
1.alert.log check---------- Tue Feb 23 05:05:46 2010Errors in file /u01/oracle/admin/chnldb/udump/chnldb1_ora_1376882.trc:ORA-00600: 錗-^FM-^E镸-^CㄩM-^TM-^Y璇唬鐮M-^A, 錗-^OM-^B鍹-^U? [13011], [3343517], [1
sqlload 介绍
pengshk 专注于oracle ebs dba跟BI
04-18 9854
在 Oracle 数据库中，我们通常在不同数据库的表间记录进行复制或迁移时会用以下几种方法：1. A 表的记录导出为一条条分号隔开的 insert 语句，然后执行插入到 B 表中2. 建立数据库间的 dblink，然后用 create table B as select * from A@dblink where ...，或 insert into B select * from A@dbli
oracle temp表空间处理方法
pengshk 专注于oracle ebs dba跟BI
04-19 5129
一．检查过程(1) 我们可以查查数据库alert日志例如：/u0/admin/sm/bdump/alert_sm.log数据库中并没有报temp表空间的错，一般来说不报错，可以先不管，因为没有temp表空间可能由于没有需要用到而不释放空间。(2) 我们可以用这个语句查一下SELECT se.username,sid,serial#,sql_address,machine,program,tab
Orackle 10g 数据库DataGuard配置规范
pengshk 专注于oracle ebs dba跟BI
04-18 3053
Orackle 10g 数据库DataGuard配置规范目录：第一部分：文档信息第二部分：配置DataGuard需要的硬件和系统要求第三部分：Oracle10g的DataGuard的几个概念第四部分: 创建物理备用数据库和启动DataGuard的具体步骤第五部分: 对DataGuard日常监控的要求第六部分: 备用数据库的角色切换步骤第七部分: 在最大性能模式下的D
ASM转文件系统方法四种方法
pengshk 专注于oracle ebs dba跟BI
04-16 2135
1.ASM Access Through FTP and HTML Using XDB Configuration 2.rman3.dbms_file_transfer4.11g asmcmd利用RMAN将数据库从文件系统迁移到ASM一、打开ASM实例[oracle@ASM ~]$ echo $ORACLE_SID+ASM[oracle@ASM ~]$ sqlplus /nolog
shell脚本到处oracle数据
09-08
通过使用shell脚本连接数据库，我们可以将Oracle数据库中的数据导出到其他地方，例如FTP服务器。首先，需要在Linux环境中安装sqlplus客户端来连接Oracle数据库。 使用shell脚本导出Oracle数据的方法有多种，其中一...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值