Regular Manual Check

Ready

cd /mnt/play/systec/项目/中烟集团

# develop
scp check_oracle.sql health_check.sh orabid@172.16.33.145:/tmp/
scp check_oracle.sql health_check.sh orabip@172.16.33.146:/tmp/

# Production

scp check_oracle.sql health_check.sh orabip@172.16.33.141:/tmp/
scp check_oracle.sql health_check.sh oracrp@172.16.33.158:/tmp/
scp check_oracle.sql health_check.sh oraecp@172.16.33.131:/tmp/
scp check_oracle.sql health_check.sh oraepp@172.16.33.168:/tmp/
scp check_oracle.sql health_check.sh orasrp@172.16.33.164:/tmp/
scp check_oracle.sql health_check.sh oraxip@172.16.33.132:/tmp/
scp check_oracle.sql health_check.sh orabpp@172.16.33.180:/tmp/

System Check

telnet SERVER_IP

time sh /tmp/health_check.sh | tee /tmp/`hostname`_info_`date +%F`.txt

scp merlyn@10.160.186.75:/tmp/check_oracle.sql /tmp/

setenv SERVER_IP `netstat -in | sed -n '2p' | awk '{ print $4 }'`

# create file hostname_info, Collect system info
touch /tmp/`hostname`_info_`date +%F`.txt

echo "Network info"
echo "========================================================================================"
netstat -ni >> /tmp/`hostname`_info_`date +%F`.txt

echo "Machinfo info"
echo "========================================================================================"
machinfo >> /tmp/`hostname`_info_`date +%F`.txt

echo "vmstat info"
echo "========================================================================================"
vmstat 1 10

echo "========================================================================================"
echo "iostat info"
iostat 1 10

echo "========================================================================================"
echo "Listener info"
lsnrctl status >> /tmp/`hostname`_info_`date +%F`.txt
tnsping $ORACLE_SID >> /tmp/`hostname`_info_`date +%F`.txt

echo "Package it"
echo "========================================================================================"
sqlplus / as sysdba @/tmp/check_oracle.sql >> /tmp/`hostname`_info_`date +%F`.txt

find $ORACLE_HOME/network/admin -name "*t*n*e?.ora" | xargs tar -cvf /tmp/`hostname`_Oracle_config_`date +%F`.tar
find $ORACLE_HOME/dbs/ -name "init$ORACLE_SID.ora" | xargs tar -rvf /tmp/`hostname`_Oracle_config_`date +%F`.tar

find $ORACLE_HOME/ -name "alert_$ORACLE_SID.log" | xargs tar -rvf /tmp/`hostname`_Oracle_config_`date +%F`.tar
find $HOME/saptrace/background -name "alert_$ORACLE_SID.log" | xargs tar -rvf /tmp/`hostname`_Oracle_config_`date +%F`.tar

tar -rvf /tmp/`hostname`_Oracle_config_`date +%F`.tar /tmp/`hostname`_info_`date +%F`.txt

scp /tmp/`hostname`_Oracle_config_`date +%F`.tar merlyn@172.16.34.252:~

Database status

sqlplus / as sysdba
set lines 200;
column HOST format a7;
SELECT     inst_id,
           instance_number inst_no,
           instance_name inst_name,
           parallel,
           status,
           database_status db_status,
           active_state state,
           host_name host
FROM       gv$instance
ORDER BY   inst_id;

==================================================================

sqlplus / as sysdba
set lines 200;
column HOST format a7;
SELECT instance_name, database_status, status, host_name FROM v$instance;

@?/rdbms/admin/awrrpt.sql

show tablespace usages status

set linesize 300
SELECT upper(f.tablespace_name) "tablespace_name",
       d.Tot_grootte_Mb "tablespace(M)",
       d.Tot_grootte_Mb - f.total_bytes "used(M)",
       round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) "use%",
       f.total_bytes "free_space(M)",
       round(f.total_bytes / d.Tot_grootte_Mb * 100,2) "free%"
 FROM      
    (SELECT tablespace_name,
            round(SUM(bytes)/(1024*1024),2) total_bytes,
            round(MAX(bytes)/(1024*1024),2) max_bytes
      FROM sys.dba_free_space
     GROUP BY tablespace_name) f,
    (SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
      FROM   sys.dba_data_files dd
      GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name    
ORDER BY 4 DESC
/

Shared Pool Size hit ratio

select round((sum(gets)-sum(reloads))/sum(gets)*100,1) "libiary cache hit ratio %"
from v$librarycache where namespace
in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');

data dictionary hit ratio

select round((1-sum(getmisses)/sum(gets))*100,1) "data dictionary hit ratio %"
from v$rowcache;

锁竞争

select  substr(ln.name,1,25) Name, 
        l.gets, l.misses, 
        100*(l.misses/l.gets) "% Ratio (STAY UNDER 1%)" 
from v$latch l, v$latchname ln 
where ln.name in ('cache buffers lru chain') 
and ln.latch# = l.latch#;

排序命中率

select a.value "Sort(Disk)", b.value "Sort(Memory)",
round(100*(a.value/decode((a.value+b.value), 0,1,(a.value+b.value))),2) "% Ratio (STAY UNDER 5%)"  
from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)'
and b.name = 'sorts (memory)';

数据缓冲区命中率

select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratio 
from v$sysstat phy,v$sysstat cur,v$sysstat con
where phy.name='physical reads' and cur.name='db block gets' and con.name='consistent gets';

v$session_wait

select sid,seq#,event,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%'  and event not like 'rdbms%';

回滚段的争用情况

select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;

无效对象情况

col OBJECT_NAME for a36
SELECT object_name, object_type,status FROM dba_objects WHERE status like 'INVALID';