standby checking script 3则 V1 shell 脚本
---1
#!/bin/sh
export ORACLE_SID=hdb
export ORACLE_BASE=/db/hdbdg/app/product/database
export ORACLE_HOME=/db/hdbdg/app/product/database/11g
export LANG=en_US
export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"
export PATH=$PATH:$ORACLE_HOME/bin:.
STATE_OK=0
STATE_WARNING=1
STATE_CRITICAL=2
STATE_UNKNOWN=3
#备库
get_result=`sqlplus '/ as sysdba'<<EOF
spool off;
select 'stdcseq1=' || max(sequence#) from v\\$archived_log where thread#=1 and applied='YES' group by thread#;
quit;
EOF`
stdcseq1=`echo "$get_result"|grep stdcseq1|cut -d "=" -f2`
echo $stdcseq1
get_result=`sqlplus '/ as sysdba'<<EOF
spool off;
select 'stdcseq2=' || max(sequence#) from v\\$archived_log where thread#=2 and applied='YES' group by thread#;
quit;
EOF`
stdcseq2=`echo "$get_result"|grep stdcseq2|cut -d "=" -f2`
echo $stdcseq2
#主库
get_result=`sqlplus dbmonopr/dbmonoprhdb11@hdb<<EOF
spool off;
select 'seq1=' || max(sequence#) from v\\$archived_log where thread#=1 group by thread#;
quit;
EOF`
echo "$get_result"
seq1=`echo "$get_result"|grep seq1|cut -d "=" -f2`
echo $seq1
seqdiff1=`expr $seq1 - $stdcseq1`
echo 'seqdiff1='$seqdiff1
if [ $seqdiff1 -ge 10 ]
then
echo "CRITICAL - hdb database dataguard error large than 10."
exit 2
fi
if [ $seqdiff1 -ge 6 ]
then
echo "WARNING - hdb database dataguard error large than 6."
exit 1
fi
if [ $seqdiff1 -lt 6 ]
then
echo "OK - hdb database dataguard ok."
exit 0
fi
############2:
version=`sqlplus -v|awk '{print $3}'|awk -F '.' '{print $1}'`
process_status='select status from v$managed_standby where process!='"'ARCH' and process like '%MRP%';"
status=`sqlplus -S / as sysdba<<EOF
set heading off
set feedback off
$process_status
exit
EOF`
echo mrp_status=$status
synctime="select to_char(first_time,'""yyyy-mm-dd hh24:mi:ss'"') from v$log_history where recid in (select max(recid) from v$log_history b group by thread#);'
time=`sqlplus -S / as sysdba<<EOF
set heading off
set feedback off
$synctime
exit
EOF`
echo synctime=$time
system=`uname`
case $system in
AIX)
disk_usage=`df -g|egrep -i "archive|fra" |awk '{print $(NF-3)}'`
if [ -n "$disk_usage" ];
then
echo archivedisk_usage=$disk_usage
fi
;;
HP-UX)
disk_usage=`bdf |egrep -i "archive|fra" |awk '{print $(NF-1)}'`
if [ -n "$disk_usage" ];
then
echo archivedisk_usage=$disk_usage
fi
;;
Linux)
disk_usage=`df -h|egrep -i "archive|fra" |awk '{print $(NF-1)}'`
if [ -n "$disk_usage" ];
then
echo archivedisk_usage=$disk_usage
fi
;;
esac
if [ -z "$disk_usage" ];
then
recovery_dest='select value from v$system_parameter where name = '"'db_recovery_file_dest';"
dest=`sqlplus -S / as sysdba<<EOF
set heading off
set feedback off
$recovery_dest
exit
EOF`
dest=${dest#*+}
asmdisk_usage_sql='select round((1-(free_mb/total_mb))*100,2) from v$asm_diskgroup where name='"'$dest';"
asmdisk_usage=`sqlplus -S / as sysdba<<EOF
set heading off
set feedback off
$asmdisk_usage_sql
exit
EOF`
echo archivedisk_usage=${asmdisk_usage}%
fi
if [ $version -eq 10 ];
then
recovery_dest_usage='select PERCENT_SPACE_USED from v$flash_recovery_area_usage where FILE_TYPE like '"'%ARCHIVE%';"
fi
if [ $version -eq 11 ];
then
recovery_dest_usage='select PERCENT_SPACE_USED from v$flash_recovery_area_usage where FILE_TYPE = '"'ARCHIVED LOG';"
fi
usage=`sqlplus -S / as sysdba<<EOF
set heading off
set feedback off
$recovery_dest_usage
exit
EOF`
echo fra_usage=${usage}%
###########3
#!/bin/bash
#env
PATH=/usr/local/bin:/usr/bin:$PATH:.
source /home/oracle11g/.bash_profile
f_getparameter(){
if [ -z "$3" ]; then
return
fi
PARAMETER=$1
sqlplus -s /nolog <<EOF | awk -F= "/^a=/ {print \$2}"
##sqlplus -s /nolog <<EOF
set head off pagesize 0 feedback off linesize 200
whenever sqlerror exit 1
##conn / as sysdba
##conn ${LOGIN_ID}@${db_name}
conn $2@$3
select 'a='||value from v\$parameter where name = '$PARAMETER';
EOF
}
f_getvalues(){
if [ -z "$3" ]; then
return
fi
PARAMETER=$1
sqlplus -s /nolog <<EOF | awk -F= "/^a=/ {print \$2}"
##sqlplus -s /nolog <<EOF
set head off pagesize 0 feedback off linesize 200
whenever sqlerror exit 1
##conn / as sysdba
##conn ${LOGIN_ID}@${db_name}
conn $2@$3
select 'a='||round(max_utilization/limit_value*100) from v\$resource_limit where resource_name='$PARAMETER';
EOF
}
f_getlist(){
if [ -z "$1" ]; then
return
fi
PARAMETER=$1
# sqlplus -s /nolog <<EOF | awk -F= "/^a=/ {print \$2}"
sqlplus -s /nolog <<EOF
set head off pagesize 0 feedack off linesize 50
whenever sqlerror exit 1
conn $2@$3
select status from v\$managed_standby where process!='ARCH' and process like '%MRP%';
select to_char(first_time,'yyyy-mm-dd hh24:mi:ss') from v\$log_history where recid in (select max(recid) from v\$log_history b group by thread#);
select PERCENT_SPACE_USED from v\$flash_recovery_area_usage where FILE_TYPE like 'ARCHIVE%';
EOF
}
db_list_checking(){
db_list="ora11g_test sdbs"
#db_list="sdbs"
##remove rdbs because 210.187 can't telnet 58.8.95.3 port 1528,need network check more
for db_name in ${db_list}
do
LOGIN_ID=dbmgr/t1234DBA
echo "###########$db_name###"
#echo $list
f_getlist ANY $LOGIN_ID $db_name
done
}
main()
{
echo '----------------'`date`'------------------checing konw---------------------------'
db_list_checking
echo '----------------'`date`'------------------over---------------------------'
}
main
###sample 3:
step 1 :create table tbspct
set heading on
clear computes columns breaks
column name new_value _dbname noprint
column spool_time new_value _spool_time noprint
column spooltime new_value _spooltime noprint
select name,to_char(sysdate,'YYYYMMDD') as "spool_time",
to_char(sysdate,'YYYY-MM-DD') as "spooltime"
from v$database;
define _rpt_db_name=&_dbname
define _rpt_spool_time="&_spooltime"
create table tbspct as
SELECT '&_dbname' db_name,'&_spooltime' time,a.tablespace_name ,b.maxbytes/1024/1024/1024 "maxbyes_GB",total/1024/1024/1024 "bytes_GB",free/1024/1024/1024 "free_GB",(total-free) /1024/1024/1024 "use_GB",
ROUND((total-free)/total,4)*100 "use_%",ROUND((total-free)/b.maxbytes,4)*100 "maxuse_%"
FROM
(SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE
GROUP BY tablespace_name
) a,
(SELECT tablespace_name,sum(case autoextensible when 'YES' then maxbytes else bytes end) maxbytes,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name
) b
WHERE a.tablespace_name=b.tablespace_name
order by "maxuse_%" desc
;
step 2:
#!/bin/bash
#env
#the script is used for init dbmgr passwd every two month;
PATH=/usr/local/bin:/usr/bin:$PATH:.
export $PATH
source /home/oracle11g/.bash_profile
f_getparameter(){
if [ -z "$3" ]; then
return
fi
PARAMETER=$1
sqlplus -s /nolog <<EOF | awk -F= "/^a=/ {print \$2}"
##sqlplus -s /nolog <<EOF
set head off pagesize 0 feedback off linesize 200
whenever sqlerror exit 1
##conn / as sysdba
##conn ${LOGIN_ID}@${db_name}
conn $2@$3
select 'a='||value from v\$parameter where name = '$PARAMETER';
EOF
}
f_getvalues(){
if [ -z "$3" ]; then
return
fi
PARAMETER=$1
sqlplus -s /nolog <<EOF | awk -F= "/^a=/ {print \$2}"
##sqlplus -s /nolog <<EOF
set head off pagesize 0 feedback off linesize 200
whenever sqlerror exit 1
##conn / as sysdba
##conn ${LOGIN_ID}@${db_name}
conn $2@$3
select 'a='||round(max_utilization/limit_value*100) from v\$resource_limit where resource_name='$PARAMETER';
EOF
}
f_getlist(){
if [ -z "$1" ]; then
return
fi
PARAMETER=$1
# sqlplus -s /nolog <<EOF | awk -F= "/^a=/ {print \$2}"
time=`date +"%Y-%m-%d"`
db=$3
QUERYRES=`
sqlplus -s /nolog <<EOF
set echo off feedback off heading off underline off linesize 300;
conn $2@$3
SELECT a.tablespace_name ,b.maxbytes/1024/1024/1024 "maxbyes_GB",total/1024/1024/1024 "bytes_GB",free/1024/1024/1024 "free_GB",(total-free) /1024/1024/1024 "use_GB",
ROUND((total-free)/total,4)*100 "use_%",ROUND((total-free)/b.maxbytes,4)*100 "maxuse_%"
FROM
(SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE
GROUP BY tablespace_name
) a,
(SELECT tablespace_name,sum(case autoextensible when 'YES' then maxbytes else bytes end) maxbytes,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name
) b
WHERE a.tablespace_name=b.tablespace_name
order by "maxuse_%" desc
;
exit;
EOF`
echo 'echo variable_1: '${QUERYRES}
while read -r tablespace_name maxbyes_GB bytes_GB free_GB used_GB use_percent maxuse_percent
do
echo "..${tablespace_name}..${maxbyes_GB}..${bytes_GB}..${free_GB}..${used_GB}..${use_percent}..${maxuse_percent}.."
time=`date +"%Y-%m-%d"`
db=$3
sqlplus -S dbmgr/dd1234DBA <<EOF
insert into tabpct values ('${db}','${time}','${tablespace_name}',${maxbyes_GB},${bytes_GB},${free_GB},${used_GB},${use_percent},${maxuse_percent});
commit;
exit
EOF
done <<< "${QUERYRES}"
echo "------------------------------------------------------"
echo "------------------------------------------------------"
}
f_init_passwd(){
if [ -z "$1" ]; then
return
fi
PARAMETER=$1
# sqlplus -s /nolog <<EOF | awk -F= "/^a=/ {print \$2}"
sqlplus -s /nolog <<EOF
set head off pagesize 0 feedback off linesize 50
whenever sqlerror exit 1
conn $2@$3
select '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++' from dual;
alter user dbmgr profile default;
alter user dbmgr identified by dd1234DBA;
alter user dbmgr profile dba_profile;
EOF
}
db_list_checking(){
db_list="
#db_list="ora11g"
#db_list="afa_dg nsp_dg"
for db_name in ${db_list}
do
LOGIN_ID=dbmgr/dd1234DBA
echo "###########$db_name###"
#para=`f_getparameter processes $LOGIN_ID $db_name`
##echo $para
#
#if [ -z "$para" ]; then
### return "please check $db_name connect issue"
# echo return "please check $db_name connect issue"
# continue
#fi
#
#
#if [ $para -ge 151 ]
#then
# echo "OK"
#else
# echo "$db_name processes values is 150, please increase to 500;"
#fi
#
#value=`f_getvalues processes $LOGIN_ID $db_name`
###echo $value
#
#if [ $value -ge 80 ]
#then
# echo "$db_name processes values is $para and not more , please increase to more;"
#fi
#
#list=`f_getlist ANY $LOGIN_ID $db_name`
#echo $list
f_getlist ANY $LOGIN_ID $db_name
#f_init_passwd ANY $LOGIN_ID $db_name
done
}
main()
{
echo '----------------'`date`'------------------checing konw---------------------------'
#/*每月27号清理alert_log{异常分析}、listener(监听log)*,udit_log(跟踪)、log_xml/
## date_=`date +%d`
## if [ $date_ -eq 27 ]
## then
## alert_log
db_list_checking
##fi
echo '----------------'`date`'------------------over---------------------------'
}
main
