--================================================
-- 使用外部表管理 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ì)您有幫助就好】元
