- #!/bin/bash
- sqlplus -s / as sysdba <<EOF
- spool /tmp/oraclecheck/dbcheck.log
- set echo off
- whenever sqlerror continue;
- 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 Top 5 wait event:
- col event for a60
- col wait_class for a30
- select *
- from (select case when event is null then 'cpu time'
- else event end enent, round(sum(wait_time + time_waited)/1000000,1) wait_time, wait_class
- from gv\$active_session_history active_session_history
- where sample_time between sysdate - 60 / 2880 and sysdate
- group by case when event is null then 'cpu time'
- else event end, wait_class
- order by 2 desc)
- where rownum <= 5
- order by 2 desc;
- prompt
- prompt ###################################################################################################################################
- prompt If exits table lock:
- col username for a10
- col MACHINE for a35
- col PROGRAM for a46
- col owner for a10
- col object_name for a30
- col object_type for a10
- select sid,serial#,username,MACHINE,PROGRAM,owner,object_name,object_type
- 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
- prompt####################################################################################################################################
- prompt Archivelog Increate:
- set numf '9999999.99'
- SELECT TRUNC(FIRST_TIME) day,
- TRUNC(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024, 2) "size(GB/DAY)"
- FROM V\$ARCHIVED_LOG
- where TRUNC(FIRST_TIME) > sysdate - 8
- and TRUNC(FIRST_TIME) <> TRUNC(sysdate)
- GROUP BY TRUNC(FIRST_TIME)
- ORDER BY 1 DESC;
- prompt
- prompt
- spool off
- exit
- EOF
- echo "####################################################################################################################################">>/tmp/oraclecheck/dbcheck.log
- echo "The localhost disk Check:" >>/tmp/oraclecheck/dbcheck.log
- df -h>>/tmp/oraclecheck/dbcheck.log
- echo " ">>/tmp/oraclecheck/dbcheck.log
- echo "The 500 line from the bottom of rac2 Alert.log abnormal Check:" >>/tmp/oraclecheck/dbcheck.log
- tail -500 /opt/oracle/admin/STATRAC/bdump/alert_STATRAC2.log| egrep -i "Error|Fail|WARNING|Invalid|Global Enqueue Services|dead|ORA-" >>/tmp/oraclecheck/dbcheck.log
- echo "####################################################################################################################################">>/tmp/oraclecheck/dbcheck.log
- echo "The 500 line from the bottom of rac2 Listener.log abnormal Check:" >>/tmp/oraclecheck/dbcheck.log
- tail -500 /opt/oracle/product/10.2.0/db_1/network/log/listener.log|egrep -i "Error|WARNING|Invalid|Global Enqueue Services|dead|ORA-" >>/tmp/oraclecheck/dbcheck.log
- echo "####################################################################################################################################">>/tmp/oraclecheck/dbcheck.log
- echo "The rac1 disk Check:" >>/tmp/oraclecheck/dbcheck.log
- ssh rac1 'df -h'>>/tmp/oraclecheck/dbcheck.log
- echo " ">>/tmp/oraclecheck/dbcheck.log
- echo "The 500 line from the bottom of rac1 Alert.log abnormal Check" >>/tmp/oraclecheck/dbcheck.log
- ssh rac1 'tail -500 /opt/oracle/admin/STATRAC/bdump/alert_STATRAC1.log| egrep -i "Error|Fail|WARNING|Invalid|Global Enqueue Services|dead|ORA-"' >> /tmp/oraclecheck/dbcheck.log
- echo "####################################################################################################################################">>/tmp/oraclecheck/dbcheck.log
- echo "The 500 line from the bottom of rac1 Listener.log abnormal Check:" >>/tmp/oraclecheck/dbcheck.log
- ssh rac1 'tail -500 /opt/oracle/product/10.2.0/db_1/network/log/listener.log| egrep -i "Error|WARNING|Invalid|Global Enqueue Services|dead|ORA-"' >> /tmp/oraclecheck/dbcheck.log
- echo "####################################################################################################################################">>/tmp/oraclecheck/dbcheck.log
- echo "The local standby disk Check:" >>/tmp/oraclecheck/dbcheck.log
- ssh standby1 'df -h'>>/tmp/oraclecheck/dbcheck.log
- echo " ">>/tmp/oraclecheck/dbcheck.log
- echo "The local standby logsync Check:" >>/tmp/oraclecheck/dbcheck.log
- ssh standby1 '/opt/cron/logsyncchk.sh'>>/tmp/oraclecheck/dbcheck.log
- echo "####################################################################################################################################">>/tmp/oraclecheck/dbcheck.log
- echo "The remote standby disk Check:" >>/tmp/oraclecheck/dbcheck.log
- ssh standby2 'df -h'>>/tmp/oraclecheck/dbcheck.log
- echo " ">>/tmp/oraclecheck/dbcheck.log
- echo "The remote standby logsync Check:" >>/tmp/oraclecheck/dbcheck.log
- ssh standby2 '/opt/cron/logsyncchk.sh'>>/tmp/oraclecheck/dbcheck.log
- egrep -v 'SQL|Copyright|Connected to:|Oracle Database|With the Partitioning|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 "Check date: `date +"%F.%T"`" >>/tmp/oraclecheck/dbcheck.log
logsyncchk.sh:
- #!/bin/bash
- export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
- export ORACLE_SID=STATDG
- export PATH=$ORACLE_HOME/bin:$PATH
- sqlplus / as sysdba <<EOF
- set linesize 150
- col name for a45
- SELECT
- ARCH.THREAD# "Thread",name,
- ARCH.SEQUENCE# "Last Sequence Received",
- APPL.SEQUENCE# "Last Sequence Applied",
- (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
- FROM
- (SELECT THREAD# ,SEQUENCE#,name FROM V\$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V\$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
- (SELECT THREAD# ,SEQUENCE# FROM V\$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
- FROM V\$LOG_HISTORY GROUP BY THREAD#)) APPL
- WHERE ARCH.THREAD# = APPL.THREAD#
- ORDER BY 1;
- select * from v\$archive_gap;
- exit
- EOF
单独sql检查脚本:
- <pre name="code" class="sql">set echo off
- whenever sqlerror continue;
- 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 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||'M' "totol",
- f.free||'M' "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 Top 5 wait event:
- col event for a60
- col wait_class for a30
- select *
- from (select case when event is null then 'cpu time'
- else event end enent, round(sum(wait_time + time_waited)/1000000,1) wait_time, wait_class
- from v$active_session_history active_session_history
- where sample_time between sysdate - 60 / 2880 and sysdate
- group by case when event is null then 'cpu time'
- else event end, wait_class
- order by 2 desc)
- where rownum <= 5
- order by 2 desc;
- prompt
- prompt ############################################################################################
- prompt Log file check:
- col member for a50
- col size for a5
- select l.GROUP#,
- lf.MEMBER,
- l.SEQUENCE#,
- l.BYTES / 1024 / 1024 || 'M' "size",
- l.STATUS
- from v$log l, v$logfile lf
- where l.GROUP# = lf.GROUP#;
- prompt
- prompt ##############################################################################################
- prompt Top 5 io SQL:
- col sql for a60
- SELECT sql, disk_reads, executions
- FROM (SELECT sql_text sql,
- disk_reads,
- executions,
- disk_reads / executions "Reads/Exec",
- hash_value,
- address
- FROM V$SQLAREA
- WHERE disk_reads > 1000
- ORDER BY disk_reads DESC)
- WHERE rownum <= 5;
- prompt
- prompt ################################################################################################
- col sql for a60
- select sql, cpu_time, elapsed_time
- from (select sql_text sql,
- round(cpu_time / 1000000) cpu_time,
- round(elapsed_time / 1000000) elapsed_time,
- disk_reads,
- buffer_gets,
- rows_processed
- from v$sqlarea
- order by cpu_time desc, disk_reads desc)
- where rownum < = 5;
- prompt
- prompt ##################################################################################################
- prompt Top 10 io dbfile:
- col name for a60
- select *
- from (select df.name, phyrds, phywrts
- from v$filestat fs, v$dbfile df
- where fs.file# = df.file#
- order by fs.phyrds desc)
- where rownum < 11;
- prompt
- prompt ##################################################################################################
- prompt If exits table lock:
- col username for a10
- col MACHINE for a35
- col PROGRAM for a46
- col owner for a10
- col object_name for a30
- col object_type for a10
- select sid,serial#,username,MACHINE,PROGRAM,owner,object_name,object_type
- 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
- </pre><br>
- <br>
- <p></p>
- <pre></pre>
- <p></p>