Database by wuyunyi




       Patient Database

                   TECHNICAL SUPPORT
                       TRAINING GUIDE

Topic Objectives


       Database Function and Interrelationships

       How Data Gets into the Database

                      This topic examines how data:

                          • gets into the database
                          • is extracted from the database to be viewed by the user
                          • interacts with other components in the IMPAX for Cardiology System

               TRAINING GUIDE




       Oracle Database

                       IMPAX for Cardiology uses the Oracle database, a relational implementation of
                       the DICOM-standard translated into tables -- making it efficient to extract data
                       out of the database.

                       The database is not DICOM-compliant internally but is DICOM-compliant on
                       the data input and output.

       Brains of the System

                       The database is the brains of the system and the single point of failure. Without
                       the primary Oracle database, users are limited to calling up images from the
                       Emergency database or via other contingency data source configured to support
                       the system during recovery

       Database Interaction with Other Components in the System

                       Every component that works with the Oracle Database is a “two-way street”. The
                       exception is HIS (the hospital information system). The pages that follow detail
                       the relationship of these components to the database.

                                                                                  TECHNICAL SUPPORT
                                                                                      TRAINING GUIDE


How Data Gets into the System
               DICOMservice brings data into the system in two ways:

                   • DICOM Data Source (modality or DICOM Gateway Alternative)
                   • DICOMservice transfer of images from a remote system

               Header data associated with the image is parsed and indexed into the database
               via SQL insert and update statements.

How the Image File is Handled

               The image portion of the image file is not written into the database. DICOM-
               store compresses the original image file from TIFF to U-JPEG using a lossless
               compression technique, then creates the appropriate file structure in the appro-
               priate file location and moves the compressed file there.


       File Pointers

       File Pointers

                       The file system has subdirectories for patient and series. Studies from store
                       server, DVD, CD or other data sources are inserted into the database at which
                       time the file pointer information is created.

                       When we request images, we are searching the database -- which provides the
                       file pointers to the images. File pointers to image files are UNC paths (this is true
                       of primary DICOM images and web images). The images are then extracted
                       from the online file system or data archive and displayed on the Cardiology
                       Workstation for study presentation.

                                                                                TECHNICAL SUPPORT
                                                                                    TRAINING GUIDE

Cardiology Workstation

ODBC Open Database Communications

             Most of the communication with the database is via ODBC, Open Database
             Connectivity, a standard.

             Samples of ODBC queries include:
                • A list of patients displays on the Cardiology Workstation
                • A full path to display an image file
                • A report displaying

             ODBC provides links to other applications.


             Java Database Connectivity is utilized for the Java Applet portion of Report En-


       Cardiology Workstation as Source of Data into the Database

                       The Cardiology Workstation uses the database to determine what patient studies
                       are available to view and where the image files are located, but more than that,
                       the Cardiology Workstation can write to the database through several methods:

                           •   Copy Destination command
                           •   Reporting
                           •   Save as Still
                           •   Load a bitmap from a scan and save it as a study

       Copy Destination

                       The Cardiology Workstation writes to the database from a foreign CD with the
                       Copy Destination command (version 2.06).

                       Copy Destination is an automated way of appending a .raw extension to images
                       and placing them into the raw folder. The image then propagates to databases
                       throughout the system: online, emergency, web, and multi online. By default
                       with version 2.06, a database is configured to be disabled as a copy destination.
                       By checking the box, the customer is forced to use Copy Destination to copy im-
                       ages from an external source, and this places the images in the required destina-


                       The Cardiology Workstation and Cardiology Remote Station write to the data-
                       base through reporting.

       Save as Still

                       When a frame is displayed in a video file on the Cardiology Workstation, it can
                       be saved as a still. When the medical professional annotates a frame, it creates a
                       secondary capture and adds it to the study. It is placed into the file system and
                       entered into the database.

                                                                                 TECHNICAL SUPPORT
                                                                                     TRAINING GUIDE

Cardiology Remote Station

Connection to Oracle Database

               The underlying connection for Oracle 8i is Net8 and SQLnet. For Oracle 9i it is
               SQLnet. Configuration is the same for both 8i and 9i: TNSnames.ora,
               SQLnet.ora and Listener.ora in the Network or Net80 folder.

               This is part of the Network Protocol Stack. The TNS (Transparent Network Sub-
               strate) is the layer above the TCP/IP layer. The ODBC layer operates on top of
               the TNS or Oracle Net8i/SQLnet

               The Cardiology Remote Station uses ASP (active server pages) to query the data-
               base, creating an SQL query run against the database. The result is an Internet
               Explorer web page within the Cardiology Remote Station application.


       Database Admin Tool

       Oracle Client

                        The Oracle client is required to run Oracle forms.

       Tool Locations

                        The 8i tool is located on the Admin Station; the 9i tool is web-based.

                                                                                   TECHNICAL SUPPORT
                                                                                       TRAINING GUIDE

Report Engine


               At this time, a report is just data entries in the database. When the user enters
               values by clicking check boxes and radio buttons and then moves to the next
               page, the reporting application does a series of insert statements into the data-
               base. Disparate pieces of data linked to a particular study become a readable re-
               port once a style sheet is applied.

               To display a report, an HTML viewer is rendered within the Cardiology Work-
               station application making a connection to Internet Explorer.

New Generation of Reporting

               Currently, when a report is finalized, data can no longer be changed. This will
               change with the next generation of reporting when reports will be associated
               with 'state'.

               Reports stored with the new Outbound Reporting (on a separate server) will
               write PDF and XML files, changing the way reporting works.


       HIS, CIS and Clinical Systems


                      HL7 is the primary language of most hospital information systems. Most of the
                      communication with the hospital information system (HIS) is via the Interface
                      Server. With Outbound Reporting (not directly part of the Oracle database),
                      data will be sent by way of HL7 directly to the HIS information system.

       HIS and the Database

                      The database interfaces directly or indirectly with the Hospital Information Sys-
                      tem (ICIS is in between the HIS and the database). The database does not up-
                      date the HIS unless it is through sending reporting information through

                                                                                       TECHNICAL SUPPORT
                                                                                           TRAINING GUIDE

Database DVD & DRO Status Generator

A Daily Batch File

                  A batch file called “dvdstatus.bat” is scheduled to run a series of four SQL que-
                  ries daily that write information in a comma-separated value format (.csv files)
                  on the Admin Station. This file can be viewed in an Excel spreadsheet. Called the
                  “offline database”, the customer can view the number of archived studies and
                  where the studies are stored.

                  When monitoring sites, check that dvdstatus.bat has been modified to call a fifth
                  batch file to perform a daily export of the database to the S Drive off Admin Sta-
                  tion. It should run daily at 6 am.

Four .CSV Files

                  The four .csv files: are:

                       •   Online archive
                       •   Online not archive
                       •   Nearline
                       •   Purge


       Physical Architecture

                  TECHNICAL SUPPORT
                      TRAINING GUIDE

Oracle Versions


       Oracle 8i: Hard Drive/RAID Configuration

       Database Server

                         For Oracle 8i, the database server is a Poweredge 2650 with 5 hard drives. The
                         first two and second two hard drives are mirrored; the 5th is potentially a hot

       Drive Size

                         The drives from Dell are 36 gigabytes. For a 200K database, the first two drives
                         are upgraded to 72 gigabytes to make room for the database. If the spare is a 72
                         gigabytes, it can be a failover for either container. If the spare is only 36 gi-
                         gabytes, it isn’t really sufficient for capturing the data of the two containers.

       Hot Spare

                         If a customer loses a drive, the hot-swap drive allows Technical Support person-
                         nel time to rebuild the crashed drive. By bringing the hot-swap drive into the ar-
                         ray, Technical Support can rebuild files to it for temporary storage.

                                         TECHNICAL SUPPORT
                                             TRAINING GUIDE

Oracle 8i: Logical Drive Configuration


       Oracle 8i File Locations

                                                                                     TECHNICAL SUPPORT
                                                                                         TRAINING GUIDE

Oracle 8i File Locations: System Database


               All of the system, data files, the primary control file and online review log files
               are located in the Oradata\Enco directory.


               This is the database recovery file when using RMAN (Oracle Recovery Man-
               ager). It is the database that keeps track of the requirements for RMAN to re-
               cover data.


               This is the system index file that contains indexes for system tablespaces.


               The rollback segment tablespace is the tablespace that contains the rollback seg-
               ments -- individual objects, similar to online redo log files, circular in nature,
               and utilized to roll back transactions to the last commit point. There must be at
               least one rollback segment.


           If, on a rare occasion, rollback segments become corrupt, they are taken off-line.
           They need to be recovered or deleted.

           Redo log files (online)

           Redo log files (online) are the main primary redo logs. They contain “before”
           and “after” images of all transactions. If a database has to be recovered, Oracle
           looks to the redo log files to locate transactions to apply to the database to roll it
           forward to a point of recovery. The database will utilize the online redo log files
           to aid in recovery to the degree possible; additionally, it will look to an archive
           redo log.

           The three redo logs are a circular buffer scheme; each log is written and filled. By
           the time Redo Log 3 is filled, Redo Log 1 has already been archived at which time
           it can be overwritten.

           In most cases, there are few problems when using redo logs for database recov-
           ery. However, if the archive processor fails or is unable to free Redo Log 1, this
           must be fixed.

           Most issues with redo logs involve corrupt files. They will either be able to be re-
           covered with database recovery or, if they are at the “half-back” point, they
           should be deleted to allow the database to recreate them. This is a last ditch re-
           covery effort.

           In all likelihood, all that will be lost are pending transactions. Because the IM-
           PAX for Cardiology system can ‘recook’ images, at most, only some report data
           will be lost.


           System.dbf is the main system file containing all the tablespaces that Oracle is
           using. All data objects that the system is using are contained in the tablespace.


           This is used for sorting (queries).


           This is used for any Oracle tools that are running.


           This is used more frequently in environments where the database is undergoing
           active development.

                                                                                      TECHNICAL SUPPORT
                                                                                          TRAINING GUIDE

Oracle 8i File Locations: Database

Oracle Database Files

               Oracle database files are in the Data folder and have a .dbf extension. This will be
               a different number of files from customer to customer depending upon the size
               of the database.

               Indexes are stored in the Index directory.


       Oracle 8i File Locations: G

       Mirrored Redo Log Files

                      Mirrored versions of redo log files can be used in the event of redo log file cor-

                                                                                       TECHNICAL SUPPORT
                                                                                           TRAINING GUIDE

Oracle 8i File Locations: Network Folder

               Listener.ora is the SQLnet/Net8 listener configuration file. It is only important
               on the server. It tells the listener the port that it is listening on and provides the

               SQLnet is the SQLnet/Net8 network configuration file, containing Oracle name
               and client authentication. It includes the parameters for the network side of


               Tnsnames.ora contains SQLnet pointers that represent connections to the data-
               base for the Oracle client.


       Oracle 9i: Hard Drive/RAID Configuration

                                         TECHNICAL SUPPORT
                                             TRAINING GUIDE

Oracle 9i: Logical Drive Configuration


       Oracle 9i File Locations

       Oracle 9i Installation

                           Installation of the Oracle 9i client is scripted. Installation creates an ODBC con-
                           nection that includes the underlying SQLnet connection. The database ships
                           with 4 data files, 2 index files, 1 static file and 10 gigabytes of datafiles. Data and
                           Index folders are 1.5G; Status is 1G.

                           In the Oracle 9i database, nothing is pre-sized, including tables. Oracle, through
                           scheduled tasks and other means, manages itself and grows over time.

       Oracle 9i IP Info

                           Oracle 9i binds to port 0000. It listens on all IP addresses.

                                   TECHNICAL SUPPORT
                                       TRAINING GUIDE

Oracle 9i File Locations (cont.)


       Logical Architecture

                                                                                   TECHNICAL SUPPORT
                                                                                       TRAINING GUIDE

Heartlab Schema Diagram

Mostly Reporting Data

               The lower left corner of the schema displays tables for capturing image data. All
               other tables are used for storing report-related information.

               The diagram shows the entities, attributes and relationships of the IMPAX for
               Cardiology database design that are translated into the tables, fields and rela-
               tionships of the database schema.


       Heartlab Schema

       3 Tablespaces

                       Oracle 9i allows for more performance tuning than did the Oracle 8i database.
                       As a result, in the Oracle 9i databases in use in the IMPAX for Cardiology sys-
                       tem, there are now three tablespaces.

                       Enco_static includes read-only tables such as Equipments and other report-re-
                       lated tables where insertions, updates and deletions are minimal.

                       The index tablespace is also relatively static. Both tablespaces are configured dif-
                       ferently from tablespaces that undergo more activity.

       Extent and Table Management

                       Oracle 9i has local extent and table management. The database does not have to
                       be pre-sized. Table extents and tablespace/datafile expansion are dynamically
                       controlled by the database and scheduled procedures.

                       Oracle 9i dynamically looks at the data and fits those objects into the database. It
                       can do this internally within tablespaces and has datafile tablespace manage-
                       ment as well.

                                                                                       TECHNICAL SUPPORT
                                                                                           TRAINING GUIDE


Minimal Data Loss

              Backup files exist in the event there is corruption on the main database file. If
              backup and recovery are working correctly, there should be minimal or no data
              loss. Technical Support should be able to recover to a point in time just prior to a

              Redo Logs

              Oracle writes redo log files, continuously overwriting them. It also makes copies
              of the redo logs -- called archive redo logs -- and writes them to a location called
              Archive. If a system crashes, Technical Support can take the last backup file, ap-
              ply an archive log file and then roll the database forward to a time prior to crash.

              Image Re-indexing

              If archive logs are lost, it is possible to re-index the images. As a result, image in-
              formation will not be lost. If archive log files became corrupt, a day's worth of
              report changes may be lost.


           Export Log

           Another redundancy is the Export file. Each day, Oracle makes an export (or
           dump file) of the data; an Oracle export of the user that owns the schema. The
           only loss will be changes in the data since that export was created.

                                                                                       TECHNICAL SUPPORT
                                                                                           TRAINING GUIDE

Oracle 8i Backups

System/Program Backup: Veritas Backup Exec

                  Backup Exec provides a full daily backup of the operating system and the Oracle
                  program files. This essentially covers the contents of the C and D drives (not in-
                  cluding Oracle data files). There is a different backup folder for each day of the

Data Backup: Oracle Hot Backup

                  Oracle Hot Backup is used for Oracle data files. A weekly backup is done each
                  Wednesday and twice again on alternate days creating Oracle A and Oracle B
                  versions. As a result, backup of database files is at least three days old.

Backup Location

                  On newer systems, backups are made to the external USB drive attached to the
                  Admin Station. The number of data files is the same over time.


       Oracle 8i Infrastructure


                 Oracle tuning is conducted through a statistics package (tuning package). It gen-
                 erates statistics for the cost-based optimizer, allowing Oracle to do a better job in
                 optimizing how it executes queries against the database.

                                                                                    TECHNICAL SUPPORT
                                                                                        TRAINING GUIDE

Oracle 9i Backups

System/Program Backup: Windows Backup

              Windows Backup provides a daily full backup of the operating system and the
              Oracle 9i program files. One folder holds seven files.

Data Backup: RMAN

              The Oracle Recovery Manager (built by Legatto) backs up the Oracle data files.
              It has a GUI-driven interface. It keeps the last seven backups.


              The SPfile folder is a binary copy of the init.ora file. It is a second folder that
              holds a full backup.

              Full and Differential Backups

              RMAN does a full backup for 100K and 200K databases on Sunday and Wednes-
              day, and an incremental cumulative for a 500K database. A 500K system has a
              third folder that holds the differential backups. All of these get backed up to an
              external hard drive on the primary admin station.



                         Install the most recent full then most recent cumulative files.

       Backup Location

                         Backups are made to the external hard drive off the Admin Station. The number
                         of data files is the same over time.

                                                                                TECHNICAL SUPPORT
                                                                                    TRAINING GUIDE

Oracle 9i Infrastructure

Additional RMAN Functionality

               RMAN automatically manages archive files and tuning. Once every 28 days,
               RMAN runs a tuning script and checks database free space (tablespace by ta-
               blespace). If there is less than 30% free space, RMAN adds another data file.



       How the Database Handles Data

                     Place the letter:

                          • T (for True) next to a correct statement
                          • F (for False) next to an incorrect statement

                               Header data associated with the image is parsed and indexed
                               into the database via SQL insert and update statements.

                               Header data associated with the image is parsed and indexed
                               into the database via SQL insert and update statements.

                               The image is saved to the database.

                               1. The user requests images
                               2. The database provides file pointers to the images,
                               which are UNC paths.
                               3. The images are extracted from the online file sys-
                               tem or archive and are displayed

                                                                                         TECHNICAL SUPPORT
                                                                                             TRAINING GUIDE

Oracle 8i and Oracle 9i Distinguishing Features

                For each related pair in the table:

                Place the letters:

                     • 8i next to the statement that applies to the Oracle 8i version of the database
                     • 9i next to the statement that applies to the Oracle 9i version of the database

                          Forms 10g

                          Forms 6

                          DB Admin Tool location is web-based

                          Admin Station is the DB Admin Tool location

                          1 Oracle home

                          2 Oracle homes

                          RAID Configuration: 5 physical drives (36GB and/or 72GB),
                          2 RAID-1 containers, hot spare possible

                          RAID Configuration: 5 physical drives (72GB), 1 RAID-10
                          container, hot spare configured

                          Logical Drive configuration: C, D, G

                          Logical Drive configuration: C, D, I, G

                          Logical Architecture:
                          2 tablespaces: needs to be resized by a Database Administra-

                          Logical Architecture:
                          3 tablespaces; has extent and table management

                          Underlying connection: SQLnet

                          Underlying connection: Net8 and SQLnet

                          Backup programs:
                          Backup Exec for OS/Oracle program
                          Hot Backup for Oracle data files

                          Backup programs:
                          Windows Backup for OS/Oracle program
                          RMAN for Oracle data files

                          Backup location: external hard drive off the Admin Station

                          Backup location: external USB drive off the Admin Station


       System Database and Network Files
                      Match the options to its description.


                          •   Control.crl
                          •   DRO1.dbf
                          •   Index.dbf
                          •   RBS.dbf
                          •   Redo.log
                          •   System.dbf
                          •   Listener.ora
                          •   SQLnet.ora
                          •   TNSnames.ora

                                        RMAN database recovery file

                                        Locates transactions that the database needs applied to roll it

                                        Configuration file containing Oracle name and client

                                        Primary control file

                                        Rollback segment tablespace utilized to roll back
                                        transactions to the last commit point

                                        File containing all the Oracle tablespaces

                                        Contains SQLnet pointers that represent connections
                                        to the database for the Oracle client

                                        System index file containing indexes for tablespaces

                                        Listener configuration file that tells the listener the
                                        port it is listening on and provides parameters

                                                                       TECHNICAL SUPPORT
                                                                           TRAINING GUIDE


SQL Plus Exercise
               This exercise is designed for practice with SQL Plus:

                    1.   Log into SQL Plus
                    2.   Run query: select * from tab;
                    3.   Use describe command: desc patients;
                    4.   Shutdown database
                    5.   Delete alternate control file
                    6.   Restart database



To top