Chinaunix首页 | 论坛 | 博客
  • 博客访问: 617552
  • 博文数量: 28
  • 博客积分: 6060
  • 博客等级: 准将
  • 技术积分: 1948
  • 用 户 组: 普通用户
  • 注册时间: 2006-08-03 08:55
文章分类

全部博文(28)

文章存档

2011年(3)

2009年(9)

2008年(16)

我的朋友

分类: Oracle

2009-03-23 13:50:02

在11g中,oracle对数据库的诊断结果进行了一些改进,下面将讨论通过sql语句获取警告日志信息:
在oracle11g以前,如果需要通过sql语句来读取警告日志,一般通过外部表或者使用utl_file管道函数方式。在读取文本信息之后需要解析然后导出自己需要的信息。
从11g开始,oracle已经替你做了这些工作。它提供了一个固态表x$dbgalertext,当你查询的时候oracle从alert目录读取log.xml(包含警告日志中的所有信息),解析之,然后按行返回给用户:
SQL> set pagesize 9999
SQL> select message_text from x$dbgalertext where rownum<=20;
MESSAGE_TEXT
--------------------------------------------------------------------------------
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as D:\app\hongsy\product\11.1.0\db_1\RDBMS
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in client-side pfile D:\APP\HONGSY\ADMIN\ORCL\PFILE\INIT.ORA on machine HH
System parameters with non-default values:
  processes                = 150
  nls_language             = "SIMPLIFIED CHINESE"
  nls_territory            = "CHINA"
  sga_target               = 460M
  control_files            = "D:\APP\HONGSY\ORCL\CONTROL01.CTL"
20 rows selected.
以下是x$dbgalertext的字段信息:
SQL> desc X$DBGALERTEXT
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(4)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 ORIGINATING_TIMESTAMP                              TIMESTAMP(3) WITH TIME ZONE
 NORMALIZED_TIMESTAMP                               TIMESTAMP(3) WITH TIME ZONE
 ORGANIZATION_ID                                    VARCHAR2(64)
 COMPONENT_ID                                       VARCHAR2(64)
 HOST_ID                                            VARCHAR2(64)
 HOST_ADDRESS                                       VARCHAR2(16)
 MESSAGE_TYPE                                       NUMBER
 MESSAGE_LEVEL                                      NUMBER
 MESSAGE_ID                                         VARCHAR2(64)
 MESSAGE_GROUP                                      VARCHAR2(64)
 CLIENT_ID                                          VARCHAR2(64)
 MODULE_ID                                          VARCHAR2(64)
 PROCESS_ID                                         VARCHAR2(32)
 THREAD_ID                                          VARCHAR2(64)
 USER_ID                                            VARCHAR2(64)
 INSTANCE_ID                                        VARCHAR2(64)
 DETAILED_LOCATION                                  VARCHAR2(160)
 PROBLEM_KEY                                        VARCHAR2(64)
 UPSTREAM_COMP_ID                                   VARCHAR2(100)
 DOWNSTREAM_COMP_ID                                 VARCHAR2(100)
 EXECUTION_CONTEXT_ID                               VARCHAR2(100)
 EXECUTION_CONTEXT_SEQUENCE                         NUMBER
 ERROR_INSTANCE_ID                                  NUMBER
 ERROR_INSTANCE_SEQUENCE                            NUMBER
 VERSION                                            NUMBER
 MESSAGE_TEXT                                       VARCHAR2(2048)
 MESSAGE_ARGUMENTS                                  VARCHAR2(128)
 SUPPLEMENTAL_ATTRIBUTES                            VARCHAR2(128)
 SUPPLEMENTAL_DETAILS                               VARCHAR2(128)
 PARTITION                                          NUMBER
 RECORD_ID                                          NUMBER
这里同样有个固态表X$DBGDIREXT,它包含diag目录下所有文件和目录信息:
SQL> select lpad(' ',lvl,' ')||logical_file file_name
  2  from X$DBGDIREXT
  3  where rownum <=20;
FILE_NAME
--------------------------------------------------------------------------------
asm
clients
 user_hongsy
  host_2986972688_11
   alert
    log.xml
   cdump
   incident
   incpkg
   lck
    am_1096102193_3488045378.lck
    am_1096102262_3454819329.lck
    am_3216668543_3129272988.lck
   metadata
    adr_control.ams
    inc_meter_impt_def.ams
    inc_meter_pk_impts.ams
   stage
   sweep
   trace
20 rows selected.
如果你自己构建了一些监控警告日志的程序,那么从11g开始,这些X$表将会帮助你。这对没有操作系统用户权限时,可以通过数据库权限来查阅。但是如果数据库实例没有运行的话,将不能使用此方式来查看警告日志信息了。
阅读(4938) | 评论(0) | 转发(2) |
给主人留下些什么吧!~~