5. 将alert文件内容保存为数据库表的记录行.
存放为记录行, 可以让我们对日志文件进行更详细的分析.
drop table alert_log; create table alert_log ( line int primary key, text varchar2(4000) ); CREATE OR REPLACE PACKAGE BODY Pkg_Alert IS g_last_pos Number ; g_last_line Number ; PROCEDURE Load_Alert_To_Lob(Pfilename VARCHAR2) IS ...... PROCEDURE Load_Alert(Pfilename VARCHAR2, prefresh Varchar2 Default 'Y') AS l_Bfile BFILE; l_Last NUMBER; l_Current NUMBER; l_Line NUMBER; l_linestr Varchar2(4000); BEGIN l_Bfile := Bfilename('X$ALERT_LOG$X', Pfilename); Dbms_Lob.Fileopen(l_Bfile); If upper(prefresh) = 'Y' Then l_Last := g_last_pos; l_line := g_last_line; Else l_last := 1; l_Line := 1; Delete alert_log; End If; LOOP l_Current := Dbms_Lob.Instr(l_Bfile, '0A', l_Last, 1); EXIT WHEN(Nvl(l_Current, 0) = 0); l_linestr := Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(l_Bfile, l_Current - l_Last + 1, l_Last)); l_Linestr := REPLACE(l_Linestr, Chr(10), ''); l_Linestr := REPLACE(l_Linestr, Chr(13), ''); INSERT INTO Alert_Log (Line, Text) VALUES (l_Line, l_linestr); l_Last := l_Current + 1; l_Line := l_Line + 1; END LOOP; Commit; g_last_pos := l_last; g_last_line := l_line; Dbms_Lob.Fileclose(l_Bfile); END; BEGIN -- Initialization g_last_pos := 1; g_last_line := 1; END Pkg_Alert; exec pkg_alert.load_alert('&fname')
(这段脚本来源于asktom的这篇文章( Monitoring Alert file , 本人进行了一定的改动)
6. 分析现在alert文件已经保存到表alert_log里了. 对数据的分析/检索可是sql的拿手好戏.
比如: 俺曾经向snow请教过的一个问题:
怎么样实现在一个文本文件中,查找特定的词所有出现的行以及上下各n行。比如说,oracle的alert.log有时候会涨的很大,用vi之类的打开很不方便, 我想只找到ora-所在的行以及上下n行,这样就能快速定位问题。
她给出的解决方法(http://blog.itpub.net/post/5/4486)是用awk来处理.
用sql解决起来就很简单, 并且更完美一些(比如对于连续多行的Ora-错误的情形):
create or replace view v_alert_errs as Select * From alert_log o Where Exists(Select line From alert_log Where text Like 'ORA-%' And line Between o.line-5 And o.line+5) Order By line;
示例:
system@O9I.US.ORACLE.COM> set pagesize 10000 system@O9I.US.ORACLE.COM> column text format a60 system@O9I.US.ORACLE.COM> select * from v_alert_errs; LINE TEXT ---------- ------------------------------------------------------------ 1470 SMON: enabling tx recovery 1471 Tue Jul 05 15:28:56 2005 1472 Database Characterset is ZHS16GBK 1473 Tue Jul 05 15:29:03 2005 1474 Errors in file d:oracleadmino9ibdumpo9i_smon_12504.trc: 1475 ORA-01595: error freeing extent (2) of rollback segment (7)) 1476 ORA-01594: attempt to wrap into rollback segment (7) extent (2) which is being freed 1477 1478 replication_dependency_tracking turned off (no async multima ster replication found) 1479 Completed: alter database open 1480 Wed Jul 06 00:26:35 2005 1481 Thread 1 advanced to log sequence 246 1745 Thread 1 advanced to log sequence 256 1746 Current log# 3 seq# 256 mem# 0: D:ORACLEORADATAO9IREDO 03.LOG 1747 Thu Jul 07 20:57:48 2005 1748 Error 2068 trapped in 2PC on transaction 6.0.30948. Cleaning up. 1749 Error stack returned to user: 1750 ORA-02068: 以下严重错误源于CIQTP 1751 ORA-03113: 通信通道的文件结束 1752 Thu Jul 07 20:58:50 2005 1753 Thread 1 advanced to log sequence 257 1754 Current log# 1 seq# 257 mem# 0: D:ORACLEORADATAO9IREDO 01.LOG 1755 Fri Jul 08 00:20:38 2005 1756 Thread 1 advanced to log sequence 258 2006 Completed: alter database open ........
如果想继续继续美化一下, 在每一段连续的错误信息后,加上一条分割线, 可以这样:
system@O9I.US.ORACLE.COM> Select * From v_alert_errs 2 Union 3 Select v1.line+1, '===============================' 4 From v_alert_errs v1 5 Where Not Exists(Select 1 From v_alert_errs Where line = v1.line+1); LINE TEXT ---------- ------------------------------------------------------------ 1470 SMON: enabling tx recovery 1471 Tue Jul 05 15:28:56 2005 1472 Database Characterset is ZHS16GBK 1473 Tue Jul 05 15:29:03 2005 1474 Errors in file d:oracleadmino9ibdumpo9i_smon_12504.trc: 1475 ORA-01595: error freeing extent (2) of rollback segment (7)) 1476 ORA-01594: attempt to wrap into rollback segment (7) extent (2) which is being freed 1477 1478 replication_dependency_tracking turned off (no async multima ster replication found) 1479 Completed: alter database open 1480 Wed Jul 06 00:26:35 2005 1481 Thread 1 advanced to log sequence 246 1482 =============================== 1745 Thread 1 advanced to log sequence 256 1746 Current log# 3 seq# 256 mem# 0: D:ORACLEORADATAO9IREDO 03.LOG 1747 Thu Jul 07 20:57:48 2005 1748 Error 2068 trapped in 2PC on transaction 6.0.30948. Cleaning up. 1749 Error stack returned to user: 1750 ORA-02068: 以下严重错误源于CIQTP 1751 ORA-03113: 通信通道的文件结束 1752 Thu Jul 07 20:58:50 2005 1753 Thread 1 advanced to log sequence 257 1754 Current log# 1 seq# 257 mem# 0: D:ORACLEORADATAO9IREDO 01.LOG 1755 Fri Jul 08 00:20:38 2005 1756 Thread 1 advanced to log sequence 258 1757 =============================== ......
用这种方法, 你可以对alert进行更多的分析, 比如: 某种特定错误的出现频率, 数据库shutdown/startup的频率等.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/32/viewspace-184226/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/32/viewspace-184226/