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

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

《使用Oracle的外部表查询警告日志文件.docx》由会员分享，可在线阅读，更多相关《使用Oracle的外部表查询警告日志文件.docx（9页珍藏版）》请在冰豆网上搜索。

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

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

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

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

以下一个例子用来说明外部表的用途。

首先需要创建一个Directory：

[oracle@jumperoracle]$sqlplus"/assysdba"

SQL*Plus:

Release9.2.0.4.0-ProductiononSunOct1521:

42:

282006

Copyright（c）1982,2002,OracleCorporation.Allrightsreserved.

Connectedto:

Oracle9iEnterpriseEditionRelease9.2.0.4.0-Production

WiththePartitioningoption

JServerRelease9.2.0.4.0-Production

SQL>createorreplacedirectorybdump

2as'/opt/oracle/admin/eygle/bdump';

Directorycreated.

SQL>colDIRECTORY_PATHfora30

SQL>colownerfora10

SQL>select*fromdba_directories;

OWNERDIRECTORY_NAMEDIRECTORY_PATH

----------------------------------------------------------------------

SYSBDUMP/opt/oracle/admin/eygle/bdump

然后创建一个外部表:

SQL>createtablealert_log（textvarchar2（400））

2organizationexternal（

3typeoracle_loader

4defaultdirectoryBDUMP

5accessparameters（

6recordsdelimitedbynewline

7nobadfile

8nodiscardfile

9nologfile

10）

11location（'alert_eygle.log'）

12）

13rejectlimitunlimited

14/

Tablecreated.

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

SQL>select*fromalert_logwhererownum<51;

TEXT

-----------------------------------------------------------------------------------------

MonJun2612:

00:

242006

StartingORACLEinstance（normal）

MonJun2612:

00:

252006

WARNING:

EINVALcreatingsegmentofsize0x0000000008c00000

fixshmparametersin/etc/systemorequivalent

LICENSE_MAX_SESSION=0

LICENSE_SESSIONS_WARNING=0

SCNscheme2

Usinglog_archive_destparameterdefaultvalue

LICENSE_MAX_USERS=0

SYSauditingisdisabled

StartingupORACLERDBMSVersion:

9.2.0.4.0.

Systemparameterswithnon-defaultvalues:

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

PMONstartedwithpid=2

DBW0startedwithpid=3

LGWRstartedwithpid=4

CKPTstartedwithpid=5

SMONstartedwithpid=6

RECOstartedwithpid=7

50rowsselected.

SQL>

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

SQL>select*fromalert_logwheretextlike'ORA-%';

TEXT

-----------------------------------------------------------------------------------

ORA-1652:

unabletoextendtempsegmentby128intablespaceTEMP

ORA-1113signalledduring:

alterdatabaseopen...

ORA-1113signalledduring:

alterdatabasedatafile3online...

ORA-09968:

scumnt:

unabletolockfile

ORA-1102signalledduring:

ALTERDATABASEMOUNT...

ORA-1507signalledduring:

ALTERDATABASECLOSENORMAL...

ORA-01157:

cannotidentify/lockdatafile3-seeDBWRtracefile

ORA-01110:

datafile3:

'/opt/oracle/oradata/eygle/users01.dbf'

ORA-27037:

unabletoobtainfilestatus

ORA-01157:

cannotidentify/lockdatafile3-seeDBWRtracefile

ORA-01110:

datafile3:

'/opt/oracle/oradata/eygle/users01.dbf'

TEXT

-----------------------------------------------------------------------------------

ORA-01157:

cannotidentify/lockdatafile4-seeDBWRtracefile

ORA-01110:

datafile4:

'/opt/oracle/oradata/eygle/eygle01.dbf'

ORA-27037:

unabletoobtainfilestatus

ORA-01157:

cannotidentify/lockdatafile4-seeDBWRtracefile

ORA-01110:

datafile4:

'/opt/oracle/oradata/eygle/eygle01.dbf'

ORA-1109signalledduring:

ALTERDATABASECLOSENORMAL...

ORA-01157:

cannotidentify/lockdatafile4-seeDBWRtracefile

ORA-01110:

datafile4:

'/opt/oracle/oradata/eygle/eygle01.dbf'

ORA-27037:

unabletoobtainfilestatus

ORA-01157:

cannotidentify/lockdatafile4-seeDBWRtracefile

ORA-01110:

datafile4:

'/opt/oracle/oradata/eygle/eygle01.dbf'

TEXT

-----------------------------------------------------------------------------------

ORA-1109signalledduring:

ALTERDATABASECLOSENORMAL...

ORA-1113signalledduring:

alterdatabaseopen...

ORA-01157:

cannotidentify/lockdatafile4-seeDBWRtracefile

ORA-01110:

datafile4:

'/opt/oracle/oradata/eygle/eygle01.dbf'

ORA-27037:

unabletoobtainfilestatus

ORA-01157:

cannotidentify/lockdatafile4-seeDBWRtracefile

ORA-01110:

datafile4:

'/opt/oracle/oradata/eygle/eygle01.dbf'

ORA-1113signalledduring:

alterdatabaseopen...

ORA-1122signalledduring:

alterdatabaseopen...

ORA-283signalledduring:

ALTERDATABASERECOVERdatabase...

ORA-1122signalledduring:

alterdatabaseopen...

TEXT

-----------------------------------------------------------------------------------

ORA-1109signalledduring:

ALTERDATABASECLOSENORMAL...

ORA-1113signalledduring:

ALTERDATABASEOPEN...

ORA-1109signalledduring:

ALTERDATABASECLOSENORMAL...

ORA-1122signalledduring:

ALTERDATABASEOPEN...

ORA-1503signalledduring:

CREATECONTROLFILEREUSEDATABASE"EYGLE"NORESETL...

ORA-1109signalledduring:

ALTERDATABASECLOSENORMAL...

ORA-1991signalledduring:

ALTERDATABASEMOUNT...

ORA-01110:

datafile4:

'/opt/oracle/oradata/eygle/eygle01.dbf'

ORA-01115:

IOerrorreadingblockfromfile4（block#1）

ORA-27069:

skgfdisp:

attempttodoI/Obeyondtherangeofthefile

ORA-01122:

databasefile1failedverificationcheck

TEXT

-----------------------------------------------------------------------------------

ORA-01110:

datafile1:

'/opt/oracle/oradata/eygle/system01.dbf'

ORA-01207:

fileismorerecentthancontrolfile-oldcontrolfile

ORA-1122signalledduring:

alterdatabaseopen...

ORA-283signalledduring:

ALTERDATABASERECOVERdatabaseusingbackupcont...

ORA-01110:

datafile4:

'/opt/oracle/oradata/eygle/eygle01.dbf'

ORA-01115:

IOerrorreadingblockfromfile4（block#1）

ORA-27069:

skgfdisp:

attempttodoI/Obeyondtherangeofthefile

ORA-01194:

file1needsmorerecoverytobeconsistent

ORA-01110:

datafile1:

'/opt/oracle/oradata/eygle/system01.dbf'

ORA-1194signalledduring:

alterdatabaseopenresetlogs...

ORA-283signalledduring:

ALTERDATABASERECOVERdatafile1...

TEXT

-----------------------------------------------------------------------------------

ORA-283signalledduring:

ALTERDATABASERECOVERdatabaseusingbackupcont...

ORA-01110:

datafile4:

'/opt/oracle/oradata/eygle/eygle01.dbf'

ORA-01115:

IOerrorreadingblockfromfile4（block#1）

ORA-27069:

skgfdisp:

attempttodoI/Obeyondtherangeofthefile

ORA-01194:

file1needsmorerecoverytobeconsistent

ORA-01110:

datafile1:

'/opt/oracle/oradata/eygle/system01.dbf'

ORA-1194signalledduring:

alterdatabaseopenresetlogs...

ORA-283signalledduring:

ALTERDATABASERECOVERdatafile1...

ORA-1109signalledduring:

ALTERDATABASECLOSENORMAL...

ORA-1589signalledduring:

ALTERDATABASEOPEN...

ORA-01110:

datafile4:

'/opt/oracle/oradata/eygle/eygle01.dbf'

TEXT

-----------------------------------------------------------------------------------

ORA-01115:

IOerrorreadingblockfromfile4（block#1）

ORA-27069:

skgfdisp:

attempttodoI/Obeyondtherangeofthefile

ORA-01194:

file1needsmorerecoverytobeconsistent

ORA-01110:

datafile1:

'/opt/oracle/oradata/eygle/system01.dbf'

ORA-1194signalledduring:

alterdatabaseopenresetlogs...

ORA-1109signalledduring:

alterdatabaseclose...

ORA-1503signalledduring:

CREATECONTROLFILEREUSEDATABASE"EYGLE"NORESETL...

ORA-1507signalledduring:

ALTERDATABASECLOSENORMAL...

ORA-1113signalledduring:

alterdatabaseopen...

ORA-00202:

controlfile:

'/opt/oracle/oradata/eygle/control01.ctl'

ORA-27037:

unabletoobtainfilestatus

TEXT

-----------------------------------------------------------------------------------

ORA-205signalledduring:

ALTERDATABASEMOUNT...

ORA-1507signalledduring:

ALTERDATABASECLOSENORMAL...

ORA-01501:

CREATEDATABASEfailed

ORA-01526:

errorinopeningfile'?

/rdbms/admin/sql.bsq'

ORA-07391:

sftopn:

fopenerror

ORA-01526:

errorinopeningfile''

ORA-1092signalledduring:

CREATEDATABASEeygle

ORA-1079signalledduring:

ALTERDATABASEMOUNT...

ORA-1507signalledduring:

alterdatabaseopen...

ORA-214signalledduring:

alterdatabasemount...

ORA-1507signalledduring:

ALTERDATABASECLOSENORMAL...

TEXT

-----------------------------------------------------------------------------------

ORA-214signalledduring:

ALTERDATABASEMOUNT...

ORA-214signalledduring:

alterdatabasemount...

ORA-1113signalledduring:

ALTERDATABASEOPEN...

ORA-01157:

cannotidentify/lockdatafile3-seeDBWRtracefile

ORA-01110:

datafile3:

'/opt/oracle/oradata/eygle/eygle02.dbf'

ORA-27037:

unabletoobtainfilestatus

ORA-1113signalledduring:

ALTERDATABASEOPEN...

ORA-01157:

cannotidentify/lockdatafile3-seeDBWRtracefile

ORA-01110:

datafile3:

'/opt/oracle/oradata/eygle/eygle02.dbf'

ORA-27037:

unabletoobtainfilestatus

ORA-1113signalledduring:

alterdatabaseopen...

TEXT

-----------------------------------------------------------------------------------

ORA-01157:

cannotidentify/lockdatafile3-seeDBWRtracefile

ORA-01110:

datafile3:

'/opt/oracle/oradata/eygle/eygle02.dbf'

ORA-27037:

unabletoobtainfilestatus

ORA-283signalledduring:

ALTERDATABASERECOVERdatabase...

O