Database Projects

JTrumbo

March 2005 

**Contents**

Freeware & Postgres Mini Sam

Oracle 10 Upgrade

Oracle Rman 10g Testing

Advanced Security Option

Backup & Recovery

Oem10 Installation

Cad

CMS Detector Database

Fundamentals of database design class

Unexpected Pleasures

**To be covered in a subsequent project meeting**

> SDSS
>
> CDF & D0 daily maintenance

Databases Project

JTrumbo

March 2005

 

**Freeware & Postgres Mini Sam**

**Project manager's summary**

Freeware & Postgres Mini Sam is a 2 pronged project. Freeware
documentation needs updating, dbas need freeware cross training. Mini
Sam is a product created by J.Trumbo last year that allows individual
developers to quickly create, destroy, recreate small sam schemas in
their own oracle accounts complete with sam seed data. S.Lebedeva took
this schema a step further by transforming it to postgres. Needless to
say, this is an experiment in a non Oracle dependant sam.

The postgres ddl was packaged into kits. Morag Burgon-Lyon at Glasgow is
attempting to run a dbserver against this schema, as well as testing and
commenting on the postgres product.

R.Jetton has been spending effort cross training in mysql and postgres,
including bringing web documentation up to date. This has been an 'as
time permits' activity.

**Resources used (budget, effort)**

S.Lebedeva spent \~ writing java code that reads the sam oracle ddl and
transforms it to postgres ddl. 35 days

Mini sam to postgres oct, 27% nov 30% dec & jan , 35% feb,

Freeware documentation 20% mar.

R.Jetton cross training in freeware dba skills and documentation. 3
days.

Jan 5%, Feb&mar 10%

A.Kumar worked with S.Lebedeva as a sam schema consultant.

**Schedule/Milestones**

Cross training is done as time permits. No schedule has been announced
for db server testing on postgres mini sam.

**Titles and links**

<http://www-css.fnal.gov/dsg/external/freeware/>

<http://www-css.fnal.gov/dsg/internal/sam/index.htm>

Databases Project

JTrumbo

March 2005

 

**Oracle Enterprise Manager 10 Installation (OEM10)**

**Project manager's summary**

D.Bonham is the lead dba, C.Kastner is the lead sysadmin for the OEM 10
implementation. OEM is Oracle Enterprise Manager, proprietary software
that monitors, alerts, schedules database and system jobs. OEM 10 will
be installed on Linux machines housed in FCC. These machines were
delivered \~Mar 1, 2005. OEM 10 implementation must be complete before
the Oracle databases move to v10. OEM 10 will need thorough testing and
parallel testing against existing databases. Dsg will continue to put
effort into maintenance of both hardware and software in the future.

**Resources used (budget, effort)**

Resources will include a dba and sysadmin, working together initially
for requirements and hardware implementation.

D.Bonham 4 days

Mar. 20%

C.Kastner 2 days

Feb 2%, mar. 9%

**Schedule/Milestones**

March 2005, machines delivered and setup in FCC.

Dsg would like full implementation by mid May/early June.

**Titles and links**

<http://www-css.fnal.gov/dsg/internal/oem/OEMPlan/OEM10g_hardware_requirements.html>

<http://www-css.fnal.gov/dsg/internal/oem/OEMPlan/OEM10g_new_machines_setup.html>

<http://www-css.fnal.gov/dsg/internal/oem/OEMPlan/linux_system_setup_10g.html>

Databases Project

JTrumbo

March 2005

 

**Oracle 10 upgrade**

**Project manager's summary**

Upgrade to Oracle 10 on the books to begin fy 2005. Normal procedures
would demand this upgrade begin, as Oracle 10 has been available for
over a year. Oracle 10 will provide enhancements for streams and backup
and recovery of large databases. The CMS database machines are running
Oracle 10, but at this time are not considered mission critical to
monitor. Only toolman is being used to monitor the CMS v10 instances.

**Resources used (budget, effort)**

No resources has been expended on the Oracle 10 upgrade thus far. Just
began thinking of a plan in Mar. 2005. OEM (Oracle Enterprise Manager)
must be upgraded to 10 before the databases, as OEM 9 is incompatible
with Oracle 10.

Starting documentation for plan will be based on the CMS experience.

**Schedule/Milestones**

Oracle 10 upgrade for critical systems is dependant on OEM moving to 10
first. OEM 10 was be covered in another section.

Dsg will begin moving non mission critical databases to 10, those non
dependant on OEM 10 for monitoring, in Apr. 2005. It is not expected all
databases will be migrated to Oracle 10 before Oct.

**Titles and links**

<http://www-css.fnal.gov/dsg/internal/cms/CMS/Oracle10gonbagernode_cmscald.htm>

<http://www-css.fnal.gov/dsg/internal/cms/CMS/upgrade_cmscaldr10103.htm>

Databases Project

JTrumbo

March 2005

 

**Oracle Advanced Security Option**

**Project manager's summary**

R.Jetton has researched the implementation of Oracle Advanced Security
Option. OAS will provide a kerberized logon environment to Oracle
databases. This will be a welcome extra layer of security. However,
R.Jetton has found a show stopper. OAS has a 30 byte connect string
limitation. This is too short to handle the Fermi kcron method. R.Jetton
continues as a low priority, discussion with Oracle and Fermi security
as to how to get around this issue. Oracle has suggested using ldap
\"light-weight directory access protocol\", where the ldap server is
configured to a) use our kdc\'s for the kerberos part, and b)
automatically map long principals like\
\
<rjetton/krcon/fcdfdata012.fnal.gov@FNAL.GOV>\
\
to\
\
<rjetton@FNAL.GOV>

**Resources used (budget, effort)**

R.Jetton has spent \~48 hours prototyping this technology.

Oct 5%, nov 5%, dec 5%, jan 5%, feb 5% , mar 5%

**Schedule/Milestones**

Currently this project is being handled as time requires. There is no
scheduled implementation dates. Previous to this testing, it was hoped
OAS would be implemented fy 2005. Due to the effort from both Oracle and
Fermi, this now seems unrealistic unless uninterrupted, dedicated time
can be found. Work and communications between Fermi and Oracle tech
support continues.

**Titles and links**

<http://www-css.fnal.gov/dsg/internal/oracle_advanced_security/index.html>

Databases Project

JTrumbo

March 2005

 

**Backup & Recovery**

**Project manager's summary**

The bulk of backup & recovery has been spent trying to get Oracle rman
backups to a san, verify the san is writing and not corrupting the data
on ied disks, and restoration testing. A new validate utility must be
built & run against the rman disk pieces to insure restorability. This
validate technology had to be learned and tested. Backup and recovery to
the san from d0ora1 databases seems to be going smoothly. Work from
d0ora2 has been significantly delayed due to the problems encountered
getting the correct qlogic card with firmware that is compatible with
the sun systems as well as the clarion arrays. This issue combined with
1 down time a month for opportunity to 'test' on this unique
configuration has led to delay. Dsg cannot say with confidence the san
technology scales to vldbs, until the d0ora2 database is successfully
taken to the san for a period of testing.

Dsg is also looking to implement san technology to its other instances.
Dsg is planning on purchasing additional san area fy 2005, to use for
the cdf backups.

A big success in the backup recovery arena was the flawless full
recovery of d0ofprd1 from d0ora2 to the new cdf online machine. The cdf
machine, while in burn in phase was used to stage this recovery. It is
significant because d0ofprd1 has been too large for several years for a
test recovery to be done. This recovery gave us benchmarks as to the
time it will take to fully recover 1TB from an rman.

Testing rman recoveries from 32b oracle and os installs to 64b oracle
and os installs was also test successfully.

Backup and recovery testing for miscomp was done with the tibs
technology. Tibs was used for miscomp backups (small database) after the
operators left and automated tape backups were no longer available.

**Resources used (budget, effort)**

D.Bonhan has been performing the rman testing. 53 days.

Nov, 82%(parttime), dec 85%, (pt), jan 80% (pt) , feb 76% (pt), mar.
50%(full time)

S.Kovich has been performing the sysadmin testing on d0ora1 and d0ora2.
14 days.

**Schedule/Milestones**

Apr. 5, another fiber card will be installed in d0ora2 to see if the san
can be seen. We are attempting to have 2 cards available and test both
(if necessary) during this downtime. Results of this test may prove we
need to purchase a fiber card from sun.

Again, this testing of scalability of backup and recovery is greatly
hindered due to a once a month downtime window, and the unique
configuration d0ora2 and its clarion array.

Schedule of moving additional database backups to san is dependent on
purchase and delivery of san hardware. D0ora2 & d0ora1 currently are
allocated all available san hardware.

**Titles and links**

<http://www-css.fnal.gov/dsg/internal/SAN/san_index.htm>

Databases Project

JTrumbo

March 2005

 

**Cad**

**Project manager's summary**

Cad has been working this year toward the implementation of the 3 tier
architecture for TeamCenter Engineering for Ideas. This implementation
of the 2 middle tier servers will host the TeamCenter/Ideas application
and the Ansys license server. Two Sun database server machines will host
the Oracle databases.

The middle tier machines are Windows boxes to be supported by A.Romero,
the database machines will be 2 Sun solaris boxes supported by
C.Kastner. Dsg will also support the dev/int/prod databases to be
installed.

Thus far effort of a couple of hours has been made by A.Romero to assist
in the requirement planning and purchase of the mid tier machines. He
has not yet installed these machines.

C.Kastner has also spent time doing the requirements and purchase req
for the database server machine.

Once all hardware and software is available, implementation of the
applications will require resources from both C.Kastner & A.Romero, as
well as additional support from dba(s), as C.Kaster has never
implemented an Oracle database.

**Resources used (budget, effort)**

C.Kastner has spent approximately 5 days gathering requirements, writing
reqs, etc. We expect more time to be dedicated after the hardware
arrives.

A.Romero is waiting for delivery of Windows 2000 Server software for the
2 mid tier machines. This is a requirement for Ideas.

**Schedule/Milestones**

Hardware for the middle tier arrived mid Feb. A.Romero is waiting for a
software delivery before the implementation of the Windows machines can
be done. A.Romero will commence with installation when the software
arrives, due within 30 days.

The Sun machines for the database machines are on order. Should also
arrive \~ the first of Apr.

**Titles and links**

See the license server and oracle server requirements links.

<http://www-css.fnal.gov/dsg/internal/cad/index.html>

Databases Project

JTrumbo

March 2005

 

**CMS Detector Databases at Fermilab**

**Project manager's summary**

CMS Detector Database CMS detector databases at Fermi Lab are being used
for development, integration of CMS calibration databases. Once
developed and tested applications will be deployed on the production
databases. 10g database has been deployed for development , integration
and production databases.   Development, Integration and production
databases are already in place at FERMILAB. Development and
integration/production databases are deployed on Dell Power Edge 700
powered with dual 3.2 GHz processors, 2GB of RAM and 2 250Gb of Disks
and Linux ES Release 3 .

Oracle Designer is used for all database design and development work
including space report and generation of ddls. A review process is
established with application data modelers and database support group.
All data model design standards are adhered before deployment
application in integration and production instances.

Slow Control, Pixel, Calibration and Equipment management schemas are
deployed in development databases.  Dsg provides tuning to application
developers as needed.

Database replication is being discussed. CMS replication of data from
Tier 0 and Tier 1 may be merged in 3D (Distributed Deployment of
Databases) project. A test bed has been established between CERN and
FERMI to test streams deployment for database replication using 10g.  So
far requirement submitted by CMS, the application metadatasystem(RefDB)
will be replicated from t0 to t1 site.

A Case study has been presented by Dsg team on database development and
deployment during LHC database Workshop for On-line and off-line
developers in Jan 2005 at CERN, presented by A.Kumar.

**Resources used (budget, effort)**

Anil Kumar, primary database administrator  24 days, includes a trip to
cern

Oct, 20%, nov 20%, dec 20% jan 20%, feb 20%

Maurine Mihalek , primary system administrator  for uscmsdb01 11.5 days

Oct 20%, nov 15%, dec. 10%, jan 5%, feb 5%, mar. 2%

Stefan Piperov       - Primary System Administrator for uscmsb03 and
bager  PPD-EPP

**Schedule/Milestones**

-  Sep 2004  Upgrade to CMS databases to 10.1.0.3

\- Nov 2004  CMS Pixel Data Model Review.

\- Jan 2004   Case Study on Database Application Deployment

\- Feb 2005   CMS databases patched for Oracle Critical Security Patch

\- Oct 2005   Getting Ready for Slice Test. Review data models for
application as needed and deploy schema on a need basis.

**Presentations/reports/documentations**

Case Study on Database Application Deployment LHC Database Workshop at
CERN  Jan 205
<http://www-css.fnal.gov/dsg/external/workshops/lhc_db_workshop_files/frame.htm>

Database Backups at a Glance :
<http://www-css.fnal.gov/dsg/internal/backups/backups_status_2005.html>

**Titles and links**

CMS Db Support Page <http://www-css.fnal.gov/dsg/internal/cms/index.htm>

3D Project :   ( Distributed Deployment of Databases )
<http://www-css.fnal.gov/dsg/internal/3d/index.htm>

Databases Project

JTrumbo

March 2005

 

**Fundamentals of Database Design Class**

**Project manager's summary**

J.Trumbo wrote and presented a class discussing commercial and freeware
database selection criteria, relational design philosophy, lessons
learned, etc. This is a generic class for users not familiar with
nuisances of available database technology.

This project consists of a .ppt for a class of \~10 people. J.Trumbo
worked with S.Panacek and M.Votava to design. Class was given Feb. 2005.
Class discusses mysql, postgres and oracle, the 3 supported databases in
DSG.

**Resources used (budget, effort)**

Approximately 40 hours J.Trumbo

**Schedule/Milestones**

This class is available for future use. The class size of 10 proved
efficient. Class took \~4 hours to present.

<http://www-css.fnal.gov/dsg/external/workshops/index.htm>

Databases Project

JTrumbo

March 2005

 

**Unexpected Pleasures**

**Project manager's summary**

Two issues were raised over the last 3-4 months that have proven
unplanned resource usage. Implementation of the d0 luminosity
application and the responsibilities for the cdf online management.

A large request for unavailable space from the d0 lum group on d0ora2
prompted a switch in strategies late in 2004. There was not enough free
database space on d0ora2 to house the luminosity database. Plans had to
be made to purchase in additional hardware. In the mean time, to allow
loading of data immediately, resources at both the system and dba levels
were diverted to setup a temporary database to allow loading to proceed.

Dsg has also taken sysadmin responsibility for cdf's online environment.
This added responsibility came during a time when a new cdf online
machine was being installed. There have been numerous issues with this
new responsibility, not the least of which is the discovery that the
database backup procedure had data corruption, and the entire backup
procedure needed revamping immediately to insure the database be
recoverable if necessary.

**Resources used (budget, effort)**

J.Trumbo lumimosity, 5 days, cdf 20 days

lum 25% in dec

cdf 15% dec, 25% jan, 50% feb, 65% mar.

N.Stanfield

Lum 20% dec, 4 days

Cdf 10% feb, 2 days

R.Jetton

Lum 6 days

Cdf 24 days

M.Mihalek

Cdf 15 days

A.Kumar

lum, 2 days

S.Kovich

Lum 3 days

**Schedule/Milestones**

Luminosity hardware is not fully delivered, we are missing disk.
Luminosity will be moved from its temporary box/instance to Oracle 10 on
the new box as soon as the hardware arrives. Hardware includes a new dev
and prod box, disks for prod, disks for dev are being recycled from
d0mino.

Dsg took responsibility for the cdf online database machines feb. 28,
2005, in coordination with the RunII department. Simultaneously, Dsg is
implementing the replacement box for cdf online production, and a new
backup recovery method for the database.

**Titles and links**

Several links for cdf online plans under

<http://www-css.fnal.gov/dsg/internal/cdf_onl_dbs/index.htm>

Luminosity

<http://www-css.fnal.gov/dsg/internal/d0_ofl_dbs/D0_databases_servers_linux/index.htm>