YsA.Net

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 :

1. Oracle database : version 9i.
2. 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 pf thing, 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 stores in the database when using 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.

For example :

In the schema Tests there is a layer which is named "roads". The "roads" layer is composed of 3 tables :

1. "roads" table.
2. "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 :

1. 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.
2. Each version of SDE has changes in the SDE schema. So 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).
3. 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.

So you see 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 :) )...

ArcSDE (Spatial Database environment) is a software product from ESRI which handles geographic databases. I like to think about it as another tier which wraps all the access to the geographic features that are stored in the database. When you want to access some geographic data , SDE generates queries which access the geographic tables and retrieves the data.

After this very short intro, a question raises : How do know which queries and which operations SDE performs on the database ? This is critical when you have unexpected behavior in your application which you suspect originates in the database.

Luckily SDE has a "Debug Mode". In this mode SDE writes all the operations it performs to log files. Shani Raba helped me find some information about this mode. The following links explain how to initiate this mode (SDEVERBOSE & SDEINTERCEPT environment variables) and the ramification of it on performance :

http://www.systematics.co.il/gis/Support/sde/pdf/LOGS_ArcSDE.pdf (Hebrew)

troubleshooting guide

I have a couple of recommendation about using this mode :

1. Before enabling this mode,first try to create a simple program that recreates the problem as simply as possible (In my case a spatial query which get halts at some point and is being timed out). Only then enable this mode so you will have the exact information in the log.In this way you wouldn't need to dig the logs (which can contains couple hundred rows) for the information you need.
2. Initiate both modes at once (SDEVERBOSE & SDEINTERCEPT) - so you will have the maximum information required. SDEVERBOSE triggers an accumulating log for all the operations that are being performed by SDE. SDEINTERCEPT creates a log file for each operation (or transaction) that is being performed - so every operation has a different log file (the order of the operations is described by the extension of the log files : *.001,*.002,*.003 and so forth...
3. Perform the operation that causes you trouble and then turn the mode off : This is because the logs rapidly grows (In my case the mode has been turn on for about 30 minutes and the logs exceeded 120MB).
4. In ArcSDE 9.1/9.2 the name if the SDEVERBOSE log file is sde_esri_sde.log and not sde.errlog.
5. Remember that when you restart the SDE service the logs are erased. So before doing that copy them to somewhere else the the etc directory. This can be changed by changing an environment variable called SDELOGAPPEND to TRUE (for Unix only).

One more thing : There is another log file which is called giomgr_sde.log. This log contains information about the processes that are managed by SDE and can also be used for understanding the problem.

That's about it. Hope this helps...