Geographic Database Migration

I would like to present a problem I had when it was decided that our GIS application needed a new version of the geographic database.
Let me explain: Our geographic database includes two components:
Oracle database: version 9i

ESRI's SDE component: version 9.1

We decided that it was time to use the new versions of these components (Oracle 10g, SDE 9.2) so we can use their new features. We had no experience doing these sort of things, so I was assigned (As the team's applicative DBA) to find a way to do this properly, without losing information.
This operation, moving data from one source to another, is called Data Migration

"It is simple as you get" - every Oracle DBA will tell you - "Simply export the schemas from the old database, and import them to the new database".

Well, if I had only the non geographic data to worry about, I would probably to that. But the problem begins with SDE.

In order to understand the problem, let's explain how geographic information is stored in the database when using SDE.

Geographic Information in SDE

When you install the SDE component, you need to run scripts (according to the instructions) which creates a new schema in the database which is called SDE.
This schema contains, among else, metadata on the geographic tables (or layers) of all the schemas in the database!.
The geographic data of each layer is stored in several tables in the schema in which the layer was created:

Layer table: Which you, as the one who creates the layer, are naming. For example : "roads".
F/S tables: These are tables which are named with a prefix of F and S and a suffix of a number.
This number is an internal number which identifies the layer. SDE uses this number a couple of times in the metadata tables in the SDE schema. These tables contains the geographic data for each feature that is added to the layer.

There are more tables but they are used only for versioned layers

Example

In the schema Tests there is a layer which is named "roads". The "roads" layer is composed of 3 tables:
"roads" table, "S123" and "F123" tables.

In the SDE schema the layer is represented in a row inside the table "layers" which contains, among else, the id number of the layer.

More information about SDE can be found in ESRI developers network: ArcSDE 9.2 developer help.

"Wait!" - the nice DBA will say - "If SDE has a schema of it's own, then simply export this schema from the original database and import it to the new database also.".
WRONG! Why? There are several reasons:

The SDE schema doesn't only contain the metadata on the layers, it also contain additional data about the state of SDE itself.

For example: there is a table which contains data about the processes SDE runs at the moment.

Each version of SDE has changes in the SDE schema

If you import the schema from another version of SDE (like our case), the SDE simply would not work. (To be honest, there is a command in SDE which updates old schemas structure to a new schema structure:
sdesetup -o upgrade).

SDE schema contains metadata of all the schemas in the database!

That means that if you import the SDE schema to the new database which already contains schemas with layers, you will erase the data of the old schemas, which will cause other projects some trouble.

"Oracle import command contains an option of appending data to the tables that are imported, if they already exists in the schema" - the DBA will say. But what happens if there are two layers in the old and new database with the same ID name (which is used in the S/F tables)? All hell will break loose.
This is possible because the numbers of the layers are calculated with a sequence. So each database has it's own sequence numbers, and a collision between the id numbers will happen.
This is just a simple example to objects and values in the SDE schema that might collide when doing this.

You can understand that migration between these platforms is more complicated that is seems at first, and I didn't even mention differences between 9i and 10g.

What should we do? How to perform data migration between Oracle 9i + SDE 9.1 to Oracle 10g + SDE 9.2?

All this and more in the next post (After I will get it right...)

UPDATE

The solution to this problem is described in Geographic Database Migration - The solution post.