# Presentation: 274978

## D0 Taking Stock 

**By Anil Kumar **
**CD/CSS/DSG**
**June 06, 2005**

- D0 Taking Stock

## Production/Integration Infrastructure

**8 900MHz CPU  16Gb RAM**
**The machine has a Clariion 4500 hardware raid array with 80 drives.**
**Oracle Server 9.2.0.6 (64 bit) on Solaris 2.9 64 bit. **
**Load Avg 2 –3  CPU usage < 50% , Memory Free : 61%**
**Average Db Response time/execute ~ 0.01-0.05 secs**
**Uptime  excluding schedule down times.**
**99.85269% Uptime (based on 420 min of total db unavailability) since **
**	****Nov 15, 2004 **
**System Performance :**
- _[http://d0om.fnal.gov/d0admin/sysperf/](http://d0om.fnal.gov/d0admin/sysperf/)_
**Db Performance Charts :**
**https://mistest12.fnal.gov/cp/**

- D0 Taking Stock

**Notes:**
Average Response time per execute is 0.01 secs to 0.07 secs 

## D0 offline development Infrastructure

**8 400 MHz, 4GB of RAM**
**OS and Oracle Version same as of int/prd.**
**     ****64bit OS and 64 bit Oracle 9.2.0.6**
**Load Avg 1-2 , CPU usage 10-15%, Mem Free 7.8%**
**System Performance URL**
**http://d0om.fnal.gov/d0admin/sysperf/**

- D0 Taking Stock

## D0 Calib Servers Deployment Infrastructure

- Sun Solaris

- Linux PC for Analysis Farm

- Linux PC

- Linux PC

- Failover

- Failover

- Linux PC for Reconstructed Farm

**Note :** **There was 1 failure for User Servers and Farm Servers since Nov 15, 2005**

- D0 Taking Stock

**Notes:**
Notes : 

## Space Usage 

- D0 Taking Stock

## Space Usage Summary

**D0ofprd1 786 GB  used. **
**d0ofint1 77GB     used.**
**     ****800Gb is available for use for int and production.**
**d0ofdev1 82 GB Used **
**               ****190GB is available for use.**

- D0 Taking Stock

**Notes:**
Db Space Growth doesn’t include space for redos, undos and temp areas. 

Oct 02 150Gb
Oct 03 355Gb
Oct 04 515Gb 
Nov 04 643Gb
June 05 832Gb

250Gb/year 

## Capacity Planning

**Next three years expected Growth  825Gb.**
**   ****SAM growth 250Gb/year and other apps**
**   ****25Gb/year.  This exclude Luminosity DB **
**   ****We have around 800Gb available. **
**   ****Should start planning to upgrade Disk Capacity Next Year. **
**Luminosity growth is 125Gb/year.**
**     ****Sun v40z machine with a Sun StorEdge 3310 scsi disk array w/ 1.752 tb & 2 Ultra160 raid controllers.**
**URL for Capacity Planning : **
**http://www-css.fnal.gov/dsg/internal/d0_ofl_dbs/D0_database_servers(sun)/d0ora_index_page/d0ora2_d0ofprd/d0ora2_disk_planning.htm**

- D0 Taking Stock

## Accomplishments

**Upgraded D0 offline databases to 9.2.0.6**
**Quarterly Database Security Up-to-date**
**Tested Complete Database Recovery of d0ofprd1 database. It took 4 hours. This assumes hardware is already configured and Backup files are available on disk. **
**Moving  d0 offline to a standardized backup recovery method using a san and enstore.  Parallel testing of san as backup media for development and production instances going well. **** **
**Luminosity db deployed 9i and 10g versions on loaner CDF machine **

- D0 Taking Stock

## 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**
**The url for the ganglia charts (monitoring tools) is:****http://fcdfmon2.fnal.gov/**
**Data Modeling Tool :**
** ****Oracle Designer is used for Data Modeling and space estimates.**

- D0 Taking Stock

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

## Back-up/Recovery 

**D0ofprd1**
** ****- Daily, 7 days of archives, one always on DISK**
** ****- Bi-weekly backup of READ ONLY tablespaces **
** ****- Allocated 1179GB  Used 755GB, Tape Daily, RMAN Back-up time -> 5 Hrs 45Min ( 3 Hrs Excl READ ONLY  + 2 Hrs 45 Exclude READ ONLY ) **
**	****No Export**
**Tape Rotation : 1 Week for Daily backups and 2 months for Read Only backups.**
**D0ofint1   Once a week on SAN **
**D0ofdev1**
** ****- Daily 3 days of archives  Sat on DISK otherwise on SAN**
** ****-Allocated 100GB, used 58GB, Daily Tape Backup**
**  ****RMAN Backup time -> 1.5 Hr. **
**  ****Tape Rotation : 2 Months. **
**- backup strategy for d0of lum boxes will be the SAN centralize strategy  **

- D0 Taking Stock

**Notes:**
READ ONLY Backups for OLD Event Partitions. 
No Event data on d0 offline integration. No READ ONLY backups. 
Nov 15, 2004 RMAN backup size was 506Gb , as of June 01, 2005  755Gb  250Gb growth in 6.5 Months. 
READ ONLY Backup Size is 265Gb and READ WRITE is 490GB

## 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, it will discuss the san testing in more detail**
**Multiplexing of archives to local disk and SAN**

- D0 Taking Stock

## RMAN to SAN test case on d0ofdev1

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

- D0 Taking Stock

## Production backups to SAN

**Initial problems encountered due to incompatible PCI cards – solved now**
**Two 1TB SAN mount points in use**
**2 daily backups – one to SAN, one to local backup disk**
**Always 2 backups on disk, plus X200 tape library backup of RMAN from local disk**
**Read-only portion of database backed up twice/month to local backup disk**

- D0 Taking Stock

## 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)**

- D0 Taking Stock

## SAM Schema

**Production Deployments : **
**	****   ****-  Autodestination  Sub-System of SAM schema **
**	****   ****-   Indexes on Param Values Deployed in production. **
**	****   ****-   Data Types correction cut. **
**         ****-    Indexes for Volumes   to be deployed on 06/07/05 **
**         ****-     Partition Cut to be deployed on 06/07/05 **
**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**
**1.61B events in 32 Partitions.  Now Avg 1 partition/ 3 running weeks **
**Partitions Rollover dates URL :     **
**http://www-css.fnal.gov/dsg/internal/databs_appl/sam_event_partitions.html**

- D0 Taking Stock

## What’s Next ?

**Deploy san/enstore backup recovery plan. **
**( TESTING OF SAN on d0ofprd1 is work-in-progress) **
**Deployment of Lum Db in production. 10g.  **
**Possible Upgrade to 10g  to d0ofprd1 due to enhanced feature of incremental database backups.**
**Upgrade OEM to 10g **
**Rewrite of dbatools/toolman for enhanced features of monitoring and 10g support. **
**SAM Schema Deployment for SAM Request System.**
**Testing of postgres mini sam for proof of product.**** **

- D0 Taking Stock

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

**Backups will get bigger . So backup of VLDB **
**Speaker Bureau application to be moved to production ASAP. It is on dev being used in production mode. **
**SAM Servers on Linux ? **
**Not Enough Space for Integration db to do full refresh of SAM. **
**Single point of failures with D0 offline database. **
**future of the aging clarion array must be addressed in next budget.**** **

- D0 Taking Stock