This database is stored at oracms.cern.ch . To access the database you can login on srv-c2c05-07 and digit this command on the prompt:

# sqlplus CMS_PC_INFO/password@OMDS

OMDS is the database name and CMS_PC_INFO is the database username.

If you prefer to use windows OS you can access this DB using the Benthic Software. It can be downloaded on this site : http://www.benthicsoftware.com/products.htm and install it. At CERN you can download it from G:\Applications\Benthic directory.

Database Tables

These tables are divided actually in 2 groups, the tables in use now and the tables what maybe will be used on the future.

---

Tables in use actually:

---

PC_INFO

This table contain the PC`s information.

Fields Description:

PC_SN[varchar2(128)] – Primary key. This field stores the pc serial number. This is the field which “connects” all the other tables on the DB.

MANUFACTURER[varchar2(64)] – This field stores the pc manufacturer name.

MODEL[varchar2(128)] – This field stores the pc model.

OS[varchar2(256)] – This field stores the OS installed.

OS_VERSION[varchar2(256)] – This field stores the OS version installed.

MEMORY[varchar2(64)] – This field stores the pc memory value in MB.

HDS[varchar2(64)] – This field stores the pc hard disk capacity.

BMC_VERSION[varchar2(64)] – This field stores the BMC version installed .

---

PC_POSITION

Fields Description

PC_NAME[varchar2(128)] - This field stores the pc name.

PC_SN[varchar2(128)] - Foreign key. This field stores the pc serial number. It is associated with the field PC_SN on the table PC_INFO.

RACK[varchar2(64)] – This field stores the rack there the pc is installed. Examples : C2A01, C2B02, C2E03

LOCAL[varchar2(64)] – This field stores the position on the rack where the pc is installed. Examples: Rack= (C2A01),Local=(01,02,…..,20); Rack= (C2E08),Local=(01,02,…..,20);

DATE_INSTALL[timestamp(6)] – This field stores the date when the OS was installed.

STATUS[varchar2(128)] – This field store the pc status . The possible values are: InOperation: The pc is in installed and working. OutOfOperation: The pc is still o the rack but it is not in operation. Broken: The pc is broken. Removed: The pc was removed.

POSITION[varchar2(64)] – This field store the information about the pc local history. For example, if one pc is installed on the rack C2A01 local 02, it is on it`s first position, POSITION=1st, if the machines moves to another rack or local , POSITION=2nd .

---

NETWORK_INFO

This table stores the information about all mac address on each machine.

Fields Description:

PC_SN [varchar2(128)] – Foreign key. This field stores the pc serial number. It is associated with the field PC_SN on the table PC_INFO.

ETH0_MAC[varchar2(64)] – This field stores the mac address on the eth0.

ETH1_MAC[varchar2(64)]- This field stores the mac address on the eth1.

ETH2_MAC[varchar2(64)] - This field stores the mac address on the eth2.

ETH3_MAC[varchar2(64)] - This field stores the mac address on the eth3.

ETH4_MAC[varchar2(64)] - This field stores the mac address on the eth4.

ETH5_MAC[varchar2(64)] - This field stores the mac address on the eth5.

BMC_MAC[varchar2(64) - This field stores the bmc mac address.

---

BMC_ERRORS

This table stores the information about all bmc events (errors, corrections) happened on each machine on SXC5 with ipmi configuration .

Fields Description:

PC_SN [varchar2(128)] – Foreign key. This field stores the pc serial number. It is associated with the field PC_SN on the table PC_INFO. DATE_ERROR [timestamp(6)]– This field stores the date when the bmc event occurred. A bcm event can be an error or a system information.

STATUS [varchar2(128)]– This field stores the bmc event status. The possible values are: Problem Disappeared: Occur when a bmc problem disappears mysteriously. Problem Solved by the System: Occur when a bmc problem is solved without interventions. Problem Solved: Occur when a problem is solved with an intervention. Problem Unsolved: Occur when a problem was not solved yet. System Correction: Occur when the bmc event isn`t an error but a system information telling that the previous problem was solved.

DATE_SOLV[timestamp(6)] – This field stores the date when the bmc_error was solved.

ACTIONS[varchar2(4000)] – This field stores the actions to solve the bmc problem .

PERSON_ID [varchar2(128)]– Foreign key. This field will store the id of the person who did the actions to solve the problem. It is associated with the field person_id on the table STAFF.

COMMENTS[varchar2(4000)] – This field stores any important comment about this database register. If an information on this field occur many times, it can create other fields on the future.

SENSORTYPE [varchar2(128)]– This field stores the type of sensor used by the bmc to identify the bmc event. Examples: Battery, fan, memory and others.

DESCRIPTION [varchar2(128)]– This field stores the bmc event description detected by the sensor. Examples: sensortype=(Battery), description=(Failed,Low);Sensortype=(Fan), description=( RedundancyLost, FullyRedundant, LowerCriticalgoinglow);

EVENT_DIRECTION [varchar2(128)]– This field stores the event direction, if it is an assertion event or a deassertion event. Example: Sensortype= Battery,Description=Failed,Event_Direction= AssertionEvent (the battery failed) Sensortype= Battery,Description=Failed,Event_Direction= DeassertionEvent (the previous battery failure was corrected) OBS: The information on the fields PC_SN,DATE_ERROR,SENSORTYPE, DESCRIPTION and EVENT_DIRECTION are inserted through a shell script that reads the bmc entries and insert it on the DB. The information on the fields STATUS, DATE_SOLV, ACTIONS,PERSON_ID and COMMENTS actually are inserted on the DB through a sql statement , but soon it will be done through a web interface.

---

BMC_ERROR_DESCRIPTION

Fields Description:

BMC_ERROR[varchar2(128)] - This field stores the bmc event name. The name was choose according to the values on the fields sensortype, description and event_direction.

BMC_ERROR_DESCRIPTION[varchar(1000)] - This field is an explanation for the each bmc event.

SENSORTYPE [varchar2(256)]- Equal to the field sensortype on table BMC_ERRORS.

DESCRIPTION [varchar2(256)]- Equal to the field description on table BMC_ERRORS.

EVENT_DIRECTION[varchar2(256)] - Equal to the field event_direction on table BMC_ERRORS.

SEVERITY[varchar2(64)] - This field stores the severity degree of the bmc event. Possible values: Critical: Then the bmc event is a serious error which compromise the system. Warning: Then the bmc event is a non serious error. But it indicates what a more serious error can happen. Information: Then the bmc event is a system information.

---

Tables unused actually

---

These tables maybe will be used on the future.

INTERVENTIONS

Fields Description:

PC_SN[varchar2(128)] – Foreign key. This field stores the pc serial number. It is associated with the field PC_SN on the table PC_INFO.

INT_TYPE[varchar2(256)] – This field stores the intervention type. It can be a hardware intervention.

INT_DATE[timestamp(6)] – This field stores the intervention date.

PERSON_ID [varchar2(128)] – )]– Foreign key. This field will store the id of the person who did the actions to solve the problem. It is associated with the field person_id on the table STAFF.

COMMENTS [varchar2(4000)] – This field stores any important comment about this database register. If an information on this field occur many times, it can create other fields on the future.

---

DISK_CHANGED

Fields description:

DISK_TYPE_ID[number]- Foreign key. This field will store the disk type ID. It will be a random number. It will be associated with the field DISK_TYPE_ID on the table DISK_TYPE.

PC_SN [varchar2(128)] - Foreign key. This field will store the pc serial number. It is associated with the field PC_SN on the table PC_INFO. (*1)

DISK_SN[varchar2(128)] - This field will store the disk serial number.

HD_POSITION[number] – This field will store the HD position on the PC. Possible values:1,2,3,4,5,6.

ACTIONS [varchar2(64)]- This field will store the actions done with this disk. Possible values: installed, removed, fixed. (*2)

STATUS [varchar2(64)]- This field will store the disk status, if it is broken, in operation or out of operation. (*3)

ACTION_DATE[timestamp(6)]- This field will store the date when the action was done. (*4)

PERSON_ID[varchar2(128)]- Foreign key. This field will store the id of the person who did the actions to solve the problem. It is associated with the field person_id on the table STAFF. (*5)

COMMENTS[varchar2(4000)]– This field will store any important comment about this database register. If an information on this field occur many times, it can create other fields on the future. (*6)

---

DISK_TYPE

Fields description:

DISK_TYPE_ID[number]- Primary key. This field will store the disk type ID.

MANUFACTURER[varchar2(128)]- This field will store the disk manufacturer.

CAPACITY[number]- This field will store the disk capacity in MB.

DISK_INTERFACE[varchar2(128)]- This field will store the disk interface type. Ex: ATA, SCSI.

ROT_SPEED [number]- This field will store the disk rotation speed in rpm.

LST[number]-This field will store the disk latency and seek time.

BUFFER[number]-This field will store the disk buffer size.

MTBF[number]- This field will store the mean time between failures value.

ASTR [number]– This field will store the average sustained transfer rate value.

AAT[number]- This field will store the average access time value.

FORM_FACTOR[varchar2(128)]- this field will store the disk`s dimensions.

---

FAN_CHANGED

Fields description:

FAN_TYPE_ID[number]- Foreign key. This field will store the fan type ID. It will be a random number. It will be associated with the field

FAN_TYPE_ID on the table FAN_TYPE.

PC_SN[varchar2(128)]- See (*1) .

FAN_SN[varchar2(128)]- This field will store the fan serial number.

FAN_POSITION[number]- This field will store the fan position on the PC. Possible values:1,2,3,4.

ACTIONS[varchar2(64)]- See (*2) .

STATUS[varchar2(64)]- See (*3) .

ACTION_DATE[timestap(6)]- See (*4) .

PERSON_ID[varchar2(128)]- See (*5) .

COMMENTS[varchar2(4000)]- See (*6) .

---

FAN_TYPE

Fields description:

FAN_TYPE_ID[number]- Primary key. This field will store the fan type ID.

MANUFACTURER[varchar2(128)]- This field will store the fan manufacturer.

SPEED[number]- This field will store the fan speed value in rpm.

NOYSE[number]- This field will store the fan noise value in dB.

AIRFLOW[number]- This field will store the fan air flow value in CFM.

---

MEMORY_CHANGED

Fields description:

MEMORY_TYPE_ID[number]- Foreign key. This field will store the fan type ID. It will be a random number. It will be associated with the field MEMORY_TYPE_ID on the table MEMORY_TYPE.

PC_SN[varchar2(128)]- See (*1) .

MEMORY_SN[varchar2(128)]- This field will store the memory serial number.

SOCKET[number]- This field will store the memory socket position on the PC. Possible values:1 to 8.

ACTIONS[varchar2(64)]- See (*2) .

STATUS[varchar2(64)]- See (*3) .

ACTION_DATE[timestap(6)]- See (*4) .

PERSON_ID[varchar2(128)]- See (*5) .

COMMENTS[varchar2(4000)]- See (*6) .

---

MEMORY_TYPE

Fields description:

MEM_TYPE_ID[number]- Primary key. This field will store the memory type ID.

MANUFACTURER[varchar2(128)]- This field will store the memory manufacturer.

TECHNOLOGY[varchar(64)]- This field will store the memory technology type.

CAPACITY[number]- This field will store the memory capacity in MB.

MEMORY_SPEED [number]- This field will store the memory speed in MHz.

FORM_FACTOR[varchar2(128)]- this field will store the memory`s dimensions.

---

MICROPROCESSOR_CHANGED

Fields description:

MP_TYPE_ID[number]- Foreign key. This field will store the fan type ID. It will be a random number. It will be associated with the field MICROPOCESSOR_TYPE_ID on the table FAN_TYPE.

PC_SN[varchar2(128)]- See (*1) .

MP_SN[varchar2(128)]- This field will store the microprocessor serial number.

MP_POSITION[number]- This field will store the microprocessor position on the PC. Possible values:1,2.

ACTIONS[varchar2(64)]- See (*2) .

STATUS[varchar2(64)]- See (*3) .

ACTION_DATE[timestap(6)]- See (*4) .

PERSON_ID[varchar2(128)]- See (*5) .

COMMENTS[varchar2(4000)]- See (*6) .

---

MICROPROCESSOR_TYPE

Fields description:

MP_TYPE_ID[number]- Primary key. This field will store the microprocessor type ID.

MANUFACTURER[varchar2(128)]- This field will store the microprocessor manufacturer.

MODEL[varchar2(128)]- This field will store the microprocessor model.

ARCHITECTURE[varchar(64)]- This field will store the microprocessor architecture type.

CACHE[number]- This field will store the microprocessor cache size in MB.

CLOCK_SPEED [number]- This field will store the clock frequency in MHz.

---

MOTHERBOARD_CHANGED

Fields description:

MB_TYPE_ID[number]- Foreign key. This field will store the fan type ID. It will be a random number. It will be associated with the field MOTHERBOARD_TYPE_ID on the table FAN_TYPE.

PC_SN[varchar2(128)]- See (*1) .

MB_SN[varchar2(128)]- This field will store the motherboard serial number.

MAC1[varchar2(16)]- This field will store the motherboard`s first mac address.

MAC2[varchar2(16)]- This field will store the motherboard`s second mac address.

ACTIONS[varchar2(64)]- See (*2) .

STATUS[varchar2(64)]- See (*3) .

ACTION_DATE[timestap(6)]- See (*4) .

PERSON_ID[varchar2(128)]- See (*5) .

COMMENTS[varchar2(4000)]- See (*6) .

---

MOTHERBOARD_TYPE

Fields description:

MB_TYPE_ID[number]- Primary key. This field will store the motherboard type ID.

MANUFACTURER[varchar2(128)]- This field will store the motherboard manufacturer.

BIOS [varchar2(128)]- This field will store the motherboard BIOS.

CPU [varchar2(128)]- This field will store the motherboard`s supported cpus.

FSB[varchar2(128)]- This field will store the motherboard`s front side bus value in MHz.

MEMORY[varchar2(128)]- This field will store the memories supported by the motherboard and their possible rates(MB/s).

EXP_SLOTS[varchar2(128)]- This field will store the motherboard possible expansions slots.

RAID_LEVELS[varchar2(128)]- This field will store the raid levels supported by the motherboard.

DMA_TX[varchar2(128)]- This field will store the transfer rates supported(MB/s) by the motherboard.

LAN[varchar2(128)]- This field will store the motherboard nics lan type and speed.

FORM_FACTOR[varchar2(128)]- This field will store the motherboard `s dimensions.

---

POWER_SUPPLY_CHANGED

Fields description:

PS_TYPE_ID[number]- Foreign key. This field will store the power supply type ID. It will be a random number. It will be associated with the field PS_TYPE_ID on the table POWER_SUPPLY_TYPE.

PC_SN[varchar2(128)]- See (*1) .

PS_SN[varchar2(128)]- This field will store the power supply serial number.

PS_POSITION[number]- This field will store the power supply position on the PC. Possible values:1,2.

ACTIONS[varchar2(64)]- See (*2) .

STATUS[varchar2(64)]- See (*3) .

ACTION_DATE[timestap(6)]- See (*4) .

PERSON_ID[varchar2(128)]- See (*5) .

COMMENTS[varchar2(4000)]- See (*6) .

---

POWER_SUPPLY_TYPE

Fields description:

PS_TYPE_ID[number]- Primary key. This field will store the power supply type ID.

MANUFACTURER[varchar2(128)]- This field will store the power supply manufacturer.

EFFICIENCY[number]- This field will store the power supply`s efficiency.

MAX_WATT[number]- This field will store the power supply`s maximum wattage.

OP_TEMP[number]- This field will store the power supply `s operating temperature.

---

MYRINET_CHANGED

Fields description:

PC_SN[varchar2(128)]- See (*1) .

MNET_SN[varchar2(128)]- This field will store the myrinet serial number.

MAC1- This field will store the myrinet mac address.

MNET_TYPE- This field will store the myrinet card type.

ACTIONS[varchar2(64)]- See (*2) .

STATUS[varchar2(64)]- See (*3) .

ACTION_DATE[timestap(6)]- See (*4) .

PERSON_ID[varchar2(128)]- See (*5) .

COMMENTS[varchar2(4000)]- See (*6) .

---

SILICOM_CHANGED

Fields description:

PC_SN[varchar2(128)]- See (*1) .

SCOM_SN[varchar2(128)]- This field will store the myrinet serial number.

SCOM_TYPE[varchar2(128)]- This field will store the myrinet card type.

CMS[number]- This field will store the silicom card CMS number.

MAC1[varchar2(16)]- This field will store the silicom mac address 1.

MAC2[varchar2(16)] - This field will store the silicom mac address 2.

MAC3[varchar2(16)]- This field will store the silicom mac address 3.

MAC4[varchar2(16)]- This field will store the silicom mac address 4.

MAC5[varchar2(16)]- This field will store the silicom mac address 5.

MAC6[varchar2(16)]- This field will store the silicom mac address 6.

ACTIONS[varchar2(64)]- See (*2) .

STATUS[varchar2(64)]- See (*3) .

ACTION_DATE[timestap(6)]- See (*4) .

PERSON_ID[varchar2(128)]- See (*5) .

COMMENTS[varchar2(4000)]- See (*6) .

---

LCD_ERRORS

Fields description:

PC_SN[varchar(128)]- See (*1) .

LCD_CODE_ERROR[varchar(64)]- This field will store the code error that appeared on the pc`s LCD display. A list of the possibles LCD errors can be found at this page: http://www.mohca.gov.bt/~susumu/other/dell/2950/data/pe2950/doc/en/hom_E/about.htm#wp1096182

DATE_ERROR[timestamp(6)]-This field will store the error date.

STATUS[varchar(128)]- This field will store the error status, if it is solved, unsolved, or disappeared.

DATE_SOLV[timestamp(6)]- This field will store the date when the problem was solved.

ACTIONS[varchar(4000)]- This field will store the actions to solve the problem.

PERSON_ID[varchar(128)]- See (*5) .

COMMENTS[varchar(4000)]- See (*6) .

---

SYSTEM_ERRORS

Fields description:

PC_SN [varchar2(128)] – See (*1) .

SYS_MESG[varchar2(2000)]- This field will store system error message shown on the pc screen.

DATE_ERROR [timestamp(6)]– This field stores the date when the bmc event occurred. A bcm event can be an error or a system information.

STATUS [varchar2(128)]– This field stores the bmc event status. The possible values are: Problem Disappeared: Occur when a bmc problem disappears mysteriously. Problem Solved by the System: Occur when a bmc problem is solved without interventions. Problem Solved: Occur when a problem is solved with an intervention. Problem Unsolved: Occur when a problem was not solved yet. System Correction: Occur when the bmc event isn`t an error but a system information telling that the previous problem was solved.

DATE_SOLV[timestamp(6)] – This field stores the date when the system error was solved

ACTIONS[varchar2(4000)] – This field stores the actions to solve the problem .

PERSON_ID [varchar2(128)]– See (*5) .

COMMENTS[varchar2(4000)] – See (*6) .

---

STAFF

Fields description:

PERSON_NAME[varchar2(600)]- This field will store the name of all persons authorized to do interventions on pcs.

PERSON_ID[varchar2(128)]- Primary key. This field stores the ID of the person who did the actions. This is the field which “connects” the other tables on the DB.

EMPLOYER[varchar(256)]- This field will store the person`s employer.

---

-- FabianaRodrigues - 08 Jul 2008

• database_keys3.docx: Database keys