select value from v$diag_info where name ='Diag Trace';
/app/oracle/diag/rdbms/db2/DB2/trace/alert_DB2.log
https://www.doczj.com/doc/3f18118443.html,/104152/viewspace-139982/
上周去巡检.结果一个用户问我如何使用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
https://www.doczj.com/doc/3f18118443.html,/22558114/viewspace-1097786
-- 可以查询 alert 日志中的任何信息
-- 创建 bdump 目录
sys@DEMO> create directory b_dump_dir as 'D:\oracle\product\10.2.0\admin\demo\bdump'; Directory created.
--
sys@DEMO> select * from all_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
----- --------------- -------------------------------------------
SYS B_DUMP_DIR D:\oracle\product\10.2.0\admin\demo\bdump
1 rows selected.
-- 赋予用户权限
sys@DEMO> grant read, write on directory b_dump_dir to go;
Grant succeeded.
sys@DEMO> conn go/go
-- 创建外部表
create table alert_log
(
text_line varchar(255)
)
organization external
(
type oracle_loader
default directory b_dump_dir
access parameters
(
records delimited by newline
fields
reject rows with all null fields
)
location
(
'alert_demo.log'
)
)
reject limit unlimited
/
-- 执行查询语句
select
to_char(last_time,'yyyy-mm-dd hh24:mi') shutdown, to_char(start_time,'yyyy-mm-dd 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 ----------------- ----------------- ---------- ------- -------------2011-07-24 15:10 2011-07-24 15:10 0
2011-07-24 15:31 2011-07-24 15:31 .65 .01
2011-07-24 15:36 2011-07-24 15:39 2.48 0
2011-07-24 20:08 2011-07-24 20:57 49.08 .19
2011-07-25 21:51 2011-07-25 21:51 0 1.04
2011-07-29 10:18 2011-07-29 10:19 .9 3.52
2011-07-29 10:20 2011-07-29 10:22 2 0
2011-07-29 10:25 2011-07-29 10:25 .1 0
2011-08-07 11:32 2011-08-07 11:32 .07 9.05
2011-08-07 11:35 2011-08-07 11:36 .05 0
2011-08-07 12:38 2011-08-07 12:38 .5 .04
2011-08-07 17:16 2011-08-07 17:16 .13 .19
2011-08-07 17:21 2011-08-07 17:26 5.18 0
2011-08-07 17:32 2011-08-07 17:32 .12 0
2011-08-08 21:14 2011-08-08 21:14 0 1.15
2011-08-08 22:06 2011-08-08 22:06 .35 .04
2011-08-12 21:39 2011-08-12 21:39 0 3.98
2011-08-12 21:40 2011-08-12 21:40 .05 0
2011-08-13 09:25 2011-08-13 09:25 0 .49 .17 19 rows selected.
https://www.doczj.com/doc/3f18118443.html,/26686207/viewspace-764596
外部表：表中的数据以操作系统文件的方式来存放，现在表中的数据不是放在数据库中了而是放在操作系统上面，Oracle提供了一种直接读取外部数据的机制。
外部表好处：1.数据二次开发
2.大数据量迁移
3.充分利用操作系统空间
4.不占用数据库空间
5.支持标准SQL条件检索
外部表也需要目录对象的支持，通过目录对象可以知道从哪个目录读取文本数据
LEO1@LEO1>create directory alert as'/u02/app/oracle/diag/rdbms/leo1/LEO1/trace';
Directory created.
这是Oracle 11g 告警日志目录
grant read,write on directory alert topublic; 对这个目录对象授予读/写权限，并授予所有用户
LEO1@LEO1>select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------------------------------------------------------------------------------------------------------
SYS EXP_DUMP /home/oracle/exp_dump
SYS XMLDIR /u02/app/oracle/product/11.2.0/db_1/rdbms/xml
SYS ALERT /u02/app/oracle/diag/rdbms/leo1/LEO1/trace
SYS DATA_PUMP_DIR /u02/app/oracle/admin/LEO1/dpdump/
SYS ORACLE_OCM_CONFIG_DIR /u02/app/oracle/product/11.2.0/db_1/ccr/state
我们下面就是Oracle告警日志文件当作数据库的一个外部数据源来访问，我们使用外部表的方式抽取alert日志数据，然后使用标准SQL语句来检索“ora-错误信息”。
下面我们就来创建一个外部表
LEO1@LEO1>create table leo_alert(content varchar2(4000)) alert日志数据量多因此字符串设置的大一点
organization external
(
type oracle_loader 如果你设置的是oracle_datapump请修改为loader
default directory alert
access parameters (
records delimited by newline 每条记录用换行区分
nobadfile 没有坏文件，丢弃文件，日志文件nodiscardfile
nologfile
)
location ('alert_LEO1.log') 加载告警日志文件内容
); 2 3 4 5 6 7 8 9 10 11 12 13
LEO1@LEO1>select count(*) fromleo_alert; 一共7198条
COUNT(*)
----------------
7198
我们抽取其中10条ORA-开头的错误记录显示出来
LEO1@LEO1>select * from leo_alert wherecontent like '%ORA-%' and rownum<=10;
CONTENT
------------------------------------------------------------------------------------------------------------------------------------------------------------------ORA-210 signalled during: create tablespacetest datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextendoff... ORA-00210: cannot open the specifiedcontrol file
ORA-00202: control file:'/u02/app/oracle/oradata/LEO1/control01.ctl'
ORA-27041: unable to open file
ORA-00210: cannot open the specifiedcontrol file
ORA-00202: control file:'/u02/app/oracle/oradata/LEO1/control01.ctl'
ORA-27041: unable to open file
ORA-00210: cannot open the specifiedcontrol file
ORA-00202: control file:'/u02/app/oracle/oradata/LEO1/control01.ctl'
ORA-27037: unable to obtain file status
10 rows selected.
小结：这里需要注意几个问题，我们在创建外部表的时候需要设置没有坏文件，丢弃文件，日志文件参数否则会报错ORA-29913: error in executing ODCIEXTTABLEOPEN callout。