Oracle的告警日誌之v$diag_alert_ext檢視

Oracle的告警日誌之v$diag_alert_ext檢視

最近由於自己寫的一個job老是報錯，找不出來原因，資料庫linux的terminal由於安全原因不讓連線，因此告警日誌就沒有辦法閱讀，沒有辦法就想想其它的辦法吧，比如採用外部表的形式來閱讀告警日誌就是一個不錯的辦法。

告警日誌的重要性就不多說了。。。。

實驗環境

本次所有的實驗環境是Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production,10g的話應該很多是類似的，就不去研究那個了。。。。。

C:\Users\Administrator>sqlplus lhr/lhr@orclasm

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 7月 17 14:34:47 2014

連線到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL>

Automatic Diagnostic Repository (ADR)

一個存放資料庫診斷日誌、跟蹤檔案的目錄，稱作ADR base，對應初始化引數DIAGNOSTIC_DEST，如果設定了ORACLE_BASE環境變數，DIAGNOSTIC_DEST等於 ORACLE_BASE，如果沒有設定ORACLE_BASE，則等與ORACLE_HOME/log。SQL> show parameter DIAGNOSTICNAME TYPE VALUE------------------------------------ ----------- ------------------------------diagnostic_dest string /oracle/oracle

關於ADR這裡不多說了，網上一百度一大堆。。。。。。。

告警檔案的路徑

首先，告警日誌檔案有2種型別，一個是純文字格式的，另外一種是xml檔案格式的，不管哪個版本都可以用這個引數得到純文字格式告警日誌的路徑：

SQL> show parameter background_dump_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

background_dump_dest string /u01/app/oracle/diag/rdbms/orc

lasm/orclasm/trace

SQL>

文字格式的日誌還可以通過這個檢視來查詢：

select value from v$diag_info where name='Diag Trace';

還有xml格式的告警日誌檔案在：

SQL> select value from v$diag_info where name='Diag Alert';

VALUE

--------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/orclasm/orclasm/alert

SQL>

/u01/app/oracle/diag/rdbms/orclasm/orclasm/alert/log.xml

告警日誌的內容

訊息和錯誤的型別(Types of messages and errors)
ORA-600內部錯誤(ORA-600 internal errors that need immediate support from Oracle's customer support )'
ORA-1578塊損壞錯誤(ORA-1578 block corruption errors that require recovery)
ORA-12012(作業佇列錯誤(ORA-12012 job queue errors)
例項啟動關閉，恢復等資訊(STARTUP & SHUTDOWN, and RECOVER statement execution messages)
特定的DDL命令(Certain CREATE, ALTER, & DROP statements )
影響表空間，資料檔案及回滾段的命令(Statements that effect TABLESPACES, DATAFILES, and ROLLBACK SEGMENTS )
可持續的命令被掛起(When a resumable statement is suspended )
LGWR不能寫入到日誌檔案(When log writer (LGWR) cannot write to a member of a group )
歸檔程式啟動資訊(When new Archiver Process (ARCn) is started )
排程程式的相關資訊(Dispatcher information)
動態引數的修改資訊(The occurrence of someone changing a dynamic parameter)

使用外部表檢視oracle報警日誌

關於外部表的使用網上一搜又是一大堆，這裡不列舉起語法了，直接到使用層次吧。。。。。

先來個最簡單的使用方法

SQL> drop directory DIR_ALERT;

目錄已刪除。

SQL> create directory DIR_ALERT as '/u01/app/oracle/diag/rdbms/orclasm/orclasm/trace';

目錄已建立。

SQL>

SQL> drop table alert_log;

表已刪除。

SQL> create table alert_log(

2 　　text varchar2(500)

3 　　)organization external

4 　　(type oracle_loader

5 　　default directory DIR_ALERT

6 　　access parameters

7 　　(records delimited by newline

8 　　)location('alert_orclasm.log')

9 　　) reject limit unlimited;

表已建立。

SQL>

檢視ora錯誤：

select * from alert_log where text like 'ORA-%';

-------檢視最新的10條告警日誌記錄

select * from (

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

-------檢視最新的10條ora告警日誌記錄

SELECT *

FROM (SELECT rownum rn,

a.text

FROM alert_log a

WHERE a.text LIKE 'ORA-%') b

WHERE b.rn >=

(SELECT COUNT(1) - 10 FROM alert_log a WHERE a.text LIKE 'ORA-%');

以上程式碼細心的網友可能會發現一個缺點，我不能檢視歷史某一時間段內的告警日誌，或者說檢視歷史某一時間段內的告警日誌很困難。。。。別急，，，，哥還有辦法的。。。。。以下給出另一段程式碼，這段程式碼可以把歷史告警日誌做了格式化處理，採用了分割槽表的形式，我不執行了，直接貼程式碼了：

再來個稍微複雜點的

------建立表xb_alert_log_lhr用於存放告警日誌的歷史資訊

-- drop table xb_alert_log_lhr;

create table xb_alert_log_lhr (

id number primary key,

alert_date date,

alert_text varchar2(500)

) nologging

partition by range(alert_date)

interval(numtoyminterval(1,'month'))

(partition P201406 VALUES LESS THAN(TO_DATE('201407','YYYYMM')));

create sequence s_xb_alert_log_lhr ;

create index alert_log_idx on xb_alert_log_lhr(alert_date) local nologging ; --為表alert_log建立索引

column db new_value _DB noprint;

column bdump new_value _bdump noprint;

select instance_name db from v$instance; --獲得例項名以及告警日誌路徑

select value bdump from v$parameter

where name ='background_dump_dest';

-- drop directory DIR_ALERT_LHR;

create directory DIR_ALERT_LHR as '/u01/app/oracle/diag/rdbms/orclasm/orclasm/trace';

-- drop table xb_alert_log_disk_lhr;

create table xb_alert_log_disk_lhr ( text varchar2(500) ) --建立外部表

organization external (

type oracle_loader

default directory DIR_ALERT_LHR

access parameters (

records delimited by newline nologfile nobadfile

)

location('alert_orclasm.log')

) reject limit unlimited;

CREATE OR REPLACE PROCEDURE pro_alert_log_lhr AS

isdate NUMBER := 0;

start_updating NUMBER := 0;

v_rows_inserted NUMBER := 0;

v_alert_date DATE;

v_max_date DATE;

v_alert_text xb_alert_log_disk_lhr.text%TYPE;

BEGIN

EXECUTE IMMEDIATE 'alter session set NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';

EXECUTE IMMEDIATE 'alter session set nls_date_language=''american''';

/* find a starting date */

SELECT MAX(v_alert_date) INTO v_max_date FROM xb_alert_log_lhr;

IF (v_max_date IS NULL) THEN

v_max_date := to_date('01-01-1980', 'dd-mm-yyyy');

END IF;

--使用for迴圈從告警日誌過濾資訊

FOR cur IN (SELECT *

FROM xb_alert_log_disk_lhr

) LOOP

isdate := 0;

v_alert_text := NULL;

SELECT COUNT(*)

INTO isdate --設定標誌位，用於判斷該行是否為時間資料

FROM dual

WHERE substr(cur.text, 21) IN

('2009', '2010', '2011', '2012', '2013', '2014', '2015') ---Sat Jun 14 23:22:14 2014

AND length(cur.text) = 24;

IF (isdate = 1) THEN

--將時間資料格式化

SELECT to_date(substr(cur.text, 5), 'Mon dd hh24:mi:ss rrrr')

INTO v_alert_date

FROM dual;

IF (v_alert_date > v_max_date) THEN

--設定標誌位用於判斷是否需要update

start_updating := 1;

END IF;

ELSE

v_alert_text := cur.text;

END IF;

IF (v_alert_text IS NOT NULL) AND (start_updating = 1) THEN

--start_updating標誌位與v_alert_text為真，插入記錄

INSERT INTO xb_alert_log_lhr nologging

(id, alert_date, alert_text)

VALUES

(s_xb_alert_log_lhr.nextval, v_alert_date, v_alert_text);

v_rows_inserted := v_rows_inserted + 1;

COMMIT;

END IF;

END LOOP;

sys.dbms_output.put_line('Inserting after date ' ||

to_char(v_max_date, 'YYYY-MM-DD HH24:MI:SS'));

sys.dbms_output.put_line('Rows Inserted: ' || v_rows_inserted);

COMMIT;

END pro_alert_log_lhr;

/

執行存過：

begin

pro_alert_log_lhr;

end;

執行結束後大家可以檢視，格式化之後的表：

select * from xb_alert_log_disk_lhr ;

select * from xb_alert_log_lhr partition(SYS_P381) a where a.id>=834180 order by a.id;

select * from xb_alert_log_lhr partition(SYS_P381) a where a.alert_text like '%ORA%' ;

雖然可以採用了分割槽表儲存了歷史告警日誌，也有索引可用，但是存過有個缺點，每次都會對外部表全部掃描，這個有點慢。。。。。

自己用的(本篇的重點)

主要採用v$diag_alert_ext 檢視中的內容，因為這個檢視中的內容很全，記錄到歷史表中，利於我們分析。

-------------------------------------------------歷史告警日誌記錄

---drop table XB_ALERTLOG_ALL_LHR ;

create table XB_ALERTLOG_ALL_LHR

(

ID NUMBER primary key,

alert_date date,

message_text VARCHAR2(3000),

message_type NUMBER,

message_level NUMBER,

message_id VARCHAR2(67),

message_group VARCHAR2(67),

detailed_location VARCHAR2(163),

problem_key VARCHAR2(67),

record_id NUMBER,

organization_id VARCHAR2(67),

component_id VARCHAR2(67),

host_id VARCHAR2(67),

host_address VARCHAR2(49),

client_id VARCHAR2(67),

module_id VARCHAR2(67),

process_id VARCHAR2(35)

) nologging

partition by range(alert_date)

interval(numtoyminterval(1,'month'))

(partition P201406 VALUES LESS THAN(TO_DATE('201407','YYYYMM')));

--drop SEQUENCE S_XB_SQL_MONITOR_LHR;

CREATE SEQUENCE S_XB_ALERTLOG_ALL_LHR START WITH 1 INCREMENT BY 1 cache 20;

create index ind_ALERTLOG_ALL_In_Date on XB_ALERTLOG_ALL_LHR(ALERT_DATE,Record_Id) local nologging;

---------記錄歷史告警日誌

CREATE PROCEDURE p_alert_log_lhr AS

v_max_recordid NUMBER;

v_max_date DATE;

BEGIN

SELECT MAX(a.record_id),

MAX(a.alert_date)

INTO v_max_recordid,

v_max_date

FROM XB_ALERTLOG_ALL_LHR a

WHERE a.alert_date >= SYSDATE - 360 / 1440 --3h'之前

AND a.alert_date <= SYSDATE;

INSERT INTO XB_ALERTLOG_ALL_LHR nologging

(ID,

ALERT_DATE,

MESSAGE_TEXT,

MESSAGE_TYPE,

MESSAGE_LEVEL,

MESSAGE_ID,

MESSAGE_GROUP,

DETAILED_LOCATION,

PROBLEM_KEY,

RECORD_ID,

ORGANIZATION_ID,

COMPONENT_ID,

HOST_ID,

HOST_ADDRESS,

CLIENT_ID,

MODULE_ID,

PROCESS_ID)

SELECT s_XB_ALERTLOG_ALL_LHR.Nextval,

to_date(to_char(a.ORIGINATING_TIMESTAMP,

'YYYY-MM-DD HH24:MI:SS'),

'YYYY-MM-DD HH24:MI:SS') alert_date,

a.MESSAGE_TEXT,

a.MESSAGE_TYPE,

a.MESSAGE_LEVEL,

a.MESSAGE_ID,

a.MESSAGE_GROUP,

a.DETAILED_LOCATION,

a.PROBLEM_KEY,

a.RECORD_ID,

a.ORGANIZATION_ID,

a.COMPONENT_ID,

a.HOST_ID,

a.HOST_ADDRESS,

a.CLIENT_ID,

a.MODULE_ID,

a.PROCESS_ID

FROM v$diag_alert_ext a

WHERE a.COMPONENT_ID = 'rdbms'

AND a.FILENAME LIKE

'/u01/app/oracle/diag/rdbms/orclasm/orclasm/alert/log.xml%'

AND a.RECORD_ID > v_max_recordid

AND a.ORIGINATING_TIMESTAMP >= v_max_date;

COMMIT;

END p_alert_log_lhr;

/

定時任務：

BEGIN

DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'job_p_alert_log_lhr',

JOB_TYPE => 'STORED_PROCEDURE',

JOB_ACTION => 'p_alert_log_lhr',

ENABLED => TRUE,

START_DATE => SYSDATE,

comments => '記錄歷史告警日誌，每2個小時執行一次');

END;

/

歸檔告警檔案

歸檔告警日誌檔案，每週日早上凌晨歸檔一次，，，（linux下的crontab如何使用？？？？？百度吧，哥這裡不列出了。。。。。。）：

#*************************************************************************

# FileName :alert_log_archive.sh

#*************************************************************************

# Author :lhr

# CreateDate :2014-07-16

# blogs　　 :http://blog.itpub.net/26736162

# Description :this script is made the alert log archived every day

# crontab : 2 0 * * 0 /home/oracle/lhr/alert_log_archive.sh ---sunday exec

#*************************************************************************

#! /bin/bash

# these solved the oracle variable problem.

export ORACLE_SID=orclasm

export ORACLE_BASE=/u01/app/oracle

mydate=`date +'%Y%m%d%H%M%S'`

alert_log_path="$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/"

alert_log_file="alert_$ORACLE_SID.log"

alert_arc_file="alert_$ORACLE_SID.log""."${mydate}

cd ${alert_log_path};

if [ ! -e "${alert_log_file}" ]; then

echo "the alert log didn't exits, please check file path is correct!";

exit;

fi

if [ -e ${alert_arc_file} ];then

echo "the alert log file have been archived!"

else

mv ${alert_log_file} ${alert_arc_file}

cat /dev/null > ${alert_log_file}

fi

與告警日誌有關的檢視

select * from dba_alert_history a order by a.sequence_id desc ;

select * from dba_alert_arguments;

select * from dba_outstanding_alerts;

列出3個OCP考題

1、Identify the two situations in which you use the alert log file in your database to check the details. (Choose two.)

選項

A.Running aquery on a table returns"ORA-600: Internal Error ."

B.Inserting a value in a table returns"ORA-01722: invalid number ."

C.Creating a table returns"ORA-00955: name is already used by an existing object."

D.Inserting a value in a table returns "ORA-00001: unique constraint (SYS.PK_TECHP)

violated."

E.Inserting a row in a table returns"ORA-00060:deadlock detected while waiting for resource."

Correct Answers: A E

2、Identify the three predefined server-generated alerts. (Choose three.)

確定三個預定義的伺服器生成的警報。

A. Drop User

B. Tablespace Space Usage表空間空間使用率

C. Resumable Session Suspended可恢復會話暫停

D. Recovery Area Low On Free Space自由空間上的恢復區低

E. SYSTEM Tablespace Size Increment

Answer: B,C,D

3、Which two statements are true about alerts? (Choose two.) 選項

A.Clearing an alert sends the alert to the alert history .

B.Response actions cannot be specified with server-generated alerts.

C.The nonthreshold alerts appear in the DBA_OUTSTANDING_ALERTS view .

D.Server-generated alerts notify the problems that cannot be resolved automatically and require administrators to be notified.

Correct Answers: A D

列出官網的一些內容

Alerts help you monitor your database. Most alerts notify you of when particular metric thresholds are exceeded. For each alert, you can set critical and warning threshold values. These threshold values are meant to be boundary values that when exceeded, indicate that the system is in an undesirable state. For example, when a tablespace becomes 97 percent full, this can be considered undesirable, and Oracle Database generates a critical alert.

Other alerts correspond to database events such as Snapshot Too Old or Resumable Session suspended. These types of alerts indicate that the event has occurred.

In addition to notification, you can set alerts to perform some action such as running a script. For instance, scripts that shrink tablespace objects can be useful for a Tablespace Usage warning alert.

By default, Oracle Database issues several alerts, including the following:

Archive Area Used (warning at 80 percent full)
Broken Job Count and Failed Job Count (warning when goes above 0)
Current Open Cursors Count (warning when goes above 1200)
Dump Area Used (warning at 95 percent full)
Session Limit Usage (warning at 90 percent, critical at 97 percent)
Tablespace Space Used (warning at 85 percent full, critical at 97 percent full)
You can modify these alerts and others by setting their metrics

The alert log is an XML file that is a chronological log of database messages and errors. It is stored in the ADR and includes messages about the following:

Critical errors (incidents)
Administrative operations, such as starting up or shutting down the database, recovering the database, creating or dropping a tablespace, and others.
Errors during automatic refresh of a materialized view
Other database events

You can view the alert log in text format (with the XML tags stripped) with Enterprise Manager and with the ADRCI utility. There is also a text-formatted version of the alert log stored in the ADR for backward compatibility. However, Oracle recommends that any parsing of the alert log contents be done with the XML-formatted version, because the text format is unstructured and may change from release to release.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25960404/viewspace-1222492/，如需轉載，請註明出處，否則將追究法律責任。

Oracle的告警日誌之v$diag_alert_ext檢視

實驗環境

ADR目錄

告警檔案的路徑

告警日誌的內容

使用外部表檢視oracle報警日誌

先來個最簡單的使用方法

再來個稍微複雜點的

自己用的(本篇的重點)

歸檔告警檔案

與告警日誌有關的檢視

列出3個OCP考題

列出官網的一些內容

相關文章