作者：IT邦德
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
（Web\java\Python）工作，主要服务于生产制造
现拥有 Oracle 11g OCP/OCM、
Mysql、Oceanbase（OBCA）认证
分布式TBase\TDSQL数据库、国产达梦数据库以及红帽子认证
从业8年DBA工作，在数据库领域有丰富的经验
擅长Oracle数据库运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。 
文章目录
- 前言
- 1.ASM磁盘
- 2.DB Time
- 3. 内存命中率
- 4.表空间监控
- 5.event大事件
- 6. seesion数
- 7. archive日志监控
- 8.无效索引
- 9.无效对象
- 10.锁信息
前言
Oracle数据库巡检，是DBA的日常工作，总结以下脚本分享给大家，直接在服务器上部署即可
1.ASM磁盘
export ORACLE_SID=+ASM1
sqlplus -S / as sysdba << EOF
set linesize 199 pagesize 999
col name for a10
col used_pct for a10
select group_number,name,total_mb,free_mb,
round((total_mb-free_mb)/total_mb*100,2)||'%' as used_pct
from v\$asm_diskgrou 
2.DB Time
sqlplus -S /nolog <<EOF
connect / as sysdba
set linesize 130
set pagesize 999
col BEGIN_TIME for a25
col END_TIME for a25
col HOUR for a5
col DB_LOAD for a10
SELECT
INSTANCE_NUMBER INSTANCE,
to_char(BEGIN_TIME,'hh24') HOUR,
to_char(BEGIN_TIME,'YYYY/MM/DD HH24:MI:SS') BEGIN_TIME,
to_char(END_TIME,'YYYY/MM/DD HH24:MI:SS') END_TIME,
DB_TIME,
to_char(ROUND(DB_TIME/ROUND(TO_NUMBER(END_TIME - BEGIN_TIME) * 24 * 60),2),'fm90.99') AAS,
ROUND((DB_TIME/ROUND(TO_NUMBER(END_TIME - BEGIN_TIME) * 24 * 60))*100/22,2)||'%' DB_LOAD
FROM ( SELECT A.INSTANCE_NUMBER,
A.SNAP_ID,
B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME,
B.END_INTERVAL_TIME + 0 END_TIME,
ROUND(VALUE - LAG( VALUE, 1 , '0')
OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB_TIME"
FROM (SELECT B.SNAP_ID,
INSTANCE_NUMBER,
SUM(VALUE ) / 1000000 / 60 VALUE
FROM DBA_HIST_SYS_TIME_MODEL B
WHERE B.DBID = (SELECT DBID FROM V\$DATABASE)
AND UPPER (B.STAT_NAME) IN UPPER(('DB TIME' ))
GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,
DBA_HIST_SNAPSHOT B
WHERE A.SNAP_ID = B.SNAP_ID
AND B.DBID = (SELECT DBID FROM V\$DATABASE)
AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER
AND A.INSTANCE_NUMBER = 1)
WHERE TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD') >= TO_CHAR(sysdate-1, 'YYYY-MM-DD')
ORDER BY AAS desc;
exit
EOF 3. 内存命中率
sqlplus -S /nolog <<EOF
connect / as sysdba
select 1 - ((physical.value - direct.value - lobs.value) / logical.value)
"Buffer Cache Hit Ratio"
from v\$sysstat physical,v\$sysstat direct,v\$sysstat lobs,v\$sysstat logical
where physical.name = 'physical reads'
and direct.name='physical reads direct'
and lobs.name='physical reads direct (lob)'
and logical.name='session logical reads';
select (1-(sum(getmisses)/sum(gets))) "Dictionary Hit Ratio"
from v\$rowcache;
exit
EOF 4.表空间监控
sqlplus -S /nolog <<EOF
set lines 200
col tablespace_name for a40
set pagesize 9999
connect / as sysdba
select tablespace_name ,pct_used "used(%)" from (
SELECT df.tablespace_name, COUNT (*) datafile_count,
ROUND (SUM (df.BYTES) / 1048576) size_mb,
ROUND (SUM (free.BYTES) / 1048576, 2) free_mb,
ROUND (SUM (df.BYTES) / 1048576 - SUM (free.BYTES) / 1048576, 2 ) used_mb,
ROUND (MAX (free.maxbytes) / 1048576, 2) maxfree,
100 - ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_used,
ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_free
FROM dba_data_files df,
(SELECT tablespace_name, file_id, SUM (BYTES) BYTES, MAX (BYTES) maxbytes FROM dba_free_space GROUP BY tablespace_nam
e, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+) AND df.file_id = free.file_id(+)
GROUP BY df.tablespace_name ORDER BY 8)
where pct_used >= 95;
exit
EOF 5.event大事件
sqlplus -S /nolog <<EOF
connect / as sysdba
set pagesize 9999
col event for a30
col username for a10
col program for a20
col machine for a30
set lines 300
select
username,
sid,
event,
sql_id,
last_call_et,
machine,program
from v\$session 6. seesion数
sqlplus -S / as sysdba <<EOF
select count(0) "TOTAL ACTIVE SESSIONS"
from v\$session 7. archive日志监控
sqlplus -S / as sysdba << EOF
set line 100
select trunc(FIRST_TIME) datum,
count(*) total,
round(10 * sum(blocks * block_size) / 1024 / 1024 ) / 10 MB
from v\$archived_log 8.无效索引
sqlplus -S / as sysdba << EOF
select index_name from dba_ind_partitions where status!='USABLE'
union
select index_name from dba_indexes where status not in ('N/A','VALID');
exit
EOF 9.无效对象
sqlplus -S / as sysdba <<EOF
set pagesize 9999
col owner for a10
col object_name for a30
set lines 200
select owner,object_name,object_type,status
from dba_objects where status !='VALID';
exit
EOF 10.锁信息
sqlplus -S / as sysdba << EOF
col owner for a20
col object_name for a30
set lines 200
select owner,object_name,status,locked_mode,session_id
from dba_objects obj,v\$locked_object