Migrating the Oracle Database Requires Cleaning Up Cache Objects

When you set up cache, cache objects and metadata are installed on both the TimesTen and Oracle databases. When you migrate the Oracle database, the cache metadata on the back-end Oarcle database is no longer correct. Thus, before you migrate your Oracle database, you must clean up cache objects and metadata from both the TimesTen and Oracle databases.

1. Drop all cache groups with the DROP CACHE GROUP statement.

   The following example connects as the TimesTen cache administration user to the cache1 database and drops the customer_orders cache group.

   % ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
   Command> DROP CACHE GROUP customer_orders;

   See Dropping a Cache Group.

2. Clean up cache on both TimesTen and Oracle databases. If you do not clean up cache on both TimesTen and Oracle databases, you will encounter cache errors.

   • When using TimesTen Classic, run the timesten_home/install/oraclescripts/cacheCleanUp.sql SQL*Plus script as the Oracle cache administration user to clean up the Oracle database cache objects and metadata used for cache 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.

   • In TimesTen Scaleout, run the timesten_home/install/oraclescripts/scaleoutCacheCleanUp.sql SQL*Plus script as the Oracle cache administration user to clean up the Oracle Database cache objects and metadata used for cache operations. The grid name and the TimesTen database name are passed as arguments to the scaleoutCacheCleanUp.sql script.

     The following example shows the TimesTen database contains one read-only cache group customer_orders with cache tables sales.customers and sales.orders. This example uses the cacheCleanUp.sql script for a TimesTen Classic system. It drops the change log tables and triggers associated with the two cache tables. The scaleoutCacheCleanup.sql script runs in the same manner for TimesTen Scaleout, except that it requires the grid name and database name as input parameters.

     % cd timesten_home/install/oraclescripts
     % sqlplus cacheadmin/orapwd
     SQL> @cacheCleanUp "sys1" "/disk1/databases/database1"
     
     *****************************OUTPUT**************************************
     Performing cleanup for object_id: 69959 which belongs to table : CUSTOMERS
     Executing: delete from tt_07_agent_status where host = sys1 and datastore =
     /disk1/databases/database1 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 =
     /disk1/databases/database1 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
     **************************************************************************

3. Perform the Oracle database migration.

4. If the Oracle migration eliminated the Oracle cache administration user and its tablespace, then set up cache again on the Oracle database. Check the TimesTen database to ensure that the TimesTen cache administration user and the schema user still exists. See Setting Up a Caching Infrastructure.

5. Recreate the cache groups. See Defining Cache Groups.