作者：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