Backing Up and Restoring a TimesTen Classic Database With Cache Groups

TimesTen Classic databases containing cache groups can be backed up and restored with either the ttBackup or ttMigrate utilities.

• If the restored database connects to the same backend Oracle database, then use the ttBackup and ttRestore utilities, then drop and recreate all cache groups in the restored TimesTen database. If they are static cache groups, you may be required to reload them. For dynamic cache groups, the reload is optional as data is pulled in from the Oracle database as it is referenced.

  Note:

  If another TimesTen database is used to connect to the original backend Oracle database (and now no longer connects) and if all cache groups in the TimesTen database were not cleanly dropped, then run the cacheCleanUp.sql SQL*Plus script against the original Oracle database to remove all leftover objects. Specify the host and path for the original TimesTen database.

  See SQL*Plus Scripts for Cache.

• If the restored database connects to a different backend Oracle database than what it had originally connected with, then perform one of the following:

  • Backing Up and Restoring Using the ttBackup and ttRestore Utilities

  • Backing Up and Restoring TimesTen Classic Database With the ttMigrate Utility

Backing Up and Restoring Using the ttBackup and ttRestore Utilities

When you use the ttBackup utility, it backs up the TimesTen database with all of its data at a particular time.

Thus, if you want to use these cache groups again, restoring this backup requires additional action as the restored data within the cache groups are out of date and out of sync with the data in the backend Oracle database. See Backup, Restore, and Migrate Data in TimesTen Classic in the Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide.

Note:

See ttBackup and ttRestore 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 ttBackup and ttRestore utilities to backup and restore your database, then perform the following:

1. Run the ttBackup utility command to backup the database and its objects into a binary file. For example, to backup the cache1 database using the /tmp/dump directory for temporary storage:

   % ttBackup -dir /tmp/dump -connstr "DSN=cache1"

2. Stop the cache agent.

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

3. (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.

   Command> DROP CACHE GROUP readcache;
   Command> exit;
   Disconnecting...
   Done.

4. Destroy the database before restoring in the same or another location.

   % ttDestroy cache1

5. Clean up objects on the Oracle database. Run the timesten_home/install/oraclescripts/cacheCleanUp.sql SQL*Plus script from the current database install 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_07_agent_status where host = sys1 and datastore =
   /users/OracleCache/cache1 and object_id = 69959
   Executing: drop table tt_07_69959_L
   Executing: drop trigger tt_07_69959_T
   Executing: delete from tt_07_user_count where object_id = object_id1
   Performing cleanup for object_id: 69966 which belongs to table : ORDERS
   Executing: delete from tt_07_agent_status where host = sys1 and datastore =
   /users/OracleCache/cache1 and object_id = 69966
   Executing: drop table tt_07_69966_L
   Executing: drop trigger tt_07_69966_T
   Executing: delete from tt_07_user_count where object_id = object_id1
   **************************************************************************

6. Restore the database with the ttRestore utility and then delete the temporary directory.

   % ttRestore -dir /tmp/dump -connstr "DSN=cache1"
   Restore started ...
   Restore complete
   
   % rm -r /tmp/dump

7. In order to re-synchronize the data within the cache groups, you must drop and recreate the cache groups:

   1. Connect to the TimesTen database providing the cache administration user credentials.

   2. Drop the cache groups that were restored with the ttRestore utility. Because the data is out of sync, you may see errors.

   3. Register the Oracle cache administration user name and password with the ttCacheUidPwdSet built-in procedure.

   4. Start the cache agent.

   5. Recreate and, if required, reload the cache groups.

   % ttIsql -connstr "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
   
   Command> DROP CACHE GROUP readcache;
   Command> call ttCacheUidPwdSet('cacheadmin','orapwd');
   Command> call ttCacheStart;
   Command> CREATE READONLY CACHE GROUP readcache
           AUTOREFRESH INTERVAL 5 SECONDS
           FROM sales.readtab
           (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));
   Command> LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS;
   2 cache instances affected.

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.

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 Backup, 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:

1. Stop the cache agent.

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

2. 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.

3. (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.

4. Destroy the TimesTen database.

   % ttDestroy cache1

5. 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
   **************************************************************************
   

6. 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.

7. 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.

8. 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.