1. Scaleout User's Guide
  2. Migrating, Backing Up and Restoring Data
  3. Migrating a Database from TimesTen Classic to TimesTen Scaleout

Migrating a Database from TimesTen Classic to TimesTen Scaleout

TimesTen Scaleout enables you to migrate a database from TimesTen Classic to TimesTen Scaleout. TimesTen Scaleout supports and includes most of the features of TimesTen Classic; it does not support any of the features of TimesTen Replication and only supports static read-only cache groups with incremental autorefresh. See Comparison Between TimesTen Scaleout and TimesTen Classic.

These procedures are for TimesTen Classic databases. You cannot migrate the following objects:

  • Tables containing a LOB column

  • Tables that contain ROWID columns

  • Tables with in-memory columnar compression

  • Tables with aging policies

  • Cache groups other than static read-only cache groups with incremental autorefresh

  • Replication schemes

Prerequisites before migrating a database from TimesTen Classic to TimesTen Scaleout:

  • Create a grid with management and data instances. See Setting Up a Grid.

  • Create a backup of your TimesTen Classic database. See Backing up and Restoring a Database in Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide.

  • After you have created a backup of your TimesTen Classic database, consider removing LOB columns from your tables. TimesTen Scaleout cannot import a table with LOB columns and the import process displays an error message if a table contains LOB columns. Use the ALTER TABLE statement with the DROP keyword to drop these columns. See ALTER TABLE in Oracle TimesTen In-Memory Database SQL Reference.

  • In case that you have tables with ROWID columns consider not using ROWID based access in your applications. The semantics of ROWID columns are different in TimesTen Classic than in TimesTen Scaleout. See Understanding ROWID in Data Distribution.

  • Understand the performance trade-off between table distribution schemes. See Defining Table Distribution Schemes.

The procedures in this topic explain how to remove the objects that cannot be migrated from your TimesTen Classic database.

To migrate a database from TimesTen Classic to a TimesTen Scaleout database, export your database schema, and migrate supported objects out of the TimesTen Classic database. Then restore these into a new TimesTen Scaleout database.

  1. Disconnect all applications from your TimesTen Classic database.
  2. On the TimesTen Classic instance, export the database schema with the -list option of the ttSchema utility. The -list option only specifies objects that are supported in TimesTen Scaleout. Ensure that you replace database1 with the name of your database:
    % ttSchema -list tables,views,sequences,synonyms database1 > /tmp/database1.schema

    For more information about the ttSchema utility, see ttSchema in Oracle TimesTen In-Memory Database Reference.

  3. On the TimesTen Classic instance, save a copy of your database with the ttMigrate utility.
    % ttMigrate -c database1 /tmp/database1.data

Saving user PUBLIC
User successfully saved.
...
Sequence successfully saved.

    For more information about the ttMigrate utility, see ttMigrate in Oracle TimesTen In-Memory Database Reference.

  4. Copy the database schema and the migrate object files to a file system that is accessible by one of your data instances. You can choose any data instance and you need to complete all further procedures from this same data instance unless stated otherwise.
  5. On your selected data instance, use a text editor to edit the database schema file to remove SQL statements and clauses that are not supported in TimesTen Scaleout and add distribution scheme clauses for your tables. This is the database schema file that you created in step 3.

    Remove the following SQL statements:

    • CREATE CACHE GROUP

      Note:

      Except for statements that create static read-only cache groups with incremental autorefresh.

    • CREATE REPLICATION

    • CREATE ACTIVE STANDBY PAIR

    • CREATE INDEX (Before removing these statements review the note below)

      Note:

      CREATE INDEX statements are supported in TimesTen Scaleout, but it is more efficient to create indexes once your data has been distributed. However, for child tables which you want to distribute with the DISTRIBUTE BY REFERENCE distribution scheme, you should not remove the FOREIGN KEY clause of the child table, nor the CREATE INDEX statement of the referenced parent table. Step 9 restores your indexes once your data has been inserted into your TimesTen Scaleout database.

    Remove the following CREATE TABLE clauses:

    • COMPRESS BY

    • FOREIGN KEY (Before removing these statements review the note above)

    • AGING

    Add CREATE USER statements to create the schema owners referenced by the objects in database1.schema. For example, hr.employees, would require a CREATE USER hr IDENTIFIED BY password statement. You also may need to add privileges to these users if you want to log in as the users.

    Add distribution scheme clauses for all of your table definitions. If you do not specify a distribution scheme for a CREATE TABLE statement, TimesTen Scaleout distributes the data of that table with the DISTRIBUTE BY HASH distribution scheme.

    Note:

    When you use the DISTRIBUTE BY REFERENCE distribution scheme, ensure that you declare the child key columns of a foreign key constraint as NOT NULL.

    Before adding distribution schemes to your table definitions, ensure that you understand the performance trade-off between the distribution schemes. See Defining Table Distribution Schemes.

  6. From a TimesTen Scaleout management instance, create a TimesTen Scaleout database. See Creating a Database.
  7. On your selected data instance, log in as the instance administrator to create the database schema from the database schema file. Ensure that you replace new_database1 with the name of your new TimesTen Scaleout database:
    % ttIsql -connStr "DSN=new_database1" -f /tmp/database1.schema

Copyright (c) 1996, 2023, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=new_database1";
Connection successful:
...
exit;
Disconnecting...
Done.

    Note:

    It can be useful to redirect the output of the ttIsql command to an output file. You can then review this output to ensure that the command ran successfully. To redirect output to a file, add > myoutput.txt after the ttIsql -connStr "DSN=new_database1" -f /tmp/database1.schema command.

  8. On your selected data instance, use the following ttMigrate command to restore rows for all user tables:
    % ttMigrate -r -gridRestoreRows new_database1 /tmp/database1.data

Restoring table HR.EMPLOYEES
...
 10/10 rows restored.
Table successfully restored.
  9. On your selected data instance, use the following ttMigrate command to restore indexes and foreign keys:
    % ttMigrate -r -gridRestoreFinale new_database1 /tmp/database1.data

Restoring table HR.EMPLOYEES
...
 10/10 rows restored.
Table successfully restored.

    Note:

    If you did not remove FOREIGN KEY clauses in step 5 because you are using a DISTRIBUTE BY REFERENCE distribution scheme, you may see error messages that TimesTen Scaleout is unable to create some foreign keys. If you already created these foreign keys in step 5, you can ignore these messages.

Once the database is operational on TimesTen Scaleout, create a backup of the TimesTen Scaleout database to have a valid restoration point for your database. See Backing Up and Restoring a Database. Once you have created a backup of your database, you may remove the database schema file (in this example, /tmp/database1.schema) and the ttMigrate copy of your database (in this example, /tmp/database1.data).