2 Migration, Backup, and Restoration

This chapter describes migration, backup and restoration for the TimesTen database, covering the following topics:

Overview: copying, migrating, and restoring a database

TimesTen utilities for copying, restoring, and migrating a database enable you to perform a variety of migration and upgrade tasks. Relevant utilities include the following:

  • The ttBackup utility: Creates an image copy of the database that is compatible only within the same major release line and on the same platform. This is a general backup utility, but you can also use it to back up data for minor upgrades (for example, from 11.2.2.x.x to 11.2.2.y.y) or for migrations that do not require any changes to the size or structure of the database.

    This utility saves one checkpoint file and a copy of the log files to create the image.

  • The ttRestore utility: Restores a database that was backed up using ttBackup.

  • The ttMigrate utility: Exports a database to a binary file or imports a database from a binary file created by ttMigrate. (For those familiar with Oracle Database, this is similar in nature to the Oracle Database export and import features.) This utility exports the database in a release-neutral but platform-specific format. You can use it for major upgrades (for example, from 11.2.1.x.x to 11.2.2.y.y).

    To move between platforms, use the ttMigrateCS -c client/server version to export a remote database into a file on your target system, in order to ensure the appropriate format for the target system. Then use ttMigrate -r to restore the contents of the file into the database.

    Note:

    The ttMigrateCS version of ttMigrate is UNIX-only. On Windows systems, you can do the equivalent by using ttMigrate to connect to the source system from the target system through a defined TimesTen client DSN.
  • The ttBulkCp utility: Writes a database table to an ASCII file, or restores a table to a database from an ASCII file created by ttBulkCp.

  • The ttSchema utility: Outputs the SQL DDL statements used to create the schema of a database or selected objects (such as tables, views, or sequences) of a database. This is useful in conjunction with ttBulkCp, to give you the SQL statement to create a table before restoring its data from ttBulkCp.

  • The ttDestroy utility: If the database will be in a new location, you can optionally use this to destroy the old database once the new one is confirmed to be functioning properly.

See "Utilities" in Oracle TimesTen In-Memory Database Reference for details about these utilities.

Note:

You can alternatively use corresponding C functions for ttBackup and ttRestore. See "TimesTen Utility API" in Oracle TimesTen In-Memory Database C Developer's Guide.

Typical tasks may include the following.

TimesTen upgrades:

Database migrations:

Database alterations:

Other tasks:

  • Take a snapshot of a database and then restore the database in the same state. Use ttBackup and ttRestore.

  • Move selected tables of a database to a new location or platform. You can use ttMigrate if the target is the same platform. You can use ttMigrate with ttMigrateCS if the target is a different platform. (Alternatively, you can use ttBulkCp with ttSchema to move to a different platform, but ttMigrate with ttMigrateCS is generally easier and faster.)

  • Change the metadata (for example, change a column name). Use ttBulkCp and ttSchema.

Note:

Reset connection attributes to reflect the changes you made, such as DataStore when you move the database to a different directory or PermSize when you reduce database size.

See "Connection Attributes" in Oracle TimesTen In-Memory Database Reference for details about these connection attributes.

Backing up and restoring a database

The TimesTen backup and restore facility enables you to create a backup of any TimesTen database to restore it at a later time. The primary use for the backup and restore facility is to allow the restoration of a recent state of a database that has been lost. For details about using the TimesTen backup and restore facility, see "ttBackup" and "ttRestore" in Oracle TimesTen In-Memory Database Reference.

Note:

You cannot use these utilities in a client-only installation.

TimesTen backup features

Every database backup contains the information needed to restore the database as it existed at a the backup point, which is the time the backup began. Restoration of a database from a given backup restores the modifications of all transactions that committed before the backup point.

A backup operation is atomic: If it completes successfully, it produces a backup you can use to restore a database to the state of its backup point. If it fails for any reason, it leaves the files of any existing backup intact and its backup point unchanged.

TimesTen writes a database backup to a location specified by a backup path, which consists of a directory name and an optional basename. You must specify the backup directory and basename when the backup is created. The basename defaults to the basename of the database itself if you do not specify a basename.

Important:

  • Do not manually change the contents of the backup directory. The addition, removal, or modification of any file in the backup directory, except for modifications made by ttBackup and ttRestore themselves, may compromise the integrity of the backup. In this event, restoration of the database from the backup may not be possible.

  • Databases containing cache groups can be backed up with the ttBackup utility; however, when restoring such a backup, special consideration is required. The restored data within the cache groups may be out of date or out of sync with the data in the back-end Oracle database. To restore a database that contains cache groups, see "Backing up and restoring a database with cache groups" in Oracle TimesTen Application-Tier Database Cache User's Guide.

Types of backup provided

TimesTen supports both full and incremental backups. TimesTen also allows stream backups.

  • Stream: A stream backup writes the database backup file to stdout.

  • Full: A full backup saves the entire database. For full backups, you must have enough disk space available to hold both the existing backup and the new backup, until the new backup succeeds.

  • Incremental: An incremental backup augments an existing incremental-enabled backup of the same database. An incremental backup moves the backup point of an existing backup forward in time by augmenting the backup with all of the transaction log records created since its last backup point.

    An incremental backup typically completes much faster than a full backup, as it has less data to copy. The performance gain of incremental backups over full backups comes at the cost of increased disk usage and longer restoration times. Use incremental backups in concert with full backups to achieve a balance between backup time, disk usage, and restoration time.

    Before you can perform an incremental backup, you must first enable your backup to allow for incremental backups by executing the ttBackup utility command with the -fileFullEnable or the -fileIncrOrFull options. In either case, if your backup was not previously enabled for incremental, a full file backup is performed before the backup is enabled for subsequent incremental backups. TimesTen supports the creation of up to eight incremental-enabled backup instances for each database. If you attempt to start a ninth incremental backup, TimesTen returns an error.

    If you restore a database from a backup, regardless of whether the backup was enabled or disabled for incremental, the restored database is disabled for incremental backups. Thus, if you want incremental backups, you must again execute the ttBackup utility command with the -fileFullEnable or the -fileIncrOrFull option to enable incremental backups.

A set of files containing backup information for a given database, residing at a given backup path, is known as a backup instance. A given backup instance must be explicitly enabled for incremental backups.

The files of the existing backup may be modified by a failed full or incremental backup, but not in a way that compromises the ability to restore from them.

The total list of backup types supported by TimesTen are as follows:

Backup type File or stream Full or incremental Incremental-enabled Comment
fileFull File Full No This is the default.
fileFullEnable File Full Yes  
fileIncremental File Incremental Yes Fails if incremental backup is not possible.
fileIncrOrFull File Either Yes Performs fileIncremental if possible, or fileFullEnable otherwise.
streamFull Stream Full No  
incrementalStop None None No Takes no backup; just disables existing incremental-enabled backup.

Migrating a database

This section discusses migration of TimesTen databases to a new location, covering the following scenarios:

These procedures are for standalone TimesTen databases. Replication and TimesTen Cache scenarios are not considered here.

Note:

As discussed in appropriate subsections following, when a database is moved between platforms or bit levels, ttMigrateCS (the client/server version of ttMigrate) or Windows equivalent is required. This is to remotely access the database to be saved, in order to have the data file created on the local target system, assuring proper format for the target system. The Windows equivalent is using ttMigrate to connect to the source system from the target system through a defined TimesTen client DSN.

Moving a database to a different computer (same platform)

You can use the ttBackup and ttRestore utilities to move a database between two computers that have the same major release of TimesTen, the same CPU architecture, and the same operating system.

To copy a database from one system to another with the same CPU architecture and operating system:

  1. Disconnect applications from the database.

  2. Back up the database on the original system using ttBackup. For example:

    ttBackup -dir /tmp/dump -fname salesdata SalesData
    
  3. Move the backup to the new system.

  4. Create a DSN for the database on the new system. See "Creating a Data Manager DSN on UNIX" or "Creating a Data Manager DSN on Windows" in Oracle TimesTen In-Memory Database Operations Guide.

  5. Confirm the DataStore connection attribute is set correctly for the location on the new system.

  6. Restore the backup on the new system using ttRestore. For example:

    ttRestore -dir /tmp/dump -fname salesdata NewSalesData
    
  7. As desired, once the database is operational on the new system, use the ttDestroy utility to destroy the database on the old system, and delete the old DSN.

Moving a database to a different platform

The internal format of a TimesTen database differs between platforms. You can use this procedure to move a database from one platform to another, such as from Solaris to Linux or from a 32-bit platform to a 64-bit platform, and reformat it for the target platform.

  1. Disconnect applications from the database.

  2. From the target system, use ttMigrateCS (or Windows equivalent) to connect in client/server mode to the database on the originating system and use the -c option to create a data file for the database on the target system. For example:

    ttMigrateCS -c salesdata1 salesdata.dat
    
  3. On the target system, create a DSN for the database. See "Creating a Data Manager DSN on UNIX" or "Creating a Data Manager DSN on Windows" in Oracle TimesTen In-Memory Database Operations Guide.

  4. Recreate the database using AutoCreate=1, such as in the following example:

    ttIsql -connstr "dsn=salesdata2;AutoCreate=1" -e "quit"
    

    The database will be empty at this point.

  5. Confirm the DataStore connection attribute is set correctly for the location on the new system.

  6. On the target system, import the file created by ttMigrateCS into the new database using the ttMigrate utility with the -r option and the -relaxedUpgrade option. For example:

    ttMigrate -r -relaxedUpgrade salesdata2 salesdata.dat
    
  7. As desired, once the database is operational on the new system, use the ttDestroy utility to destroy the database on the old system, and delete the old DSN.

Notes:

Be aware of the following when moving between 32-bit and 64-bit platforms.
  • When changing bit levels, the database cannot be involved in a replication scheme.

  • TimesTen does not support replication between a 32-bit database and a 64-bit database.

Altering a database

This section discusses scenarios where the database remains on the same system, covering the following topics:

These procedures are for standalone TimesTen databases. Replication and TimesTen Cache scenarios are not considered here.

Note:

As discussed in appropriate subsections following, when a database is moved between platforms or bit levels, ttMigrateCS (the client/server version of ttMigrate) or Windows equivalent is required. This is to remotely access the database to be saved, in order to have the data file created on the local target system, assuring proper format for the target system. The Windows equivalent is using ttMigrate to connect to the source system from the target system through a defined TimesTen client DSN.

Moving a database to a different directory

The TimesTen daemon identifies a database by the full path name of the database checkpoint files. To move a TimesTen database to a different directory, back up the database using the ttBackup utility, create a new DSN definition that specifies the new database path name, then restore the database into its new location using the ttRestore utility.

Examples in the following procedure move a database from /old/SalesData/sales with a database name SalesData to /new/SalesData/sales with database name NewSalesData, using the /tmp/dump directory for temporary storage. (The examples are for a UNIX system. Do the equivalent on a Windows system.)

  1. Create a temporary directory. For example:

    mkdir /tmp/dump
    
  2. Disconnect applications from the database.

  3. Back up the database to the temporary directory using ttBackup. For example:

    ttBackup -dir /tmp/dump -fname salesdata SalesData
    
  4. Unload the database from memory. For more information, see "Unloading a database from memory".

  5. Create a DSN definition for the new location and specify the new database path. For example: /new/SalesData/sales/NewSalesData.

  6. Confirm the DataStore connection attribute is set correctly for the new location.

  7. Restore the backup in the new location. For example:

    ttRestore -dir /tmp/dump -fname salesdata NewSalesData
    
  8. Remove the temporary directory. For example:

    rm -r /tmp/dump
    
  9. After you confirm the database is operational in the new location, optionally use the ttDestroy utility to destroy it in the old location, and delete the old DSN.

Reducing database size

Once a database has been defined with a particular size for the permanent partition (indicated by the PermSize DSN attribute), it cannot be loaded at a smaller size, even if tables or rows are deleted. A copy of the database made with ttBackup also has the permanent partition size of the database embedded in it.

To reduce the allocated size of the permanent partition of a database, you must save a copy using the ttMigrate utility then re-create the database with a smaller permanent partition size and restore the data.

Perform these steps to reduce the permanent partition size of a database:

  1. Disconnect applications from the database.

  2. Use the ttMigrate -c option to create a data file for the database. For example:

    ttMigrate -c salesdata /tmp/salesbackup
    
  3. Unload the database from memory. For more information, see "Unloading a database from memory".

  4. To modify the original DSN rather than create a new one, you must destroy the original database files using the ttDestroy utility before restoring from the backup.

  5. Create a new DSN definition for the new copy of the database with a smaller PermSize value.

  6. Recreate the database using AutoCreate=1, such as in the following example:

    ttIsql -connstr "dsn=salesdata;AutoCreate=1" -e "quit"
    

    The database will be empty at this point.

  7. Restore the backup, using the ttMigrate -r and -relaxedUpgrade options. For example:

    ttMigrate -r -relaxedUpgrade salesdata /tmp/salesbackup
    

Notes:

  • The permanent partition size of a database cannot be reduced below the size that is required by the data currently stored in the database. This value can be determined by querying the perm_in_use_size column of the system table sys.monitor.

  • You can also use this procedure to compact the database in order to reduce fragmentation caused by partially full table pages or fragmentation of the heap buffers that store index nodes and out-of-line values.

Globalization support during migration

The ttMigrate utility saves one or more migration objects from a TimesTen database into a binary data file or restores the objects from the binary data files into a TimesTen database. Migration objects include tables, cache group definitions, views, and sequences.

The following topics describe what occurs with globalization issues during migration:

Also see the description of ttMigrate in the Oracle TimesTen In-Memory Database Reference.

Object migration and character sets

The ttMigrate utility tags each object it saves with the object's character set. By default, ttMigrate stores object data in the database character set; however, you can select a different character set by using the -saveAsCharset option. You can specify this option in create mode (-c) or append mode (-a).

When you use ttMigrate to restore an object, the data is implicitly converted to the database character set of the target database, if necessary. Character set conversion may result in data loss if the database character set of the target database cannot represent all of the data that it receives.

Notes:

  • If you use the -saveAsCharset option when saving an object, you cannot use ttMigrate -r -exactUpgrade to restore it.

  • The ttMigrate utility issues a warning whenever there is an implicit or explicit character set conversion while saving or restoring data.

  • If character set conversion is requested when migrating databases, performance may be slower than if character set conversion is not requested.

If you know that the data is encoded in the database character set of the target database, use the -noCharsetConversion option when restoring (-r). When using this option, ttMigrate assumes that the data uses the same database character set of the target database.

When you restore untagged character data from a database that was created before release 7.0 into a database from release 7.0 and later, ttMigrate treats the data as if it is in the database character set of the target database. All TimesTen databases from release 7.0 and earlier use the TIMESTEN8 character set.

If you are migrating from a TimesTen database that uses TIMESTEN8 to a TimesTen database that uses a different character set, the following may occur:

  • The query result may differ in the new TimesTen database with the new character set. The user application may work on a multibyte character set and use the TIMESTEN8 character set to store the character string as it is. When querying the data using the LIKE predicate (or any scalar functions) to match a substring, the query engine may match a binary pattern that does not begin or end at a character boundary under TIMESTEN8 character set, since it is a single byte character set. Every byte is treated as a character even it is actually in the middle of a multibyte character.

    Another possible issue arises if the user partitions a long character string and stores it in separate rows. The string may be reconstructed later by concatenating the values from multiple rows. This may work with the TIMESTEN8 character set. However, when using a multibyte character set, if the partition is not on the character boundary, the string value can be changed. In this case, ensure that the string is partitioned on the character boundary.

  • Performance may be slower with queries that use predicates or scalar functions on character strings in databases with a character set other than TIMESTEN8.

Migration and length semantics

The ttMigrate utility saves length semantic information about CHAR and VARCHAR2 columns. It restores the length semantic information when restoring objects into databases created in TimesTen release 7.0 or later.

When objects are migrated back into a TimesTen release before 7.0, columns with character semantics are converted to byte semantics and the column length is adjusted to match the byte length of the original columns.

When objects are migrated from a release before 7.0 to release 7.0 and later, byte semantics is used.

Migrating linguistic indexes

The ttMigrate utility supports migration of linguistic indexes into TimesTen releases that support them. When migrating back to a TimesTen release before 7.0, ttMigrate issues a warning indicating that the linguistic indexes cannot be restored. Migration of the table proceeds without the linguistic indexes.

Migrating cache group tables

You cannot restore cache group tables containing NCHAR/NVARCHAR2 columns to a release before 7.0. Releases before 7.0 do not allow these data types in cache group tables.