B
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 a release 8.0.4, 8.0.5, 8.1.6, or 8.1.7 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 Oracle8i database server. Therefore, you must upgrade both the database 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 Oracle8i 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 was 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 Spatial uses extensible indexing and object types in release 8.1; and therefore if a release 8.1 database instance is downgraded to release 8.0.5, the spatial objects must be deleted and re-created. In this case, the data must be exported and imported into the release 8.0.5 database. This, and other requirements, result in application incompatibility. A release 8.1 Spatial application will likely use the new spatial operators and therefore will not work with a release 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 a release 8.0.5 database server is upgraded to release 8.1, Spatial must also be upgraded. The reason has to do with using dynamic SQL in PL/SQL and with invoker's rights in release 8.1. Similarly, if a release 8.1 server is downgraded, Spatial must be downgraded too. Lastly, if a release 8.1 server is running in release 8.0 compatibility mode, Spatial will experience various failures unless it is reconfigured for release 8.0.5. You can reconfigure the product by running the downgrade script: c813d805.sql.

In summary:

The Spatial release and the Oracle8i database server release must match.
Upgrade and downgrade scripts must be run when upgrading or downgrading between releases 8.0.5 and 8.1.
Spatial will work in release 8.0 compatibility mode for a release 8.1 database server if, and only if, the downgrade script is run and users or applications attempt to use only the relational implementation of the product.

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 tablespaces.

Installation of Spatial requires that the COMPATIBLE init.ora parameter is set to 8.1.0.0.0 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, do the following:

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.
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.
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.

While connected as SYSTEM, enter the following:

     ALTER DATABASE RESET COMPATIBILITY  
     SHUTDOWN 
     Change  the init.ora parameter COMPATIBLE=8.0.0.0.0 
     STARTUP

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. The Oracle8i (release 8.1.5) Spatial release differed from pre-Oracle8i releases in that it:

Used object types (a varray-based type called SDO_GEOMETRY to store ordinates)
Supported new spatial data types, namely arcs and circles
Had new spatial operators (SDO_WITHIN_DISTANCE) and functions (SDO_POLY_UNION, SDO_BUFFER, SDO_POLY_INTERSECT, and SDO_POLY_XOR)
Utilized dynamic SQL in PL/SQL
Allowed invoker's rights
Tessellated a geometry as a whole rather than one element at a time

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 release 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 database 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 database server and Spatial from Oracle8i to release 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 release 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, you must migrate your spatial data from the release 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 a release 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:

From release 7.3.4 onward, Spatial has an UNSUPPORTED_GEOMETRY type that is always used in conjunction with a bounding box or polygon, which is used for indexing purposes and which encloses the spatial object. This did not exist in release 7.3.3.
From release 8.1.5 onward, Spatial supports arcs, circles, arc strings, and geometries made up of a mixture of arc and line segments.

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

B.5 Migrating from Spatial Release 8.1.5 or 8.1.6 to Release 8.1.7

To migrate from Spatial release 8.1.5 to release 8.1.7, you must first migrate from release 8.1.5 to release 8.1.6, as described in Section B.5.1, and then migrate to release 8.1.7, as described in Section B.5.2. Be sure that you perform any actions needed for data migration and compatibility for release 8.1.6 before migrating to release 8.1.7.

To migrate from Spatial release 8.1.6 to release 8.1.7, run the c816u817.sql script, as described in Section B.5.2.

B.5.1 Migrating from Spatial Release 8.1.5 to Release 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 delete all the spatial indexes built in release 8.1.5 database and re-create them in a release 8.1.6 database.

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

Make sure that the Oracle RDBMS is upgraded to release 8.1.6.
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:
```
SELECT PARAMETERS, INDEX_NAME FROM user_indexes, 
     sdo_index_metadata_table   
     WHERE INDEX_NAME = SDO_INDEX_NAME;
```
Save this information before upgrading to Spatial 8.1.6.
Run the following script: ORACLE_HOME/md/c815u816.sql

This script deletes 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):
```
INSERT INTO USER_SDO_GEOM_METADATA
   SELECT TABLE_NAME, COLUMN_NAME, DIMINFO, NULL
   FROM SDO_GEOM_METADATA;
```
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.
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.1.)
Manually re-create all the spatial indexes that are required.

B.5.1.1 Data Migration to Release 8.1.6

Spatial release 8.1.6 introduced 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.1.2 Compatibility Between Releases 8.1.5 and 8.1.6

Spatial release 8.1.6 changed 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 metadata 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
FOR EACH ROW
BEGIN

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

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

B.5.2 Migrating from Spatial Release 8.1.6 to Release 8.1.7

To migrate from Spatial release 8.1.6 to release 8.1.7, run the following script: ORACLE_HOME/md/c816u817.sql

B Installation, Compatibility, and Migration Issues