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

试问：如何快速地得到alert警告文件中所有与“ORA-”相关的错误信息？一般的方法：使用Shell的grep命令或sed命令对alert文件进行检索。这种方法是有瑕疵的，首先grep和sed是UNIX操作系统的命令，不具有通用性；其次使用这种方法只能精确地检索到“ORA-”所在的行，无法得到报错信息的出现时间及错误的完整信息。Oracle 11g所提供的ADRCI工具中“show alert”命令的“-p”选项可以较好并灵活地解决了上述问题。简单演示在此，供参考。1.进入到ADRCI命令行界面secooler@secDB /home/oracle$ adrciADRCI: Release 11.2.0.1.0 - Production on Mon Dec 21 21:20:47 2009Copyright (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/secooler3.指定使用编辑器vi打开检索到的内容adrci> set editor vi4.使用“-p”选项检索alert日志，搜索一切包含“ORA-”关键字的内容adrci> show alert -p "message_text like '%ORA-%'"2009-10-28 00:38:12.504000 +08:00ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...RA-00313: open failed for members of log group 2 of thread 12009-10-28 00:38:23.164000 +08:00Errors 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 1ORA-00312: online log 1 thread 1: '/oracle/ora11gR2/oradata/secooler/redo01.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3... ...从上面检索出的内容可见，不仅仅是出错信息，还有发生错误的时间信息，这些信息很重要。5.简化上述变交互操作为直接的Shell交互通过下面的Shell调用一样可以达到我们的检索目的。secooler@secDB /home/oracle$ adrci << EOF> set homepath diag/rdbms/secooler/secooler> show alert -p "message_text like '%ORA-%'"> EOF6.ADRCI命令行的show alert命令的“-p”选项提供的功能还远远不限于此。通过联机帮助可以得到其他可用的字段。adrci> help show alertUsage: 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         timestampNORMALIZED_TIMESTAMP          timestampORGANIZATION_ID               text(65)COMPONENT_ID                  text(65)HOST_ID                       text(65)HOST_ADDRESS                  text(17)MESSAGE_TYPE                  numberMESSAGE_LEVEL                 numberMESSAGE_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    numberERROR_INSTANCE_ID             numberERROR_INSTANCE_SEQUENCE       numberMESSAGE_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 andoutput latest messages as the alert log grows. If num is not specified,the last 10 messages are displayed. If "-f" is specified, new datawill 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 usedto set other editors.[-file ]: Allow users to specify an alert file whichmay not be in ADR. must be specified with full path.Note that this option cannot be used with the -tail optionExamples:show alertshow alert -p "message_text like '%incident%'"show alert -tail 207.更多参看可以见Oracle官方文档8.小结使用ADRCI工具的“show alert”命令可以很灵活的对alert文件进行检索和过滤，对系统的分析alert日志有很大的裨益。善用工具，提高技术含量，减少故障排查时间是我们永恒的追求目标。Good luck.secooler09.12.21-- The End --