Backing Up and Restoring TimesTen Classic Database with the ttMigrate Utility

The ttMigrate utility saves tables and indexes from a TimesTen Classic database into a binary file.

When a cache group is migrated and included in the binary file, it includes the cache group definition and schema; however, the data of the cache group is not migrated.

Note:

See Back Up, Restore, and Migrate Data in TimesTen Classic in the Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide and ttMigrate in the Oracle TimesTen In-Memory Database Reference.

If the restored database connects to a different backend Oracle database than what it had originally connected with and you want to use the ttMigrate utility for backing up and restoring the database, then perform the following:

Stop the cache agent.

% ttIsql -connstr "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> call ttCacheStop;
Command> exit
Disconnecting...
Done.

Run the ttMigrate -c utility command to save the database and its objects into a binary file.

% ttMigrate -c "DSN=cache1" cache1.ttm
...
Saving user CACHEADMIN
User successfully saved.

Saving user sales
User successfully saved.
 
Saving table CACHEADMIN.READTAB
  Saving rows...
  2/2 rows saved.
Table successfully saved.
 
Saving cache group CACHEADMIN.READCACHE
  Saving cached table SALES.READTAB
Cache group successfully saved.

(Optional) Drop all cache groups from the TimesTen database. Since the database still exists with its cache groups, TimesTen recommends that you drop the cache groups. When you drop all cache groups before destroying the TimesTen database, all metadata on the Oracle Database for these cache groups is deleted. However, if you use the cacheCleanup.sql script in a future step, this script deletes the metadata on the Oracle Database.

You may see errors reported, which can be ignored.
```
Command> DROP CACHE GROUP readcache;
Command> exit
Disconnecting...
Done.
```
Destroy the TimesTen database.
```
% ttDestroy cache1
```

Clean up objects on the Oracle database: If you did not drop that cache groups in an earlier step, you can run the timesten_home/install/oraclescripts/cacheCleanUp.sql SQL*Plus script as the Oracle cache administration user to drop the Oracle Database objects used to implement autorefresh operations. The host name of the TimesTen Classic system and the TimesTen database (including its path) are passed as arguments to the cacheCleanUp.sql script.

You can run the ttCacheInfo utility or the cacheInfo.sql script as the Oracle cache administration user to determine the host and database names.

% cd timesten_home/install/oraclescripts
% sqlplus cacheadmin/orapwd
SQL> @cacheCleanUp "sys1" "/users/OracleCache/cache1"

*****************************OUTPUT**************************************
Performing cleanup for object_id: 69959 which belongs to table : CUSTOMER
Executing: delete from tt_05_agent_status where host = sys1 and datastore =
/users/OracleCache/cache1 and object_id = 69959
Executing: drop table tt_05_69959_L
Executing: drop trigger tt_05_69959_T
Executing: delete from tt_05_user_count where object_id = object_id1
Performing cleanup for object_id: 69966 which belongs to table : ORDERS
Executing: delete from tt_05_agent_status where host = sys1 and datastore =
/users/OracleCache/cache1 and object_id = 69966
Executing: drop table tt_05_69966_L
Executing: drop trigger tt_05_69966_T
Executing: delete from tt_05_user_count where object_id = object_id1
**************************************************************************

Create and restore the database:
1. Create the TimesTen database with a first connection request.
2. Create the cache table user and the TimesTen cache administration user. Grant appropriate privileges to these users.
  
  Note:
  
  Depending on which TimesTen Classic release you are migrating from, the users and privileges may or may not be migrated. See ttMigrate in the Oracle TimesTen In-Memory Database Reference.
```
% ttIsql cache1
Command> CREATE USER cacheadmin IDENTIFIED BY timesten;
 User created.
 
Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheadmin;
Command> CREATE USER sales IDENTIFIED BY timesten; 
User created.
 
Command> exit
Disconnecting...
Done.
```
3. Register the Oracle cache administrator user name and password with the ttCacheUidPwdSet built-in procedure.
```
% ttIsql -connstr "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> call ttCacheUidPwdSet('cacheadmin','orapwd');
Command> exit
Disconnecting...
Done.
```

Restore the database from the saved binary file with the ttMigrate -r utility command.

% ttMigrate -r -relaxedUpgrade -cacheuid cacheadmin -cachepwd orapwd
 -connstr "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
 cache1.ttm
...
Restoring table CACHEADMIN.READTAB
  Restoring rows...
  2/2 rows restored.
Table successfully restored.
 
Restoring cache group CACHEADMIN.READCACHE
  Restoring cached table SALES.READTAB
  1/1 cached table restored.
Cache group successfully restored.

Connect to the restored database and reset the cache autorefresh state:
1. Connect to the TimesTen database with ttIsql.
2. Start the cache agent.
3. Alter the cache groups to set autorefresh state to ON.
```
% ttIsql -connstr "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> call ttCacheStart;
Command> ALTER CACHE GROUP readcache SET AUTOREFRESH STATE ON;
```

Note:

If the restored TimesTen database is not able to connect to any backend Oracle database, then TimesTen cannot autorefresh the data for the read-only cache groups.