实战:oracle巡检脚本v1(十三)
cechon "5.42 REDOLOG HIT RATIO is: " red
echo
echo "--------------------------------------------------------------------"
cechon "REDOLOG HIT RATIO should >95" yellow
echo
echo "--------------------------------------------------------------------"
cat 542.txt |xargs |awk '{print "redo copy:" $3 " " $4,"\nredo allocation:" $7" "$8}'
echo
rm -rf 542.txt
#5.43 The ratio of free data buffers
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 543.txt
SELECT SUM(DECODE(STATUS,'AVAILABLE',VAL,0)) "AVAILABLE",
SUM(DECODE(STATUS,'BEING USED',VAL,0)) "BEING_USED",
round(SUM(DECODE(STATUS,'AVAILABLE',VAL,0))/(SUM(DECODE(STATUS,'AVAILABLE',VAL,0))
+SUM(DECODE(STATUS,'BEING USED',VAL,0)))*100,2)||'%'
"AVAILABLE_PERCENT"
FROM
(SELECT DECODE(STATE,0,'FREE',1,DECODE(LRBA_SEQ, 0, 'AVAILABLE', 'BEING USED'),
3,'BEING USED',STATE) "STATUS",
COUNT(*) VAL
FROM X\$BH
GROUP BY DECODE(STATE,0,'FREE',1,DECODE(LRBA_SEQ, 0, 'AVAILABLE', 'BEING USED'),
3,'BEING USED',STATE));
spool off
exit;
!01
cechon "5.43 The ratio of free data buffers is: " red
echo
echo "--------------------------------------------------------------------"
cechon "REDOLOG HIT RATIO should >95" yellow
echo
echo "--------------------------------------------------------------------"
cat 543.txt |xargs |awk '{print "AVAILABLE:"$1, \
"\nBEING_USED:"$2,"\nAVAILABLE_PERCENT:"$3}'
echo
rm -rf 543.txt
echo
cechon "***********************************************************************" yellow
echo
cechon "6.oracle database disabled objects:" green
echo "Disabled Indexes"
echo "Disabled constraints"
echo "Disabled triggers"
cechon "***********************************************************************" yellow
echo
#6.1 Disabled Indexes
sqlplus -S "${ora_user}/${ora_pass} as sysdba" <
/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 61.txt
select owner,index_name,index_type,table_name,status from dba_indexes
where status = 'UNUSABLE';
spool off
exit;
!01
cechon "6.1 The Disabled Indexes is: " red
echo
cat 61.txt
echo
rm -rf 61.txt
#6.2 Disabled Constraints
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 62.txt
select owner,constraint_name,constraint_type,table_name from dba_constraints
where status='DISABLED';
spool off
exit;
!01
cechon "6.2 The Disabled Constraints is: " red
echo
cat 62.txt
echo
rm -rf 62.txt
#6.3 Disabled Triggers
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 63.txt
select owner,trigger_name,trigger_type from dba_triggers
where status='DISABLED';
spool off
exit;
!01
cechon "6.3 The Disabled Triggers is: " red
echo
cat 63.txt
echo
rm -rf 63.txt
echo
cechon "***************************************************