資料庫健康檢查 sqlplus 指令碼

#!/bin/bash
# This script. is used to make healthcheck for **** DB everyday by manual
# for **** DB sever keep running normally  purpose .
# The log file of running script. need to keep 7 days
# 2012-03-07
#----------by liguoyin------

export ORACLE_BASE=/home/oraprod
export ORACLE_HOME=${ORACLE_BASE}/product/10.2.0
export ORACLE_SID=CILCPROD

SQLPLUS=${ORACLE_HOME}/bin/sqlplus

LOG_DATE=`date +%Y%m%d`
DEL_DATE=`date +%Y%m%d -d "7 day ago"`

CHECK_LOG=/home/oraprod/healthcheck/log/healthcheck_poms_${LOG_DATE}.log

rm -f /home/oraprod/healthcheck/log/healthcheck_poms_${DEL_DATE}.log

${SQLPLUS} / as sysdba<

spool ${CHECK_LOG}
set linesize 300
set termout off

col NAME for a30
col TYPE for a50

desc v\$instance

col HOST_NAME for a30
col INSTANCE_NAME for a20
select instance_name ,host_name,startup_time,status,database_status from v\$instance;

desc v\$database
select name , log_mode ,open_mode from v\$database;

col name for a60
select status ,name from v\$controlfile;

desc v\$logfile
col member for a60
select group#,status ,type ,member from v\$logfile;

desc dba_tablespaces;
select tablespace_name,status from dba_tablespaces;

desc v\$datafile
col name for a60
select name ,status from v\$datafile;

desc dba_data_files
col file_name for a60
select file_name ,status from dba_data_files;

desc dba_objects
col owner for a20
col object_name for a25
col  object_type for a20
select owner ,object_name ,object_type from dba_objects where status != 'VALID'and owner != 'SYS' and owner != 'SYSTEM';

select owner,object_name,object_type from dba_objects where status = 'INVALID';

desc dba_rollback_segs;
select segment_name ,status from dba_rollback_segs;

desc v\$resource_limit;
col recource_name for a20
col limit_value for a20
col initial_allocation for a20
select resource_name ,max_utilization,initial_allocation,limit_value from v\$resource_limit;

select count(*) from v\$session;

desc v\$session
col username for a20
col program for a30
col machine for a20
select sid ,serial#,username ,program ,machine ,status from v\$session;

desc dba_tables
col owner for a20
select distinct(owner) from dba_tables where tablespace_name ='SYSTEM' and owner != 'SYS' and owner != 'SYSTEM' and owner != 'SYS' and owner != 'SYSTEM';

desc all_tables
col talbe_name for a20
col table_name for a20
col tablespace_name for a20
select a.table_name ,a.next_extent,a.tablespace_name from all_tables a,
(select tablespace_name,max(bytes)as big_chunk from dba_free_space group by tablespace_name)f
where f.tablespace_name = a.tablespace_name
and a.next_extent >f.big_chunk
union
select a.index_name,a.next_extent,a.tablespace_name from all_indexes a,
(select tablespace_name ,max(bytes) as big_chunk from dba_free_space
group by tablespace_name)f
where f.tablespace_name = a.tablespace_name
and a.next_extent>f.big_chunk;

set pages 80
set lines 120
col event for a40
select sid,event,p1,p2,p3,wait_time,seconds_in_wait from v\$session_wait where event not like 'SQL%' and event not like 'rdbms%';

select sql_text from (select * from v\$sqlarea order by disk_reads ) where rownum <=5;

select * from (select parsing_user_id executions ,sorts,command_type,disk_reads,sql_text from v\$sqlarea order by disk_reads desc)where rownum<10;

select * from (select * from v\$system_event where event not like 'SQL%' order by total_waits desc)where rownum<=5;

col username for a12
col opname for a16
col progress for a8
select username,sid,opname,round(sofar*100/totalwork,0)||'%' as progress,time_remaining,sql_text from v\$session_longops,v\$sql where time_remaining <> 0  and sql_address=address and sql_hash_value=hash_value;

set line 240
set verify off
col sid format 999
col pid for 999
col s_# format 999
col username for a9 heading "ora user"
col program for a29
col sql for a60
col osname format a9 heading "OS USER"
select p.pid pid,s.sid sid ,p.spid spid ,s.username username ,s.osuser osname ,p.serial# s_#,p.terminal,p.program program ,p.background , s.status ,rtrim(substr(a.sql_text,1,80)) sql from v\$process p ,v\$session s,v\$sqlarea a where p.addr=s.paddr and s.sql_address = a.address(+) and p.spid like '%&1%';

select segment_name table_name,count(*) extents from dba_segments where owner not in ('SYS','SYSTEM') group by segment_name having count(*)=(select max(count(*)) from dba_segments group by segment_name);

col tablespace_name for a20
col file_name for a60
select df.tablespace_name name ,df.file_name "FILE",F.PHYRDS PYR, F.PHYBLKRD PBR,F.PHYWRTS PYW, F.PHYBLKWRT PBW FROM V\$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# = DF.FILE_ID ORDER BY DF.TABLESPACE_NAME;

SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "NAME", A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTS FROM V\$DATAFILE A, V\$FILESTAT B WHERE A.FILE# = B.FILE#;

col sid for 999999
col username for a10
col schemaname for a10
col osuser for a16
col machine for a16
col terminal for a20
col owner for a10
col object_name for a30
col object_type for a10
select sid,serial#,username,SCHEMANAME,osuser,MACHINE,
terminal,PROGRAM,owner,object_name,object_type,o.object_id
from dba_objects o,v\$locked_object l,v\$session s
where o.object_id=l.object_id and s.sid=l.session_id;

select spid from v\$process where addr not in (select paddr from v\$session);

select table_name,num_rows,chain_cnt From dba_tables Where wner='CTAIS2' And chain_cnt<>0;

Select table_name,num_rows,last_analyzed From user_tables where table_name ='DJ_NSRXX';

SELECT a.VALUE + b.VALUE logical_reads,
 c.VALUE phys_reads,
 round(100*(1-c.value/(a.value+b.value)),4) hit_ratio
 FROM v\$sysstat a,v\$sysstat b,v\$sysstat c
 WHERE a.NAME='db block gets'
 AND b.NAME='consistent gets'
 AND c.NAME='physical reads' ;

select sum(pinhits)/sum(pins)*100 from v\$librarycache;

select name,value from v\$sysstat where name like '%sort%';

select name,value from v\$sysstat where name in ('redo entries','redo buffer allocation retries');

select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';

select A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
   from (select tablespace_name,sum(bytes) total
   from dba_free_space group by tablespace_name) A,
   (select tablespace_name,sum(bytes) total
   from dba_data_files group by tablespace_name) B
   where A.tablespace_name=B.tablespace_name;

select index_name,table_name,tablespace_name,status From dba_indexes Where wner='CTAIS2' And status<>'VALID';

SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status ='DISABLE' and constraint_type='P';

SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';

spool off
exit
EOF

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24558279/viewspace-718724/，如需轉載，請註明出處，否則將追究法律責任。