Conversion of '07 - First stage finished

The first export & load process finished. 304 gig of data transfered and loaded in 188 hours, only 15 tables had problems and were all easily corrected and scripted around for the next test. We had some network problems too that caused us to rewrite the process script on the linux server to pull the file size that was created on the old server and verify it against what actually appeared on disk.We are going to take a different approach to our transfers for the next test, we are going to have 5 "pipes" to transfer data over as the network guys promise to have 4 VPN's connected by the end of next week, simply will be 5 different IP addresses to transfer too so we can manually split the transfer up over the dedicated pipes and the shared pipe. The network guys estimate we should be able to cut our network transfer times by about 45%. The first shipment of new drives for the SAN has been shipped, the SA says it will be installed and functional by the 2nd week of February. I have assigned the PL/SQL verifying to 2 applications support people who had the unfortunate bad luck to look bored at a meeting, and I have delegated the rewriting of the .sh that creates the scripts to precreate the tables before the import to a developer here. I put an SA in charge of co-ordinating the hardware and OS installations so I don't have to. I have come to realize, being the project leader is a good thing, I don't have to do it, I can get someone else to do it and just verify they did it right.

More good news on the hardware front. Our broker found us hardware sooner than expected and HP came up with a supported OS structure that will run what is necessary for the contractors to move/port the application to a different apps server. Timing is going to be an issue though, our "soft" deadline is quickly approaching and already the MS project estimated finish date is 12 days behind our deadline. The consultants rewriting the front end have hit a major roadblock pushing their time estimates out over a week. As I said though, it is a "soft" deadline as the old system will still be functional but the contractors will become extremely expensive to continue on.

Things are still going good though and I confident that the oracle end of the transfer will be smooth.

Conversion of '07 - Still Processing

Well the ftp and load process is still going at 130 hours, but so far very few errors, and the few that are happening are easily by passable or fixable. I am very pleasantly surprised. Our estimates have greatly increased due to the higher than expected bandwidth use by the users during business hours. If everything continues on track the last file should be sent via FTP on this coming Wednesday morning.

I used a combination of DBMS_META_DATA and CTAS , extracted the create DDL and created the entire database sans data on my laptop and have gotten all of the DDL for the PL/SQL ran and the majority of the objects created. A boatload of errors and uncompiled PL/SQL, but at least it is all there.The project from the oracle end is ticking along nicely.

From the hardware end, well that is another story. The hardware to replace the VMS system oh man, people that say "disk is cheap" need to have their heads examined! To increase the capacity on our SAN to hold this new DB, this includes the drives themselves, a new drive enclosure, cabling, more room on the DAS (more drives) on the tape array to hold near line backup, another 25 tapes for the tape array for the offline/offsite and various odds and ends to make all that happen - initial estimate, $425,000. We almost fell off of our chairs at that. That was just the storage for the DB. The application server estimate from HP via our broker is in, $375,000 and we can't have it for 45 days.

The conversion of '07

5 days straight on this project, averaging 15 hours a day for 6 "in house" IT staff and 5 consultants. Took today off as it is Sunday, but I had to come into the office to do the "real" work that has been missed. This coming week is not going to show much work internally on this as other projects are taking priority. I used sqlplus and extracted all of the DDL for the programs and triggers, 109.302 megabytes of text DDL files (uncompressed). That was a shocker, even with almost 6,000 PL/SQL that is a huge amount.

I was made the official "Project Lead", that means that today, after spending 6-8 hours to find out why backups were failing last week and delegate the user requests that flowed in, I get to transpose the project task list and milestones into a Gant chart for our status meeting on Friday. I have to devise a project time line that will have this system running on a Linux back-ended database and the front end running on an ancient HP-UX server that was slated for the trash bin no later than the middle of April 2007. One of our SA's called HP to see about getting some warranty and parts coverage on the ancient server and the possibility of adding a CPU. He was literally laughed at, HP is looking into it and will get back to him with some options, we hope they have some newer hardware that can run the older OS and we will simply purchase the hardware. On the purchase note, we were given an almost blank check for this project on Friday, simply "keep it under 6 million", that dollar figure does not count internal IT time ("free") or the consultants who's time gets divided up and charged to the sites. The sites had came back some what appears to be valid numbers that for every day they do not have this system the chance of a processing line failure increases by 1.2%, even the smallest plant going down will cost the company $125,000 a day with the largest plant showing a net loss of $1.1 million a day. There are 17 sites that will be exposed, that woke the management up. The customer interaction and shipping data retrieved from the system is another story and can not be quantified easily into a dollar figure, the note was something to the effect of

How much is it worth when a customer who buys a million a month worth of product asks "When will my order by completed?" and the companies answer is "We don't know, our system is down".

I called our Oracle sales rep on Thursday, to talk about the licensing implications of this move, I loved the answer I got back on Friday morning - "Won't cost you any more oracle licensing as long as the old system is turned off within 60 days of the new system coming online, your covered - need any consulting?".

As I sat here writing this, 4 other IT staff showed up without being asked to catch up on missed work and work on this project. I like the people I work with.

Some good progress

Well, we made some really good progress today with the DW conversion. We carved off 400 gig section of our SAN even before the new disks have arrived, and created a 10gR2 DB there in noarchive mode. I built a DCL script that runs on the VMS cluster and exports one table at a time from a list that we compiled to a distinct file name, then FTP's the export file to the Linux server with the new LUN mounted. Then the Linux server imports those tables into the new DB and sends a notification if there are any failures. We have created all of the tables already in the DB and spread out the tables over multiple tablespaces. This is more for our ease of management than anything performance based. The process has been running for about 7 hours now and has sent 1,079 tables with very few failures, but the big tables are still to come, with the throughput we are getting, are estimates are about 120-130 hours. The import process imports 4 files at a time as any more than that saturates the IO on the linux box. This is of course simply a test to see what will fail and give us an idea on how long we will have to book downtime for. The largest slow down is the network link between the 2 sites, we 100% utilize the network link. The network folks are going to investigate some point to point VPN's as they are confident they can get 3 or 4 dedicated ADSL lines into the remote site and our internet pipe is much larger (x10) than the dedicated network link.

I think our downtime will be limited though, The data import process can be stopped and only a select few users have rights assigned by the application to make data changes. We will remove those rights via the application, then stop the data load process and start the transfer when we do this for real. That will mean the users will be able to query the old system while the data is being transfered. The consultants have been very successful in "fooling" the application to connect to the test database on the linux server, took some work on their side though. They knew they were able to compile the application on an older HP-UX PA-RISC series running V10 of the OS, luckily we had one. On that server we were able to install an oracle 9 client which their application recognizes and will still connect to the 10g database. They have about 3 weeks of work to finish the application move to the HP-UX server though and that does not include any testing or recoding the inevitable failures. We have a consulting company coming up next week to work on converting the VMS DCL script portion of the system to unix shell scripts. That is going to be interesting working through that conversion.

Tomorrow we tackle the triggers and PL/SQL code and see what fails there. 15 hours at the office today is enough for me.

OCP certification and knowledge

I have mentioned before that Howard Rogers is a great man, and this post proves it. Absolutely no argument from me on this.

I too have written in many places about the "dead from the neck up" job candidates I have been through over the years, OCP certification is a joke and a waste of money for companies. The people who have proclaimed themselves experts "OCP Certified" who can't answer the simplest of database questions.

I will put the list of questions we ask up on a post very soon.

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.

Blogger and formatting

I forgot just how hard it is to get something to format the way I want it on blogger, I think I will have to do some research on another blogspace.

Old Questions refreshed again

Saw somebody bumped one of the many "is select count(1) faster than select count(*)" threads on AskTom .

I believe Tom, but I just had to go and look AGAIN to see if anything changed in 10gR2:

SQL> select count(1) from dbtesting.t; 

COUNT(1) 
---------- 
20413 

Elapsed: 00:00:00.00 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2966233522 

------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 59 (2)| 00:00:01 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T | 20413 | 59 (2)| 00:00:01 | 
------------------------------------------------------------------- 


Statistics 
---------------------------------------------------------- 
0 recursive calls 
0 db block gets 
315 consistent gets 
0 physical reads 
0 redo size 
413 bytes sent via SQL*Net to client 
381 bytes received via SQL*Net from client 
2 SQL*Net roundtrips to/from client 
0 sorts (memory) 
0 sorts (disk) 
1 rows processed 

SQL> select count(*) from dbtesting.t; 

COUNT(*) 
---------- 
20413 

Elapsed: 00:00:00.00 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2966233522 

------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 59 (2)| 00:00:01 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T | 20413 | 59 (2)| 00:00:01 | 
------------------------------------------------------------------- 


Statistics 
---------------------------------------------------------- 
0 recursive calls 
0 db block gets 
315 consistent gets 
0 physical reads 
0 redo size 
413 bytes sent via SQL*Net to client 
381 bytes received via SQL*Net from client 
2 SQL*Net roundtrips to/from client 
0 sorts (memory) 
0 sorts (disk) 
1 rows processed 

SQL> select count(66666) from dbtesting.t; 

COUNT(66666) 
------------ 
20413 

Elapsed: 00:00:00.00 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2966233522 

------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 59 (2)| 00:00:01 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T | 20413 | 59 (2)| 00:00:01 | 
------------------------------------------------------------------- 


Statistics 
---------------------------------------------------------- 
1 recursive calls 
0 db block gets 
315 consistent gets 
0 physical reads 
0 redo size 
417 bytes sent via SQL*Net to client 
381 bytes received via SQL*Net from client 
2 SQL*Net roundtrips to/from client 
0 sorts (memory) 
0 sorts (disk) 
1 rows processed 

SQL> select count(dbms_random.value(1,1000)) from dbtesting.t; 

COUNT(DBMS_RANDOM.VALUE(1,1000)) 
-------------------------------- 
20413 

Elapsed: 00:00:00.17 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2966233522 

------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 59 (2)| 00:00:01 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T | 20413 | 59 (2)| 00:00:01 | 
------------------------------------------------------------------- 


Statistics 
---------------------------------------------------------- 
1 recursive calls 
0 db block gets 
315 consistent gets 
0 physical reads 
0 redo size 
437 bytes sent via SQL*Net to client 
381 bytes received via SQL*Net from client 
2 SQL*Net roundtrips to/from client 
0 sorts (memory) 
0 sorts (disk) 
1 rows processed 

SQL> spool off 

> >

There, now if somebody somebody is wondering, the argument is solved. They are the same when selecting from a table without a predicate, I even threw in the dbms_random to show any number in the count() is the same.

Dizwell is Back

Ahhh... there we go.

Howard relented and opened his site again. Howard is a great guy!

http://www.dizwell.com