Adrci And X$DBGALERTEXT

最新推荐文章于 2021-04-06 00:24:46 发布

最新推荐文章于 2021-04-06 00:24:46 发布 · 197 阅读

文章标签：

#数据库 #测试

本文介绍Oracle11g中ADRCI工具的使用方法，包括查看告警日志、问题和事件信息等。并通过实例展示了如何利用ADRCI进行数据库问题诊断。

oracle 11g开始提供的命令行工具，可以查看数据库问题和相关事件，可以打包包括获取跟踪文件信息，数据库告警日志的详细信息以及其他配置/测试用例信息.
首先来看看Adrci的使用：
使用help可以获得帮助手册.

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

使用ADRCI查看告警日志

adrci> show homes 
ADR Homes: 
diag/rdbms/leo11/leo11

adrci>show alert -tail

adrci> show alert -p "message_text like '%7445%'"

Choose the alert log from the following homes to view:

1: diag/rdbms/leo11/leo11
2: diag/tnslsnr/localhost/listener1
3: diag/tnslsnr/localhost/listener
Q: to quit

Please select option: 1
Output the results to file: /tmp/alert_4753_30863_leo11_1.ado

2012-03-17 07:53:16.484000 +08:00
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9477.trc  (incident=137):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
2012-03-17 07:55:26.425000 +08:00
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9591.trc  (incident=1338):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
2012-03-17 07:55:56.760000 +08:00
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9638.trc  (incident=1362):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
2012-03-17 07:56:12.950000 +08:00
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9664.trc  (incident=1363):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
2012-03-17 07:57:12.955000 +08:00
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9702.trc  (incident=1364):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
2012-03-17 07:58:13.414000 +08:00
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9731.trc  (incident=1354):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
2012-03-17 07:59:16.517000 +08:00

问题和意外事件

drci> show problem 

ADR Home = /opt/app/oracle/diag/rdbms/leo11/leo11:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                             
-------------------- ----------------------------------------------------------- -------------------- ---------------------------------------- 
1                    ORA 7445 [nttaddr2bnd()+2284]                               30918                2012-05-11 12:04:51.473000 +08:00       
1 rows fetched

通过show incident可以看到那些方面受这个错误的影响：

adrci> show incident


ADR Home = /opt/app/oracle/diag/rdbms/leo11/leo11:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME                              
-------------------- ----------------------------------------------------------- ---------------------------------------- 
26715                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-17 20:24:27.317000 +08:00       
26716                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-17 20:25:27.761000 +08:00       
26717                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-17 20:26:28.374000 +08:00       
26718                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-17 20:27:28.661000 +08:00       
26719                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-17 20:28:29.102000 +08:00       
28308                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-19 08:01:48.673000 +08:00       
28380                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-19 08:02:23.557000 +08:00       
28381                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-19 08:03:22.846000 +08:00       
28382                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-19 08:04:23.246000 +08:00       
28383                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-19 08:05:23.523000 +08:00       
29573                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-20 08:11:55.732000 +08:00       
29574                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-20 08:12:56.193000 +08:00       
29575                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-20 08:13:56.659000 +08:00       
29576                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-20 08:14:57.401000 +08:00       
29577                ORA 7445 [nttaddr2bnd()+2284]                               2012-04-20 08:15:57.831000 +08:00       
29817                ORA 7445 [nttaddr2bnd()+2284]                               2012-05-11 11:20:47.600000 +08:00       
29897                ORA 7445 [nttaddr2bnd()+2284]                               2012-05-11 11:21:34.716000 +08:00       
29898                ORA 7445 [nttaddr2bnd()+2284]                               2012-05-11 11:22:33.661000 +08:00       
29899                ORA 7445 [nttaddr2bnd()+2284]                               2012-05-11 11:23:34.013000 +08:00       
29900                ORA 7445 [nttaddr2bnd()+2284]                               2012-05-11 11:24:34.502000 +08:00       
20 rows fetched

我们可以通过show tracefile命令得出与各个事件相关的信息：

adrci> show tracefile -I 29900
     diag/rdbms/leo11/leo11/incident/incdir_29900/leo11_d000_4663_i29900.trc

adrci> show incident -mode detail -p "incident_id=29900"

ADR Home = /opt/app/oracle/diag/rdbms/leo11/leo11:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
   INCIDENT_ID                   29900
   STATUS                        ready
   CREATE_TIME                   2012-05-11 11:24:34.502000 +08:00
   PROBLEM_ID                    1
   CLOSE_TIME                    <NULL>
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  7445
   ERROR_ARG1                    nttaddr2bnd()+2284
   ERROR_ARG2                    SIGSEGV
   ERROR_ARG3                    ADDR:0x0
   ERROR_ARG4                    PC:0xA77C190
   ERROR_ARG5                    Address not mapped to object
   ERROR_ARG6                    <NULL>
   ERROR_ARG7                    <NULL>
   ERROR_ARG8                    <NULL>
   ERROR_ARG9                    <NULL>
   ERROR_ARG10                   <NULL>
   ERROR_ARG11                   <NULL>
   ERROR_ARG12                   <NULL>
   SIGNALLING_COMPONENT          <NULL>
   SIGNALLING_SUBCOMPONENT       <NULL>
   SUSPECT_COMPONENT             <NULL>
   SUSPECT_SUBCOMPONENT          <NULL>
   ECID                          <NULL>
   IMPACTS                       0
   PROBLEM_KEY                   ORA 7445 [nttaddr2bnd()+2284]
   FIRST_INCIDENT                137
   FIRSTINC_TIME                 2012-03-17 07:53:16.484000 +08:00
   LAST_INCIDENT                 30921
   LASTINC_TIME                  2012-05-11 12:07:52.810000 +08:00
   IMPACT1                       0
   IMPACT2                       0
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      Client ProcId
   KEY_VALUE                     oracle@localhost.4663_2418624
   KEY_NAME                      ProcId
   KEY_VALUE                     27.5
   OWNER_ID                      1
   INCIDENT_FILE                 /opt/app/oracle/diag/rdbms/leo11/leo11/incident/incdir_29900/leo11_d000_4663_i29900.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_4663.trc
1 rows fetched

意外事件打包服务adrci> ips create package incident 29900

另外还有一个视图很牛，X$DBGALERTEXT，11g 开始引入了新的ADR，默认的Alert.log转换为LOG.XML的形式，默认存放在$ADR_HOME/alert&trace目录下。

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(46)
 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_SEQUE          NUMBERNCE
 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

ORIGINATING_TIMESTAMP                                                       MESSAGE_GROUP                                                  PROBLEM_KEY
--------------------------------------------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------
MESSAGE_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
17-MAR-12 07.53.16.484 AM +08:00                                            Access Violation                                               ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9477.trc  (incident=137):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []

17-MAR-12 07.55.26.425 AM +08:00                                            Access Violation                                               ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9591.trc  (incident=1338):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []

17-MAR-12 07.55.56.760 AM +08:00                                            Access Violation                                               ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9638.trc  (incident=1362):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []

17-MAR-12 07.56.12.950 AM +08:00                                            Access Violation                                               ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9664.trc  (incident=1363):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []

17-MAR-12 07.57.12.955 AM +08:00                                            Access Violation                                               ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9702.trc  (incident=1364):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []

17-MAR-12 07.58.13.414 AM +08:00                                            Access Violation                                               ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9731.trc  (incident=1354):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []

17-MAR-12 07.59.16.517 AM +08:00                                            Access Violation                                               ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9809.trc  (incident=2542):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []

17-MAR-12 07.59.51.658 AM +08:00                                            Access Violation                                               ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9855.trc  (incident=2590):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []

17-MAR-12 08.00.00.506 AM +08:00                                            Access Violation                                               ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9869.trc  (incident=2591):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []

9 rows selected.
查看最近2天内管理员执行过的DDL语句:

SQL> l
  1  select originating_timestamp, message_text
  2      from X$DBGALERTEXT
  3      where originating_timestamp > sysdate - 2
  4*     and message_group = 'admin_ddl'
SQL> /

ORIGINATING_TIMESTAMP               MESSAGE_TEXT
----------------------------------- ----------------------------------------
11-MAY-12 11.20.49.402 AM +08:00    ALTER DATABASE   MOUNT
11-MAY-12 11.20.56.006 AM +08:00    Completed: ALTER DATABASE   MOUNT
11-MAY-12 11.20.56.412 AM +08:00    ALTER DATABASE OPEN
11-MAY-12 11.21.26.701 AM +08:00    Completed: ALTER DATABASE OPEN