先通过查看系统参数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休闲数据库