Roads ? - where we're going, we don't need roads ...
Month of December, 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 December, 2008

Hunting down partitioned IOT objectid's

I'm so glad you can't sub-partition an indexed organized table (IOT).  (no we do not want this feature in 12g)

I was looking into my code for unloading partitioned IOT's.
Unloading IOT's (with or without overflow segments) is easy. It's keeping track of all the meta-data that's starting to get difficult.

DUDE allows you to unload tables as follows :

  • Table by table based on owner and tablename or objectid
  • Shema by schema - so based on schema/owner name
  • Tablespace by tablespace - and this is based on the data objectid of the table

You basically can define three different types of object id's

  • There's the objectid itself, which defines the object uniquely in most if not all base dictionary table
  • Then there's the data objectid (or to use an expensive word 'data layer objectid' - which is the objectid stored within the block itself. Mostly it equals objectid, but if you for example move or truncate a table it'll change. A data objectid can be NULL - for example the table part of a partitioned table does not have a physical segment - it's the partitions that actually contain the data
  • And then there's the base objectid - which is the objectid of the base table of an object … or a pointer to another object to put it simple.

So - if you're unloading a complete tablespace, you are going through the data objectid's found in the tablespace. And using those physical objectid's you have to move your way up to the table objectid using the metadata in the base dictionary tables.

Why ? Because you need the table definition (name, columns, datatypes etc). This is quite simple tho.

But wait, sometimes it can get messy. Let's imaging a partitioned IOT using overflow segments with <n> partitions.
What you get is the following meta data spread around several base dictionary tables :

  • 1 table   :  objectid  is <t>,  data objectid is null, base objectid is <o>
  • <n> table partitions : objectid is <t+n>, data objectid is null, base objectid is <t>
  • 1 index   : objectid is <i>, data objectid is null, base objectid is <t>
  • <n> index partitions  : objectid is <i+1>, data objectid is <i+1> initially, base objectid is <i>
  • 1 table (overflow) : objectid is <o>, data objectid is null, base objectid is <t>
  • <n> table partitions (overflow) : objectid is <o+1>, data objectid is <o+1> initially, base objectid is <o>

As you can see - the table is linked to the overflow table and vice versa using a base objectid.
The table partitions are linked to the table, the index is connected to the table, the index partitions are hooked up to the index, and the overflow table partitions are linked to the overflow table… all using the base objectid's. And only two types of objects have actual physical segments - the index partitions and the overflow table partitions.  So if DUDE hits a data objectid of an IOT index partition it can traverse up to the table object and get the definitions.

Pfew … the whole thing made me think about the Front 242 song 'Headhunter' - '1 - you lock the target - 2 - you bait the line - 3 - you slowly spread the net - 4 - you catch the … euh objectid ?'

I'm sure there are a couple of Oracle nerds out there that can appreciate this clip for 1988 !

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 ...


See DUDE primer for info

Product is discontinued.