--================================================
-- 使用外部表管理 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 閃回特性(FLASHBACK DATABASE)
Oracle 閃回特性(FLASHBACK DROP & RECYCLEBIN)
Oracle 閃回特性(Flashback Query 、Flashback Table)
Oracle 閃回特性(Flashback Version 、Flashback Transaction)
?
有關基于用戶管理的備份和備份恢復的概念請參考：
??????? Oracle 冷備份
??????? Oracle 熱備份
??????? Oracle 備份恢復概念
??????? Oracle 實例恢復
??????? Oracle 基于用戶管理恢復的處理 ( 詳細描述了介質恢復及其處理 )
???????
??? 有關 RMAN 的恢復與管理請參考：
??????? RMAN 概述及其體系結構
??????? RMAN 配置、監控 與管理
??????? RMAN 備份詳解
??????? RMAN 還原與恢復
???????
??? 有關 Oracle 體系結構請參考：
??????? Oracle 實例和Oracle 數據庫(Oracle 體系結構)
??????? Oracle 表空間與數據文件
??????? Oracle 密碼文件
??????? Oracle 參數文件
??????? Oracle 聯機重做日志文件(ONLINE LOG FILE)
??????? Oracle 控制文件(CONTROLFILE)
??????? Oracle 歸檔日志
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
