ORACLE 巡检脚本

脚本内容

vim oracle_inspection_report.sh

#!/bin/bash
#
#    file name: /home/oracle/oracle_inspection_report.sh   
#
#    description:
#    part 1.collecting the DB basic infomation
#    part 2.collecting the DB performance infomation
#    part 3.collecting the DB LOG infomation
#    part 4.collecting the OS infomation
#
#    excute: bash /home/oracle/oracle_inspection_report.sh
#
#    author:chaicheng
#  
#    date(yyyy-mm-dd):2019-11-04
#
#    version:1.0
#
#    modified:none       
#    

echo 'Instance Health Data'
echo '************************************************'
echo 'The current database ORACLLE_SID 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 '*******************************************************************************' from dual;
select '*                               1: DB information                             *' from dual;
select '*******************************************************************************' from dual;
select '#####1-1: instance information' from dual;
select '************************************************' from dual;
set linesize 1000
set pagesize 1000
col HOST_NAME for a10
set heading on
select instance_name,host_name,to_char(startup_time,'yyyy-mm-dd hh24:mi:ss') "startup_time",status,database_status from v\$instance;


set heading off
select '#####1-2: database created date and archive type' from dual;
select '************************************************' from dual;
set heading on 
Select to_char(Created,'yyyy-mm-dd hh24:mi:ss') "Created Time",Log_Mode From V\$Database;


set heading off
select '#####1-3: ulimit oracle' from dual;
select '************************************************' from dual;
!ulimit -a


set heading off
col VALUE for a10
select '#####1-4: 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 '#####1-5: used production component' 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 '#####1-6: spfile' from dual;
select '************************************************' from dual;
show parameter spfile


set heading off
select '#####1-7: 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 '#####1-8: control file' from dual;
select '************************************************' from dual;
show parameter control_files


set heading off
select '#####1-9: backup control file' from dual;
select '************************************************' from dual;
alter database backup controlfile to trace;


set heading off
select '#####1-10: 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 '#####1-11: 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 '#####1-12: Archive log' from dual;
select '************************************************' from dual;
Archive log list 


select '#####1-13: data file' from dual;
select '************************************************' from dual;
set heading on
col MAX_G for a20
col USED_G for a20 
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 '#####1-14: data file location' from dual;
select '************************************************' from dual;
set heading on
col FILE_NAME for a60
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 '------1-14-1.temp data file' from dual;
select '************************************************' from dual;
set heading on
col FILE_NAME for a60
select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024 byte_MB,status,AUTOEXTENSIBLE from sys.dba_temp_files;

set heading off
select '-----1-14-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 '#####1-15: 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 '#####1-16: 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 '#####1-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 '#####1-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 '#####1-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 '#####1-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 '#####1-20: indexes' from dual;
select '************************************************' from dual;
set heading on
select * From dba_indexes where BLEVEL>4;


set heading off
select '#####1-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 '#####1-22: sysdba role' from dual;
select '************************************************' from dual;
set heading on
SELECT * FROM v\$pwfile_users order by username;


set head off
select '*******************************************************************************' from dual;
select '*                               2: performance                                *' from dual;
select '*******************************************************************************' 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
col NAME for a60
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 '*******************************************************************************' from dual;
select '*                                   3: LOG                                    *' from dual;
select '*******************************************************************************' from dual;
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 ""
echo '#####3-2: listener configure'
echo '**********************************************'
echo 'listener.ora'
echo '----------------------------------------------'
cat listener*.ora
sleep 2;
echo 'sqlnet.ora'
echo '----------------------------------------------'
cat sqlnet*.ora
sleep 2;
echo 'tnsnames.ora'
echo '----------------------------------------------'
cat tnsnames*.ora
sleep 2;

echo ""
echo '#####3-3: user dump'
echo '**********************************************'
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 ""
echo '#####3-4: Alert Log ORA- Warning Error'
echo '**********************************************'
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 ""
echo '#####3-5: Alert Log size'
echo '**********************************************'
du -sh  alert_$ORACLE_SID.log

echo ""
echo '#####3-6: listener.log size'
echo '**********************************************'
lsnrctl status|grep listener.log|awk '{print $4}'|xargs du -sh

echo ""
echo '#####3-7: crontab info'
echo '**********************************************'
crontab -l

echo ""
echo '#####3-8:Alert Log  tail 200 nums'
echo '**********************************************'
tail -2000 alert_$ORACLE_SID.log


SYSTEM=`uname -s`
export SYSTEM
echo ' '
echo '*******************************************************************************' from dual;
echo '*                             4.machine information                           *'
echo '*******************************************************************************' from dual;

if [ $SYSTEM = "Linux" ] ; then
echo "#####4.1: host name"
echo '**********************************************'
hostname
echo ""
echo "#####4.2: id"
echo '**********************************************'
id
echo ""
echo "#####4.3: Current uptime,users and load averages"
echo '**********************************************'
uptime
echo ""
echo "#####4.4: CPU number"
echo '**********************************************'
echo '4.4.1 CPU version'
echo '----------------------------------------------'
cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
echo '4.4.2 CPU physical number'
echo '----------------------------------------------'
cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l
echo '4.4.3 CPU logical number'
echo '----------------------------------------------'
cat /proc/cpuinfo| grep "processor"| wc -l
echo '4.4.4 CPU core number'
echo '----------------------------------------------'
cat /proc/cpuinfo| grep "processor"| wc -l
sleep 1;
echo ""
echo "#####4.5: memory info"
echo '**********************************************'
cat /proc/meminfo
sleep 1;
echo ""
echo "#####4.6: disk info"
echo '**********************************************'
df -Th
sleep 1;
echo ""
echo "#####4.7: kernel parameter"
echo '**********************************************'
cat /etc/sysctl.conf
sleep 1;
echo ""
echo "#####4.8: OS Version"
echo '**********************************************'
lsb_release -a
sleep 1;
echo ""
echo "#####4.9: product type"
echo '**********************************************'
dmidecode |grep Product
sleep 1;
echo ""
echo "#####4.10: CPU memory usage"
echo '**********************************************'
vmstat 5 5
sleep 1;
echo ""
echo "#####4.11: top info"
echo '**********************************************'
#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 "#####4.1: host name"
echo '**********************************************'
hostname
echo ""
echo "#####4.2: id"
echo '**********************************************'
id
echo "#####4.3: CPU,memory number"
echo '**********************************************'
/usr/platform/sun4u/sbin/prtdiag -v
echo "#####4.4: os lever"
echo '**********************************************'
cat /etc/release
echo "#####4.5: Kernel parameter"
echo '**********************************************'
/usr/sbin/sysdef |grep SHM
/usr/sbin/sysdef |grep SEM
cat /etc/system
echo "#####4.6: disk info"
echo '**********************************************'
df -k
echo "#####4.7: IP info"
echo '**********************************************'
ifconfig -a
sleep 1;

elif [ $SYSTEM = "AIX" ] ; then
echo "#####4.1: host name"
echo '**********************************************'
hostname
echo ""
echo "#####4.2: id"
echo '**********************************************'
id
echo ""
echo "#####4.3: machine plat"
echo '**********************************************'
uname -M
echo ""
echo "#####4.4: CPU,memory number"
echo '**********************************************'
prtconf
sleep 2;
echo ""
echo "#####4.5: disk info"
echo '**********************************************'
df -k
echo ""
echo "#####4.6: os lever"
echo '**********************************************'
oslevel -r
echo ""
echo "#####4.7: kernel parameter"
echo '**********************************************'
lsattr -El sys0
echo ""
echo "#####4.8: HACMP"
echo '**********************************************'
lslpp -l |grep cluster
echo ""
echo "#####4.9: network parameter"
echo '**********************************************'
no -a
echo ""
echo "#####4.10: CPU memory usage"
echo '**********************************************'
vmstat 5 5
sleep 1;
echo ""
echo "#####4.11: IP info"
echo '**********************************************'
ifconfig -a
sleep 1;
echo ""
echo "#####4.12: view cluster"
echo '**********************************************'
lssrc -g cluster
sleep 1;
echo ""
echo "#####4.13: view VG"
echo '**********************************************'
lsvg
sleep 1;
echo ""


elif [ $SYSTEM = "HP-UX" ] ; then
echo "#####4.1: host name"
echo '**********************************************'
hostname
echo ""
echo "#####4.2: id"
echo '**********************************************'
id
echo ""
echo "#####4.3: machine plat"
echo '**********************************************'
model
echo ""
echo "#####4.4: CPU,memory number"
echo '**********************************************'
machinfo
sleep 2;
echo ""
echo "#####4.5: disk info"
echo '**********************************************'
bdf
echo ""
echo "#####4.6: os lever"
echo '**********************************************'
oslevel -r
echo ""
echo "#####4.7: HACMP"
echo '**********************************************'
lslpp -l |grep cluster
echo ""
echo "#####4.8: network parameter"
echo '**********************************************'
no -a
echo ""
echo "#####4.9: CPU memory usage"
echo '**********************************************'
vmstat 5 5
sleep 1;
sar -du 5 5
echo ""
echo "#####4.10: IP info"
echo '**********************************************'
ifconfig -a

else
echo "--------------------What?---------------------"
fi