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 an 8.0.4, 8.0.5, or 8.1.3 database server. The implementations of these interfaces have changed and therefore PL/SQL packages from older versions of the spatial cartridge will not work with newer versions of the Oracle database server. Therefore, you must upgrade both server and Spatial at the same time if you wish to use older spatial applications with new Oracle8i releases.

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

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 features this option depends on are new in release 8.1 of the database server. Therefore, there are many compatibility and migration issues that need to 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:

Spatial release and the Oracle database server release must match
Upgrade and downgrade scripts must be run when upgrading or downgrading between 8.0.5 and 8.1
Spatial will work in 8.0 compatibility mode for an 8.1 database server if and only if the downgrade script is run and users or applications only attempt to use 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 tablespace.

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.x.x.x, the DBA must shut down the database and restart with COMPATIBLE=8.1.x.x.x.

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.x.x.x from 8.1.x.x.x, then 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 Oracle8i Spatial will be deleted. The relational implementation available in release 8.0.x.x.x will remain installed and accessible.

While connected as SYSTEM, enter the following:

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

After running ORACLE_HOME/MD/c813d805.sql, resetting the database comptability to 8.1.x.x.x from 8.0.x.x.x requires running the script ORACLE_HOME/MD/c805u813.sql to re-install 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 Spatial release differs from previous ones in that it:

Uses object types (a varray-based type called SDO_GEOMETRY to store ordinates)
Supports new spatial data types, namely arcs and circles
Has new spatial operators (SDO_WITHIN_DISTANCE) and functions, SDO_POLY_UNION, SDO_BUFFER, SDO_POLY_INTERSECT, and SDO_POLY_XOR
Utilizes Dynamic SQL in PL/SQL
Allows Invoker's Rights
Tessellates a geometry as a whole rather than an element at a time

All interfaces preceding Oracle8i are maintained, but the package bodies have been changed to use the above 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.3 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 Oracle8i specific database objects 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, indexmethod 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:

From 7.3.4 onward, Spatial has an UNSUPPORTED_GEOMETRY type that is always used in conjunction with a bounding box or polygon, used for indexing purposes, which encloses the spatial object. This did not exist in release 7.3.3.
From 8.1.3 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 7.3.4 or later spatial layer in 7.3.3 if it contains instances of type UNSUPPORTED_GEOMETRY (etype=0).

B Installation, Compatibility, and Migration Issues