Roads ? - where we're going, we don't need roads ...
ORA600 | Last resort Oracle recovery
ORA600 content Content RSS
Oracle ORA600 News RRSS Oracle News RSS
ORA600 blog Blog RSS
ORA600 blog Blog Atom


User login

I love slideshare

Some idea's are so cool I'm just jealous I didn't think of them myself. With all the crappy, time consuming and totally useless social network sites (linkedin being the exception) it's refreshing to see something like
The idea is pretty simple - share your slides ! In fact, now that I think of it - it's basically like youtube - but with slides - or presentations.
Just create an account - upload your presentations and slideshare will convert your pdf's or ppt's or whatever format into a flash driven presentation. Which at that point, you'll be able to embed them into your website.
Just for fun I uploaded the presentations I did for UKOUG2007 and UKOUG2008. The 2007 one got selected by the slideshare editorial team as a showcase on their technology page. Way cool!

Your presentation Extending the Oracle SSO is currently being showcased on the 'Technology' page by our editorial team.

It's likely to be there for the next 16-20 hours...


- the SlideShare team

I'm pretty sure their editorial team is a couple of uber-geeks as the selected presentation 'Extending the Oracle Single-Sign-On server' is … well … kind of geeky … and I even dumb'ed it down a bit. It's all about PKI, certificates, authentication, reverse proxies, apache and … fitting Oracle's SSO server somewhere in between Wink
It's also loaded with PowerPoint's custom animations, so you actually need to download it and run it in PowerPoint to get an idea of what I'm aiming for Innocent  … so the editorial team must have been geeky enough to download it and let all the animations spin their heads around.

The content itself is based on the work I've been doing at a customer's site - an insurance company - that had very specific requirements regarding their SSO architecture. They had made Oracle their choice of platform as they had been using the Oracle RDBMS for about 10years (I was the one who set it up back then). So it was only natural that when they wanted to 'web-enable' their back-office applications, and give their - and third party - brokers access to some of their back-office applications, they chose Oracle's Internet Application Server.

As all their business logic was built within PLSQL - the fat database model  or database centric approach as Toon Koppelaars calls it - it was quite easy to develop a web front-end for it. The thing that they struggled the most with, was integrating Oracle single sign on with their fancy and expensive vpn/ssl boxes. Not only that but, they needed to integrate Oracle SSO with a federated authentication infrastructure, so that third party brokers, who were already authenticated through a third party identity provider could automatically logon to their applications, mapping third party identities to their identities.

And we're only halfway there - they also wanted to run their own certificate authority (thank god for Oracle CA … are we the only one actually using it in production ?  Cool). Using their own CA they wanted to generate client certificates so that internet brokers could authenticate themselves with their own client certificate.  But they also had the choice of using their Belgian passport to authenticate themselves against Oracle SSO. And because the insurance holding has multiple companies, the logon screens needed multiple look-and-feels … oh and multi-language support of course.

So for the last couple of years I have been busy pulling Oracle's SSO server inside-out, integrating it with a juniper 4000 VPN/SSL series - which we threw out in favor of an apache2 based SSL terminator and reverse proxy. The juniper box was just holding us back. I did have to write some custom apache filters for Oracle Portal. I think Oracle Portal generates some of the weirdest html I've ever seen… and mod_proxy_html wasn't up to the job for that Smile

I've also integrated SAMLv2 with Oracle SSO. I had to write a java based SAML proxy to get it done, because the SAML token needed to be transferred over a HTTPs connection using a client certificate authentication - but it works like a charm. This was only done this year - so it's only slightly mentioned in the presentation. We had a kind of DIY federated authentication before that, as the third party was not finished implementing a SAML IP themselves.

So I wrote various software packages that tied it all together, like :

  • User admin packages : delegate user administration - this is a pure PLSQL API. This is our own version of OIDDAS on steroids. It can
    • create/delete users 
    • change user passwords - force password changes - enforce minimum password requirements
    • change/get user LDAP attributes
    • do identity bootstrapping for federated authentication
    • request/revoke client certificates for users - update the CRL
    • restrict access to applications depending on whether they are on the LAN, internet or private network (third party) 
    • synchronize Oracle SSO users with users of a MS IIS/ASP/Sybase based application
  • login proxies and plugin's for SSO, which enables us to
    • login based on client certificates/password for internet users
    • login based on eID certificate for internet users
    • login based on SAMLv2 token for third party brokers
    • login based on a DIY federated authentication for third party brokers 
    • login based on username/password for LAN back-office user
    • login via Oracle SSO to a Microsoft IIS/ASP/Sybase application (oh yes - we did it!) 
    • OS/browser detection - quite handy for VISTA and webforms, so we can switch a webforms config transparently if we detect vista. For example - the url /forms/frmservlet?config=app is rewritten to /forms/frmservlet?config=va_app transparently when vista is detected
    • User-based Oracle Webforms config - we can also change Webforms configs on a user per user basis. This is quite handy for debugging or if one specific user has troubles. For example /forms/frmservlet?config=app is rewritten to /forms/frmservlet?config=d_app for certain users. We do this by de-obfuscating the sitetoken,which will give us the URL, lookup the SSO user and see if we need to change the URL, rewrite the URL and then obfuscate the URL back to a sitetoken. Once the sitetoken reaches mod_osso it will redirect to the modified URL.
    • It does lots more things like multi-language detection, different look-and-feel depending on which apache virtualhost your coming from etc
  • Certificate manager - we use the Oracle CA (OCA) quite extensively - but the user and admin screen are … let's say … not so user friendly … and you can't adjust the look and feel just the way you like. So I wrote what I call a certificate manager.
    • The certificate manager uses openssl to create a private key and certificate request on the infrastructure server. This, as opposed to using activeX, javascript and the browser's wallet. The certificate manager picks up requests from our user admin package.
    • The certificate manager then submits the request to the OCA.
    • Someone from the company then approves the request (also through the certificate manager)
    • And then the certificate is packaged in a password protected PKCS12 wallet with the private key and is shipped to the broker in a secure way (which I won't go into details here)
    • The certificate manager uses a bunch of techniques and also has a PLSQL wrapper as an API - so there's no need to use the actual OCA webpages. This means we can easily delegate administration. For example  - if an employee  of a broker office leaves or gets fired, the head of the office can revoke the client certificate through his application, which will in turn update the CRL and disable the user in OID. Of course - approval of client certificates is always done centrally by the head office.

Anyway - that's in a nutshell what the presentation is about. Never mind all the DBA stuff and java programming … working with these technologies was awesome … and it'll help me a grea lot in my DUDE work when I finally finish support for unloading data encrypted using Transparent Data Encryption (TDE) Laughing


Oh - here's the presentation embedded ... but you'll need to download it and run it powerpoint to get the animations running !


Extending Oracle SSO
View SlideShare presentation or Upload your own. (tags: oracle single)


How to avoid costly data recovery ?


A couple of days ago I received Daniel Fink's presentation 'Never a DUL moment - how to avoid costly data recovery'.

Today I've found the time to publish the slides on slideshare  - go forth and recover !

Nevera Dul Moment
View SlideShare presentation or Upload your own. (tags: oracle dul)

Taking another blow in the face

Well well well - I reckon this month is 'poke the belgian in the face' month !

I knew my last post was going to be trouble ;-) That'll teach me (I'm sure a lot of people are having a laugh now!)
I kind of suspected someone, somewhere in the world would be offended by it. The world has become a small place, and cultures differ.

Hey, I like to eat horse meat stew with my fries and mayonnaise on the side - I know this is completely not done in some parts of the world - some prefer ketchup Innocent

In parts of the world it's perfectly legal to carry guns and it's part of their legacy, it's part of their constitution - so I respect that.

What I don't like is my situation being exploited by certain people who are frustrated with the UKOUG.

This certain person - who we will give the fictional name 'Dom' - turns my small incident into a 'vicious attack'… man - I'm lucky to be alive if I read all this. He then goes on into explaining, 'how everybody is against Americans'… he and his wife were verbally threatened when they were once abroad (they were called 'yanks').

Well - what can I say - I'm pretty sure that's not a pleasant situation when you're in a foreign country, I can't deny that. But I actually happen to like Americans as some of my best and dearest friends are yanks  ;-)  (Which is not an offensive word where I come from - but again cultures differ)

UKOUG 2008 was a blast

It flew by so fast I didn't feel like wasting my time blogging about it last week. I don't know how the other guys do it, but I had too much fun with other things.

I could give you a detailed description of all the sessions I followed but that would be rather dull Wink

In my opinion there were a couple trends emerging:

1.       The rise of Oracle VM

I think this will be one of the biggest things in the near future. It’s a no brainer. Companies are already in love with VMWare – however, anyone who has half a brain will never run an Oracle production database on it, as the combo is simply not certified. In fact, according to metalink note 249212.1, Oracle has not certified any of its products on a VMWare virtualized environment. Oracle support will only provide support for issues that either are known to occur on the native OS, or can be demonstrated not be as a result of running on VMWare.

UKOUG 2008 - day 0

It's almost 3'o clock at night - I arrived earlier today in Birmingham.

By sheer luck - both me and Doug Burns arrived at the Jury's Inn at the same time... needless to say ... we ended up in the hotel bar 5min later where we joined the Pythian crew (including Paul Vallee!).

I gave Doug an ORA600 polo shirt 3y ago - and I noticed the print is totally worn off ... funny enough, it actually works as a gimmick ... it encountered severe corruption. Wink

 doug & paul

Here's a picture of Doug & Paul


Anyway - It's great to be here again - but as it happens I got called for a recovery emergency and I'll probably be working all night ...


Kugendran Naidoo awarded best speaker and best innovative presentation at SAOUG

It seems that real-life (and larger than life) database recovery stories always seem to lure in the crowds.
It's like a car accident - people slow down, have a look and hope it never happens to them (and causing additional traffic jams while doing so).

I mentioned in my previous blogentry that Daniel Fink will be presenting some real-life recovery stories at RMOUG on the 21th november, so I'm pretty sure it will generate some disaster tourism Wink
One of the other ORA600 partners - South African's NRG Consulting - delivered a similar presentation last month at the South African Oracle Usergroup (SAOUG) :

DUDE Where is My Data - Database Recoveries and Data unloaders by Kugendran Naidoo

Kugendran is one of the brightest people I know - so I know he's a good presenter.
But Kugendran sure hit a home run that day.
He received the 'Best speaker award' for day 1 *and* the award for "Best Innovative Presentation 2008" :

SAOUG award

Two thumbs up !  

The SAOUG also made all presentations available on their website here.

And they've put a massive amount of pictures online.

Man - this sure looks like a class A event and I regret not being their with Kugendran - but who knows, maybe I'll submit a paper next year.


Never a DUL moment with Daniel Fink

On november 21st, Daniel Fink, my US partner and fellow Oaktable member, will be giving a presentation about his data recovery experiences at the RMOUG Quarterly Educational Workshop.
Dan has helped out several US based companies in some very complex recovery scenarios, where a data unloader like DUDE was the only option left.

Abstract :
Never a DUL Moment: How to Avoid Costly Data Recovery
Dan Fink

One of the worst situations is when you have a database in need of recovery...and find that you don't have a usable backup. One option is to use a Data Unloader (DUL), a costly tool/service that is able to extract data from a down and unusable database. However, the best option is to avoid this situation completely through recovery testing, database refreshes, and proper security. This presentation focuses on common reasons for data recovery (worst practices) and how to avoid them (best practices).

You'll find more info at the RMOUG website ( and here is an overview of the agenda, which looks very promising with speakers like Steven Feuerstein and Tim Gorman.


Carel-Jan Engel on Citroen high-availability

A couple of weeks ago, my wife and I, were driving on the E314 when suddenly we had a tyre blowout.
This is never a pleasant experience - especially in the middle of the night while driving to the airport to catch a flight !
I know now I'm ready to work the formula one pits, as me and the wife exchanged that wheel in a record time and 10minutes later we were driving again !
(ok - my hands were bleeding and my jacket looked like I came out of a coal mine)

That reminds of the next video made for the 2008 Miracle Oracle Open World (moow2008) by Carel-Jan Engel :

Ps - this does not apply to Alfa Romeo's - it's *OK* for an Alfa to have - several - warning lights flashing and still continue driving ... Cool

In the spirit of Halloween - here are some other scary movies Laughing

OOW2008 Highlight

OOW2008 highlight

I know, I know - many will consider the launch of HP Oracle Exadata storage server as the highlight of OOW2008.
I agree - it looks pretty exiting if you are running a datawarehouse firing a gazillion pq slaves at it. It put a smile
on my face to see that one of the beta testers was the ABSA bank in South Africa as I had worked on their datawarehouse 8years ago.
My buddy Kugendran was one of the lead DBA's.

Anyway - *the* highlight for me was the fact that Oracle had gotten their cool back.
For years now - I think, it started around the last OOW in Europe - it was Paris in 2003 - Oracle have produced lame white T-shirts and polo shirts.
I don't know who's in charge of their clothing line (if you can call it that) - but wake up and smell the coffee... no self-respecting DBA dares showing up at work with a white and red t-shirt with a massive bulls-eye logo to top it off.
Clearly, from a marketing perspective, the goal of giving away (who would buy them?) such shirts, is that people would wear them and thus, promote your product.
It got even worse when they thought - 'hey - this fleece is so white and bright, let's make it completely red!'.
Now - unless there's an Alfa Romeo logo on that fleece ... who would wear it?

As an experiment, Doug Burns, tried on one of these Alfa red fleeces at OOW2007.
Clearly, as shown on the picture below, some people were utterly shocked.
The girl's reaction - on the right - tells it all (and no - she is not dancing ... she's is starting a sprint)

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.


See DUDE primer for info

Product is discontinued.