# Presentation: 869159

## Database Projects

- J.Trumbo
- CSS-DSG
- May, 30 2006

- March, 2005

## OutlineWhat’s included 

- Freeware (Postgres/MySql/Mini Sam on Postgres)
- Oracle Contract Status
- Oracle 10 upgrade
- Advanced Security Option
- Backup & recovery
- Accomplishments in a Nutshell
- Oem 10 installation
- Cad – 2 new database machines, 2 new Windows machines
- Minos gets production machine
- D0 luminosity
- SDSS
- Unexpected Pleasures
- Moving Forward

- March, 2005

## Freeware

- Dsg is continually attempting to find time and resources to
- Update of web documentation.
- Cross train dbas as time allows.
- Establishment of test freeware databases.
- The loss of RJetton has hurt us in this area. Additional cross training has not been possible.
- Since the demand for freeware support is not as high as Oracle (yet), and Dsg currently supports no production freeware database, Dsg staff attempts train as time allows.
- Dsg still depends primarily on S.Lebedeva as our freeware expert.

- March, 2005

## Freeware

- Postgres Mini Sam
-  S.Lebedeva has created a java script to translate the mini sam oracle ddl to postgres ddl.  There is now a postgres mini sam product. Due to lack of developer resources, postgres mini sam was never tested for proof of product against a dbserver. This would be a 1st step in viability of moving sam off Oracle.

- March, 2005

## Oracle Contract Status

- June 1, 2005 the lab initiated 2 new contracts with Oracle.
- The 1st contract covers all Fermi employees. This was needed to accommodate training and payroll (upcoming) functions that are done individually.  This contract does not include RAC, real application clustering. It does include unlimited use of databases and most Oracle tools.

- March, 2005

## Oracle Contract Status

- The 2nd contract is to cover all non employee users that access Oracle dbs, mostly our experiment users. This agreement is an annual lease of licenses, trued up by a count of kerberos users. This contract runs for 5 years.  June 1, 2006 is the start of year 2.
- This contract acknowledges the ever changing user community. It is not a per name basis, it is a simply count.  The 2006 contract purchased and additional 176 users over 2005. This contract does include RAC.

- March, 2005

## Oracle 10 Upgrade

- Upgrading all the databases to Oracle 10 is well under way.  Our move to Oracle 10 was pushed forward by the site assist and the accelerator downtime schedules.
- As a prerequisite to upgrading to Oracle 10, the Oracle Enterprise Manager, OEM, had to be installed and tested.  There has been a loss of some functionality in OEM 10. We are working (complaining) with Oracle on this. Specifically, the charting tools were dropped, and significant monitoring events were dropped.

- March, 2005

## Oracle Versions

- Currently at Oracle 10
- D0 lum
- Minos
- Cdf online/offline & replicas
- Cad
- CMS
- ESH
- D0 offline development
- Still at Oracle 9
- Miscomp  - complications with 3rd party products
- D0 offline int/prd (scheduled for june 6)

- March, 2005

## Oracle 10 Upgrade

- A major prerequisite to upgrading miscomp to Oracle 10 was the Oracle Application Server, including, Oracle Forms and Reports. S.Jones completed this task several months ago. The oracle application server is now running on 2 new linux boxes.  We will be making a plan to move miscomp to Oracle 10 soon. There will be many more 3rd party obstacles. We will also be looking at replacing miscomp hardware.

- March, 2005

## Oracle Advanced Security Option

- Advanced Security is an Oracle product.
- Purpose of Advance Security is to kerberize database logons.  Have done proof of product, and several limitations prevent us from implementation.
- Oracle’s June Futamasa, is the DOE rep. She has promised help in getting OAS fixed.  No deliverables thus far.

- March, 2005

## Oracle Advanced Security

- Organized a working group including
- Argonne
- Fermi
- Sandia
- Los Alamos
- Cern
- U.S. Navy
- Larwence Livermore
- Lack of response from Oracle has hindered this. Fermi will be pushing harder now that the Oracle 10 upgrade is finishing.

- March, 2005

## Backup & Recovery

- Backup and recovery for very large databases always a huge issue.
- No space to test full recoveries.
- Moving to san storage for rman backups. Growing need for additional san space to accommodate growing backup files. San solution is more economical.
- Currently doing rmans to san:
- D0ora2, d0 offline production
- D0lum2, d0 luminosity production
- Bzora1, cdf online production (not quite there yet, currently using san to test restores for dcache)

- March, 2005

## Backup & Recovery

- Dsg has been moving to dcache/enstore for tape backups of rman files for our larger databases.  Tibs is handling the small databases, miscomp, cad.
- Our homegrown product rman_dcache stores and retrieves rman files from dcache. Rman_dcache still needs development work.  We have been too short handed to truly finish the product. There is also need to improve the code. Work is done as resources can manage.
- Since the revamp of stken, and minos moving to it own area, the backups to dcache have been dependable. Thanks to the isa-group, they have been great.

- March, 2005

## Backup/Recover

- Database backups going to dcache:
- D0ofprd1 (d0 offline)
- D0ofprd1_readonly (d0 offline events)
- D0oflump (d0 luminosity)
- Minosprd  (minos sam)
- Cdfonprd (cdf online)
- Cdfofpr2 (cdf offline)
- D0ofprd1, READ ONLY is scheduled, but not currently being stored.  We hope dcache/enstore will allow us to deprecate the aging tape robot currently attached to d0ora2 at some time.

- March, 2005

## Accomplishments in a Nutshell

- New machines and databases for
- Oracle Enterprise Manager (OEM)
- Cad
- Minos
- D0 Luminosity
- Oracle Application Server (Matt’s group)
- Upgrade to Oracle 10 almost complete including:
- Installation and testing of OEM 10
- Installation of Oracle Application Server machines
- Streams testing and planning for Oracle 10
- Linux testing and planning for Oracle 10

- March, 2005

## Accomplishments in a Nutshell

- Continued maintenance, patching, refreshing, accounts, etc.  of operating systems and databases with a > 99% uptime.  In fact I believe last year cdfonprd had a 100% level.
- Continued maintenance of Sam schema for 3 experiments.
- Continued consult to application owners on schema design and implementation.
- Incorporation and review of security issues raised by the site assist team.
- Keeping pace with SDSS load schedule and releases, starting new initiatives including runs_db and monitoring.

- March, 2005

## Replacement of OEM boxes

- Oracle Enterprise Manager 10 has been installed on new machines running RH Enterprise Linux. OEM 10 was a prerequisite for Oracle 10.  However we have run into several issues with OEM 10.
- No charts
- Some lost functionality from OEM9
- Working with Oracle.

- March, 2005

## Cad

- Cad got 2 new database machines and 2 new middle tier windows machines this year.  SKovich and NStanfield setup the new Sun boxes for the databases, ARomero is supporting the Windows boxes.
- DSG & CSI host bi weekly meetings with TParker & LCarpenter to plan & push the project forward.
- Shooting for implementation by Dec. 2006.  Issues include:
- Software delivery delays
- Dependency on stakeholders testing
- Dependency on stakeholders data scrubbing.
- Stakeholders are the PPD, TD, CD, ADMS, ADCRYO.

- March, 2005

## Minos

- Minos has the first installation of sam on a 64b Linux Sun box, Oracle 10.  This installation has been smooth and without incident.
- The development implementation currently uses an old Dsg linux box, but will be moving to a the new minos dev box as soon as the dbas get some spare cycles.

- March, 2005

## D0 Luminosity

- D0 luminosity application has been installed on 2 new Sun boxes (dev/prod).
- Luminosity went production in Dec. 05.
- The D0 luminosity team is helpful and communicates freely when it has special needs such as huge data loads.

- March, 2005

## SDSS

- S.Lebedeva has become the lead dba for SDSS. She has been able to cross train A.Kumar to assist.  This year SDSS has
- Loaded DR4 & 5.
- Initiated loaded the new RunsDb.
- Extensive documentation where there was none.
- Backed up DR1-5 to enstore.
- Worked on the web interface.
- Began a helpdesk procedure.

- March, 2005

## Unexpected Pleasures

- Dsg has been down 3 people most of the year. We are trying to hire, but the market is tough and we support a diverse set of databases. Even though, loads of work have been accomplished, thanks to the dbas and sysadmins.
- However, we have not been able to accomplish several other things:
- Cross training
- 	Dsg is too dependant on individuals with specific expertise in areas.  There is no time to cross train.
- Kerberization of Oracle
- 	RJetton was handling, AKumar will now have to come up to speed, and really PUSH Oracle Corporation.

- March, 2005

## Unexpected Pleasures

- Rac testing
- 	No time to investigate, though we have hardware & license to test.
- New responsibilities
- 	Have gotten a bit of experience with ESH databases on Windows, but not enough manpower to master the new platform.
- 	Nimi...have not been able to even look at. No manpower.
- Training and development
- 	Group members have indicated there is no time to learn new technologies, classes, etc. They are correct.

- March, 2005

## Moving Forward

- New employee and cross training
- Replacement of g1/h1. Adding additional structure to the business end of CD. Its not just miscomp any more.
- Replacement of aging disk arrays, particularly the Clarion array on d0ora2. Expecting to use san technology to store database files as well as rman files.

- March, 2005

## Move to SAN?

- Only if benefits outweigh costs
- Benefits
- Clustering
- Dynamic allocation of storage
- Centrally Managed

- March, 2005

## Move to SAN? (2)

- What is not addressed?
- Reduction of required disk resources
- Cost (purchase and maintenance)?
- Power?
- Floor Space?
- Reduction of maintenance outages
- Tuning
- Optimization

- March, 2005

## Next Generation RAID Arrays

- 3PAR and Compellent
-  Can help reduce required disk resources
- Thin provisioning
- R/W snapshots for development and integration
- Automatically move data from SATA <-> FC disks without interrupting application
- Reduce maintenance outages
- Can tune storage w/o disrupting application

- March, 2005

## Reducing Storage Needs

- Production

- Production

- Development

- Integration

- Development
- R/W Snapshot

- Integration
- R/W Snapshot

- Delta from
- Developers

- 3x Storage Needed

- 1+x storage needed

- Traditional

**New**

- Refresh DB for Dev
- and Int much faster

- Much smaller in size
- than production DB

- Delta from
- Inegrators

- March, 2005

## Reducing Maintenance Downtimes

- 4+1 RAID5 Group

- I/O Bound/Disk Change

- Copy Data off

- Recreate RAID Group

- Repopulate new storage

- Traditional

- Downtime

- Dynamic Optimization

- 4+1 RAID5 Group

- I/O Bound/Disk Change

- Add Storage

- Dynamically Optimize across
- new storage

- _NO DOWNTIME_

- Add Storage

- March, 2005

## Questions

- Probably more expensive than traditional arrays in terms of $$/TB
- Will reduced storage == less overall cost?
- Do R/W snapshots work with Oracle?
- Relatively new companies
- Private
- Longevity questions

- March, 2005

## Slide 31

|  | Proposed
SYSADM | ProposedDBA | YTD |
| --- | --- | --- | --- |
| CAD | .5 | .5 | .17 |
| CMS | .25 | .25 | 0 |
| SDSS |  | 1.25 | .67 |
| CDF | 1.0 | 1.5 | .13 |
| D0 | .75 | .75 | .24 |
| MISCOMP | .5 | .5 | 0 |
| ESH |  | .25 | 0 |
| MINOS |  |  | 0 |
| DB Admin |  |  | 1.12 |
| Exp Support |  |  | 1.67 |
| Total | 3.0 | 5.0 | 4.0 |