【DB笔试面试536】在Oracle中，Oracle 10g和11g告警日志文件的位置在哪里？-无忧网

【DB笔试面试536】在Oracle中，Oracle 10g和11g告警日志文件的位置在哪里？ - 文章图片

? ? ? ? ? ?题目 ? ? ? ? 部分

在Oracle中，Oracle 10g和11g告警日志文件的位置在哪里？

? ? ?
? ? ? ? ? ?答案部分 ? ? ? ? ?

作为一名DBA，必须知道告警日志是什么，在何处。实时的监控数据库的告警日志是必须进行的工作。监控并且根据不同的告警级别，通过邮件或短信发送告警信息给DBA，这可以帮助DBA及时了解数据库的变化与异常，及时响应并介入处理。

告警日志文件有2种类型，一种是纯文本格式的，另外一种是xml文件格式的，平时查看最多地是纯文本格式的告警日志。告警日志的内容包含：消息和错误的类型、ORA-600内部错误、ORA-1578块损坏错误、ORA-12012作业队列错误、实例启动关闭，恢复等信息、特定的DDL命令、影响表空间，数据文件及回滚段的命令、可持续的命令被挂起、LGWR不能写入到日志文件、归档进程启动信息、调度进程的相关信息和动态参数的修改信息等。

无论是Oracle 10g还是11g，其告警日志的位置都可以由参数BACKGROUND_DUMP_DEST来查询，只不过在Oracle 11g中位置有所变化。在Oracle 10g中，告警日志一般在$ORACLE_BASE/admin/$ORACLE_SID/bdump目录下：

1SYS@lhrdb>?SHOW?PARAMETER?BACKGROUND_DUMP_DEST
2NAME?????????????????????????????????TYPE????????VALUE
3------------------------------------?-----------?------------------------------
4background_dump_dest?????????????????string??????/u01/app/oracle/admin/lhrdb/bdump

? ? ?在Oracle?11g中，告警日志一般在$ORACLE_BASE/diag/rdbms/$DBNAME/$ORACLE_SID/trace目录下 ? ? ? ： ? ? ?

1SYS@lhrdb>?SHOW?PARAMETER?BACKGROUND_DUMP_DEST
2
3NAME?????????????????????????????????TYPE???????????????????VALUE
4------------------------------------?----------------------?------------------------------
5background_dump_dest?????????????????string?????????????????/oracle/app/oracle/diag/rdbms/lhrdb/lhrdb/trace
6[ZFZHLHRDB1:oracle]:/oracle>cd?/oracle/app/oracle/diag/rdbms/lhrdb/lhrdb/trace
7[ZFZHLHRDB1:oracle]:/oracle/app/oracle/diag/rdbms/lhrdb/lhrdb/trace>ls?-l?alert*
8-rw-r-----????1?oracle???asmadmin???13852942?Oct?17?13:22?alert_lhrdb.log

? ? ?从Oracle?12.1.0.1开始，参数BACKGROUND_DUMP_DEST已废弃，告警日志的位置应该由以下SQL来查询 ? ? ? ： ? ? ?

1SQL>?SELECT?d.VALUE?FROM?V$DIAG_INFO?d?WHERE?d.NAME='Diag?Trace';
2VALUE
3---------------------------------------------------------
4/u01/app/oracle/diag/rdbms/lhrdb/lhrdb/trace

? ? ?需要注意的是，该SQL也适用于Oracle?11g的版本。由于告警日志按时间的先后顺序不断累积存储，久而久之，势必造成告警日志的过大，难以维护，查找相关的信息也不方便。另外，若告警日志超大，也会严重影响系统的性能。这里提供3种办法来管理告警日志：①使用外部表方式来管理告警日志将大大简化维护工作量，也可以更直关的获取所需的信息。②视图V$DIAG_ALERT_EXT对应的基表里存储了告警日志的内容，可以根据该视图将告警日志的内容存储在历史表中。③利用SHELL脚本定时将告警日志进行备份，防止告警日志过大而影响系统性能。1．外部表管理告警日志的脚本如下所示：

 1drop?directory?DIR_ALERT;
 2create?directory?DIR_ALERT?as?'/u01/app/oracle/diag/rdbms/orclasm/orclasm/trace';
 3
 4drop?table?alert_log;
 5create?table?alert_log(?
 6　　text?varchar2(500)?
 7　　)organization?external?
 8　　(type?oracle_loader?
 9　　default?directory?DIR_ALERT?
10　　access?parameters?
11　　(records?delimited?by?newline?
12　　)location('alert_orclasm.log')?
13　　)?reject??limit??unlimited;
14
15select?*?from?alert_log?where?text?like?'ORA-%';?

? ? ?查看最新的10条告警日志记录：

1select?*?from?(
2select?rownum?rn,a.text?from?alert_log?a)??b?where?b.rn>=(select?count(1)-10??from?alert_log?a);

? ? ?看最新的10条ORA告警日志记录：

 ?1SELECT?*
 ?2FROM???(SELECT?rownum?rn,
 ?3???????????????a.text
 ?4????????FROM???alert_log?a
 ?5????????WHERE??a.text?LIKE?'ORA-%')?b
 ?6WHERE??b.rn?>=
 ?7???????(SELECT?COUNT(1)?-?10?FROM?alert_log?a?WHERE?a.text?LIKE?'ORA-%');
 ?8
 ?9
 10--2．利用V$DIAG_ALERT_EXT记录历史告警日志的脚本如下所示：
 11---drop?table??XB_ALERTLOG_ALL_LHR?;?
 12create?table?XB_ALERTLOG_ALL_LHR
 13(
 14ID????????????????NUMBER?primary?key,
 15alert_date????????date,
 16message_text??????VARCHAR2(3000),
 17message_type??????NUMBER,
 18message_level?????NUMBER,
 19message_id????????VARCHAR2(67),
 20message_group?????VARCHAR2(67),
 21detailed_location?VARCHAR2(163),
 22problem_key???????VARCHAR2(67),
 23record_id?????????NUMBER,
 24organization_id???VARCHAR2(67),
 25component_id??????VARCHAR2(67),
 26host_id???????????VARCHAR2(67),
 27host_address??????VARCHAR2(49),
 28client_id?????????VARCHAR2(67),
 29module_id?????????VARCHAR2(67),
 30process_id????????VARCHAR2(35)?
 31)?nologging
 32partition?by?range(alert_date)
 33?interval(numtoyminterval(1,'month'))??
 34?(partition?P201406??VALUES?LESS?THAN(TO_DATE('201407','YYYYMM')));
 35
 36--drop?SEQUENCE?S_XB_SQL_MONITOR_LHR;
 37CREATE?SEQUENCE?S_XB_ALERTLOG_ALL_LHR?START?WITH?1?INCREMENT?BY?1?cache?20;
 38
 39create?index?ind_ALERTLOG_ALL_In_Date?on???XB_ALERTLOG_ALL_LHR(ALERT_DATE,Record_Id)?local?nologging;
 40
 41
 42---------记录历史告警日志
 43??CREATE??PROCEDURE?p_alert_log_lhr?AS
 44
 45????????v_max_recordid?NUMBER;
 46????????v_max_date?????DATE;
 47
 48????BEGIN
 49
 50????????SELECT?MAX(a.record_id),
 51???????????????MAX(a.alert_date)
 52????????INTO???v_max_recordid,
 53???????????????v_max_date
 54????????FROM???XB_ALERTLOG_ALL_LHR?a
 55????????WHERE??a.alert_date?>=?SYSDATE?-?360?/?1440?--3h'之前
 56????????AND????a.alert_date?<=?SYSDATE;
 57
 58????????INSERT?INTO?XB_ALERTLOG_ALL_LHR?nologging
 59????????????(ID,
 60?????????????ALERT_DATE,
 61?????????????MESSAGE_TEXT,
 62?????????????MESSAGE_TYPE,
 63?????????????MESSAGE_LEVEL,
 64?????????????MESSAGE_ID,
 65?????????????MESSAGE_GROUP,
 66?????????????DETAILED_LOCATION,
 67?????????????PROBLEM_KEY,
 68?????????????RECORD_ID,
 69?????????????ORGANIZATION_ID,
 70?????????????COMPONENT_ID,
 71?????????????HOST_ID,
 72?????????????HOST_ADDRESS,
 73?????????????CLIENT_ID,
 74?????????????MODULE_ID,
 75?????????????PROCESS_ID)
 76????????????SELECT?s_XB_ALERTLOG_ALL_LHR.Nextval,
 77???????????????????to_date(to_char(a.ORIGINATING_TIMESTAMP,
 78???????????????????????????????????'YYYY-MM-DD?HH24:MI:SS'),
 79???????????????????????????'YYYY-MM-DD?HH24:MI:SS')?alert_date,
 80???????????????????a.MESSAGE_TEXT,
 81???????????????????a.MESSAGE_TYPE,
 82???????????????????a.MESSAGE_LEVEL,
 83???????????????????a.MESSAGE_ID,
 84???????????????????a.MESSAGE_GROUP,
 85???????????????????a.DETAILED_LOCATION,
 86???????????????????a.PROBLEM_KEY,
 87???????????????????a.RECORD_ID,
 88???????????????????a.ORGANIZATION_ID,
 89???????????????????a.COMPONENT_ID,
 90???????????????????a.HOST_ID,
 91???????????????????a.HOST_ADDRESS,
 92???????????????????a.CLIENT_ID,
 93???????????????????a.MODULE_ID,
 94???????????????????a.PROCESS_ID
 95????????????FROM???v$diag_alert_ext?a
 96????????????WHERE??a.COMPONENT_ID?=?'rdbms'
 97????????????AND????a.FILENAME?LIKE
 98???????????????????'/u01/app/oracle/diag/rdbms/orclasm/orclasm/alert/log.xml%'
 99????????????AND????a.RECORD_ID?>?v_max_recordid
100????????????AND????a.ORIGINATING_TIMESTAMP?>=?v_max_date;
101
102????????COMMIT;
103
104END?p_alert_log_lhr;
105/
106
107--定时任务：
108BEGIN
109
110??DBMS_SCHEDULER.CREATE_JOB(JOB_NAME???=>?'job_p_alert_log_lhr',
111????????????????????????????JOB_TYPE???=>?'STORED_PROCEDURE',
112????????????????????????????JOB_ACTION?=>?'p_alert_log_lhr',
113????????????????????????????ENABLED????=>?TRUE,
114????????????????????????????START_DATE?=>?SYSDATE,
115????????????????????????????comments?=>?'记录历史告警日志，每2个小时执行一次');
116
117END;
118/

? ? ?3．利用SHELL脚本定时将告警日志进行备份 ? ? ?
利用Linux下的crontab设置每周日早上凌晨归档一次告警日志：

 1[oracle@rhel6lhr?~]$?crontab?-l
 22?12?*?*?1?/home/oracle/lhr/alert_log_archive.sh
 3[oracle@rhel6lhr?~]$?more?/home/oracle/lhr/alert_log_archive.sh
 4#*************************************************************************
 5#??FileName?????:alert_log_archive.sh
 6#*************************************************************************
 7#??Author???????:lhr
 8#??CreateDate???:2014-07-16
 9#??blogs　　????:http://blog.itpub.net/26736162
10#??Description??:this?script?is?made?the?alert?log?archived?every?day
11#??crontab??????:?2?0?*?*?0?/home/oracle/lhr/alert_log_archive.sh???---sunday?exec
12#*************************************************************************
13#!?/bin/bash
14#?these?solved?the?oracle?variable?problem.
15export?ORACLE_SID=orclasm
16export?ORACLE_BASE=/u01/app/oracle
17mydate=`date?+'%Y%m%d%H%M%S'`
18alert_log_path="$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/"
19alert_log_file="alert_$ORACLE_SID.log"
20alert_arc_file="alert_$ORACLE_SID.log""."${mydate}
21cd?${alert_log_path};
22if?[?!?-e?"${alert_log_file}"?];?then
23??echo?"the?alert?log?didn't?exits,?please?check?file?path?is?correct!";
24exit;
25fi
26if?[?-e?${alert_arc_file}?];then
27??echo?"the?alert?log?file?have?been?archived!"
28else
29mv?${alert_log_file}??${alert_arc_file}
30cat?/dev/null?>?${alert_log_file}
31fi
32