# Presentation: 897271

## CDF Taking Stock 

- By Anil Kumar
- CD/CSS/DSG
- June 22, 2005

- CDF Taking Stock    2005-2006

- 06/22/2005

## Current Infrastructure

- CDF Taking Stock    2005-2006

- 06/22/2005

**Notes:**
The table reflects our PRD & DEV environment. 
O/S are all kept  up to date and security patches applied as necessary all certified with oracle

Specs for bzora1 ( Sun V440) 
4 x 1.28 GHz UltraSPARC IIIi processors with 1 MB cache each, 16 GB memory (16 x 1 GB DIMMS), 4 x 73 10K rpm Ultra320 SCSI disks, DVD-ROM, 2 Power supplies. Part Number: A42-XCB4-16HD-E. 

## Current Infrastructure

- CDF Taking Stock    2005-2006

- 06/22/2005

**Notes:**
This is our capacity for the Production machines only. These upgrades will support our present projections for cpu and capacity.

We break out capacity planning for db & backups to help us plan.  We are concerned about the sizes of the backups as the db grows.  Note:  cdfonline capacity for backups...   We find that as a general rule the size of the backups are closely tied to allocation space. 

We use rman, an oracle utility to support our backups which  improves & simplifies our recovery procedure.

Presently this tool is acceptable for speed & sizing but we have a project in place to understand when we are in trouble. 

We only backup SAM, DFC and DFC Books. Replicated Data is not backed up. 

* Need to configure the space

## CDF Capacity All Applications 

- CDF Offline DB Growth* 43(online)+5G(offline)/year
- 	* Slow Control is not in Offline
- CDF Online DB Growth 50G/year

- CDF Taking Stock    2005-2006

- 06/22/2005

**Notes:**
Average Growth is 40Gb/Year 
33Gb 01
80Gb 02 
123Gb 03
166Gb 04 

## CDF Online Applications

- CDF Taking Stock    2005-2006

- 06/22/2005

**Notes:**
CDF growth pattern for used space for online, this does not take into reflect  oracle’s overhead,
( 20% saved, temp, Redo etc.)

This chart reflects actually used for each application so the no.’s differ from earlier table. 

Our capacity planning needs to include infrastructure of the database.

These charts were created by CDF to help plan individual application growth. 

## CDF Offline Applications

- CDF Taking Stock    2005-2006

- 06/22/2005

**Notes:**
With the present projections the new v880 for cdf offline has sufficient capacity for 3-5 years.

## Monitoring And Data Modeling Tools 

**Monitoring Tools** :
- dbatool/toolman
- 	To monitor the space usage, users, SQL, tempspace, sniping of inactive sessions, auto start of Listener, IA, estimate table/Index stats
- OEM (Oracle Enterprise Manager)
- 	-  DB Monitoring tool/ Monthly charts posted on web
- Db Performance Charts :
- _[http://www-cdserver.fnal.gov/cd_public/css/dsg/db_stats/data/db_stats.html](http://www-cdserver.fnal.gov/cd_public/css/dsg/db_stats/data/db_stats.html)_
- The url for the ganglia charts (monitoring tools) is:_[http://fcdfmon2.fnal.gov/](http://fcdfmon2.fnal.gov/)_
**Data Modeling Tool :**
-  Oracle Designer is used for Data Modeling and initial space estimates for
-  applications.

- CDF Taking Stock    2005-2006

- 06/22/2005

**Notes:**
Logintrace -> This enables DBA’s to have an archive of which oracle user logs In to the database under which O/S name, and how long

## Uptimes 

- Cdfonprd   100%
- Cdfofprd    99.4356%
- 	1776 minutes unscheduled Down Time since 11/11/2004
- Cdf Replica    100%

- CDF Taking Stock    2005-2006

- 06/22/2005

**Notes:**
Cdfrep01 : 
There were about 10 or so reboots during the past year to install new kernels (or glibc).  there were no crashes or other "unannounced" outages.  Each of these reboots was done after disabling listener and allowing the connection queue to drain.  Therefore, it is expected that  no users were even aware when a reboot was done since 'ora4 was available to them as a backup.As a result, the serving of metadata to offline analysis jobs was 100% available during the past year. since it's not really the uptime percentage for 'lnx1 that is important but rather the uptime for the service provided.

## Accomplishments

- Upgraded CDF  databases to 9.2.0.6
- Quarterly Database Security Up-to-date
- Tuned/Regression test the streams replication as per  current API usage.
- Deployment of bzora1 for cdfonprd
-        Very smooth transition. No interruption to Data Taking !
- Decommissioned b0dau35
- Oracle Backups for cdfonprd to DCache/Enstore
- 	_[http://www-css.fnal.gov/dsg/external/cdfdbmtgs/all_other_documentation/bzora1.pdf](http://www-css.fnal.gov/dsg/external/cdfdbmtgs/all_other_documentation/bzora1.pdf)_
- Deployed the long/eagerly awaited streams replication across CDF databases.
-       *Hard Work* of css-dsg spanned across more than 2 years is finally in production. All issues encountered are addressed in timely manner.
- Smooth Transition to fcdfora6 with streams
-       replication.
- Decommissioned fcdflnx1.
- Implemented Capture of Long transactions in db.

- CDF Taking Stock    2005-2006

- 06/22/2005

## Replication Tool

- Streams Replication tool   “strmrep”
- Production Deployment of Streams Replication encountered
-       two issues :
-       a) Replication of two packages RUNDB and HDWDB caused
-           Streams to halt.  Worked very hard to address the issue/deploy the workaround.
-           Permanent bug Fix is released by Oracle on Thus 06/16/05.
-           This bug was not encountered in integration Test.
-       b) SAM can’t be replicated using streams since SAM application
-           has variable length CLOBS and functional index.
-           There was not  enough time to do regression test and no use case.
- One more error after production deployment that was causing one of streams
-         process to halt. Deployed the work around.
-        Oracle found the root cause and bug fix will be available in 1-2 weeks.
- Cdf Streams Status on-line ( Courtesy Randy Herber) _[http://dbb.fnal.gov:8520/cdfr2/databases?type=ora-strms&fsrc=cdfofpr2&nsrc=cdfofpr2&gsrc=cdfofpr2&dcbk=FILECATALOG](http://dbb.fnal.gov:8520/cdfr2/databases?type=ora-strms&fsrc=cdfofpr2&nsrc=cdfofpr2&gsrc=cdfofpr2&dcbk=FILECATALOG)_
**Documentation **_[http://www-css.fnal.gov/dsg/internal/ora_repl/](http://www-css.fnal.gov/dsg/internal/ora_repl/)_

- CDF Taking Stock    2005-2006

- 06/22/2005

## Freeware Db Support

- Mysql/Postgres prototype
- proof of product with CDF data
- Mechanism for population IS on demand,  it does not support updates
- CDF successfully tested with CDF code  - (Karlsruhe)
- DSG has begun to provide consulting for freeware databases
- actively maintaining new versions of mysql & postgres  in KITS and working towards a more robust environment
- actively maintaining documentation  for mysql & postgres in our freeware area.
- Reference url:
- 	http://www-css.fnal.gov/dsg/external/freeware
- actively assisting users with questions, upgrades, testing, etc. for freeware products.

- CDF Taking Stock    2005-2006

- 06/22/2005

## Back-up 

**	**
**CDF ONLINE DATABASES**** **
**cdfonprd**
-    - Daily, 7 days of archives, Two Backup copies always on DISK
-    - Allocated 535GB Used 461GB ( 2 Copies) , Backup time 1 Hr 23 Min  Vs 2 Hrs 30 Min on old hardware
-    -  CDF on-line Backup to DCache/Enstore: Daily
**cdfondev** Daily, 14 days archives, one always on DISK cdfondev -> 2 Hrs 30Min
**cdfonint**   Daily, 30 days archives, one always on Disk
-    - Allocated 356GB, Used 219GB ,cdfonint -> 2 Hrs 15 Min
**CDF OFFLINE DATABASES**
**cdfofprd ** DFC+SAM, Daily, 8 days of archive and Export. One always on Disk.
-       Allocated 270GB , Used 25 GB Backup time -> 1 Hr 24 Min
**Cdfstrm1 **being replica of on-line and DFC.  No backup ->RMAN/ Tape.** **
**cdfofdev– **Daily cdfofdev, 7 days of archives , cdfofdev -> 3 Hrs 20 Mins
-  **cdfofint** 2 times/week for cdfofint, 7 days of archives
-       Allocated 67Gb, used 36Gb (2 copies) , cdfofint-> 2:30Mins

- CDF Taking Stock    2005-2006

- 06/22/2005

**Notes:**
No RMAN for replicated Data. 

For CDF Offline, planning to move backups to  SAN. 

## Oracle Backup for cdfonprd toDCache/Enstore

- RMAN to DCache/Enstore is working fine, but needs fine tuning to fit our(dsg) standard, firewall independent backup mode.
- Working reliably. Fully automated for dailys.
- Data Integrity tested twice while recovery.
- Data Integrity tested 4 times via md5sum
- Not currently using weekly or monthly PNFS directory structure.
- Intend to send weekly on Sunday and Monthly on Ist.
- No archives being sent yet.
- PNFS metadata maintenance being done manually.

- CDF Taking Stock    2005-2006

- 06/22/2005

## RMAN Backup on SAN

- Inexpensive, large disk array can accommodate growing RMAN backups
- Fast & reliable backup and recovery
- 24 x 7 and 8 x 5 support tiers available
- Can serve various O/S platforms
- Briefing on the database backup/recovery standardization on june 16, discussed the san testing in more detail.
- _[http://www-css.fnal.gov/dsg/internal/briefings_and_projects/briefings/standardizing_database_backups.ppt](http://www-css.fnal.gov/dsg/internal/briefings_and_projects/briefings/standardizing_database_backups.ppt)_
- Multiplexing of archives to local disk and SAN

- CDF Taking Stock    2005-2006

- 06/22/2005

## RMAN to SAN Experience

- d0ofdev1 RMANs to SAN since Nov. ’04
- Two 1TB SAN mount points available
- Keep 2 alternating days of RMANs on SAN, once/week to local backup disk
- RMAN validation to determine backup file integrity
- One validation failure since Nov. ’04
- Recoveries from SAN were all successful

- CDF Taking Stock    2005-2006

- 06/22/2005

## SAN issues

- Current SAN is not 24 x 7 support
- IDE disks are not as reliable as other, more expensive disks are
- Purchasing 24 x 7 SAN requires licensing and changes to O/S to be able to use it
- Firewall issues (CDF & D0 online)
- We will be extremely careful in implementing SAN for bzora1.
-        On bzora1 :
-        a) PCI Card has been installed.
-        b) fiber between cdf and fcc has been identified for use, we are waiting       for additional san hardware for bzora1.

- CDF Taking Stock    2005-2006

- 06/22/2005

## SAM Schema

- Production Deployments :
- 	   -  Autodestination  Sub-System of SAM schema
- 	   -   Indexes on Param Values Deployed in production.
- 	   -   Data Types correction cut.
-          -    Indexes for Volumes
- Work-in-progress
- 	   - Request Sub System of SAM Schema. Cut in Mini-sam.
- Upgrade to Mini SAM as SAM Schema Evolved. -> This facilitate individual developers to have copy of SAM metadata and seed data available for server software rewrite if needed.
- Mini-SAM in Postgres. Initiative to move towards free ware Databases for SAM . Proof of product not complete, requires testing with a dbserver  from the sam development team

- CDF Taking Stock    2005-2006

- 06/22/2005

## What’s Next ?

- Deploy san/enstore backup recovery plan.
- ( TESTING OF SAN on d0 offline  is work-in-progress)
- Backup to DCache/Enstore already in place for CDF on-line** **
- Re-allocate Winchester Disk Array from fcdflnx1 to fcdfora1
-        sothat enough space to reconfigure streams integration setup.
- Reconfigure Streams Test Env cdfonint -> cdfofint -> cdfrep23
- SAM Request Sub System Schema Deployment
- Patch cdf database for replication of RUNDB and HDWDB packages.
-        ( Patch was released by Oracle on Thu 06/16/05)
- Converting cdfonline to 64 bit. Testing will be challenge.
- O/S upgrade (reinstall) to 2.9 on b0dau36 . Decommissioned Veritas.
- Performance tuning on fcdfora4 to sga > 2Gb to allocate more memory to streams
- Migrating Slow Control to Linux.
- Rewrite of dbatools/toolman for enhanced features of monitoring and 10g support.
- Upgrade OEM to 10g . Work in progress.
- Possible Upgrade to 10g  due to incremental database backups and streams replication’s enhanced features.
- Testing of postgres mini sam for proof of product.

- CDF Taking Stock    2005-2006

- 06/22/2005

**Notes:**
Upgrade OEM to 10g : 
Upgrade OEM 10g is needed since we have 10g databases for CMS. To monitor CMS databases, upgrade OEM to 10g is needed. 
OEM 10g is backward compatible for monitoring 9i

## Concerns

- Replication of SAM depends upon the stress test results on fcdfora4.
- Simulation of Applications as we have for  CALIB. Robust test Suite needed.
- Single point of failure for SAM and DFC
- Migration of DFC to SAM . Plan and Schedule ?
- Close Out  for Data Guard/Standby is still pending.
- Move Slow Control off of  bzora1
- 		- Require 3 instances
-                   - OS Linux ? If Linux then not a 24*7 Machine.
- Some of CDF Applications Data Model is not  in Designer.
- What is cdf's direction, if any, in respect to freeware?
- Any more Streams replica ?
- Deputy CDF database Liaison ?
- TNSNAMES deployment for CDF was a nightmare. Experience should be documented.
- Special Clean-up jobs should be co-ordinated with css-dsg
- In case of Hardware Failure on offline, we have to resintantiate replication Vs recovery since we have  partial backups on offline prd db.
- Move off VxWorks from b0dau36.

- CDF Taking Stock    2005-2006

- 06/22/2005

**Notes:**
Move Slow Control Off of b0dau35
API Impact : If Slow control communicate with other APIs and/or other APIs communicate with Slow Control. 
Margherita Comments on API impact w.r.t. Move off Slow Control to Another Machine :

as far as SlowControl, there is no API yet taht access data from the offline; so , so far ,there should be no impact, at least as far as i know,in moving the Icicle data to a different db/instance.The change in icicle is basically transparent, since the db informationin read from a file.