Tag Archives: Fixed Table Views

Query alert log from sqlplus

It is not really a novum that you can directly query the alertlog from SQLPlus. Tanel Poder and others already have discussed this a while ago. Somehow I can never remember the name of the X$ view when I need it. So it is time to sum up the information a little bit.

SQL> DESC X$DBGALERTEXT
    Name                         NULL?    TYPE
    ---------------------------- -------- ---------------------------
 1  ADDR                                  RAW(8)
 2  INDX                                  NUMBER
 3  INST_ID                               NUMBER
 4  CON_ID                                NUMBER
 5  ORIGINATING_TIMESTAMP                 TIMESTAMP(3) WITH TIME ZONE
 6  NORMALIZED_TIMESTAMP                  TIMESTAMP(3) WITH TIME ZONE
 7  ORGANIZATION_ID                       VARCHAR2(64)
 8  COMPONENT_ID                          VARCHAR2(64)
 9  HOST_ID                               VARCHAR2(64)
10  HOST_ADDRESS                          VARCHAR2(46)
11  MESSAGE_TYPE                          NUMBER
12  MESSAGE_LEVEL                         NUMBER
13  MESSAGE_ID                            VARCHAR2(64)
14  MESSAGE_GROUP                         VARCHAR2(64)
15  CLIENT_ID                             VARCHAR2(64)
16  MODULE_ID                             VARCHAR2(64)
17  PROCESS_ID                            VARCHAR2(32)
18  THREAD_ID                             VARCHAR2(64)
19  USER_ID                               VARCHAR2(64)
20  INSTANCE_ID                           VARCHAR2(64)
21  DETAILED_LOCATION                     VARCHAR2(160)
22  PROBLEM_KEY                           VARCHAR2(550)
23  UPSTREAM_COMP_ID                      VARCHAR2(100)
24  DOWNSTREAM_COMP_ID                    VARCHAR2(100)
25  EXECUTION_CONTEXT_ID                  VARCHAR2(100)
26  EXECUTION_CONTEXT_SEQUENCE            NUMBER
27  ERROR_INSTANCE_ID                     NUMBER
28  ERROR_INSTANCE_SEQUENCE               NUMBER
29  VERSION                               NUMBER
30  MESSAGE_TEXT                          VARCHAR2(2048)
31  MESSAGE_ARGUMENTS                     VARCHAR2(512)
32  SUPPLEMENTAL_ATTRIBUTES               VARCHAR2(512)
33  SUPPLEMENTAL_DETAILS                  VARCHAR2(4000)
34  PARTITION                             NUMBER
35  RECORD_ID                             NUMBER

A simple query to get the alert log messages and timestamp would look like.

SET linesize 160 pagesize 200
col RECORD_ID FOR 9999999 head ID
col ORIGINATING_TIMESTAMP FOR a20 head DATE
col MESSAGE_TEXT FOR a120 head Message

SELECT
    record_id,
    to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS'),
    message_text
FROM
    x$dbgalertext;

For daily use I’ve put together two scripts.

  •  tal.sql list all or some alert log messages. Messages will be filtered by the parameter
  •  taln.sql list the last n numbers of rows in an alert log.

Write into the alertlog

The procedure kdswrt in dbms_system package allows us to write own messages in the alert log / trace files or both. It receives two parameters:

  • A number that indicates where do we want to write our message
    1. Writing to a TRACE file
    2. Writing to the Alert.log file
    3. Writing to both of them
  • A text string (the message itself).
EXEC dbms_system.ksdwrt(2, 'ORA-00042: Test message in alert log.');

Query the Alertlog

List the last 10 lines in the alert log.

SQL> @taln 10


SQL> @taln 10

      ID DATE                 Message
-------- -------------------- ----------------------------------------------------------------------
    4333 23.07.2013 22:00:47  Thread 1 advanced TO log SEQUENCE 94 (LGWR switch)
    4334 23.07.2013 22:00:47    CURRENT log# 1 seq# 94 mem# 0: /u00/oradata/TDB01/redog1m1TDB01.dbf
    4335 23.07.2013 22:00:47    CURRENT log# 1 seq# 94 mem# 1: /u01/oradata/TDB01/redog1m2TDB01.dbf
    4336 23.07.2013 22:00:47  Archived Log entry 111 added FOR thread 1 SEQUENCE 93 ID 0xa3d43dfa...
    4337 24.07.2013 02:00:00  Closing scheduler window
    4338 24.07.2013 02:00:00  Closing Resource Manager plan via scheduler window
    4339 24.07.2013 02:00:00  Clearing Resource Manager plan via parameter
    4340 24.07.2013 03:38:21  VKTM detected a TIME drift. Please CHECK trace file FOR more details.
    4341 24.07.2013 09:18:38  VKTM detected a TIME drift. Please CHECK trace file FOR more details.
    4342 24.07.2013 14:50:05  ORA-00042: Test Message IN alert log

10 ROWS selected.

Query the alert log string ORA-00042.

SQL> @tal ORA-00042

      ID DATE                 Message
-------- -------------------- -------------------------------------------
    4342 24.07.2013 14:50:05  ORA-00042: Test Message IN alert log


FILTER ON alert log message => ORA-00042

Other fixed tables

There are bunch of other X$ Fixed Tables. At lease the following are somehow related to the ADR

  • X$DBGDIREXT list all file and directory names under diagnostic_dest/diag directory. Will be quite a lot on a shared DB server
  • X$DBGRICX list of ADR Incidents

References

Some links related to this post.