概述

今天主要分享一下兩個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 <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 <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 <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 <#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方面內容，感興趣的朋友可以關注下！