1、创建目录【注意目录是alert_sid.log所在的目录,使用show parameter background_dump_dest查看】

sys@ORA11G> show parameter background_dump_dest

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

sys@ORA11G> 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告警相关信息了

如：

sys@ORA11G> 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:

sys@ORA11G>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  数据库最后一次运行到当前有多少天