oracle日常检查脚本

[plain]

#!/bin/bash

sqlplus -s / as sysdba <

spool /tmp/oraclecheck/dbcheck.log

set echo off

whenever sqlerror continue;

set lines 500

set pages 500

prompt #############################################

prompt Database status:

col host_name for a45

col instance_name for a10

col status for a10

select INSTANCE_NAME,host_name,status,active_state,STARTUP_TIME from gv\$instance;

prompt

prompt ############################################

prompt Asm_diskgroup status and usage:

col name for a10

col state for a15

col pt_free for a5

select name,state,total_mb,free_mb,round(free_mb/total_mb,3)*100||'%' pt_free from v\$asm_diskgroup;

prompt

prompt ###############################################

prompt Session number:

select INST_ID,count(*) from gv\$session group by INST_ID;

prompt

prompt ################################################

prompt Datafile status invalid:

col file_name for a50

select file_name,status from dba_data_files where status='INVALID';

prompt

prompt ###########################################

prompt Tablespace usage:

col tablespace_name for a15

select

f.tablespace_name,

a.total,

f.free,

round((f.free/a.total)*100) "% Free"

from

(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,

(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f

WHERE a.tablespace_name = f.tablespace_name(+)

order by "% Free";

prompt

prompt ##########################################

prompt Current wait event:

col event for a60

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%';

prompt

prompt #######################################

prompt If exits table lock:

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;

prompt

prompt ###########################################

prompt If exits zombie process:

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

prompt

prompt ###########################################

prompt Objects invalid:

col owner for a15

col object_name for a30

SELECT owner, object_name, object_type FROM dba_objects WHERE status= 'INVALID';

prompt

prompt#################################

prompt Constraint invalid:

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

prompt

prompt####################################

prompt Triggers invalid:

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

prompt

spool off

exit

EOF

egrep -v 'prompt|set|whenever|col|spool off' /tmp/oraclecheck/dbcheck.log >/tmp/oraclecheck/dbcheckdone.log

mv /tmp/oraclecheck/dbcheckdone.log /tmp/oraclecheck/dbcheck.log

echo "#############################################">>/tmp/oraclecheck/dbcheck.log

echo "The 500 line from the bottom of BOLO1022 Alert.log abnormal Check:" >>/tmp/oraclecheck/dbcheck.log

echo "#############################################">>/tmp/oraclecheck/dbcheck.log

echo "The 500 line from the bottom of BOLO1022 Listener.log abnormal Check:" >>/tmp/oraclecheck/dbcheck.log

echo "Check date: `date +"%F.%T"`" >>/tmp/oraclecheck/dbcheck.log