#acl merlyn:read,write All:read <> = 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; }}} ### Column definitions ### 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; }}}