方便檢視11g錯誤日誌的指令碼

westzq1984發表於2012-10-11
指令碼
寫了個指令碼，showalert11.sh 呼叫adrci命令，顯示最近N天的 ORA- 錯誤

[oracle@billdb01 olm]$ ./showalert11.sh 2

ADR Home = /oracle/app/oracle/diag/rdbms/bill1/bill1:
*************************************************************************
2012-10-09 08:45:29.117000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_21045.trc.
2012-10-09 14:00:30.947000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_21030.trc.
2012-10-09 15:03:25.025000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_24370.trc.
2012-10-09 15:13:35.803000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_21085.trc.
2012-10-09 15:28:57.215000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_21044.trc.
2012-10-09 16:45:23.198000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_11987.trc.
2012-10-09 17:19:44.845000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_12118.trc.
2012-10-09 18:19:30.327000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_12039.trc.
2012-10-10 09:15:29.608000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_11987.trc.
2012-10-10 09:25:41.059000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_21085.trc.
2012-10-10 11:25:12.234000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_12039.trc.
2012-10-10 13:01:44.408000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_21045.trc.
2012-10-10 13:51:26.219000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_11987.trc.
2012-10-10 15:46:43.646000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_12013.trc.
2012-10-10 21:14:52.300000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_21045.trc.
2012-10-11 05:37:17.204000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_12039.trc.
2012-10-11 07:54:03.627000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_12071.trc.
2012-10-11 09:06:28.600000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_24370.trc.
2012-10-11 10:00:21.904000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_12013.trc.
2012-10-11 10:00:26.882000 +08:00
ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/bill1/bill1/trace/bill1_ora_12013.trc.

指令碼如下：

[oracle@billdb01 olm]$ cat showalert11.sh
runsql(){
sqlplus -silent "/as sysdba" <
set pagesize 0 feedback off verify off heading off echo off long 9999 linesize 130
$1
EOF
}

TMPTEXT=`runsql "SELECT substr(VALUE,INSTR(VALUE,'diag'))||' '||to_char(SYSDATE-$1,'yyyy-mm-dd') FROM v\\$diag_info WHERE NAME='ADR Home';"`
ADRHOME=`echo $TMPTEXT|awk '{print $1}'`
QRYDATE=`echo $TMPTEXT|awk '{print $2}'`

adrci exec="set home $ADRHOME;show alert -term -p \\\"ORIGINATING_TIMESTAMP>'$QRYDATE' and message_text like '%ORA-%'\\\" " 

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

相關文章