分享
 
 
 

巧用外部表访问警告日志文件或跟踪文件

王朝other·作者佚名  2008-06-01
窄屏简体版  字體: |||超大  

从Oracle数据库9i开始,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>

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有