Oracle 使用外部表访问警告日志或监听日志

原创 eygle 2019-12-05

1015

一、使用外部表访问警告日志文件

对于DBA来说，最常见一个例子是可以使用外部表来访问警告日志文件（alert_.log）或其他跟踪文件；由于警告日志文件存放于服务器上，有时候如果不能直接访问操作系统文件，则同样可以通过外部表来访问警告日志文件。

以下通过一个例子用来说明外部表在数据库管理方面的实际用途。

1. 创建Directory

首先需要创建一个Directory，如果是普通数据库用户，那么就需要得到授权（CREATE ANY DIRECTORY的权限是必须的）：

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

2.创建及访问外部表

创建了目录之后，可以创建一个外部表，指向前面创建的目录，具体文件则指向警告日志文件：

1 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 <9;
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

8 rows selected.

如果需要查看数据库中曾经出现过的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'
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
………………

163 rows selected.

从Oracle 10g开始，通过Oracle提供的Web方式的EM，可以更方便地查询警告日志的内容，从首页主目录下面的相关链接部分，如图9-1所示，选择“预警日志内容”链接可以进入相关部分：

图1 主目录

缺省的会显示预警日志最后100,000字节的内容，我们也可以定义时间段进行查询，相当灵活，如图9-2所示。

图9-2 自定义搜索

二、使用外部表访问监听日志文件

监听器的日志文件对于数据库管理来说也是一个重要的参考，同样通过外部表也可以访问监听器的日志文件。

1. 定位监听器日志文件

监听器日志的缺省位置在$ORACLE_HOME/network/admin/log路径下，或者在监听器启动时，可以看到日志的路径：

C:\>lsnrctl start
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 02-1月 -2007 20:45:50
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
启动tnslsnr: 请稍候...

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
写入C:\oracle\10.2.0\network\log\listener.log的日志信息
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gqgai)(PORT=1521)))
………………………
命令执行成功

在监听器启动的情况下，通过lsnctl status也可以看到这个信息：

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 02-1月 -2007 20:46:06
Copyright (c) 1991, 2005, Oracle.  All rights reserved.

正在连接到 (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER 的 STATUS
------------------------
别名                      LISTENER
……
监听程序日志文件          C:\oracle\10.2.0\network\log\listener.log
监听端点概要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gqgai)(PORT=1521)))
监听程序不支持服务
命令执行成功

在UNIX/Linux环境下，同样可以通过类似的方法获得相关信息。

2.创建Directory

创建指向监听器日志目录的Directory：

SQL> create or replace directory LISTENER_LOG
  2  as '/opt/oracle/product/9.2.0/network/log'
  3  /

Directory created.
SQL> select * from dba_directories where DIRECTORY_NAME='LISTENER_LOG';
OWNER      DIRECTORY_NAME  DIRECTORY_PATH
---------- --------------- ----------------------------------------
SYS        LISTENER_LOG    /opt/oracle/product/9.2.0/network/log

3.创建外部表

创建指向监听器日志的外部表：

SQL> CREATE TABLE listener_log (text VARCHAR2(4000))
  2    ORGANIZATION EXTERNAL (
  3      TYPE oracle_loader
  4      DEFAULT DIRECTORY listener_log
  5      ACCESS PARAMETERS (
  6         RECORDS DELIMITED BY NEWLINE
  7         NOBADFILE
  8          NOLOGFILE
  9          NODISCARDFILE
 10      )
 11      LOCATION ('listener.log')
 12    )
 13    REJECT LIMIT UNLIMITED
 14  /

Table created.

4.查询外部表

现在就可以通过SQL查询和访问外部表的数据了：

SQL> set pagesize 99
SQL> select * from listener_log where rownum < 12;
TEXT
--------------------------------------------------------------------------------------
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 19-DEC-2005 13:05:07
Copyright (c) 1991
System parameter file is /opt/oracle/product/9.2.0/network/admin/listener.ora
Log messages written to /opt/oracle/product/9.2.0/network/log/listener.log
Trace information written to /opt/oracle/product/9.2.0/network/trace/listener.trc
Trace level is currently 0
Started with pid=20979
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.33.11)(PORT=1521)))
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
19-DEC-2005 13:05:07 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=jumper.hurray.com.cn)(USER=oracle))(COMMAND=status)(ARGUMENTS=
64)(SERVICE=LISTENER)(VERSION=153093120)) * status * 0

11 rows selected.

查询监听器的启动时间和次数：

SQL> select * from listener_log  
  2  where text like 'TNSLSNR%'
  3  /
TEXT
---------------------------------------------------------------------------
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 19-DEC-2005 13:05:07
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 18-JAN-2006 08:49:40
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 18-JAN-2006 08:50:31
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 18-APR-2006 11:33:03
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 24-APR-2006 16:33:48
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 08-MAY-2006 09:28:20
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 18-MAY-2006 15:26:41
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 27-JUN-2006 15:28:07
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 28-AUG-2006 14:38:58
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 19-SEP-2006 17:13:10
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 17-OCT-2006 11:13:47
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 20-OCT-2006 09:47:13
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 03-NOV-2006 08:47:09
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 03-JAN-2007 10:00:50

14 rows selected.

查询客户端用户都使用哪些应用访问数据库：

SQL> SELECT DISTINCT SUBSTR (text,
  2            INSTR (text, 'PROGRAM') + 8,
  3            INSTR (SUBSTR (text, INSTR (text, 'PROGRAM') + 8),')')- 1) PROGRAM
  4             FROM (SELECT *
  5                     FROM listener_log
  6                    WHERE text LIKE '%PROGRAM%')
  7  /

PROGRAM
------------------------------------------------------------------
C:\Program Files\Oracle\jre\1.1.8\bin\jrew.exe
C:\Program Files\PLSQL Developer\plsqldev.exe
C:\oracle\10.2.0\bin\sqlplus.exe

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者