Contents
Regular Manual Check
lsnrctl status tnsping $ORACLE_SID sqlplus / as sysdba set lines 300; column HOST format a7; SELECT instance_name, database_status, status, host_name FROM v$instance; exit more $HOME/saptrace/background/alert_$ORACLE_SID.log more /oracle/$ORACLE_SID/102_64/network/log/sqlnet.log
Ready
cd /mnt/play/systec/项目/中烟集团
# develop scp health_check.sh orabid@172.16.33.145:/tmp/systec/ scp health_check.sh orabip@172.16.33.146:/tmp/systec/ scp health_check.sh oracrd@172.16.33.149:/tmp/systec/ scp health_check.sh oracrp@172.16.33.150:/tmp/systec/ scp health_check.sh oraecd@172.16.33.137:/tmp/systec/ scp health_check.sh oraecp@172.16.33.138:/tmp/systec/ scp health_check.sh oraepd@172.16.33.151:/tmp/systec/ scp health_check.sh oraepq@172.16.33.154:/tmp/systec/ scp health_check.sh orasrd@172.16.33.153:/tmp/systec/ scp health_check.sh orasrp@172.16.33.152:/tmp/systec/ scp health_check.sh oraxid@172.16.33.139:/tmp/systec/ scp health_check.sh oraxip@172.16.35.34:/tmp/systec/ # Production scp health_check.sh orabip@172.16.33.141:/tmp/systec/ scp health_check.sh oracrp@172.16.33.158:/tmp/systec/ scp health_check.sh oraecp@172.16.33.131:/tmp/systec/ scp health_check.sh oraxip@172.16.33.132:/tmp/systec/ scp health_check.sh oraepp@172.16.33.168:/tmp/systec/ scp health_check.sh orasrp@172.16.33.164:/tmp/systec/ scp health_check.sh orabpp@172.16.33.180:/tmp/systec/
System Check
telnet SERVER_IP
USE ansible
# Check database status
export TERM=xterm ansible GDZY_DEV -m raw -a "lsnrctl status" ansible GDZY_PRO -m raw -a "lsnrctl status" ansible GDZY_DEV -m raw -a "echo 'set linesize 200;\n select instance_name,database_status,status,host_name from v\$instance;' | sqlplus / as sysdba | sed -n '1,14p'" ansible GDZY_PRO -m raw -a "echo 'set linesize 200;\n select instance_name,database_status,status,host_name from v\$instance;' | sqlplus / as sysdba | sed -n '1,14p'" grep -A 4 "ORA-" aler*.log | grep -v "ORA-" | grep -v '^\d*$'
# stop database
ansible GDZY_DEV -m raw -a "echo 'set linesize 200;\n shutdown immediate;' | sqlplus / as sysdba" ansible GDZY_DEV -m raw -a "lsnrctl stop" ansible GDZY_PRO -m raw -a "echo 'set linesize 200;\n shutdown immediate;' | sqlplus / as sysdba" ansible GDZY_PRO -m raw -a "lsnrctl stop"
# start database
ansible GDZY_DEV -m raw -a "lsnrctl start" ansible GDZY_PRO -m raw -a "lsnrctl start" ansible GDZY_DEV -m raw -a "echo 'set linesize 200;\n startup;' | sqlplus / as sysdba" ansible GDZY_PRO -m raw -a "echo 'set linesize 200;\n startup;' | sqlplus / as sysdba"
time sh /tmp/health_check.sh | tee /tmp/`hostname`_info_`date +%F`.txt # New time sh /tmp/systec/health_check.sh | tee /tmp/systec/`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
/- select tablespace_name, maxbytes/1024/1024 MAX_SIZE from dba_data_files;
- SELECT NAME from v$datafile;
database size
select ( select sum(bytes)/1024/1024 data_size from dba_data_files ) + ( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) + ( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) + ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) "Size in MB" from dual;
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';