Oracle_loader将警告文件导入到表里面

先通过查看系统参数background_dump_dest找出存放alert log文件的地址：

SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

background_dump_dest                 string      /app/oracle/product/10.2.0.1/a

                                                 dmin/test/bdump

然后创建一个路径并设为默认路径再建表将指定LOG文件的内容存进表里面（这样查看警告日志文件的信息就很方便的说！）

SQL> create directory data_dir as '/app/oracle/product/10.2.0.1/admin/test/bdump/'

  2  /

Directory created.

SQL> create table alert_test_log

  2  (

  3  text_line varchar2(255)

  4  )

  5  organization external

  6  (

  7  type oracle_loader

  8  default directory data_dir

  9  access parameters

 10  (

 11  records delimited by newline

 12  fields

 13  reject rows with all null fields

 14  )

 15  location

 16  (

 17  'alert_test.log'

 18  )

 19  )

 20  reject limit unlimited

 21  /

Table created.

SQL> select count(*) from alert_test_log;

  COUNT(*)

----------

      2227

这只是一个用oracle_loader把LOG文件存到表里面的一个例子，具体怎样通过这个报警日志文件进行系统分析以及oracle_loader的详细用法以后再更新上来。

分析准备：

（1）LAG(EXPRESSION,<OFFSET>,<DEFAULT>)  

第一个是表的字段名，第二个是表示与上几个数据进行对比，第三个值表示如果没有值就默认显示成什么

SQL> select last_name,hire_date,salary,lag(salary,2,0)over(order by hire_date)as prev_sal

  2  from employees where job_ID='PU_CLERK';

LAST_NAME                 HIRE_DATE     SALARY   PREV_SAL

------------------------- --------- ---------- ----------

Khoo                      18-MAY-95       3100          0

Tobias                    24-JUL-97       2800          0

Baida                     24-DEC-97       2900       3100

Himuro                    15-NOV-98       2600       2800

Colmenares                10-AUG-99       2500       2900

Lead() 与LAG()用法一样，只是对比的方向相反。

SQL> select last_name,hire_date,salary,lead(salary,1,0)over(order by hire_date) as prev_sal

  2  from employees where job_ID='PU_CLERK';

LAST_NAME                 HIRE_DATE     SALARY   PREV_SAL

------------------------- --------- ---------- ----------

Khoo                      18-MAY-95       3100       2800

Tobias                    24-JUL-97       2800       2900

Baida                     24-DEC-97       2900       2600

Himuro                    15-NOV-98       2600       2500

Colmenares                10-AUG-99       2500          0

//2010-05-20 14:40 

搞了一晚上，我终于整出来啦！！！  （通过警告文件查看数据库的启动和关闭时间日志记录）

select f,lag(start_time,1,'Nodata')over(order by f)last_start_time, end_time,start_time

from

(

     Select rownum f ,text_line,start_time, end_time

     from (

             Select text_line,lag(text_line,1,'Nodata')over(order by r)start_time, lag(text_line,2,'Nodata')over(order by r)end_time

             from (

                    select rownum r, text_line

                    from alert_test_log

                    where text_line like 'Starting ORACLE instance %' or text_line like '___ ___ __ __:__:__ 20__' )    --查出oracle启动标志及LOG文件中所有的时间信息

           )  --查出LOG文件中所有时间，oracle启动标志及他上一行（就是他的启动时间）和上两行信息（上两行的信息肯定就是上一次关闭的时间三！）

where text_line like 'Starting ORACLE instance %'

) ;

显示结果类似于以下信息：

         F

----------

LAST_START_TIME

--------------------------------------------------------------------------------

END_TIME

--------------------------------------------------------------------------------

START_TIME

--------------------------------------------------------------------------------

        16

Wed May 19 16:27:27 2010

Wed May 19 16:48:18 2010

Wed May 19 16:48:27 2010

         F

----------

LAST_START_TIME

--------------------------------------------------------------------------------

END_TIME

--------------------------------------------------------------------------------

START_TIME

--------------------------------------------------------------------------------

        17

Wed May 19 16:48:27 2010

Wed May 19 17:02:18 2010

Wed May 19 17:02:23 2010

//2010-05-20 20:30 

Update 对时间转换进行了一次修正

select f,to_char(to_date(last_start_time,'dy Mon DD HH24:MI:SS YYYY'),'YYYY Mon DD HH24:MI:SS') last_start_time,to_char(to_date(end_time,'dy Mon DD HH24:MI:SS YYYY'),'YYYY Mon DD HH24:MI:SS')last_end_time,to_char(to_date(start_time,'dy Mon DD HH24:MI:SS YYYY'),'YYYY Mon DD HH24:MI:SS')start_time from( select f,lag(start_time,1,to_char(sysdate,'dy Mon DD HH24:MI:SS YYYY'))over(order by f)last_start_time, end_time,start_time from(Select rownum f ,text_line,start_time, end_time from (Select text_line,lag(text_line,1,to_char(sysdate,'dy Mon DD HH24:MI:SS YYYY'))over(order by r)start_time, lag(text_line,2,to_char(sysdate,'dy Mon DD HH24:MI:SS YYYY'))over(order by r)end_time from (select rownum r, text_line from alert_log where text_line like 'Starting ORACLE instance %' or text_line like '___ ___ __ __:__:__ 20__' ))where text_line like 'Starting ORACLE instance %')) where last_start_time not like 'Starting%'

//2010-05-20 23:30

再次更新，修正了上次数据库关闭时间显示不正确的BUG，以及不能显示出对应的是此LOG文件第几行的BUG，但还是有一点小问题，在last_end_time里面有三行数据显示的是 Starting ORACLE instance... 而不是时间格式。待续

select r,to_char(to_date(last_start_time,'dy Mon DD HH24:MI:SS YYYY'),'YYYY Mon DD HH24:MI:SS') last_start_time,to_char(to_date(last_end_time,'dy Mon DD HH24:MI:SS YYYY'),'YYYY Mon DD HH24:MI:SS')last_end_time,to_char(to_date(start_time,'dy Mon DD HH24:MI:SS YYYY'),'YYYY Mon DD HH24:MI:SS')start_time from( select r,lag(start_time,1,to_char(sysdate,'dy Mon DD HH24:MI:SS YYYY'))over(order by r)last_start_time, last_end_time,start_time from(Select r,text_line,start_time,last_end_time from (Select r,text_line,lag(text_line,1,to_char(sysdate,'dy Mon DD HH24:MI:SS YYYY'))over(order by r)start_time, last_end_time from (select rownum r, text_line,lag(text_line,2,to_char(sysdate,'dy Mon DD HH24:MI:SS YYYY'))over(order by rownum)last_end_time from alert_log where text_line like 'Starting ORACLE instance %' or text_line like '___ ___ __ __:__:__ 20__' ))where text_line like 'Starting ORACLE instance %') where last_end_time not like 'Starting%')

         R LAST_START_TIME      LAST_END_TIME        START_TIME

---------- -------------------- -------------------- --------------------

       369 2010 Apr 07 21:52:20 2010 Apr 07 22:00:23 2010 Apr 08 23:02:30

       423 2010 Apr 08 23:02:30 2010 Apr 08 23:54:04 2010 Apr 11 20:39:14

       470 2010 Apr 11 20:39:14 2010 Apr 11 21:16:01 2010 Apr 16 10:37:07

       516 2010 Apr 16 10:37:07 2010 Apr 16 10:42:00 2010 Apr 16 23:35:56

       566 2010 Apr 16 23:35:56 2010 Apr 17 00:38:07 2010 May 18 21:29:14

       617 2010 May 18 21:29:14 2010 May 18 22:24:27 2010 May 19 20:24:19

       656 2010 May 19 20:24:19 2010 May 19 20:53:53 2010 May 19 20:53:58

       706 2010 May 19 20:53:58 2010 May 19 21:17:35 2010 May 20 20:54:53

       756 2010 May 20 20:54:53 2010 May 20 22:34:58 2010 May 21 08:45:44

//2010-05-21 11:40

一样的道理，可以通过以下代码查出数据库的所有变动记录及时间

select r,text_line,to_char(to_date(Modifydate,'dy Mon DD HH24:MI:SS YYYY'),'YYYY-Mon_DD HH24:MI:SS')Modifydate from(select rownum r,text_line,lag(text_line,1)over(order by rownum)Modifydate from alert_test_log where text_line like 'alter%' or text_line like '___ ___ __ __:__:__ 20__' )where text_line like 'alter%';

©著作权归作者所有：来自51CTO博客作者Yofee的原创作品，如需转载，请注明出处，否则将追究法律责任

oracledatabase休闲数据库