Data migration from one storage type to another

Geodata » Administering geodatabases » Geodatabases in Oracle » Inside a geodatabase in Oracle

You can use the Migrate Storage geoprocessing tool to migrate existing binary, spatial, or raster columns from one storage type to another. This is done by specifying a configuration keyword that contains an ATTRBUTE_BINARY, GEOMETRY_STORAGE, or RASTER_STORAGE parameter set to the new storage type to which you want to convert the data.

It is important that the keyword be properly created to include the correct parameter and value. If you specify a keyword with incorrect or missing information, the information is read from the DEFAULTS keyword. For this reason, Esri recommends that you create a custom keyword specifically for the migration and make sure the keyword contains the parameter and value to which you are migrating the data, as well as a UI_TEXT parameter. The UI_TEXT parameter makes the keyword available for use to ArcGIS clients.

To add a keyword to the DBTUNE table requires the sdedbtune command. ArcSDE administation command tools can be installed with the ArcSDE application server.

Tip:

To learn about configuration keywords and parameters, see What are DBTUNE configuration keywords and parameters? and its related topics.

The following are the supported migration paths by database management system (DBMS):

DBMS	Configuration parameter	Migrate from/to
Oracle	ATTRIBUTE_BINARY	LONG RAW to BLOB
	GEOMETRY_STORAGE	LONG RAW (SDEBINARY) to BLOB (SDELOB)
		LONG RAW (SDEBINARY) to ST_GEOMETRY
		BLOB (SDELOB) to ST_GEOMETRY
		SDO_GEOMETRY to ST_GEOMETRY
	RASTER_STORAGE	LONG RAW to BLOB
		LONG RAW to ST_RASTER*
		BLOB to ST_RASTER*
PostgreSQL	RASTER_STORAGE	BYTEA to ST_RASTER*
SQL Server	RASTER_STORAGE	IMAGE to ST_RASTER*
	GEOMETRY_STORAGE	SDEBINARY to GEOMETRY
		SDEBINARY to GEOGRAPHY
		OGCWKB to GEOMETRY
		OGCWKB to GEOGRAPHY

Tip:

If the table being migrated is registered as versioned, migrating it to a different storage type also updates the corresponding columns in the Adds table. If the feature class has archiving enabled, the archive table's columns are also updated

Why would I migrate my data?

There are two reasons you would migrate your data: 1) to be able to access your spatial or raster data using structured query language (SQL); 2) to move from a data type that may not be supported in the future to one that is.

SQL access

Accessing the information in a geodatabase via SQL allows external applications (those not developed in an ArcObjects environment) to work with the tabular data managed by the geodatabase. If these applications need to access spatial or raster data in the geodatabase, you must store your spatial or raster data in data types that allow SQL access. For example, using the ST_Raster storage type allows you to access your raster data with SQL, something that you cannot do easily if your raster data is stored in a BLOB, LONG RAW, IMAGE, BINARY, or BYTEA field.

Moving from types that may not be supported in future releases

Oracle is recommending the use of BLOB or BFILE data types instead of LONG RAW data types in its databases. Although LONG RAW columns are still supported, if you have LONG RAW attribute, geometry, or raster fields in your current ArcSDE geodatabase in Oracle, you should migrate them to a different format in preparation for when they are not supported.

The storage for the attribute, geometry, and raster columns in a geodatabase is controlled by the DBTUNE parameters ATTRIBUTE_BINARY, GEOMETRY_STORAGE, and RASTER_STORAGE, respectively. The defaults for these parameters under the DBTUNE DEFAULTS configuration keyword are different depending on which release of ArcGIS you were using when you created your geodatabase. The following table shows the default setting under the DEFAULTS keyword in the DBTUNE table of ArcSDE geodatabases in Oracle.

Parameter	Default at ArcGIS 9.3 and later releases	Default at ArcGIS 9.2	Default prior to ArcGIS 9.2
ATTRIBUTE_BINARY	BLOB	BLOB	LONG RAW
GEOMETRY_STORAGE	ST_GEOMETRY	LONG RAW (SDEBINARY)	LONG RAW (SDEBINARY)
RASTER_STORAGE	BLOB	LONG RAW	LONG RAW

Note:

Data created in new (not upgraded) 9.3 or later release geodatabases using the default parameter settings do not use the LONG RAW storage type. However, any existing data created with any or all of these parameters set to LONG RAW or any new data in upgraded geodatabases that have these parameters set to LONG RAW will still contain LONG RAW columns. To change the data types for these columns, you must alter your DBTUNE settings and migrate the data.

Beginning with ArcGIS 10.1, feature classes created in geodatabases in SQL Server use the Microsoft geometry type be default. To move your existing feature classes to the geometry storage type, use the Migrate Storage geoprocessing tool or a Python script.

To alter the DBTUNE settings, use the sdedbtune command to add a parameter to an existing keyword or to export, alter, then import the contents of the DBTUNE table. See the ArcSDE Administration Command Reference for information on using the sdedbtune command. As mentioned previously, the command and reference are installed separately and can be downloaded from the Esri Customer Care Portal.

Before you migrate...

The following conditions must be met before you convert your data:

• You must make a backup of the data before you migrate it.
• If you are converting the spatial column type, the data must be stored in high precision. If your data is currently stored with basic precision, you must first migrate it to high precision before you migrate the storage type. This can be done with either the Upgrade Spatial Reference geoprocessing tool or the sdelayer command with the alter operation. See Migrating to high precision for information on migrating the precision of a dataset.
• If you are converting the spatial column, the table must contain an object ID column. Registering a layer with the geodatabase automatically adds this column.
• The configuration keyword you specify when migrating the data type has to contain the correct value for the GEOMETRY_STORAGE, ATTRIBUTE_BINARY, or RASTER_STORAGE parameter. For example, if you want to migrate a LONG RAW geometry column to ST_GEOMETRY, but you specify a keyword that has the GEOMETRY_STORAGE parameter set to SDO_GEOMETRY, the migration will fail because that is not a supported migration path.
• When data is migrated from one data type to another, a new segment is created in the database to which the data is copied. Once the migration is complete, the metadata gets repointed to the new segment and the old one is deleted. That means during the migration, there are two copies of the data; therefore, make sure you have enough room in the database for two copies of the data.
• Since attribute, raster, and geometry storage can be migrated to a BLOB data type in Oracle, it is recommended that you read the topic BLOB data storage in geodatabases in Oracle before proceeding with the migration.
• You must be logged in as the owner of the table that contains the column being migrated.
• Migration of a feature class to the SQL Server GEOGRAPHY type requires that the data be in one of the geographic coordinate systems supported by the GEOGRAPHY type, and that the feature class not contain z or m-coordinate values.