oracle自动化巡检脚本

#!/bin/bash 
# 
#    NAME 
#      report_oracle_inspection.sh   2016-09-30
# 
#    DESCRIPTION 
#      collecting the DB info 
# 
#    NOTES 
#      sh  report_oracle_inspection.sh 
# 
#    MODIFIED   (yyyy-mm-dd) 
#    liyanming       2016-09-30 


 
echo 'Instance Health Data' 
echo '================================================' 
echo 'The current database is $ORACLE_SID' 
echo 'The current running processes for $ORACLE_SID are' 
echo '================================================' 
ps -ef|grep $ORACLE_SID 
 
sqlplus -S /nolog <<EOF      
 connect / as sysdba 
set feedback off 
set heading off 
select '00.instance information' from dual; 
select '================================================' from dual; 
set linesize 1000 
set pagesize 1000 
set heading on 
select * from v\$instance; 
 
set heading off 
select '01:database created date and archive type' from dual; 
select '================================================' from dual; 
set heading on  
Select Created, Log_Mode, Log_Mode From V\$Database; 
 
set heading off 
select '1.ulimit oracle' from dual; 
select '================================================' from dual; 
!ulimit -a 
 
set heading off 
select '2.installed production option' from dual; 
select '================================================' from dual; 
set linesize 1000 
set pagesize 1000 
set heading on 
select * from v\$option; 
 
set heading off 
select '3.used production option' from dual; 
select '================================================' from dual; 
set linesize 1000 
set pagesize 1000 
col COMP_NAME for a40 
set heading on 
select COMP_ID, COMP_NAME, VERSION,STATUS from   dba_registry; 
 
set heading off 
select '4.spfile' from dual; 
select '================================================' from dual; 
show parameter spfile 
 
set heading off 
select '5.not default parameter' from dual; 
select '================================================' from dual; 
col name for a40 
col value for a40 
set heading on 
select name,value from v\$parameter where isdefault='FALSE'; 
 
set heading off 
select '6.control file' from dual; 
select '================================================' from dual; 
show parameter control_files 
 
set heading off 
select '7.backup control file' from dual; 
select '================================================' from dual; 
alter database backup controlfile to trace; 
 
set heading off 
select '8.log file' from dual; 
select '================================================' from dual; 
set linesize 1000 
set pagesize 1000 
set heading on 
select group#,thread#,bytes/1024/1024 size_MB , members, archived,status from 
v\$Log; 
 
set heading off 
select '9.log file' from dual; 
col MEMBER for a40 
select '================================================' from dual; 
set heading on 
select * From v\$logfile order by 1; 
 
set heading off 
select '10.Archive log' from dual; 
select '================================================' from dual; 
Archive log list  
 
select '11.data file' from dual; 
select '================================================' from dual; 
set heading on 
select count(*),sum(bytes)/1024/1024/1024 ||'G'  max_G from v\$datafile; 
 
SELECT trunc(sum(sum_m-sum_free_m)/1024,2)||'G' used_G 
FROM (  
SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files 
where tablespace_name not like 'UNDO%' GROUP BY tablespace_name) df, 
(SELECT tablespace_name, 
sum(bytes)/1024/1024 AS sum_free_m 
FROM dba_free_space GROUP BY tablespace_name ) fs 
where df.tablespace_name=fs.tablespace_name; 
 
set heading off 
select '12.data file location' from dual; 
select '================================================' from dual; 
set heading on 
select t1.TABLESPACE_NAME,t1.FILE_ID, t1.bytes/1024/1024 
SIZE_MB,t1.AUTOEXTENSIBLE AUT,t2.status,t1.FILE_NAME 
from dba_data_files t1,v\$datafile t2 
where t1.file_id=t2.file#; 
 
set heading off 
select '13-1.temp data file' from dual; 
select '================================================' from dual; 
set heading on 
select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024 
byte_MB,status,AUTOEXTENSIBLE from sys.dba_temp_files; 
 
set heading off 
select '13-2.temp tablespace' from dual; 
select '================================================' from dual; 
set heading on 
col file_name for a30 
col  byte_MB for a20 
col  cached_MB for a20 
SELECT d.file_name,  v.status, TO_CHAR((d.bytes / 1024 / 1024), '99999990.000') 
byte_MB,  
TO_CHAR(NVL(t.bytes_cached, 0) / 1024 / 1024, '99999990.000') cached_MB, 
d.autoextensible, d.increment_by, d.maxblocks  
FROM sys.dba_temp_files d, v\$temp_extent_pool t, v\$tempfile v  
WHERE (t.file_id (+)= d.file_id) AND (d.tablespace_name = 'TEMP') AND (d.file_id 
= v.file#); 
 
set heading off 
select '14.system tablespace' from dual; 
select '================================================' from dual; 
set heading on 
select owner,segment_type,segment_name from dba_segments where owner not 
in('SYS','SYSTEM','MDSYS','ORDSYS','OUTLN','WMSYS') and 
tablespace_name='SYSTEM' order by 1; 
exit 
EOF 
 
ora_version=`sqlplus -S '/ as sysdba' <<EOF 
set head off 
select version from v\\\$instance; 
exit; 
EOF` 
 
echo $ora_version 
 
if [ `echo $ora_version|awk -F"." '{print $1}'` -ne 8 ] 
then 
sqlplus -S /nolog <<EOF 
conn / as sysdba 
set linesize 1000 
set pagesize 1000 
set heading off 
select '15.tablespace fragmentation and free' from dual; 
select '================================================' from dual; 
col TABLESPACE_NAME for a30 
col FREE_PCT for a20 
set heading on 
SELECT df.TABLESPACE_NAME,FILES, extent_management ,sum_m as 
TOTAL_SIZE,--sum(largest) as "MAXFREE_MB",  
sum_free_m as "FREE_MB",to_char(100*sum_free_m/sum_m, '999.99') AS 
FREE_PCT--,sum(blocks) as "FREE_EXTENTS" 
FROM ( SELECT tablespace_name,count(file_id) as files ,sum(bytes)/1024/1024 AS 
sum_m FROM dba_data_files GROUP BY tablespace_name) df, 
(SELECT tablespace_name,--max(bytes)/1024/1024 largest, 
sum(bytes)/1024/1024 AS sum_free_m --,count(blocks) as blocks 
FROM dba_free_space GROUP BY tablespace_name ) fs,(select 
tablespace_name,extent_management from dba_tablespaces) ts 
where df.tablespace_name=fs.tablespace_name and 
fs.tablespace_name=ts.tablespace_name;  
exit; 
EOF 
 
else 
sqlplus -S /nolog <<EOF 
conn / as sysdba 
set linesize 1000 
set pagesize 1000 
select '16.tablespace fragmentation and free (8i)' from dual; 
select '================================================' from dual; 
col TABLESPACE_NAME for a30 
col FREE_PCT for a20 
set heading on 
SELECT df.TABLESPACE_NAME,FILES, sum_m as TOTAL_SIZE,--sum(largest) as 
"MAXFREE_MB",  
sum_free_m as "FREE_MB",to_char(100*sum_free_m/sum_m, '999.99') AS 
FREE_PCT--,sum(blocks) as "FREE_EXTENTS" 
FROM ( SELECT tablespace_name,count(file_id) as files ,sum(bytes)/1024/1024 AS 
sum_m FROM dba_data_files GROUP BY tablespace_name) df, 
(SELECT tablespace_name,--max(bytes)/1024/1024 largest, 
sum(bytes)/1024/1024 AS sum_free_m --,count(blocks) as blocks 
FROM dba_free_space GROUP BY tablespace_name ) fs,(select tablespace_name from 
dba_tablespaces) ts 
where df.tablespace_name=fs.tablespace_name and 
fs.tablespace_name=ts.tablespace_name;  
exit; 
EOF 
fi 


sqlplus -S /nolog <<EOF 
conn / as sysdba 
set linesize 1000 
set pagesize 1000 
set heading off 
select '17.object list' from dual; 
select '================================================' from dual; 
col OBJECT_TYPE for a20 
set heading on 
select owner,replace(object_type,' ','_') as OBJECT_TYPE,count(*) from 
dba_objects where 
owner not in ('SYS','SYSTEM') group by owner,object_type order by 
owner,object_type; 
 
set heading off 
select '18.invalid objects' from dual; 
select '================================================' from dual; 
col OBJECT_NAME for a40 
col OBJECT_TYPE for a20 
set heading on 
select OWNER,OBJECT_NAME,replace(OBJECT_TYPE,' ','_') as 
OBJECT_TYPE,STATUS,TIMESTAMP from dba_objects where status='INVALID'; 
 
set heading off 
select '19.dblinks' from dual; 
select '================================================' from dual; 
col DB_LINK for a40 
col OWNER for a10 
col HOST for a20 
set heading on 
select * from dba_db_links; 
 
set heading off 
select '20.indexes' from dual; 
select '================================================' from dual; 
set heading on 
select * From dba_indexes where BLEVEL>4; 
 
set heading off 
select '21.dba role' from dual; 
select '================================================' from dual; 
set heading on 
select grantee,granted_role from dba_role_privs where granted_role='DBA'; 
 
set heading off 
select '22.sysdba role' from dual; 
select '================================================' from dual; set heading on 
SELECT * FROM v\$pwfile_users order by username; 


set head off 
select '2-performance' from dual; select 
'================================================================================================' from dual; 
select '2-1.buffer cache hit ratio:(Higher than 80% is ok, high value does not alwasy mean good performance)' from dual; 
select '================================================' from dual; set head on 
select (1 - (sum(decode(name, 'physical reads', value, 0)) / (sum(decode(name, 'db block gets', value, 0)) + 
sum(decode(name, 'consistent gets', value, 0))))) * 100  "Hit Ratio" from v\$sysstat;  
set head off 
select '2-2.data dictionary hit ratio:should >98%' from dual; 
select '================================================' from dual; set head on 
select (1 - (sum(getmisses) / sum(gets))) * 100 "Hit Ratio" from v\$rowcache;  
set head off select '2-3.library cache hit ratio:(Should be kept over 90%, otherwise there mighe be too much reparse)' from dual; 
select '================================================' from dual; set head on 
select sum(pins) / (sum(pins) + sum(reloads)) * 100 "Hit Ratio" from v\$librarycache;  
set head off 
select '2-4.menory sort ratio:should >98%' from dual; 
select '================================================' from dual; set head on 
select a.value "Disk Sorts", b.value "Memory Sorts", round((100 * b.value) / 
decode((a.value + b.value), 0, 1, (a.value + b.value)), 2) "Pct Memory Sorts" 
from v\$sysstat a, v\$sysstat b where a.name = 'sorts (disk)' and b.name = 'sorts (memory)';


set head off 
select '2-5.memory top 10 sql read ratio:should <5%' from dual; 
select '================================================' from dual; set head on 
select sum(pct_bufgets) 
from (select rank() over(order by buffer_gets desc) as rank_bufgets, to_char(100 * ratio_to_report(buffer_gets) over(), '999.99') pct_bufgets from v\$sqlarea) 
where rank_bufgets < 11;  
set heading off 
select '' from dual; select '2-6.Top 10 Wait Event (Time unit:Hundreths of a second, IO operations should be common wait event)' from dual; 
select '================================================' from dual; set heading on 
column event format a30 
select * from (select event,total_waits,time_waited, average_wait from v\$system_event where  
event not like 'SQL*Net%' and event not like '%ipc%' order by total_waits desc) where  rownum<11;  
set head off 
select '2-7.memory top 10 sql' from dual; 
select '================================================' from dual; set head on  
set serveroutput on size 1000000 declare 
top10 number; 
text1 varchar2(4000); x number; len1 number; cursor c1 is 
select buffer_gets, substr(sql_text, 1, 4000) from v\$sqlarea 
order by buffer_gets desc; begin  
dbms_output.put_line('------------' || ' ' || '-------------------'); open c1; 
for i in 1 .. 10 loop 
fetch c1 into top10, text1; 
dbms_output.put_line('------------top sql No.' ||i||'-------------------');




dbms_output.put_line(rpad(to_char(top10), 9) ); len1 := length(text1); x := 1; 
while len1 > x - 1 loop 
dbms_output.put_line('  ' || substr(text1, x, 65)); x := x + 66; end loop; end loop; end; /  
set head off 
select '2-8.IO information' from dual; 
select '================================================' from dual; set head on 
Select phyrds,phywrts,d.name from v\$datafile d,v\$filestat f where f.file#=d.file# order by d.name;  
set head off 
select '2-9.full table scan' from dual; 
select '================================================' from dual; set head on 
Select name,value value1 from v\$sysstat where name like '%table scan%';  
set head off 
select '3-1.sys and system security' from dual; 
select '================================================' from dual; 
select username "User(s) with Default Password!",ACCOUNT_STATUS from dba_users where password in 
('E066D214D5421CCC',  -- dbsnmp 
'24ABAB8B06281B4C',  -- ctxsys 
'72979A94BAD2AF80',  -- mdsys 
'C252E8FA117AF049',  -- odm 
'A7A32CD03D3CE8D5',  -- odm_mtr 
'88A2B2C183431F00',  -- ordplugins 
'7EFA02EC7EA6B86F',  -- ordsys 
'4A3BA55E08595C81',  -- outln 
'F894844C34402B67',  -- scott 
'3F9FBD883D787341',  -- wk_proxy 
'79DF7A1BD138CF11',  -- wk_sys 
'7C9BA362F8314299',  -- wmsys 
'88D8364765FCE6AF',  -- xdb 
'F9DA8977092B7B81',  -- tracesvr 
'9300C0977D7DC75E',  -- oas_public
'A97282CE3D94E29E',  -- websys 
'AC9700FD3F1410EB',  -- lbacsys 
'E7B5D92911C831E1',  -- rman 
'AC98877DE1297365',  -- perfstat 
'66F4EF5650C20355',  -- exfsys 
'84B8CBCA4D477FA3',  -- si_informtn_schema 
'D4C5016086B2DC6A',  -- sys 
'D4DF7931AB130E37')  -- system 
; 
exit 
EOF 
 
 
cd $ORACLE_HOME/network/admin/ 
echo '3-2.listener configure' 
echo 'listener.ora================================================' 
cat listener*.ora 
sleep 2;  
echo 'sqlnet.ora=================================================' 
cat sqlnet*.ora 
sleep 2;  
echo 'tnsnames.ora================================================' 
cat tnsnames*.ora 
sleep 2;  
 
echo '3-3.controlfile dump============================================' 
ora_dump=`sqlplus -S '/ as sysdba' <<EOF 
set head off 
select value 
from v\\\$parameter 
where name='user_dump_dest'; 
exit; 
EOF` 
 
cd $ora_dump 
ls -lt|head -n 2|tail -n 1|awk '{print $9}'|xargs cat 
sleep 2; 
 
echo '3-4.Alert Log ORA- Warning 
Error============================================' 
ora_background_dump=`sqlplus -S '/ as sysdba' <<EOF 
set head off 
select value 
from v\\\$parameter 
where name='background_dump_dest'; 
exit; 
EOF` 
 
cd $ora_background_dump 
tail -10000 alert_$ORACLE_SID.log|grep ORA- 
sleep 2; 
 
echo '3-5.Alert Log size============================================' 
ls -l alert_$ORACLE_SID.log 
 
echo '3-6.listener.log size============================================' 
lsnrctl status|grep listener.log|awk '{print $4}'|xargs ls -l 
 
echo '3-7.crontab info============================================' 
crontab -l 
 
echo '3-8.Alert Log  tail 20000 
nums============================================' 
tail -20000 alert_$ORACLE_SID.log 
 
SYSTEM=`uname -s` 
export SYSTEM 
 
echo '4.machine information============================================' 
 
if [ $SYSTEM = "Linux" ]  then 
echo "----------------host name----------------" 
hostname 
echo "" 
echo "----------------id----------------" 
id 
echo "" 
echo '--- Current uptime,users and load averages ---' 
uptime 
echo "" 
echo "----------------CPU number----------------" 
cat /proc/cpuinfo 
sleep 1; 
echo "" 
echo "----------------memory info----------------" 
cat /proc/meminfo 
sleep 1; 
echo "" 
echo "----------------disk info----------------" 
df -k 
sleep 1; 
echo "" 
echo "----------------kernel parameter----------------" 
cat /etc/sysctl.conf 
sleep 1; 
echo "" 
echo "----------------os lever----------------" 
lsb_release -a 
sleep 1; 
echo "" 
echo "----------------product type----------------" 
dmidecode |grep Product 
sleep 1; 
echo "" 
echo "----------------CPU memory usage----------------" 
vmstat 5 5 
sleep 1; 
echo "" 
echo "----------------top info----------------" 
top -d 1 -n 20 
sleep 5; 
top -d 1 -n 20  
sleep 5; 
top -d 1 -n 20  
sleep 5; 
top -d 1 -n 20  
sleep 5; 
top -d 1 -n 20  
 
elif [ $SYSTEM = "SunOS" ]  then 
echo "----------------host name----------------" 
hostname 
echo "" 
echo "----------------id----------------" 
id 
echo "----------------CPU,memory number----------------" 
/usr/platform/sun4u/sbin/prtdiag -v 
echo "----------------os lever----------------" 
cat /etc/release 
echo "----------------Kernel parameter----------------" 
/usr/sbin/sysdef |grep SHM 
/usr/sbin/sysdef |grep SEM 
cat /etc/system 
echo "----------------disk info----------------" 
df -k 
echo "----------------IP info----------------" 
ifconfig -a 
sleep 1; 
 
elif [ $SYSTEM = "AIX" ]  then 
echo "----------------host name----------------" 
hostname 
echo "" 
echo "----------------id----------------" 
id 
echo "" 
echo "----------------machine plat----------------" 
uname -M 
echo "" 
echo "----------------CPU,memory number----------------" 
prtconf 
sleep 2; 
echo "" 
echo "----------------disk info----------------" 
df -k 
echo "" 
echo "----------------os lever----------------" 
oslevel -r 
echo "" 
echo "----------------kernel parameter----------------" 
lsattr -El sys0 
echo "" 
echo "----------------HACMP----------------" 
lslpp -l |grep cluster 
echo "" 
echo "----------------network parameter----------------" 
no -a 
echo "" 
echo "----------------CPU memory usage----------------" 
vmstat 5 5 
sleep 1; 
echo "" 
echo "----------------IP info----------------" 
ifconfig -a 
sleep 1; 
echo "" 
echo "----------------view cluster----------------" 
lssrc -g cluster 
sleep 1; 
echo "" 
echo "----------------view VG----------------" 
lsvg 
sleep 1; 
echo "" 
 
 
elif [ $SYSTEM = "HP-UX" ]  then  
echo "----------------host name----------------" 
hostname 
echo "" 
echo "----------------id----------------" 
id 
echo "" 
echo "----------------machine plat----------------" 
model 
echo "" 
echo "----------------CPU,memory number----------------" 
machinfo 
sleep 2; 
echo "" 
echo "----------------disk info----------------" 
bdf 
echo "" 
echo "----------------os lever----------------" 
oslevel -r 
echo "" 
echo "----------------HACMP----------------" 
lslpp -l |grep cluster 
echo "" 
echo "----------------network parameter----------------" 
no -a 
echo "" 
echo "----------------CPU memory usage----------------" 
vmstat 5 5 
sleep 1; 
sar -du 5 5 
echo "" 
echo "----------------IP info----------------" 
ifconfig -a 
 
else 
echo "What " 
fi