--================================================
-- 使用外部表管理 Oracle 告警日志 (ALAERT_$SID.LOG)
--================================================
?
??? Oracle 告警日志時(shí) DBA 維護(hù)數(shù)據(jù)庫經(jīng)常需要關(guān)注的一部分內(nèi)容。然而告警日志以文本文件，按時(shí)間的先后順序不斷累積的形式來存儲(chǔ)，久而
久之，勢(shì)必造成告警日志的過大，難于維護(hù)和查找相關(guān)的信息。使用外表表方式來管理告警日志將大大簡化維護(hù)工作量，也更直關(guān)的獲取所需的
信息。
??? 有關(guān)外部表的使用請(qǐng)參考： Oracle 外 部 表
???????
一、告警日志的內(nèi)容
???
??? 消息和錯(cuò)誤的類型 (Types of messages and errors)
??? ORA-600 內(nèi)部錯(cuò)誤 (ORA-600 internal errors that need immediate support from Oracle's customer support )'
??? ORA-1578 塊損壞錯(cuò)誤 (ORA-1578 block corruption errors that require recovery)
??? ORA-12012( 作業(yè)隊(duì)列錯(cuò)誤 (ORA-12012 job queue errors)
??? 實(shí)例啟動(dòng)關(guān)閉，恢復(fù)等信息 (STARTUP & SHUTDOWN, and RECOVER statement execution messages)
??? 特定的 DDL 命令 (Certain CREATE, ALTER, & DROP statements )
??? 影響表空間，數(shù)據(jù)文件及回滾段的命令 (Statements that effect TABLESPACES, DATAFILES, and ROLLBACK SEGMENTS )
??? 可持續(xù)的命令被掛起 (When a resumable statement is suspended )
??? LGWR 不能寫入到日志文件 (When log writer (LGWR) cannot write to a member of a group )
??? 歸檔進(jìn)程啟動(dòng)信息 (When new Archiver Process (ARCn) is started )
??? 調(diào)度進(jìn)程的相關(guān)信息 (Dispatcher information)
??? 動(dòng)態(tài)參數(shù)的修改信息 (The occurrence of someone changing a dynamic parameter)
?
二、建立外部表
??? 1. 查看后臺(tái)日志路徑
??????? sys@ORCL > show parameter % b % _dump_dest ?? -- 此可以省略，在后面直接用腳本 cre_ext_tb.sql 實(shí)現(xiàn)
?
??????? NAME ???????????????????????????????? TYPE ??????? VALUE
??????? ------------------------------------ ----------- ------------------------------
??????? background_dump_dest ???????????????? string ????? / u01 / oracle / admin / orcl / bdump
?
??? 2. 創(chuàng)建用戶并賦予特定的權(quán)限，并創(chuàng)建數(shù)據(jù)庫目錄 ???
??????? sys@ORCL > create user usr1 identified by usr1 ??? -- 創(chuàng)建帳戶 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 授予創(chuàng)建目錄的權(quán)限
?
??????? sys@ORCL > conn usr1 / usr1 ??????????????????????? -- 使用 usr1 連接數(shù)據(jù)庫
?
??? 3. 下面使用腳本來完成對(duì)告警日志的跟蹤及管理
??????? 腳本描述
??????? cre_ext_tb . sql
??????????? 主要是創(chuàng)建了一個(gè) alert_log 表用于存放告警日志的重要信息，一個(gè)外部表 alert_log_disk 使得查看告警日志可以直接在本地?cái)?shù)據(jù)
??????????? 庫中完成。
??????? update_alert_log . sql
??????????? 用于從外部表將重要信息經(jīng)過過濾并且將沒有存放到 alert_log 表中的最新信息更新到 alert_log 表。
???????????
??? 4. 使用下面的腳本來創(chuàng)建 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 ( ????????????????? -- 創(chuàng)建表 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 創(chuàng)建索引
??????? ? 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 ; ?????????????? -- 獲得實(shí)例名以及告警日志路徑
??????? ? 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 ) ) ??? -- 創(chuàng)建外部表
??????? ? 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 ??? -- 執(zhí)行建表的代碼
???
??? 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 循環(huán)從告警日志過濾信息
??????? ? 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 ????????????????????????? -- 設(shè)定標(biāo)志位，用于判斷改行是否為時(shí)間數(shù)據(jù)
??????? ? 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 ???????????????????????????????? -- 將時(shí)間數(shù)據(jù)格式化
??????? ? 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 ?????????????????? -- 設(shè)定標(biāo)志位用于判斷是否需要 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 標(biāo)志位與 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 .
???????
??????? 基于上述方法，可以定期將告警日志更新到本地?cái)?shù)據(jù)庫，然后清空告警日志文件 ?
???????
三、查看告警日志的內(nèi)容
???????
??????? 1. 修改會(huì)話日期的顯示格式
??????????? 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 錯(cuò)誤信息
??????????? 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'
?
四、更多參考
有關(guān)閃回特性請(qǐng)參考
??????? Oracle 閃回特性(FLASHBACK DATABASE)
Oracle 閃回特性(FLASHBACK DROP & RECYCLEBIN)
Oracle 閃回特性(Flashback Query 、Flashback Table)
Oracle 閃回特性(Flashback Version 、Flashback Transaction)
?
有關(guān)基于用戶管理的備份和備份恢復(fù)的概念請(qǐng)參考：
??????? Oracle 冷備份
??????? Oracle 熱備份
??????? Oracle 備份恢復(fù)概念
??????? Oracle 實(shí)例恢復(fù)
??????? Oracle 基于用戶管理恢復(fù)的處理 ( 詳細(xì)描述了介質(zhì)恢復(fù)及其處理 )
???????
??? 有關(guān) RMAN 的恢復(fù)與管理請(qǐng)參考：
??????? RMAN 概述及其體系結(jié)構(gòu)
??????? RMAN 配置、監(jiān)控 與管理
??????? RMAN 備份詳解
??????? RMAN 還原與恢復(fù)
???????
??? 有關(guān) Oracle 體系結(jié)構(gòu)請(qǐng)參考：
??????? Oracle 實(shí)例和Oracle 數(shù)據(jù)庫(Oracle 體系結(jié)構(gòu))
??????? Oracle 表空間與數(shù)據(jù)文件
??????? Oracle 密碼文件
??????? Oracle 參數(shù)文件
Oracle 數(shù)據(jù)庫實(shí)例啟動(dòng)關(guān)閉過程
??????? Oracle 聯(lián)機(jī)重做日志文件(ONLINE LOG FILE)
??????? Oracle 控制文件(CONTROLFILE)
??????? Oracle 歸檔日志
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
