Oracleのアラートログ、リスナーログをSQLでみる


# やりたいこと

Oracleのアラートログやリスナーログの内容を出力時刻とともにSQL*Plusで確認します。



# アラートログをSQLでみる

`x$dbgalertext`または`x$diag_alert_ext`にて確認することができます。


## 直近2日のログの内容を表示

```
select distinct originating_timestamp,
       message_text 
  from x$dbgalertext 
 where originating_timestamp > sysdate-2
order by originating_timestamp ;
```

結果例

```
ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
06-OCT-16 02.00.00.264 PM +09:00
Setting Resource Manager plan SCHEDULER[0x4443]:DEFAULT_MAINTENANCE_PLAN via sch
eduler window

以下略
````





## ORA-を含むログを表示

```
--ORA-を出力
exec dbms_system.ksdwrt(2, 'ORA-000 HOGEHOGE');


select distinct originating_timestamp,
       message_text 
  from x$dbgalertext 
 where originating_timestamp > sysdate-2
   and message_text like '%ORA-%'
 order by originating_timestamp ;
```

結果例

```
ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
08-OCT-16 12.39.12.161 PM +09:00
ORA-000 HOGEHOGE
```

以下のSQLでも同等の結果となる。

 ```
select distinct originating_timestamp,
       message_text 
  from sys.x$diag_alert_ext
 where component_id like '%rdbms%'
   and originating_timestamp > sysdate-2
   and message_text like '%ORA-%'
 order by originating_timestamp ;
 ```
 


# リスナーログをSQLでみる


## 直近2日のリスナーログの出力

```
select distinct originating_timestamp,
       message_text 
  from sys.x$diag_alert_ext
 where component_id like '%tnslsnr%'
   and originating_timestamp > sysdate-2
 order by originating_timestamp;
```

結果例

```
ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
06-OCT-16 12.53.45.295000000 PM +09:00
06-OCT-2016 12:53:45 * service_update * ORCL * 0
```


# 表の定義

CON_IDが含まれるので、マルチテナントではコンテナ別の出力も可能。

```
SQL> desc x$dbgalertext
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_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(550)
 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(512)
 SUPPLEMENTAL_ATTRIBUTES                            VARCHAR2(512)
 SUPPLEMENTAL_DETAILS                               VARCHAR2(4000)
 PARTITION                                          NUMBER
 RECORD_ID                                          NUMBER
 
 
 SQL> desc X$DIAG_ALERT_EXT
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_ID                                             NUMBER
 ADR_PATH_IDX                                       VARCHAR2(445)
 ADR_HOME                                           VARCHAR2(445)
 ORIGINATING_TIMESTAMP                              TIMESTAMP(9) WITH TIME ZONE
 NORMALIZED_TIMESTAMP                               TIMESTAMP(9) WITH TIME ZONE
 ORGANIZATION_ID                                    VARCHAR2(67)
 COMPONENT_ID                                       VARCHAR2(67)
 HOST_ID                                            VARCHAR2(67)
 HOST_ADDRESS                                       VARCHAR2(49)
 MESSAGE_TYPE                                       NUMBER
 MESSAGE_LEVEL                                      NUMBER
 MESSAGE_ID                                         VARCHAR2(67)
 MESSAGE_GROUP                                      VARCHAR2(67)
 CLIENT_ID                                          VARCHAR2(67)
 MODULE_ID                                          VARCHAR2(67)
 PROCESS_ID                                         VARCHAR2(35)
 THREAD_ID                                          VARCHAR2(67)
 USER_ID                                            VARCHAR2(67)
 INSTANCE_ID                                        VARCHAR2(67)
 DETAILED_LOCATION                                  VARCHAR2(163)
 UPSTREAM_COMP_ID                                   VARCHAR2(103)
 DOWNSTREAM_COMP_ID                                 VARCHAR2(103)
 EXECUTION_CONTEXT_ID                               VARCHAR2(103)
 EXECUTION_CONTEXT_SEQUENCE                         NUMBER
 ERROR_INSTANCE_ID                                  NUMBER
 ERROR_INSTANCE_SEQUENCE                            NUMBER
 MESSAGE_TEXT                                       VARCHAR2(2051)
 MESSAGE_ARGUMENTS                                  VARCHAR2(515)
 SUPPLEMENTAL_ATTRIBUTES                            VARCHAR2(515)
 SUPPLEMENTAL_DETAILS                               VARCHAR2(515)
 PARTITION                                          NUMBER
 RECORD_ID                                          NUMBER
 FILENAME                                           VARCHAR2(515)
 LOG_NAME                                           VARCHAR2(67)
 PROBLEM_KEY                                        VARCHAR2(553)
 VERSION                                            NUMBER

 ```

# 参考

http://www.dba-oracle.com/t_11g_dbgalertext_alert_log_sql.htm

https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.Oracle.html