Oracle to buy Sun
Oracle announced today that they will be buying Sun Microsystems. Their plan is to produce 'pre-integrated' systems where everything from the bare metal, silicon and spinning rust up to the application front end is from themselves. The idea is that customers will save money on systems integration costs but reliability will go up.

Labels: sun

  ¶ 7:14 AM 0 comments

  Ada Lovelace Day
Apparently today is Ada Lovelace Day. Ada Lovelace (aka Augusta Ada King, Countess of Lovelace) is widely recognised as the first computer programmer. The daughter of Lord Byron, she was encouraged away from her father's dissolute lifestyle by her mother, who encouraged her interest in mathematics. Ada excelled at mathematics and became a friend for Charles Babbage.

When Babbage created his Difference Engine, the first programmable computer, Ada wrote out the method to configure it to calculate Bernoulli numbers. Hence her appellation as the first computer programmer.

Ada Lovelace day was created by Suw Charman-Anderson as a way to promote women in IT.  ¶ 5:23 AM 0 comments

  New Blog - Stephen's SAP Blog
As I'm now beginning to work on SAP I've started a blog about it, mostly just somewhere to make notes about interesting/useful things I come across. If it also helps anyone else then great but if it doesn't then no worries.

One of the things I have noticed, comparing Oracle with SAP, is that whilst for Oracle you can find online free resources at pretty much any level (both official Oracle sites and individual sites and blogs) in SAP there seems to be loads of 'salesy' type sites telling you how wonderful SAP is and how it will revolutionise the way you do business (presumably for the better) and some very in depth technical sites (mostly forums), where you're sunk if you can't get in an manually edit the data and code, there's nothing anywhere in between. There certainly don't seem to be any how-tos, any pages that seem to be telling you howto when they come up in Google searches are actually saying "If you want to find out how to sign up for our course and pay us a lot of money." Of community there seems to be little, aside from the aforementioned bit twiddler forums.

http://stephenssapblog.blogspot.com/

I admit I'm a bit Leonard of Quirm when it comes to blog names.

Labels: SAP

  ¶ 5:21 AM 1 comments

  Old tech you'd like to see updated and rereleased

Note an Oracle post, but I know a lot of DBAs and into tech as well.

Yesterday I found my old Psion3 mx (which I used before having to switch to Palm handhelds due to work). I had a play and was reminded why I liked it so much. The key advantage it had, and still has over many more modern devices, is the size. It's small enough to fit in a suit jacket or coat pocket whilst being large enough to have a keyboard you can actually type on and a decent screen size. It also has a decent battery life and, as it runs off a couple of AA batteries, you don't have to worry to much if you're going to be away from the mains for a while. The Psion 5 (that superceded the 3 in 1998) had a larger and slightly more usable keyboard and screen but the size (about that of the Eee PC) meant that it couldn't fit in a pocket so reduced it's utility.

I then got to thinking, how would I improve the Psion3 taking account of the changes of the 10 years since it went out of production?

Here's what I think:

• Stick with the same basic form of the Psion3mx, same size screen and keyboard.
• More memory and storage, at least 8GB storage.
• Increase screen resolution and go full colour.
• Add graphical viewer (with zoom and pan/scan) and MP3 player/recorder.
• Add headphone and microphone sockets using standard 3.5mm jacks.
• Add Bluetooth and, possibly, WiFi (maybe provide WiFi through an external 'holster' with it's own power supply, to reduce drain on the PDA batteries).
• Replace existing RS232 interface with USB for sync/datatransfer to PC and connection of external devices.
• Remove the divider in the battery compartment so the batteries are in direct contact with each other (it has a tendency to break so you have to wedge a piece of aluminum foil between them to make the electrical contact).
• SD card slot.
• Add rechargable, removable Lithium (or similar high capacity) battery (possibly as external 'holster' option) to replace/supplement existing AA batteries. Allow charging both on an off the device.
• Update PC connection software for full support of current operating systems, including Linux, and PIM software. Allow wireless sync/datatransfer over Bluetooth.
• Support for ODF (Open Document Format) in the on board wordprocessor and spreadsheet. Include converter to other formats in PC software.

So, what piece of old tech would you like to see updated and rereleased?

  ¶ 12:33 AM 0 comments

  Dataguard, documentation/scripts for non-DBAs during failover
We're looking at implementing Dataguard as part of an implementation of Documentum (a document management system from EMC) and I have been asked to look at producing documentation and scripts for non-DBA users to use during a failover. The actual failover of the database itself will be handled by our DBAs, this is for the sys admins, network admins, application admins &c who may need to do things during fail over.

I'm currently going through the Dataguard concepts guide and have found some other documentation on OTN but was hoping that someone with more knowledge of Dataguard could point me towards any documentation that might help with the non-Oracle side of failover. If anyone has any documentation/scripts like this they have prepared themselves that they are prepared to share and I could adapt to our environment then I'd be very grateful.

Here is the background:

We have a pair of IBM p590 servers, running AIX 5, currently sitting in different parts of the same datacentre but (hopefully) one will be moving to a different site in the near future. Each will run both primary and standby environments, one running the standbys for the primaries on the other. Each environment will run in a virtual server.

The Documentum service runs in an N-Tier configuration:

Presentation layer/
Application/Business logic layer
Metadata/Storage layer

The presentation layer is either a fat client running on local PCs or a web front end running in Tomcat that the users can access via a browser. The application/business logic layer is the Documentum application running in Oracle Application Server 10g. The metadata/storage layer consists of an Oracle 10g database and filesystem storage on IBM storage devices.

Additionally on the application/business logic layer there are interfaces to SAP provided by Documentum Services for SAP running on a separate Windows 2003 server and scanning stations and servers, these connect to the Documentum application.

Users do not access the database directly, nor do any other services, all access is via the application.

When a document is added to the repository (via the presentation layer, Services for SAP or scanned) it is rendered to PDF and added to a filestore on the storage (i.e. the file is saved to a directory), metadata about the document (title, location, categories, key words &c) are stored in the Oracle database.

The filestore will be synced from primary to standby by either IBM Flashcopy or IBM Metro Mirror, the metadata will be synced by Oracle Dataguard. Due to the way Documentum handles inconsistency between the metadata and the filestore (i.e. documents in one that are not in the other) the metadata sync will always lag behind the filestore sync (if there's metadata for a non-existant document then the metadata can easily be found and deleted but if there's no metadata for an existing document it's a bigger job to find the document, an analogy would be looking up words in a book's index to find them in the book vs checking each word in a book to see if it's in the index).

Edited to add (as a result of comments on Experts Exchange):

The failover of the database itself will be handled by the DBA team. I have been asked to produce documentation for any changes that need to be made outside the database. All the documentation I can find ignores anything outside the database. Clearly there will need to be activities outside of the database when a failover or switchover takes place, the obvious one that comes to mind is pointing the clients to the new server. I haven't been able to find anything about that.

Possible solutions that come to mind for pointing the clients to the new server are:

• Edit the TNSNAMES.ORA files. This would be possible in this set up as we only have a few boxes (application servers, scanning servers and Services for SAP servers) that connect to these databases. If the number of boxes increases significantly then it might no longer be possible, anyhow I prefer to avoid manual processes as I know how easy it is to miss something when you're under pressure.
• Use a unique hostname for each database and have a DNS entry (so if database ORCL1 is on server bigprod, IP address 10.10.10.10, then we have a DNS entry orcl1 which resolves to 10.10.10.10 and use that in the Net8 settings for the ORCL1 service), when failover happens we just edit the DNS settings to point to the IP address of the standby server. This was proposed in another project and may get implemented. The downside is it means involving a directories person in a fail/switch over.
• Use OID for database names resolution. Probably the ideal solution but also means implementing another extra technology, and paying for it. On the other hand we do have a plan to implement OID at some point in the future so we will probably use this eventually.
• Specify multiple ADDRESSes in the TNSNAMES.ORA file so if it can't reach the primary it will try to standbys, if the failover hasn't happened yet (primary is down, standby hasn't changed to primary yet) then it will have to time out. As we're planning regular switch overs we'd have to make sure it times out quickly for those times that the first server in the list is the secondary.
  

Labels: aix, dataguard, failover, IBM, oracle

  ¶ 4:37 AM 1 comments

  Where should data be validated?
This just came up on the mailing list for my local Linux Users Group following last night's meeting (which I didn't attend), the original mail and my response is below:

> In the pub, there was an interesting conversation going on regarding
> validation of data in databases.
>
> Excuse the omissions, as I said, it was overheard
>
> Someone brought up the point that in databaseX If say, you have a
> varchar field set to a limit of 10, and put 26 chars of data into it
> databaseX silently truncates it.
>
> So my question is, in your opinion, should it be up to the front end or
> the database to do this kind of data validation?
>

I'm a Database Administrator, mostly working with Oracle. The reverse of this problem (data validated in the client but not in the database) is something I come accross a lot. Most RDBMS/ODBMS/ORDBMS, certainly any that can claim to be enterprise class, will have functionality to implement data validation (key constraints, check constraints, strong datatyping, triggers &c). Unfortunately the majority of software vendors, in my experience, seem unwilling to use this functionality with their products. The most common excuse is 'database independence', which in reality translates to their app, instead of working well with one *DBMS, will work badly with three or four. They want all the data validation to happen in their application so if the business rules change you have to pay them to update the application rather than just getting your DBA to change the rules in the database, I've met a couple of vendors who have insisted that lists of values (where the user picks a value from a list) have to be hard coded in the application rather than generated from a lookup on a table so everytime you need a new value you have to pay them to write a patch.

To get back to the original question. I'd say that validation _must_ be done in the database layer, _may_ be done in the application layer and _could_ be done in the client layer (I differentiate between application layer and client layer as, in my world of work, N-Tier is very common). The database, however, when it gets invalid data should raise an error/exception which it then propergates to the application layer for handling. The database may carry out some action as a result of that error/exception (writing it out to a log file, the error plus the data that caused the error, is often a good idea) but it should pass it back to the application, what the application then does with it is up to the application, usually you'd want it passed back to the user, maybe translated to a more human readable message if necessary.

I remember attending a presentation at UKOUG Conference a few years ago about implementing business rules via constraints in the database, a very useful presentation.  ¶ 3:48 AM 3 comments

  Preventing record deletion
This entry is partly an aide memoire for me, partly to try to get something that has been keeping me awake for the past hour or so out of my brain so I can sleep and partly in the hope that someone can suggest a way forward.

A quick bit of background. Until April 06 most of our major systems were looked after by an external Faccilities Management company. In April 06 IT was kind of outsourced to a joint venture company, support of the systems transferred to that company and a couple of the DBAs transferred in as well under TUPE. A reccurring problem on one of the systems is that the users keep deleting records which, by law, they must not delete so we (actually one of the DBAs who transferred in who is responsible for that system) has to restore from backup to another machine and copy the deleted records over (she's tried using LogMiner but finds it too unwieldy). The core problem is that the application is faulty and lets the users delete the records when it shouldn't.

The application is closed source from an external vendor so we cannot change it to make it prevent the users from deleting the records. Due to the political environment we've got zero chance of getting the application changed to one that does stop them (the people with authority to replace the app don't have responsibility for the costs of continually putting right the problems it causes and vice versa).

The application logs into the database as the schema owner, individual user authentication is handled within the app, so we can't just revoke delete privilege from the users.

It occured to me just (this is what has been keeping me awake) that we might be able to fix it using triggers.

First I thought of a before delete...for each row trigger to archive to another table before it deletes them so at least restoring the rows is just a case of an insert statement. Then I thought thst times when we might legitimately need to delete a record are massively out numbered by the times we want to prevent a record being deleted so preventing deletion would be much better. Now I'm thinking we need and instead of delete trigger so if someone attempts a delete it won't let them. According to the documentation instead of triggers can only be applied to views so we might have to rename the table and replace it with an updateable view witht he original tablename, not sure how that would impact on the support/maintenence of the app, we might not able able to do it. Assuming we can work around that problem the next issue is what we do when the trigger fires. Do we do nothing, log that somone tried to delete a record or do we raise an exception?

Any thoughts and/or suggestions gratefully received.  ¶ 4:33 PM 7 comments