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

最新推荐文章于 2021-04-07 10:14:35 发布
最新推荐文章于 2021-04-07 10:14:35 发布
阅读量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
关注
专栏目录
shell脚本可配置备份oracle数据库.rar
05-12
shell脚本可配置备份oracle数据库，完全可配置，使用方便。
oracle 表空间查看脚本
weixin_33845881的博客
11-15 254
#!/bin/bashecho -e "\n\n*********~~~~~~~~~~~~~~~~~~~~~~~~~~~~DATE:" `date '+%Y/%m/%d %T'` "~~~~~~~~~~~~~~~~~~~~~~~*********" >>/home/oracle/tablespace.logexport PATH=/opt...
Linux下Oracle数据库自动备份Shell脚本
StarLOVELeaf的博客
10-17 1879
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 183
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...
Oracle Rman 备份监控shell脚本
最新发布
10-24
此部分为针对Oracle rman 备份的监控脚本，此脚本为shell scripts，通过linux 系统的shell command 进行监控每次备份的状态并发送邮件进行通知。
Shell脚本连接oracle数据库的实现代码
09-08
在许多场景下，我们可能需要使用Shell脚本来连接Oracle数据库并执行SQL查询，例如进行数据备份、监控或定期报告。本文将详细讲解如何在Shell脚本中实现这一功能。 首先，确保在Linux环境中安装了Oracle的SQL*Plus...
Linux oracle数据库自动备份自动压缩脚本代码
09-10
本文将详细介绍一个用于Linux Oracle数据库自动备份和自动压缩的脚本代码，帮助你理解其工作原理并应用于实际环境中。 首先，让我们分析提供的脚本代码： ```bash #!/bin/bash # 系统名称 sysname=gzsys # 备份...
ORACLE RMAN本地备份shell脚本
06-02
RMAN本地备份shell脚本
oracle读取表空间物理文件中数据,shell脚本读取oracle数据库数据写入到文件中
weixin_31621945的博客
04-02 172
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 201
#!/bin/bash source ~/.bash_profile . /home/ora10/system_tablespace sqlplus -S oracle/hao12345@hxy < col ...
实战:oracle新建表空间的shell脚本
yangzhawen
09-25 2195
日常维护中，oracle新建表空间的shell脚本 ！
Oracle 11g EXPDP、RMAN自动备份SHELL脚本
cuantangnie3915的博客
06-23 153
#!/bin/sh umask 022 set -o vi export ORACLE_SID=market export ORACLE_BASE=/app/usr/oracle export ORACLE_HOM...
shell怎么查询oracle数据库,通过shell脚本查看数据库表空间使用情况
weixin_39820588的博客
04-02 872
对于数据库中表空间查看，想必大家都有很多的脚本已经在用了，自己也啰嗦一下，分享一个通过shell脚本查看表空间使用情况的例子。脚本如下：sqlplus -s $DB_CONN_STR@$SH_DB_SID <set echo off heading on underline on;column inst_num heading "Inst Num" new_value inst_num...
oracle表空间监控shell,8个DBA最常用的监控Oracle数据库的常用shell脚本
weixin_30608641的博客
04-07 394
4. 清除旧的归档文件以下的脚本将会在log文件达到90%容量的时候清空旧的归档文件：$ df -k | grep archFilesystem kbytes used avail capacity Mounted on/dev/vx/dsk/proddg/archive 71123968 30210248 40594232 43% /u08/archive####################...
zabbix mysql 表空间_Zabbix监控达梦数据库表空间
weixin_42143221的博客
01-30 296
技术实现思路利用Zabbix监控工具的定制功能、借鉴Zabbix对Oracle的监控原理，实现达梦数据库表空间监控。通过自动发现脚本发现达梦数据库表空间，然后再制定额外的属于达梦表空间的监控项，配置告警触发器，最后在zabbix的Web页面端展示达梦表空间的使用情况。实战步骤自定义KEY监控#修改agent端配置文件vim /etc/zabbix/zabbix_agentd.confUnsafeU...
linux中shell定时任务执行oracle备份
football98的专栏
02-10 9454
linux中shell定时任务执行oracle备份。 1编写shell脚本 #!/bin/bash #:本脚本自动备份7天的数据库，每次备份完成后，删除7天之前的数据。编写脚本是否可以执行，请自行测试 #加载oracle的相关参数 #如果oracle参数不明确，请使用命令查看 # su - oracle  # echo $ORACLE_HOME export ORACLE_BASE
Oralce SQLPlus 格式化输出
花果山
08-11 9523
set echo on/off                     是否显示脚本中的需要执行的命令 set feedback on/off               是否显示 select 结果之后返回多少行的提示set linesize n                          设置一行最多显示多少字符，之前就是因为 n 设得过大，导致行与行之间有空白行set termout on/off                 在执行脚本时是否在屏幕上输出结果，如果 spool 到文件可以将其
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值