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" | egrep --after-context=2 ' STATUS' 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" | egrep --after-context=2 ' 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 pages 999 set lines 400 SELECT df.tablespace_name tablespace_name, max(df.autoextensible) auto_ext, round(df.maxbytes / (1024 * 1024), 2) max_ts_size, round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used, round(df.bytes / (1024 * 1024), 2) curr_ts_size, round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size, round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used, round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size, nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free FROM dba_free_space fs, (select tablespace_name, sum(bytes) bytes, sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes, max(autoextensible) autoextensible from dba_data_files group by tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytes, df.maxbytes UNION ALL SELECT df.tablespace_name tablespace_name, max(df.autoextensible) auto_ext, round(df.maxbytes / (1024 * 1024), 2) max_ts_size, round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used, round(df.bytes / (1024 * 1024), 2) curr_ts_size, round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size, round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used, round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size, nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free FROM (select tablespace_name, bytes_used bytes from V$temp_space_header group by tablespace_name, bytes_free, bytes_used) fs, (select tablespace_name, sum(bytes) bytes, sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes, max(autoextensible) autoextensible from dba_temp_files group by tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytes, df.maxbytes ORDER BY 4 DESC;
TABLESPACE_NAME: This is the Tablespace Name.
AUTO_EXT: If the datafiles are ‘Auto Extendable’ or not.
Please Note: This is using a max function, so if all are ‘NO’, then the ‘NO’ is true for all datafiles, however if one is ‘YES’, then the ‘YES’ is possible for one through to all of the datafiles.
MAX_TS_SIZE: This is the maximum Tablespace Size if all the datafile reach their max size.
MAX_TS_PCT_USED: This is the percent of MAX_TS_SIZE reached and is the most important value in the query, as this reflects the true usage before DBA intervention is required.
CURR_TS_SIZE: This is the current size of the Tablespace.
USED_TS_SIZE: This is how much of the CURR_TS_SIZE is used.
TS_PCT_USED: This is the percent of CURR_TS_SIZE which if ‘Auto Extendable’ is on, is a little meaningless. Use MAX_TS_PCT_USED for actual usage.
FREE_TS_SIZE: This is how much is free in CURR_TS_SIZE.
TS_PCT_FREE: This is how much is free in CURR_TS_SIZE as a percent.
Please Note: All sizes are in Megabytes, this can be changed to Gigabytes by added a ‘/1024’ to the columns.
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;
Displays Space Usage for Each Datafile
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN "Tablespace Name" FORMAT A20
COLUMN "File Name" FORMAT A80
SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,80) "File Name",
Round(df.bytes/1024/1024,0) "Size (M)",
decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
sum(bytes) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name
/
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';
- Recompiling All Invalid Objects
SQL> @Oracle_home/rdbms/admin/utlrp.sql
检查碎片
SELECT a.tablespace_name,
TRUNC (
SQRT (MAX (blocks)/SUM (blocks))
* (100/SQRT (SQRT (COUNT (blocks)))),
2)
fsfi
FROM dba_free_space a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name
AND b.contents NOT IN ('TEMPORARY', 'UNDO', 'SYSAUX')
GROUP BY A.tablespace_name
ORDER BY fsfi;