Skip to Main Content
  • Questions
  • How to analyse or dig log.xml of Oracle Listener Log with XMLTABLE?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Quanwen.

Asked: June 28, 2019 - 2:51 am UTC

Last updated: August 15, 2019 - 2:05 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hey Connor and Chris,

I want to analyse or dig log.xml of Oracle Listener Log with XMLTABLE. Afterwards I found Thomas Kyte's this thread - https://asktom.oracle.com/pls/apex/asktom.search?tag=xmldb What he replies to somebody attracts me, have a look at the following code,
ops$tkyte@ORA920.US.ORACLE.COM> host cat tkyte.xml
    <Employee>
        <Name>Shailesh</Name>
        <Age>23</Age>
    </Employee>

ops$tkyte@ORA920.US.ORACLE.COM> 
ops$tkyte@ORA920.US.ORACLE.COM> create or replace directory xmldir as '/export/home/tkyte/'
  2  /

Directory created.

ops$tkyte@ORA920.US.ORACLE.COM> 
ops$tkyte@ORA920.US.ORACLE.COM> drop table xmltable;

Table dropped.

ops$tkyte@ORA920.US.ORACLE.COM> create table xmltable of xmltype;

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> 
ops$tkyte@ORA920.US.ORACLE.COM> create or replace function get_Document(filename varchar2) return clob is
  2     xbfile bfile;
  3     xclob clob;
  4  begin
  5    xbfile  := bfilename('XMLDIR',filename);
  6    dbms_lob.open(xbfile);
  7  
  8    dbms_lob.createtemporary(xclob,TRUE,dbms_lob.session);
  9    dbms_lob.loadfromfile(xclob,xbfile, dbms_lob.getlength(xbfile));
 10    dbms_lob.close(xbfile);
 11    return xclob;
 12  end;
 13  /

Function created.

ops$tkyte@ORA920.US.ORACLE.COM> 
ops$tkyte@ORA920.US.ORACLE.COM> insert into xmltable values ( xmltype( get_document( 'tkyte.xml' ) ) );

1 row created.

ops$tkyte@ORA920.US.ORACLE.COM> select * from xmltable;

SYS_NC_ROWINFO$
-----------------------------------------------------------------------------------------------------------------------------------
<Employee>
  <Name>Shailesh</Name>
  <Age>23</Age>
</Employee>


ops$tkyte@ORA920.US.ORACLE.COM>


It worked for me -- Are your sure the file has what you think in it?  Also, this could be character set related -- if you have a UTF8 instance on windows -- it might be (and in that case, contact support and reference in progress bug #2535898) 


So I mimic to write my example,

SYS@test> create or replace directory xmldir as '/u01/app/oracle/diag/tnslsnr/test/listener/alert/';

Directory created.

SYS@test> drop table xmltable;
drop table xmltable
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SYS@test> create table xmltable of xmltype;

Table created.

SYS@test> create or replace function get_Document(filename varchar2) return clob is
  2  xbfile bfile;
  3  xclob clob;
  4  begin
  5    xbfile := bfilename('XMLDIR', filename);
  6    dbms_lob.open(xbfile);
  7  
  8    dbms_lob.createtemporary(xclob, TRUE, dbms_lob.session);
  9    dbms_lob.loadfromfile(xclob, xbfile, dbms_lob.getlength(xbfile));
 10    dbms_lob.close(xbfile);
 11    return xclob;
 12  end;
 13  /

Function created.

SYS@test> 
SYS@test> insert into xmltable values (xmltype(get_document('log.xml')));
insert into xmltable values (xmltype(get_document('log.xml')))
                             *
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of '?'
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1


Could you help me to trouble-shooting it?

In fact it's my intention to dig real IP addresses connecting to DB from log.xml. Almost all of stuff/content on log.xml looks like this,
<msg time='2019-06-27T03:57:19.244+08:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='test'
 host_addr='xxx.xxx.xxx.xxx' version='1'>
 <txt>27-JUN-2019 03:57:19 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=36080)) * establish * test * 0
 </txt>
</msg>
<msg time='2019-06-27T03:57:19.265+08:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='test'
 host_addr='xxx.xxx.xxx.xxx'>
 <txt>27-JUN-2019 03:57:19 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=36082)) * establish * test * 0
 </txt>
</msg>
......


Could you have a good example of digging ip address from it with XMLTABLE (because it's xml format)?

Thank you very much.

Best Regards
Quanwen Zhao

and Connor said...

This will be most likely be a character set conversion issue, because "loadfromfile" does not handle charactersets.

eg

SQL>    declare
  2         xbfile bfile;
  3         xclob clob;
  4         x xmltype;
  5      begin
  6        xbfile  := bfilename('TEMP','listener.xml');
  7        dbms_lob.open(xbfile);
  8
  9        dbms_lob.createtemporary(xclob,TRUE,dbms_lob.session);
 10        dbms_lob.loadfromfile(xclob,xbfile, dbms_lob.getlength(xbfile));
 11       dbms_lob.close(xbfile);
 12
 13       x := xmltype(xclob);
 14     end;
 15     /
   declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00210: expected '<' instead of '┐'
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 13


versus

SQL> set serverout on
SQL> declare
  2     xbfile bfile;
  3     xclob clob;
  4     x xmltype;
  5
  6     dest_offset integer := 1;
  7     src_offset integer := 1;
  8     src_csid number := nls_charset_id('UTF8');
  9     lang_context integer := dbms_lob.default_lang_ctx;
 10     warning integer;
 11  begin
 12        xbfile  := bfilename('TEMP','listener.xml');
 13        dbms_lob.open(xbfile);
 14
 15        dbms_lob.createtemporary(xclob,TRUE,dbms_lob.session);
 16        dbms_lob.loadclobfromfile(xclob,xbfile, dbms_lob.getlength(xbfile),dest_offset, src_offset, src_csid, lang_context, warning);
 17       dbms_lob.close(xbfile);
 18
 19       x := xmltype(xclob);
 20
 21       dbms_output.put_line(x.getclobval());
 22  end;
 23  /
<msg time='2019-06-27T03:57:19.244+08:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='test'

host_addr='xxx.xxx.xxx.xxx' version='1'>
 <txt>27-JUN-2019 03:57:19 *
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) *
(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=36080)) * establish * test * 0
 </txt>
</msg>



Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

LPX-00245: extra data after end of document

Quanwen Zhao, July 04, 2019 - 7:54 am UTC

Hello Connor,

According to your code I replace with my DIRECTORY and filename of XML. What a pity it shows another ERROR - "LPX-00245".

set serverout on
declare
   xbfile bfile;
   xclob clob;
   x xmltype;
   dest_offset integer := 1;
   src_offset integer := 1;
   src_csid number := nls_charset_id('UTF8');
   lang_context integer := dbms_lob.default_lang_ctx;
   warning integer;
begin
      <b>xbfile  := bfilename('XMLDIR', 'log.xml');</b>
      dbms_lob.open(xbfile);
      dbms_lob.createtemporary(xclob, TRUE, dbms_lob.session);
      dbms_lob.loadclobfromfile(xclob, xbfile, dbms_lob.getlength(xbfile), dest_offset, src_offset, src_csid, lang_context, warning);
      dbms_lob.close(xbfile);
      x := xmltype(xclob);
      dbms_output.put_line(x.getclobval());
end;
/
declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
<b>LPX-00245: extra data after end of document</b>
Error at line 7
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 16


Best Regards
Quanwen Zhao
Connor McDonald
July 05, 2019 - 6:01 am UTC

It needs to be a single XML

SQL> create table t ( c clob );

Table created.

SQL> declare
  2    x clob := q'{<msg time='2019-02-27T18:00:32.372+08:00' version='1'> <txt>Creating new log segment: </txt></msg>}';
  3  begin
  4  delete t;
  5  insert into t values (x);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select * from t;

C
--------------------------------------------------------------------------------
<msg time='2019-02-27T18:00:32.372+08:00' version='1'> <txt>Creating new log seg
ment: </txt></msg>


1 row selected.

SQL> select xmltype(c) from t;

XMLTYPE(C)
----------------------------------------------------------------------------------------------------------------------------------
<msg time="2019-02-27T18:00:32.372+08:00" version="1">
  <txt>Creating new log segment: </txt>
</msg>


1 row selected.

SQL>
SQL> declare
  2    x clob := q'{<msg time='2019-02-27T18:00:32.372+08:00' version='1'> <txt>Creating new log segment: </txt></msg>}';
  3  begin
  4  delete t;
  5  insert into t values (x||x);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select * from t;

C
--------------------------------------------------------------------------------
<msg time='2019-02-27T18:00:32.372+08:00' version='1'> <txt>Creating new log seg
ment: </txt></msg><msg time='2019-02-27T18:00:32.372+08:00' version='1'> <txt>Cr
eating new log segment: </txt></msg>


1 row selected.

SQL> select xmltype(c) from t;
ERROR:
ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00245: extra data after end of document
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1



no rows selected

SQL>
SQL>


so you'll need to wrap it

Could you have another better idea to finding out all of real IP addresses from that "log.xml"?

Quanwen Zhao, July 08, 2019 - 9:20 am UTC

Hi Connor :-),

I'm very thankful for your guiding me. I always learn something new for you. I think that XMLTYPE (XMLTABLE) is actually some limitation on handling more line xml stuff.

As you can see there're more than one line on the xml file of "log.xml" on oracle db.

......
08-JUL-2019 15:47:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.XXX.XXX)(PORT=47766)) * establish * test * 0
08-JUL-2019 15:47:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.XXX.XXX)(PORT=47768)) * establish * test * 0
08-JUL-2019 15:47:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.XXX.XXX)(PORT=47764)) * establish * test * 0
08-JUL-2019 15:47:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.XXX.XXX)(PORT=47770)) * establish * test * 0
08-JUL-2019 15:47:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.XXX.XXX)(PORT=47772)) * establish * test * 0
08-JUL-2019 15:47:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.XXX.XXX)(PORT=47774)) * establish * test * 0
08-JUL-2019 15:47:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.XXX.XXX)(PORT=47776)) * establish * test * 0
08-JUL-2019 15:47:35 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.XXX.XXX)(PORT=47778)) * establish * test * 0
08-JUL-2019 15:47:35 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.XXX.XXX)(PORT=47782)) * establish * test * 0
08-JUL-2019 15:47:35 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.XXX.XXX)(PORT=47780)) * establish * test * 0
08-JUL-2019 15:47:35 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XXX.XXX.XXX)(PORT=47786)) * establish * test * 0
......


Could you have another better idea to finding out all of real IP addresses from that "log.xml"?
(because Gaz in Oz once remind me from https://community.oracle.com/message/15361733#15361733 )

Best Regards
Quanwen Zhao
Connor McDonald
July 25, 2019 - 2:15 am UTC

I'd be more inclined just to mine the default log with some simple SQL expressions


SQL> create or replace directory LISTENER_LOG as '/u01/app/oracle/diag/tnslsnr/db192/listener/trace';

Directory created.

SQL>
SQL> drop table listener;

Table dropped.

SQL>
SQL> create table listener
  2  (
  3     line varchar2(4000)
  4  )
  5  organization external (
  6     type   oracle_loader
  7     default directory LISTENER_LOG
  8     access parameters
  9     (
 10        records delimited by newline
 11        nobadfile
 12        nologfile
 13        nodiscardfile
 14        fields ldrtrim
 15        missing field values are null
 16        reject rows with all null fields
 17        (
 18            line char(4000)
 19        )
 20     )
 21     location ('listener.log')
 22  )
 23  reject limit unlimited
 24  /

Table created.

SQL>
SQL> select * from listener where rownum <= 20;

LINE
----------------------------------------------------------------------------------------------------------------------------------
2019-02-18T23:48:42.647524-08:00
Create Relation ADR_CONTROL
Create Relation ADR_INVALIDATION
Create Relation INC_METER_IMPT_DEF
Create Relation INC_METER_PK_IMPTS
LISTENER for Linux: Version 19.0.0.0.0 - Production
Version 19.2.0.0.0
System parameter file is /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/db192/listener/alert/log.xml
Trace information written to /u01/app/oracle/diag/tnslsnr/db192/listener/trace/ora_4087_140671000284160.trc
Trace level is currently 0
Started with pid=4087
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db192)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listener completed notification to CRS on start
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
18-FEB-2019 23:48:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=db192.localdomain)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=L
ISTENER)(VERSION=318767104)) * status * 0

2019-02-18T23:49:28.339534-08:00
WARNING: Subscription for node down event still pending

20 rows selected.

SQL>
SQL> with details as
  2  (
  3  select last_value(tstamp ignore nulls) over ( order by tstamp ) as tstamp,
  4         substr(host,1,instr(host,')')-1) host
  5  from
  6    ( select
  7        case when line like '__-___-____ __:__:__ %' then to_date(substr(line,1,20),'DD-MON-YYYY HH24:MI:SS') end tstamp,
  8        case when line like '%HOST=%' then substr(line,instr(line,'HOST=')+5) end host
  9      from listener
 10    )
 11  )
 12  select  *
 13  from details
 14  where host is not null;

TSTAMP              HOST
------------------- ----------------------------------------
18/02/2019 23:48:43 db192.localdomain
18/02/2019 23:49:28 db192.localdomain
18/02/2019 23:49:28 db192.localdomain
18/02/2019 23:49:28 db192.localdomain
18/02/2019 23:49:28 db192.localdomain
18/02/2019 23:49:28 db192.localdomain
18/02/2019 23:49:28 db192.localdomain
18/02/2019 23:49:28 db192.localdomain
18/02/2019 23:49:29 db192.localdomain
18/02/2019 23:49:29 db192.localdomain
18/02/2019 23:49:29 db192.localdomain
18/02/2019 23:49:29 db192.localdomain
18/02/2019 23:50:09 192.168.1.182
18/02/2019 23:51:07 192.168.1.182
18/02/2019 23:51:34 192.168.1.182
18/02/2019 23:54:19 192.168.1.182
18/02/2019 23:56:33 192.168.1.182
...
178 rows selected.

SQL>
SQL>


Your SQL is really simple and cool

Quanwen Zhao, August 01, 2019 - 1:56 am UTC

Hello Connor :-),

Thank you very much for providing me this so simple and cool SQL. I'll read and test it, unfortunately my current SQL skills is not much too deeper than you.

Best Regards
Quanwen Zhao

Mimic your prior SQL to eventually write out my own on 'XML' format of listener log file "log.xml"

Quanwen Zhao, August 14, 2019 - 2:58 am UTC

Hello Connor :-),

Yesterday I observed and learnt your prior SQL using to dig real IP Address from listener log file "listener.log".

Today I eventually write out my own from the XML format of listener log file "log.xml".

SYS@xxxx> create or replace directory LISTENER_LOG_XML as '/u01/app/oracle/diag/tnslsnr/xxxx/listener/alert';

Directory created.

SYS@xxxx> drop table listener_log_xml;
drop table listener_log_xml
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SYS@xxxx> create table listener_log_xml (line varchar2(4000))
  2  organization external (
  3    type oracle_loader
  4    default directory LISTENER_LOG_XML
  5    access parameters (
  6      records delimited by newline
  7      nobadfile
  8      nologfile
  9      nodiscardfile
 10      fields ldrtrim
 11      missing field values are null
 12      reject rows with all null fields
 13      ( 
 14        line char(4000)
 15      )
 16    )
 17    location ('log.xml')
 18  )
 19  reject limit unlimited
 20  /

Table created.

SYS@xxxx> set long 999999999
SYS@xxxx> set pagesize 80
SYS@xxxx> select line from listener_log_xml where rownum <= 18;

LINE
--------------------------------------------------------------------------------
<msg time='2019-08-14T10:26:27.507+08:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='xxxx'
 host_addr='xxx.xxx.xxx.xxx' version='1'>
 <txt>14-AUG-2019 10:26:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=r
oot))(SERVICE_NAME=xxxx)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS
=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=33953)) * establish * xxxx * 0

 </txt>
</msg>
<msg time='2019-08-14T10:26:27.549+08:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='xxxx'
 host_addr='xxx.xxx.xxx.xxx'>
 <txt>14-AUG-2019 10:26:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=r
oot))(SERVICE_NAME=xxxx)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS
=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=33954)) * establish * xxxx * 0

 </txt>
</msg>
<msg time='2019-08-14T10:26:27.553+08:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='xxxx'
 host_addr='xxx.xxx.xxx.xxx'>
 <txt>14-AUG-2019 10:26:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=r
oot))(SERVICE_NAME=xxxx)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))) * (ADDRESS
=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=47094)) * establish * xxxx * 0

 </txt>
</msg>

18 rows selected.


SYS@xxxx> set pagesize 200
SYS@xxxx> col host for a15
SYS@xxxx> select distinct host
  2  from
  3    ( select substr(host, 1, instr(host,')')-1) host
  4      from
  5        ( select
  6            case when line like '%HOST=%' then substr(line,instr(line, 'HOST=', -1, 1)+5) end host
  7          from listener_log_xml where line like '%establish%'
  8        )
  9    )
 10  order by 1  
 11  ;

HOST
---------------
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx
xxx.xxx.xxx.xxx

114 rows selected.


BTW due to security reason I deliberately hidden my real IP Address and db_name on version 11.2.0.4.0 of my oracle Production System.

Thank you very much for helping me.

Best Regards
Quanwen Zhao
http://quanwenzhao.wordpress.com
Connor McDonald
August 15, 2019 - 2:05 am UTC

Glad we could help, and thanks for posting your code

Using '*' to separate newline on "log.xml" via oracle external table

Quanwen Zhao, August 19, 2019 - 6:44 am UTC

Hello Connor :-),

Today I have a new idea in my mind - using '*' to separate newline on "log.xml" via oracle external table.

I think that it's more simple I just filter keyword "%PORT%' and other SQL select is same to the previous code.

The following is my partial code.

......
SYS@xxxx> SELECT line FROM listener_log_xml WHERE rownum <= 6
/

LINE
--------------------------------------------------------------------------------
<msg time='2019-08-12T20:38:17.906+08:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='xxxx'
 host_addr='xxx.xxx.xxx.xxx' version='1'>
 <txt>12-AUG-2019 20:38:17

 (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=xxxx)
(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root)))

 (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=55604))
 establish
 xxxx
 0
 </txt>
</msg>
<msg time='2019-08-12T20:38:17.906+08:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='xxxx'
 host_addr='xxx.xxx.xxx.xxx'>
 <txt>12-AUG-2019 20:38:17


6 rows selected.

SYS@xxxx> SELECT * FROM listener_log_xml WHERE line LIKE '%PORT%' AND rownum <= 12
/

LINE
--------------------------------------------------------------------------------
 (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=55604))
 (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=55600))
 (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=55606))
 (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=55608))
 (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=55610))
 (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=55612))
 (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=55614))
 (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=55616))
 (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=55618))
 (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=55620))
 (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=55624))
 (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=55622))

12 rows selected.
......


But the full version of my code has been upload to Github, please see here - https://github.com/guestart/Oracle-SQL-Scripts/blob/master/dig_listener_log_xml/dig_ip_via_listener_log_xml_2.sql

Best Regards
Quanwen Zhao
https://quanwenzhao.wordpress.com

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.