Oracle Spatial User's Guide and Reference
Release 8.1.6






Prev Next

Installation, Compatibility, and Migration Issues

This appendix provides information concerning installation, compatibility, and migration between various Oracle Spatial product releases.

Beginning with Spatial Data Option 7.3.3, all interfaces are supported in each subsequent release. A spatial application built for and using the 7.3.3 Spatial Data Option interfaces will work with an 8.0.4, 8.0.5, or 8.1.6 database server. The implementations of these interfaces have changed, and therefore PL/SQL packages from prior releases of the Spatial cartridge will not work with later releases of the Oracle database server. Therefore, you must upgrade both the server and Spatial at the same time if you wish to use older spatial applications with an Oracle8i release of Spatial.

Spatial must always be synchronized with the Oracle database server on upgrade or downgrade. In both cases, Spatial must be reinstalled.

B.1 Introduction

Spatial release 8.1 requires Oracle8i Enterprise Edition and the Objects Option. Spatial release 8.1 has been redesigned to use various Oracle8i object and extensibility features. Many of the Spatial release 8.1 features depend on new features in release 8.1 of the database server. Therefore, there are many compatibility and migration issues that need to be addressed in this release of Spatial. This appendix outlines the database and application compatibility issues.

Database compatibility issues exist because the product uses extensible indexing and object types in 8.1, and therefore if an 8.1 database instance is downgraded to 8.0.5, then the spatial objects must be deleted and re-created. In this case, the data must be exported and imported into 8.0.5. This, and other requirements, result in application incompatibility. An 8.1 Spatial application will likely use the new spatial operators and therefore will not work with an 8.0.5 instance unless it can identify the Spatial version and dynamically change its spatial queries.

An upgrade or downgrade of the database server version requires a corresponding upgrade or downgrade of Spatial. If an 8.0.5 server is upgraded to 8.1, Spatial also has to be upgraded. The reason has to do with using dynamic SQL in PL/SQL, and Invoker's Rights in 8.1. Similarly, if an 8.1 server is downgraded, Spatial must be downgraded too. Lastly, if an 8.1 server is running in 8.0 compatibility mode, then Spatial will experience various failures unless it is reconfigured for 8.0.5. You can reconfigure the product by running the downgrade script: c813d805.sql.

In summary:

B.2 Installation Details

To install Spatial, the script catmd.sql in the ORACLE_HOME/md/admin directory must be run as user MDSYS. The MDSYS user should be created with the set of privileges listed in ORACLE_HOME/MD/mdprivs.sql, and with both default and temporary tablespace.

Installation of Spatial requires that the COMPATIBLE init.ora parameter is set to or higher. This is required for the creation and definition of Spatial index types and operators. Thus, if the database was created with a compatibility parameter value of 8.0.n.n.n, the DBA must shut down the database and restart with COMPATIBLE=8.1.n.n.n.

B.2.1 Changing from 8.1 to 8.0 Compatibility Mode

If Spatial has been installed and the database compatibility needs to be reset to 8.0.n.n.n from 8.1.n.n.n, then do the following:

  1. Determine if there is any user data that contains instances of the type MDSYS.SDO_GEOMETRY. That is, determine if any user table has a column of type MDSYS.SDO_GEOMETRY and has data in it.

  2. If there are instances, delete all spatial indexes on these columns. Delete the data in these columns or delete these columns and tables. If there are no instances, go on to the next step.

  3. Run the script c813d805.sql in ORACLE_HOME/md/admin. This will delete all spatial objects that require 8.1 compatibility. That is, all the object-relational implementation objects for Oracle Spatial will be deleted. The relational implementation available in release 8.0.n.n.n will remain installed and accessible.

  4. While connected as SYSTEM, enter the following:

         Change  the init.ora parameter COMPATIBLE= 

After running ORACLE_HOME/MD/c813d805.sql, resetting the database compatibility to 8.1.n.n.n from 8.0.n.n.n requires running the script ORACLE_HOME/MD/c805u813.sql to reinstall and enable the object-relational implementation of Spatial.

B.3 Compatibility Details

All releases of the Spatial product provide a set of predefined spatial data types, topological operators such as RELATE, and a spatial indexing mechanism. This Oracle Spatial release differs from pre-Oracle8i ones in that it:

All interfaces preceding Oracle8i are maintained, but the package bodies have been changed to use the preceding features. Thus for Oracle8i, the Spatial packages must be reinstalled to use these interfaces even if the compatibility parameter is set to 8.0.

No data migration is needed and the 7.3.4/8.0.4 spatial applications will work without modification. Any OCI-specific migration issues must be handled in the same manner as they would have to be for any OCI application.

The 7.3.4/8.0.4 to 8.1.5 upgrade requirements are the same. Upgrade both Oracle8i and Spatial. Perform all the necessary steps for an upgrade. Your spatial applications will continue to work as before.

Downgrading from 8.0.5 or earlier releases to a previous release of the server and Spatial requires no special steps specific to the Spatial implementation. However, this situation is different for Oracle8i. In Oracle8i, Spatial uses objects and extensible indexing. Therefore, it creates database objects specific to Oracle8i that are not compatible with previous releases of the database server. When you downgrade the server and Spatial from Oracle8i to 8.0.5, a spatial-specific downgrade script must be executed to remove all the spatial geometry type, index type, and spatial operator definitions.

B.4 Data Migration Issues

Beginning with 7.3.3, all subsequent releases can work with spatial data from previous releases. That is no data migration is required. The situation is different in Oracle8i because Spatial now allows two storage mechanisms. If you want the features specific to Oracle8i, such as extensible indexing and spatial operators, then you must migrate your spatial data from the 7.3.3 columns-of-numbers style to the SDO_GEOMETRY storage scheme. Spatial provides a stored procedure and sample code that demonstrates one way of migrating data and metadata.

Migrating data on downgrades is more complex. Spatial provides OCI demonstration programs to read SDO_GEOMETRY instances and store them in an 8.0.5 spatial schema for comparable data. The demo also addresses issues related to the changes in the way metadata is stored in Oracle8i compared to previous releases. The complexity arises from the following:

You cannot store arcs and circles in any release earlier than Oracle8i, and you cannot use data from a 7.3.4 or later spatial layer in 7.3.3 if it contains instances of type UNSUPPORTED_GEOMETRY (etype=0).

B.5 Migrating from Spatial Release 8.1.5 to 8.1.6

Spatial release 8.1.5 uses objects and index types to create spatial indexes. However, currently there is no tool to convert release 8.1.5 spatial indexes to the new format for release 8.1.6. Therefore, you must drop all the spatial indexes built in 8.1.5 database and re-create them in an 8.1.6 database.

Follow these steps to upgrade from release 8.1.5 to release 8.1.6 of Spatial:

  1. Make sure that the Oracle RDBMS is upgraded to 8.1.6.

  2. Find out how the current spatial indexes are built (including what the index parameters are), so that you can re-create all the indexes after the upgrade.

    To see which users have spatial indexes and the spatial index parameters, enter the following SQL statement:


    Save this information before upgrading to Spatial 8.1.6.

  3. Run the following script: ORACLE_HOME/md/c815u816.sql

    This script drops all the spatial indexes and installs types and packages related to Spatial 8.1.6. It also migrates the data from all the SDO_GEOM_METADATA tables in each user's schema to the new xxx_SDO_GEOM_METADATA views managed by Spatial.

    However, this metadata migration is done only for those layers that have a spatial index built on them. If you need to do this metadata migration separately, you can use the following SQL statement (once for each user with spatial data):


    For example, if you connect as user Herman and execute that statement, it migrates all the metadata in user Herman's SDO_GEOM_METADATA table.

  4. Migrate the geometry objects to release 8.1.6-style types by executing the SDO_MIGRATE.FROM_815_TO_81X procedure on each table that has geometry data. (This step is strongly recommended, as explained in Section B.5.1.)

  5. Manually re-create all the spatial indexes that are required.

B.5.1 Data Migration

Spatial release 8.1.6 introduces new SDO_GTYPE and SDO_ETYPE values, to better manage the geometry data. To take advantage of these benefits, it is strongly recommended that the data be migrated to the new SDO_GTYPE and SDO_ETYPE values.

To initiate this data migration, execute the SDO_MIGRATE.FROM_815_TO_81X procedure on each table that has geometry data. This procedure updates all the geometries to set the SDO_GTYPE and SDO_ETYPE values.

B.5.2 Compatibility Between Releases 8.1.5 and 8.1.6

Spatial release 8.1.6 changes the way the geometry metadata is managed. In release 8.1.5, the metadata is managed by the users by keeping the metadata in SDO_GEOM_METADATA tables in each user's schema. In release 8.1.6, the metadata is centrally managed under the MDSYS schema, and the user can access and manipulate the metadata through metadata views.

A release 8.1.5 Spatial application will fail against a release 8.1.6 database if it tries to access the metadata. Therefore, if you need to run a release 8.1.5 application with a release 8.1.6 database, you need to keep the SDO_GEOM_METADATA table and make sure that the USER_SDO_GEOM_METADATA view and the SDO_GEOM_METADATA table are consistent all the time.

A recommended method for ensuring consistency is to create SQL triggers on the SDO_GEOM_METADATA table to perform a corresponding insert, update, or delete operation on USER_SDO_GEOM_METADATA for each such operation on SDO_GEOM_METADATA. For example, if user SCOTT has spatial data and needs to keep the SDO_GEOM_METADATA table consistent with the new metdata views, SCOTT can create a trigger (shown in Example B-1) that inserts data into the USER_SDO_GEOM_METADATA view whenever SCOTT inserts data into the SDO_GEOM_METADATA table.

Example B-1 Insert Trigger for Metadata Consistency

CREATE TRIGGER scott_sdo_meta_ins_trig
before insert on sdo_geom_metadata
referencing new as n

 INSERT INTO user_sdo_geom_metadata 
 VALUES(:n.table_name, :n.column_name, :n.diminfo, NULL);

User SCOTT can create similar triggers for delete and update operations on the SDO_GEOM_METADATA table.

Prev Next
Copyright © 1999 Oracle Corporation.

All Rights Reserved.