ttMigrate

Description

Performs one of these operations:

• Saves a migrate object from a TimesTen data store into a binary datafile.

• Restores the migrate object from the binary datafile into a TimesTen data store.

• Examines the contents of a binary datafile created by this utility.

Migrated objects include:

• Tables

• Cache group definitions

• Views and materialized views

• Materialized view log definitions

• Sequences

• Replication schemes

The ttMigrate utility is used when upgrading major release versions of TimesTen, since major revisions are not compatible. For an example, see the Oracle TimesTen In-Memory Database Installation Guide.

When you migrate a database into Release 11.2.1 from a previous release, users and user privileges are not migrated. When you migrate a database between releases of Release 11.2.1 or into a release later than Release 11.2.1, users and user privileges are migrated.

Binary files produced by this utility are platform-dependent. For example a binary file produced on Windows must be restored on Windows. Use the ttBulkCp utility to copy data between platforms.

The ttMigrate utility can be used to copy data between bit-levels within the same architecture. For example, it can be used to move data from a 32-bit Solaris system to a 64-bit Solaris system. The -noRepUpgrade option must be used when changing bit-levels and the data store should not be involved in a replication scheme, in this case.

On UNIX, this utility is supported for TimesTen Data Manager DSNs. For TimesTen Client DSNs, use the utility ttMigrateCS.

Required privilege

This utility requires various privileges depending on the options specified. In general, a user must be the instance administrator or have the ADMIN privilege to use this utility.

Using the -r option requires the instance administrator privilege, as it generally creates a database. If the database has already been created when this option is used, it requires CREATE ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY VIEW, CREATE ANY MATERIALIZED VIEW, CREATE ANY CACHE GROUP, CREATE ANY INDEX privileges, as well as ADMIN if autocreation of users is necessary. If the database is involved in replication or IMDB Cache, then CACHE_MANAGER is also required.

Using the -c option to capture an entire database requires the ADMIN privilege.

Using the -c option to capture a subset of the database objects (tables, views, materialized views, cache groups, sequences) requires SELECT ANY TABLE and SELECT ANY SEQUENCE privileges.

Syntax

ttMigrate {-h | -help | -?}

ttMigrate {-V | -version}

To create or append a binary datafile, use:

ttMigrate {-a | -c} [-v verbosity] [-nf] [-nr] [-fixNaN] [-saveAsCharset charset] [-repUpgrade | -noRepUpgrade [-convertTypesToOra | -convertTypestoTT]] 
{-connStr connection_string | DSN} dataFile [objectOwner.]objectName

To restore a data store from a binary datafile created by this utility, use:

ttMigrate -r -noRepUpgrade  [-numThreads n] [-inline rule] [-v verbosity] [-fixNaN] [-nf] [-nr] [-C chkPtFreq] [-rename oldOwner:newOwner] [-localhost hostName] [-n noCharsetConversion] [-cacheUid uid [-cachePwd pwd]] [-convertCGtypes]  [-autorefreshPaused] [-convertTypesToOra | -convertTypesToTT]  [-noAutoCreateUsers] -connStr connection_string | DSN} dataFile [[objectOwner.]objectName]

or

ttMigrate -r -repUpgrade  [-numThreads n] [-vverbosity] [-fixNaN] [-nf] [-nr] [-C chkPtFreq] [-rename oldOwner:newOwner] [-localhost hostName] [-noCharsetConversion] [-cacheUid uid [-cachePwd pwd]] [-convertCGtypes] [-updateStats | -estimateStats percentRows] {-connStr connection_string | DSN} dataFile [[objectOwner.]objectName]

To list or display the contents of a binary datafile created by this utility, use:

ttMigrate {-l | -L | -d | -D} dataFile [[objectowner.]name ...]

Options

ttMigrate has the options:.

Modes

Create mode (-c) and Append mode (-a)

In Create mode, ttMigrate saves migrate objects from a TimesTen data store into a new binary datafile. If the datafile does not exist, ttMigrate creates it. Otherwise, ttMigrate overwrites the existing file, destroying its contents.

The datafile format used by ttMigrate is independent of any release of TimesTen, so it is possible to use ttMigrate to migrate data from one TimesTen release to another.

In Append mode, ttMigrate appends migrate objects from a TimesTen data store to an existing datafile. If the datafile does not exist, ttMigrate creates it.

For each ordinary (non-cached) table, ttMigrate saves:

• The table description: the name and type of each of the table's columns, including primary key and nullability information.

• The table's index definitions: the name of each index and the columns contained in the index. The actual contents of the index are not saved; ttMigrate only saves the information needed to rebuild the index when the table is restored.

• The table's foreign key definitions. You can disable the saving of foreign key definitions using the -nf option.

• The rows of the table. You can disable the saving of rows using the -nr option.

For each cache group, ttMigrate saves the following:

• The cache group definition: the cache group owner and name, the names of all tables in the cache group and any relevant cache group settings, such as the cache group duration.

  Note:

  After ttMigrate has been used to restore a data store, all autorefresh cache groups in the restored data store have AUTOREFRESH STATE set to OFF, no matter how it was set on the source data store. Reset AUTOREFRESH STATE to ON by using the ALTER CACHE GROUP statement.

• All the cached tables in the cache group: the table name, column information, table attributes (propagate or read-only), WHERE clause, if any, foreign key definitions and index definitions.

For each view, ttMigrate saves the following:

• All of the same information as a normal table.

• The query defining the view.

For each sequence, ttMigrate saves the following:

• The complete definition of the sequence.

• The sequence's current value.

For each user (except the instance administrator), ttMigrate saves the following:

• User name

• The user's encrypted password

• Privileges that have been granted to the user

For PUBLIC, ttMigrate saves all privileges that have been granted to PUBLIC after database creation.

If there are any replication schemes defined, ttMigrate saves all the of the TTREP tables containing the replication schemes. Replication schemes should have names that are unique from all other data store objects. It is not possible to migrate a replication scheme with the same name as any other database object.

By default, ttMigrate saves all data store objects and users in the data store to the datafile, including tables, views, cache groups, sequences, users and replication schemes. Alternatively, you can give a list of data store objects to be saved on the command line, with the exception of replication schemes. The names in this list can contain the wildcard characters % (which matches one or more characters) and _ (which matches a single character). ttMigrate saves all data store objects that match any of the given patterns. Names do not need to be fully qualified: If a name is given with no owner, ttMigrate saves all data store objects that match the specified name or pattern, regardless of their owners.

You cannot save cached tables independently of their cache groups. If you list a cached table on the command line without also listing the corresponding cache group ttMigrate issues an error.

Use the -v option to control the information that ttMigrate prints while the save is in progress.

Restore mode (-r)

In Restore mode, ttMigrate restores all data store objects from a datafile into a TimesTen data store.

For each ordinary (non-cached) table, ttMigrate restores:

• The table, using the original owner, table name, column names, types and nullability and the original primary key. You can use the -rename option to restore tables with a new owner name.

• The table's foreign keys. You can use the -nf flag to disable the restoration of foreign keys.

• All indexes on the table.

• All rows of the table. You can use the -nr flag to disable the restoration of rows.

For each cache group, ttMigrate restores:

• The cache group definition, using the original cache group owner and name. You can use the -rename option to restore cache groups with a new owner name.

• Each cached table in the cache group, using the original table names, column names, types and nullability, the original primary key, the table attributes (PROPAGATE or READONLY), and the WHERE clause, if any. You can use the-rename option to restore tables with a new owner name.

• The foreign key definitions of the cached tables.

• All of the indexes on the cached tables.

  Note:

  The ttMigrate utility does not restore the rows of cached tables, even if you have not specified the -nr option. The foreign key definitions of the cached tables are always restored, regardless of the use of the -nf option, as they are needed to maintain the integrity of the cache group.

By default, the -repUpgrade option is set during restore.

By default, ttMigrate restores all tables and cache groups in the datafile. Alternatively, you can list specific tables and cache groups to be restored on the command line. The names in this list must be fully qualified and cannot use wildcard characters.

You cannot restore cached tables independently of their cache groups. If you list a cached table on the command line without also listing the corresponding cache group, then ttMigrate issues an error.

Use the -v option to control the information that ttMigrate prints while the restoration is in progress.

The -inline option may be used to control whether variable length columns are restored as INLINE or NOT INLINE. See "Type specifications" in Oracle TimesTen In-Memory Database SQL Reference. In the default mode, -inlinepreserve, ttMigrate restores all variable-length columns with the same INLINE or NOT INLINE setting with which they were saved. In the other two modes, -inlinedsDefault and -inlinemaxlen, ttMigrate restores variable-length columns equal to or shorter than a threshold length as INLINE, and restores all other variable length columns as NOT INLINE. For-inlinedsDefault, this threshold is the default automatic INLINE length for a TimesTen data store. The -inlinemaxlen mode restores variable length columns with a user-specified threshold length of maxlen as INLINE, and all other variable length columns as NOT INLINE, even if they were saved as INLINE. If maxlen is 0, then all variable-length columns are restored as NOT INLINE.

List mode (-l) and Long-list mode (-L)

In List mode, ttMigrate lists the names of data store objects in the specified datafile, including cached tables and the replication scheme TTREP tables.

In Long-list mode, ttMigrate lists the names of data store objects in the datafile, including cached tables and the replication scheme TTREP tables, along with the number of rows in each table and the index definitions for each table, the query defining each view and the specifications for each sequence.

By default, ttMigrate lists the replication scheme name as well as all of the data store objects in the file. Alternatively you can provide a list of names of data store objects on the command line. The names in this list must be fully qualified and cannot use wildcard characters.

Describe mode (-d)

In Describe mode, ttMigrate gives a short description for data store objects in the specified file.

For each table, ttMigrate lists the table name, the number of rows in the table, and the table's column definitions, primary key and foreign keys. For cached tables, ttMigrate also lists the table attributes (PROPAGATE or READONLY) and the table's WHERE clause, if any.

For views, ttMigrate also lists the query defining the view.

For cache groups, ttMigrate lists the cache group name, the number of tables in the cache group, the cache group duration and describes each cached table in the cache group.

For replication schemes, ttMigrate lists the replication scheme name as well as all of the TTREP replication scheme tables in the same manner as user tables.

By default, ttMigrate describes all of the data store objects in the file. Alternatively, you can provide a list of names of data store objects on the command line. The names in this list must be fully qualified and cannot use wildcard characters.

Long-describe mode (-D)

In Long-describe mode, ttMigrate gives a full description for data store objects in the specified file.

For each table, ttMigrate lists the table's name and the number of rows in the table, the table's column definitions, primary key, foreign keys and index definitions. For cached tables, ttMigrate also lists the table attributes (PROPAGATE or READONLY) and the table's WHERE clause, if any.

For cache groups, ttMigrate lists the cache group name, the number of tables in the cache group, the cache group duration and describes each cached table in the cache group.

For sequences, ttMigrate lists all of the values used to define the sequence, as well as its current value.

For replication schemes, ttMigrate lists all of the TTREP replication scheme tables in the same manner as user tables.

By default, ttMigrate describes all of data store objects in the file. Alternatively, you can provide a list of names of data store objects on the command line. The names in this list must be fully qualified and cannot use wildcard characters.

TimesTen to Oracle data type conversions

Both TimesTen and Oracle data types are supported in TimesTen 11.2.1 When migrating a data store from an earlier version of TimesTen to TimesTen release 11.2.1, you can convert the data types in your data store to the default Oracle type mode. This is not required, however.

In replication, the type mode must be the same on both sides of the replication scheme. Therefore you cannot convert the data types as part of an online upgrade, as TimesTen releases previous to 11.2.1 do not support Oracle data types.

To convert from TimesTen data types to Oracle data types, use the -convertTypesToOra option.

The -convertTypesToOra option instructs ttMigrate to make the following type conversions as it saves or restores tables:

For information on data types, see "Data Types" in the Oracle TimesTen In-Memory Database SQL Reference.

Oracle to TimesTen data type conversions

When migrating tables backward from TimesTen release 11.2.1 to an earlier version of TimesTen, you may need to convert Oracle data types to TimesTen data types, as the Oracle data types were not supported in releases prior to 11.2.1.

To convert from Oracle data types to TimesTen data types, use the -convertTypesToTT option.

The -convertTypesToTT option instructs the ttMigrate utility to make the following type conversions as it saves or restores tables:

For information on data types, see "Data Types" in the Oracle TimesTen In-Memory Database SQL Reference.

Cache group data type conversions

When restoring a data store that contains cache groups from a TimesTen release that is earlier than 7.0, use the -convertCGTypes. option to convert the data type of columns from pre-7.0 types to more clearly map with the data types of the columns in the Oracle database with which the cache group is associated.

The following table describes the type mapping.

For information on data types, see "Data Types" in the Oracle TimesTen In-Memory Database SQL Reference and "Mappings between Oracle and TimesTen data types" in the Oracle In-Memory Database Cache User's Guide.

Examples

The following command dumps all data store objects from data store SalesDS into a file called sales.ttm. If sales.ttm already exists, ttMigrate overwrites it.

ttMigrate -c DSN=SalesDS sales.ttm

This command appends all data store objects in the SalesDS data store owned by user MARY to sales.ttm:

ttMigrate -a DSN=SalesDS sales.ttm MARY.%

This command restores all data store objects from sales.ttm into the SalesDS data store:

ttMigrate -r DSN=SalesDS sales.ttm

This command restores MARY.PENDING and MARY.COMPLETED from sales.ttm into SalesDS (Case is ignored in migrate objects.):

ttMigrate -r DSN=SalesDS sales.ttm MARY.PENDINGMARY.COMPLETED

This command lists all migrate objects saved in sales.ttm:

ttMigrate -l sales.ttm

Notes

When migrating backwards into a release of the Oracle TimesTen In-Memory Database that does not support features in the current release, TimesTen generally issues a warning and continues without migrating the unsupported features. In a few cases, where objects have undergone conversion, ttMigrate may fail and return an error message. This may be the case with conversions of data types, character sets and primary key representation.

The following restrictions, limitations and suggestions should be considered before preparing to use ttMigrate.

Asynchronous materialized view: When migrating to a previous release, asynchronous materialized views are ignored and TimesTen returns a warning.

Cache groups: In restore mode, the presence of foreign key dependencies between tables may require ttMigrate to reorder tables to ensure that a child table is not restored before a parent table.

When migrating data stores that contain cache groups from a previous release of TimesTen to TimesTen 7.0 or greater, you must use the option -convertTypesToOra. See "Cache group data type conversions" for a description of the data type mapping.

Character columns in cached tables must have not only the same length but also the same byte semantics as the underlying Oracle tables. Cache group migration fails when there is a mismatch in the length or length semantics of any of its cached tables.

Character sets: By default, ttMigrate stores table data in the database character set, unless you have specified the -saveAsCharset option. At restore time, conversion to another character set can be achieved by migrating the table into a data store that has a different database character set. When migrating data from a release of TimesTen that is earlier than 7.0, TimesTen assumes that the data is already in the target database's character set. If the data is not in the same database character set as the target database, the data may not be restored correctly.

When migrating columns with BYTE length semantics between two data stores that both support NLS but with different database character sets, it is possible for migration to fail if the columns in the new database are not large enough to hold the values in the migrate file. This could happen, for example, if the source data store uses a character set whose maximum byte-length is 4 and the destination data store uses a character set whose maximum byte-length is 2.

TimesTen issues a warning whenever character set conversion takes place to alert you to the possibility of data loss due to conversion.

Data type conversions: When migrating data from a pre-7.0 release of TimesTen, you must explicitly request data type conversions, using either the -convertTypesToOra or the -convertTypesToTT options.

ttMigrate saves the length semantic annotation (BYTE or CHAR) of CHAR and VARCHAR columns and restores these annotations when restoring into TimesTen releases that support them. When migrating backwards into a TimesTen release that does not support these annotations, columns with CHAR length semantics are converted to BYTE length, but their lengths are adjusted to match the byte length of the original columns. When migrating forward from a release that does not support these annotations, BYTE length semantics are used.

Foreign key dependencies: In restore mode, the presence of foreign key dependencies between tables may require ttMigrate to reorder tables to ensure that a child table is not restored before any of its parents. Such dependencies can also prevent a child table from being restored if any of its parent tables were not restored. For example, when restoring a table A that has a foreign key dependency on a table B, ttMigrate first checks to verify that table B exists in the data store. If table B is not found, ttMigrate delays the restoration of table A until table B is restored. If table B is not restored as part of the ttMigrate session, TimesTen prints an error message indicating that table A could not be restored due to an unresolved dependency.

Indexes: TimesTen supports range indexes as primary-key indexes into TimesTen releases that support this feature. When migrating backwards into a release that does not support range indexes as primary-key indexes, the primary keys are restored as hash indexes of the default size. When migrating forward from a release that does not support range indexes as primary-key indexes, the primary keys are restored as hash indexes of the same size as the original index.

TimesTen also supports bitmap indexes. When migrating backwards into a release that does not support bitmap indexes, ttMigrate converts the bitmap indexes to range indexes.

INLINE columns: When migrating TimesTen tables that contain INLINE variable length columns to a release of TimesTen that is earlier than 5.1, you must explicitly use the -noRepUpgrade option. Using the default -repUpgrade option results in an error. The INLINE attribute of the columns is maintained, unless you specify otherwise using the -inline option.

Materialized view logs: TimesTen does not save the content of materialized view logs, only the definition.

Replication: Before attempting a full store migrate of replicated stores, make sure the host name and data store name are the same for both the source and destination data stores.

System views: TimesTen does not save the definitions or content of system vies during migration.

Other considerations: Because ttMigrate uses a binary format, you cannot use ttMigrate to:

• Migrate data stores between hardware platforms.

• Restore data saved with ttBackup or use ttBackup to restore data saved with ttMigrate.

Platforms: You can use ttMigrate to migrate data stores between 32- and 64-bit platforms if the two platforms are otherwise the same. Follow the examples in the Oracle TimesTen In-Memory Database Installation Guide.

• On Windows, you can use ttMigrate to access data stores from any release of TimesTen. On Windows, this utility is supported for all TimesTen Data Manager and Client DSNs.

• On UNIX, the release of ttMigrate must match the release of the data store you are connecting to.

See also