试问：如何快速地得到alert警告文件中所有与“ORA-”相关的错误信息？
一般的方法：使用Shell的grep命令或sed命令对alert文件进行检索。
这种方法是有瑕疵的，首先grep和sed是UNIX操作系统的命令，不具有通用性；其次使用这种方法只能精确地检索到“ORA-”所在的行，无法得到报错信息的出现时间及错误的完整信息。

Oracle 11g所提供的ADRCI工具中“show alert”命令的“-p”选项可以较好并灵活地解决了上述问题。简单演示在此，供参考。

1.进入到ADRCI命令行界面
secooler@secDB /home/oracle$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Mon Dec 21 21:20:47 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

ADR base = "/oracle/ora11gR2"
adrci>

2.设定homepath，指定只查询secooler这个实例的alert文件
adrci> set homepath diag/rdbms/secooler/secooler

3.指定使用编辑器vi打开检索到的内容
adrci> set editor vi

4.使用“-p”选项检索alert日志，搜索一切包含“ORA-”关键字的内容
adrci> show alert -p "message_text like '%ORA-%'"
2009-10-28 00:38:12.504000 +08:00
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
RA-00313: open failed for members of log group 2 of thread 1
2009-10-28 00:38:23.164000 +08:00
Errors in file /oracle/ora11gR2/diag/rdbms/secooler/secooler/trace/secooler_ora_29470.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/ora11gR2/oradata/secooler/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
... ...

从上面检索出的内容可见，不仅仅是出错信息，还有发生错误的时间信息，这些信息很重要。

5.简化上述变交互操作为直接的Shell交互
通过下面的Shell调用一样可以达到我们的检索目的。
secooler@secDB /home/oracle$ adrci << EOF
> set homepath diag/rdbms/secooler/secooler
> show alert -p "message_text like '%ORA-%'"
> EOF

6.ADRCI命令行的show alert命令的“-p”选项提供的功能还远远不限于此。
通过联机帮助可以得到其他可用的字段。
adrci> help show alert

Usage: SHOW ALERT [-p ] [-term]
                    [ [-tail [num] [-f]] | [-file ] ]
Purpose: Show alert messages.

Options:
    [-p ]: The predicate string must be double quoted.
    The fields in the predicate are the fields:
        ORIGINATING_TIMESTAMP         timestamp
        NORMALIZED_TIMESTAMP          timestamp
        ORGANIZATION_ID               text(65)
        COMPONENT_ID                  text(65)
        HOST_ID                       text(65)
        HOST_ADDRESS                  text(17)
        MESSAGE_TYPE                  number
        MESSAGE_LEVEL                 number
        MESSAGE_ID                    text(65)
        MESSAGE_GROUP                 text(65)
        CLIENT_ID                     text(65)
        MODULE_ID                     text(65)
        PROCESS_ID                    text(33)
        THREAD_ID                     text(65)
        USER_ID                       text(65)
        INSTANCE_ID                   text(65)
        DETAILED_LOCATION             text(161)
        UPSTREAM_COMP_ID              text(101)
        DOWNSTREAM_COMP_ID            text(101)
        EXECUTION_CONTEXT_ID          text(101)
        EXECUTION_CONTEXT_SEQUENCE    number
        ERROR_INSTANCE_ID             number
        ERROR_INSTANCE_SEQUENCE       number
        MESSAGE_TEXT                  text(2049)
        MESSAGE_ARGUMENTS             text(129)
        SUPPLEMENTAL_ATTRIBUTES       text(129)
        SUPPLEMENTAL_DETAILS          text(129)
        PROBLEM_KEY                   text(65)

    [-tail [num] [-f]]: Output last part of the alert messages and
    output latest messages as the alert log grows. If num is not specified,
    the last 10 messages are displayed. If "-f" is specified, new data
    will append at the end as new alert messages are generated.

    [-term]: Direct results to terminal. If this option is not specified,
    the results will be open in an editor.
    By default, it will open in emacs, but "set editor" can be used
    to set other editors.

    [-file ]: Allow users to specify an alert file which
    may not be in ADR. must be specified with full path.
    Note that this option cannot be used with the -tail option

Examples:
    show alert
    show alert -p "message_text like '%incident%'"
    show alert -tail 20

7.更多参看可以见Oracle官方文档

8.小结
使用ADRCI工具的“show alert”命令可以很灵活的对alert文件进行检索和过滤，对系统的分析alert日志有很大的裨益。
善用工具，提高技术含量，减少故障排查时间是我们永恒的追求目标。

Good luck.

secooler
09.12.21

-- The End --

ITPub博客

【11g】使用ADRCI工具的“show alert”命令快速检索出alert中所有“ORA-”错误信息

最新文章