自动收集数据库应用系统信息的脚本

小灰灰 2022-08-08 05:08 137阅读 0赞

总结一种自动收集数据库应用系统信息的方法,提高远程系统维护的效率. (以下内容仅在suse linux + oracle 11g上测试通过)

功能:可以收集某应用程序的进程信息,配置信息;  硬盘设备信息; 数据库进程信息,表空间信息,以及特定数据表的内容,以及指定的sql语句的执行结果.

使用方法: 以root用户将以下两个文件上传至同一目录，再执行rpt\_support.sh，即可将信息收集到 "主机名-时间.tar".

文件1: rpt\_support.ini

\#application configure: the user of Application system  
APPLICATION\_USER=ap\_user

\#prestat configure: the path which Application installed in  
APPLICATION\_SETUP\_PATH=/home/ap

\#database configure: the OS User of Oracle Database  
ORACLE\_USER=oracle

\#database configure: the user name of database  
DATABASE\_USER=db\_user\_name

\#database configure: the user's password of database  
DATABASE\_USER\_PASSWORD=db\_user\_passwd

\#database configure: the important tables should be feedback,multi-table should be separated by ","  
DATABASE\_TABLES=t\_info\_version,t\_sys\_config,t\_info\_config,t\_runlog,

文件2: rpt\_support.sh

\#!/usr/bin/ksh

\#  ======================================================================  
\#  File name:   rpt\_Support.sh  
\#  Author:      xuwei  
\#  Version:      
\#  Date:        2009-06-30  
\#  Description: collect the info of oracle system,out put to result.txt  
\#  Content:        
\#  Others:         NULL  
\#  History:  
\#    1.    Date:        2009-06-30  
\#          Author:      xuwei  
\#          Description: initial  
\# ======================================================================

\# include configures  
BASEDIR=\`pwd\`  
\#BASEDIR=\`dirname $path\`  
CONFIG\_FILE=$BASEDIR/rpt\_support.ini  
. $CONFIG\_FILE

HOSTNAME=\`hostname\`  
TIMESTAMP=\`date +%Y%m%d%H%M\`  
USER\_LOG\_FILE=$BASEDIR/$\{HOSTNAME\}\_$\{TIMESTAMP\}.log

\# description: log a message to the user defined log file  
function WriteLog  
\{  
    if \[ ! -f "$USER\_LOG\_FILE" \]  
    then  
        touch $USER\_LOG\_FILE  
    fi

\#   print "$(date '+%Y%m%d%H%M') - Node /"$(HOSTNAME)/": /c" >> $USER\_LOG\_FILE  
    print $\* >> $USER\_LOG\_FILE  
\#   logfz=\`ls -l $USER\_LOG\_FILE| awk '\{printf "%s", $5\}'\`  
\#   if \[ $logfz -ge $MAX\_LOG\_FILE\_SIZE \]  
\#   then  
\#       mv $USER\_LOG\_FILE $USER\_LOG\_FILE.old  
\#       echo "-------backup App HA Agent log file------" >> $USER\_LOG\_FILE.old  
\#       touch $USER\_LOG\_FILE  
\#   fi  
\}

\# description: log a OS command to the log file  
function WriteCMDLog  
\{  
    print "\#$(whoami)> $\*" >> $USER\_LOG\_FILE  
    print "$($\*)" >> $USER\_LOG\_FILE  
\}

\# description: write a sql text, which be executed by sqlplus.  
function WriteSqlFile  
\{  
    print "conn / as sysdba"             > $1  
    print "set pagesize 10000;"          >> $1  
    print "set linesize 400;"            >> $1  
    print "set feedback off;"            >> $1  
    print "spool ora\_$\{HOSTNAME\}.log;"   >> $1  
    print "select tablespace\_name, bytes / (1024\*1024) TotalSpace\_MB from sm/$ts\_avail;" >> $1  
    print "select tablespace\_name, bytes / (1024\*1024) UsedSpace\_MB from sm/$ts\_used;"   >> $1  
    print "select tablespace\_name, bytes / (1024\*1024) FreeSpace\_MB from sm/$ts\_free;"   >> $1  
    print "select resource\_type,limit from dba\_profiles where profile = 'DEFAULT' and resource\_name = 'PASSWORD\_LIFE\_TIME';" >> $1  
    print "select resource\_type,limit from dba\_profiles where profile = 'DEFAULT' and resource\_name = 'FAILED\_LOGIN\_ATTEMPTS';" >> $1  
    print "show parameter spfile;"      >> $1  
    print "show parameter audit\_trail;" >> $1  
    print "show parameter recyclebin;"  >> $1  
    print "conn $\{DATABASE\_USER\}/$\{DATABASE\_USER\_PASSWORD\};" >> $1

TABLE\_NAME=\`echo $\{DATABASE\_TABLES\} | awk -F, \{'print $1'\}\`  
    while \[ "X" != "X$\{TABLE\_NAME\}" \]  
      do  
         DATABASE\_TABLES=\`echo $\{DATABASE\_TABLES\} | awk -F, '\{print substr($0,index($0,",")+1)\}'\`  
         print "select \* from $\{TABLE\_NAME\};" >> $1  
         TABLE\_NAME=\`echo $\{DATABASE\_TABLES\} | awk -F, \{'print $1'\}\`  
      done

echo "exit;" >> $1  
\}

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Program Entry \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
    WriteLog "0.Get base info    =================================="  
    tar cf $HOSTNAME-$TIMESTAMP.tar $CONFIG\_FILE

WriteLog "1.collect disk info=================================="  
    WriteCMDLog "df -h"  
    WriteCMDLog "fdisk -l"  
    WriteCMDLog "vgdisplay"  
    WriteCMDLog "lvdisplay"

WriteLog "2.collect application process info======================="  
    USER\_EXISTS=\`cat /etc/passwd | grep "$\{APPLICATION\_USER\}:x:" | grep -v "grep"\` > /dev/null  
    if \[ "X" = "X$\{USER\_EXISTS\}" \]  
    then  
       WriteLog "error: user $\{APPLICATION\_USER\} not exists!"  
       print "warning:user /"$\{APPLICATION\_USER\}/" not exists! please reconfig this file /"$\{CONFIG\_FILE\}/"."  
    else  
       WriteCMDLog "su -c /"pshow/" - $\{APPLICATION\_USER\}"  
    fi

if \[ -f "$APPLICATION\_SETUP\_PATH/CSHRC" \] && \[ -d "$APPLICATION\_SETUP\_PATH/etc" \]  
    then  
       tar rf $HOSTNAME-$TIMESTAMP.tar $APPLICATION\_SETUP\_PATH/CSHRC  
       tar rf $HOSTNAME-$TIMESTAMP.tar $APPLICATION\_SETUP\_PATH/lib  
       tar rf $HOSTNAME-$TIMESTAMP.tar $APPLICATION\_SETUP\_PATH/etc  
       tar rf $HOSTNAME-$TIMESTAMP.tar $APPLICATION\_SETUP\_PATH/data  
    else  
      WriteLog "error: your Prestat software not installed in $\{APPLICATION\_SETUP\_PATH\}!"  
       print "warning:your Prestat software not installed in $\{APPLICATION\_SETUP\_PATH\}!"  
    fi

WriteLog "3.collect db info===================================="  
    USER\_EXISTS=\`cat /etc/passwd | grep "$\{ORACLE\_USER\}:x:"\` > /dev/null

\# check the oracle user name in OS  
    if \[ "X" = "X$\{USER\_EXISTS\}" \]  
    then  
       WriteLog "error: user $\{ORACLE\_USER\} not exists!"  
       print "warning:user /"$\{ORACLE\_USER\}/" not exists! please reconfig this file /"$\{CONFIG\_FILE\}/"."  
    else  
       \# check oracle process in OS  
       ps -ef | grep "$\{ORACLE\_USER\}" | grep "ora\_" | awk \{'print $8'\} > ora\_process\_$TIMESTAMP.log  
       tar rf $HOSTNAME-$TIMESTAMP.tar ora\_process\_$TIMESTAMP.log

\# get info from database  
       ORACLE\_BASE=\`more /etc/passwd | grep "$\{ORACLE\_USER\}:x:" | awk -F: \{'print $6'\}\` > /dev/null  
       SQLFILE=$ORACLE\_BASE/ora\_support.sql  
       WriteSqlFile "$\{SQLFILE\}"  
       chmod +rx "$\{SQLFILE\}"  
       su -c "sqlplus /nolog @$\{SQLFILE\}" - $\{ORACLE\_USER\} > /dev/null \#\#\#> ora\_db\_$TIMESTAMP.log  
       tar rf $HOSTNAME-$TIMESTAMP.tar $SQLFILE  
       tar rf $HOSTNAME-$TIMESTAMP.tar $ORACLE\_BASE/ora\_$\{HOSTNAME\}.log

rm -f ora\_process\_$TIMESTAMP.log  
       rm -f $SQLFILE  
       rm -f $ORACLE\_BASE/ora\_$\{HOSTNAME\}.log  
    fi

tar uf $HOSTNAME-$TIMESTAMP.tar $USER\_LOG\_FILE  
    rm -f $USER\_LOG\_FILE

echo ""  
    echo ""  
    echo "Attention: All info are collectd, please send $HOSTNAME-$TIMESTAMP.tar to supporter."  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# Program exit \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#