Implementation of ORACLE in an ArcInfo - ARCVIEW GIS Environment.

• ORACLE Licensing scheme
• ORACLE Terminology - The RDBMS language.
• ORACLE Installation process - Building a strong foundation.
• ORACLE Training programs.
• Configurations required to support the ORACLE Server.
• PC Connectivity.
• ARCStorm and ORACLE.

Traditionally, the use of GIS has been restricted to a relatively few highly technical and experienced users within organizations. In this setting the implementation of GIS results only in a limited overall benefit to the organization. True enterprise wide implementation of GIS into the normal business process has therefore been a long time goal of many organizations.

The enterprise implementation of GIS requires foremost the ability for the GIS database management system to coexist and integrate with an organization's information systems infrastructure. ArcInfo and ARCView products have provided this functionality through external database integrators. This functionality provides GIS users with the ability to access and use data from a number of Relational Database Management Systems (RDBMS) including: Oracle, Sybase, Informix and Ingres.

This paper provides an introduction into the recommended steps in the implementation of an Oracle database for use in an organization's GIS. It should be noted that many of the issues discussed are generic to the implementation of any of the RDBMS packages supported by Esri.

Why not "Info".

The incorporation of Oracle into an organization's GIS functions adds a significant layer of complexity to the operations of the system. Oracle also requires a sizable investment in additional costs and support of the system. However, unless the GIS database can remain completely isolated from the remainder of an organization's other information systems, the implementation of an RDBMS such as Oracle is ultimately required.

In general, Oracle provides the following advantages:

1. Performance. On a typical project an ASCII data set was loaded into INFO. This process required approximately 14 Hours to complete. The same data set was later loaded into Oracle in under 1/2 hour.
2. ANSI SQL Compliance. The INFO database and its SQL interface is not ANSI compliant and therefore incompatible to all other SQL type databases. Oracle is a complete ANSI compliant system with a standard SQL interface.
3. None Client Server. The INFO database management system is not based on Client Server technology. The INFO database can be accessed only using INFO or ArcInfo Products. Oracle is a client server system which permits access to the data from any system on the network.
4. Single User Update. INFO permits only single user update of its database without the ability to lock records or fields. Oracle permits multi-user updates of a single database with a variety of locking mechanisms.
5. Database Recovery. Oracle provides capabilities for reconstruction of the database up to the last transaction in the event of a system emergency. INFO databases can be restored from backup only. All updates since the last backup would be lost.
6. Security. INFO provides no additional security beyond system level access. (i.e. An individual with access to add to the database can also delete the entire database or modify various field). Oracle provide multiple layers of database security based on user names and assigned roles.
7. Third party development platform. There are multiple vendors with application development products which work with Oracle using C, C++, Visual C, Visual Basic, MS Access, etc. There are no application development products which work with INFO.
8. Multi-platform Access. A variety of PC and workstation based products can be used to enter and retrieve data from an Oracle database. Only Esri products can enter or retrieve data from an INFO database.
9. SDE. Esri is moving toward the use of more sophisticated databases such as Oracle. The Spatial Data Engine (SDE) which is Esri's new database query system is entirely based on the Oracle Client/Server technology without the ability to use INFO.
10. Data views. Corporate databases such as a Tax Assessors databases typically contain over 100 fields and multiple thousands of records. INFO provided no facility for creating viewable subsets of a database. Oracle permits creation of databases Views which are not copies, rather are a window into a particular subset of fields and/or records of a larger database. Use of alias field names in Oracle also provide a more user friendly view of a database.
11. Robust input/output. Oracle provides specific modules for import of large data sets and for creation of specialized reports. INFO provides no facilities for large data imports and provides an extremely limited reporting capability.
12. System Resource Management. Oracle provide a suite of tools for management and utilization of system resources down to the percent CPU used for Oracle.
13. Parallel databases Management. Many organizations require copies of a single database at multiple different sites. Oracle provides facilities for synchronization of remote databases using either a full or a part time network connection.
14. Unlimited database Size. The Oracle database is limited in size only by the system's resources (i.e. the system's disk capacity).

Oracle Implementation a Step by Step Approach:

Pre-purchase Planning (the effort in planning up front will avoid expenses later) :

• Identify number of GIS Coverages, their size and their linkage to Oracle.
• Identify the number GIS tables and record and table sizes.
• Identify number of consecutive GIS and none GIS users of Oracle. Consider the users with a need for multiple simultaneous connections to Oracle (e.g. users on workstation ArcInfo) separately than users with the need for a single connection (e.g. PC ARCView users running one ARCView session at any given time.)
• Identify Network protocol, server configuration and available disk storage.
• Identify programming requirements. (i.e. will in-house development of client-server applications outside of ArcInfo or ARCView be required)

• Purchasing Oracle (You can negotiate a deal):
  • Find a reputable Oracle Business Partner where Oracle products can be purchased. Oracle Business Partners can typically provide better pricing that direct purchases through Oracle. Ideally, the Business Partner would have a background in ArcInfo and can provide some technical assistance as well.
  • Oracle licensing is considerably more complex than ArcInfo and/or ARCView. The type of product (Server, Enterprise, etc.) or license (Named, Seat, etc.) required depends highly on the planned application of the system. Licensing requirements may vary significantly from one GIS site to the next.
  • Don't over buy. If budgeting cycles and availability of funds is not an issue, buy only what is required to get the project going. Other products can be added as the need arises. Additionally, many of the Oracle products are available for trial use. Once the project is started other products can be installed and tested for applicability.
  • Be sure that all the pieces are in place. For example: when installing an Oracle database with the intention of serving PC based ARCView Clients, the minimum of following components are required:
    • Oracle Server (Unix or Intel based)
    • ARCView 2.x for PC
    • Oracle SQLNet for PC
    • ODBC drivers for PC
    • Physical Network Connectivity
    • Network software (TCP/IP or IPX)

Post-purchase Implementation (A:Setup won't do it):

• Attend Oracle classes. At a minimum the individual assigned as the Database Administrator (DBA) should attend the Introduction to SQL and SQLPlus, Database Administrator I and Database Administrator II classes at Oracle. Each class is one week long. Please note that your selected Oracle Business Partners can also sell Oracle training classes at a reduced cost.
• Identify the Oracle Model: Mini, Medium or large. Oracle can be configured to serve low data requirements (Thousands of records per day) to high data requirements (Millions of records per day).
• Identify required table spaces. Many organizations will require the implementation of multiple table spaces for use in quality control, testing, user data and the GIS data.
• Define database parameters. Server memory size, disk requirements, backup levels (Cold / Hot), Instance name, index names - locations - and sizes, Redo log files - names - locations - and sizes, dedicated CPU option, control files, schemas and objects, rollback segments, etc.
• Develop installation plan. A set of SQL scripts can be used to create the required database instance and other required tables. These scripts can also be used in the database disaster recovery procedures.
• Oracle Installation. The installation of the server components of Oracle can performed once the aforementioned steps are completed. If applicable, the installation should also include: changes to the appropriate UNIX databases including: /etc/passwd, /etc/groups, /etc/services, config.ora, listener.ora and various user login files such as .cshrc, .profile, .login etc.
• Configure Oracle user access profiles. User table access, roles, quotas and privileges should be configured.
• ArcInfo - Oracle extensions should tested. If the ArcInfo Oracle extensions have not previously been installed they should be loaded from the ArcInfo product CD.
• Establish backup / recovery procedures. Backup methodology, Hot/Cold backup procedures should be determined and testes.
• Load data into Oracle database. If appropriate, the SQL*Loader utility can be used for import of large ASCII data sets.
• Register Oracle databases with ARCStrom. If GIS Coverages are stored in ARCStrom, the related Oracle databases must be registered with ARCStrom. This process involves the execution of several scripts and addition of special columns to the Oracle database and if appropriate, the development of Oracle database locking triggers.
• Perform system tests. Tests should include the connection of ArcInfo and ARCView to Oracle, transfer of attribute files to Oracle, transfer of Oracle Tables to ArcInfo, Execution of dynamic query from ArcInfo to an Oracle table.
• Documentation. Finally the most import aspect of the installation of Oracle is documentation of the process in a DBA manual. The DBA manual at a minimum should include:
  • Complete description of the hardware and network, including disk partitions.
  • List of all Oracle components installed on the various systems and their disk location.
  • Listing of all scripts used during the installation of Oracle.
  • Description of all tests performed during the installation process.
  • Description of all tables, including schemas, indexes, etc.
  • Description of the Security mechanisem on the system (Roles, users etc.).
  • Description of all changes made to the UNIX databases.
  • Description of procedures for adding and deleting users from the system.
  • Login/logoff procedures.
  • Backup, Recovery and Archive procedures.
  • Database disaster recovery procedures.
  • Oracle Technical Support Number, Contract number, description of support type (Silver etc.)
  • Other system specific information needed in the event of an emergency.

• Conclusion:
  • The transformation of GIS from specialized tool to a broadly used enterprise information system will require an investment into more sophisticated data management techniques. Oracle can provide the database platform necessary to accomplish this transformation. As with any advancement, the challenge is in the up front planning required to insure a successful implementation.