Roads ? - where we're going, we don't need roads ...
Month of September, 2008 | ORA600
ORA600 content Content RSS
Oracle ORA600 News RRSS Oracle News RSS
ORA600 blog Blog RSS
ORA600 blog Blog Atom

User login

Month of September, 2008

Consuming WS-Security enabled webservices in PL/SQL - part 2

A couple of weeks ago I wrote about consuming ws-security enabled webservices in PLSQL.

The problem was that, even using Oracle 11g and Jpublisher 11g, I was not able to generate a usertoken and password in the SOAP header according to the WS-Security standard.

My twisted solution was to put a WS proxy (or gateway if you like) in between the consumer (database) and the provider.
I would then place the proxy in the DMZ - and on behalf of the consumer :
- the proxy would set up an SSL connection to the provider
- receive the plsql/jpub generated XML
- inject a WS-Security header in the SOAP envelop
- adjust http headers (especially HOST & Content-Length)
- send the new SOAP message to the provider
- receive the response from the provider
- send the response to the consumer

It's not a real proxy in the sense that it proxies the complete http traffic. The proxy/gateway needs to alter the message and that wouldn't be possible if we would use SSL encryption straight out of the database :

Oracle rdbms --- http/s ----> proxy ---- http/s -----> WS provider (endpoint)

So instead we do something like this :

Oracle rdbms --- http ---> proxy (endpoint) ---> http/s --> WS provider (endpoint)
|                                             |
+->(set endpoint to proxy)    |
+-> alter soap message + set endpoint to WS provider

In order for it to work you need to run jpub and use the WS provider as endpoint.
Then load all the generated plsql packages and java classes.
Then set the endpoint to the proxy/gateway address before you consume the WS, like this :
exec referencedatawebservice.setendpoint('')
(in this case we are running the gateway on localhost on port 8000)
And then the proxy will send the soap message further on over http/s.

Anyway I've made my little proxy/gateway available for download here
It's only tested in combination with Oracle 11g rdbms & jpub - I used it and it works, but use it at your own risk.

How to run it :
- download the ws_proxy.cfg config file here
- download WSP.jar here
- open ws_proxy.cfg and adjust the parameters to your needs/requirements
PORT - server socket port the gateway binds on
SO_TIMEOUT - socket timeout in msec
LOG_DIR - directory where the proxy will place its logfiles
LOG_FILE - logfile name - the logfile will automatically rotate every 10Mb
SERVER_PORT - WS provider socket port (443)
SERVER_HOST - WS provider virtual hostname
WSSE_USERNAME - WS-Security wsse usertoken
WSSE_PASSWORD- - password for the above username
XMLNS - additional namespaces - these will be injected in the SOAP header - look at the xml jpub is generating based on the wsdl
- make sure the root/CA certificate that signed the WS provider's server certificate is available in java's certificate wallet
(typically placed in JAVA_HOME/lib/security/cacerts - check with the keytool utility - if the root certificate is not
available then the proxy won't be able to setup a SSL connection with the ws provider)
- start the proxy : >java -cp WSP.jar WS_proxy
Reading ws_proxy.cfg ...
Reading ws_proxy.cfg done !
Current size logfile = 260 bytes
Init done...
Create socket on port 8000...
Create socket on port 8000 done!
Waiting for incoming connection.

- connect to the Oracle database
- set the endpoint of the webservice to the proxy
- consume the webservice - all done !

I have an idea or two to make it better ... but for now that's all I need.


Worst recovery case ... ever !

Worst recovery case ... ever.

Oracle DMP files generated by 'exp' are not considered as a backup. In a sense that is correct.
If you run 'exp' on a live database, data in your dump file can be inconsistent. This can be an issue when you export for example tables with a parent-child relationship.
Or for example - while performing full exports and tables are using a surrogate primary key. Typically a sequence is used, but the sequences are exported at a different point of the export flow. This means that sequences can become out-of-sync with the table data. Anyway, some of these issues are handled by the CONSISTENT parameter.

The main reason DMP files became legacy is the fact that we live in a 24/7 world (or 25/8 for dba's & sysadmins) - and DMP files are just a snapshot of your data in time, and thus are basically useless from the moment the file hits your disk. They are only vaguely useful now when it comes to having a snapshot of your database object DDL.
I remember the days though, where we had databases that we could shut down during the night for a cold backup and a startup followed by an export ... those days are long gone.

However, somehow people are still very fond of the beloved exp/imp tools even as a backup.

I remember one case were I was asked to look into an import problem.
Import errors are mostly like this :
1. something is wrong with the database you are importing to - like permissions - and thus, those are easely solved
2. or something is wrong with the DMP file itself - and this is mostly the point where you feel your blood pumping, because, well, not much you can do about that ...
(unless of course you still have the source database, and you can redo to export)

DUDE contains a DMP api ... which means that it can generate 8.0.5 compatible DMP files. These dump files can be read by the Oracle imp tool, version 8.0 and above. What's even more - the API allows the generation of datatypes which didn't even exist in 8.0.5 and still it will work in 10g.
For example, a table containing the TIMESTAMP datatype, extracted by DUDE to a 8.0.5 DMP file, will import without problems using 10g imp.

Anyway - a couple of years ago, I was approached by a company that had lost their database *and* their online backups... all they had left was an export dump.
Now, if you've lost both your database and backup ... and all you have left is one export dump... well, then that dump file *becomes* your backup.
So close your eyes - imagine yourself in this situation  ... and then imagine encountering an IMP-00009 error half-way through your only dump.
I'm telling you - forget base-jumping - IMP-00009 in that situation gives you the same adrenaline high !
Now - the company approached me with the question - can you repair this ?
This is not the sort of thing you do on a daily bases - but I was intrigued so I agreed to have a look at it without garantuee.

It was quite obvious what had happened. They were running Oracle on MS Windows - I don't know for sure if they were using NTFS or FAT32 - but my guess would have been FAT as I did recognise the corruption.
It's something one might call cross-linked files where a FAT corruption results in 2 files sharing a number of clusters.
The symptoms are quite easy to spot - just open a file with a hex editor and you'll see parts of other files popping up in multiples of 512bytes, aligned at multiples of 512bytes.
I've seen it at least a handful of times at several sites running MS Windows where they experienced a sudden power-loss.
And on each occasion datafiles (or in this case, a dmp file) were partially over-written by parts of some random dll.
In case of - partitially - overwritten files - the conclusion is simple : data will be lost.

This is what the DMP looked like (over-simplified) :

| dmp header (see below)                                               |
| filler bytes                                                                     |
| CONNECT <user>                                                         
| TABLE "<name>"                                                     
| CREATE TABLE DDL                                                  
| INSERT INTO STATEMENT                                             
| NOF columns                                                        
| COLx type | COLx len                                              
| RECx data  1                                                       
| corruption - 512 bytes                                             
| RECx data  3                                                       
| RECx data  4                                                       
| corruption - 4096 bytes                                            
| RECx data  6                                                       
| RECx data  7                                                       
| STOP bytes                         

The header stores NLS characterset settings, version of dump file, type of export, etc.
NLS info might also be stored on a per column basis.
Anyway - the format is pretty straight forward (at least compared to database blocks).
The company was interested in only one table and this was a 3Gb DMP (full export) file.

Here's a snapshot of what the DMP would have looked like in a hexeditor (this is not the actual dump)

corrupt dump

You clearly see something is wrong - even if you don't know a thing about DMP internals - just look at the part that says 'This program requires microsoft windows' ... not likely Oracle would put that in their dumps ;-)

I actually managed to manually create a new DMP file with only that one table in it using a hex editor and cutting
and pasting only good parts - so the end result was a bit like this :

| dmp header (see below)                                               |
| filler bytes                                                                     |
| CONNECT <user>                                                        
| TABLE "<name>"                                                         
| CREATE TABLE DDL                                                      
| INSERT INTO STATEMENT                                             
| NOF columns                                                                
| COLx type | COLx len                                                   
| RECx data  1                                                                
| RECx data  3                                                                
| RECx data  4                                                                
| RECx data  6                                                                
| RECx data  7                                                                
| STOP bytes                                                                  

The dump file imported without spawning imp-00xxx errors - however, data was lost and it was quite hard to tell what the actual value of this dump file was for the company.
Especially as I had to cut away a large amount of data - I counted 30 corruptions of on average 8K and there were many more outside that one table.
It took me long enough too - about 6hours... it felt a bit like an ER surgeon cutting away damaged flesh.

I'm sure the handmade DMP file had its use for them - but they probably had a long way to go from there... cross-checking what they had lost, re-entering data and so on...

It was without a doubt the worst recovery case I had encountered ... ever ... no datafiles ... no backup ... corrupted DMP.


See DUDE primer for info

Get Support


Belgium :
Kurt Van Meerbeeck
ORA600 bvba
Cell : +32 495 580714

Denmark :
Michael Môller
Service & Support Manager
Miracle AS
E-mail :
Cell: +45 53 74 71 27

Latin America

Brazil :
Cell : +55 47 88497639


South Africa :
Kugendran Naidoo
NRG Consulting
Cell : +27 82 7799275