文档章节

使用Oracle的外部表查询警告日志文件

郑加威
 郑加威
发布于 2017/01/25 09:28
字数 2106
阅读 11
收藏 0

从Oracle9i开始,Oracle的外部表技术(Oracle External Tables)被极大的增强,通过外部表访问外部数据增强了Oracle数据库和外部数据源进行数据交互的能力,对于数据仓库和ETL来说,这些增强极大的方便了数据访问。

对于DBA来说,最常见一个例子是可以使用外部表来访问警告日志文件或其他跟踪文件.
以下一个例子用来说明外部表的用途。

首先需要创建一个Directory:

[oracle@jumper oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Oct 15 21:42:28 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> create or replace directory bdump 
2 as '/opt/oracle/admin/eygle/bdump';

Directory created.

SQL> col DIRECTORY_PATH for a30
SQL> col owner for a10
SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------------ ------------------------------
SYS BDUMP /opt/oracle/admin/eygle/bdump

然后创建一个外部表:

SQL> create table alert_log ( text varchar2(400) )
2 organization external (
3 type oracle_loader
4 default directory BDUMP
5 access parameters (
6 records delimited by newline
7 nobadfile
8 nodiscardfile
9 nologfile
10 )
11 location('alert_eygle.log')
12 )
13 reject limit unlimited
14 /
Table created.

然后我们就可以通过外部表进行查询警告日志的内容:

SQL> select * from alert_log where rownum < 51;
TEXT
-----------------------------------------------------------------------------------------
Mon Jun 26 12:00:24 2006
Starting ORACLE instance (normal)
Mon Jun 26 12:00:25 2006
WARNING: EINVAL creating segment of size 0x0000000008c00000
fix shm parameters in /etc/system or equivalent
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 104857600
large_pool_size = 0
java_pool_size = 0
control_files = /opt/oracle/oradata/eygle/control01.ctl
db_block_size = 8192
db_cache_size = 16777216
db_cache_advice = ON
compatible = 9.2.0.0.0
db_file_multiblock_read_count= 16
fast_start_mttr_target = 300
log_checkpoints_to_alert = TRUE
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = eygle
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = /opt/oracle/admin/eygle/bdump
user_dump_dest = /opt/oracle/admin/eygle/udump
core_dump_dest = /opt/oracle/admin/eygle/cdump
sort_area_size = 524288
db_name = eygle
open_cursors = 500
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 52428800
aq_tm_processes = 0
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7

50 rows selected.

SQL>

如果我们需要查看数据库中曾经出现过的ORA-错误,那么可以执行如下查询:

SQL> select * from alert_log where text like 'ORA-%';
TEXT
-----------------------------------------------------------------------------------
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
ORA-1113 signalled during: alter database open...
ORA-1113 signalled during: alter database datafile 3 online...
ORA-09968: scumnt: unable to lock file
ORA-1102 signalled during: ALTER DATABASE MOUNT...
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'
ORA-27037: unable to obtain file status
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'

TEXT
-----------------------------------------------------------------------------------
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-27037: unable to obtain file status
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-27037: unable to obtain file status
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'

TEXT
-----------------------------------------------------------------------------------
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-1113 signalled during: alter database open...
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-27037: unable to obtain file status
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-1113 signalled during: alter database open...
ORA-1122 signalled during: alter database open...
ORA-283 signalled during: ALTER DATABASE RECOVER database ...
ORA-1122 signalled during: alter database open...

TEXT
-----------------------------------------------------------------------------------
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-1113 signalled during: ALTER DATABASE OPEN...
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-1122 signalled during: ALTER DATABASE OPEN...
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "EYGLE" NORESETL...
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-1991 signalled during: ALTER DATABASE MOUNT...
ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-01115: IO error reading block from file 4 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
ORA-01122: database file 1 failed verification check

TEXT
-----------------------------------------------------------------------------------
ORA-01110: data file 1: '/opt/oracle/oradata/eygle/system01.dbf'
ORA-01207: file is more recent than controlfile - old controlfile
ORA-1122 signalled during: alter database open...
ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont...
ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-01115: IO error reading block from file 4 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/eygle/system01.dbf'
ORA-1194 signalled during: alter database open resetlogs...
ORA-283 signalled during: ALTER DATABASE RECOVER datafile 1 ...

TEXT
-----------------------------------------------------------------------------------
ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont...
ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-01115: IO error reading block from file 4 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/eygle/system01.dbf'
ORA-1194 signalled during: alter database open resetlogs...
ORA-283 signalled during: ALTER DATABASE RECOVER datafile 1 ...
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-1589 signalled during: ALTER DATABASE OPEN...
ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf'

TEXT
-----------------------------------------------------------------------------------
ORA-01115: IO error reading block from file 4 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/eygle/system01.dbf'
ORA-1194 signalled during: alter database open resetlogs...
ORA-1109 signalled during: alter database close...
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "EYGLE" NORESETL...
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-1113 signalled during: alter database open...
ORA-00202: controlfile: '/opt/oracle/oradata/eygle/control01.ctl'
ORA-27037: unable to obtain file status

TEXT
-----------------------------------------------------------------------------------
ORA-205 signalled during: ALTER DATABASE MOUNT...
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-01501: CREATE DATABASE failed
ORA-01526: error in opening file '?/rdbms/admin/sql.bsq'
ORA-07391: sftopn: fopen error
ORA-01526: error in opening file ''
ORA-1092 signalled during: CREATE DATABASE eygle
ORA-1079 signalled during: ALTER DATABASE MOUNT...
ORA-1507 signalled during: alter database open...
ORA-214 signalled during: alter database mount...
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...

TEXT
-----------------------------------------------------------------------------------
ORA-214 signalled during: ALTER DATABASE MOUNT...
ORA-214 signalled during: alter database mount...
ORA-1113 signalled during: ALTER DATABASE OPEN...
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'
ORA-27037: unable to obtain file status
ORA-1113 signalled during: ALTER DATABASE OPEN...
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'
ORA-27037: unable to obtain file status
ORA-1113 signalled during: alter database open...

TEXT
-----------------------------------------------------------------------------------
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'
ORA-27037: unable to obtain file status
ORA-283 signalled during: ALTER DATABASE RECOVER database ...
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-1100 signalled during: alter database mount...
ORA-1178 signalled during: alter database create datafile '/opt/oracle/produc...
ORA-1516 signalled during: alter database create datafile '/opt/oracle/oradat...
ORA-1991 signalled during: ALTER DATABASE MOUNT...
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'

TEXT
-----------------------------------------------------------------------------------
ORA-27037: unable to obtain file status
ORA-283 signalled during: ALTER DATABASE RECOVER database ...
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'
ORA-27037: unable to obtain file status
ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont...
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-1991 signalled during: ALTER DATABASE MOUNT...
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'
ORA-27037: unable to obtain file status

TEXT
-----------------------------------------------------------------------------------
ORA-283 signalled during: ALTER DATABASE RECOVER database ...
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'
ORA-27037: unable to obtain file status
ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont...
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf'
ORA-27037: unable to obtain file status
ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont...
ORA-279 signalled during: ALTER DATABASE RECOVER database using backup cont...
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...

TEXT
-----------------------------------------------------------------------------------
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...
ORA-1589 signalled during: alter database open...
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "EYGLE" NORESETL...
ORA-1178 signalled during: alter database create datafile '/opt/oracle/produc...
ORA-1991 signalled during: ALTER DATABASE MOUNT...
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf'
ORA-27037: unable to obtain file status
ORA-1157 signalled during: alter database open...

TEXT
-----------------------------------------------------------------------------------
ORA-1113 signalled during: alter database open...
ORA-1991 signalled during: ALTER DATABASE MOUNT...
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-1031 signalled during: alter database open...
ORA-3217 signalled during: ALTER TABLESPACE TEMP DEFAULT STORAGE (INITIAL 10M...
ORA-1507 signalled during: alter database close...
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-1507 signalled during: alter database close normal...
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-1106 signalled during: alter database dismount...
ORA-1531 signalled during: alter database open...

TEXT
-----------------------------------------------------------------------------------
ORA-1531 signalled during: alter database open...
ORA-1531 signalled during: alter database open...
ORA-1531 signalled during: alter database open...
ORA-1531 signalled during: alter database open...
ORA-1109 signalled during: alter database close...
ORA-1507 signalled during: alter database close...
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-1185 signalled during: alter database add logfile group 6
ORA-350 signalled during: alter database drop logfile group 3...

163 rows selected.

SQL>

 

© 著作权归作者所有

郑加威
粉丝 171
博文 183
码字总数 387300
作品 0
杭州
架构师
私信 提问
使用Oracle外部表分析访问日志

经过使用Oracle的外部表对Oracle的警告日志文件、跟踪文件进行获取和分析之后,我发现外部表实在是非常易用,甚至到了随心所欲的境地(当然外部表尚不能修改外部文件)。 使用外部表可以很容...

郑加威
2017/01/26
22
0
使用Oracle的外部表访问跟踪文件

前面说过Oracle的外部表可以用来访问警告日志文件,其实Oracle的外部表可以非常灵活的被使用。 通过Create Directory命令创建相应的Directory之后,我们可以将目录的访问权限授予其他用户,这...

郑加威
2017/01/26
11
0
oracle告警日志文件

告警日志介绍 告警日志文件是一类特殊的跟踪文件(trace file)。告警日志文件命名一般为alert_.log,其中SID为ORACLE数据库实例名称。数据库告警日志是按时间顺序记录message和错误信息。 ...

y862318
2014/08/11
0
0
Oracle 11g R2 常见问题处理

--======================查询Oracle错误日志和警告日志 通过命令查看错误日志目录 SQL> show parameter backgrounddumpdest; 根据错误提示的PID:3159 模糊查询对应的错误日志 find -name "...

UltraSQL
2018/07/23
0
0
怎么对Navicat Mac 模型进行偏好设置

Navicat 给全部工具提供完整的自定义用户界面选项,方便用户根据自己的需求进行自定义,本教程将介绍Navicat Mac 模型和文件路径偏好设置的方法。从主菜单选择 Navicat Premium->偏好设置,即...

Navicat数据库管理工具
2016/10/10
150
0

没有更多内容

加载失败,请刷新页面

加载更多

小知识:讲述Linux命令别名与资源文件的区别

别名 别名是命令的快捷方式。为那些需要经常执行,但需要很长时间输入的长命令创建快捷方式很有用。语法是: alias ppp='ping www.baidu.com' 它们并不总是用来缩短长命令。重要的是,你将它...

老孟的Linux私房菜
38分钟前
2
0
《JAVA核心知识》学习笔记(6. Spring 原理)-5

它是一个全面的、企业应用开发一站式的解决方案,贯穿表现层、业务层、持久层。但是 Spring 仍然可以和其他的框架无缝整合。 6.1.1. Spring 特点 6.1.1.1. 轻量级 6.1.1.2. 控制反转 6.1.1....

Shingfi
40分钟前
4
0
Excel导入数据库数据+Excel导入网页数据【实时追踪】

1.Excel导入数据库数据:数据选项卡------>导入数据 2.Excel导入网页数据【实时追踪】:

东方墨天
48分钟前
5
1
正则表达式如何匹配一个单词存在一次或零次并且不占捕获组位置

正则表达式如何匹配一个单词存在一次或零次并且不占捕获组位置 今天要用正则表达式实现匹配一个词出现一次或者不出现的情况,但是又不仅仅是这么简单的需求。先详细说下我这种情况吧,也许有...

Airship
54分钟前
6
0
第八讲:asp.net C# web 读取文件

本讲主要讲解如何在asp.net页面上传文件。 首先,前台页面: 其次,后台页面: 结果: 1、前台效果: 2、后台结果:

刘日辉
今天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部