# Presentation: 882867

## Status of Online Databases

- Currently there are 2 online Oracle Databases running
- on d0online cluster:
- Production DB, d0onprd, running on d0olc.fnal.gov
-      allocated space: 12 Gb
- Development DB, d0ondev, running on d0olb.fnal.gov
-      allocated space: 8 Gb
- Both DB operates Oracle server v8.1.7.1 with latest
- security patch installed
- Client support available to D0 online user:
- sqlplus, oci(oracle call interface), dcoracle(python),
- perl_dbd_oracle(perl), misweb(query web tools)

- V.Sirotenko, July 26 2001

## Applications on online production DB

-  Hardware(EPICS) DB (HDB)        S. Krzywdzinski,  J.Simmons
- - Run Configuration DB (RUNS)     V. Sirotenko,  J.Simmons
- - Luminosity DB (LUM)                    M. Begel,  J.Simmons
- - Calibration DBs
-      cal_calib, cft_calib, smt_calib, ... (see Taka’s talk)
- Online development DB:  101 user
- If you need an account to start developing DB application
- send request to  d0db-support from WWW page
- http://d0db.fnal.gov/d0db/access.html

- V.Sirotenko, July 26 2001

## Current Status of DB Applications

|        |      RUNS |      HDB |      LUM |
| --- | --- | --- | --- |
|   Online DB status |      done |       done |   design  |
|    Command UI     |      done |       done |       no |
|        GUI status | query, Web | done,Web |       no |
| Transfer to Offline |    1⁄2 done |       n/a |   1⁄2 done  |

- V.Sirotenko, July 26 2001

## Future DB Developments

- Significant Event DB: will contain the most
- 	important Significant Event System messages
-     coming from DAQ
- 2. Monitoring DB:  will contain various detector
-     and accelerator parameters taken at different
-     time intervals (5 mins, 15 mins, 30 mins, hour,
-     day and so on) which can be used for future
-     physics analysis.
- 3. Others? Electronics DB, Rack DB, ...

- V.Sirotenko, July 26 2001

## Tools to Monitor Online Databases

-    **OEM** (3-tier architecture, servers on odsoem(dev).fnal.gov NT machines
- 	 monitor oracle processes and state of databases
-      send e-mails if something goes wrong
-   ** ****dbMonitor **daemon running on d0olc
-      communicates with SigEvtSystem (has to be updated)
-    **StatsPack Viewer** (2-tier architechure, running on NT)
-      monitor database status, can send e-mails (not implemented yet)
-    **OracleTools**, web based set of SQL scripts
-      useful to monitor and administer databases
-    **KARMA**, web based database monitor display
-      can be used in control room (?), need tuning

- V.Sirotenko, July 26 2001

## StatsPack Viewer for Oracle (shareware)

- V.Sirotenko, July 26 2001

## OracleTools, web based (freeware)

- V.Sirotenko, July 26 2001

## OracleTools, continue

- V.Sirotenko, July 26 2001

## KARMA Oracle Monitor

- V.Sirotenko, July 26 2001

## RUNS Database Content

- For every run started by COOR the following information
- is inserted into the DB:
- - run parameters from COOR: run number,start time,
-   end time,trigger configuration, list of readout crates with
-   attributes, luminosity start/end block numbers, exposure
-   groups information and so on
-  run parameters from EPICS: toroid and solenoid status,
-   currents and polarity
-  readout crate/modules map: details of the readout crate
-   configuration and download configuration (not fully
-   implemented, static map)
- Latest ER design diagram: look at
- http://www-d0ol/run/db_report/modell.htm

- V.Sirotenko, July 26 2001

## RUNS Database Diagram

- Online
-  RUNS
- DB

- Offline
-  RUNS
- DB

- COOR

**RunGrabber**

**RunPusher**

- V.Sirotenko, July 26 2001

## RUNS Database Content (2)

- Tablespace allocated: 	2 Gb
- Current size:			144 Mb (7%)
- RUNS table:  			  18759 rows
- RUN_TRIGS table:			  72619 rows
- RUN_CRATE_ATTS table: 	113718 rows
- RUN_CONFIGS table:		587683 rows

- V.Sirotenko, July 26 2001

## RUNS Database WWW page

- V.Sirotenko, July 26 2001

## RUNS Database Query Page

- V.Sirotenko, July 26 2001

## RUNS Database Query Page (2)

- V.Sirotenko, July 26 2001

## RUNS Database Query Page (3)

- V.Sirotenko, July 26 2001

## RUNS Database Query Page (4)

- V.Sirotenko, July 26 2001

## RUNS Database Query Page (5)

- V.Sirotenko, July 26 2001

## Hardware (EPICS) DB

- The EPICS Database serves as a repository of the following objects:
-  EPICS record types, as given by the .dbd files
-  EPICS templates, as given by .dbt files
-  EPICS generators, as given by .dbg files
-  instances of EPICS records, contained in .db files
-    which are downloaded into front-end nodes (IOC)
- EPICS records are stored related to all IOC's used at D0, and also could be grouped into devices, e.g. an entire power supply. The devices in turn, and thus their records, could be further grouped according to categories:
- detector type, device type, templates, front-end node, location
- For tutorial on HDB look at _[\\D0SERVER4\projects\Online_Computing\Tutorials\](file://D0SERVER4/projects/Online_Computing/Tutorials/HdbTutorial.ppt)__[HdbTutorial](file://D0SERVER4/projects/Online_Computing/Tutorials/HdbTutorial.ppt)__[.](file://D0SERVER4/projects/Online_Computing/Tutorials/HdbTutorial.ppt)__[ppt](file://D0SERVER4/projects/Online_Computing/Tutorials/HdbTutorial.ppt)_

- V.Sirotenko, July 26 2001

## Hardware (EPICS) DB  (2)

** **
**Tablespace allocated: ****	****	****1 Gb**
**Current size:****	****	****	****20 Mb (2%)**
**Tables                                                Count as of 7/24/01**
-   ALARMS                  	                              1
-   DEFAULTS                      	                          1648
-   DETECTORS                   		30
-   DEVICES                                      		3919
-   DEVICE_TYPES                           		71
-   EPICS_DEFINITIONS           		1983
-   GENERATORS             			31338
-   LOCATORS                            		259
-   NODES                                                         64
-   PARAMETERS                                             616
-   TEMPLATE_IDS                       		108
-   TEMPL_FIELDS               		18687
-   TEMPL_RECORDS                     		1884
-  **Views**
-    DEV_INSTANCES               		623123

- V.Sirotenko, July 26 2001

## Hardware (EPICS) DB  (3)

- Custom utilities were created, to enter, maintain and extract the data from the EPICS Database:
-  **hdbWeb:****   interactive-type Web-based GUI**
** ****hdbBatch****: batch-type Python scripts **
- Oracle account (username/password) to the D0 Production Database, d0onprd, with either hdb_operator, or hdb_administrator role granted, is needed in order to use these utilities.
- Oracle Enterprise Manager, a powerful GUI interface available on NT and Unix, allows a DBA to do almost anything to a database, including manipulation of database definitions and data in tables.
- One can always resort to SQL*Plus ...

- V.Sirotenko, July 26 2001

## HDB WWW Page

- V.Sirotenko, July 26 2001

## HDB Web GUI

- V.Sirotenko, July 26 2001

## HDB Web GUI (2)

- V.Sirotenko, July 26 2001

## LUMinosity DB Status

-    ***From Jeremy’s report on 7/26/2001:***
-   **Design of Db Schema still 95% **
**    ****- Missing L3 Script Runner**
**  ****Online to Offline transfer**
**    ****- Design and Coding complete, command line based prototype available**
**   ****lmReader v1 complete : reads in LmBlock files, can be use of batch **
**    ****loading and testing, need better error handling, 4.5 sec to load one  **
**    ****LmBlock**
**   ****lmDbAccess package v1 complete**
**   ****Gregor Geurkov :  interfacing lmServer to lmDbAccess**

- V.Sirotenko, July 26 2001