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

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

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

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

　　首先需要创建一个Directory

[oracle@jumper oracle]$ sqlplus / as sysdba

　　SQL*Plus: Release Production on Sun Oct : :

　　Copyright (c) Oracle Corporation All rights reserved

　　Connected to:Oracle i Enterprise Edition Release ProductionWith the Partitioning optionJServer Release Production

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

　　Directory created

　　SQL> col DIRECTORY_PATH for a SQL> col owner for a SQL> select * from dba_directories;

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

　　然后创建一个外部表:

SQL> create table alert_log ( text varchar ( ) ) anization external ( type oracle_loader default directory BDUMP access parameters ( records delimited by newline nobadfile nodiscardfile nologfile ) location( alert_eygle log ) ) reject limit unlimited /

　　Table created

然后我们就可以通过外部表进行查询警告日志的内容: SQL> select * from alert_log where rownum < ;

　　TEXT Mon Jun : : Starting ORACLE instance (normal)Mon Jun : : WARNING: EINVAL creating segment of size x c fix shm parameters in /etc/system or equivalentLICENSE_MAX_SESSION = LICENSE_SESSIONS_WARNING = SCN scheme Using log_archive_dest parameter default valueLICENSE_MAX_USERS = SYS auditing is disabledStarting up ORACLE RDBMS Version: System parameters with non default values:processes = timed_statistics = TRUEshared_pool_size = large_pool_size = java_pool_size = control_files = /opt/oracle/oradata/eygle/control ctldb_block_size = db_cache_size = db_cache_advice = ONpatible = db_file_multiblock_read_count= fast_start_mttr_target = log_checkpoints_to_alert = TRUEundo_management = AUTOundo_tablespace = UNDOTBS undo_retention = remote_login_passwordfile= EXCLUSIVEdb_domain =instance_name = eyglejob_queue_processes = hash_join_enabled = TRUEbackground_dump_dest = /opt/oracle/admin/eygle/bdumpuser_dump_dest = /opt/oracle/admin/eygle/udumpcore_dump_dest = /opt/oracle/admin/eygle/cdumpsort_area_size = db_name = eygleopen_cursors = star_transformation_enabled= FALSEquery_rewrite_enabled = FALSEpga_aggregate_target = aq_tm_processes = PMON started with pid= DBW started with pid= LGWR started with pid= CKPT started with pid= SMON started with pid= RECO started with pid=

　　 rows selected

　　SQL>

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

SQL> select * from alert_log where text like ORA % ;

　　TEXT ORA : unable to extend temp segment by in tablespace TEMPORA signalled during: alter database open ORA signalled during: alter database datafile online ORA : scumnt: unable to lock fileORA signalled during: ALTER DATABASE MOUNT ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/users dbf ORA : unable to obtain file statusORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/users dbf

　　TEXT ORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/eygle dbf ORA : unable to obtain file statusORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/eygle dbf ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/eygle dbf ORA : unable to obtain file statusORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/eygle dbf

　　TEXT ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA signalled during: alter database open ORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/eygle dbf ORA : unable to obtain file statusORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/eygle dbf ORA signalled during: alter database open ORA signalled during: alter database open ORA signalled during: ALTER DATABASE RECOVER database ORA signalled during: alter database open

　　TEXT ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA signalled during: ALTER DATABASE OPEN ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA signalled during: ALTER DATABASE OPEN ORA signalled during: CREATE CONTROLFILE REUSE DATABASE EYGLE NORESETL ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA signalled during: ALTER DATABASE MOUNT ORA : data file : /opt/oracle/oradata/eygle/eygle dbf ORA : IO error reading block from file (block # )ORA : skgfdisp: attempt to do I/O beyond the range of the fileORA : database file failed verification check

　　TEXT ORA : data file : /opt/oracle/oradata/eygle/system dbf ORA : file is more recent than controlfile old controlfileORA signalled during: alter database open ORA signalled during: ALTER DATABASE RECOVER database using backup cont ORA : data file : /opt/oracle/oradata/eygle/eygle dbf ORA : IO error reading block from file (block # )ORA : skgfdisp: attempt to do I/O beyond the range of the fileORA : file needs more recovery to be consistentORA : data file : /opt/oracle/oradata/eygle/system dbf ORA signalled during: alter database open resetlogs ORA signalled during: ALTER DATABASE RECOVER datafile

　　TEXT ORA signalled during: ALTER DATABASE RECOVER database using backup cont ORA : data file : /opt/oracle/oradata/eygle/eygle dbf ORA : IO error reading block from file (block # )ORA : skgfdisp: attempt to do I/O beyond the range of the fileORA : file needs more recovery to be consistentORA : data file : /opt/oracle/oradata/eygle/system dbf ORA signalled during: alter database open resetlogs ORA signalled during: ALTER DATABASE RECOVER datafile ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA signalled during: ALTER DATABASE OPEN ORA : data file : /opt/oracle/oradata/eygle/eygle dbf

　　TEXT ORA : IO error reading block from file (block # )ORA : skgfdisp: attempt to do I/O beyond the range of the fileORA : file needs more recovery to be consistentORA : data file : /opt/oracle/oradata/eygle/system dbf ORA signalled during: alter database open resetlogs ORA signalled during: alter database close ORA signalled during: CREATE CONTROLFILE REUSE DATABASE EYGLE NORESETL ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA signalled during: alter database open ORA : controlfile: /opt/oracle/oradata/eygle/control ctl ORA : unable to obtain file status

　　TEXT ORA signalled during: ALTER DATABASE MOUNT ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA : CREATE DATABASE failedORA : error in opening file ?/rdbms/admin/sql bsq ORA : sftopn: fopen errorORA : error in opening file ORA signalled during: CREATE DATABASE eygleORA signalled during: ALTER DATABASE MOUNT ORA signalled during: alter database open ORA signalled during: alter database mount ORA signalled during: ALTER DATABASE CLOSE NORMAL

　　TEXT ORA signalled during: ALTER DATABASE MOUNT ORA signalled during: alter database mount ORA signalled during: ALTER DATABASE OPEN ORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/eygle dbf ORA : unable to obtain file statusORA signalled during: ALTER DATABASE OPEN ORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/eygle dbf ORA : unable to obtain file statusORA signalled during: alter database open

　　TEXT ORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/eygle dbf ORA : unable to obtain file statusORA signalled during: ALTER DATABASE RECOVER database ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA signalled during: alter database mount ORA signalled during: alter database create datafile /opt/oracle/produc ORA signalled during: alter database create datafile /opt/oracle/oradat ORA signalled during: ALTER DATABASE MOUNT ORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/eygle dbf

　　TEXT ORA : unable to obtain file statusORA signalled during: ALTER DATABASE RECOVER database ORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/eygle dbf ORA : unable to obtain file statusORA signalled during: ALTER DATABASE RECOVER database using backup cont ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA signalled during: ALTER DATABASE MOUNT ORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/users dbf ORA : unable to obtain file status

　　TEXT ORA signalled during: ALTER DATABASE RECOVER database ORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/users dbf ORA : unable to obtain file statusORA signalled during: ALTER DATABASE RECOVER database using backup cont ORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/users dbf ORA : unable to obtain file statusORA signalled during: ALTER DATABASE RECOVER database using backup cont ORA signalled during: ALTER DATABASE RECOVER database using backup cont ORA signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT

　　TEXT ORA signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ORA signalled during: ALTER DATABASE RECOVER CANCEL ORA signalled during: alter database open ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA signalled during: CREATE CONTROLFILE REUSE DATABASE EYGLE NORESETL ORA signalled during: alter database create datafile /opt/oracle/produc ORA signalled during: ALTER DATABASE MOUNT ORA : cannot identify/lock data file see DBWR trace fileORA : data file : /opt/oracle/oradata/eygle/eygle dbf ORA : unable to obtain file statusORA signalled during: alter database open

　　TEXT ORA signalled during: alter database open ORA signalled during: ALTER DATABASE MOUNT ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA signalled during: alter database open ORA signalled during: ALTER TABLESPACE TEMP DEFAULT STORAGE (INITIAL M ORA signalled during: alter database close ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA signalled during: alter database close normal ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA signalled during: alter database dismount ORA signalled during: alter database open

　　TEXT ORA signalled during: alter database open ORA signalled during: alter database open ORA signalled during: alter database open ORA signalled during: alter database open ORA signalled during: alter database close ORA signalled during: alter database close ORA signalled during: ALTER DATABASE CLOSE NORMAL ORA signalled during: alter database add logfile group ORA signalled during: alter database drop logfile group

　　 rows selected

　　SQL>

lishixinzhi/Article/program/Oracle/201311/17897