续: 在数据库中远程分析服务器上的alert文件(1)

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的频率等.

在数据库中远程分析服务器上的alert文件(2)