oracle巡检脚本

精选转载

张景可123 2016-01-17 17:08:51 博主文章分类：Oracle常见问题解决方案

文章标签 oracle 检测 文章分类 Oracle 数据库

# ---------------------------------------------------- #

# 系统负载 #

# ---------------------------------------------------- #

top -n1|head -20

netstat -apn|wc -l

netstat -pn -l|grep 1521 -c

df -h

# ---------------------------------------------------- #

# 检测实例 #

# ---------------------------------------------------- #

ps -ef|grep ora_.mon |grep -v grep

# ---------------------------------------------------- #

# 检测监听 #

# ---------------------------------------------------- #

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';