LTPP IMSOpsManual_2010Dec.pdf

IMSOpsManual_v2.docx Thursday, December 16, 2010 i ii

Technical Report Documentation Page

1. Report No.

TBD

2. Government Accession No.

3. Recipient’s Catalog No.

4. Title and Subtitle

LONG-TERM PAVEMENT PERFORMANCE

INFORMATION MANAGEMENT SYSTEM

OPERATIONS MANUAL

5. Report Date

December 2010

6. Performing Organization Code

7. Author(s)

Miriam Pitz and Tommy Clark

8. Performing Organization Report No.

9. Performing Organization Name and Address

Scientific Applications International Corporation (SAIC)

151 Lafayette Dr., Oak Ridge, TN 37830

10. Work Unit No. (TRAIS)

11. Contract or Grant No.

DTFH61-09-F-00046

12. Sponsoring Agency Name and Address

Office of Infrastructure Research and Development

Federal Highway Administration

6300 Georgetown Pike

McLean, VA 22101-2296

13. Type of Report and Period Covered

Operations Manual, May 2009

– December 2010

14. Sponsoring Agency Code

15. Supplementary Notes

Contracting Officer‘s Technical Representative (COTR): Jane Jiang, HRDI-13

16. Abstract

This document provides information about the Long-Term Pavement Performance (LTPP)

Program‘s database operations. This document provides specific information about maintaining and upgrading the LTPP database servers, operating systems and software, performing DBA functions, tracking change requests, producing software releases, and producing the annual

Standard Data Release. It provides information about using the RIMS Application, including running QC programs, data loaders and entering data into the Pavement Performance Database

(PPDB) with data entry forms. References to the Traffic Analysis Software (LTAS) are included.

In addition, it provides an overview of regional activities that relate to the database and data entry applications.

17. Key Words

Database, database server, general pavement studies, LTPP, pavement performance, specific pavement studies, LTAS, operating system, RIMS, AIMS, central database, regional database, server maintenance, anti-virus, backup software, relational database, database tools, SDR, standard data release, DBA functions, user administration, SPR, uploads, software release, QC output, data loaders, data entry forms, QC Manual.

18. Distribution Statement

19. Security Classif. (of this report)

Unclassified

20. Security Classif. (of this page)

Unclassified

21. No. of Pages

22. Price

Form DOT F 1700.7 (8-72) Reproduction of completed page authorized iii

Table of Contents

Introduction ...................................................................................................................... xi

Chapter 1. LTPP System Overview

1.1 LTPP Program

1.2 LTPP Data

1.3 LTPP Information Management System (IMS)

1.3.1 Central Database

1.3.2 Regional Databases

1.4 Major IMS Components

1.4.1 Pavement Performance Database (PPDB)

1.4.1.1 Production PPDB

1.4.1.2 PPDB Development/Test Environment

1.4.1.3 RIMS Application

1.4.1.4 PPDB Data Modules

1.4.2 LTPP Traffic Analysis Database (LTASDB)

1.4.2.1 Production LTASDB

1.4.2.2 LTASDB Development Environment

1.4.2.3 LTPP Traffic Analysis Software (LTAS) Application

1.4.3 Ancillary Information Management System (AIMS)

Chapter 2. LTPP Database Servers

2.1 Server Basics

2.1.1 General Hardware

2.1.1.1 Server Setup

2.1.1.2 Server Maintenance

2.1.2 General Software

2.1.2.1 Anti-Virus

2.1.2.2 Backup

2.1.2.3 ORACLE

2.1.2.4 Microsoft Office

2.2 Turner-Fairbanks Highway Research Center (TFHRC) Server Specifics 14

2.2.1 Hardware

2.2.1.1 Server Setup

2.2.1.2 Server Maintenance

2.2.2 Software

2.2.2.1 Anti-Virus

2.2.2.2 Backup

2.2.2.3 ORACLE

2.2.2.4 Microsoft Office

LTPP Information Management System Operations Manual iv

2.3 Technical Support Services Contractor (TSSC) Server Specifics

2.3.1 Hardware

2.3.1.1 Server Setup

2.3.1.2 Server Maintenance

2.3.2 Software

2.3.2.1 Anti-Virus

2.3.2.2 Backup

2.3.2.3 ORACLE

2.3.2.4 Microsoft Office

2.4 Regional Server Specifics

2.4.1 Hardware

2.4.1.1 Server Setup

2.4.1.2 Server Maintenance

2.4.2 Software

2.4.2.1 Anti-Virus

2.4.2.2 Backup

2.4.2.3 ORACLE

2.4.2.4 Microsoft Office

Chapter 3. Regional Information Management System (RIMS) Application

3.1 Introduction

3.2 Major RIMS Components

3.2.1 Data Entry Forms

3.2.2 Electronic Data Loaders

3.2.3 QC Programs

3.3 Minor RIMS Components

3.3.1 System Options Screen

3.3.2 RIMS DBA Operations

3.4 RIMS Navigation

3.4.1 Menu Navigation

3.4.1.1 Example 1. Navigating to Inventory Entry Forms

3.4.2 Menu to Form Navigation

3.4.3 Form to Menu, Form to Form Navigation

3.4.4 Item Level Navigation

3.4.5 Exiting a Form

3.5 Function Keys

Chapter 4. Regional Operations

4.1 Data Flow

4.2 Collect Data

4.3 Process Data

v

4.3.1 Electronic Data

4.3.1.1 Backup Raw Data

4.3.1.2 Review Data/Run Preprocessors

4.3.1.3 Archive Processed Data

4.3.1.4 Filter Data into Database

4.3.2 Sheet Data

4.3.2.1 Review Data Sheets

4.3.2.2 Enter Data with RIMS Forms

4.4 Assign Construction Number (CN) to Data Records

4.4.1 CN Process Notes

4.4.2 Using the CN Utilities

4.5 Perform QC Checks

4.5.1 QC Summary

4.5.2 Execute QC Programs

4.5.3 Record Status Field

4.6 Review QC Output

4.7 Research Data Anomalies

4.8 Correct or Manually Upgrade Data

4.9 Run Record Counts

4.10 Upload Data to PPDB

4.11 Scan Paper Data Sheets

4.12 Populate AIMS Folders

4.13 Apply Software Releases

4.13.1 Backup Regional Database

4.13.2 Run Release Batch File

4.13.3 Review Output Files

4.13.4 Update RIMS Programs

Chapter 5. Central Operations

5.1 DBA Functions

5.1.1 Increase Tablespace Size

5.1.2 Clone Database

5.1.2.1 Before Cloning for the First Time

5.1.2.2 Cloning

5.1.3 Interpret Oracle Error Messages

5.1.4 Stop/Start the Database

5.1.5 Recover Database

5.1.6 Change Schema

5.1.6.1 Add/Remove/Modify Field

5.1.6.2 Add/Remove Keys/Constraints

vi

5.1.6.3 Add/Remove Table

5.1.7 User Administration

5.1.7.1 Add/Remove Users

5.1.7.2 Defining/Using Roles

5.1.7.3 Creating Public Synonyms

5.2 Software Performance Reports (SPRs)

5.2.1 Log New SPR

5.2.2 Update Existing SPR

5.2.3 Create Reports

5.2.3.1 SPR Form

5.2.3.2 SPR Reports

5.2.3.3 Software Change Notice (SCN) Report

5.2.3.4 Report Query Updates

5.3 Software Releases

5.3.1 Develop PPDB Software Release

5.3.1.1 Update Software

5.3.1.2 Create Update Scripts and Other Release Files

5.3.1.3 Identify CM Objects to Release (changed/created since last release) .. 78

5.3.1.4 Create Data Export Files

5.3.1.5 Create Release Batch File(s)

5.3.1.6 Package Release

5.3.1.7 Create Release Documents

5.3.1.8 Validate Release

5.3.1.9 Test Release

5.3.2 Apply PPDB Software Release

5.3.3 Develop LTPP Traffic Analysis Software (LTAS) Release

5.3.3.1 Create New LTAS Executable

5.3.3.2 Create Database Update Scripts

5.3.3.3 Create Release Batch File

5.3.3.4 Create Release Documentation

5.3.4 Apply LTAS Software Release

5.4 Regional Data Uploads

5.4.1 Confirm good backup of production database

5.4.2 Create an Empty Upload Directory Structure

5.4.3 Run record counts before data upload

5.4.4 Clone the IMSProd Database to IMSTest

5.4.5 Verify Regional Media and Organize Uploaded Data

5.4.6 Review Selected Upload Files

5.4.7 Update Upload Processing Files

5.4.8 Process Upload

5.4.9 Run record counts after data upload

5.4.10 Additional Data Processing

5.4.10.1 Run CN Assign on all data

vii

5.4.10.2 Run QC on SMP CP data

5.4.11 Correct Data Errors

5.5 Central Data Loads

5.5.1 Computed Parameters

5.5.1.1 Transverse Profile and Faulting CP Data

5.5.1.2 TRF_MEPDG CP Data

5.5.1.3 TRF_ESAL CP Data

5.6 Standard Data Releases (SDRs)

5.6.1 Update Data Dictionary and Table Dictionary

5.6.2 Update SDR master tables

5.6.3 Update SDR extraction program

5.6.4 Execute SDR extraction program

5.6.5 Review SDR databases for correct content

5.6.6 Compress SDR databases

5.6.7 Organize SDR Files into Volumes burn DVD media

5.6.8 Print and Burn DVD media

Appendix A. Reference Documents ....................................................................... A-1

A.1 PPDB User Guide .......................................................................................... A-1

A.2 QC Manual .................................................................................................... A-1

A.3 Accessing LTPP Data ................................................................................... A-1

A.4 LTPP Traffic Software Installation Guide ................................................. A-1

A.5 LTAS User Guide/Bookshelf ........................................................................ A-1

A.6 LTPP IMS Engineer Workguide ................................................................. A-1

A.7 HRD-LTPP Server Setup Document .......................................................... A-1

A.8 Regional Server Setup Document ................................................................ A-2

A.9 LTPP Server Upgrade to Oracle 9.2 ........................................................... A-2

A.10 LTPP Upgrade to Oracle 10g....................................................................... A-2

A.11 RIMS User Guide .......................................................................................... A-2

A.12 Source Code Inventory ................................................................................. A-2

A.13 SPR Database ................................................................................................ A-2

A.14 Directive GO-48 ............................................................................................ A-2

Appendix B. Sample QC Output Files .................................................................. B-1

Level C QC Output ................................................................................................... B-1

Level D QC Output ................................................................................................... B-3

Level E QC Output ................................................................................................... B-5

Appendix C. Sample Regional Upload Instructions ............................................. C-1 viii

Appendix D. Sample Directive for IMS Software Release .................................. D-1

Appendix E. Sample Software Release Batch File ............................................... E-1

Appendix F. Release Checklist ................................................................................ F-1

Appendix G. Sample Regional Backup Procedures ............................................. G-1

Appendix H. Sample Record Count Report .......................................................... H-1

Appendix I. Dell PE 2900 LCD Status Messages ................................................. I-1

Appendix J. Acronyms Used in this Document .................................................... J-1 ix

List of Figures

Figure 1. LTPP Information Management System (IMS) Figure 2. Major Database Components

Figure 3. RIMS Application provides access to PPDB Figure 4. Dell™ PowerEdge™ 4400 Server, from the front Figure 5. Dell™ PowerEdge™ 4400 from the back Figure 6. APC UPS Figure 7. Drive Status Indicators

Figure 8. Location of Redundant Power Supply Indicators Figure 9. Example search for OS updates Figure 10. Example search for Oracle update

Figure 11. Oracle Patches & Updates – Simple Search Window Figure 12. Oracle Patches & Updates - Patch 8559467 Download Window Figure 13. Hard-Drive Indicators Figure 14. Location of Redundant Power Supply Indicators

Figure 15. Example search for OS updates Figure 16. Example search for Oracle update

Figure 17. Oracle Patches & Updates - Simple Search Window Figure 18. Oracle Patches & Updates - Patch 7631956 Download Window Figure 19. Dell™ PowerEdge™ 4400 Server, from the front

Figure 20. Dell™ PowerEdge™ 4400 Server, from the back Figure 21. APC UPS

Figure 22. Dell OpenManage Array Manager Figure 23. Dell Array Manager Hardware Configuration Window

Figure 24. Dell Array Manager Array Tree Figure 25. Example search for Oracle update

Figure 26. Oracle Patches & Updates - Simple Search Window Figure 27. Oracle Patches & Updates - Patch 7631956 Download Window Figure 28. RIMS Main Menu

Figure 29. System Options Screen in RIMS Figure 30. DBA Utilities Available from RIMS Figure 31. Data Entry/Edit Menu

Figure 32. Screen List for INV Data Entry/Edit Figure 33. Inventory 1 Data Entry Form Figure 34. Regional Data Flow Diagram Figure 35. Oracle Documentation Library

Figure 36. Oracle Error Search Figure 37. SPR Database Objects and List of Forms Figure 38. SPR Auto form

Figure 39. List of Reports Figure 40. Report design view Figure 41. Report properties Figure 42. Query Builder view Figure 43. Empty Release Directory Structure x

Figure 44. ExtractStandardDataRelease.cpp before change Figure 45. ExtractStandardDataRelease.cpp after change

List of Tables

Table 1. PPDB Data Modules and Submodules Table 2. Hard-Drive Indicator Patterns for RAID

Table 3. Function of Redundant Power Supply Indicators Table 4. Hard-Drive Indicator Patterns for RAID Table 5. Function of Redundant Power Supply Indicators

Table 6. Filter, CN, and QC Programs and Chapters for each Data Module xi

Introduction

This manual is intended to support server and database operations for the Central LTPP

Pavement Performance Database (PPDB) and the LTPP Traffic Analysis Software

Database (LTAS DB). Much of the information that pertains to the LTAS Database and

Application exists in other documents and is referenced here to avoid duplication. See

Appendices A.4 and A.5 for specific references.

Software that may be run on the Central Server will be discussed in this manual, including the RIMS Application, which was originally developed for the regional offices to input and run checks on data collected by the LTPP Program. A list of software created in support of RIMS and LTAS operations can be found in the Source Code

Inventory Document, listed in Appendix A.12. Some of these programs were utilities written to accomplish a task and were not intended for general distribution. However, the majority of the programs are part of the RIMS or LTAS Applications.

Information for each server currently being used by the LTPP Program is in Chapter 2.

The section formatting is parallel for each group of servers in order to make it easier to locate required information. Some of the parallel sections have minimal information and have been left in as placeholders.

A discussion of regional operations is included in Chapter 3. This is not intended to be an exhaustive list of regional activities, but merely an overview of the main objectives. A useful diagram showing the flow of data through regional processing is shown in Figure

37.

Chapter 1. LTPP System Overview

1.1 LTPP Program

The LTPP program was established as part of the Strategic Highway Research Program

(SHRP) in 1987 and has been managed by the Federal Highway Administration (FHWA) since 1992. LTPP was designed as a partnership with the States and Canadian Provinces.

The LTPP program is a study of the performance of in-service pavement sections across the United States and Canada. These pavement sections have been constructed using highway agency specifications and contractors and have been subjected to real-life traffic loading. Pavement sections that are part of the LTPP program are categorized as General

Pavement Studies (GPS) and/or Specific Pavement Studies (SPS). GPS consists of a series of studies on nearly 800 in-service pavement test sections throughout North

America. SPS are intensive studies of specific variables involving new construction, maintenance treatments, and rehabilitation activities. Refer to the QC Manual, listed in

Appendix A2, for a list of GPS and SPS experiments.

1.2 LTPP Data

The majority of LTPP data has been collected by four Regional Contracting Offices

(RCOs). Each RCO is responsible for data collection in a region of North America. The

RCOs coordinate with state and provincial highway agencies (SHAs) in their regions to collect many types of data including details of maintenance and rehabilitation activities, coring and sampling activities, collection of site-specific weather data, drainage and traffic data. RCOs also collect deflection (FWD), distress, friction, longitudinal profile and transverse profile data. Each regional office has its own database server and the software necessary to enter and validate the data. This software comprises the Regional

Information Management System (RIMS) Application. See Section 1.4.1.3 and Chapter

3 for additional information on the RIMS Application.

1.3 LTPP Information Management System (IMS)

The LTPP IMS is comprised of all the information collected as part of the LTPP

Program. This includes electronic data and data on paper datasheets, in addition to documents, raw data files, videos, meeting minutes, software, reports, products, and much more that is not easily captured in a relational database. The electronic data and data entered from paper forms are loaded, processed, and stored in the Pavement

Performance Database (PPDB). The electronic media, paper data forms and other types of LTPP information are considered part of the Ancillary Information Management

System (AIMS) (Figure 1). Therefore, the LTPP IMS is made up of two major components – the PPDB and the AIMS (see Figure 2).

The PPDB and LTAS DB are relational Oracle databases that contain data elements that are easily organized into relational tables. The LTAS database is managed separately from the PPDB, so some information about the LTAS database and application are included in this document.

IMSOpsManual_v1.docx Thursday, December 16, 2010

This document provides information on operations related to the PPDB. References for the LTAS Database are included.

AIMS

Electronic

Data

Paper Forms

C

V Videos

Pictures

PPDB

Figure 1. LTPP Information Management System (IMS)

1.3.1 Central Database

The LTPP Database is maintained at a central location as part of the Technical Support

Services Contract (TSSC). Changes and additions to the database schema and RIMS

Application are developed at this central location and are subsequently distributed to the regional versions of the database.

The data in the central database has typically been uploaded from the regional databases once or twice annually. Data that is provided by outside contractors and does not require regional review or coordination is usually loaded directly into the central database. This includes climate and computed parameter data. Public data releases are created from this central production database.

1.3.2 Regional Databases

The regional databases have the same structure as the central database, though they contain only the data that pertains to a particular region. For that reason, the data files associated with the regional databases are smaller; they contain approximately one quarter of the data in the central database.

Once regional data has gone through data review, loading, and the QC process, the data is exported with the Oracle export utility and sent on electronic media to the central site where it is imported with the Oracle import utility into the central database.

1.4 Major IMS Components

The two major IMS components are the Pavement Performance Database (PPDB and the

Ancillary Information Management System (AIMS) (see Figure 2).

AIMS

Major IMS Components

Figure 2. Major IMS Components

1.4.1 Pavement Performance Database (PPDB)

The PPDB is a large Oracle database that contains many types of pavement performance data including materials testing, monitoring, maintenance and rehabilitation.

1.4.1.1 Production PPDB

In the fall of 2009, the production instance of the PPDB (IMSProd) moved to the Federal

Highway Administration (FHWA) Headquarters at the Turner-Fairbanks Highway

Research Center (TFHRC) in McLean, VA. It is housed on a powerful Windows 2008

Server in an Oracle 10g database.

1.4.1.2 PPDB Development/Test Environment

Both a development database instance (IMSDev) and a test database instance (IMSTest) are housed with the production database at TFHRC in McLean. These instances, or versions, of the database are used to develop and test updates to the production system.

Updates are subsequently applied to the production instance.

1.4.1.3 RIMS Application

The Regional Information Management System (RIMS) Application is made up of data entry forms, data loaders and Quality Checks (QC) programs (see Figure 3). This application is the user‘s interface with the data in the regional databases. The user can enter, review, edit and delete data from the database with this application. See 0 for detailed information about the RIMS Application. In addition, a list of software created in support of RIMS and LTAS operations can be found in the Source Code Inventory

Document, listed in Appendix A.12.

Data Entry Forms Data Loader

Programs

Quality Check (QC)

Programs

Figure 3. RIMS Application provides access to PPDB

1.4.1.4 PPDB Data Modules

The PPDB contains approximately 12,000 data elements that are organized into nearly

550 tables. These tables are grouped into data modules by data type. For example, the

Rehabilitation Module contains 54 tables that contain data about various rehabilitation events that have taken place on each section. Table 1 lists the modules and submodules that comprise the PPDB. With the exception of the tables in the Administration module, the first three letters of the table name (Table Prefix) identify the module to which a particular table belongs. For more detailed information about each module and the data it contains, refer to the LTPP Pavement Performance Database User Reference Guide

(PPDBURG) listed in Appendix A.1.

Table 1. PPDB Data Modules and Submodules

Data Module Table Prefix Description

Administration None This module contains tables that describe the structure of the database (LTPPDD, LTPPTD) and coded values used (CODES, CODETYPES, REGIONS). It also contains the master test section control table

(EXPERIMENT_SECTION), the section location table (SECTION_COORDINATES), the section layering table

(SECTION_LAYER_STRUCTURE), a regional lookup table (REGIONS), and a table of general section comments

(COMMENTS_GENERAL).

Automated

Weather Station

AWS This module contains data collected by the

LTPP program from automated weather stations installed on some SPS projects.

Climate CLM This module contains data collected from offsite weather stations that are used to compute a simulated virtual weather station for

LTPP test sections or project sites. Data in this module are updated at 5-year intervals and was last updated in 2008 with data through 2006.

Dynamic Load

Response

DLR This module contains dynamic load response instrumentation data from SPS test sections located in North Carolina and Ohio.

Ground

Penetrating

Radar

GPR This module contains Ground Penetrating

Radar (GPR) measurements performed on a subset of LTPP sections which provide an estimate of layer thickness variations within the monitoring portion of the test section.

Inventory INV This module contains inventory information for all GPS test sections and for SPS sections originally classified in maintenance and rehabilitation experiments.

Maintenance MNT This module contains information on maintenance-type treatments reported by a highway agency that were applied to a test section.

Monitoring MON This module contains pavement performance monitoring data and it is the largest module in the database. It is divided into submodules by data type:

Deflection MON_DEFL This submodule contains data from FWD tests.

Distress MON_DIS This submodule contains distress survey data from both manual and film-based (PADIAS) surveys.

Drainage MON_DRAIN This submodule contains information on the inspection of drainage features.

Friction MON_FRICTION This submodule contains friction measurements taken by participating highway agencies.

Profile MON_PROFILE This submodule contains longitudinal profile data collected by an automated profiler or by manual dipstick measurements.

Rut MON_RUT This submodule contains rutting data measured using a 1.2-m (4-ft) straightedge. These data tables are superseded by the rutting indices located within the Transverse Profile module.

(Note: Straightedge rut measurements were not taken on all test sections.)

Transverse

Profile

MON_T_PROF This submodule contains transverse profile data and computed transverse profile distortion indices (rut depth) from manual dipstick measurements or the optical Pavement Distress

Analysis System (PADIAS) method. Cross slope data is included in this submodule.

Rehabilitation RHB This module contains information on rehabilitation treatments.

Seasonal

Monitoring

Program

SMP This module contains SMP-specific data, such as the onsite air temperature and precipitation data, subsurface temperature and moisture content data, and frost-related measurements.

Specific

Pavement

Studies

SPS This module contains SPS-specific general and construction information.

Traffic TRF This module contains traffic load, classification, and volume data.

Test TST This module contains field and laboratory materials testing data. A key table in this module is TST_L05B, which contains layer thickness and composition information based on measurements from the test section site.

1.4.2 LTPP Traffic Analysis Database (LTASDB)

The LTAS Database is an Oracle database that contains summary traffic information for each LTPP site. An overview of LTASDB and support documents is included here.

1.4.2.1 Production LTASDB

The production instance of the LTASDB (TRFProd) also resides at the Federal Highway

Administration (FHWA) Headquarters at the Turner-Fairbanks Highway Research Center

(TFHRC) in McLean, VA. It is housed on the same Windows 2008 Server as the PPDB, in an Oracle 10g database.

1.4.2.2 LTASDB Development Environment

Both a development database instance (TRFDev) and a test database instance (TRFTest) are housed with the production database at TFHRC in McLean. The test instance is used to test database and software updates before they are sent to the regional offices. Updates are subsequently applied to the production instance.

1.4.2.3 LTPP Traffic Analysis Software (LTAS) Application

The LTAS Application is the user‘s interface to the LTASDB. The raw hourly and vehicle traffic data goes through automated checks as it is loaded and summarized to daily data records. Then, the daily records go through another series of automated checks and are summarized to monthly records. Finally, the monthly records are checked and summarized to annual records. The LTAS application provides tools to analyze the data including running the QC process, purging data, graphing data sets and reporting.

The LTAS Application is documented in the following volumes of the LTPP Traffic

Software Materials Reference:

Volume 1 – LTAS Users‘ Guide Volume 2 – LTAS Graphics Specifications Volume 3 – LTAS Oracle Table Specifications, Codes and QC Volume 4 – LTAS Functional Specifications Volume 5 – LTAS Program Design Specifications

The LTAS Users‘ Guide and information on the other LTAS volumes are listed in

Appendix A.5. In addition, a list of software created in support of RIMS and LTAS operations can be found in the Source Code Inventory, listed in Appendix A.12.

1.4.3 Ancillary Information Management System (AIMS)

The AIMS is a collection of information not contained in the PPDB. It includes items like raw profile and deflection data, distress photographs and images, reference documents, experiment guidelines, test protocols, etc.

LTPP began to catalogue and archive data (using the AIMS metadata) with the intent to provide the public with information about the availability of the AIMS online so that they may request this data through the LTPP Customer Support Services Center.

AIMS items combined with the PPDB are vital to the program's mission because they represent the LTPP legacy.

Chapter 2. LTPP Database Servers

The LTPP Program has provided servers and workstations to key contracts at various points over the last 20 years. Due to the way that servers have been upgraded, the program has a variety of distinct hardware and software environments. This situation has evolved primarily due to funding limitations.

The regions possess the oldest server environments. These Dell PowerEdge 4400 servers were purchased in September of 2001 and are running Windows Server 2000. They are long out of warranty. The TSSC has one of these older servers, but is also running a newer Dell 2900 server. This server was acquired in March of 2007 and is running

Windows Server 2003.

The newest server was purchased in April of 2009 and is located at FHWA headquarters at TFHRC in McLean, VA. It is a Dell 2900 running Windows Server 2008. This server became the new production server when operations were centralized at FHWA in the fall of 2009.

2.1 Server Basics

This section provides general information about server setup, maintenance, and software.

More specific information regarding the different servers being utilized at different physical locations is in the following sections.

2.1.1 General Hardware

The various LTPP servers do share some general characteristics. For one, the hard drives on each server are joined together in a single RAID 5 array. On the oldest and the newest

LTPP servers, the hard drives are partitioned into volumes and these volumes are set aside for different purposes. All of the servers have Microsoft Windows Server installed on the C: volume. The oldest servers at the regions have the Oracle database files stored on the D: volume. The TSSC server has only a single partition, and the databases are stored on the C: volume. The TFHRC server has the most partitions and the Oracle database files are stored on the G: volume.

The servers are also designed to be somewhat fault tolerant. They each feature multiple power supplies and hot swappable hard drives. While the uptime requirements of the

LTPP servers are not that high, this redundancy helps to keep the systems running while waiting on parts to arrive. On the down side, without someone checking the system for faults on a regular basis, the servers can keep running for a long time with failed parts.

This could lead to a loss of data if, for example, one hard drive has already failed and another fails before the first failure is repaired.

2.1.1.1 Server Setup

All of the servers have gone through a similar setup process. First, the operating system and various utilities such as the backup software were installed. Then the Oracle database software was installed and the database instances created. Minimal end user software has been installed on the servers.

2.1.1.2 Server Maintenance

The following guidelines are provided to assist in checking the PPDB servers for problems before they result in data loss. It is recommended that these checks be performed on a weekly basis, concurrent with the server backup process.

2.1.1.2.1 Physical Inspection of Server

Look at the front of the server; check that the lights on each disk in the array are green.

Amber or red lights indicate a problem with the disk.

Figure 4. Dell™ PowerEdge™ 4400 Server, from the front

Images taken from ―Dell™ PowerEdge™ 4400 Systems User's Guide― at http://support.dell.com/support/edocs/systems/pe4400/en/ug/intro.htm and ―Dell™ PowerEdge™

2900 Systems Hardware Owner's Manual‖ at http://support.dell.com/support/edocs/systems/pe2900/en/hom/html/about.htm.

http://support.dell.com/support/edocs/systems/pe4400/en/ug/intro.htm

Look at the rear of the server, check that the fans are rotating and that air is being blown out.

Figure 5. Dell™ PowerEdge™ 4400 from the back

Looking at the front of the UPS, check that the UPS indicates a full charge and that it is not overloaded.

Figure 6. APC UPS

Image taken from ―Dell™ PowerEdge™ 4400 Systems User's Guide― at http://support.dell.com/support/edocs/systems/pe4400/en/ug/intro.htm Images taken from APC User‘s Guide at http://sturgeon.apcc.com/techref.nsf/partnum/990-

7042A/$FILE/D7042A2.pdf

Check for airflow here

Not all lit All lit

2.1.1.2.2 Physical Inspection of Storage Units

Look at the front of the storage units; check that the power light (#2 in Figure 7) is green and that the enclosure status light (#3 in Figure 7) is a steady blue.

Flashing blue, flashing amber or steady amber lights require additional actions.

Figure 7. Dell™ PowerVault™ MD1000 Storage Enclosure Bezel (front)

Look at the rear of the storage units and verify that air is coming through the fans (#3 in Figure 8)

Figure 8. Dell™ PowerVault™ MD1000 Storage Enclosure (rear)

Image taken from ―Dell™ PowerVault™ MD1000 Storage Enclosure Hardware Owner‘s Guide‖ at http://support.dell.com/support/edocs/systems/md1000/en/HOM/index.htm.

http://support.dell.com/support/edocs/systems/md1000/en/HOM/index.htm.

If further investigation is required after checking the front bezel on either unit, remove the bezel by pushing in on the ―locks‖ in the middle of the bezel on both sides

(just behind #3 in Figure 7). The enclosure status light at #1 uses the same colors and coding as the enclosure status light visible on the bezel.

The next item to check will be the drive carrier status LEDs (#3) on each drive. The LED at #2 is an activity LED that lights when a drive is accessed. The codes for the drive carrier status LEDs are in

Table 2.

Figure 9. Dell™ PowerVault™ MD1000 from behind bezel

Table 2 Dell™ PowerVault™ MD1000 Drive Carrier Status LED conditions

LED Description

Off Slot empty; drive not yet discovered by server, or an unsupported drive is present

Steady green Drive is online

Green flashing (250 ms) Drive is being identified or is being prepared for removal

Green flashing

On 400 ms

Off 100 ms

Drive rebuilding

Amber flashing (125 ms) Drive failed

Green/amber flashing

Green On 500 ms

Amber On 500 ms

Off 1000 ms

Predicted failure reported by drive

Green /amber flashing

Green On 3000 ms

Off 3000 ms

Amber On 3000 ms

Off 3000 ms

Drive being spun down by user request or other nonfailure condition http://support.dell.com/support/edocs/systems/md1000/en/HOM/index.htm.

2.1.1.2.3 Software Inspection

Software inspection is specific to each server. Refer to specific server sections for this information.

2.1.1.2.4 Operating System Updates

If the server is connected to the Internet, use the Windows Update service to keep the operating system current on patches. If not connected to the Internet, periodically download the latest patches from the Microsoft web site to a transportable medium and manually apply the latest patches to the server.

2.1.2 General Software

By default, very little software beyond the bare essentials has been loaded onto the servers. Some of the servers have had Microsoft Office loaded in order to be able to use

Microsoft Access to process standard data releases.

2.1.2.1 Anti-Virus

Various antivirus products are in use. There is not a standard among the LTPP

Contractors and the FHWA.

2.1.2.2 Backup

Backup hardware and software vary by the age of the servers. The oldest of the servers use an old version of ArcServe and DLT IV tapes for backups. The TSSC server uses

Symantec Backup Exec and LTO-2 tapes. The TFHRC server, which is the newest, uses

Symantec Backup Exec 12 and RD1000 500GB Disk cartridges for backups.

While the backup software and devices vary, all servers are backed up using a similar backup strategy - weekly backups of the Oracle database to removable media. Due to the fact that most information entered into the database comes on either paper forms or electronic data sets, the risk of data loss is low. Therefore, a weekly backup to removable media has been chosen as the proper balance between risk and cost. Incremental backup policies vary by location.

2.1.2.3 ORACLE

Oracle was chosen early in the development of the IMS. At the time, it was one of the only tools which could handle large databases across multiple hardware platforms. Oracle also had a forms and reports package for application development which was able to run on multiple platforms without recoding.

Due to the distributed nature of the LTPP program, Oracle software updates have generally only been applied during major system upgrades. This was agreed upon by the

LTPP regional and TSSC contractors as the best balance between the risk of applying the updates and the risk of an external attack. The regional contractors have their servers on their corporate networks which are protected from the outside by firewalls; the TSSC server, which houses a central repository, is on an isolated network with access limited to

TSSC personnel in Oak Ridge, TN; the TFHRC server is also on an isolated network with access limited to FHWA personnel.

2.1.2.3.1 Relational Database Management System (RDBMS)

All of the servers are currently running the Oracle 10.2 RDBMS. The Regional and TSSC servers are running at the Oracle 10.2.0.3 patch level. Since Windows Server 2008 requires a minimum of Oracle 10.2.0.4, the TFHRC server is running at the Oracle

10.2.0.4 patch level. Given the limited access to these servers, it has been decided that it is not necessary to keep up with the latest patches. So, the Oracle software which is deployed is rarely patched.

2.1.2.3.2 Database Tools

Enterprise Manager

This is Oracle software that provides a Graphical User Interface (GUI) with which to view and manage database objects (tables, views, indices, tablespaces, users, etc.). This software is installed on the server when the Oracle RDBMS is installed and is installed on the workstation when the Oracle Client is installed.

SQLPLUS Worksheet

The SQLPlus Worksheet is a client tool that allows the user to type sqlplus commands in an input window and see results in an output window. Commands are executed by pressing F5 or CTRL-Enter. Previous commands can be accessed by pressing CTRL-p and next commands by pressing CTRL-n. Other shortcuts are available in this tool.

Command Line

SQLPlus and Oracle utilities can be executed from a DOS window. For example, a sqlplus file (.sql) can be executed with the following syntax:

sqlplus connectstring @sqlfile.sql

The Oracle data export command can be executed as follows:

exp connectstring parameters.par where the parameters.par file has all input parameters. The same list of parameters can be included on the command line. For help with the export command, type: exp help=y

2.1.2.4 Microsoft Office

Office has purposely been loaded onto some of the LTPP servers in order to process data releases efficiently. Since data releases are provided in Microsoft Access format, it is very important to have Office tools available.

2.2 Turner-Fairbanks Highway Research Center (TFHRC) Server

Specifics

The TFHRC server is the newest and most powerful server owned by the LTPP program.

It was purchased with the vision to be a central repository located at a FHWA facility instead of contractor facilities.

2.2.1 Hardware

The TFHRC server is a Dell PowerEdge 2900 with two 3 GHz Xenon E5450 quad-core processors. This server also has 32 GB of RAM, an internal RAID 5 Array consisting of eight 7,200 RPM 1-TB SATA disks and an external RAID 5 Array consisting of thirty

5,400 RPM 2-TB SATA disks. It is supplemented by two fully populated Dell

PowerVault MD1000 storage units (50TB). For backups, it has a PowerVault RD1000 which accepts 1-TB hard disk cartridges. All of this is protected by a 2200 VA UPS.

2.2.1.1 Server Setup

The server setup is detailed in HRD-LTPPServerSetup200906-1.doc (see Appendix A.7).

This document describes in detail how the RAID Array, operating system and Oracle databases were configured. The highlights of the server setup are as follows:

The server was delivered with the Windows Server 2008 Operating System. This was then configured to comply with NIST 800-53 Revision 2 Annex 1 since this is a low impact system.

The server was made the DHCP and DNS server for the private LAN to which it is attached. There is no domain controller on this network.

Oracle 10g 10.2.0.4, which is the first version that handles 64 bit databases under

Windows Server 2008, was installed on the server.

Once Oracle was installed, the production database instances (IMSProd and

TRFProd) were created.

Once the production instances were created, two test instances (IMSTest, TRFTest) and two development instances (IMSDev, TRFDev) were cloned.

2.2.1.2 Server Maintenance

2.2.1.2.1 Physical Inspection

The Hard Drives, Power Supplies, and the messages on the LCD panel should be checked once a week. A convenient time to perform this inspection would be during backups since someone has to be physically at the server to mount the backup cartridge. The following sections contain excerpts from the Dell PowerEdge 2900 Hardware Owner‘s

Manual located at https://support.dell.com/support/edocs/systems/pe2900/en/hom/html/about.htm and the

Dell PowerVault MD 1000 Hardware Owner‘s Manual located at http://support.dell.com/support/edocs/systems/md1000/en/HOM/index.htm.

The basic procedure is to look for lights that are not green. Each hard drive should have a green drive-status indicator. Each power supply should have a green power supply status and a green AC status indicator. And as a general check, the LCD panel should be lit with a blue light. If anything goes wrong on the system, the LCD panel will have an amber light which means further action is necessary.

Hard Drive Inspection

The hard-drive carriers have two indicators — the drive-status indicator and the drive-activity indicator

(Figure 10). In RAID configurations, the drive-status indicator lights http://support.dell.com/support/edocs/systems/pe4400/en/ug/intro.htm and ―Dell™ PowerEdge™ https://support.dell.com/support/edocs/systems/pe2900/en/hom/html/about.htm up to indicate the status of the drive. In non-RAID configurations, only the drive-activity indicator lights up; the drive-status indicator is off.

1 drive-status indicator (green and amber) 2 green drive-activity indicator

Figure 10. Drive Status Indicators

Table 3 lists the drive indicator patterns for RAID hard drives. Different patterns are displayed as drive events occur in the system. For example, if a hard drive fails, the

"drive failed" pattern appears. After the drive is selected for removal, the "drive being prepared for removal" pattern appears, followed by the "drive ready for insertion or removal" pattern. After the replacement drive is installed, the "drive being prepared for operation" pattern appears, followed by the "drive online" pattern.

NOTE: For non-RAID configurations, only the drive-activity indicator is active. The drive-status indicator is off.

2900 Systems Hardware Owner's Manual‖ at

Table 3. Hard-Drive Indicator Patterns for RAID

Condition Drive-Status Indicator Pattern

Identify drive/preparing for removal

Blinks green two times per second

Drive ready for insertion or removal

Off

Drive predicted failure Blinks green, amber, and off.

Drive failed Blinks amber four times per second.

Drive rebuilding Blinks green slowly.

Drive online Steady green.

Rebuild aborted Blinks green three seconds, amber three seconds, and off six seconds.

Power Supply Inspection

The power button on the front panel controls the power input to the system's power supplies. The power indicator lights green when the system is on.

The indicators on the optional redundant power supplies show whether power is present or whether a power fault has occurred

(see Table 4 and Figure 11).

Table 4. Function of Redundant Power Supply Indicators

Indicator Function

Power supply status

Green indicates that the power supply is operational.

Power supply fault Amber indicates a problem with the power supply.

AC line status Green indicates that a valid AC source is connected to the power supply.

http://support.dell.com/support/edocs/systems/pe4400/en/ug/intro.htm and ―Dell™ PowerEdge™

2900 Systems Hardware Owner's Manual‖ at

1 power supply status 2 power supply fault 3 AC line status

Figure 11. Location of Redundant Power Supply Indicators

LCD Status Messages

The system's control panel LCD provides status messages to signify when the system is operating correctly or when the system needs attention. The LCD lights blue to indicate a normal operating condition and lights amber to indicate an error condition. The LCD scrolls a message that includes a status code followed by descriptive text.

Appendix I lists the LCD status messages that can occur and the probable cause for each message. The LCD messages refer to events recorded in the system event log (SEL). For information on the SEL and configuring system management settings, see the systems management software documentation.

CAUTION: Many repairs may only be done by a certified service technician. You should only perform troubleshooting and simple repairs as authorized in your product documentation, or as directed by the online or telephone service and support team. Damage due to servicing that is not authorized by Dell is not covered by your warranty. Read and follow the safety instructions that came with the product.

NOTE: If your system fails to boot, press the System ID button for at least five seconds until an error code appears on the LCD. Record the code, then see Getting Help.

https://support.dell.com/support/edocs/systems/pe2900/en/hom/html/gethelp.htm#wp1057008

2.2.1.2.2 Software Inspection

Software inspection can be accomplished by using the Dell Remote Access Card

(DRAC). Since the physical inspection outlined in the previous section is sufficient, there is really no need to perform an inspection using the DRAC. All of the relevant messages will appear on the LCD panel.

2.2.1.2.3 Operating System Updates

Operating system updates are applied as individual patches downloaded from Microsoft on a separate computer since internet access is not available from the server. These patches are located by going to http://www.microsoft.com/technet/security/current.aspx and performing a Microsoft Security Bulletin Search. We are interested in searching for all bulletins for Windows Server 2008 x64 SP2. An example search is shown below, in

Figure 12.

http://www.microsoft.com/technet/security/current.aspx

Figure 12. Example search for OS updates

Each patch that has not been applied is downloaded to a flash drive or other portable media. The media containing the patches is then mounted on the server and the patches are applied while logged in as an administrator.

2.2.2 Software

The following is a list of key software packages that are loaded on the LTPP servers to facilitate server operations.

2.2.2.1 Anti-Virus

Symantec Endpoint Protection 11.0.2000.1567 is being used on the server at TFHRC.

This is the FHWA supported antivirus program.

2.2.2.2 Backup

Symantec Backup Exec 12.5 is being used to perform backups to RD1000 disk cartridges.

2.2.2.3 ORACLE

The Oracle Database server version 10.2.0.4 along with the administrative tools is installed on this server to house the PPDB and LTAS DB.

As operations are centralized, the cost/benefit ratio of applying the updates will change.

The centralized server will have a greater attack surface. This will make the updates more valuable. At the same time, by maintaining only a single server, the potential for a deployment problem is minimized.

Oracle patches can be downloaded from ―My Oracle Support‖ formerly Metalink. The general procedure is to perform a knowledge base search for the update that you are interested in. For example, you could search for ―Critical Patch Update July 2009 Oracle

Products‖. In the Patch Availability document, search for the table listing the critical patch update availability for Oracle Database. Then find the patch number for version

10.2.0.4 on Windows x86-64. In the example below, this is patch 8559467 (see Figure

13).

Figure 13. Example search for Oracle update

After locating the patch number, you proceed to the ―Patches & Updates‖ tab and select a simple search. Enter the patch number that you found in the table and hit ―Go‖ (see

Figure 14).

Figure 14. Oracle Patches & Updates – Simple Search Window

That will bring you to the actual download screen (see Figure 15).

Figure 15. Oracle Patches & Updates - Patch 8559467 Download Window

You should be sure to view the readme file. It will tell you which version of OPatch is required to install this patch and other prerequisites. It will also give you step by step instructions for installing the patch.

2.2.2.4 Microsoft Office

Microsoft Office has been installed on this server primarily to provide Microsoft Access for SDR processing. It is possible to update this software using the same procedure outlined in the preceding Operating System Updates section. Just choose Microsoft

Access as the product.

2.3 Technical Support Services Contractor (TSSC) Server Specifics

The TSSC server is a central repository for PPDB data as well as the development environment for IMS and Traffic Analysis software. It was purchased as an upgrade to a

Dell PowerEdge 4400 server like the regions are using.

While the current server does not mirror the regions physically, or even at the operating system level, it has still proved to be useful in diagnosing problems that have occurred at the regions. This is mainly because the Oracle environment is similar to the regions.

2.3.1 Hardware

The TSSC server is a Dell PowerEdge 2900 with two 1.86 GHz Xenon 5150 dual-core processors. This server also has 2 GB of RAM and a RAID 5 array consisting of seven

10,000 RPM 146 GB SAS disks. For backups, it has an internal LTO2 tape drive which uses LTO-2 tapes capable of holding 200GB uncompressed. All of this is protected by a

2200 VA UPS.

2.3.1.1 Server Setup

The instructions for the upgrade from Oracle 9.2 to Oracle 10.2 were used as the basic instructions for setting up this server. Those instructions are documented in ―LTPP

Upgrade to Oracle 10g_ppdb_trf.doc‖ referenced in Appendix A.10. While the steps about removal of Oracle 9.2 were skipped, the installation of the Oracle 10g server and the initial creation of the instances were followed. After the production instances were created, they were cloned to test and development instances. All of the files required for daily operations, such as the PVCS repository, were then copied from the PowerEdge

4400 to this machine.

Since the TSSC server has only one volume (C:), the layout of the database differs slightly from the regional servers who have two volumes (C: and D:) The database resides on D: volume in the regions and on the C: volume at the TSSC.

The highlights of the server setup are as follows.

The server in Oak Ridge, TN, came with Windows 2003 Server Operating System

Oracle 10g (10.2.0.3) was installed on the server

The production PPDB instance (IMSProd) was created on the server,…