Search

OakieTags

more tags

Who's online

There are currently 0 users and 25 guests online.

Recent comments

Affiliations

Home

ORA-31098: Internal event to turn on XDB tracing

Submitted by marco on Sun, 2010-01-17 22:25

“Do not document”…

…if not only due to that comment, it is a very interesting event, if not only that it seems that it is used for multiple items and not only tracing. I tried to figure out what I could do with this event regarding the XDB Protocol Server trying to figure out how it works and to trace a partially documented/undocumented feature in the manuals that makes use of the XDB Protocol Server.

The XDB Procotol Server architecture is used for more than the (APEX) PL/SQL Gateway. It also supports HTTP, FTP and WebDAV (so called) “servlets” and a hook-in into C kernel library that enables the XMLDB Native Database Web Service (NDWS).

The following should only be done asked by Oracle Support and/or are at your own risk. Always test on a test environment (so if when the database is corrupt is not a big deal)

Anyway, “events” can be set on session and system level and/or via the oradebug facility. Julian Dyke has a good post on the basics. As Julian describes, there are four types of numeric events: Immediate dumps, Conditional dumps, Trace dumps, Events that change database behavior. The “fun” with the ORA-31098 seems that is used for multiple things. It at least creates DDL scripts in trace file during dbms_xmlschema registration and it also traces XDB Protocol Server issues. During X-Mas and new years eve I had some time, so I tried to figure out some of them…

Described in a small overview XDB event post, some of these settings can be found somewhere in the Oracle manuals…

Debugging XML Schema Registration

ORA-31098: Internal event to turn on XDB tracing

When you store your data via the object relational method, using DBMS_XMLSCHEMA.RegisterSchema, you can use the following event to debug or see which statements are used to create the tables and object types. You should call this event before you execute DBMS_XMLSCHEMA.

#993333; font-weight: bold;">ALTER session #993333; font-weight: bold;">SET events #66cc66;">= #ff0000;">'31098 trace name context forever'

The trace file with the statements is created in the udump directory. The udump directory is set via the parameter user_dump_dest

Tracing The Protocol Server

This event is tracing the XDB protocol server. Trace files will be written to the udump directory. Look out for shared server trace files like the following: SID_s000_17016.trc. Depending on the amount of shared servers you have defined, you will encounter more then one trace file. Be ware that this is causing a lot of extra overhead, so switch it off (remove the event setting from the database parameter file / spfile) if you don’t use it any more…

For multiple system event 31098 level x settings, although tedious I checked level 1-16, while doing every time the following, after bouncing the database with the new event 31098 level setting:

SQL#66cc66;">> #993333; font-weight: bold;">ALTER system #993333; font-weight: bold;">SET event#66cc66;">= 
     #ff0000;">'31098 trace name context forever, level x' scope#66cc66;">=spfile;
SQL#66cc66;">> shutdown immediate
SQL#66cc66;">> startup

HTTP

  • Connected via HTTP on default port 8080 while using the SYSTEM database account
  • Clicked on the OLAP_XDB folder
  • Clicked on the dsclass.xml file and therefore opening it
  • Closed the Windows Internet Explorer

FTP

  • Opened a FTP session by connecting on default port 2100
  • Logging in via the SYSTEM database account
  • Did a “ls” statement
  • Did a “cd OLAP_XDB” statement
  • Did a “get dsclass.xml” statement
  • Exited the ftp session via “bye”

Now to the results of my attempt to document the undocumented…

31098 trace name context forever, level 1

HTTP – Level 1 showed simple tracing of HTTP 1.1 messages like

#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-08 #cc66cc;">23:07:#cc66cc;">40.120
HTTP: GET : #66cc66;">/OLAP_XDS#66cc66;">/ : HTTP#66cc66;">/#cc66cc;">1.1 #cc66cc;">200 OK
HTTP: GET : #66cc66;">/favicon#66cc66;">.ico : HTTP#66cc66;">/#cc66cc;">1.1 #cc66cc;">404 #993333; font-weight: bold;">NOT found

FTP – Level 1 caused connection problems…

ftp#66cc66;">> ls
#cc66cc;">200 PORT Command successful
#cc66cc;">150 ASCII #993333; font-weight: bold;">DATA Connection
Aborting any active #993333; font-weight: bold;">DATA connections#66cc66;">...
#cc66cc;">550 NLST error #993333; font-weight: bold;">IN processing request

The NLST statement for ftp is the raw statement that returns a list of file names in the given directory.

31098 trace name context forever, level 2

HTTP – Level 2 showed HTTP 1.1 tracing:

  • HTTP 1.1 state messagecodes
  • DAV security calls
  • Client info like browser version etc
  • Directory info send
  • File get statement
  • Directory get statement
  • File content
  • Server info
  • ETag and GUID / mime info

FTP – No information about the ftp session was traced.

31098 trace name context forever, level 3

HTTP – Level 3 showed HTTP 1.1 tracing

  • HTTP 1.1 state messagecodes
  • DAV security calls
  • Client info like browser version etc
  • Directory info send
  • File get statement
  • Directory get statement
  • ETag and GUID / mime info

FTP – No information about the ftp session was traced.

31098 trace name context forever, level 4

No HTTP or FTP tracing but tracing of execution of XDB repository event triggers…

#808080; font-style: italic;">--------Dumping Sorted Master Trigger List --------
#993333; font-weight: bold;">TRIGGER Owner : XDB
#993333; font-weight: bold;">TRIGGER Name : XDBCONFIG_VALIDATE
#993333; font-weight: bold;">TRIGGER Owner : XDB
#993333; font-weight: bold;">TRIGGER Name : XDB$CONFIG$xd
#808080; font-style: italic;">--------Dumping Trigger Sublists --------
 #993333; font-weight: bold;">TRIGGER sublist #cc66cc;">0 :
 #993333; font-weight: bold;">TRIGGER sublist #cc66cc;">1 :
#993333; font-weight: bold;">TRIGGER Owner : XDB
#993333; font-weight: bold;">TRIGGER Name : XDBCONFIG_VALIDATE
 #993333; font-weight: bold;">TRIGGER sublist #cc66cc;">2 :
 #993333; font-weight: bold;">TRIGGER sublist #cc66cc;">3 :
#993333; font-weight: bold;">TRIGGER Owner : XDB
#993333; font-weight: bold;">TRIGGER Name : XDB$CONFIG$xd
 #993333; font-weight: bold;">TRIGGER sublist #cc66cc;">4 :

HTTP – No information about the http session was traced.
FTP – No information about the ftp session was traced.

31098 trace name context forever, level 5

HTTP – Level 5 showed HTTP 1.1 server tracing

qmhGetHTTPError: Got unknown oracle error#66cc66;">. Error stack #993333; font-weight: bold;">IS:
HTTP#66cc66;">/#cc66cc;">1.1 #cc66cc;">500 Internal Server Error
MS#66cc66;">-Author#66cc66;">-Via: DAV
DAV: #cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">,<http :#66cc66;">//www#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/webdav#66cc66;">/props#66cc66;">>
Server: Oracle XML DB#66cc66;">/Oracle #993333; font-weight: bold;">DATABASE
Date: Mon#66cc66;">, 08 Jun #cc66cc;">2009 #cc66cc;">21:#cc66cc;">52:#cc66cc;">37 GMT
Content#66cc66;">-Type: text#66cc66;">/html; charset#66cc66;">=UTF#66cc66;">-#cc66cc;">8
Content#66cc66;">-Length: #cc66cc;">165
 
#66cc66;">< !DOCTYPE HTML PUBLIC #ff0000;">"-//IETF//DTD HTML 2.0//EN"#66cc66;">>
#66cc66;"><html#66cc66;">><head#66cc66;">>
#66cc66;"><title#66cc66;">>#cc66cc;">500 Internal Server Error#66cc66;">title#66cc66;">>
#66cc66;">head#66cc66;">><body#66cc66;">><h1#66cc66;">>Internal Server Error#66cc66;">h1#66cc66;">>
#66cc66;">body#66cc66;">>html#66cc66;">>
#66cc66;">http#66cc66;">>

FTP – No information about the ftp session was traced.

31098 trace name context forever, level 6

As far as I could detect, it looked the same as level 3…
To give you an example, trace output had the following output.

#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-08 #cc66cc;">23:#cc66cc;">56:#cc66cc;">01.513
GET
#66cc66;">/OLAP_XDS#66cc66;">/
HTTP#66cc66;">/#cc66cc;">1.1
Accept: image#66cc66;">/gif#66cc66;">, image#66cc66;">/x#66cc66;">-xbitmap#66cc66;">, image#66cc66;">/jpeg#66cc66;">, image#66cc66;">/pjpeg#66cc66;">, application#66cc66;">/x#66cc66;">-shockwave#66cc66;">-flash#66cc66;">, application#66cc66;">/vnd#66cc66;">.ms#66cc66;">-excel#66cc66;">, application#66cc66;">/vnd#66cc66;">.ms#66cc66;">-powerpoint#66cc66;">, application#66cc66;">/msword#66cc66;">, #66cc66;">*#808080; font-style: italic;">/*
Referer: http://10.252.252.102:8080/
Accept-Language: nl
UA-CPU: x86
Accept-Encoding: gzip, deflate
If-None-Match: "6850905AC52423DBE040E40AD6DE7A51786D04180B2E2F68C47904"
User-Agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2)
Host: 10.252.252.102:8080
Connection: Keep-Alive
Authorization: ***

FTP – No information about the ftp session was traced.

31098 trace name context forever, level 7

HTTP tracing is the same as level 3 and 6

FTP tracing showed the following (based on the statements used showed below):

ftp#66cc66;">> open localhost
ftp: connect: Connection refused
ftp#66cc66;">> open localhost #cc66cc;">2100
Connected #993333; font-weight: bold;">TO localhost#66cc66;">.localdomain#66cc66;">.
#cc66cc;">220#66cc66;">- srv01#66cc66;">-#cc66cc;">18#66cc66;">-102#66cc66;">.amis#66cc66;">.#993333; font-weight: bold;">LOCAL
Unauthorised #993333; font-weight: bold;">USE of this FTP server #993333; font-weight: bold;">IS prohibited #993333; font-weight: bold;">AND may be subject #993333; font-weight: bold;">TO civil #993333; font-weight: bold;">AND criminal prosecution#66cc66;">.
#cc66cc;">220 srv01#66cc66;">-#cc66cc;">18#66cc66;">-102#66cc66;">.amis#66cc66;">.#993333; font-weight: bold;">LOCAL FTP Server #66cc66;">(Oracle XML DB#66cc66;">/Oracle #993333; font-weight: bold;">DATABASE#66cc66;">) ready#66cc66;">.
#cc66cc;">530  Please login #993333; font-weight: bold;">WITH USER #993333; font-weight: bold;">AND PASS#66cc66;">.
#cc66cc;">530  Please login #993333; font-weight: bold;">WITH USER #993333; font-weight: bold;">AND PASS#66cc66;">.
KERBEROS_V4 rejected #993333; font-weight: bold;">AS an authentication type
Name #66cc66;">(localhost:oracle#66cc66;">): system
#cc66cc;">331 pass required #993333; font-weight: bold;">FOR SYSTEM
Password:
#cc66cc;">230 SYSTEM logged #993333; font-weight: bold;">IN
Remote system type #993333; font-weight: bold;">IS Unix#66cc66;">.
ftp#66cc66;">> ls
#cc66cc;">227 Entering Passive Mode #66cc66;">(#cc66cc;">10#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">102#66cc66;">,#cc66cc;">62#66cc66;">,#cc66cc;">156#66cc66;">)
#cc66cc;">150 ASCII #993333; font-weight: bold;">DATA Connection
drw#66cc66;">-r#808080; font-style: italic;">--r--   2 SYS      oracle         0 APR 24 10:45 OLAP_XDS
drw#66cc66;">-r#808080; font-style: italic;">--r--   2 SYS      oracle         0 MAY 25 07:49 home
drw#66cc66;">-r#808080; font-style: italic;">--r--   2 SYS      oracle         0 APR 24 11:01 images
drw#66cc66;">-r#808080; font-style: italic;">--r--   2 SYS      oracle         0 APR 24 10:45 olap_data_security
drw#66cc66;">-r#808080; font-style: italic;">--r--   2 SYS      oracle         0 MAY 27 17:29 public
drw#66cc66;">-r#808080; font-style: italic;">--r--   2 SYS      oracle         0 MAY 11 18:34 sys
#66cc66;">-rw#66cc66;">-r#808080; font-style: italic;">--r--   1 SYS      oracle         0 MAY 15 14:35 xdbconfig.xml
drw#66cc66;">-r#808080; font-style: italic;">--r--   2 SYS      oracle         0 APR 24 10:45 xds
#cc66cc;">226 ASCII Transfer Complete
ftp#66cc66;">> cd OLAP_XDS
#cc66cc;">250 CWD Command successful
ftp#66cc66;">> ls
#cc66cc;">227 Entering Passive Mode #66cc66;">(#cc66cc;">10#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">102#66cc66;">,#cc66cc;">39#66cc66;">,#cc66cc;">101#66cc66;">)
#cc66cc;">150 ASCII #993333; font-weight: bold;">DATA Connection
#66cc66;">-rw#66cc66;">-r#808080; font-style: italic;">--r--   1 SYS      oracle         0 APR 24 10:45 dsclass.xml
#cc66cc;">226 ASCII Transfer Complete
ftp#66cc66;">> get dsclass#66cc66;">.xml
#993333; font-weight: bold;">LOCAL: dsclass#66cc66;">.xml remote: dsclass#66cc66;">.xml
#cc66cc;">227 Entering Passive Mode #66cc66;">(#cc66cc;">10#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">102#66cc66;">,#cc66cc;">221#66cc66;">,#cc66cc;">72#66cc66;">)
#cc66cc;">150 ASCII #993333; font-weight: bold;">DATA Connection
#cc66cc;">226 ASCII Transfer Complete
#cc66cc;">1078 bytes received #993333; font-weight: bold;">IN #cc66cc;">0.004 seconds #66cc66;">(2#66cc66;">.6e#66cc66;">+02 Kbytes#66cc66;">/s#66cc66;">)
ftp#66cc66;">> bye
#cc66cc;">221 QUIT Goodbye#66cc66;">.

The trace output for these statements were traced as follows:

#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">45.916
#cc66cc;">220#66cc66;">- srv01#66cc66;">-#cc66cc;">18#66cc66;">-102#66cc66;">.amis#66cc66;">.#993333; font-weight: bold;">LOCAL 
Unauthorised #993333; font-weight: bold;">USE of this FTP server #993333; font-weight: bold;">IS prohibited #993333; font-weight: bold;">AND may be subject #993333; font-weight: bold;">TO civil #993333; font-weight: bold;">AND criminal prosecution#66cc66;">.
#cc66cc;">220 srv01#66cc66;">-#cc66cc;">18#66cc66;">-102#66cc66;">.amis#66cc66;">.#993333; font-weight: bold;">LOCAL FTP Server #66cc66;">(Oracle XML DB#66cc66;">/Oracle #993333; font-weight: bold;">DATABASE#66cc66;">) ready#66cc66;">.
AUTH GSSAPI
#cc66cc;">530  Please login #993333; font-weight: bold;">WITH USER #993333; font-weight: bold;">AND PASS#66cc66;">.
AUTH KERBEROS_V4
#cc66cc;">530  Please login #993333; font-weight: bold;">WITH USER #993333; font-weight: bold;">AND PASS#66cc66;">.
 
#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">48.034
USER system
#cc66cc;">331 pass required #993333; font-weight: bold;">FOR SYSTEM
 
#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">49.176
PASS XXXXXXX
#cc66cc;">230 SYSTEM logged #993333; font-weight: bold;">IN
SYST
#cc66cc;">215 Unix Type:A Version:Oracle XML DB 
 
#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">50.449
PASV
#cc66cc;">227 Entering Passive Mode #66cc66;">(#cc66cc;">10#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">102#66cc66;">,#cc66cc;">62#66cc66;">,#cc66cc;">156#66cc66;">)
LIST
#cc66cc;">150 ASCII #993333; font-weight: bold;">DATA Connection
#cc66cc;">226 ASCII Transfer Complete
 
#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">52.244
CWD OLAP_XDS
#cc66cc;">250 CWD Command successful
 
#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">53.159
PASV
#cc66cc;">227 Entering Passive Mode #66cc66;">(#cc66cc;">10#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">102#66cc66;">,#cc66cc;">39#66cc66;">,#cc66cc;">101#66cc66;">)
LIST
#cc66cc;">150 ASCII #993333; font-weight: bold;">DATA Connection
#cc66cc;">226 ASCII Transfer Complete
 
#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">55.688
PASV
#cc66cc;">227 Entering Passive Mode #66cc66;">(#cc66cc;">10#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">102#66cc66;">,#cc66cc;">221#66cc66;">,#cc66cc;">72#66cc66;">)
RETR dsclass#66cc66;">.xml
#cc66cc;">150 ASCII #993333; font-weight: bold;">DATA Connection
#cc66cc;">226 ASCII Transfer Complete
 
#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">56.907
QUIT
#cc66cc;">221 QUIT Goodbye#66cc66;">.

31098 trace name context forever, level 8

Level 8 traced the same kind of error as in level 5 (server errors)

31098 trace name context forever, level 9

Level 9 traced the same kind of error as in level 7 (FTP & HTTP output)

31098 trace name context forever, level 10

Level 10 traced the same kind of error as in level 7 (FTP & HTTP output)

31098 trace name context forever, level 11

Level 11 traced the same kind of error as in level 7 (FTP & HTTP output)

31098 trace name context forever, level 12

Level 12 traced the same kind of error as in level 5 (server errors)

31098 trace name context forever, level 13 – 16

Level 13 until level 16 didn’t produce any trace files regarding my FTP and HTTP statements.

Summery

Although this tracing is far from complete regarding actions I could have done like WebDAV access or to trigger automatic shredding of objects or other XDB event (repository?) relevant actions, at least some events could be useful for further investigation if needed regarding HTTP tracing (level 2), FTP tracing (eg. level 7) and repository event XML Schema triggers (level 4) and automatic XDB shredding mechanisms.

Setting event level 2 could be useful regarding APEX PL/SQL Gateway tracing and/or for APEX PL/SQL Gateway “hacking”, although I think your asking for it, if the PL/SQL Gateway via the XDB Proctocol Server has been put on the internet unprotected, and does not at least a proxy server in front of it to protect for some unauthorized access…

HTH

:-)