やりたいこと
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