Project Outline: Minos Oracle Server

Tasks that have been completed as of 12/10/03 are colored green

1. Set up a Development Server
   Currently use odets2.fnal.gov, a FRHL 7.3 2 box in Nelly Stanfields' office running Oracle 9i.

   TO DO LIST

   create read-only logins on server inform users that logins exist (reader/reader@minosdev)

2. Populate Development Server
   To date: have written some perl scripts analagous to load_tables.sh described here. These scripts take the priming data files from /afs/fnal.gov/files/data/minos/database_dumps/ and move them into the oracle database on odets2.

   Running the scripts revealed the following:

   • the mysql schema often used a default date of '0000-00-00 00:00:00'. This is an illegal date in Oracle, fixing it is not hard but has implications for check-summing validation programs. NULL might have been a better choice as it is explicitly defined as unknown or meaningless data, is portable, and takes up very little storage. The unknown or meaningless date specified above probably takes up more space and is not portable.

   • default values in mysql and oracle are specified slightly differently.
     mysql: not null default 'foo' oracle: default 'foo' not null

   • Validity tables have primary keys (good). Data tables reference validity tables through foreign key constraints, and in all instances the data tables referenced existing validity table rows (very good).
     None of the data tables seems to have primary keys defined (not so good).

   • There are still Oracle reserved words in the Mysql schema. COMMENT and CONSTANT are both used as column names. I have changed these to COMMENTS and CONSTANTS in the Oracle schema, which may have implications for the API on top of RDBC.

   • 3 tables failed to load completely due to format inconsistencies between mysqldump and sqlldr programs.
     • DBILOGENTRY - 10 rows
     • DBUSUBRUNSUMMARY - 51 rows
     • UGLIDBISTRIPSTRUCT - 40 rows

   • Table DCS_HV failed to load 14,516,381 of 31,855,144 rows when the database ran out of space. Getting enough space requires the DBA's to stop the server, move data files to different disks, restart, resize and/or add data files. They are working on it.

   TO DO LIST

   get space on dev server change scripts to handle format errors in DBILOGENTRY, DBUSUBRUNSUMMARY, and UGLIDBISTRIPSTRUCT tables repopulate with latest priming data identify and create primary keys for data tables cvs checkin scripts

3. Test DB Independent Checksum Program

   Spot checking has been done so far, the oracle checksumming is slower than the mysql. As an example checking the CALGAINDRIFTREFERENCE table with 406866 rows took 40.89 seconds wall clock time for mysql, and 1 minute 18.90 seconds for Oracle. The lack of primary keys for the data tables may exacerbate the slower retrieval times for Oracle as an 'order by' clause has to do more sorting when no primary key is specified.

   TO DO LIST

   alter checksum script to handle default date differences between mysql and oracle systematically check all tables in mysql offline vs oracle development change checksum program to do incremental as well as full checksums

4. Verify that RDBC/Oracle_ODBC/DBMAuto Still Works

   Last tested March 2003. Had to come up to speed on some changes to cvs/openssh configurations. Trying to compile now, running into (I suspect self inflicted) compile problems.

   TO DO LIST

   compile, run RDBC/io test suite against oracle run RDBC example programs against oracle

5. Set UP DBMAuto Replication
   
   1. from minos-db1.fnal.gov(mysql) to odets2.fnal.gov(oracle)
   2. from odets2.fnal.gov(oracle) to fndapl.fnal.gov(mysql) or equivalent
   3. Change DBMAuto Scripts to use DB independent checksum

   TO DO LIST

   the above 3 steps (yes I know thats a skimpy description for a lot of work! I will flesh this out.)

6. Set up Oracle Production Server
   

   TO DO LIST

   aquire hardware, sign MOU's DBA's and sysadmins install and configure hardware/software export data/schema from development server import into production server change DBMAuto configuration files to point into and out of new server