使用外部表格管理Oracle 警示日誌

--================================================

-- 使用外部表格管理Oracle 警示日誌(ALAERT_$SID.LOG)

--================================================

Oracle 警示日誌時DBA維護資料庫經常需要關注的一部分內容。然而警示日誌以文字檔，按時間的先後順序不斷累積的形式來儲存，久而

久之，勢必造成警示日誌的過大，難於維護和尋找相關的資訊。使用外表表方式來管理警示日誌將大大簡化維護工作量，也更直關的擷取所需的

資訊。

有關外部表格的使用請參考：Oracle 外部表格

一、警示日誌的內容

訊息和錯誤的類型(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)

二、建立外部表格

1.查看後台日誌路徑

sys@ORCL> show parameter %b%_dump_dest   --此可以省略，在後面直接用指令碼cre_ext_tb.sql 實現

NAME                                 TYPE        VALUE

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

background_dump_dest                 string      /u01/oracle/admin/orcl/bdump

2.建立使用者並賦予特定的許可權，並建立資料庫目錄  

sys@ORCL> create user usr1 identified by usr1    --建立帳戶usr1

 2  temporary tablespace temp

 3  default tablespace users

 4  quota unlimited on users;

sys@ORCL> grant connect,resource to usr1;       --為帳戶usr1授予connect,resource角色

sys@ORCL> grant create any directory to usr1;   --為帳戶usr1授予建立目錄的許可權

sys@ORCL> conn usr1/usr1                        --使用usr1串連資料庫

3.下面使用指令碼來完成對警示日誌的跟蹤及管理

指令碼描述

cre_ext_tb.sql

主要是建立了一個alert_log表用於存放警示日誌的重要訊息，一個外部表格alert_log_disk使得查看警示日誌可以直接在本機資料

庫中完成。

update_alert_log.sql

用於從外部表格將重要訊息經過過濾並且將沒有存放到alert_log表中的最新資訊更新到alert_log表。

4.使用下面的指令碼來建立alert_log表及alert_log_disk外部表格

usr1@ORCL> get /u01/bk/scripts/cre_ext_tb.sql  --查看建表的代碼

 1  define alert_length="500"

 2  drop table alert_log;

 3  create table alert_log (                  --建立表alert_log用於存放警示日誌的重要訊息

 4    alert_date date,

 5    alert_text varchar2(&&alert_length)

 6  )

 7  storage (initial 512k next 512K pctincrease 0);

 8  create index alert_log_idx on alert_log(alert_date)    --為表alert_log建立索引

 9  storage (initial 512k next 512K pctincrease 0);

10  column db    new_value _DB    noprint;                

11  column bdump new_value _bdump noprint;

12  select instance_name db from v$instance;               --獲得執行個體名以及警示日誌路徑

13  select value bdump from v$parameter

14   where name ='background_dump_dest';

15  drop   directory BDUMP;

16  create directory BDUMP as '&&_bdump';

17  drop table alert_log_disk;

18  create table alert_log_disk ( text varchar2(&&alert_length) )    --建立外部表格

19  organization external (

20    type oracle_loader

21    default directory BDUMP

22        access parameters (

23            records delimited by newline nologfile nobadfile

24            fields terminated by "&" ltrim

25        )

26    location('alert_&&_DB..log')

27  )

28* reject limit unlimited;

usr1@ORCL> start /u01/bk/scripts/cre_ext_tb.sql    --執行建表的代碼

5.使用下面的指令碼填充alert_log表            

usr1@ORCL> get /u01/bk/scripts/update_alert_log.sql  --指令碼update_alert_log.sql用於將外部表格的重要訊息填充到alert_log

 1  set serveroutput on

 2  declare

 3    isdate         number := 0;

 4    start_updating number := 0;

 5    rows_inserted  number := 0;

 6    alert_date     date;

 7    max_date       date;

 8    alert_text     alert_log_disk.text%type;

 9  begin

10    /* find a starting date */

11    select max(alert_date) into max_date from alert_log;

12    if (max_date is null) then

13      max_date := to_date('01-jan-1980', 'dd-mon-yyyy');

14    end if;

15    for r in (              

16      select substr(text,1,180) text from alert_log_disk     --使用for迴圈從警示日誌過濾資訊

17       where text not like '%offlining%'

18         and text not like 'ARC_:%'

19         and text not like '%LOG_ARCHIVE_DEST_1%'

20         and text not like '%Thread 1 advanced to log sequence%'

21         and text not like '%Current log#%seq#%mem#%'

22         and text not like '%Undo Segment%lined%'

23         and text not like '%alter tablespace%back%'

24         and text not like '%Log actively being archived by another process%'

25         and text not like '%alter database backup controlfile to trace%'

26         and text not like '%Created Undo Segment%'

27         and text not like '%started with pid%'

28         and text not like '%ORA-12012%'

29         and text not like '%ORA-06512%'

30         and text not like '%ORA-000060:%'

31         and text not like '%coalesce%'

32         and text not like '%Beginning log switch checkpoint up to RBA%'

33         and text not like '%Completed checkpoint up to RBA%'

34         and text not like '%specifies an obsolete parameter%'

35         and text not like '%BEGIN BACKUP%'

36         and text not like '%END BACKUP%'

37    )

38    loop

39      isdate     := 0;

40      alert_text := null;

41      select count(*) into isdate                          --設定標誌位，用於判斷改行是否為時間資料

42        from dual

43       where substr(r.text, 21) in ('2009','2010','2011','2012','2013')

44         and r.text not like '%cycle_run_year%';

45      if (isdate = 1) then                                 --將時間資料格式化

46        select to_date(substr(r.text, 5),'Mon dd hh24:mi:ss rrrr')

47          into alert_date

48          from dual;

49        if (alert_date > max_date) then                   --設定標誌位用於判斷是否需要update

50          start_updating := 1;

51        end if;

52      else

53        alert_text := r.text;

54      end if;

55      if (alert_text is not null) and (start_updating = 1) then   --start_updating標誌位與alert_text為真，插入記錄

56        insert into alert_log values (alert_date, substr(alert_text, 1, 180));

57        rows_inserted := rows_inserted + 1;

58        commit;

59      end if;

60    end loop;

61    sys.dbms_output.put_line('Inserting after date '||to_char(max_date, 'MM/DD/RR HH24:MI:SS'));

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

63    commit;

64* end;

65

usr1@ORCL> start /u01/bk/scripts/update_alert_log.sql

Inserting after date 01/01/80 00:00:00

Rows Inserted: 632

PL/SQL procedure successfully completed.

基於上述方法，可以定期將警示日誌更新到本機資料庫，然後清空警示記錄檔  

三、查看警示日誌的內容

1.修改會話日期的顯示格式

usr1@ORCL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

2.查看警示日誌的資訊

usr1@ORCL> select * from alert_log where rownum < 5;                

ALERT_DATE          ALERT_TEXT

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

2011-02-14 21:36:11 SYS auditing is disabled

2011-02-14 21:36:11 ksdpec: called for event 13740 prior to event group initialization

2011-02-14 21:36:11 Starting up ORACLE RDBMS Version: 10.2.0.1.0.

2011-02-14 21:36:11 System parameters with non-default values:

3.查看警示日誌最新的5條資訊

usr1@ORCL> select * from alert_log where rownum < 5 order by alert_date desc;

ALERT_DATE          ALERT_TEXT

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

2011-02-14 21:36:11 SYS auditing is disabled

2011-02-14 21:36:11 ksdpec: called for event 13740 prior to event group initialization

2011-02-14 21:36:11 Starting up ORACLE RDBMS Version: 10.2.0.1.0.

2011-02-14 21:36:11 System parameters with non-default values:

4.查看警示日誌ORA錯誤資訊

usr1@ORCL> select * from alert_log where alert_text like 'ORA-%';      

ALERT_DATE          ALERT_TEXT

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

2011-02-14 21:36:13 ORA-00202: control file: '/u01/oracle/oradata/orcl/control03.ctl'

2011-02-14 21:36:13 ORA-27037: unable to obtain file status

2011-02-14 21:36:13 ORA-205 signalled during: ALTER DATABASE   MOUNT...

2011-02-14 21:36:23 ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...

2011-02-14 21:36:27 ORA-00202: control file: '/u01/oracle/oradata/orcl/control03.ctl'

oracle視頻教程請關注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html