概述

今天主要分享一下两个shell脚本，主要是为了查看数据库进程的相关信息和存储过程信息，下面一起来看看吧~

数据库连接脚本

use script settdb.sh for DB login details registry

#!/bin/bash
tmp_username=$SH_USERNAME
tmp_password=$SH_PASSWORD
tmp_db_sid=$SH_DB_SID

#check $1 and $2 should be mandatory from input
if [[ -z $1 ]] || [[ -z $2 ]]; then
echo '***********************************************'
echo 'WARNING :UserName And PassWord Is Needed!'
echo '***********************************************'

exit
fi
if [[ -z $3 ]] && [[ -z $ORACLE_SID ]];then
echo '***********************************************'
echo 'WARNING :There is Instance can be used !'
echo '***********************************************'
exit
fi

SH_USERNAME=`echo "$1"|tr '[a-z]' '[A-Z]'`
SH_PASSWORD=$2
echo '***********************************************'

if [[ -z $3 ]]
then
 SH_DB_SID=$ORACLE_SID
 echo 'Using Default Instance :'$ORACLE_SID
 echo .
else
 SH_DB_SID=`echo "$3"|tr '[a-z]' '[A-Z]'`
fi

if [[ $SH_DB_SID = $tmp_db_sid ]] && [[ $SH_USERNAME = $tmp_username ]] && [[ $SH_PASSWORD = $tmp_password ]];then
 echo 'Instance '$SH_DB_SID 'has been connected'
 echo '***********************************************'
 exit
fi

export SH_USERNAME=$SH_USERNAME
export SH_DB_SID=$SH_DB_SID
export SH_PASSWORD=$SH_PASSWORD
export DB_CONN_STR=$SH_USERNAME/$SH_PASSWORD
#echo $DB_CONN_STR
listfile=`pwd`/listdb
Num=`echo show user | $ORACLE_HOME/bin/sqlplus -s $DB_CONN_STR@$SH_DB_SID| grep -i 'USER ' | wc -l`
if [ $Num -gt 0 ]
 then
 ## ok - instance is up
 echo 'Instance '$SH_DB_SID 'has been connected'
 echo -e '--' `date`'-- \n--'$SH_USERNAME@$SH_DB_SID 'has been connected --\n' >> listdb
 echo '***********************************************'
 echo 'Initalize DB login details registry OK!'
 echo 'Now you can Execution script~'
 echo '***********************************************'
 $SHELL

 else
 ## inst is inaccessible 
 echo Instance: $SH_DB_SID Is Invalid Or UserName/PassWord Is Wrong 
 echo '***********************************************'
 exit
 fi
del_length=3
tmp_txt=$(sed -n '$=' listdb) 
echo '***********************************************'
echo '********* ' $SH_USERNAME'@'$SH_DB_SID '**********'
echo '***********************************************'
curr_len=`cat $listfile|wc -l`
if [ $curr_len -gt $del_length ]; then
echo ' There Are Below Sessions Still Alive '
echo '***********************************************'
fi
sed $((${tmp_txt}-${del_length}+1)),${tmp_txt}d $listfile | tee tmp_listfile
mv tmp_listfile $listfile

输出：

./settdb.sh 用户名 用户密码

showpid.sh

脚本内容：

#!/bin/bash
echo "=================================================查看数据库进程spid：$1的相关信息====================================================="
if [ -z "$1" ]; then 
 echo "no process has provided!" 
 exit 0
fi
sh_tmp_process=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID <<END
set pagesize 0 feedback off verify off heading on echo off 
select addr from v\\$process where spid=$1;
exit; 
END` 

if [ -z "$sh_tmp_process" ]; then 
 echo "no process exists or session is not from a DB account" 
 echo 
 echo "####### Process Information from OS level as below ########" 
 ps -ef|grep $1|grep -v "grep"|grep ora
 echo "##############################################" 
 exit 0 
else 
 echo '*******************************************'
 echo "Process has found, pid: $1 , addr: $sh_tmp_process " 
 echo 
 echo "####### Process Information from OS level as below ########" 
 ps -ef|grep $1|grep -v grep|grep ora
 echo "##############################################" 
 sqlplus -s $DB_CONN_STR@$SH_DB_SID <<EOF
col machine format a20
col terminal format a15
col osuser format a15
col process format a10
col username format a15
set linesize 1000 pagesize 500
col type format a15
col login_time format a20
select sid,serial#,username,osuser ,machine,process,terminal,type,to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')login_time from v\$session
where paddr='$sh_tmp_process';
prompt .
col sql_id format a30
col prev_sql_id format a30
col sql_text format a60
set linesize 150
set pages 50
select sql_id,sql_text from v\$sql where sql_id in (select sql_id from v\$session where paddr='$sh_tmp_process' and sql_id is not null ) and rownum
<2;select sql_id prev_sql_id ,sql_text from v\$sql where sql_id in (select prev_sql_id sql_id from v\$session where paddr='$sh_tmp_process' ) and rown
um<2;EOF
fi

输出：./showpid.sh 数据库进程ID

这个一般要跟前面的会话脚本配合使用，主要是为了获得数据库进程更多明细。

showproc.sh

脚本内容：

一开始是想输出存储过程明细的，但是考虑到shell脚本看这些内容毕竟很痛苦，所以改成了去获得当前数据库所有存储过程，特定的存储过程还是用plsql看吧~

#!/bin/bash
echo "=======================================查看数据库用户$1的存储过程信息=========================================="
PROC_OWNER=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID <<END
set pagesize 100 feedback off verify off heading on echo off
col owner format a15
col object_name format a30
col object_type format a20
col parallel format a10
col interface format a10
col aggregate format a10
col pipelined format a10
col deterministic format a10
col authid format a10
set linesize 1000
select owner, object_name,object_id,object_type,aggregate,pipelined,parallel,interface,deterministic,authid from dba_procedures
where owner=upper('$1') and object_type='PROCEDURE'
/
exit;
END`

if [ -z "$PROC_OWNER" ]; then
 echo "no object exists, please check again"
 exit 0
else
 echo '*******************************************'
 echo " $PROC_OWNER "
 echo '*******************************************'
fi


#sqlplus -silent $DB_CONN_STR@$SH_DB_SID <<EOF
#prompt .
#set long 99999
#set pages 0
#select text
#from dba_source 
#where type='PROCEDURE' and owner=upper('$1')
#order by line;

#EOF
exit

输出：./showproc.sh rfuser

前段时间头条bug导致很多代码都没显示，所以后面会整理脚本后一次性发出来，这段时间先写一下。

后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下！