巧用外部表訪問警告日志文件或跟蹤文件

從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>

使用Oracle的外部表訪問跟蹤文件
前面說過Oracle的外部表可以用來訪問警告日志文件,其實Oracle的外部表可以非常靈活的被使用。 通過Create Direc...查看完整版>>使用Oracle的外部表訪問跟蹤文件
 
使用Oracle的外部表查詢警告日志文件
從Oracle9i開始,Oracle的外部表技術(Oracle External Tables)被極大的增強,通過外部表訪問外部數據增強了Oracle數據庫和外部數據源進行數據交互的能力,對于數據倉庫和ETL來說,這些增強極大的方便了數據訪問。 ...查看完整版>>使用Oracle的外部表查詢警告日志文件
 
創建訪問非Oracle文件的外部表格
  Oracle9i能夠直接從操作系統的文件獲取數據,並讓其看起來就像是數據庫的內部表格一樣。     Oracle9i已經擴...查看完整版>>創建訪問非Oracle文件的外部表格
 
創建訪問非Oracle文件的外部表格
  Oracle9i能夠直接從操作系統的文件獲取數據,並讓其看起來就像是數據庫的內部表格一樣。  Oracle9i已經擴展了它與操作系統的接口,因此讓能夠任何類型的平面文件操作起來就好像這個文件是一個關系表一樣,這事...查看完整版>>創建訪問非Oracle文件的外部表格
 
跟蹤分析iis日志有助于關鍵詞排名提升
  作爲一個站長必須要學會分析iis日志,不會分析iis日志的算不上一個合格的seoer!確實如此,通過幾次的實踐讓我初步懂得了分析iis日志的重要性。通過對iis日志的分析發現,百度給我返回了大量的404狀態碼,這說明我...查看完整版>>跟蹤分析iis日志有助于關鍵詞排名提升
 
巧用外部dns解決域名所有權和管理權的問題
  大部分中小型企業網站都不是自己建設和維護的,通常都是找外面的專業網絡公司外包。但是依照當前市場規律,能生存3年以上的不到20%,一旦網絡公司倒閉,網站相關管理權限回收將是一件麻煩事情。( 也許對于我們專...查看完整版>>巧用外部dns解決域名所有權和管理權的問題
 
瑞星稱卡卡網恢複訪問 警告黑客團夥停止作惡
  7月27日消息,繼奇虎360發布消息“瑞星官網被黑客植入木馬,內含Firefox漏洞攻擊代碼”後,瑞星今日發表聲明,稱目前瑞星“雲安全”系統已成功截獲黑客團夥向“瑞星卡卡”網站植入木馬,並隨即對網站服務器進行修...查看完整版>>瑞星稱卡卡網恢複訪問 警告黑客團夥停止作惡
 
跟蹤訪問沖突AVs的位置
假如一個 AV 的起因源自于試圖訪問沒有初始化的類的屬性(或方法),在沒有多少代碼的情況下這是很輕易識別的。但另一方面假如有一大堆代碼時,或者 AV 出現在多次重複的循環體中的話,就相當有難度了。幸運的是,(...查看完整版>>跟蹤訪問沖突AVs的位置
 
NAT後無法在內網通過外部IP訪問內部服務的問題
  這是個理論問題,我們先從NAT講起:NAT有兩種基本類型,一種是SNAT(Source NAT),一種是DNAT(Dest. NAT).SNAT即源NAT是改變數據包的IP層中的源IP地址,一般是用來將不合法的IP外出請求轉換成合法的IP的外出請求...查看完整版>>NAT後無法在內網通過外部IP訪問內部服務的問題
 
 
回到王朝網路移動版首頁