Database Projects

1
Database Projects

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

2
OutlineWhats 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

3
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.

4
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.

5
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.

6
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.

7
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.

8
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)

9
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.

10
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.
• Oracles June Futamasa, is the DOE rep. She has
  promised help in getting OAS fixed. No
  deliverables thus far.

11
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.

12
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)

13
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.

14
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.

15
Accomplishments in a Nutshell

• New machines and databases for
• Oracle Enterprise Manager (OEM)
• Cad
• Minos
• D0 Luminosity
• Oracle Application Server (Matts 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

16
Accomplishments in a Nutshell

• Continued maintenance, patching, refreshing,
  accounts, etc. of operating systems and
  databases with a gt 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.

17
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.

18
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.

19
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.

20
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.

21
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.

22
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.

23
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.
• Nimihave 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.

24
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.

25
Move to SAN?

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

26
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

27
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 lt-gt FC disks
  without interrupting application
• Reduce maintenance outages
• Can tune storage w/o disrupting application

28
Reducing Storage Needs
Traditional
3x Storage Needed
Integration
Production
Development
Delta from Inegrators
New
Much smaller in size than production DB
Delta from Developers
1x storage needed
Refresh DB for Dev and Int much faster
Development R/W Snapshot
Integration R/W Snapshot
Production
29
Reducing Maintenance Downtimes
Traditional
41 RAID5 Group
I/O Bound/Disk Change
Add Storage
Copy Data off
Recreate RAID Group
Downtime
Repopulate new storage
Dynamic Optimization
41 RAID5 Group
I/O Bound/Disk Change
Add Storage
Dynamically Optimize across new storage
NO DOWNTIME
30
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

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