A long time ago I presented a problem: How can you migrate from Oracle 9i with SDE 9.1 to Oracle 10g with SDE 9.2.
After a long time I have found a solution. This solution was suggested to me by Tom Brown from ESRI. In his visit in Israel Shani and I had a rare chance to meet him and ask him a couple of questions.

Definitions

Source: Oracle 9i + SDE 9.1

Destination: Oracle 10g + SDE 9.2

Process

  • Install on a different server Oracle 10g and SDE 9.2. DO NOT install the SDE schema in the database and DO NOT perform the Post installation of SDE. This will be our destination server.
  • Export your ENTIRE source database - all the schemas, including the SDE schema. We used the weekly backup of our database for this.
  • Import the export you created in the previous step, as is, to the destination database - again all the schemas.
  • Run the Post installation of SDE 9.2, but follow the instructions about the Upgrade and not the fresh installation instructions. Remeber to give the SDE schema the permissions described in the installation instructions.
  • Install the SDE service packs. Service pack 5 is the most recent.

That's it. So simple that it is aggravating...

Deep dive

So what is the magic here ?
The export/import is understandable , data wise - you have to import all of your data. But why migrate the SDE schema ? Why run the post installation with the upgrade instructions ?

The SDE schema contains metadata about the layers in all the schemas in the database. Every version of SDE contains changes and additions to the schema. When we migrated the SDE schema we infect migrated all of the metadata only the layers in our database, so all the layers are now registered as they were in the source db.
This saves us the need to deal with exporting the layers, one by one, and then import them again. We have to remember that when we migrated all the regular schemas, They contain the S/F table with the same numbers as in the source database, so by migrating the SDE schema we don't have to worry about that.

Now, we have an SDE schema in the new database, but it's a schema in the structure of the 9.1-9i version. So now we have to upgrade it to the 9.2-10g version schema. In order to do this we need to run the Post installation of SDE 9.2 with the instructions of an upgrade. They contain the following command:

sdesetup -o upgrade -d ORACLE10g -u <sde user name> -p <sde password>

This command does that exact trick - it upgrades the SDE version to 9.2 10g.

I have to note a drawback to this solution: It completely erases the destination database. Currently there is no option to merge the two SDE databases that contains also non-geographic data and other database objects - packages, sequences, etc.
All the exporting tools I found in ESRI's toolkits, and other toolkits, can not perform this successfully. I hope that a solution will be devised by the geodatabase team at ESRI.