Defragmenting TimesTen Databases

Under some circumstances, a TimesTen database may develop memory fragmentation such that significant amounts of free memory are allocated to partially filled pages of existing tables. This can result in an inability to allocate memory for other uses (such as new pages for other tables) due to a lack of free memory. In these circumstances, it is necessary to defragment the database in order to make this memory available for other uses.

A secondary table partition is created after a table has been altered with the ALTER TABLE ADD SQL statement. Defragmentation enables you to remove the secondary table partitions and create a single table partition that contains all of the table columns. When secondary table partitions have been created, it is recommended to periodically defragment the database in order to improve space utilization and performance.

The following procedures address the different types of database fragmentation:

Offline Defragmentation of a TimesTen Scaleout Database

A TimesTen Scaleout database is defragmented as part of the export and import process.

Thus, to defragment a TimesTen Scaleout database, use the ttGridAdmin dbExport and dbImport commands. See Exporting and Importing a Database in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

Offline Defragmentation of a TimesTen Classic Database

To defragment a TimesTen Classic database, use the ttMigrate utility.

Stop all connections to the database.
Save a copy of the database using ttMigrate.
```
ttMigrate -c ttdb ttdb.dat
```
As the administration user, rebuild the ttdb database:
```
ttMigrate -r -relaxedUpgrade -connstr "dsn=ttdb" ttdb.dat
```
Note:

You can achieve maximum table defragmentation with the ttMigrate -r -relaxedUpgrade command. The -relaxedUpgrade option also condenses table partitions. If you do not want to condense table partitions, remove the -relaxedUpgrade option from the ttMigrate -r -relaxedUpgrade command.

At this time:

All the users, cache groups, and the active standby pair have been restored to ttdb.
The cache groups are in AUTOREFRESH STATE = OFF.
The cache agent and replication agent are not running.

Table partitions can be added when columns are added to tables with the ALTER TABLE ADD SQL statement. See the notes on ALTER TABLE in the Oracle TimesTen In-Memory Database SQL Reference. See, Avoid ALTER TABLE for performance considerations.

See ttMigrate in the Oracle TimesTen In-Memory Database Reference.

Online Defragmentation of TimesTen Classic Databases in an Active Standby Pair Replication Scheme

Use a combination of the ttMigrate -relaxedUpgrade and ttRepAdmin -duplicate utilities to defragment TimesTen Classic databases (with minimal overall service downtime) that are involved in a replication scheme where TABLE DEFINITION CHECKING is set to RELAXED. In addition, the ttMigrate -relaxedUpgrade option condenses partitions.

Note:

If your TimesTen Classic database uses an active standby pair replication scheme, then you can only defragment these databases if the active standby pair replication scheme either does not contain any cache groups or contains only READONLY cache groups.

The following sections describe how to defragment TimesTen Classic databases that are involved in an active standby pair replication scheme:

The example in this section shows how to perform an online defragmentation with an active standby pair replication scheme where the active database is ttdb1 and the standby database is ttdb2.

Note:

The examples provided in each section assume that you are familiar with the configuration and management of replication schemes. See Getting Started in the Oracle TimesTen In-Memory Database Replication Guide.

Migrate and Rebuild the Standby Database

This section shows how to stop replication to the standby TimesTen database, save a copy of the standby database, and then defragment the standby database.

Note:

While the standby database is defragmented, application processing can continue on the active database.

Perform the following to save a copy of the standby database:

Stop the replication agent on the standby database (ttdb2):
```
ttAdmin –repStop ttdb2
```
If there any subscribers, run ttRepStateSave on the active database to set the status of the standby to failed. As long as the standby database is unavailable, updates to the active database are replicated directly to the subscriber databases.
```
call ttRepStateSave('FAILED', 'ttdb2', 'ttsrv2');
```
Save a copy of the standby database using ttMigrate.
```
ttMigrate -c ttdb2 ttdb2.dat
```
Stop the cache agent, drop any cache groups, and destroy the standby.
```
ttAdmin –cacheStop ttdb2
```
While connected as cache manager user in ttIsql, drop all cache groups:
```
DROP CACHE GROUP t_cg;
```
Destroy the standby database:
```
ttDestroy ttdb2
```
Rebuild the standby database. Run the following on the standby as the instance administrator:
```
ttIsql ttdb2
```
Create the cache manager user and grant the user ADMIN privileges. Using ttIsql:
```
CREATE USER cacheadmin IDENTIFIED BY cadminpwd;
GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE, DROP ANY TABLE TO cacheadmin;
GRANT ADMIN TO cacheadmin;
```
Note:

The cache manager user requires ADMIN privileges in order to run ttMigrate –r. Once migration is completed, you can revoke the ADMIN privilege from this user if desired.

See ttMigrate in the Oracle TimesTen In-Memory Database Reference.
As the cache manager user, rebuild the ttdb2 database:
```
ttMigrate -r -relaxedUpgrade -cacheuid cacheadmin -cachepwd cadminpwd 
-connstr "dsn=ttdb2;uid=cacheadmin;pwd=cadminpwd;oraclepwd=oraclepwd" ttdb2.dat
```
At this time:
- All the users, cache groups, and the active standby pair have been restored to ttdb2.
- The cache groups are in AUTOREFRESH STATE = OFF.
- The cache agent and replication agent are not running.
As the cache manager user, start the cache agent on the standby:
```
ttAdmin –cacheStart ttdb2
```
Load any cache groups.
```
ALTER CACHE GROUP t_cg SET AUTOREFRESH STATE PAUSED;
LOAD CACHE GROUP t_cg COMMIT EVERY 256 ROWS PARALLEL <nThreads>;
```
Note:
- Choose nThreads based on how many CPU cores you use to insert the data into TimesTen for this load operation.
- If there are several read-only cache groups it is recommended that you run several LOAD operations in separate sessions in parallel, if the TimesTen and Oracle Database resources are available.

After completion, verify the cache group state.

Command> cacheGroups;
Cache Group CACHEADMIN.T_CG:
  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: On
  Autorefresh Interval: 10 Seconds
  
  Autorefresh Status: ok
  Aging: No aging defined
 
  Root Table: SALES.T
  Table Type: Read Only
 
1 cache group found.

Start the replication agent on the standby database:
```
ttAdmin -repStart ttdb2
```

Check the replication state on the standby:

% ttIsql ttdb2
Command> call ttRepStateGet;
< STANDBY >
1 row found.

The standby database (ttdb2) has been defragmented and both the active and standby databases are functional.

Reverse the Active and Standby Roles

In order to perform the database defragmentation on the active database, switch the roles of the active and standby database.

The active (ttdb1) becomes the standby database. The original standby (ttdb2) becomes the active database.

Stop all application processing and disconnect all application connections with the ttAdmin -disconnect command. Any query only processing can be moved to work at the ttdb2 TimesTen database. See Disconnecting from a Database in this book and ttAdmin in the Oracle TimesTen In-Memory Database Reference.
Call the ttRepSubscriberWait built-in procedure at the current active database (ttdb1), with the database name and host of the current standby database (ttdb2) as input parameters. This ensures that all queued updates have been transmitted to the current standby database.
Note:

If you set the waitTime to -1, the call waits until all transactions that committed before the call have been transmitted to the subscriber.

However, if you set the waitTime to any value (this value cannot be NULL), ensure that the return timeOut parameter value is 0x00 before continuing. If the returned value 0x01, call the ttRepSubscriberWait built-in procedure until all transactions that committed before the call have been transmitted to the subscriber.

For more information about the ttRepSubscriberWait built-in procedure, see ttRepSubscriberWait in the Oracle TimesTen In-Memory Database Reference.
```
call ttRepSubscriberWait(NULL,NULL,'ttdb2','ttsrv2', 100);
```
Stop the replication agent on the current active database.
```
call ttRepStop;
```
Call the ttRepDeactivate built-in procedure on the current active database. This puts the database in the IDLE state.
```
call ttRepDeactivate;
```
Call the ttRepStateGet built-in procedure to show the database state.
```
Command> call ttRepStateGet;
< IDLE >
1 row found.
```
Promote the standby to active by calling the ttRepStateSet('ACTIVE') built-in procedure on the old standby database. This database now becomes the active database in the active standby pair. Use the ttRepStateGet built-on to verify that the database has become active.
```
call ttRepStateSet('ACTIVE');
```
```
Command> call ttRepStateGet;
< ACTIVE >
1 row found.
```
Stop the replication agent on the database that used to be the active database.
```
ttAdmin -repStop ttdb1
```
Run ttRepStateSave on the new active database to set the status of the old active database to failed. As long as the standby database is unavailable, updates to the active database are replicated directly to the subscriber databases.
```
call ttRepStateSave('FAILED', 'ttdb1', 'ttsrv1');
```
Restart the full application workload on the new active database (ttdb2).

This database now acts as the standby database in the active standby pair.

Destroy and Re-Create a New Standby

You can destroy and recreate a new standby by duplicating the new active with the ttRepAdmin -duplicate command.

During these steps, application processing can continue at the active database.

Stop the cache agent on the new standby database:
```
ttAdmin –cacheStop ttdb1
```
As the cache manager user, drop all cache groups using ttIsql:
```
DROP CACHE GROUP t_cg;
```
Destroy the database:
```
ttDestroy ttdb1
```

Re-create the new standby database by duplicating the new active.

ttRepAdmin -duplicate -from ttdb2 -host ttsrv2 –setMasterRepStart -UID 
ttAdmin -PWD ttadminpwd -keepCG -cacheUID cacheadmin -cachePWD cadminpwd ttdb1

Start cache and replication agents on the new standby database:
```
ttAdmin –cacheStart ttdb1
ttAdmin –repStart ttdb1
```

This process defragments both the active and standby databases with only a few seconds of service interruption.

Online Defragmentation of TimesTen Classic Databases in a Non Active Standby Pair Replication Scheme

Note:

The examples provided in each section assume that you are familiar with the configuration and management of replication schemes. For more information, see Getting Started in the Oracle TimesTen In-Memory Database Replication Guide.

The following sections describe how to defragment TimesTen Classic databases that are involved in a non active standby pair replication scheme:

Note:

These sections discuss how to defragment databases that are involved in a bidirectional replication scheme. In bidirectional replication schemes, each database is both a master and subscriber.

The examples in this section show how to perform an online defragmentation with bidirectional and unidirectional replication schemes with two TimesTen databases named ttdb1 and ttdb2. For the unidirectional replication example, ttdb1 represents the master and ttdb2 represents the subscriber.

Migrate and Rebuild a Database

The first step in the procedure is to stop replication on one of the TimesTen Classic databases and then defragment this database.

Note:

While one of the databases is defragmented, application processing can continue on the other database.

Perform the following to save a copy of the database:

Stop the replication agents on one of the databases.
On the ttdb2 database:
```
ttAdmin –repStop ttdb2
```
Save a copy of the ttdb1 database using ttMigrate.
```
ttMigrate -c ttdb2 ttdb2.dat
```
Destroy the database:
```
ttDestroy ttdb2
```
As a TimesTen user with ADMIN privileges, rebuild the ttdb2 database:
```
ttMigrate -r -relaxedUpgrade -connstr "dsn=ttdb2;uid=ttadmin;pwd=ttadminpwd" ttdb2.dat
```
At this time:
- All the users have been restored to ttdb2.
- The replication agent is not running.
Restart the replication agent on ttdb2:
```
ttAdmin -repStart ttdb2
```

The ttdb2 TimesTen database has been defragmented.

Alter the Replication Scheme

In order to perform the database defragmentation on the ttdb1 database, perform the following:

Stop all application processing and disconnect all application connections with the ttAdmin -disconnect command. Any processing can be moved to work at the ttdb2 TimesTen database. For more information, see Disconnecting from a Database in this book and ttAdmin in the Oracle TimesTen In-Memory Database Reference.
Call the ttRepSubscriberWait built-in procedure at the database that has not been defragmented (ttdb1), with the database name and host of the defragmented database (ttdb2) as input parameters. This ensures that all queued updates have been transmitted to both databases.
Note:

If you set the waitTime to -1, the call waits until all transactions that committed before the call have been transmitted to the subscriber.

However, if you set the waitTime to any value (this value may not be NULL), ensure that the return timeOut parameter value is 0x00 before continuing. If the returned value 0x01, call the ttRepSubscriberWait built-in procedure until all transactions that committed before the call have been transmitted to the subscriber.

See ttRepSubscriberWait in the Oracle TimesTen In-Memory Database Reference.

Using ttIsql on ttdb1:
```
call ttRepSubscriberWait(NULL,NULL,'ttdb2','ttsrv2', 100);
```
If you are using a bidirectional replication scheme, skip steps 3-4 and move to step 5.
For a unidirectional replication scheme, where ttdb1 is the master and ttdb2 is the subscriber, drop the replication scheme on both TimesTen databases:
Using ttIsql on ttdb1:
```
DROP REPLICATION r1;
```
Using ttIsql on ttdb2:
```
DROP REPLICATION r1;
```
For a unidirectional replication scheme, drop the replication scheme on the master (ttdb1) and subscriber (ttdb2):
Using ttIsql on ttdb1:
```
DROP REPLICATION r1;
```
Using ttIsql on ttdb2:
```
DROP REPLICATION r1;
```
Start the replication agent on ttdb2:
```
ttAdmin -repStart ttdb2
```
Stop the replication agent on ttdb1.
```
ttAdmin -repStop ttdb1
```

If you modified a unidirectional replication scheme, the ttdb2 database now acts as the master database in the unidirectional scheme; the ttdb1 database acts as the subscriber database in the unidirectional replication scheme.

Destroy and Re-Create a Database

Destroy and recreate the TimesTen database in the replication scheme that has not yet been defragmented using ttRepAdmin -duplicate.

During these steps, application processing can continue on the defragmented database.

Destroy the database:
```
ttDestroy ttdb1
```
Recreate the new TimesTen Classic database (ttdb1) by duplicating the previously defragmented TimesTen Classic database (ttdb2) involved in the replication scheme.
```
ttRepAdmin -duplicate -from ttdb2 -host ttsrv2 –setMasterRepStart -UID ttadmin -PWD ttadminpwd ttdb1
```
Start the replication agent on the new standby database:
```
ttAdmin –repStart ttdb1
```

This process defragments both of the TimesTen Classic databases involved in either a unidirectional or bidirectional replication scheme with only a few seconds of service interruption.