Migrating from an Oracle® OLTP system to Cloud Spanner

This article explains how to migrate your database from Oracle® Online Transaction Processing (OLTP) systems to Cloud Spanner.

Cloud Spanner uses certain concepts differently from other enterprise database management tools, so you might need to adjust your application to take full advantage of its capabilities. You might also need to supplement Cloud Spanner with other services from Google Cloud Platform (GCP) to meet your needs.

Migration constraints

When you migrate your application to Cloud Spanner, you must take into account the different features available. You probably need to redesign your application architecture to fit with Cloud Spanner's feature set and to integrate with additional GCP services.

Stored procedures and triggers

Cloud Spanner does not support running user code in the database level, so as part of the migration, you must move business logic implemented by database-level stored procedures and triggers into the application.


Cloud Spanner does not implement a sequence generator, and as explained below, using monotonically increasing numbers as primary keys is an anti-pattern in Cloud Spanner. An alternative way to generate a unique primary key is to use a random UUID.

If sequences are required for external reasons, then you must implement them in the application layer.

Access controls

Cloud Spanner supports only database-level access controls using Cloud IAM access permissions and roles. Predefined roles can give read-write or read-only access to the database.

If you require finer grained permissions, you must implement them at the application layer. In a normal scenario, only the application should be allowed to read and write to the database.

If you need to expose your database to users for reporting, and want to use fine-grained security permissions (such as table- and view-level permissions), you should export your database to BigQuery.

Data validation constraints

Cloud Spanner can support a limited set of data validation constraints in the database layer.

If you need more complex data constraints, implement them in the application layer.

The following table discusses the types of constraints commonly found in Oracle® databases, and how to implement them with Cloud Spanner.

Constraint Implementation with Cloud Spanner
Not null NOT NULLcolumn constraint
Unique Secondary index with UNIQUE constraint
Foreign key (for normal tables) Implemented in the application layer
Foreign key ON DELETE/ON UPDATE actions Only possible for interleaved tables, otherwise implemented in the application layer
Value checks and validation via CHECK constraints or triggers Implemented in the application layer

Supported data types

Oracle® databases and Cloud Spanner support different sets of data types. The following table lists the Oracle data types and their equivalent in Cloud Spanner. For detailed definitions of each Cloud Spanner data type, see Data Types.

You might also have to perform additional transformations on your data as described in the Notes column to make Oracle data fit in your Cloud Spanner database.

For example, you can store a large BLOB as an object in a Cloud Storage bucket rather than in the database, and then store the URI reference to the Cloud Storage object in the database as a STRING.

Oracle data type Cloud Spanner equivalent Notes
Character types (CHAR, VARCHAR, NCHAR, NVARCHAR) STRING Note: Cloud Spanner uses Unicode strings throughout.
Oracle supports a maximum length of 32,000 bytes or characters (depending on type), while Cloud Spanner supports up to 2,621,440 characters.
BLOB, LONG RAW, BFILE BYTES or STRING containing URI to the object. Small objects (less than 10 MiB) can be stored as BYTES.
Consider using alternative GCP offerings such as Cloud Storage to store larger objects.
CLOB, NCLOB, LONG STRING (either containing data or URI to external object) Small objects (less than 2,621,440 characters) can be stored as STRING. Consider using alternative GCP offerings such as Cloud Storage to store larger objects.
NUMBER, NUMERIC, DECIMAL STRING, FLOAT64, INT64 The NUMBER Oracle data type supports up to 38 digits of precision, while the FLOAT64 Cloud Spanner data type supports up to 16 digits of precision. See Storing arbitrary precision numeric data for alternative mechanisms.
DATE DATE The default STRING representation of the Cloud Spanner DATE type is yyyy-mm-dd, which is different from Oracle's, so use caution when automatically converting to and from STRING representations of dates. SQL functions are provided to convert dates to a formatted string.
DATETIME TIMESTAMP Cloud Spanner stores time independent of timezone. If you need to store a timezone, you need to use a separate STRING column. SQL functions are provided to convert timestamps to a formatted string using timezones.
XML STRING (either containing data or URI to external object) Small XML objects (less than 2,621,440 characters) can be stored as STRING. Consider using alternative GCP offerings such as Cloud Storage to store larger objects.
ROWID PRIMARY KEY Cloud Spanner uses the table's primary key to sort and reference rows internally, so in Cloud Spanner it is effectively the same as the ROWID data type.
SDO_GEOMETRY, SDO_TOPO_GEOMETRY_SDO_GEORASTER   Cloud Spanner does not support geospatial data types. You will have to store this data using standard data types, and implement any searching and filtering logic in the application layer.
ORDAudio, ORDDicom, ORDDoc, ORDImage, ORDVideo, ORDImageSignature Cloud Spanner does not support media data types. Consider using Cloud Storage to store media data.

Migration process

An overall timeline of your migration process would be:

  • Convert your schema and data model.
  • Translate any SQL queries.
  • Migrate your application to use Cloud Spanner in addition to Oracle.
  • Bulk export your data from Oracle and import your data into Cloud Spanner using Cloud Dataflow.
  • Maintain consistency between both databases during your migration.
  • Migrate your application away from Oracle.

Converting your database and schema

You convert your existing schema to a Cloud Spanner schema to store your data. This should match the existing Oracle schema as closely as possible to make application modifications simpler. However, due to the differences in features, some changes will be necessary.

Using best practices in schema design can help you increase throughput and reduce hot spots in your Cloud Spanner database.

Primary keys

Every table that needs to store more than one row must have a primary key consisting of one or more columns of the table. Your table's primary key uniquely identifies each row in a table, and because the table rows are sorted by primary key, the table itself will act as a primary index.

You should avoid designating columns that monotonically increase or decrease as the first part of the primary key (examples include sequences or timestamps), because this can lead to hot spots caused by inserts occurring at the end of your keyspace. A hot spot is a concentration of operations on a single node, which lowers the write throughput to the node's capacity instead of benefiting from load-balancing all writes among the Cloud Spanner nodes.

Use the following techniques to generate unique primary key values and reduce the risk of hot spots:

After you designate your primary key for your table, you cannot change it later without deleting and recreating the table. For more information on how to designate your primary key, see Schema and Data Model-Primary keys.

Here is an example DDL statement creating a table for a database of music tracks:

  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
  BirthDate  DATE,
) PRIMARY KEY(SingerId);

Interleaving your tables

Cloud Spanner has a feature where you can define two tables as having a one-to-many, parent-child relationship. This interleaves child data rows with their parent row in storage, effectively pre-joining the table and improving data retrieval efficiency when the parent and children are queried together.

The child table's primary key must start with the primary key column(s) of the parent table. From the child row's perspective, the parent row primary key is referred to as a foreign key. You can define up to 6 levels of parent-child relationships.

You can define on-delete actions for child tables to determine what happens when the parent row is deleted: either all child rows are deleted, or the parent row deletion is blocked while child rows exist.

Here is an example of creating an Albums table interleaved in the parent Singers table defined earlier:

  SingerId     INT64 NOT NULL,
  AlbumId      INT64 NOT NULL,
  AlbumTitle   STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId)

Creating secondary indexes

You can also create secondary indexes to index data within the table outside of the primary key.

Cloud Spanner implements secondary indexes in the same way as tables, so the column values to be used as index keys have the same constraints as the primary keys of tables. This also means that indexes have the same consistency guarantees as Cloud Spanner tables.

Value lookups using secondary indexes are effectively the same as a query with a table join. You can improve the performance of queries using indexes by storing copies the original table's column values in the secondary index using the STORING clause, making it a covering index.

Cloud Spanner's query optimizer will only automatically use secondary indexes when the index itself stores all the columns being queried (a covered query). To force the use of an index when querying columns in the original table, you must use a FORCE INDEX directive in the SQL statement, for example:

FROM MyTable@{FORCE_INDEX=MyTableIndex}
WHERE IndexedColumn=@value

Indexes can be used to enforce unique values within a table column, by defining a UNIQUE index on that column. Adding duplicate values will be prevented by the index.

Here is an example DDL statement creating a secondary index for the Albums table:

CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);

Note that if you create additional indexes after your data is loaded, populating the index may take some time. You should limit the rate at which you add them to an average of three per day. For more guidance on creating secondary indexes, see Secondary Indexes. For more information on the limitations on index creation, see Schema Updates.

Translate any SQL queries

Cloud Spanner uses the ANSI 2011 dialect of SQL with extensions, and has many functions and operators to help translate and aggregate your data. You must convert any SQL queries that use Oracle-specific syntax, functions, and types to be compatible with Cloud Spanner.

While Cloud Spanner does not support structured data as column definitions, structured data can be used in SQL queries using ARRAY and STRUCT types.

For example, a query could be written to return all Albums for an artist using an ARRAY of STRUCTs in a single query (taking advantage of the pre-joined data). For more information see the Notes about Subqueries section of the documentation.

SQL queries can be profiled using the Cloud Spanner Query interface in the GCP Console to execute the query. In general, queries that perform full table scans on large tables are very expensive, and should be used sparingly.

See the SQL Best Practices documentation for more information on optimising SQL queries.

Migrate your application to use Cloud Spanner

Cloud Spanner provides a set of Client Libraries for various languages, and the ability to read and write data using Cloud Spanner-specific API calls, as well as by using SQL queries and Data Modification Language (DML) statements. Using API calls may be faster for some queries, such as direct row reads by key, because the SQL statement does not have to be translated.

You can also use the Java Database Connectivity (JDBC) driver to connect to Cloud Spanner, leveraging existing tooling and infrastructure that does not have native integration.

As part of the migration process, features not available in Cloud Spanner must be implemented in the application. For example, a trigger to verify data values and update a related table would need to be implemented in the application using a read/write transaction to read the existing row, verify the constraint, then write the updated rows to both tables.

Cloud Spanner offers read-write and read-only transactions, which ensure external consistency of your data. Additionally, read transactions can have timestamp bounds applied, where you are reading a consistent version of the data specified in these ways:

  • At an exact time in the past (up to 1 hour ago).
  • In the future (where the read will block until that time arrives).
  • With an acceptable amount of bounded staleness, which will return a consistent view up to some time in the past without needing to check that later data is available on another replica. This can give performance benefits at the expense of possibly stale data.

Transferring your data from Oracle to Cloud Spanner

To transfer your data from Oracle to Cloud Spanner, you will need to export your Oracle database to a portable file format, for example CSV, then import that data into Cloud Spanner using Cloud Dataflow.


Bulk exporting from Oracle

Oracle does not provide any built-in utilities for exporting or unloading your entire database into a portable file format.

Some options for performing an export are listed in the Oracle FAQ.

These include:

Each of these has the disadvantage that only one table can be exported at a time, which means that you must pause your application or quiesce your database so that the database remains in a consistent state for export.

Other options include third-party tools as listed in the Oracle FAQ page, some of which can unload a consistent view of the entire database.

After they're unloaded, you should upload these datafiles to a Cloud Storage bucket so that they are accessible for import.

Bulk importing into Cloud Spanner

Because the database schemas probably differ between Oracle and Cloud Spanner, you might need to make some data conversions as part of the import process.

The easiest way to perform these data conversions and import the data into Cloud Spanner is by using Cloud Dataflow.

Cloud Dataflow is the GCP distributed Extract Transform and Load (ETL) service. It provides a platform for running data pipelines written using the Apache Beam SDK in order to read and process large amounts of data in parallel over multiple machines.

The Apache Beam SDK requires you to write a simple Java program to set read, transform and write the data. Beam connectors exist for Cloud Storage and Cloud Spanner, so the only code that needs to be written is the data transform itself.

See an example of a simple pipeline that reads from CSV files and writes to Cloud Spanner in the sample code repository that accompanies this article.

If parent-child interleaved tables are used in your Cloud Spanner schema, then care must be taken in the import process so that the parent row is created before the child row. The Cloud Spanner Import pipeline code handles this by importing all data for root level tables first, then all the level 1 child tables, then all the level 2 child tables, and so on.

The Cloud Spanner import pipeline can be used directly to bulk import your data, but this requires that your data exist in Avro files using the correct schema.

Maintaining consistency between both databases

Many applications have availability requirements that make it impossible to keep the application offline for the time required to export and import your data. While you are transferring your data to Cloud Spanner, your application continues modifying the existing database. You must duplicate updates to the Cloud Spanner database while the application is running.

There are various methods of keeping your two databases in sync, including Change Data Capture, and implementing simultaneous updates in the application.

Change Data Capture

Oracle GoldenGate can provide a Change Data Capture stream for your database. You can write an application that subscribes to this stream and applies the same modifications (after data conversion, of course) to your Cloud Spanner database.

Simultaneous updates to both databases from the application

An alternative method is to modify your application to perform writes to both databases. One database (initially Oracle) would be considered the source of truth, and after each database write, the entire row is read, converted, and written to the Cloud Spanner database.

In this way, the application constantly overwrites the Cloud Spanner rows with the latest data.

After you're confident that all your data has been transferred correctly, you can switch the source of truth to the Cloud Spanner database.

This mechanism provides a rollback path if issues are found when switching to Cloud Spanner.

Verifying data consistency

As data streams into your Cloud Spanner database, you can periodically run a comparison between your Cloud Spanner data and your Oracle data to make sure that the data is consistent.

You can validate consistency by querying both data sources and comparing the results.

You can use Cloud Dataflow to perform a detailed comparison over large data sets by using the Join transform. This transform takes 2 keyed data sets, and matches the values by key. The matched values can then be compared for equality.

You can regularly run this verification until the level of consistency matches your business requirements.

Switching to Cloud Spanner as your application's source of truth

When you have confidence in the data migration, you can switch your application to using Cloud Spanner as the source of truth. Continue writing back changes to the Oracle database to keep the Oracle database up to date, giving you a rollback path should issues arise.

Finally, you can disable and remove the Oracle database update code and shut down the Oracle database.

Exporting and importing Cloud Spanner databases

You can optionally export your tables from Cloud Spanner to a Cloud Storage bucket using a Cloud Dataflow template to perform the export. The resulting folder contains a set of Avro files and JSON manifest files containing your exported tables. These files can serve various purposes, including:

  • Backing up your database for data retention policy compliance or disaster recovery.
  • Importing the Avro file into other GCP offerings such as BigQuery.

For more information on the export and import process, see Exporting Databases and Importing Databases.

What's next

Was this page helpful? Let us know how we did:

Send feedback about...