Creating an alert log via XML/SQL

I don’t know what it is today, but reading Tanel’s post about “Oracle 11g: Reading alert log via SQL“, I just wanted to see if I could manage to do it backwards.

In principle you don’t have to use the statements below, because I noticed that the DBMS_SYSTEM.KSDWRT will do it for you automatically.

The content of my Oracle 11g log.xml file is as follows

 <msg TIME='2008-11-24T21:10:48.265+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='opistr_real:887:3971575317' TYPE='NOTIFICATION' GROUP='startup'
 level='16' host_id='14-317-818' host_addr='10.10.10.103'
 pid='2188' version='1'>
 <txt>Starting ORACLE instance (normal)
 </txt>
</msg>
<msg TIME='2008-11-24T21:10:48.312+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksunfy:13802:2937430291' TYPE='NOTIFICATION' GROUP='startup'
 level='16' host_id='14-317-818' host_addr='10.10.10.103'
 pid='2188'>
 <txt>LICENSE_MAX_SESSION = 0
 </txt>
</msg>
<msg TIME='2008-11-24T21:10:48.312+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='ksunfy:13803:4207019197' TYPE='NOTIFICATION' GROUP='startup'
 level='16' host_id='14-317-818' host_addr='10.10.10.103'
 pid='2188'>
 <txt>LICENSE_SESSIONS_WARNING = 0
 </txt>
</msg>

So based on Tanel’s blog, could I do the same, but now with XMLDB functions (based on database version 11.1.0.7.0)…

Here it goes.

SELECT XMLElement(NOENTITYESCAPING "msg", 
                     XMLAttributes( alt.ORIGINATING_TIMESTAMP AS "time"
                                  , alt.ORGANIZATION_ID       AS "org_id"
                                  , alt.COMPONENT_ID          AS "comp_id"
                                  , alt.MESSAGE_ID            AS "msg_id"
                                  , alt.MESSAGE_TYPE          AS "type"
                                  , alt.MESSAGE_GROUP         AS "group"
                                  , alt.MESSAGE_LEVEL         AS "level"
                                  , alt.HOST_ID               AS "host_id"
                                  , alt.HOST_ADDRESS          AS "host_addr"
                                  , alt.PROCESS_ID            AS "pid_id"
                                  , alt.VERSION               AS "version"
                                  ),
                     XMLElement("txt", MESSAGE_TEXT)                         
                 ) AS "LOG.XML"
FROM   X$DBGALERTEXT alt
WHERE  rownum < = 3
/
 
SQL> SET LINES 74
SQL>
SQL> SELECT XMLElement(NOENTITYESCAPING "msg",
  2                       XMLAttributes( alt.ORIGINATING_TIMESTAMP AS "time"
  3                                    , alt.ORGANIZATION_ID       AS "org_id"
  4                                    , alt.COMPONENT_ID          AS "comp_id"
  5                                    , alt.MESSAGE_ID            AS "msg_id"
  6                                    , alt.MESSAGE_TYPE          AS "type"
  7                                    , alt.MESSAGE_GROUP         AS "group"
  8                                    , alt.MESSAGE_LEVEL         AS "level"
  9                                    , alt.HOST_ID               AS "host_id"
 10                                    , alt.HOST_ADDRESS          AS "host_addr"
 11                                    , alt.PROCESS_ID            AS "pid_id"
 12                                    , alt.VERSION               AS "version"
 13                                    ),
 14                       XMLElement("txt", MESSAGE_TEXT)
 15                   ) AS "LOG.XML"
 16  FROM   X$DBGALERTEXT alt
 17  WHERE  rownum < = 3
 18  /
 
LOG.XML
--------------------------------------------------------------------------
<msg TIME="2008-11-24T21:10:48.265+01:00" org_id="oracle" comp_id="rdbms"
msg_id="opistr_real:887:3971575317" TYPE="5" GROUP="startup" level="16" ho
st_id="14-317-818" host_addr="10.10.10.103" pid_id="2188" version="1"><txt>Starting ORACLE instance (normal)
</txt>
 
<msg TIME="2008-11-24T21:10:48.312+01:00" org_id="oracle" comp_id="rdbms"
msg_id="ksunfy:13802:2937430291" TYPE="5" GROUP="startup" level="16" host_
id="14-317-818" host_addr="10.10.10.103" pid_id="2188" version="0"><txt>LI
CENSE_MAX_SESSION = 0
</txt></msg>
 
<msg TIME="2008-11-24T21:10:48.312+01:00" org_id="oracle" comp_id="rdbms"
msg_id="ksunfy:13803:4207019197" TYPE="5" GROUP="startup" level="16" host_
id="14-317-818" host_addr="10.10.10.103" pid_id="2188" version="0"><txt>LI
CENSE_SESSIONS_WARNING = 0
</txt></msg>
 
3 ROWS selected.

I comes close, but it uses still double quotes and doesn’t do the new lines on every third element, also the pretty print layout between “msg” and “txt” elements isn’t properly done yet. Trying to come a little bit close to the original log.xml output, I came up with the following…

SELECT xmlserialize
       (CONTENT
        XMLElement(NOENTITYESCAPING "msg", 
                     XMLAttributes( alt.ORIGINATING_TIMESTAMP AS "time"
                                  , alt.ORGANIZATION_ID       AS "org_id"
                                  , alt.COMPONENT_ID          AS "comp_id"
                                  , alt.MESSAGE_ID            AS "msg_id"
                                  , alt.MESSAGE_TYPE          AS "type"
                                  , alt.MESSAGE_GROUP         AS "group"
                                  , alt.MESSAGE_LEVEL         AS "level"
                                  , alt.HOST_ID               AS "host_id"
                                  , alt.HOST_ADDRESS          AS "host_addr"
                                  , alt.PROCESS_ID            AS "pid_id"
                                  , alt.VERSION               AS "version"
                                  ),
                     XMLElement("txt", MESSAGE_TEXT)                         
                 )
        INDENT SIZE=0 HIDE DEFAULTS
        ) AS "LOG.XML"
FROM   X$DBGALERTEXT alt
WHERE  rownum < = 3
/
 
SQL> SET LINES 1000
SQL> col "LOG.XML" FOR a100
SQL> SELECT xmlserialize
  2         (CONTENT
  3          XMLElement(NOENTITYESCAPING "msg",
  4                       XMLAttributes( alt.ORIGINATING_TIMESTAMP AS "time"
  5                                    , alt.ORGANIZATION_ID       AS "org_id"
  6                                    , alt.COMPONENT_ID          AS "comp_id"
  7                                    , alt.MESSAGE_ID            AS "msg_id"
  8                                    , alt.MESSAGE_TYPE          AS "type"
  9                                    , alt.MESSAGE_GROUP         AS "group"
 10                                    , alt.MESSAGE_LEVEL         AS "level"
 11                                    , alt.HOST_ID               AS "host_id"
 12                                    , alt.HOST_ADDRESS          AS "host_addr"
 13                                    , alt.PROCESS_ID            AS "pid_id"
 14                                    , alt.VERSION               AS "version"
 15                                    ),
 16                       XMLElement("txt", MESSAGE_TEXT)
 17                   )
 18          INDENT SIZE=0 HIDE DEFAULTS
 19          ) AS "LOG.XML"
 20  FROM   X$DBGALERTEXT alt
 21  WHERE  rownum < = 3
 22  /
 
LOG.XML
------------------------------------------------------------------------------
<msg TIME="2008-11-24T21:10:48.265+01:00" org_id="oracle" comp_id=
"rdbms" msg_id="opistr_real:887:3971575317"
TYPE="5" GROUP="startup" level="16" host_id=
"14-317-818" host_addr="10.10.10.103" pid_id
="2188" version="1">
<txt>Starting ORACLE instance (normal)
</txt>
 
 
<msg TIME="2008-11-24T21:10:48.312+01:00" org_id="oracle" comp_id=
"rdbms" msg_id="ksunfy:13802:2937430291" typ
e="5" GROUP="startup" level="16" host_id="14
-317-818" host_addr="10.10.10.103" pid_id="2
188" version="0">
<txt>LICENSE_MAX_SESSION = 0
</txt>
</msg>
 
<msg TIME="2008-11-24T21:10:48.312+01:00" org_id="oracle" comp_id=
"rdbms" msg_id="ksunfy:13803:4207019197" typ
e="5" GROUP="startup" level="16" host_id="14
-317-818" host_addr="10.10.10.103" pid_id="2
188" version="0">
<txt>LICENSE_SESSIONS_WARNING = 0
</txt>
</msg>
 
3 ROWS selected.

It doesn’t matter. It was just for fun. DBMS_SYSTEM.KSDWRT will do it for you automatically, anyway…

8-)
.