使用sql查看alert日志

上周去巡检.结果一个用户问我如何使用sql语句来查看alert日志中的内容. 当时想着用存储过程来做.回来后想了想,用external测试一下看看:

SQL> !pwd
/u01/app/oracle/admin/ORALINUX/bdump

create directory BDUMP as '/u01/app/oracle/admin/ORALINUX/bdump';

SQL> !ls
ALERT_LOG_3220.bad alert_ORALINUX.log oralinux_lgwr_3202.trc oralinux_lgwr_7056.trc
ALERT_LOG_3220.log oralinux_lgwr_3123.trc oralinux_lgwr_6993.trc oralinux_pmon_7048.trc

create table alert_log ( text varchar2(80) )
organization external (
type oracle_loader
default directory BDUMP
access parameters (
records delimited by newline
)
location('alert_ORALINUX.log')
)
reject limit 1000;

SQL> select * from alert_log where text like '%ORA-%';

TEXT
--------------------------------------------------------------------------------
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/oradata/ORALINUX/redo01.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u02/oradata/ORALINUX/redo02.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u02/oradata/ORALINUX/redo03.log'
ORA-27037: unable to obtain file status

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/104152/viewspace-139982/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/104152/viewspace-139982/