A
Installation, Compatibility, and Migration

A.1 Introduction

Many of the Spatial release 9i features depend on new features in release 9i of the database server. Therefore, there are compatibility and migration issues that need to be addressed in this release of Spatial. This appendix outlines the database and application compatibility issues.

An upgrade or downgrade of the database server version requires a corresponding upgrade or downgrade of Spatial. If an Oracle8i (8.1.5, 8.1.6, or 8.1.7) database server is upgraded to an Oracle9i database server, Spatial must also be upgraded. Similarly, if an Oracle9i database server is downgraded, Spatial must be downgraded too. Lastly, if an Oracle9i database server is running in Oracle8i compatibility mode, features that are new for Spatial in release 9.0.1 (Oracle9i) will not work.

In summary:

• The Spatial release and the Oracle database server release must match.

• Upgrade and downgrade scripts must be run when upgrading or downgrading between Oracle8i and Oracle9i.

A.2 Installation of Spatial

This section applies to new users of Oracle Spatial. If you are upgrading from a previous release of Spatial, see Section A.3, "Upgrading from Spatial Release 8.1.5, 8.1.6, or 8.1.7" or Section A.4, "Upgrading from Spatial Release 9.0.1".

When you install Oracle release 9.2.0, the option to install Spatial is preselected by default. If you accept this default, you do not need to perform the installation steps described in this section, because the MDSYS user is already created and locked automatically.

If you create an Oracle database using the Database Configuration Assistant (DBCA), Spatial is installed by default and you do not need to perform the installation steps described in this section.

If you did not select the option to install Spatial at installation time and you want to install Spatial later, follow these steps.

1. Connect to the database instance specifying AS SYSDBA.

2. Create the MDSYS user with a command in the following format:

   SQL> CREATE USER MDSYS IDENTIFIED BY <password>;
   
   

3. Grant the required privileges to the MDSYS user by running the following procedure:

   SQL> @ORACLE_HOME/md/admin/mdprivs.sql
   
   

4. Connect as MDSYS.

5. Install Spatial by running the following procedure:

   SQL> @ORACLE_HOME/md/admin/catmd.sql
   
   

After you install Spatial, it is strongly recommended that you lock the MDSYS user. The MDSYS user is created with administrator privileges; therefore, it is important to protect this account from unauthorized use. To lock the MDSYS user, connect as SYS and enter the following command:

SQL> ALTER USER MDSYS ACCOUNT LOCK;

A.3 Upgrading from Spatial Release 8.1.5, 8.1.6, or 8.1.7

If you are upgrading from Spatial release 8.1.5, 8.1.6, or 8.1.7 to Spatial 9i release 2 (9.2.0), and if you have not chosen the automatic upgrade option, perform the following steps to upgrade to Spatial release 9.2.0.

1. Make sure that the Oracle RDBMS is upgraded to release 9.2.0.

2. Connect to the database instance specifying AS SYSDBA.

3. Grant the required privileges to the MDSYS user by running the following procedure:

   SQL> @$ORACLE_HOME/md/admin/mdprivs.sql
   
   

4. Connect as MDSYS.

5. Perform the migration by running the following procedure:

   SQL> @$ORACLE_HOME/md/admin/c81Xu9X.sql
   

A.4 Upgrading from Spatial Release 9.0.1

If you are upgrading from Spatial 9i release 1 (9.0.1) to Spatial 9i release 2 (9.2.0), and if you have not chosen the automatic upgrade option, perform the following steps to upgrade to Spatial release 9.2.0.

1. Make sure that the Oracle RDBMS is upgraded to release 9.2.0.

2. Connect to the database instance specifying AS SYSDBA.

3. Grant the required privileges to the MDSYS user by running the following procedure:

   SQL> @$ORACLE_HOME/md/admin/mdprivs.sql
   
   

4. Connect as MDSYS.

5. Perform the migration by running the following procedure:

   SQL> @$ORACLE_HOME/md/admin/c901u920.sql
   

A.5 Upgrading LRS Data

If you have linear referencing data (that is, geometries with measure information) in release 8.1.5, 8.1.6, or 8.1.7 format, you must upgrade that data to the format for Spatial releases 9.0.1 and 9.2.0, as follows:

1. Drop any spatial indexes on the table with the linear referencing data.

2. Find out which dimension of the object has the linear referencing information.

   This could be the third or the fourth dimension, depending on the dimensionality of the data. For example, if the data has three dimensions (such as X, Y, and height), the LRS geometry object is 4D, and the LRS dimension in this case is usually 4.

3. Make sure that the data is in the format for release 8.1.6 or higher (that is, it has 4-digit SDO_GTYPE values).

4. Update the LRS geometry objects by setting the LRS dimension in the SDO_GTYPE field, as in the following examples.

   Example 1: The LRS dimension is 3 for the geometries in the GEOMETRY column of table LRS_DATA. Update the SDO_GTYPE as follows:

   UPDATE LRS_DATA a SET a.geometry.sdo_gtype = a.geometry.sdo_gtype + 300;
   
   

   Example 2: The LRS dimension is 4 for the geometries in the GEOMETRY column of table LRS_DATA. Update the SDO_GTYPE as follows:

   UPDATE LRS_DATA a SET a.geometry.sdo_gtype = a.geometry.sdo_gtype + 400;