script1:
PATH=/usr/local/bin:/usr/bin:$PATH:.
. $HOME/utility/macro/macro.env
OVO_DIR=/tmp
LOGFILE=$REPORT_DIR/chk_conn.log_`date '+%y%m%d'` MAILLOG=$TMP_DIR/chk_conn.log_`date '+%y%m%d%H%M'` tmpfile=$TMP_DIR/chk_conn.tmp1
OVOLOG=$OVO_DIR/chk_conn.log OVOSMY=$OVO_DIR/chk_conn_smy.log
db_list="test" err_dblst="" sname=yidbmon
EMAIL_LST=$ADMIN_DIR/email.tst #EMAIL_LST=$ADMIN_DIR/email.lst
mail_flag=N WAIT_TIME=$1
rm $OVOLOG
touch $tmpfile $OVOLOG for db_name in ${db_list} do # 2b. Check whether connection is OK or not
$ORACLE_HOME/bin/sqlplus -s oper/oper123@${db_name} @$SCRIPT_DIR/chk_conn.sql > $tmpfile 2>&1 & sleep ${WAIT_TIME}
Msg="" dt1=`date '+%y/%m/%d %H:%M:%S'` dt2=`date '+%y%m%d%H%M%S'`
grep -i $db_name"-" $tmpfile >/dev/null
if [ $? -ne 0 ]; then mail_flag="Y"
echo "Unable to connect to database: " $db_name ".."${dt1} >> $MAILLOG
err_dblst=${err_dblst}" "$db_name"("${dt2}") "
else
echo "Connect to database successfully: " $db_name ".."${dt1} >> $MAILLOG
fi
done
echo "\n" >> $MAILLOG
grep -i "Unable to connect" $MAILLOG >> /dev/null
if [ $? -eq 0 ]; then
echo "Possible Causes:" >> $MAILLOG
echo "1. Scheduled shutdown." >> $MAILLOG
echo "2. Maybe the connection needs more than "${WAIT_TIME}" seconds to complete." >> $MAILLOG
echo "3. Database or listener is not up and running." >> $MAILLOG
echo "Actions:" >> $MAILLOG
echo "1. Please ignore this message if it is a scheduled shutdown." >> $MAILLOG
echo "2. Otherwise run program /macro/chk_db.sh to check database services on yictngd3 & yictngd4." >> $MAILLOG
echo "\n" >> $MAILLOG
sed -e 's/$/ /g' $MAILLOG >$tmpfile
cp $tmpfile $MAILLOG
for iname in `cat $EMAIL_LST`
do /bin/mailx -s "Failed to connect to database " $iname < $MAILLOG done
else
rm -f $tmpfile
fi
if [ "${err_dblst}" != "" ];
then
echo "critical Unable to connect to db : "${err_dblst}"
Check Email or log "${OVOLOG}" on server "$sname > $OVOLOG fi
cat $MAILLOG >> $LOGFILE cat $OVOLOG >> $OVOSMY
chmod 777 $OVOLOG
rm -f $MAILLOG $tmpfile
script2:
yidbmon1:/home/oracle [statdb] >more ./monitor/ngtools/se/se.sh
#! /bin/ksh
BASEDIR=/home/oracle/monitor/ngtools/se #
FILENAME=/home/oracle/monitor/ngtools/se/log/se.log FILENAME=/database/log/se_log/se_nGenpr.log
FIRSTFILTER=50
SECFILTER=30
PATH=/usr/local/bin:$PATH
ORACLE_SID=statdb
ORAENV_ASK=NO
. oraenv > /dev/null 2>&1
touch $FILENAME
#linecnt_bef=`wc -l $FILENAME| cut -f 1 -d ' '`
wc -l $FILENAME| read linecnt_bef other
while [ 1 = 1 ]
do clear
( sqlplus oper/oper123@nGenpr << SEEOF
@se_nGenpr.sql
SEEOF ) | grep '\|\|' | tee -a $FILENAME
#linecnt_aft=`wc -l $FILENAME| cut -f 1 -d ' '`
wc -l $FILENAME| read linecnt_aft other
if [ $linecnt_aft -lt $linecnt_bef ]
then
linecnt_bef=0
fi
DIFF=`expr $linecnt_aft - $linecnt_bef`
################################################################################################################# # By Hilda, 17.July.2007 - to check se.log if the ora-4031 flagged to client connection ################################################################################################################# ERR4031=`tail -$DIFF $FILENAME | grep -i "ORA-04031" | wc -l` if [ $ERR4031 -gt 0 ]
then /usr/bin/mailx -s "nGenpr: ORA-4031 Shared Pool Memory (Client connect from se.log)"
ts-dba@test.com.cn < /dev/null
######################################################################### # Alert set to OVO, then operation run flush_shared_pool.sh for hot fix # ######################################################################### # OVOHOST=192.168.16.193
# OVOUSER=ovusr
# OPCMsgCmd=/opt/OV/bin/OpC/opcmsg
# msg_text="nGenpr: ORA-4031 Shared Pool Memory (Client connect from se.log),
pls run flush_shared_pool.sh for hot fix"
# remsh $OVOHOST -l $OVOUSER "$OPCMsgCmd severity=critical msg_grp=oracle application=oracle object=oracle msg_t="\"$msg_text\"""
fi #################################################################################################################
if [ $DIFF -gt $FIRSTFILTER ]
then # HP specific command => TMPFILE=`mktemp -c -d /tmp`
touch /tmp/se.tmp ;
TMPFILE=/tmp/se.tmp
# HP specific command => tail -l$DIFF $FILENAME > $TMPFILE
tail -$DIFF $FILENAME > $TMPFILE
DIFF2=`grep -v "transaction allocation" $TMPFILE |grep -v "\-\-\-\-\-\-\-\-" | grep -v "sequential read"|grep -v "rdbms ipc message"|grep -v "latch free"|grep -v "log file sync " | grep -v "\[Q\]" | wc -l ` # DIFF2=`expr $DIFF2 * 2 - $DIFF` #
echo $DIFF2
if [ $DIFF2 -gt $SECFILTER ]
then
echo "mail" # /usr/bin/mailx -s "TOO many session wait in Database $ORACLE_SID ($DIFF2)" ts-dba@test.com.cn < $TMPFILE
fi
rm $TMPFILE
fi
linecnt_bef=$linecnt_aft
sleep 10
done
yidbmon1:/home/oracle [statdb] >
########debug ora-4031
-
REM srdc_db_ora4031sp.sql - Collect information for ORA-4031 analysis on shared pool
define SRDCNAME='DB_ORA4031SP'
SET MARKUP HTML ON PREFORMAT ON
set TERMOUT off FEEDBACK off VERIFY off TRIMSPOOL on HEADING off
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
set TERMOUT on MARKUP html preformat on
REM
spool &SRDCSPOOLNAME..htm
select '+----------------------------------------------------+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp: '||
to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/
set HEADING on MARKUP html preformat off
REM === -- end of standard header -- ===
REM
SET PAGESIZE 9999
SET LINESIZE 256
SET TRIMOUT ON
SET TRIMSPOOL ON
COL 'Total Shared Pool Usage' FORMAT 99999999999999999999999
COL bytes FORMAT 999999999999999
COL current_size FORMAT 999999999999999
COL name FORMAT A40
COL value FORMAT A20
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';SET MARKUP HTML ON PREFORMAT ON
/* Database identification */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Database identification:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT name, platform_id, database_role FROM v$database;
SELECT * FROM v$version WHERE banner LIKE 'Oracle Database%';/* Current instance parameter values */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Current instance parameter values:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT n.ksppinm name, v.KSPPSTVL value
FROM x$ksppi n, x$ksppsv v
WHERE n.indx = v.indx
AND (n.ksppinm LIKE '%shared_pool%' OR n.ksppinm IN ('_kghdsidx_count', '_ksmg_granule_size', '_memory_imm_mode_without_autosga'))
ORDER BY 1;/* Current memory settings */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Current instance parameter values:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT component, current_size FROM v$sga_dynamic_components;/* Memory resizing operations */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Memory resizing operations:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT start_time, end_time, component, oper_type, oper_mode, initial_size, target_size, final_size, status
FROM v$sga_resize_ops
ORDER BY 1, 2;/* Historical memory resizing operations */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Historical memory resizing operations:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT start_time, end_time, component, oper_type, oper_mode, initial_size, target_size, final_size, status
FROM dba_hist_memory_resize_ops
ORDER BY 1, 2;/* Shared pool 4031 information */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Shared pool 4031 information:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT request_failures, last_failure_size FROM v$shared_pool_reserved;/* Shared pool reserved 4031 information */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Shared pool reserved 4031 information:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT requests, request_misses, free_space, avg_free_size, free_count, max_free_size FROM v$shared_pool_reserved;/* Shared pool memory allocations by size */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Shared pool memory allocations by size:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT name, bytes FROM v$sgastat WHERE pool = 'shared pool' AND (bytes > 999999 OR name = 'free memory') ORDER BY bytes DESC;/* Total shared pool usage */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Total shared pool usage:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT SUM(bytes) "Total Shared Pool Usage" FROM v$sgastat WHERE pool = 'shared pool' AND name != 'free memory';/* Cursor sharability problems */
/* This version is for >= 10g; for <= 9i substitute ss.kglhdpar for ss.address!!!! */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Cursor sharability problems (this version is for >= 10g; for <= 9i substitute ss.kglhdpar for ss.address!!!!):' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT sa.sql_text,sa.version_count,ss.*
FROM v$sqlarea sa,v$sql_shared_cursor ss
WHERE sa.address=ss.address AND sa.version_count > 50
ORDER BY sa.version_count ;SPOOL OFF
Log in to the database using SQL*Plus.
EXIT - Connect as the SYSDBA user.
- Download the attached srdc_db_ora4031sp.sql script.
- Run the srdc_db_ora4031sp.sql script while saving output to a spool file:SQL> connect / as sysdba
SQL> @srdc_db_ora4031sp.sql - Review the output and provide the spool file to Support as necessary.
-
-----说明：
算术运算符指的是可以在程序中实现加、减、乘、除等数学运算的运算符。Shell中常用的数学运算符如下所示。
— +：对两个变量做加法。
— -：对两个变量做减法。
— *：对两个变量做乘法。
— /：对两个变量做除法。
— **：对两个变量做幂运算。
— %：取模运算，第一个变量除以第二个变量求余数。
— +=：加等于，在自身基础上加第二个变量。
— -=：减等于，在第一个变量的基础上减去第二个变量。
— *=：乘等于，在第一个变量的基础上乘以第二个变量。
— /=：除等于，在第一个变量的基础上除以第二个变量。
— %=：取模赋值，第一个变量对第二个变量取模运算，再赋值给第一个变量。
注意：将需要运算的表达式写入在expr 后面，保证参数与运算符号中间有空格隔开。
var=1
var=`expr $var + 1`
echo $var
output：
2
https://www.cnblogs.com/yfanqiu/archive/2012/05/10/2494031.html
