Script:利用外部表实现SQL查询Oracle告警日志Alert.log
来源:互联网 发布:网络贩毒暗语 编辑:程序博客网 时间:2024/05/17 01:16
有同学问是否可以用SQL语句直接查询告警日志的内容,即创建一张包含Alert.log内容的表或视图。 实际上之前已经有人这样做了(http://t.cn/SwGvq9),只需要运行一个存储过程即可达到目的, 这里我对原有的语句做了一些改良, 直接执行PL/SQL块即可无需创建存储过程了,而且现在支持RAC了。
--drop table alert_log_view;--drop directory bdump;declare path_bdump varchar2(4000); name_alert varchar2(4000); ins_name varchar2(200);begin select value into path_bdump from sys.v_$parameter where name = 'background_dump_dest'; select 'alert_' || value || '.log' into name_alert from sys.v_$parameter where name = 'instance_name'; select value into ins_name from sys.v_$parameter where name = 'instance_number'; if ins_name = '0' then ins_name := ''; end if; execute immediate 'create or replace directory bdump'||ins_name||' as ''' || path_bdump || ''''; execute immediate 'create table ALERT_LOG_VIEW' || ins_name || ' (MSG_line varchar2(4000) ) ' || ' organization external ' || ' (type oracle_loader ' || ' default directory bdump' || ins_name || ' access parameters ( ' || ' records delimited by newline ' || ' nobadfile ' || ' nologfile ' || ' nodiscardfile ' || ' skip 0 ' || ' READSIZE 10485760 ' || ' FIELDS LDRTRIM ' || ' REJECT ROWS WITH ALL NULL FIELDS ' || ' (MSG_LINE (1:1000) CHAR(1000)) ' || ' ) ' || ' location (''' || name_alert || ''') )' || ' reject limit unlimited ' || ' noparallel nomonitoring ';end;/
执行以上PL/SQL代码,会创建名为bdump$SID的目录 和ALERT_LOG_VIEW$SID的外部表(如RAC中的1号实例PROD1,则为ALERT_LOG_VIEW1,单实例single instance则为 ALERT_LOG_VIEW), 需要时直接查询ALERT_LOG_VIEW即可,譬如要从告警信息中找出最近三天ORA-错误的记录:
col lineno noprintcol ora_error noprintcol msg_line format a132set pages 0 lines 300 trimspool on trim onalter session set nls_date_language = 'american';alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';alter session set sql_trace=false;break on thedatepromptprompt ERROR IN ALERT LOG FILE - LAST 3 DAYSprompt =====================================select "LINENO", "THEDATE", "ORA_ERROR", "MSG_LINE" from (select * from (select lineno, msg_line, thedate, max(case when (ora_error like 'ORA-%' or ora_error like 'PLS-%') then rtrim(substr(ora_error, 1, instr(ora_error, ' ') - 1), ':') else null end) over(partition by thedate) ora_error from (select lineno, msg_line, max(thedate) over(order by lineno) thedate, lead(msg_line) over(order by lineno) ora_error from (select rownum lineno, substr(msg_line, 1, 132) msg_line, case when msg_line like '___ ___ __ __:__:__ ____' then to_date(msg_line, 'Dy Mon DD hh24:mi:ss yyyy') else null end thedate from ALERT_LOG_VIEW)))) where ora_error is not null and thedate >= (trunc(sysdate) - 3) order by thedate示例输出10/11/2011 03:15:49 Thu Nov 10 03:15:49 2011 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc (incident=11105): ORA-00700: soft internal error, arguments: [kgerev1], [600], [600], [700], [], [], [], [], [], [], [], [] Incident details in: /s01/orabase/diag/rdbms/vprod/VPROD1/incident/incdir_11105/VPROD1_ora_5547_i11105.trc Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc (incident=11106): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /s01/orabase/diag/rdbms/vprod/VPROD1/incident/incdir_11106/VPROD1_ora_5547_i11106.trc10/11/2011 03:15:52 Thu Nov 10 03:15:52 2011 Dumping diagnostic data in directory=[cdmp_20111110031552], requested by (instance=1, osid=5547), summary=[incident=11105].
转自:http://www.oracledatabase12g.com/archives/%e5%88%a9%e7%94%a8%e5%a4%96%e9%83%a8%e8%a1%a8%e5%ae%9e%e7%8e%b0sql%e6%9f%a5%e8%af%a2oracle%e5%91%8a%e8%ad%a6%e6%97%a5%e5%bf%97alert-log.html
- Script:利用外部表实现SQL查询Oracle告警日志Alert.log
- Script:利用外部表实现SQL查询Oracle告警日志Alert.log
- 利用外部表实现SQL查询Oracle告警日志Alert.log
- 利用外部表实现SQL查询Oracle告警日志Alert.log
- 使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG)
- 使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG)
- oracle alert log file(告警日志文件)
- ORACLE告警日志文件( alert log file)存储信息
- 如何查看alert.log告警日志
- 使用外部表访问oracle告警日志
- 使用外部表访问oracle告警日志
- 使用外部表访问告警日志文件!!!
- 如何用外部表查看报警日志(alert log)中昨天以来产生的错误
- 查看alert<sid>.log告警文件路径
- 告警日志系列之使用外部表查看ORACLE报警日志 2009-06-19 16:20:48
- 通过外部表查看Alert日志
- 用外部表的方式查询alert日志文件中ora-错误信息
- Oracle告警日志文件
- spring属性和构造器注入
- HNhibernate插入数据子表总是UPDATE原因
- Web开发设计模式PRG:Post/Redirect/Get,防止重复提交表单
- Response.AppendHeader使用大全
- DirectX、Direct3D和OpenGL的区别
- Script:利用外部表实现SQL查询Oracle告警日志Alert.log
- 一些链接
- resolveActivity和queryIntentActivities!!!
- VC+AE开发遇到的问题及其解决之道
- 使用ADO对象进行数据库开发实例
- 音量和分贝(四)- 音量调整
- linux c编程操作数据库(sqlite3应用)
- Servlet方法说明
- jquery 实现的省市区级联,无ajax