oracle外部表記錄alert日誌&&資料庫執行報告

1、建立目錄【注意目錄是alert_sid.log所在的目錄,使用show parameter background_dump_dest檢視】

> show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      D:\oracle\ora11g\diag\rdbms\ora11g\ora11g\trace

> create or replace directory log_dir as 'D:\oracle\ora11g\diag\rdbms\ora11g\\ora11g\trace
';

Directory created.                                --建立目錄

 

2、建立外部表

CREATE TABLE alert_log
(text_line varchar2(255))  ----內部表的結構定義
ORGANIZATION EXTERNAL   -- 內部表結構和OS檔案的關聯載入引數定義
(TYPE ORACLE_LOADER   -- 使用oracle載入器
DEFAULT DIRECTORY log_dir  -- 預設的資料庫目錄
ACCESS PARAMETERS  --訪問引數
(records delimited by newline fields  -- 以換行符為單位定位載入行的範圍
REJECT ROWS WITH ALL NULL FIELDS )  -- 不載入整行都是null的記錄
LOCATION
('alert_ora11g.log'))   -- 載入的目錄下檔名
REJECT LIMIT unlimited  -- 限制長度

 

3、此時可以透過表alter_log來檢視alert告警相關資訊了

如：

> select * from alert_log where rownum<=10;

TEXT_LINE
--------------------------------------------------------------------------------------

Wed Feb 08 10:20:47 2012
Adjusting the default value of parameter parallel_max_servers
from 160 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as D:\oracle\ora11g\product\RDBMS
Autotune of undo retention is turned on.

10 rows selected.

 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

利用上面的外部表來生成一個oracle的啟動、關閉相關的報告，使用下面的sql:

SELECT TO_CHAR(LAST_TIME, 'dd-mon-yyyy hh24:mi') SHUTDOWN,
       TO_CHAR(START_TIME, 'dd-mon-yyyy hh24:mi') STARTUP,
       ROUND((START_TIME - LAST_TIME) * 24 * 60, 2) MINS_DOWN,
       ROUND((LAST_TIME - LAG(START_TIME) OVER(ORDER BY R)), 2) DAYS_UP,
       CASE
         WHEN (LEAD(R) OVER(ORDER BY R) IS NULL) THEN
          ROUND((SYSDATE - START_TIME), 2)
       END DAYS_STILL_UP
  FROM (SELECT R,
               TO_DATE(LAST_TIME, 'Dy-Mon-DD HH24:MI:SS YYYY') LAST_TIME,
               TO_DATE(START_TIME, 'Dy-Mon-DD HH24:MI:SS YYYY') START_TIME
          FROM (SELECT R,
                       TEXT_LINE,
                       LAG(TEXT_LINE, 1) OVER(ORDER BY R) START_TIME,
                       LAG(TEXT_LINE, 2) OVER(ORDER BY R) LAST_TIME
                  FROM (SELECT ROWNUM R, TEXT_LINE
                          FROM ALERT_LOG
                         WHERE TEXT_LINE LIKE '___ ___ __ __:__:__ 20__'
                            OR TEXT_LINE LIKE 'Starting ORACLE instance %'))
         WHERE TEXT_LINE LIKE 'Starting ORACLE instance %')
/
SHUTDOWN             STARTUP               MINS_DOWN    DAYS_UP DAYS_STILL_UP
-------------------- -------------------- ---------- ---------- -------------
26-jul-2012 17:34    27-jul-2012 08:41        907.23        .37
27-jul-2012 17:34    30-jul-2012 08:59       3804.52        .37
30-jul-2012 17:33    31-jul-2012 08:56         922.9        .36
31-jul-2012 17:33    01-aug-2012 08:59        926.07        .36
01-aug-2012 17:34    02-aug-2012 08:48        913.87        .36           .11

 

其中：SHUTDOWN        關閉資料庫的時間

            STARTUP              啟動資料庫的時間

            MINS_DOWN        資料庫關閉了多少分鐘

            DAYS_UP              資料庫一直執行了多少天

            DAYS_STILL_UP  資料庫最後一次執行到當前有多少天