2 Managing TimesTen Databases

Loading a database into memory for TimesTen Classic

1. Before you try to load the database into memory for TimesTen Classic, confirm that the TimesTen daemon is running with the ttStatus utility. The following output shows that the TimesTen daemon is not running.

   % ttStatus
   ttStatus: Could not connect to the TimesTen daemon.
   If the TimesTen daemon is not running, please start it by running "ttDaemonAdmin -start".
   

2. Start the TimesTen daemon, if necessary.

   % ttDaemonAdmin -start
   

3. The RAM policy setting is important as it specifies if, how, and when the database is loaded or unloaded from memory. The default RAM policy for a TimesTen database is inUse.

   You can set the RAM policy before loading the database into memory. See "Specifying a RAM policy" for more information.

   The following example sets the RAM policy of a TimesTen database to manual:

   % ttAdmin -ramPolicy manual database1
   
   RAM Residence Policy            : manual
   Manually Loaded In RAM          : False
   Replication Agent Policy        : manual
   Replication Manually Started    : False
   Cache Agent Policy              : manual
   Cache Agent Manually Started    : False
   Database state                  : open
   

4. Use the ttAdmin utility to load (or reload) the database into memory, or unload the database from memory.

   If the RAM policy is manual for the database database1, then load the TimesTen database into memory with the ttAdmin -ramload utility. The -ramLoad option of the ttAdmin utility can only be used with the manual RAM policy:

   % ttAdmin -ramLoad database1
   
   RAM Residence Policy            : manual
   Manually Loaded In RAM          : True
   Replication Agent Policy        : manual
   Replication Manually Started    : False
   Cache Agent Policy              : manual
   Cache Agent Manually Started    : False
   Database state                  : open
   

   If the RAM policy is manual, you can change it to always to specify that the database is always reloaded.

   ttAdmin -ramPolicy always database1
   

   If the RAM policy is inUse, then you want the grace period to be greater than 0, so that the database will be kept in memory for that time period when idle:

   % ttAdmin -ramPolicy inUse -ramGrace 200 database1
   
   RAM Residence Policy            : inUse plus grace period
   RAM Residence Grace (Secs)      : 200
   Replication Agent Policy        : manual
   Replication Manually Started    : False
   Cache Agent Policy              : manual
   Cache Agent Manually Started    : False
   Database state                  : open
   

   If the RAM policy is manual for the database database1 and the database was previously closed to incoming connections, then you can both load and open the TimesTen database into memory with the ttAdmin -ramload -open command.

   % ttAdmin -ramLoad -open database1
   
   RAM Residence Policy            : manual
   Manually Loaded In RAM          : True
   Replication Agent Policy        : manual
   Replication Manually Started    : False
   Cache Agent Policy              : manual
   Cache Agent Manually Started    : False
   Database state                  : open
   

5. If your database is configured for replication or cache for your database, run the ttAdmin utility to start the replication and cache agents.

   To start replication:

   ttAdmin -repStart database1
   

   To start TimesTen Cache:

   ttAdmin -cacheStart database1
   

For more information on these utilities, see "ttAdmin" and "ttDaemonAdmin" in the Oracle TimesTen In-Memory Database Reference.

Unloading a database from memory for TimesTen Classic

In TimesTen Classic, a database remains loaded in shared memory if any applications or TimesTen agents, such as the cache agent or replication agent, are connected to it. In TimesTen Classic, a database may also be kept in shared memory for particular RAM policy setting, even when no applications or agents are connected.

Before unloading the database from memory for TimesTen Classic, you must first close the database, close all active connections to the database and then set the RAM policy of the database to manual or inUse.

1. Close the database to reject any new requests to connect to the database.

   ttAdmin -close database1
   

2. Disconnect all applications from the database.

   To close all active connections to the database, run the ttAdmin -disconnect command. For more information, see "Disconnecting from a database" in this book and "ttAdmin" in the Oracle TimesTen In-Memory Database Reference.

3. If the replication agent is running on the database, set the replication state to pause and stop the replication agent. The example sets the replication state from the active master database1 to the standby master standbydb to pause, then stops the replication agent on the active master database1.

   ttRepAdmin -receiver -name database1 -state pause standbydb
   ttAdmin -repStop database1
   

4. If the cache agent is running on the database, stop the cache agent.

   ttAdmin -cacheStop database1
   

5. Ensure that the RAM policy is set to either manual or inUse. Then unload the database from memory. See "Specifying a RAM policy" for more information.

   If the RAM policy is set to always, change it to manual and then unload the database from memory with the ttAdmin -ramPolicy -ramUnload utility options.

   ttAdmin -ramPolicy manual -ramUnload database1
   

   If the RAM policy is set to manual, unload the database with the ttAdmin -ramUnload utility:

   ttAdmin -ramUnload database1
   

   If the RAM policy is set to inUse and a grace period is set, set the grace period to 0 or wait for the grace period to elapse. This results in the database being unloaded. TimesTen unloads a database with an inUse RAM policy from memory once you close all active connections.

   ttAdmin -ramGrace 0 database1
   

6. Run the ttStatus utility to verify that the database has been unloaded from memory and the database is closed. The database is unloaded if there are no processes. The database is closed when the output shows "Closed for user connections."

   See "ttStatus" in Oracle TimesTen In-Memory Database Reference for details.

7. Optionally, stop the TimesTen daemon.

   ttDaemonAdmin -stop
   

For more information on the ttAdmin utility, see "ttAdmin" in the Oracle TimesTen In-Memory Database Reference.

Changes to RAM policy after automatic recovery fails

If a fatal error invalidates the database and the automatic database recovery performed by TimesTen Classic is unsuccessful, the following occurs by default:

• The RAM policies of manual and always remain unchanged.

• The replication and cache agents are not restarted.

• After several failed attempts to reload the database, TimesTen Classic sets the policyInactive mode, which prevents any more attempts at loading the database.

Preventing an automatic reload of the database after failure

After a fatal error that causes the database to be invalidated, TimesTen Classic attempts to reload and recover the database, as long as it is consistent with the settings for the RAM policy, cache agent policy, and replication agent policy. However, user processes could still be connected to the invalidated database if they do not know that the original database has been invalidated. In this case, the invalidated database exists in memory until all user processes close their connections. Thus, the invalidated database could coexist in memory with the newly reloaded database. This can be an issue if the database is large.

You can prevent the database from being automatically reloaded after an invalidation using the ttAdmin -noautoreload command. You can reset to the default automatic database reload behavior with the ttAdmin -autoreload command. See "ttAdmin" in the Oracle TimesTen In-Memory Database Reference for more details.

Any one of the following initiates a reload and recovery of the database so that normal behavior can resume:

• The TimesTen daemon restarts.

• A process connects successfully.

• The administrator executes a ttAdmin command for the database that changes the RAM policy, performs a RAM load, or starts either the cache or replication agents.

If you set the behavior to prevent automatic reloads of the database, you may receive the following error when connecting to a database that was not reloaded.

Error 707, "Attempt to connect to a data store that has been manually unloaded from RAM"

Disconnecting from a database in TimesTen Scaleout

If you are unable to individually disconnect every application from the TimesTen Scaleout database, use the ttGridAdmin dbDisconnect command to disconnect all user connections from the database. See "Unloading a database from memory" in the Oracle TimesTen In-Memory Database Scaleout User's Guide for full details.

Disconnecting from a database in TimesTen Classic

You can disconnect all connections to a TimesTen Classic database with the ttAdmin -disconnect command. However, you must first enable the capability for forced disconnect by setting the ForceDisconnectEnabled connection attribute to 1 in the DSN definition within the sys.odbc.ini file. See "ForceDisconnectEnabled" in the Oracle TimesTen In-Memory Database Reference for more details.

While control returns to the command prompt, the force disconnect operation may take multiple seconds (or minutes) to complete. Verify the status of the force disconnect operation with the ttStatus utility.

While the forced disconnect operation is in process, any new connection request is rejected by the main daemon. Once the force disconnect operation completes, new connections are accepted.

You can specify how urgently you need connections to be forced to disconnect with the urgency level:

• The -transactional option waits for any open transactions to be committed or rolled back before disconnecting. Does not affect idle connections.

• The -immediate option rolls back any open transactions before immediately disconnecting. This option also disconnects idle connections.

• The -abort option aborts all direct mode application processes and client/server processes (ttcserver) in order to disconnect.

Most of the time, you should use the transactional and immediate urgency levels. A recommended practice is to run the -disconnect command twice, as necessary. First use the transactional urgency level. Then, after allowing some time, use ttStatus to confirm whether connections have been closed. If not all connections have been closed yet, then use the immediate urgency level.

The abort urgency level should only be used on the rare occasion when both the transactional and immediate urgency levels fail to successfully disconnect all specified connections. The abort option could result in lost transactions, as the abort operation abruptly causes every user and ttcserver process connected to the database to exit.

You can specify which type of connections to disconnect with the granularity level.

• The -users option (default) disconnects every user connection to the database. For example, use this granularity level when preparing to perform database maintenance.

• The -unload option disconnects every connection to the database, including subdaemon connections. For example, use this granularity level when attempting to unload the database.

See "Force disconnect" in the Oracle TimesTen In-Memory Database Reference for more details on using the ttAdmin -disconnect command.

#!/bin/sh
 
# disconnect users and unload the database with the transactional urgency level
ttAdmin -disconnect -transactional -unload database1
 
# wait 10 seconds for the forced disconnect to finish
COUNT = 0
while [ ttStatus | grep "pending disconnection" ] || [ $COUNT -ne 10 ]
do
  sleep 1
  COUNT=$((COUNT+1))
done
 
# increase the urgency level to immediate
if [ ttStatus | grep "pending disconnection" ]; then
  ttAdmin -disconnect -immediate -unload database1
fi

TimesTen status report
 
Daemon pid 10457 port 6627 instance user1
TimesTen server pid 10464 started on port 6629
------------------------------------------------------------------------
------------------------------------------------------------------------
Data store /disk1/databases/database1
Daemon pid 10457 port 6627 instance user1
TimesTen server pid 10464 started on port 6629
There are 14 connections to the data store, ***14 pending disconnection***
Shared Memory KEY 0x0210679b ID 949092358
PL/SQL Memory KEY 0x0310679b ID 949125127 Address 0x5000000000
Type            PID     Context             Connection Name              ConnID
Process         10484   0x00007f3ddfeb4010  tt_181                            1
...

Estimating and modifying the memory region sizes for the database

Database operations cannot complete successfully without allocation of sufficient memory. First, determine appropriate sizes for the TimesTen permanent and temporary memory regions and the transaction log buffer.

Use the ttSize utility or run the application until you can make reasonable estimates, then set these TimesTen connection attributes:

• PermSize: Size of the permanent memory region, in MB, for the database where the actual data is stored. Make sure PermSize is sufficient to hold all the data. You can increase this value, but not decrease it for this database.

  In TimesTen Classic, you can decrease the permanent memory region by re-creating the database with a smaller size. See "Reducing database size for TimesTen Classic" for more details.

  Note:

  The ttSize utility is optimized for databases in TimesTen Classic. See "Determining the value of the PermSize attribute" in the Oracle TimesTen In-Memory Database Scaleout User's Guide for details on how to evaluate an appropriate value for the PermSize connection attribute for a TimesTen Scaleout database.

• TempSize: For TimesTen Classic, TempSize indicates the total amount of memory in MB allocated to the temporary region for the database. For TimesTen Scaleout, TempSize indicates the total amount of memory in MB allocated to the temporary region for an element. Related database operations may fail if TempSize is insufficient. You can change this size with a database restart.

• LogBufMB: Size of the internal transaction log buffer, in MB. By default LogBufMB is 64 MB. See "Configure log buffer and log file size parameters" for details.

See "PermSize", "TempSize", "LogBufMB" and "LogFileSize" in the Oracle TimesTen In-Memory Database Reference for further description of these attributes.

Next, ensure that the maximum shared memory segment size of your system is large enough to contain the database. Use the maximum number of connections that you expect your database to use. All of the values are in MB (megabytes). Make it larger than the following:

PermSize + TempSize + LogBufMB + 1 + (.043 * connections)

If there are multiple TimesTen databases on your system, each using its own shared memory segment, the maximum shared memory segment size must be large enough to accommodate the largest database.

Next, determine the total shared memory allocation you will need (converted to appropriate units). If there are multiple TimesTen databases on your system, the total shared memory allocation must be large enough to accommodate all of them, using the above equation for each database. (Then, on Linux for example, divide this value by the page size, typically 4096 bytes, to get total memory allocation in pages.)

Finally, if you want to allow for database invalidations, there must be at least twice as much physical memory as the size of the largest TimesTen database. If you do not allow for this, but an invalidation does occur, you cannot reload the database into memory until all processes and connections that used the database have been found and terminated.

Also see "Linux prerequisites" in Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide.

Monitoring PermSize and TempSize attributes

The SYS.V$MONITOR and SYS.GV$MONITOR system views contains several columns that can be used to monitor usage of PermSize and TempSize. These columns include PERM_ALLOCATED_SIZE, TEMP_ALLOCATED_SIZE, PERM_IN_USE_SIZE, PERM_IN_USE_HIGH_WATER, TEMP_IN_USE_SIZE, and TEMP_IN_USE_HIGH_WATER. Each of these columns show in KB units the currently allocated size of the database and the in-use size of the database. The system updates this information each time a connection is made or released and each time a transaction is committed or rolled back.

For example, you could evaluate the temporary space usage by running a full workload and watching the high water mark (TEMP_IN_USE_HIGH_WATER) of the temporary space usage. The high water mark can be reset using the ttMonitorHighWaterReset built-in procedure. And if necessary, you can change TempSize to a value of the observed TEMP_IN_USE_HIGH_WATER value and add 10%.

You can monitor block-level fragmentation in the database with the SYS.V$BLOCK_INFO or SYS.GV$BLOCK_INFO system tables or by calling the ttBlockInfo built-in procedure.

See "SYS.GV$MONITOR", "SYS.V$MONITOR", "SYS.GV$BLOCK_INFO" or "SYS.V$BLOCK_INFO" in the Oracle TimesTen In-Memory Database System Tables and Views Reference for details on these views. For more details, see "ttBlockInfo" in the Oracle TimesTen In-Memory Database Reference.

Reducing database size for TimesTen Classic

Once a TimesTen Classic database has been defined with a particular size for the permanent region (indicated by the PermSize DSN attribute), it cannot be reduced to a smaller size, even if tables or rows are deleted.

To reduce the allocated size of the permanent region of a TimesTen Classic database, run the ttMigrate utility to save a copy of the database and then re-create the database with a smaller permanent region size and restore the data.

Perform these steps to reduce the permanent region size of a TimesTen Classic database:

1. Disconnect applications from the database with the ttAdmin -disconnect command. For more information, see "Disconnecting from a database in TimesTen Classic" in this book and "ttAdmin" in the Oracle TimesTen In-Memory Database Reference.

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

   % ttMigrate -c database1 /tmp/database1
   

3. Unload the TimesTen Classic database from memory. See "Unloading a database from memory for TimesTen Classic" for full details for more information.

4. Create a new DSN definition for the new copy of the database with a smaller PermSize value. To modify the original DSN rather than create a new one, you must destroy the original TimesTen Classic database using the ttDestroy utility before restoring from the backup.

5. Recreate the TimesTen Classic database by using ttIsql with AutoCreate=1.

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

   The database is empty at this point.

6. Restore the backup by using the ttMigrate -r and -relaxedUpgrade options.

   % ttMigrate -r -relaxedUpgrade database1 /tmp/database1
   

Receiving out-of-memory warnings

To receive out-of memory warnings, applications must call the ttWarnOnLowMemory built-in procedure.

TimesTen Classic also provides two general connection attributes that determine when a low memory warning should be issued: PermWarnThreshold and TempWarnThreshold. Both attributes take a percentage value.

Storage provisioning for transaction log files

Consider the following when estimating the necessary file system space allocation for transaction log files:

• TimesTen keeps enough transaction log files to support recovering from either checkpoint file. Transaction log files may accumulate depending on the settings for background checkpoint operations (defined with the CkptLogVolume and CkptFrequency first connection attributes). In addition, TimesTen preallocates space for three additional transaction log files.

• TimesTen uses fuzzy checkpoint operations for background operations. Transaction log files can accumulate during fuzzy checkpoint operations. The amount of time taken to create a checkpoint scales with the value assigned to the PermSize connection attribute.

• Transaction log files may accumulate between incremental backups.

• In TimesTen Scaleout, each element in each replica set has its own transaction log files to store transaction log records. If a transaction modifies data in a replica set, then the changes are normally recorded in both elements of the replica set. However, if one of the elements in the replica set is down, then the changes are only recorded in the transaction log files for the active element. Transaction log files can accumulate if one element in a replica set is down for a very long time.

• In TimesTen Classic, transaction log files may accumulate on the master during temporary replication outages or if the subscriber is down.

  Note:

  You can limit the number of transaction log files retained with the FAILTHRESHOLD clause on the CREATE REPLICATION and ALTER REPLICATION statements.

See "CkptLogVolume," "CkptFrequency," and "PermSize" in the Oracle TimesTen In-Memory Database Reference for more information on these connection attributes. See "CREATE REPLICATION" and "ALTER REPLICATION" in the Oracle TimesTen In-Memory Database SQL Reference for more information on these SQL statements.

The estimate of the transaction log volume depends on the peak update transaction rate and the average complexity of each transaction that modifies the database.

Consider:

• B represents the transaction log volume in bytes per transaction.

• C represents the number of columns updated. The minimum transaction update (which is an update of a single number column) generates 400 bytes of transaction log data. Each additional number column update generates another 250 bytes.

• V represents the average size of larger data inserted or updated (columns of type CHAR, VARCHAR2, BINARY, VARBINARY or LOB) for each write transaction.

To estimate the transaction log volume for each transaction, use this formula:

B = 400 + ((C-1) * 250) + V

Note that the first column is just 150 bytes more than all subsequent columns, as follows:

B = (C * 250) + (400-250) + V

which simplifies to:

B =(C * 250) + 150 + V

Multiply the estimated transaction log volume value (B) by the expected peak transaction rate to find the expected peak transaction log rate.

Where:

• L = total storage space to provision for transaction log files in bytes.

• C = average number of columns inserted or updated per write transaction.

• V = average bytes of large data (CHAR, VARCHAR2, BINARY, or VARBINARY columns) inserted or updated for each write transaction.

• S = time in seconds of required transaction log retention.

• T = peak transaction rate per second averaged over intervals of S seconds.

• f = fraction of transactions that include insert, update, or delete operations.

• Lastly, increase the estimated file system space by an additional 30% for contingencies.

Taking all these factors into account, estimate provisioned transaction log file space in bytes according to this formula:

L = ((C * 250) + 150 + V) * S * T * f * 1.3

Copying data from a TimesTen table to an ASCII file

Run the ttBulkCp utility with the -o option to copy data from a TimesTen table to an ASCII file.

% ttBulkCp -o -connstr "DSN=database1;UID=HR;PWD=hr" hr.employees > employees.dmp

For more information about the ttBulkCp utility, see "ttBulkCp" in the Oracle TimesTen In-Memory Database Reference.

Copying data from an ASCII file into a TimesTen table

The ttBulkCp utility enables you to copy data from an ASCII file into a database table. The ttBulkCp utility does not copy duplicate rows into a table.

• Running ttBulkCp with the -i option

• Running ttBulkCp with the -directLoad option on TimesTen Classic

% ttBulkCp -i -connstr "DSN=database1;UID=HR;PWD=hr" hr.employees employees.dmp

employees.dmp:
    107 rows inserted
    0 duplicate rows not inserted
    107 rows total

% ttBulkCp -directLoad -connstr "DSN=database1;UID=HR;PWD=hr" hr.employees employees.dmp

employees.dmp:
    107 rows inserted
    0 duplicate rows not inserted
    107 rows total

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

Offline defragmentation of a TimesTen Classic database

To defragment a TimesTen Classic database, use the ttMigrate utility as follows:

1. Stop all connections to the database.

2. Save a copy of the database using ttMigrate.

   % ttMigrate -c ttdb ttdb.dat
   

3. 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 for more information. Also see, "Avoid ALTER TABLE" for performance considerations.

For more information on ttMigrate, see "ttMigrate" in the Oracle TimesTen In-Memory Database Reference.

Online defragmentation of a TimesTen Classic database

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.

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

• Online defragmentation of TimesTen Classic databases in an active standby pair replication scheme

• Online defragmentation of TimesTen Classic databases in a non active standby pair replication scheme