# ---------------------------------------------------- #
#                      系统负载                        #
# ---------------------------------------------------- #
top -n1|head -20
netstat -apn|wc -l
netstat -pn -l|grep 1521 -c
df -h
 
# ---------------------------------------------------- #
#                      检测实例                        #
# ---------------------------------------------------- #
ps -ef|grep ora_.mon |grep -v grep
ps -ef|egrep "_pmon_|_smon_|_arc._|_dbw._|_lgwr_|_ckpt_" |egrep -v egrep
 
# ---------------------------------------------------- #
#                      检测监听                        #
# ---------------------------------------------------- #
netstat -pn -l -t|grep 1521|grep -v grep
ps -ef|grep tnslsnr|grep -v grep
 
 
# ---------------------------------------------------- #
#                   检测Alert日志                      #
# ---------------------------------------------------- #
sed -n "/`date -d "-3 day" +"%a %b %e"`/,$"p ${ORACLE_BASE}/admin/${ORACLE_SID}/bdump/alert_${ORACLE_SID}.log |grep ORA- -B 2 -A 5
 
 
# ---------------------------------------------------- #
#                   查询数据库状态                     #
# ---------------------------------------------------- #
column PLATFORM_NAME format a30
column DB_UNIQUE_NAME format a20
select DBID,NAME,DB_UNIQUE_NAME,to_char(CREATED,'yyyy-mm-dd') CREATD,
to_char(RESETLOGS_TIME,'yyyy-mm-dd') RSTIME, 
LOG_MODE,OPEN_MODE,
DATABASE_ROLE DB_ROLE,
GUARD_STATUS DGSTAT,
PLATFORM_NAME,
FLASHBACK_ON
from v$database;
 
 
# ---------------------------------------------------- #
#                   表空间使用                         #
# ---------------------------------------------------- #
column name format a20
column "Used (M)" format a20
column "Free (MB)" format a20
 
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
       TO_CHAR(NVL(a.bytes/1024/1024, 0),'99,999,990.900') "Size (M)", 
       NVL(a.bytes -NVL(f.bytes, 0), 0)/1024/1024 ||'/'||NVL(a.bytes/1024/1024, 0) "Used (M)",
       TO_CHAR(NVL((a.bytes -NVL(f.bytes, 0)) / a.bytes , 0)* 100, '990.00')||'%' "Used %" ,
       nvl(f.bytes,0)/1024/1024||'/'||nvl(a.bytes/1024/1024,0) "Free (MB)",
       to_char(nvl(nvl(f.bytes,0) / a.bytes,0)*100,'990.00')||'%' "Free (%)"
FROM sys.dba_tablespaces d, 
    (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
    (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f 
WHERE d.tablespace_name = a.tablespace_name(+) 
AND d.tablespace_name = f.tablespace_name(+) 
AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL 
SELECT d.status "Status",d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management", 
     TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
     NVL(t.bytes, 0)/1024/1024||'/'||NVL(a.bytes/1024/1024, 0) "Used (M)",
     TO_CHAR(NVL(t.bytes / a.bytes , 0)* 100, '990.00')||'%' "Used %",
     (a.bytes-nvl(t.bytes,0))/1024/1024||'/'||nvl(a.bytes/1024/1024,0) "Free (MB)",    
     to_char(nvl((a.bytes-nvl(t.bytes,0)) / a.bytes,0)*100,'990.00')||'%' "Free (%)"
FROM sys.dba_tablespaces d, 
  (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
  (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY';
 
 
# ---------------------------------------------------- #
#                   数据文件使用                       #
# ---------------------------------------------------- #
col file_name format a50
SELECT df.file_id, df.file_name, df.size_mb,
              NVL (free.maxfree, 0) maxfree_mb,
              ROUND (NVL (free.free_mb, 0), 2) free_mb,
              100 - ROUND (100.0 * NVL (free.free_mb, 0) / df.size_mb, 2) pct_used,
              ROUND (100.0 * NVL (free.free_mb, 0) / df.size_mb, 2) pct_free,
     df.status
         FROM (SELECT file_id, file_name, tablespace_name, BYTES / 1048576 size_mb,status
                 FROM dba_data_files) df,
              (SELECT   file_id, SUM (BYTES) / 1048576 free_mb,
                        TRUNC (MAX (BYTES / 1024 / 1024), 2) maxfree
                   FROM dba_free_space
               GROUP BY file_id) free
        WHERE df.file_id = free.file_id(+)
        ORDER BY 2;
 
 
# ---------------------------------------------------- #
#                   等待事件                           #
# ---------------------------------------------------- #
select event, sum(seconds_in_wait) "wait time(s)",count(sid) "count" from v$session_wait group by event; 
 
 
# ---------------------------------------------------- #
#                   引起等待事件的语句                 #
# ---------------------------------------------------- #
select s.sql_text, sw.event 
from v$session b,v$session_wait sw,v$sqltext s 
where b.sid=sw.sid 
and sw.event not like '%SQL*Net%' 
and sw.EVENT NOT LIKE 'rdbms%' 
and s.hash_value=b.sql_hash_value 
and s.sql_id=b.sql_id 
order by s.address,s.piece;
 
 
# ---------------------------------------------------- #
#                   redo log    切换频率               #
# ---------------------------------------------------- #
SELECT   TO_CHAR (first_time, 'yyyy-mm-dd') DAY, COUNT (*) switch_times, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) h00, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) h01, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) h02, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) h03, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) h04, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) h05, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) h06, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) h07, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) h08,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) h09,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) h10,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) h11,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) h12,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) h13,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) h14,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) h15,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) h16,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) h17,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) h18,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) h19,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) h20,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) h21,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) h22,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) h23
FROM v$log_history
WHERE first_time > TRUNC (SYSDATE - 1)
GROUP BY ROLLUP (TO_CHAR (first_time, 'yyyy-mm-dd'));
 
 
# ---------------------------------------------------- #
#                   redo log    缓冲区                 #
# ---------------------------------------------------- #
期望结果：应该小于5%。
巡检说明：由于数据库的所有DML和DLL操作在执行之前必须在重做日志缓冲区生成一条记录，故重做日志缓冲区内的竞争将严重影响数据库的性能。在重做日志缓冲区内的竞争主要有两类，latch竞争和过量请求竞争。
SELECT    TO_CHAR (ROUND ((r.VALUE / e.VALUE) * 100, 2), '990.99' ) || '%' "redolog buffer retry ratio" FROM v$sysstat r, v$sysstat e WHERE r.NAME = 'redo buffer allocation retries' AND e.NAME = 'redo entries';
 
 
# ---------------------------------------------------- #
#                   检查无效的对象                     #
# ---------------------------------------------------- #
select owner,object_id,object_name,object_type,status from dba_objects where owner in ( 'SMS', 'MMS', 'WAP', 'UNINET', 'SYS', 'SYSTEM')  and status != 'VALID' order by owner;
 
SELECT object_id, owner || '.' || object_name object_name, object_type, status, TO_CHAR (created, 'yy-mm-dd hh24:mi:ss') created, TO_CHAR (last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') last_ddl_time FROM all_objects WHERE status != 'VALID';