試問：如何快速地得到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 --

【11g】使用ADRCI工具的“show alert”命令快速檢索出alert中所有“ORA-”錯誤資訊

相關文章