Transferring GeoRaster Data Between Databases

3.16 Transferring GeoRaster Data Between Databases

GeoRaster data can be transferred between schemas in the same database or between databases. There are several ways to transfer the GeoRaster data:

Using GDAL or the GeoRaster ETL tool to transport the GeoRaster data between schemas in the same database or between databases. In this approach, you export the rasters from the source GeoRaster table into an exchange file format, such as GeoTIFF, and then import them into the target schema or database.
Using the SDO_GEOR.copy or SDO_GEOR.changeFormatCopy procedures to transfer GeoRaster data between schemas in the same database. The GeoRaster objects are copied from one schema to another directly, if access is granted.
Using Data Pump Export and Import utilities to transfer GeoRaster data between schemas in the same database or between databases. See Using Data Pump Utility to Transfer GeoRaster Data for more information.
Using Transportable Tablespaces to transfer GeoRaster data between databases. See Using Transportable Tablespaces To Transfer GeoRaster Data for more information.
Using Oracle Database Link to transfer GeoRaster data between databases. See Using Database Link with GeoRaster Data for more information.

It is required that in any GeoRaster database, the name of each raster data table (RDT) must be unique and the pair of (rasterDataTable, rasterID) of a GeoRaster object must be unique in a database (see Raster Data Table). So, when transferring GeoRaster data between databases using the Data Pump, the Transportable Tablespace, or Database Link approaches, conflicts of rasterDataTable name or rasterID in the target database might happen. Since changing the RDT name and making it unique will automatically make the pair of attributes (rasterDataTable, rasterID) unique, it is recommended to always resolve the conflicts by changing RDT names, as shown in the examples in the following subsections for each of these data transfer approaches. If a GeoRaster table with the same name already exists in the target database, it is also recommended to create a new GeoRaster table for the transferred GeoRaster objects in the target database instead of appending them to the existing GeoRaster table.

Parent topic: GeoRaster Database Creation and Management

3.16.1 Using Data Pump Utility to Transfer GeoRaster Data

Data Pump Utility can be used to transfer the GeoRaster data between schemas in the same database or between databases. The following instructions are for transferring GeoRaster data between databases. But, they also apply to GeoRaster data transfer between schemas. For information about the Data Pump Export and Import utilities, see Oracle Database Utilities.

To export GeoRaster data using Data Pump, do as you would for other types of data, but exclude the GeoRaster internal DML triggers (whose names start with GRDMLTR_) and the internal DDL triggers (named SDO_GEOR_ADDL_TRIGGER and SDO_GEOR_BDDL_TRIGGER). For example:

expdp herman DIRECTORY=dump_dir DUMPFILE=data.dmp 
TABLES=herman.georaster_table,herman.rdt_1,herman.rdt_2 
PARFILE=exclude.par
Enter password: password

In the preceding code, the exclude.par file contains the following:

exclude=trigger:"like 'GRDMLTR_%'"
exclude=trigger:"= 'SDO_GEOR_ADDL_TRIGGER'"
exclude=trigger:"= 'SDO_GEOR_BDDL_TRIGGER'"

The following are the general steps to import GeoRaster data (that is, the GeoRaster tables and the associated raster data tables (RDTs)) into a target database using Data Pump:

Ensure the target database schema is GeoRaster enabled. Follow the steps explained in Enabling GeoRaster at the Schema Level.
Check if there is a conflict between the GeoRaster objects in the Data Pump dump file and the GeoRaster objects in the target database.
1. If the target database has no GeoRaster objects, then there is no conflict.
2. If the GeoRaster table names and RDT names in the dump file are known, use SDO_GEOR_ADMIN.isRDTNameUnique function in the target database to find out if there is RDT name conflict. For example:
```
SELECT SDO_GEOR_ADMIN.isRDTNameUnique(<rdt_name>) FROM DUAL;
```
  In the preceding code, <rdt_name> is the name of the RDT in the dump file. If the query returns ‘FALSE’, then there is RDT name conflict on <rdt_name>.
3. If the GeoRaster table names and RDT names in the dump file are not known, use impdp with the SQLFILE option to retrieve all the import DDL statements into a file. Get the GeoRaster table names and RDT names from the DDL statements in that file. For example:
```
impdp scott DIRECTORY=dpump_dir DUMPFILE=data.dmp SQLFILE=dpump_dir:ddl.sql REMAP_SCHEMA=herman:scott
```
  In the preceding code, ddl.sql contains the DDL statements to be executed by impdp. Then for each RDT name in ddl.sql, use SDO_GEOR_ADMIN.isRDTNameUnique function in the target database to find out if there is RDT name conflict.
Skip this step and go to step 4 if you detect a RDT name conflict in step 2 or if you intend to change the names of the imported RDT tables. Otherwise, import the GeoRaster tables and RDT tables as described in the following and validate the imported data. After this, you may skip step 4 as the GeoRaster data is already imported as required by the end of this step.
Import GeoRaster data as you would for other types of data, but exclude the GeoRaster internal DML triggers (whose names start with GRDMLTR_) and DDL triggers (SDO_GEOR_ADDL_TRIGGER and SDO_GEOR_BDDL_TRIGGER) if you did not exclude them in the export operation. For example:
```
impdp scott DIRECTORY=dpump_dir DUMPFILE=data.dmp PARFILE=exclude.par
 REMAP_SCHEMA=herman:scott TABLES=herman.georaster_table,herman.rdt_1,herman.rdt_2
```
In the preceding code, the exclude.par file contains the following:
```
exclude=trigger:"like 'GRDMLTR_%'"
exclude=trigger:"= 'SDO_GEOR_ADDL_TRIGGER'"
exclude=trigger:"= 'SDO_GEOR_BDDL_TRIGGER'"
```
If you do not exclude the GeoRaster internal DML triggers and DDL triggers, some impdp errors such as the following will be raised. However, you can safely ignore the errors.
```
ORA-39083: Object type TRIGGER failed to create with error:
ORA-13391:  GeoRaster reserved names cannot be used to create regular triggers
```
Resolve the conflicts and import GeoRaster data.
1. Import RDTs using impdp with REMAP_TABLE option to change the RDT names to new RDT names during the import (make sure the RDT names are unique across the target database). To make the data transfer easier and as a recommendation, the new RDT names can be constructed by appending a string and a number to the end of all old RDT names.
  For example:
```
impdp scott DIRECTORY=dpump_dir DUMPFILE=data.dmp
TABLES=herman.rdt_1,herman.rdt_2 REMAP_SCHEMA=herman:scott 
REMAP_TABLE=herman.rdt_1:rdt_1_imp_1, herman.rdt_2:rdt_2_imp_1
```
  In the preceding code, rdt_1 is remapped to rdt_1_imp_1, and rdt_2 is remapped to rdt_2_imp_1.
2. Import GeoRaster table metadata using impdp with CONTENT=METADATA_ONLY option and exclude the GeoRaster DML and DDL triggers as described in step 3.
  For example:
```
impdp scott DIRECTORY=dpump_dir DUMPFILE=data.dmp TABLES=herman.georaster_table 
REMAP_SCHEMA=herman:scott CONTENT=metadata_only PARFILE=exclude.par
```
  If the GeoRaster table already exists in the target schema, it is recommended to use REMAP_TABLE option in the preceding code to remap the imported GeoRaster table name to a new name.
3. Login to the target database where the GeoRaster table metadata are imported and create a temporary DML trigger for each imported GeoRaster table, which will automatically replace the rasterDataTable attribute of the imported GeoRaster objects with the new RDT names during the data import in step d. The new RDT names must be the same as the new RDT names in step a.
  This is a sample DML trigger definition:
```
DEFINE tname=georaster_table  -- the georaster table name
DEFINE cname=grobj            -- the georaster column name
DEFINE rdt_suffix=’IMP’       -- the string to append  to the RDT names 
DEFINE seq_num=1              -- the number to append  to the RDT names

CREATE OR REPLACE TRIGGER tmp_dml_trigger 
  BEFORE INSERT ON &tname 
  FOR EACH ROW 
  BEGIN
  -- the new RDT table name is constructed as the old RDT table name appended with
  -- the string defined in rdt_suffix and the sequence number defined in seq_num. 
  :new.&cname.rasterDataTable := :new.&cname.rasterDataTable||’_&rdt_suffix’||’_’||’&seq_num’;
  END;
/
```
4. Import GeoRaster table data using impdp with CONTENT=DATA_ONLY option. For example:
```
impdp scott DIRECTORY=dpump_dir DUMPFILE=data.dmp  
TABLES=herman.georaster_table REMAP_SCHEMA=herman:scott CONTENT=data_only
```
  If you used the REMAP_TABLE option in step b, then include the option in the preceding code too.
5. Drop the temporary DML trigger created in step c. Validate and verify the imported data.
The preceding examples transfer a GeoRaster table, georaster_table, and two RDTs, rdt_1 and rdt_2, from schema HERMAN in the source database to schema SCOTT in the target database. It assumes all GeoRaster objects in the georaster_table store their raster cell data in either rdt_1 or rdt_2 and these two RDTs are not used by any other GeoRaster tables.

Parent topic: Transferring GeoRaster Data Between Databases

3.16.2 Using Transportable Tablespaces To Transfer GeoRaster Data

Oracle Database transportable tablespaces feature can be used to transfer GeoRaster data between databases. See Transporting Tablespaces Between Databases in Oracle Database Administrator’s Guide for more information about using the transporting tablespaces feature with spatial data.

If a tablespace to be transported contains any spatial indexes on the GeoRaster tables or raster data tables (RDTs), you may have to take some preparatory steps. See the Usage Notes for the SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS procedure in Oracle Spatial Developer's Guide for more information about using the transportable tablespace feature with spatial data.

The steps explained in the following sections enable you to use transportable tablespaces to transfer GeoRaster data between databases:

Parent topic: Transferring GeoRaster Data Between Databases

3.16.2.1 Export the Tablespaces from the Source Database

To export the tablespaces from the source database for GeoRaster data migration, perform the following steps:

Ensure the tablespaces to be transferred is self-contained. For example, run the following as DBA in SQL*Plus:
```
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘tbs_1, tbs_2’, TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
```
In the preceding code, tbs_1 and tbs_2 are the names of the tablespaces to be transported.
Make the tablespaces to be transported READ ONLY. For example, run the following as DBA in SQL*Plus:
```
ALTER TABLESPACE tbs_1 READ ONLY;
ALTER TABLESPACE tbs_2 READ ONLY;
```

Run Data Pump export utility as a user with DATAPUMP_EXP_FULL_DATABASE role. For example:

$ expdp <user_name> DUMPFILE=tbs_meta.dmp DIRECTORY=data_pump_dir TRANSPORT_TABLESPACES=tbs_1,tbs_2 LOGFILE=tts_exp.log

Transport the exported dump file from step 3 and tablespace data files to a directory that is accessible to the target database.

Restore the tablespaces to READ WRITE mode.

ALTER TABLESPACE tbs_1 READ WRITE;
ALTER TABLESPACE tbs_2 READ WRITE;

Parent topic: Using Transportable Tablespaces To Transfer GeoRaster Data

3.16.2.2 Import the Tablespaces into the Target Database

To import the tablespaces into the target database for GeoRaster data migration, perform the following steps:

Ensure the target database schema is GeoRaster enabled. Follow the steps explained in Enabling GeoRaster at the Schema Level.

Run the Data Pump import utility. For example:

$ impdp <user_name> DIRECTORY=data_pump_dir DUMPFILE=tbs_meta.dmp 
LOGFILE=tts_imp.log TRANSPORT_DATAFILES='/app/oracle/oradata/tbs_1.dbf', ’/app/oracle/oradata/tbs_2.dbf’ 
REMAP_SCHEMA=src_gruser1:target_gruser1 REMAP_SCHEMA=src_gruser2:target_gruser2 PARFILE=exclude.par

In the preceding code, <user_name> is a user with DATAPUMP_EXP_FULL_DATABASE role.

The exclude.par file contains the following:

exclude=trigger:"like 'GRDMLTR_%'"
exclude=trigger:"= 'SDO_GEOR_ADDL_TRIGGER'"
exclude=trigger:"= 'SDO_GEOR_BDDL_TRIGGER'"

If the GeoRaster table name already exists in the target database schema, use REMAP_TABLE option of the impdp command to remap the GeoRaster table name to a new name.

Place the transported tablespaces into READ WRITE mode.

ALTER TABLESPACE tbs_1 READ WRITE;
ALTER TABLESPACE tbs_2 READ WRITE;

Check if there are conflicts of RDT names in the target database.
1. Determine the GeoRaster tables and columns in the transported tablespaces by running the following query as DBA:
```
SELECT t.owner, t.table_name, c.column_name 
FROM dba_all_tables t, dba_tab_columns c 
WHERE t.tablespace_name IN ('TBS_1', ‘TBS_2’) 
     AND t.owner = c.owner 
     AND t.table_name = c.table_name 
     AND c.data_type = 'SDO_GEORASTER' 
     AND c.data_type_owner IN ('MDSYS', 'PUBLIC');
```
  In the preceding code, ‘TBS_1’ and ‘TBS_2’ are the names of the transported tablespaces. This query returns a list of GeoRaster table and column names that are in the transported tablespaces.
2. Determine if there are RDT name conflicts.
```
SELECT a.rdt_name 
    	    FROM ( SELECT UNIQUE t.<column_name>.rasterdatatable rdt_name 
            		FROM <owner>.<table_name> t) a
                WHERE SDO_GEOR_ADMIN.isRDTNameUnique(a.rdt_name)=’FALSE’;
```
  In the preceding code, <owner>, <table_name> and <column_name> are the names returned in step a.
  This query will return the conflicted RDT names in the transported tablespaces that need to be renamed.
Skip this step and go to step 6 if there are no RDT name conflicts in step 4. Otherwise, resolve the conflicts in the target database by renaming the transported RDT.
1. Determine the DML trigger name (starts with ‘GRDMLTR_’) on the transported GeoRaster table by querying DBA_TRIGGERS view.
```
SELECT owner, trigger_name FROM dba_triggers WHERE table_owner = ‘<owner>’
AND table_name = ‘<table_name>’ AND trigger_name LIKE 'GRDMLTR_%';
```
  In the preceding code, <table_name> and <owner> are the name and the owner of the GeoRaster table that has conflicted RDT name found in step 4.
2. Connect as DBA and disable the DML trigger returned in step a:
```
ALTER TRIGGER <owner>.<TRIGGER_NAME> DISABLE;
```
3. Rename the conflicting RDT to a new name and update the rasterDataTable attribute of the GeoRaster objects in the GeoRaster table. Connect as the owner to the RDT in SQL*Plus:
```
RENAME <old_rdt> to <new_rdt>;
UPDATE <table_name> t  SET t.<column_name>.rasterDataTable = ‘<new_rdt>’ 
WHERE t.<column_name>.rasterDataTable=’<old_rdt>’;
```
  In the preceding code:
  - <old_rdt>: Old RDT name that conflicts
  - <new_rdt>: New RDT name that is unique in the target database
  - <table_name>: GeoRaster table name
  - <column_name>: GeoRaster column name associated with the conflicted RDT name returned in step 4
  Repeat this step for all conflicting RDTs.
4. Connect as DBA and enable the DML trigger that was disabled at step b.
```
ALTER TRIGGER <owner>.<TRIGGER_NAME> ENABLE;
```
If there are no conflicts or the conflicts have been resolved in step 5, call SDO_GEOR_ADMIN.registerGeoRasterObjects to register the transported GeoRaster object. For example run the following as DBA in SQL*Plus:
```
EXECUTE SDO_GEOR_ADMIN.registerGeoRasterObjects;
```

Parent topic: Using Transportable Tablespaces To Transfer GeoRaster Data

3.16.3 Using Database Link with GeoRaster Data

From Oracle Database Release 12.2 onwards, database link can be used to transfer GeoRaster data from one database to another.

You can execute a SQL query through a database link to access remote GeoRaster object’s attributes and binary data in the raster data tables (RDTs). The GeoRaster data manipulations provided in the GeoRaster PL/SQL packages cannot be used on the remote GeoRaster objects through a database link.

Note:

You can check the interoperability support between different releases of the database in Oracle Interoperability Support.

To transfer GeoRaster data through a database link:

Ensure the target database schema is GeoRaster enabled. Follow the steps explained in Enabling GeoRaster at the Schema Level.
Create the database link in the target database by executing the following SQL statement:
```
CREATE PUBLIC DATABASE LINK <dblink name> 
      CONNECT TO <username> IDENTIFIED BY <password>
      USING '<tnsname>';
```
In the preceding code:
- <dblink name>: Name of the database link
- <username>: Username to connect to the source database schema where the GeoRaster table is located
- <password>: Password for the source database user
- <tnsname>: Source database connection name defined in the tnsname.ora in the target database
Identify the GeoRaster table and RDT(s) to be transferred from the source database. Run the following query from the target database to get the RDTs associated with the GeoRaster objects in the GeoRaster table.
```
SELECT UNIQUE t.<column_name>.rasterDataTable FROM <source_georaster_table>@<dblink_name> t;
```
In the preceding code, <column_name> is the GeoRaster column name of <source_georaster_table> in the source database.
Transfer the RDT data from the source database to the target database:
```
CREATE TABLE <target_rdt_table> AS (SELECT * FROM <source_rdt_table>@<dblink_name>);
```
The <source_rdt_table> in the preceding code is the RDT identified in step 3 (this example assumes that <source_rdt_table> only contains the raster data that is to be transferred). If <source_rdt_table> is unique in the target database (SDO_GEOR_ADMIN.isRDTNameUnique(‘<source_rdt_table>’) returns true), then <target_rdt_table> should be the same as <source_rdt_table>. Otherwise, choose a unique name for <target_rdt_table>.
Transfer the GeoRaster objects in the GeoRaster table from the source database to the target database. A new GeoRaster table can be created in the target database as follows:
```
CREATE TABLE <target_georaster_table> AS (SELECT * FROM <source_georaster_table>@<dblink_name>);
```
If the name of the new RDT created in step 4 in the target database, <target_rdt_table>, is different from the RDT name in the source database, <source_rdt_table>, then the rasterDataTable attribute of the GeoRaster objects in the <target_georaster_table> needs to be updated as follows:
1. Connect as the schema user to find out the GeoRaster DML trigger name on the GeoRaster table:
```
SELECT trigger_name FROM user_triggers WHERE table_name = ‘<target_georaster_table>’ AND trigger_name LIKE 'GRDMLTR_%';
```
  In the preceding code, <target_georaster_table> is the GeoRaster table name in the target database
2. Connect as DBA and disable the GeoRaster DML trigger:
```
ALTER TRIGGER <owner>.<trigger_name> DISABLE;
```
3. Connect as the schema user and update the rasterDataTable attribute of the GeoRaster object:
```
UPDATE <target_georaster_table> t  SET t.<column_name>.rasterDataTable = ‘<target_rdt_table>’ 
WHERE t.<column_name>.rasterDataTable=’<source_rdt_table>’;
```
  In the preceding code, <source_rdt_table> and <target_rdt_table> are the table names used in step 4. <column_name> is the GeoRaster column name in the <target_georaster_table>.
4. Connect as DBA and enable the GeoRaster DML trigger:
```
ALTER TRIGGER <owner>.<trigger_name> ENABLE;
```
Connect as the schema user and register the transferred GeoRaster objects in the target database:
```
EXECUTE SDO_GEOR_ADMIN.registerGeorasterObjects;
```
Database link can also be used in the Data Pump import utility to transfer GeoRaster data directly from the source database to the target database. See Using Data Pump Utility to Transfer GeoRaster Data about how to use Data Pump import utility to transfer GeoRaster data.

Parent topic: Transferring GeoRaster Data Between Databases