Oracle巡检脚本大全，服务器可直接部署

原创 IT邦德 2021-11-14

9298

作者：IT邦德

中国DBA联盟(ACDU)成员，目前从事DBA及程序编程（Web\java\Python）工作，主要服务于生产制造
现拥有 Oracle 11g  OCP/OCM、Mysql、Oceanbase（OBCA）认证
分布式TBase\TDSQL数据库、国产达梦数据库以及红帽子认证
从业8年DBA工作，在数据库领域有丰富的经验
B站主播Oracle、Mysql、PG实战课程，请搜索：jeames007

微信：jem_db
QQ交流群:168797397
更多内容请关注B站（搜索jeames007）
复制

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_diskgroup;
exit
EOF

复制

2.dbtime

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
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_n
ame, 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
复制

4.内存命中率

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
复制

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 
where username is not null and username
 not in ('SYS','SYSTEM') and status='ACTIVE'  
and event not like 'SQL*Net%' and event not like '%rdbms%';
exit
EOF
复制

6.无效索引

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
复制

7.无效对象

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
复制

8.session数监控

sqlplus -S / as sysdba <<EOF
select count(0) "TOTAL ACTIVE SESSIONS" 
from v\$session 
where username is not null and status='ACTIVE';
exit 
EOF
复制

9.归档日志大小

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
 group by trunc(FIRST_TIME)
 order by 1;
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 l
where obj.object_id=l.object_id;
exit
EOF
复制

11，集群命令格式化

$CRS_HOME/bin/srvctl status database -d PEDBDB
echo 'crs status'

CRS_HOME=/u01/crs/oracle/product/10.2.0/crs
RSC_KEY=$1
QSTAT=-u 
AWK=/usr/bin/awk

$AWK \
  'BEGIN {printf "%-45s %-10s %-18s\n", " Resource name", "Target", "State";
          printf "%-45s %-10s %-18s\n", "--------------", "------", "-----";}'
$CRS_HOME/bin/crs_stat $QSTAT | $AWK \
 'BEGIN { FS="="; state = 0; }
  $1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
  state == 0 {next;}
  $1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
  $1~/STATE/ && state == 2 {appstate = $2; state=3;}
  state == 3 {printf "%-45s %-10s %-18s\n", appname, apptarget, appstate; state=0;}'
复制