一个数据库巡检脚本

!echo ''
!echo '##########查看表空间使用情况##########'

set linesize 1000
col tablespace_name for a30
select a.tablespace_name,a.all_space,b.free_space,b.free_space/a.all_space*100 free_ratio from
(select t.tablespace_name, round(sum(bytes/(1024*1024)),0) all_space
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name) a,
(select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name) b where a.tablespace_name=b.tablespace_name;

 

!echo ''
!echo '##########查看表空间碎片情况##########'

col tablespace_name for a30
select tablespace_name,count(*) chunks,max(bytes/1024/1024) max_chunk
from dba_free_space
group by tablespace_name;

!echo ''
!echo '##########查看控制文件情况##########'
col name for a30
select status,name from v$controlfile;

!echo ''
!echo '##########查看redo文件情况##########'
col member for a30
select * from v$logfile;

!echo ''
!echo '##########查看归档文件路径##########'
col name for a30
col value for a30
select name,value from v$parameter where name like '%log_archive_dest_%';

!echo ''
!echo '##########查看数据文件##########'

col file_name for a30
select file_id,file_name,status from dba_data_files;

!echo ''
!echo '##########查看无效对象##########'
col object_name for a30
col object_type for a30
col status for a30
select owner,object_name,object_type,status from dba_objects where status='INVALID';

!echo ''
!echo '##########查看SCN信息##########'
select dbms_flashback.get_system_change_number, SCN_TO_TIMESTAMP(dbms_flashback.get_system_change_number) from dual;

!echo ''
!echo '##########查看高速缓冲区使用情况##########'

select sum(decode(NAME, 'consistent gets', VALUE, 0)) "Consistent Gets",
sum(decode(NAME, 'db block gets', VALUE, 0)) "DB Block Gets",
sum(decode(NAME, 'physical reads', VALUE, 0)) "Physical Reads",
round((sum(decode(name, 'consistent gets', value, 0)) +
sum(decode(name, 'db block gets', value, 0)) -
sum(decode(name, 'physical reads', value, 0))) /
(sum(decode(name, 'consistent gets', value, 0)) +
sum(decode(name, 'db block gets', value, 0))) * 100,
2) "Hit Ratio"
from v$sysstat;

!echo ''
!echo '##########查看库缓存命中率##########'

select Sum(Pins)/(Sum(Pins) + Sum(Reloads)) * 100 "Hit Ratio" from V$LibraryCache;

!echo ''
!echo '##########查看数据字典缓存命中率##########'

select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v$rowcache;

!echo ''
!echo '##########查看库缓存失误率##########'

select sum(PINS) pins, sum(RELOADS) misses, sum(RELOADS) / sum(PINS) "library cache miss_ratio" from v$librarycache;

!echo ''
!echo '##########查看数据字典缓存失误率##########'

SELECT SUM (getmisses) misses,SUM (gets) gets,SUM (getmisses) / SUM (gets) "Dictionary Cache miss Ratio"
FROM v$rowcache;

!echo ''
!echo '##########查看监听情况##########'

!lsnrctl status

!echo ''
!echo '##########查看当前会话情况##########'

col os_user_name for a10
col program for a30
col serial_num for a30
col terminal for a10
col user_name for a10
select s.sid,s.osuser os_user_name, status session_status, s.terminal terminal, s.program program,
s.username user_name from v$session s, v$process p where s.paddr=p.addr and s.type = 'USER'
order by s.username, s.osuser;

!echo ''
!echo '##########查看用户情况##########'
col username for a30
col account_status for a20
col default_tablespace for a20
col temporary_tablespace for a20
col profile for a20
select user_id,username,account_status,default_tablespace,temporary_tablespace,profile from dba_users;

!echo ''
!echo '##########查看OCR配置情况##########'

!olsnodes
!ocrcheck
!ocrconfig -showbackup
!crsctl check crs

!echo ''
!echo '##########查看RAC资源情况##########'

!crs_stat -t
!srvctl config database
!srvctl config database -d dbname
!srvctl status database -d dbname
!srvctl status instance -d dbname -i instname
!srvctl start nodeapps -n node1

!echo ''
!echo '##########查看数据库补丁##########'

!$ORACLE_HOME/OPatch/opatch lsinventory

exit
exit
EOF