用oracle的ADRCI功能查看日志

  • jane_pop

    2014-09-06 21:53:04
  • Oracle
  • 原创
oracle11g中提供了一个查看日志文件的命令行工具：Automatic Diagnostic Repository Command-Interpreter,包含在oracle database 11g中引入的故障可诊断性基础结构中，主要用于管理trace文件，alert文件，dump文件，健康监视报告等。在oracle10g中 这些文件时存放在ORACLE_BASE/admin/INSTANCE_NAME目录下的，到了oracle11g这些文件存放在ORACLE_BASE/diag/rdbms/DBNAME/INSTANCE_NAME目录下，oracle之所以修改这个跨越多个版本都没有修改过的参数，是由于oracle11g退出了ADRCI工具，这个工具可以统一管理ASM实例和多个数据库实例的alert文件，用户trace文件，后台trace文件，dump文件等。使用ADRCI，可以：
(1)查看自动诊断资料档案库ADR中的诊断数据。
(2)将意外事件和问题信息打包成zip文件，以传输到oracle技术支持，此操作是使用称为意外事件打包服务Incident Packaging Service ,IPS的服务完成的。
在数据库中查询v$diag_info视图：
SYS@orcl 06-SEP-14>select name,value from v$diag_info;
NAME                      VALUE
------------------------- ----------------------------------------
Diag Enabled              TRUE
ADR Base                  /u01/app/oracle
ADR Home                /u01/app/oracle/diag/rdbms/orcl/orcl
Diag Trace                /u01/app/oracle/diag/rdbms/orcl/orcl/tra
                               ce


Diag Alert                /u01/app/oracle/diag/rdbms/orcl/orcl/ale
                              rt


Diag Incident          /u01/app/oracle/diag/rdbms/orcl/orcl/inc
                             ident


NAME                      VALUE
------------------------- ----------------------------------------


Diag Cdump         /u01/app/oracle/diag/rdbms/orcl/orcl/cdu
                          mp


Health Monitor          /u01/app/oracle/diag/rdbms/orcl/orcl/hm
Default Trace File     /u01/app/oracle/diag/rdbms/orcl/orcl/tra
                             ce/orcl_ora_27646.trc


Active Problem Count       0
Active Incident Count       0

11 rows selected.

打开终端，执行adrci即可及进入ADRCI工具：
[oracle@localhost ~]$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Sat Sep 6 02:58:47 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> help

HELP [topic]
Available Topics:
        CREATE REPORT
        ECHO
        EXIT
        HELP
        HOST
        IPS
        PURGE
        RUN
        SET BASE
        SET BROWSER
        SET CONTROL
        SET ECHO
        SET EDITOR
        SET HOMES | HOME | HOMEPATH
        SET TERMOUT
        SHOW ALERT
        SHOW BASE
        SHOW CONTROL
        SHOW HM_RUN
        SHOW HOMES | HOME | HOMEPATH
        SHOW INCDIR
        SHOW INCIDENT
        SHOW PROBLEM
        SHOW REPORT
        SHOW TRACEFILE
        SPOOL

There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list

可以通过show alert命令查看警报日志文件，默认使用vi编辑器模式：
2014-08-21 18:50:52.038000 -07:00
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 USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
2014-08-21 18:50:56.360000 -07:00
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in client-side pfile /u01/app/oracle/admin/orcl/pfile/init.ora on machine localhost.localdomain
System parameters with non-default values:
  processes                = 150
  memory_target            = 812M
  control_files            = "/u01/app/oracle/oradata/orcl/control01.ctl"
显示最近5条alert log(不是5行，默认是10条)：
使用这个命令首先要set home,否则会报错DIA-48449:
adrci> show alert -tail 5
DIA-48449: Tail alert can only apply to single ADR home
这是因为在多个实例存在的情况下易造成混乱。
adrci> set home diag/rdbms/orcl/orcl
adrci> show alert -tail 5
2014-09-06 05:09:26.725000 -07:00
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Completed: ALTER DATABASE OPEN
Starting background process CJQ0
CJQ0 started with pid=28, OS id=8235 
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
2014-09-06 05:14:26.719000 -07:00
Starting background process SMCO
SMCO started with pid=36, OS id=8861 


通过show tracefile可以查看ADR下的所有trace files:
adrci>show tracefile
     diag/rdbms/orcl/orcl/trace/orcl_ora_14542.trc
     diag/rdbms/orcl/orcl/trace/orcl_ora_13410.trc
     diag/rdbms/orcl/orcl/trace/orcl_ora_26142.trc
     diag/rdbms/orcl/orcl/trace/orcl_dbrm_12336.trc
     diag/rdbms/orcl/orcl/trace/orcl_ora_22490.trc
     diag/rdbms/orcl/orcl/trace/orcl_arc0_19981.trc
     diag/rdbms/orcl/orcl/trace/orcl_ora_23694.trc
     diag/rdbms/orcl/orcl/trace/orcl_ora_9319.trc
     diag/rdbms/orcl/orcl/trace/orcl_m000_21885.trc
     diag/rdbms/orcl/orcl/trace/orcl_m000_29429.trc
     diag/rdbms/orcl/orcl/trace/orcl_mman_2679.trc
     diag/rdbms/orcl/orcl/trace/orcl_m001_19671.trc
     diag/rdbms/orcl/orcl/trace/orcl_ora_3424.trc
     diag/rdbms/orcl/orcl/trace/orcl_ora_17550.trc
     diag/rdbms/orcl/orcl/trace/orcl_m002_27131.trc
     diag/rdbms/orcl/orcl/trace/orcl_m000_21350.trc
     diag/rdbms/orcl/orcl/trace/orcl_ora_13162.trc
     diag/rdbms/orcl/orcl/trace/orcl_ckpt_4808.trc

也可以对这些trace files文件进行过滤，只看你所关注的信息：
adrci> show tracefile %mmon%
     diag/rdbms/orcl/orcl/trace/orcl_mmon_27960.trc
     diag/rdbms/orcl/orcl/trace/orcl_mmon_20946.trc
     diag/rdbms/orcl/orcl/trace/orcl_mmon_15962.trc
     diag/rdbms/orcl/orcl/trace/orcl_mmon_28628.trc
     diag/rdbms/orcl/orcl/trace/orcl_mmon_11531.trc
     diag/rdbms/orcl/orcl/trace/orcl_mmon_16814.trc
     diag/rdbms/orcl/orcl/trace/orcl_mmon_10029.trc
     diag/rdbms/orcl/orcl/trace/orcl_mmon_16492.trc
     diag/rdbms/orcl/orcl/trace/orcl_mmon_30359.trc

通过show incident命令查看incident:
adrci> show incident


ADR Home = /u01/app/oracle/diag/tnslsnr/localhost/listener:
*************************************************************************
0 rows fetched


ADR Home = /u01/app/oracle/diag/rdbms/dummy/orcl:
*************************************************************************
0 rows fetched


ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
0 rows fetched

通过show problem查看problem：
adrci> show problem


ADR Home = /u01/app/oracle/diag/tnslsnr/localhost/listener:
*************************************************************************
0 rows fetched


ADR Home = /u01/app/oracle/diag/rdbms/dummy/orcl:
*************************************************************************
0 rows fetched


ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
0 rows fetched

ADRCI的purge命令会清空alert log的内容，注意这里的内容仅仅是XML的alert log(XML类型的警报日志在diag/rdbms/orcl/alert
目录下，名称为log.xml)
对于trace目录下的text格式的alert log不会进行清空，我们在使用purge时，可以指定
alert log中的entries保留的时间。
如清除10分钟前的alert log，可用如下命令：
adrci> purge -age 10 -type alert
adrci> show alert
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************

但是diag/rdbms/orcl/orcl/trace目录下，alert_orcl.log警报日志文件内容还是存在的。
可用如下命令查看ADR中日志的保留时间：
adrci> show control
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME                              
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 
1335663986           720                  8760                 2014-08-21 18:50:50.266259 -07:00                                                 2014-09-06 05:56:58.068378 -07:00        1                    2                    76                   1                    2014-08-21 18:50:50.266259 -07:00       
1 rows fetched

ADR中有两个策略，SHORTP_POLICY和LONGP_POLICY。
SHORT_POLICY:number of hours after which to purge ADR contents that have a short life.Default 
is 720(30 days).A setting of 0 means that all contents that have a short lift can be purged.The 
maxnimum setting is 35791394.If a value greater than 35791394 is specified , then this attribute is 
set to 0.
The ADR contents that have a short life include the following:
trace files
core dump files
packaging information

LONG_POLICY:Number of hours after which to purge ADR contents that have a log life.Default
is 8760(365 days).A setting of 0 means that all contents that have a long life can be purged.The
maxnimum setting is 35791394.If a value greater than 35791394 is specified ,then this attribute
is set to 0.
The ADR contents that have a long life include the following:
incident information
incident dumps
alert logs
SHORTP_POLICY默认是720小时(30天)，其控制如下三种文件的保留时间：
trace file
core dump files
packaging information
LONGP_POLICY默认是8760小时(365天)，其控制如下三种文件的保留时间：
incident information 
incident information
alert logs
可以用如下命令修改ADR的保留时间
adrci> set control (SHORTP_POLICY=360)
adrci> set control (LONGP_POLICY=2160)
adrci> show control


ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME                              
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 
1335663986           360                  2160                 2014-09-06 06:24:21.877124 -07:00                                                 2014-09-06 05:56:58.068378 -07:00        1                    2                    76                   1                    2014-08-21 18:50:50.266259 -07:00       
1 rows fetched

通过ADR生成诊断包：
adrci> ips create package
Created package 1 without any contents, correlation level typical
adrci> ips generate package 1 in /home/oracle
Generated package 1 in file /home/oracle/IPSPKG_20140906062831_COM_1.zip, mode complete

[oracle@localhost alert]$ cd /home/oracle
[oracle@localhost ~]$ ls
Desktop  help.sql  IPSPKG_20140906062831_COM_1.zip  oradiag_oracle

[oracle@localhost ~]$ unzip -l IPSPKG_20140906062831_COM_1.zip 
Archive:  IPSPKG_20140906062831_COM_1.zip
  Length     Date   Time    Name
 --------    ----   ----    ----
     2818  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/IPS_CONFIGURATION.dmp
      460  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/IPS_PACKAGE.dmp
      151  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/IPS_PACKAGE_INCIDENT.dmp
      870  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/IPS_PACKAGE_FILE.dmp
      280  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/IPS_PACKAGE_HISTORY.dmp
     2254  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/IPS_FILE_METADATA.dmp
      214  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/IPS_FILE_COPY_LOG.dmp
      908  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/DDE_USER_ACTION_DEF.dmp
      708  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/DDE_USER_ACTION_PARAMETER_DEF.dmp
      204  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/DDE_USER_ACTION.dmp
      198  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/DDE_USER_ACTION_PARAMETER.dmp
      353  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/DDE_USER_INCIDENT_TYPE.dmp
      166  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/DDE_USER_INCIDENT_ACTION_MAP.dmp
      576  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/INCIDENT.dmp
      148  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/INCCKEY.dmp
      119  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/INCIDENT_FILE.dmp
      292  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/PROBLEM.dmp
      536  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/HM_RUN.dmp
      207  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/export/EM_USER_ACTIVITY.dmp
     3900  09-06-14 06:00   diag/rdbms/orcl/orcl/alert/log.xml
   604428  09-06-14 06:00   diag/rdbms/orcl/orcl/trace/alert_orcl.log
     1453  09-06-14 06:29   diag/rdbms/orcl/orcl/hm/HMREPORT_HM_RUN_3301.hm
      184  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/crs/crsdiag.log
      527  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/metadata.xml
     8059  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/manifest_1_1.xml
     9092  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/manifest_1_1.html
    10506  09-06-14 06:29   diag/rdbms/orcl/orcl/incpkg/pkg_1/seq_1/manifest_1_1.txt
      527  09-06-14 06:29   metadata.xml
 --------                   -------
   650138                   28 files
请使用浏览器的分享功能分享到微信等