Tuesday, January 16, 2007

New project, time for a new job?

A few years ago the company I work for purchased another company of almost the same size effectively doubling our size.

During this acquisition, we in the IT group were told about some of the systems that were in place, but luckily other than the core applications we didn't have to do anything about the older systems at all. There were consultants who managed the systems and the servers were housed far from the IT group. An "Out of Site, Out of Mind" deal.

Well, yesterday, that changed for one particular system. Our manager got a call that the 3 person consulting company that looks after the "data warehouse database" for 17 of our sites has put decided to not renew the contract which expires in 90 days, effectively dropping the management of the database into the IT group's laps. This has happened before, we took it in stride. Until the DBA's got access to take a look at the DB yesterday afternoon. Oh boy... I checked to make sure the headhunter firm I use had an up to date CV and I removed my restriction on not wanting to work in the United States.

Oracle database version 7, with an astounding 3,319 tables in a single schema with only one primary key constraint created on one table. No database referential integrity at all. 891 procedures, 4319 triggers, 771 functions (no packages). OS is OpenVMS running on some really old Digital Equipment hardware. The front end uses proprietary VMS DCL scripting coupled with a Fortran VT100 terminal user interface. Total database storage across the 8 Vax/VMS machines in the cluster - 636 gigabytes with 432 gigabytes of that being the database (according to dba_segments). Apparently backups are cold backup's that start on Friday night at 10pm and finish early Monday morning if there were no problems, it is noted that sometimes the database can go 4-6 weeks without a backup during the heavy work season because the data is needed 24 hours a day 7 days a week and the database can not be shut down. At least it is running in archive log mode. Sites send data to the database via file transfer every morning and the data is loaded some what manually by some clerks at one site.

Management asked us how long it would take to convert the data to an oracle 10g database (or 11g) on linux so a development team can be put together to build a new front end because apparently they sites can not live without that data that goes into that database, once the data is converted to 10g on Linux then we can look at redesigning the entire system but downtime has to be minimal. I was handed the "documentation" that the consulting firm has done to date, and was promised the bulk of the remaining time the consultants have on contract will be dedicated to writing better documentation. I would hope so, because the documentation I tried to read last night looks like it was written by a 11 year old in a hurry to complete a school work assignment. We also got a 3 inch binder stuffed full of about the last years worth of user requests, user filed problems and "bug" tracking that the consultants have - nothing electronic at all, all done by hand. I have until this coming Monday to come up with a realistic plan on converting the data to oracle 10g.

This project scares me, this is far past the "take it as a challenge" or "show off your skills" that the management is throwing around, I see this becoming a career ruining and stress related heart attack inducing project. Anybody out there looking for an experienced level headed Oracle DBA with years of experience in both DBA skills and development skills? :)


I think this project will be the seed of hundreds of blog posts.






9 comments:

Anonymous said...

This sounds like a very interesting challenge. Don’t run from it. Remember it you solve it you’d be worth you weight in gold..

Go for it.. as long as you have management backing on your solution.

Peter K said...

At least Management recognized the need to upgrade to 10g. They could have well told you that your group has to manage it (as it) because there is no time and budget to upgrade for the next 3 years!

Take a deep breath and clear the head. Things might not be too bad once you get into the details...

Noons said...

Aye! Sounds like a world of hurt coming your way...

Still, like the other folks said: they could have asked you to move the whole lot to Unix and keep it at release 7.

Ouch...

Stay cool.

Herod T said...

We have spent the day looking at this system.

It is much worse than we originally thought :)

The good news is we are doubling the storage on our local SAN to take this database, the order for the disks has already gone into the manufacturer.

The actual technical work of moving it are not that difficult, I have already moved in about 400 tables (no data) and all the corresponding triggers and the source code, with only very few errors that were unexpected. This was to my laptop just for testing, taken in small doses the moving of the data should be trivial.

The killer comes from the complete lack of documentation and data relation.

I have already discovered a lot of relations that I will be enforcing when the data is coming in.

There were 96 tables marked %_BAK or %_BACK% or %_DELETEME%. Almost 20 gig of data that can go away, and that is just what is visible when I was building the parameter file for the export.

Anonymous said...

These comments have been invaluable to me as is this whole site. I thank you for your comment.

Anonymous said...

Our selection ofwow gold bean bags, bean bag chairs wow goldand home theater furniture are the mostwow gold comfortable pieces of furniture that will ever invade wow goldyour home. Set up your media room with coolhome theater furniture and bean bag chairs wow goldand your room will become the envy of wow goldall your neighbors. We even have some great accessories to complete your brand new LoveSac, including wow goldbean bag chairs slipcovers, LoveSac ottomans and more.

Anonymous said...

INDIANAPOLIS – Searching for wow golda truly world of warcraft goldoriginal buy wow goldholiday cheap wow goldgift, one that wow power levelingcould bestow a wow powerlevelingbit of immortalityAge of Conan gold on a lovedAge Conan gold one or aage of conan power leveling friend?If so,aoc gold Purdue University aoc power levelinghas the goods: aoc levelingThe school iseve online iskuctioning eve iskthe naming rights toCity of Heroes influence seven newlycoh influence discovered bats City of Villains infamyand two turtles.COV infamyWinning bidders willlineage 1 adena be able to link alineage adena relative, friend or 2moons dilthemselves to an animal2moons gold's scientific Last Chaos Goldname for the ages.The first of the nineGW goldauctions began Monday, Guild Wars Goldwhen the school put up for grabsRagnarok Zeny the naming ro zenyrights to a tiny gold and black Rappelz goldinsect-munching bat found in CentralSecond Life Linden America.second life moneyThe winning bidderTabula Rasa Credit will be announced tabula rasa creditsjust beforevoyage century gold Christmas,potbs doubloon said John pirates of the burning sea goldBickham, Potbs golda Purdue professor of forestry and natural resources who discovered or co-discoveredpirates gold the nine speciesArchLord gold.He expects the auctions buy ArchLord goldto attract wide interest, EverQuest platwith the chance to include buy eq golda person's Latinized name inEverQuest gold a new species' scientificeq plat name — a tradition that dates to theeq gold mid-18th century."Unlike naming eq2 plata building or something like that,EverQuest 2 plat this is much more permanentHero online gold This will last asbuy silkroad gold long silkroad goldas we have our society.

abercrombiefitch said...

Thank you for your article to share with us, our online store Nike Outlets, have a good product Nike Air Max shoes, interested welcome to come in and see 2011 Cheap Nike Air Max Online Store, Hot products: Nike Air Griffey Max 1 Shoes. Best Nike Shoes .

baele said...

So how is the Nike vapor soccer shoes changed? Visually. the new Vapor football boots have undergone the same new paint work as the Nike Mercurial Superfly boots and now feature the updated asymmetric, Nike soccer cleats fluorescent.but now it's time to take a look at the boot Nike mercurial vapor . - the original Nike Speed boot!