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

最新推荐文章于 2021-05-11 18:38:36 发布
最新推荐文章于 2021-05-11 18:38:36 发布
阅读量163 收藏
点赞数
文章标签： 数据库

从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.


Connectedto:
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:unabletoextend 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:unabletolockfile
ORA-1102 signalled during: ALTER DATABASE MOUNT...
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-01157: cannot identify/lock datafile3 - see DBWR tracefile
ORA-01110: datafile3: '/opt/oracle/oradata/eygle/users01.dbf'
ORA-27037:unabletoobtainfilestatus
ORA-01157: cannot identify/lock datafile3 - see DBWR tracefile
ORA-01110: datafile3: '/opt/oracle/oradata/eygle/users01.dbf'

TEXT
-----------------------------------------------------------------------------------
ORA-01157: cannot identify/lock datafile4 - see DBWR tracefile
ORA-01110: datafile4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-27037:unabletoobtainfilestatus
ORA-01157: cannot identify/lock datafile4 - see DBWR tracefile
ORA-01110: datafile4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-01157: cannot identify/lock datafile4 - see DBWR tracefile
ORA-01110: datafile4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-27037:unabletoobtainfilestatus
ORA-01157: cannot identify/lock datafile4 - see DBWR tracefile
ORA-01110: datafile4: '/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 datafile4 - see DBWR tracefile
ORA-01110: datafile4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-27037:unabletoobtainfilestatus
ORA-01157: cannot identify/lock datafile4 - see DBWR tracefile
ORA-01110: datafile4: '/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: datafile4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-01115: IO error reading block fromfile4 (block # 1)
ORA-27069: skgfdisp: attempttodo I/O beyond the range of thefile
ORA-01122: databasefile1 failed verification check

TEXT
-----------------------------------------------------------------------------------
ORA-01110: datafile1: '/opt/oracle/oradata/eygle/system01.dbf'
ORA-01207:fileis 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: datafile4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-01115: IO error reading block fromfile4 (block # 1)
ORA-27069: skgfdisp: attempttodo I/O beyond the range of thefile
ORA-01194:file1 needs more recoverytobe consistent
ORA-01110: datafile1: '/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: datafile4: '/opt/oracle/oradata/eygle/eygle01.dbf'
ORA-01115: IO error reading block fromfile4 (block # 1)
ORA-27069: skgfdisp: attempttodo I/O beyond the range of thefile
ORA-01194:file1 needs more recoverytobe consistent
ORA-01110: datafile1: '/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: datafile4: '/opt/oracle/oradata/eygle/eygle01.dbf'

TEXT
-----------------------------------------------------------------------------------
ORA-01115: IO error reading block fromfile4 (block # 1)
ORA-27069: skgfdisp: attempttodo I/O beyond the range of thefile
ORA-01194:file1 needs more recoverytobe consistent
ORA-01110: datafile1: '/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:unabletoobtainfilestatus

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 openingfile'?/rdbms/admin/sql.bsq'
ORA-07391: sftopn: fopen error
ORA-01526: error in openingfile''
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 datafile3 - see DBWR tracefile
ORA-01110: datafile3: '/opt/oracle/oradata/eygle/eygle02.dbf'
ORA-27037:unabletoobtainfilestatus
ORA-1113 signalled during: ALTER DATABASE OPEN...
ORA-01157: cannot identify/lock datafile3 - see DBWR tracefile
ORA-01110: datafile3: '/opt/oracle/oradata/eygle/eygle02.dbf'
ORA-27037:unabletoobtainfilestatus
ORA-1113 signalled during: alter database open...

TEXT
-----------------------------------------------------------------------------------
ORA-01157: cannot identify/lock datafile3 - see DBWR tracefile
ORA-01110: datafile3: '/opt/oracle/oradata/eygle/eygle02.dbf'
ORA-27037:unabletoobtainfilestatus
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 datafile3 - see DBWR tracefile
ORA-01110: datafile3: '/opt/oracle/oradata/eygle/eygle02.dbf'

TEXT
-----------------------------------------------------------------------------------
ORA-27037:unabletoobtainfilestatus
ORA-283 signalled during: ALTER DATABASE RECOVER database ...
ORA-01157: cannot identify/lock datafile3 - see DBWR tracefile
ORA-01110: datafile3: '/opt/oracle/oradata/eygle/eygle02.dbf'
ORA-27037:unabletoobtainfilestatus
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 datafile3 - see DBWR tracefile
ORA-01110: datafile3: '/opt/oracle/oradata/eygle/users01.dbf'
ORA-27037:unabletoobtainfilestatus

TEXT
-----------------------------------------------------------------------------------
ORA-283 signalled during: ALTER DATABASE RECOVER database ...
ORA-01157: cannot identify/lock datafile3 - see DBWR tracefile
ORA-01110: datafile3: '/opt/oracle/oradata/eygle/users01.dbf'
ORA-27037:unabletoobtainfilestatus
ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont...
ORA-01157: cannot identify/lock datafile3 - see DBWR tracefile
ORA-01110: datafile3: '/opt/oracle/oradata/eygle/users01.dbf'
ORA-27037:unabletoobtainfilestatus
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 datafile3 - see DBWR tracefile
ORA-01110: datafile3: '/opt/oracle/oradata/eygle/eygle02.dbf'
ORA-27037:unabletoobtainfilestatus
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>

-The End-

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

转载于:http://blog.itpub.net/777981/viewspace-661866/

cuishenhen6889
关注
oracle ogg操作日志,ogg的oracle数据库同步
weixin_34380085的博客
04-02 1338
OGG(GoldenGate)Oracle GoldenGate 是一个实现异构 IT环境间数据实时数据集成和复制的综合软件包。该产品集支持高可用性解决方案、实时数据集成、事务更改数据捕获、运营和分析企业系统之间的数据复制、转换和验证。OracleGoldenGate 12c 通过简化配置和管理、加强与 Oracle Database的集成、支持云环境、扩展异构性以及增强安全性，实现了高性能。除了...
oracle1537,ora-1537 signalled during 错误分析
weixin_36242060的博客
04-03 403
Problem Description-------------------You have offline dropped a datafile then try to recreate the datafile (ALTERDATABASE ADD DATAFILE) with the same name and get error message:ORA-1537 (datafile alr...
11g警告文件的查看
congzhan2577的博客
08-30 76
11g警告文件的查看[@more@]11g警告文件的查看在11g中警告文件将有两种格式来存放：文本格式和xml格式。同时在11g中新引入了一个视图v$diag_info：SQL> col value format a40S...
RAC 实例不能启动 ORA-1589 signalled during ALTER DATABASE OPEN
David Dai -- Focus on Oracle
09-28 7068
<br /> <br />今天启动RAC 做实验，发现RAC 实例怎么启动不了。<br /> <br />[oracle@rac2 bin]$ crs_stat -t<br />Name           Type           Target    State     Host<br />------------------------------------------------------------<br />ora.orcl.db    application    ONLINE    O
ORA-214 signalled during: ALTER DATABASE MOUNT 问题
weixin_33681778的博客
07-11 1189
数据库服务器移动了位置，后来再连其数据库发现提示无法连接，大致为“无法为所有新实例创建连接。。。”，查看alert日志发现有如下错误： starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...starting up 1 shared server(s) ...Thu Jul...
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file问题解决
congtudi4111的博客
07-31 269
Subject: DB <9.2.0.3 Cannot AUTOEXTEND to 4Gb boundary in NT Doc ID: Note:317174.1 Type: PROB...
oracle文件系统I O,ORA-27069: skgfdisp: 尝试在文件范围外执行 I/O
weixin_39724793的博客
04-11 290
接到网友技术支持请求,win 2003 ntfs格式文件系统,Oracle 8.1.7版本,主机重启后,数据库无法正常启动,offline datafile 15,数据库open成功,但是datafile 无法正常online,报错为:ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file,请求协助处理SQL> ...
教你怎样用Oracle方便地查看报警日志错误
12-16
这篇指南将教你如何方便地查看和管理Oracle的报警日志，包括备份、清除以及通过外部表进行查询。 首先，备份和清除报警日志是维护数据库健康状态的基础步骤。在Oracle的默认安装路径 `$ORACLE_HOME/SID/bdump/` 下...
ORACLE 报警日志如何查看？第1/2页
09-11
首先，让我们理解一下什么是Oracle外部表。外部表是一种特殊类型的表，它的数据存储在操作系统（OS）的文件中，而不是数据库的表空间里。这使得外部表可以用来直接查询非数据库格式的文件，例如文本文件或CSV文件。...
如何查看数据库alert日志文件
09-10
现在，我们可以通过查询这个外部表来查看日志文件中的内容，例如： ```sql SELECT * FROM alert WHERE ROWNUM ; ``` 这将返回前20行的日志条目，展示出如启动实例、依赖库问题、错误消息等信息。对于数据库管理员来...
不能打开数据库alter database open
porly的专栏
08-26 1万+
http://www.itpub.net/thread-975343-1-1.html     SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> alter database open; alter database open * 第 1 行出现错误: ORA-00313: 无法打
oracle ORA-00922: 选项缺失或无效
拥有必珍惜
11-29 4万+
情况说明：今天做数据库升级脚本时，发现多个SQL文件批量执行时就出现这个错误，单个SQL文件使用SQLPLUS执行时没有问题。因此，将报该错误可能得问题记录下来。 sqlplus 执行升级脚本时出现该问题可能三种原因；     1.SQL中出现不该有的关键字；     2.SQL文件的编码不正确，应该调整ASCII码     3.上一个执行脚本完成时，没有;分号，造成
ORA-12528: TNS: 监听程序: 所有适用例程都无法建立新连
热门推荐
zhch152的专栏
03-27 16万+
ORA-12528: TNS: 监听程序: 所有适用例程都无法建立新连 解决方安参照http://blog.sina.com.cn/s/blog_4b7325080100bm46.html 首先在cmd中执行lsnrctl status命令 C:\Documents and Settings\Administrator>lsnrctl status 会显示如下信息：(注意你的可能是英文版)
ORA-01789: 查询块具有不正确的结果列数
小广的博客
09-14 1734
项目中用到了   oracle union all 和 union   报错   后来发现是有一个*里面包含了一个列明，重复了   以下是转载   java.sql.SQLSyntaxErrorException: ORA-01789: 查询块具有不正确的结果列数   发现是sql语句用union时的 两个语句查询的字段不一致
ora-12910 不能设置默认表空间的问题
qq_23490959的博客
12-11 4441
select tablespace_name,contents from dba_tablespaces; 其中PERMANENT 为永久表空间，TEMPORARY为临时表空间，系统默认表空间要为永久表空间才可以，我的TEMP就创建错误了来了  。 解决方法：oracle必须存在一个默认的临时表空间，先建立一个临时表空间temp1，并将其默认，然后删除掉temp临时表空间，再建立temp临
更改tbm 10G undo表空间出现ora-00604 ora-00376 ora-01110错误(三)
cuiganglan0701的博客
10-28 481
最终重新启动Oracle已不起作用，reboot系统之后方可以连接上oracle。这时候可以mount上，但是似乎打开的时候或者操作的时候，显示Ora-00376 ora-01110之类的错误，显示无法读取'/oradata/...
oracle 1110 system01,oracle ORA-01114、ORA-27067错误解决方法
weixin_39636411的博客
04-08 150
今天有朋友问到如下一则案例，ORA-01114,ORA-27067以及OSD-04026错误同时出现：*** ACTION NAME:() 2009-04-01 09:31:00.762*** MODULE NAME:(JDBC Thin Client) 2009-04-01 09:31:00.762*** SERVICE NAME:(SYS$USERS) 2009-04-01 09:31:00....
linux修复表空间,表空间恢复
weixin_31260665的博客
05-11 98
如何恢复?有RMAN的全备[oracle@localhost bean]$ ls -ltotal 1091988-rw-r-----1 oracle oinstall 7061504 Jul 13 15:31 control01.ctl-rw-r-----1 oracle oinstall 7061504 Jul 13 15:31 control02.ctl-rw-r-----...
Oracle外部表的创建与使用教程
"Oracle外部表是一种在Oracle数据库中访问非Oracle数据源的机制，它允许数据库用户通过SQL查询处理存储在文件系统、网络共享或任何其他外部数据源的数据。Oracle Warehouse Builder (OWB) 是一个工具，通常用于数据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值