ttMigrate
ttMigrate
utility saves and restores TimesTen objects and migrates databases between different TimesTen releases.You can perform these operations:
-
Saves an object from a TimesTen database into the
ttMigrate
data file. -
Restores an object from the
ttMigrate
data file into a TimesTen database. -
Prints details about the contents of a
ttMigrate
data file.
Saved objects include:
-
Tables
-
Cache group definitions
-
Views and materialized views
-
Sequences
-
Replication schemes
-
Users and user information, including database privileges for each user.
ttMigrate
utility to migrate databases in three ways:
- From one major release of TimesTen Classic (such as Release 18.1) to another major release of TimesTen (for example, Release 22.1).
- From TimesTen Classic to TimesTen Scaleout.
- From TimesTen Scaleout to TimesTen Classic.
Since database checkpoint and transaction log files are not compatible between major releases, you need to use the ttMigrate
utility to upgrade major release versions of TimesTen Classic.
Note:
Users and user privileges did not exist in TimesTen 7.0 and previous releases. Hence, these objects will not be available for restoration.
For more information about TimesTen databases migration, see Moving to a Different Major Release of TimesTen Classic in Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide.
The data files produced by this utility are platform-dependent. For example, a ttMigrate
data file created on Linux can only be restored on Linux and not any other platform, for example AIX.
By default, the ttMigrate
utility restores the database using one thread. During restoration, you can specify the -numThreads
option to restore the data files using multiple threads, thus potentially improving performance.
The ttMigrate
utility is supported for TimesTen server DSNs. For TimesTen client DSNs use the utility ttMigrateCS
(client/server version).
Note:
Although cross-release compatibility over client/server protocol is supported in TimesTen, the tool ttMigrateCS
is not backward and forward release compatible; hence it can be used only for the same version client/server connections.
Required Privilege
This utility requires various privileges depending on the options specified. The instance administrator, or ADMIN
, has access to all the options.
Only the instance administrator can use the -r
option. If the database has been created at the time 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, and ADMIN
if autocreation of users is necessary. If the database is involved in replication or cache operations, then CACHE_MANAGER
is also required.
Only the instance administrator can use the -c
option to capture an entire database. 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.
Usage in TimesTen Scaleout and TimesTen Classic
This utility is predominantly supported in TimesTen Classic. Migrating a database from TimesTen Classic is the only usage of thettMigrate
utility supported in TimesTen Scaleout.
Syntax
ttMigrate {-h | -help | -?} {-V | -version}
To create or append a data file, use:
ttMigrate {-a | -c} [-v verbosity] [-nf] [-nr] [-fixNaN] [-saveAsCharset charset] [-relaxedUpgrade | -exactUpgrade] [-activeDML | -noActiveDML] {DSN | -connStr connection_string} data file [[objectOwner.]objectName...]
To restore a database from a data file created by this utility, use:
ttMigrate -r [-C ckptFreq] [-v level] [-nf] [-nr] [-fixNaN] [-numThreads n] [-updateStats | -estimateStats percent] [-relaxedUpgrade | -exactUpgrade] [-inline rule] [-noCharsetConversion] [-cacheUid uid [-cachePwd pwd]] [-autorefreshPaused] [-restorePublicPrivs] [-localhost host] [-resizeHashIndexes] {DSN | -connstr connection_string} dataFile [objectOwner.objectName...]
To list or display the contents of a data file created by this utility, use:
ttMigrate {-l | -L | -d | -D} dataFile [[objectOwner
.]objectName...]
Options
The append (-a
) or create (-c
) modes, the list (-l
/-L
) or describe (-d
/-D
) modes and the restore (-r
) modes are exclusive of each other. You cannot specify any of these options on the same line as any other of these options.
ttMigrate
has the options:.
Option | Description |
---|---|
|
Prints a usage message and exits. |
|
Prints the release number of |
|
Selects append mode: Appends data to a pre-existing data file, that was originally created using |
|
Create mode: Creates an original data file. See Create Mode (-c) and Append Mode (-a) for more details. |
|
Specifies the verbosity level for messages printed when
|
|
Specifies that |
|
Specifies that |
|
Converts all |
|
Saves an object in the specified connection character set. If this option is not set, by default, |
|
Save or restore the tables in a way that is compatible with a replication scheme that uses
This option should not be used in combination with a replication scheme that uses The default is |
|
Save or restore the tables in a way that is compatible with a replication scheme that uses
This option should not be used in combination with a replication scheme that uses This is the default. |
|
Saves all tables in a foreign key hierarchy in a single transaction, maintaining consistency between these tables when there is active DML during the If
|
|
Specifies an ODBC data source name of the database to be migrated. |
|
An ODBC connection string that specifies a database location, driver, and optionally other connection attribute settings. |
|
The owner of an object. |
|
The name of the database object(s) to be saved or restored. |
|
Selects restore mode. Restores a database from a data file created by this utility. See Restore Mode (-r)for more details. |
|
Specifies the number of threads to use while restoring a database files. If unspecified, Valid values are 1 through 32. |
|
Specifies that
If you specify both Use of this flag may improve the performance of materialized view restoration and may also improve the performance of queries on the restored tables and views. |
|
Specifies that
If you specify both Use of this flag may improve the performance of materialized view restoration and may also improve the performance of queries on the restored tables and views. |
|
Indicates the rule to be used for converting variable-length columns to
If
|
|
The cache administration user ID to use when restoring asynchronous writethrough cache groups and cache groups with the |
|
The cache administration password to use when restoring autorefresh and asynchronous writethrough cache groups and cache groups with the If the cache administration user ID is provided on the command line but the cache administration password is not, then |
|
Selects list mode. Lists the names of database objects in the specified data file. See List Mode (-l) and Long-list Mode (-L) for more details. |
|
Selects long-list mode. Lists the names of database objects in the specified data file and other details about the database objects. See List Mode (-l) and Long-List Mode (-L) for more details. |
|
Selects describe mode. Displays a short description of the objects in the data file. See Describe Mode (-d) for more details. |
|
Selects long-describe mode. Displays a full description of the objects in the data file. See Long-Describe Mode (-D) for more details. |
|
The path name of the data file to which objects are to be saved or from which objects are to be restored. |
The following ttMigrate
options are available in restore mode (-r
) only:
Option | Description |
---|---|
|
Restores cache groups with |
|
Specifies that NOTE: This option is not supported in TimesTen Scaleout. |
|
Determines the best type mapping from the underlying Oracle database tables to TimesTen cached tables using:
|
|
Restores indexes and foreign keys. Use this option only for TimesTen Classic to TimesTen Scaleout migration. See Migrating a Database from TimesTen Classic to TimesTen Scaleout in Oracle TimesTen In-Memory Database Scaleout User's Guide. |
|
Restores rows into tables. Use this option only for TimesTen Classic to TimesTen Scaleout migration. See Migrating a Database from TimesTen Classic to TimesTen Scaleout in Oracle TimesTen In-Memory Database Scaleout User's Guide. |
|
Explicitly identifies the name or IP address of the local host when restoring replicated tables. |
|
Restores data, retaining the connection character set that is stored in the data file. If not set, See also: This option may be useful for legacy TimesTen users who may have migrated pre-18.1 data into a 18.1 or later release of TimesTen as |
|
Resizes user hash indexes during restore to be optimal size based on number of table rows. |
|
Restores privileges that were granted to |
Modes
Create Mode (-c) and Append Mode (-a)
In create mode, the ttMigrate
utility saves objects from a TimesTen database into a new binary data file. If the data file does not exist, the ttMigrate
utility creates it. Otherwise, ttMigrate
overwrites the existing file, destroying its contents.
The data file format used by the ttMigrate
utility 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, the ttMigrate
utility appends objects from a TimesTen database to an existing data file. If the data file does not exist, the ttMigrate
utility creates it.
For each ordinary (non-cached) table, the ttMigrate
utility saves the following:
-
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, the ttMigrate
utility 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. No row data is exported.
- 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.
Note:
When you migrate from TimesTen Classic to TimesTen Scaleout, if a cache group type that is not supported in TimesTen Scaleout is encountered, the utility issues an error, the cache group is skipped, and the import process continues. Additionally, static auto refresh cache groups will be created with hash distribution for all tables during the import process.
After the ttMigrate
utility is used to restore a database, all autorefresh cache groups in the restored database have AUTOREFRESH
state set to OFF
, no matter how it was set on the source database. After restoring a cache group with ttMigrate -r
, reset its AUTOREFRESH STATE
to ON
by using the ALTER CACHE GROUP
statement (this can be done programmatically or with the ttIsql
utility).
For each view, the ttMigrate
utility saves the following:
-
All the same information as a typical table.
-
The query defining the view.
For each sequence, the ttMigrate
utility saves the following:
-
The complete definition of the sequence.
-
The sequence's current value.
For each user (except the instance administrator), the ttMigrate
utility saves the following:
-
User name.
-
The user's encrypted password.
-
Privileges that have been granted to the user.
For PUBLIC
, the ttMigrate
utility saves all privileges that have been granted to PUBLIC
after database creation.
If there are any replication schemes defined, the ttMigrate
utility saves all of the TTREP
tables containing the replication schemes. Replication schemes should have names that are unique from all other database objects. It is not possible to migrate a replication scheme with the same name as any other database object.
Note:
The ttMigrate
utility does not save the rows of a cached table into the data file, even if you have not specified the -nr
option. The foreign key definitions of cached tables are always saved, regardless of the use of the -nf
option, as they are needed to maintain the integrity of the cache group.
By default, the ttMigrate
utility saves all database objects and users in the database to the data file, including tables, views, cache groups, sequences, users and replication schemes. Alternatively, you can give a list of database objects to be saved on the command line, except for replication schemes. The names in this list can contain the wildcard characters %
(which matches one or more characters) and _
(which matches a single character). The ttMigrate
utility saves all database objects that match any of the given patterns. You do not need to be fully qualify names: If a name is given with no owner, ttMigrate
saves all database 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 the ttMigrate
utility 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, the ttMigrate
utility restores all database objects from a data file into a TimesTen database.
For each ordinary (non-cached) table, the ttMigrate
utility restores:
-
The table, using the original owner, table name, column names, types and nullability and the original primary key.
-
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, the ttMigrate
utility restores:
-
The cache group definition, using the original cache group owner and name. No data is imported.
-
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
orREADONLY
), and theWHERE
clause, if any. -
The foreign key definitions of the cached tables.
-
All the indexes on the cached tables.
-
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. -
All autorefresh cache groups in the restored database have the
AUTOREFRESH
state set toOFF
, no matter how it was set on the source database. If preferred, you can set theAUTOREFRESH
state of restored autorefresh cache groups toPAUSED
instead ofOFF
using the-autorefreshPaused
option.
The -exactUpgrade
option is the default for both -c
and -r
options.
By default, the ttMigrate
utility restores all tables and cache groups in the data file. 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 the ttMigrate
utility issues an error.
Use the -v
option to control the information that the ttMigrate
utility 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, -inline
preserve
, ttMigrate
restores all variable-length columns with the same INLINE
or NOT INLINE
setting with which they were saved. In the other two modes, -inline
dsDefault
and -inline
maxlen
, 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-inline
dsDefault
, this threshold is the default automatic INLINE
length for a TimesTen database. The -inline
maxlen
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, the ttMigrate
utility lists the names of database objects in the specified data file, including cached tables and the replication scheme TTREP
tables.
In long-list mode, the ttMigrate
utility lists the names of database objects in the data file, 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, the ttMigrate
utility lists the replication scheme name and all the database objects in the file. Alternatively, you can provide a list of names of database 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, the ttMigrate
utility gives a short description for database objects in the specified file.
For each table, the ttMigrate
utility 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, the ttMigrate
utility also lists the query defining the view.
For cache groups, the ttMigrate
utility 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, the ttMigrate
utility lists the replication scheme name and all the TTREP
replication scheme tables in the same manner as user tables.
By default, the ttMigrate
utility describes all the database objects in the file. Alternatively, you can provide a list of names of database 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 database objects in the specified file.
For each table, the ttMigrate
utility 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, the ttMigrate
utility 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, the ttMigrate
utility lists all the values used to define the sequence and its current value.
For replication schemes, the ttMigrate
utility lists all the TTREP
replication scheme tables in the same manner as user tables.
By default, the ttMigrate
utility describes all of database objects in the file. Alternatively, you can provide a list of names of database objects on the command line. The names in this list must be fully qualified and cannot use wildcard characters.
Cache Group Data Type Conversions
When restoring a database 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.
Pre-7.0 TimesTen Type | Oracle Type | Converted Type |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Any |
|
|
Any |
|
|
Any |
|
|
Any |
|
|
Any |
|
|
Any |
|
|
Any |
|
|
Any |
|
|
|
|
|
|
|
|
|
|
Any1 |
|
|
Note:
Any
means the type value does not affect the converted result type.
For information on data types, see Data Types in Oracle TimesTen In-Memory Database SQL Reference and Mappings Between Oracle Database and TimesTen Data Types in Oracle TimesTen In-Memory Database Cache Guide.
Return Codes
The ttMigrate
utility restore (-r
) and create (-c
) commands return the following exit codes:
0
- All objects were successfully created or restored.
1
- Some objects successfully created or restored. Some objects could not be created or restored due to errors.
2
- Fatal error, for example, could not connect or could not open the data file.
3
- Ctrl-C
or another signal received during the create or restore operation.
Examples
The following command dumps all database objects from database SalesDS
into a file called sales.ttm
. If sales.ttm
exists, ttMigrate
overwrites it.
% ttMigrate -c SalesDS sales.ttm
This command appends all database objects in the SalesDS
database owned by user MARY
to sales.ttm
:
% ttMigrate -a SalesDS sales.ttm MARY.%
This command restores all database objects from sales.ttm
into the SalesDS
database:
% ttMigrate -r SalesDS sales.ttm
This command restores MARY.PENDING
and MARY.COMPLETED
from sales.ttm
into SalesDS
(objects are case-insensitive):
% ttMigrate -r SalesDS sales.ttm MARY.PENDINGMARY.COMPLETED
This command lists all objects saved in sales.ttm
:
% ttMigrate -l sales.ttm
Notes
When migrating backward 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.
Consider the following restrictions, limitations, and suggestions before using the ttMigrate
utility.
Cache groups: In restore mode, the presence of foreign key dependencies between tables may require the ttMigrate
utility to reorder tables to ensure that a child table is not restored before a parent table.
Character columns in cached tables must have not only the same length but also the same byte semantics as the underlying Oracle database tables. Cache group migration fails when there is a mismatch in the length or length semantics of any of its cached tables.
The connection attribute PassThrough
with a nonzero value is not supported with this utility and returns an error.
Character sets: By default, the ttMigrate
utility 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 database 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 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 databases 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 database uses a character set whose maximum byte-length is 4 and the destination database 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.
Foreign key dependencies: In restore mode, the presence of foreign key dependencies between tables may require the ttMigrate
utility 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 database. 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.
Replication: Before starting to migrate an entire set of replicated databases, ensure the host name and database name are the same for both the source and destination databases.
System views: TimesTen does not save the definitions or content of system vies during migration.
Other considerations: You cannot use the ttMigrate
utility to:
-
Migrate databases between hardware platforms.
-
Restore data saved with
ttBackup
or usettBackup
to restore data saved withttMigrate
.
-
Migrate from one database release to a different
ttMigrate
release. The release of thettMigrate
utility must match the release of the database to which you are connecting.
It is recommended that you do not run DDL SQL commands via ttIsql
or programmatically while running the ttMigrate -r
operation to avoid lock contention issues for your application.